1 package body hr_entry as
2 /* $Header: pyeentry.pkb 120.34.12020000.2 2012/10/22 12:35:21 pparate ship $ */
3 --
4 -- NAME
5 -- hr_entry.return_termination_date
6 --
7 -- DESCRIPTION
8 -- Returns the actual_termination_date if an assignment has been
9 -- terminated.
10 -- If the assignment has not been terminated then the returned
11 -- actual_termination_date date will be null.
12 --
13 g_debug boolean := hr_utility.debug_enabled;
14 g_target_entry_id pay_element_entries_f.target_entry_id%type;
15 function return_termination_date(p_assignment_id in number,
16 p_session_date in date)
17 return date is
18 l_actual_termination_date date;
19 begin
20 --
21 -- Select the actual termination date is the assignment has been
22 -- terminated.
23 --
24 if g_debug then
25 hr_utility.set_location('hr_entry.return_termination_date', 1);
26 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
27 hr_utility.trace(' p_session_date : '|| p_session_date);
28 end if;
29 begin
30 select pos.actual_termination_date
31 into l_actual_termination_date
32 from per_periods_of_service pos,
33 per_assignments_f pa
34 where pos.person_id = pa.person_id
35 and pa.assignment_id = p_assignment_id
36 and p_session_date
37 between pa.effective_start_date
38 and pa.effective_end_date
39 and pos.actual_termination_date is not null;
40 exception
41 when NO_DATA_FOUND then
42 null;
43 end;
44 --
45 return(l_actual_termination_date);
46 --
47 end return_termination_date;
48 --
49 -- NAME
50 -- hr_entry.get_nonrecurring_dates
51 --
52 -- DESCRIPTION
53 -- Called when a nonrecurring entry is about to be created. Makes sure that
54 -- the assignment is to a payroll and also a time period exists. Returns the
55 -- start and end dates of the nonrecurring entry taking into account
56 -- changes in payroll.
57 --
58 procedure get_nonrecurring_dates
59 (
60 p_assignment_id in number,
61 p_session_date in date,
62 p_effective_start_date out nocopy date,
63 p_effective_end_date out nocopy date,
64 p_payroll_id out nocopy number,
65 p_period_start_date out nocopy date,
66 p_period_end_date out nocopy date
67 ) is
68 --
69 -- Local Variables
70 v_payroll_id number;
71 v_asg_effective_start_date date;
72 v_asg_effective_end_date date;
73 v_time_period_start_date date;
74 v_time_period_end_date date;
75 v_start_date date;
76 v_end_date date;
77 --
78 begin
79 g_debug := hr_utility.debug_enabled;
80 --
81 if g_debug then
82 hr_utility.set_location('hr_entry.get_nonrecurring_dates',5);
83 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
84 hr_utility.trace(' p_session_date : '|| p_session_date);
85 end if;
86 --
87 -- Retrieve the payroll for the assignment on the date the nonrecurring
88 -- entry is being created NB if there is no payroll then it is invalid to
89 -- create a nonrecurring entry.
90 begin
91 --
92 select asg.payroll_id,
93 asg.effective_start_date,
94 asg.effective_end_date
95 into v_payroll_id,
96 v_asg_effective_start_date,
97 v_asg_effective_end_date
98 from per_assignments_f asg
99 where asg.assignment_id = p_assignment_id
100 and asg.payroll_id is not null
101 and p_session_date between asg.effective_start_date
102 and asg.effective_end_date;
103 --
104 exception
105 when no_data_found then
106 hr_utility.set_message(801, 'HR_6047_ELE_ENTRY_NO_PAYROLL');
107 hr_utility.raise_error;
108 end;
109 --
110 if g_debug then
111 hr_utility.set_location('hr_entry.get_nonrecurring_dates',10);
112 hr_utility.trace(' v_payroll_id : '|| v_payroll_id);
113 hr_utility.trace(' v_asg_effective_start_date : '|| v_asg_effective_start_date);
114 hr_utility.trace(' v_asg_effective_end_date : '|| v_asg_effective_end_date);
115 end if;
116 --
117 -- Retrieve the start and end dates of the period for the payroll on the
118 -- date on which the nonrecurring entry is being created NB. a payroll
119 -- period must exist for a nonrecurring entry to be created.
120 begin
121 --
122 select tim.start_date,
123 tim.end_date
124 into v_time_period_start_date,
125 v_time_period_end_date
126 from per_time_periods tim
127 where tim.payroll_id = v_payroll_id
128 and p_session_date between tim.start_date
129 and tim.end_date;
130 --
131 exception
132 when no_data_found then
133 hr_utility.set_message(801, 'HR_6614_PAY_NO_TIME_PERIOD');
134 hr_utility.raise_error;
135 end;
136 --
137 if g_debug then
138 hr_utility.set_location('hr_entry.get_nonrecurring_dates',15);
139 hr_utility.trace(' v_time_period_start_date : '|| v_time_period_start_date);
140 hr_utility.trace(' v_time_period_end_date : '|| v_time_period_end_date);
141 end if;
142 --
143 -- Current assignment record starts after the beginning of the time period.
144 -- 8798020 Removed date track joins from below query
145 if v_asg_effective_start_date > v_time_period_start_date then
146 --
147 loop
148 --
149 begin
150 --
151 select asg.effective_start_date
152 into v_start_date
153 from per_assignments_f asg
154 where asg.assignment_id = p_assignment_id
155 and asg.effective_end_date = v_asg_effective_start_date - 1
156 and asg.assignment_type = 'E' ;
157
158 -- bug 6485636
159 -- and asg.payroll_id + 0 = v_payroll_id;
160 --
161 exception
162 when no_data_found then exit;
163 end;
164 --
165 v_asg_effective_start_date := v_start_date;
166 --
167 end loop;
168 --
169 end if;
170 --
171 if g_debug then
172 hr_utility.set_location('hr_entry.get_nonrecurring_dates',20);
173 hr_utility.trace(' v_start_date : '|| v_start_date);
174 end if;
175 --
176 -- Current assignment record ends before the finish of the time period.
177 -- 8798020 Removed date track joins from below query
178 if v_asg_effective_end_date < v_time_period_end_date then
179 --
180 loop
181 --
182 begin
183 --
184 select asg.effective_end_date
185 into v_end_date
186 from per_assignments_f asg
187 where asg.assignment_id = p_assignment_id
188 and asg.effective_start_date - 1 = v_asg_effective_end_date
189 and asg.assignment_type = 'E' ;
190
191 -- bug 6485636
192 -- and asg.payroll_id + 0 = v_payroll_id;
193 --
194 exception
195 when no_data_found then exit;
196 end;
197 --
198 v_asg_effective_end_date := v_end_date;
199 --
200 end loop;
201 --
202 end if;
203 --
204 if g_debug then
205 hr_utility.set_location('hr_entry.get_nonrecurring_dates',25);
206 hr_utility.trace(' v_end_date : '|| v_end_date);
207 end if;
208 --
209 -- Return the start and end dates of the nonrecurring entry.
210 p_effective_start_date := greatest(v_asg_effective_start_date,
211 v_time_period_start_date);
212 p_effective_end_date := least(v_asg_effective_end_date,
213 v_time_period_end_date);
214
215 if g_debug then
216 hr_utility.trace(' p_effective_start_date : '|| p_effective_start_date);
217 hr_utility.trace(' p_effective_end_date : '|| p_effective_end_date);
218 end if;
219 --
220 -- Return the payroll and the start and end dates for the period.
221 p_payroll_id := v_payroll_id;
222 p_period_start_date := v_time_period_start_date;
223 p_period_end_date := v_time_period_end_date;
224 --
225 end get_nonrecurring_dates;
226 --
227 -- NAME
228 -- hr_entry.chk_entry_overlap
229 --
230 -- DESCRIPTION
231 -- When multiple entries are not allowed then make sure there are no overlaps
232 -- of normal entries of ther same type ie. entry_type = 'E'. For nonrecurring
233 -- it is important to check for nonrecurring entries within the period as it
234 -- it is now possible to have nonrecurring entriesthat can exist for part of
235 -- a period eg.
236 --
237 -- ASG |---P1----|---P2---|---P1---|
238 -- EL |-------------------------P1---------------------> (nonrecurring)
239 -- Period |-------------------------------------------|
240 -- EE |---------|
241 --
242 -- Try to add |--------|
243 --
244 -- NB. this actually clashes with the existing EE although they do not overlap.
245 --
246 procedure chk_entry_overlap
247 (
248 p_element_entry_id in number,
249 p_assignment_id in number,
250 p_element_link_id in number,
251 p_processing_type in varchar2,
252 p_entry_type in varchar2,
253 p_mult_entries_allowed_flag in varchar2,
254 p_validation_start_date in date,
255 p_validation_end_date in date,
256 p_period_start_date in date,
257 p_period_end_date in date
258 ) is
259 --
260 -- Local variables
261 v_overlap_occurred varchar2(1) := 'N';
262 --
263 begin
264 --
265 if g_debug then
266 hr_utility.set_location('hr_entry.chk_entry_overlap',5);
267 hr_utility.trace(' p_element_entry_id : '|| p_element_entry_id);
268 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
269 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
270 hr_utility.trace(' p_processing_type : '|| p_processing_type);
271 hr_utility.trace(' p_entry_type : '|| p_entry_type);
272 hr_utility.trace(' p_mult_entries_allowed_flag : '|| p_mult_entries_allowed_flag);
273 hr_utility.trace(' p_validation_start_date : '|| p_validation_start_date);
274 hr_utility.trace(' p_validation_end_date : '|| p_validation_end_date);
275 hr_utility.trace(' p_period_start_date : '|| p_period_start_date);
276 hr_utility.trace(' p_period_end_date : '|| p_period_end_date);
277 end if;
278 --
279 -- Only do check if the entry being altered is a normal entry ie. not
280 -- adjustment, additional etc ... If multiple concurrent entries are not
281 -- allowed then it is invalid for two recurring entries of the same type
282 -- to overlap or for two nonrecurring entries of the same type to exist
283 -- within the same period.
284 if p_entry_type = 'E' and p_mult_entries_allowed_flag = 'N' then
285 --
286 begin
287 -- INDEX hint added following NHS project recommendation
288 select 'Y'
289 into v_overlap_occurred
290 from sys.dual
291 where exists
292 (select /*+ INDEX(ee, pay_element_entries_f_n51) */ null
293 from pay_element_entries_f ee
294 where ee.entry_type = 'E'
295 and ee.element_entry_id <> nvl(p_element_entry_id,0)
296 and ee.assignment_id = p_assignment_id
297 and ee.element_link_id = p_element_link_id
298 and ((p_processing_type = 'R' and
299 ee.effective_start_date <= p_validation_end_date and
300 ee.effective_end_date >= p_validation_start_date)
301 or (p_processing_type = 'N' and
302 ee.effective_start_date >= p_period_start_date and
303 ee.effective_end_date <= p_period_end_date)));
304 --
305 exception
306 when no_data_found then null;
307 end;
308 --
309 end if;
310 --
311 if v_overlap_occurred = 'Y' then
312 --
313 hr_utility.set_message(801, 'HR_6956_ELE_ENTRY_OVERLAP');
314 hr_utility.raise_error;
315 --
316 end if;
317 --
318 end chk_entry_overlap;
319 --
320 -- --------------------- return_qualifying_conditions -------------------------
321 --
322 -- Name: return_qualifying_conditions
323 --
324 -- Description: If the element entry link is discretionary and has
325 -- qualifying conditions then check the length of
326 -- service and age conditions.
327 --
328 -- Returns: p_los_date --> date at which the los is eligible.
329 -- p_age_date --> date at which the age is eligible.
330 --
331 -- If dates return null then check is not valid.
332 --
333 procedure return_qualifying_conditions
334 (
335 p_assignment_id in number,
336 p_element_link_id in number,
337 p_session_date in date,
338 p_los_date out nocopy date,
339 p_age_date out nocopy date
340 ) is
341 --
342 l_status varchar2(1) := 'S'; -- returning function status
343 l_qualifying_age number(2);
344 l_qualifying_los number(6,2);
345 l_qualifying_units varchar2(30);
346 l_warning_or_error varchar2(30);
347 l_fail varchar2(1) := 'N';
348 --
349 --WWbugs 414903 and 407604
350 --Single select statements for both qualifying conditions changed to explicit
351 --cursors and therefore eliminated p_session_date from this queries.(mlisieck)
352 --
353 cursor csr_los_date is
354 -- calculate the element entry start date according to the lenght of service
355 -- qualifying condition
356 select decode(l_qualifying_units,
357 'H', p.date_start + trunc(l_qualifying_los/24),
358 'D', p.date_start + l_qualifying_los,
359 'Y', add_months(p.date_start,(12 * l_qualifying_los)),
360 'W', p.date_start + (l_qualifying_los * 7),
361 add_months(p.date_start,l_qualifying_los))
362 from per_periods_of_service p,
363 per_all_assignments_f a
364 where a.assignment_id = p_assignment_id
365 and p.period_of_service_id = a.period_of_service_id;
366 --
367 cursor csr_age_date is
368 -- calculate the element entry start date according to the qualifying age
369 -- condition
370 select add_months(p.date_of_birth, (l_qualifying_age * 12))
371 from per_all_people_f p,
372 per_assignments_f asg
373 where p.person_id = asg.person_id
374 and p.date_of_birth is not null
375 and asg.assignment_id = p_assignment_id
376 -- session_date comparison has been removed, so ensure that
377 -- looking at the recent data.
378 and p.effective_start_date = (select max(papf.effective_start_date)
379 from per_all_people_f papf
380 where papf.person_id = asg.person_id);
381 --
382 begin
383 g_debug := hr_utility.debug_enabled;
384
385 if g_debug then
386 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
387 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
388 hr_utility.trace(' p_session_date : '|| p_session_date);
389 end if;
390 --
391 -- Ensure all the passed parameters exist
392 --
393 if (p_assignment_id is null or
394 p_element_link_id is null or
395 p_session_date is null) then
396 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
397 hr_utility.set_message_token('PROCEDURE',
398 'hr_entry.return_qualifying_conditions');
399 hr_utility.set_message_token('STEP','1');
400 hr_utility.raise_error;
401 end if;
402 --
403 -- select the qualifying conditions for los.
404 --
405 begin
406 if g_debug then
407 hr_utility.set_location('hr_entry.return_qualifying_conditions', 5);
408 end if;
409 select pel.qualifying_age,
410 pel.qualifying_length_of_service,
411 pel.qualifying_units
412 into l_qualifying_age,
413 l_qualifying_los,
414 l_qualifying_units
415 from pay_element_links_f pel
416 where pel.element_link_id = p_element_link_id
417 and p_session_date
418 between pel.effective_start_date
419 and pel.effective_end_date;
420 exception
421 when NO_DATA_FOUND then
422 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
423 hr_utility.set_message_token('PROCEDURE',
424 'hr_entry.return_qualifying_conditions');
425 hr_utility.set_message_token('STEP','5');
426 hr_utility.raise_error;
427 end;
428 --
429 if (l_qualifying_los is not null) then
430 --
431 -- Need to select the valid los date
432 --
433 begin
434 if g_debug then
435 hr_utility.set_location('hr_entry.return_qualifying_conditions', 10);
436 end if;
437 open csr_los_date;
438 fetch csr_los_date into p_los_date;
439 close csr_los_date;
440 exception
441 when NO_DATA_FOUND then
442 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
443 hr_utility.set_message_token('PROCEDURE',
444 'hr_entry.return_qualifying_conditions');
445 hr_utility.set_message_token('STEP','10');
446 hr_utility.raise_error;
447 end;
448 --
449 end if;
450 --
451 -- Need to check the age qualification.
452 -- If the select does not return any rows then we can assume that the
453 -- DOB is null.
454 -- If the DOB is null then this check will be invalid and the returning
455 -- p_age_date will be NULL.
456 --
457 if (l_qualifying_age is not null) then
458 --
459 begin
460 if g_debug then
461 hr_utility.set_location('hr_entry.return_qualifying_conditions', 15);
462 end if;
463 open csr_age_date;
464 fetch csr_age_date into p_age_date;
465 close csr_age_date;
466 exception
467 when NO_DATA_FOUND then
468 NULL;
469 end;
470 --
471 end if;
472 --
473 end return_qualifying_conditions;
474 --
475 -- NAME
476 -- hr_entry.generate_entry_id
477 --
478 -- DESCRIPTION
479 -- Generates then next sequence value for inserting an element entry into the
480 -- PAY_ELEMENT_ENTRIES_F base table.
481 --
482 FUNCTION generate_entry_id return number is
483 v_element_entry_id number;
484 --
485 begin
486 --
487 -- Select the next element_entry_id unique primary key id
488 --
489 begin
490 SELECT PAY_ELEMENT_ENTRIES_S.NEXTVAL
491 INTO v_element_entry_id
492 FROM SYS.DUAL;
493 exception
494 when NO_DATA_FOUND then
495 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
496 hr_utility.set_message_token('PROCEDURE','hr_entry.generate_entry_id');
497 hr_utility.set_message_token('STEP','1');
498 hr_utility.raise_error;
499 end;
500 --
501 -- Return the next element_entry_id unique primary key id
502 --
503 return v_element_entry_id;
504 --
505 end generate_entry_id;
506 --
507 -- NAME
508 -- hr_entry.generate_run_result_id
509 --
510 -- DESCRIPTION
511 -- Generates then next sequence value for inserting a run result into the
512 -- PAY_RUN_RESULTS base table.
513 --
514 FUNCTION generate_run_result_id return number is
515 v_run_result_id number;
516 --
517 begin
518 --
519 -- Select the next run_result_id unique primary key id
520 --
521 begin
522 SELECT PAY_RUN_RESULTS_S.NEXTVAL
523 INTO v_run_result_id
524 FROM SYS.DUAL;
525 exception
526 when NO_DATA_FOUND then
527 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
528 hr_utility.set_message_token('PROCEDURE','hr_entry.generate_run_result_id');
529 hr_utility.set_message_token('STEP','1');
530 hr_utility.raise_error;
531 end;
532 --
533 -- Return the next run_result_id unique primary key id
534 --
535 return v_run_result_id;
536 --
537 end generate_run_result_id;
538 --
539 -- NAME
540 -- hr_entry.entry_process_in_run
541 --
542 -- DESCRIPTION
543 -- This function return a boolean value for the specified
544 -- element_type_id depending on the process_in_run_flag attribute.
545 -- The function returns TRUE if the process_in_run_flag = 'Y' or
546 -- FALSE if the process_in_run_flag
547 --
548 FUNCTION entry_process_in_run (p_element_type_id in number,
549 p_session_date in date) return boolean is
550 v_process_in_run_flag varchar2(1);
551 -- bugfix 703103 change select into to cursor in order to rmove the need for
552 -- the session_date restriction that was used to ensure only one row would
553 -- be returned. This allow the employee termination procedure to proceed
554 -- for elements only defined after the termination date.
555 cursor c1 is select pet.process_in_run_flag
556 from pay_element_types_f pet
557 where pet.element_type_id = p_element_type_id;
558 --
559 begin
560
561 if g_debug then
562 hr_utility.trace(' p_element_type_id : '|| p_element_type_id);
563 hr_utility.trace(' p_session_date : '|| p_session_date);
564 end if;
565
566 begin
567 open c1;
568 fetch c1 into v_process_in_run_flag;
569 if c1%NOTFOUND then
570 close c1;
571 hr_utility.set_message(801, 'HR_6884_ELE_ENTRY_NO_ELEMENT');
572 hr_utility.set_message_token('DATE_SUPPLIED',p_session_date);
573 hr_utility.raise_error;
574 end if;
575 close c1;
576 end;
577 --
578 return (v_process_in_run_flag = 'Y');
579 end entry_process_in_run;
580 --------------------------------------------------------------------------------
581 --
582 -- NAME
583 -- hr_entry.Assignment_eligible_for_link
584 --
585 -- DESCRIPTION
586 -- Returns 'Y' if the specified assignment and link match as at the
587 -- specified date. A match indicates that the assignment is eligible for
588 -- the link as at that date. This function may be called from within SQL.
589 -- If no match is found then 'N' will be returned (it never returns NULL).
590 -- NB The reason this function does not return BOOLEAN is that the boolean
591 -- datatype is not supported in SQL statements.
592 --
593 function assignment_eligible_for_link (
594 --
595 p_assignment_id in natural,
596 p_element_link_id in natural,
597 p_effective_date in date,
598 p_creator_type in varchar2) return varchar2 is
599 --
600 lpi_effective_date constant date := trunc (p_effective_date);
601 /*
602 || Make sure that code will work even if user passes a time portion in the
603 || p_effective_date. Refer to lpi_effective_date instead of p_effective_date
604 || throughout the code.
605 */
606 all_parameters_are_valid constant boolean :=
607 (
608 p_assignment_id is not null
609 and p_element_link_id is not null
610 and p_effective_date is not null
611 );
612 --
613 cursor csr_eligibility is
614 --
615 -- Return a row if a match is found between the element link criteria
616 -- and the attributes of the assignment as at the effective date
617 --
618 select 'Y' ROW_RETURNED
619 from per_assignments_f ASG,
620 pay_element_links_f PEL
621 where lpi_effective_date between pel.effective_start_date
622 and pel.effective_end_date
623 and lpi_effective_date between asg.effective_start_date
624 and asg.effective_end_date
625 and pel.element_link_id = P_ELEMENT_LINK_ID
626 and asg.assignment_id = P_ASSIGNMENT_ID
627 and ((pel.payroll_id is not null
628 and asg.payroll_id = pel.payroll_id)
629 or (pel.link_to_all_payrolls_flag = 'Y'
630 and asg.payroll_id is not null)
631 or (pel.payroll_id is null
632 and pel.link_to_all_payrolls_flag = 'N'))
633 and (pel.organization_id = asg.organization_id
634 or pel.organization_id is null)
635 and (pel.position_id = asg.position_id
636 or pel.position_id is null)
637 and (pel.job_id = asg.job_id
638 or pel.job_id is null)
639 and (pel.grade_id = asg.grade_id
640 or pel.grade_id is null)
641 and (pel.location_id = asg.location_id
642 or pel.location_id is null)
643 -- start of change 115.20 --
644 and (
645 pel.pay_basis_id = asg.pay_basis_id
646 or
647 --
648 -- if EL is associated with a pay basis then this clause fails
649 --
650 pel.pay_basis_id is null and
651 NOT EXISTS
652 (SELECT pb.pay_basis_id
653 FROM PER_PAY_BASES pb,
654 PAY_INPUT_VALUES_F iv
655 WHERE iv.element_type_id = pel.element_type_id
656 and p_effective_date between
657 iv.effective_start_date and iv.effective_end_date
658 and pb.input_value_id =
659 iv.input_value_id
660 and pb.business_group_id = asg.business_group_id
661 )
662 or
663 --
664 -- if EL is associated with a pay basis then the associated
665 -- PB_ID must match the PB_ID on ASG
666 --
667 pel.pay_basis_id is null and
668 EXISTS
669 (SELECT pb.pay_basis_id
670 FROM PER_PAY_BASES pb,
671 PAY_INPUT_VALUES_F iv
672 WHERE iv.element_type_id = pel.element_type_id
673 and p_effective_date between
674 iv.effective_start_date and iv.effective_end_date
675 and pb.input_value_id =
676 iv.input_value_id
677 and pb.pay_basis_id = asg.pay_basis_id
678 )
679 -- change 115.23
680 or
681 pel.pay_basis_id is null and
682 asg.pay_basis_id is null and
683 EXISTS
684 (SELECT pb.pay_basis_id
685 FROM PER_PAY_BASES pb,
686 PAY_INPUT_VALUES_F iv
687 WHERE iv.element_type_id = pel.element_type_id
688 and p_effective_date between
689 iv.effective_start_date and iv.effective_end_date
690 and pb.input_value_id =
691 iv.input_value_id
692 and pb.business_group_id = asg.business_group_id
693 )
694 -- bug 7434613
695 OR
696 pel.pay_basis_id is null and
697 p_creator_type IN ('RR','EE')
698 )
699 -- end of change 115.20 --
700 and (pel.employment_category = asg.employment_category
701 or pel.employment_category is null)
702 and (pel.people_group_id is null
703 or exists
704 (select 1
705 from pay_assignment_link_usages_f palu
706 where palu.assignment_id = P_ASSIGNMENT_ID
707 and palu.element_link_id = P_ELEMENT_LINK_ID
708 and lpi_effective_date between palu.effective_start_date
709 and palu.effective_end_date))
710 ;
711 --
712 l_eligibility varchar2 (1) := 'N';
713 --
714 begin
715
716 if g_debug then
717 hr_utility.trace('In hr_entry.assignment_eligible_for_link');
718 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
719 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
720 hr_utility.trace(' p_effective_date : '|| p_effective_date);
721 hr_utility.trace(' p_creator_type : '|| p_creator_type);
722 end if;
723 --
724 hr_general.assert_condition (all_parameters_are_valid);
725 --
726 open csr_eligibility;
727 fetch csr_eligibility into l_eligibility;
728 close csr_eligibility;
729 --
730 hr_utility.trace(' l_eligibility : '|| l_eligibility);
731 --
732 return l_eligibility;
733 --
734 end assignment_eligible_for_link;
735 --------------------------------------------------------------------------------
736 -- NAME
737 -- hr_entry.assignment_eligible_for_link
738 --
739 -- DESCRIPTION
740 -- Bugfix 7434613
741 -- Overloaded version provided for backwards compatibility.
742 --
743 function assignment_eligible_for_link (
744 --
745 p_assignment_id in natural,
746 p_element_link_id in natural,
747 p_effective_date in date) return varchar2 is
748 --
749 l_eligibility varchar2 (1) := 'N';
750 --
751 begin
752 --
753 -- Call the new assignment_eligible_for_link procedure, passing in null
754 -- for the p_creator_type
755 --
756 l_eligibility := assignment_eligible_for_link
757 (
758 p_assignment_id => p_assignment_id,
759 p_element_link_id => p_element_link_id,
760 p_effective_date => p_effective_date,
761 p_creator_type => null
762 );
763 --
764 return l_eligibility; /*Bug 8798020 Added missing return statement */
765 end assignment_eligible_for_link;
766 --------------------------------------------------------------------------------
767 --
768 -- NAME
769 -- hr_entry.chk_asg_visible
770 --
771 -- DESCRIPTION
772 -- Raise error PAY_34811_ENTRY_MAINT_SEC_ASG if the user does not have the
773 -- appropriate privileges to see the assignment identifed by p_assignment_id.
774 --
775 procedure chk_asg_visible (p_assignment_id in number, p_session_date in date)
776 is
777 --
778 cursor csr_sec_asg(p_asg_id number, p_session_date date) is
779 select 1
780 from per_assignments_f
781 where assignment_id = p_asg_id
782 and p_session_date between effective_start_date and effective_end_date;
783 --
784 v_asg_visible number;
785 begin
786 --
787 if g_debug then
788 hr_utility.set_location('hr_entry.chk_asg_visible', 1);
789 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
790 hr_utility.trace(' p_session_date : '|| p_session_date);
791 end if;
792 --
793 -- Check to see if this assignment is visible to a secure user.
794 -- If not then raise error.
795 -- Bug 5867658.
796 --
797 open csr_sec_asg(p_assignment_id, p_session_date);
798 fetch csr_sec_asg into v_asg_visible;
799
800 hr_utility.trace(' v_asg_visible : '|| v_asg_visible);
801 --
802 if csr_sec_asg%notfound then
803 --
804 if g_debug then
805 hr_utility.set_location('hr_entry.chk_asg_visible', 2);
806 hr_utility.trace(' Assignment ID Not Found: '||p_assignment_id);
807 end if;
808 --
809 -- The user is not authorized to process this assignment.
810 --
811 close csr_sec_asg;
812 --
813 hr_utility.set_message(801,'PAY_34811_ENTRY_MAINT_SEC_ASG');
814 hr_utility.raise_error;
815 --
816 end if;
817 --
818 close csr_sec_asg;
819 --
820 if g_debug then
821 hr_utility.set_location('hr_entry.chk_asg_visible', 3);
822 end if;
823 --
824 end chk_asg_visible;
825 --------------------------------------------------------------------------------
826 -- NAME
827 -- hr_entry.get_eligibility_period
828 --
829 -- DESCRIPTION
830 -- This procedure selects the minimum or maximum (or both) effective assignment
831 -- dates where the assignment is eligible for a given element link.
832 --
833 procedure get_eligibility_period (
834 --
835 p_assignment_id in number, -- Assignment being given the element entry
836 --
837 p_element_link_id in number, -- Link through which the eligibility for the
838 -- element is granted
839 --
840 p_session_date in date, -- Context date for datetrack selection.
841 --
842 -- Bugfix 5135065
843 -- Added parameters p_time_period_start_date and p_time_period_end_date.
844 p_creator_type IN varchar2, -- Bug 7434613. Creator type used in assignment_eligible for link
845 -- to skip pay_basis criteria validation for retro entry creation
846 p_time_period_start_date in date, -- Beginning of the time period under
847 -- consideration, should contain the
848 -- payroll period start date if a non-
849 -- recurring entry is being created
850 --
851 p_time_period_end_date in date, -- End of the time period under
852 -- consideration, should contain the
853 -- payroll period end date if a non-
854 -- recurring entry is being created
855 --
856 p_min_eligibility_date in out nocopy date, -- The earliest date that the assignment is eligible
857 -- for the element, in an unbroken period encompassing
858 -- the session date (See explanation below).
859 --
860 p_max_eligibility_date in out nocopy date -- The latest date that the assignment is eligible
861 -- for the element, in an unbroken period encompassing
862 -- the session date (See explanation below).
863 --
864 ) is
865 --
866 cursor csr_link_bounds is
867 --
868 -- Get the outer boundaries of the link date effectivity
869 -- NB The aggregate functions mean that this cursor will
870 -- ALWAYS return a row.
871 --
872 select min (effective_start_date) LINK_START,
873 max (effective_end_date) LINK_END
874 from pay_element_links_f
875 where element_link_id = P_ELEMENT_LINK_ID;
876 --
877 cursor csr_assignment_bounds is
878 --
879 -- Get the outer boundaries of the assignment date effectivity
880 -- NB The aggregate functions mean that this cursor will
881 -- ALWAYS return a row
882
883 select min (paf.effective_start_date) ASGT_START,
884 max (paf.effective_end_date) ASGT_END
885 from per_assignments_f paf
886 where paf.assignment_id = P_ASSIGNMENT_ID
887 and paf.assignment_type in ('E','B','C') ; -- Added assignment_type 'C' in the check for bug 8792107
888 -- Added assignment_type 'B' in the check for bug 8371393
889 --and paf.assignment_type = 'E' ; -- Added assignment_type check for bug 7648259
890 --
891 cursor csr_minimum (p_assignment_id number, lpi_session_date date, p_time_period_start_date date) is
892 --
893 select asg1.effective_end_date
894 from per_assignments_f asg1
895 where asg1.assignment_id = p_assignment_id
896 -- Removed the following predicate as it is redundant
897 -- i.e. If assignment end date is less than session date then it
898 -- follows that the assignment start date must be less than
899 -- session date.
900 --and asg1.effective_start_date <= lpi_session_date
901 and asg1.effective_end_date <= lpi_session_date
902 -- Bugfix 5135065
903 -- Exclude any pieces of the assignment that end before the time period
904 -- start date
905 and asg1.effective_end_date >= p_time_period_start_date
906 order by asg1.effective_end_date desc;
907 --
908 cursor csr_maximum (p_assignment_id number, lpi_session_date date, p_time_period_end_date date) is
909 select asg1.effective_start_date
910 from per_assignments_f asg1
911 where asg1.assignment_id = p_assignment_id
912 and asg1.effective_end_date >= lpi_session_date
913 -- Bugfix 5135065
914 -- Exclude any pieces of the assignment that start after the time
915 -- period end date
916 and asg1.effective_start_date <= p_time_period_end_date
917 order by asg1.effective_start_date;
918 --
919 l_link csr_link_bounds%rowtype;
920 l_assignment csr_assignment_bounds%rowtype;
921 -- Bugfix 5135065
922 -- Local time period start and end date variables
923 l_time_period_start_date date;
924 l_time_period_end_date date;
925 --
926 no_current_eligibility exception;
927 --
928 l_procedure_name constant varchar2 (80) := 'hr_entry.get_eligibility_period';
929 lpi_session_date constant date := trunc (p_session_date);
930 --
931 all_parameters_are_valid constant boolean :=
932 (
933 p_assignment_id is not null
934 and p_element_link_id is not null
935 and p_session_date is not null
936 );
937 --
938 l_post_termination_rule varchar2(10);
939 l_service_rec per_periods_of_service%rowtype;
940 l_eff_term_date date;
941 --
942 begin
943 --
944 if g_debug then
945 hr_utility.set_location(l_procedure_name,1);
946 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
947 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
948 hr_utility.trace(' p_session_date : '|| p_session_date);
949 hr_utility.trace(' p_creator_type : '|| p_creator_type);
950 hr_utility.trace(' p_time_period_start_date : '|| p_time_period_start_date);
951 hr_utility.trace(' p_time_period_end_date : '|| p_time_period_end_date);
952 hr_utility.trace(' p_min_eligibility_date : '|| p_min_eligibility_date);
953 hr_utility.trace(' p_max_eligibility_date : '|| p_max_eligibility_date);
954 end if;
955 --
956 -- Initialize "out" parameters
957 --
958 P_MIN_ELIGIBILITY_DATE := null;
959 P_MAX_ELIGIBILITY_DATE := null;
960 --
961 if g_debug then
962 hr_utility.set_location(l_procedure_name,2);
963 end if;
964 --
965 -- Get the outer bounds of the link
966 --
967 open csr_link_bounds;
968 fetch csr_link_bounds into l_link;
969 close csr_link_bounds;
970 --
971 -- Get the outer bounds of the assignment
972 --
973 open csr_assignment_bounds;
974 fetch csr_assignment_bounds into l_assignment;
975 close csr_assignment_bounds;
976 --
977 if g_debug then
978 hr_utility.trace(' l_link.LINK_START : '|| l_link.LINK_START);
979 hr_utility.trace(' l_link.LINK_END : '|| l_link.LINK_END);
980 hr_utility.trace(' l_assignment.ASGT_START : '|| l_assignment.ASGT_START);
981 hr_utility.trace(' l_assignment.ASGT_END : '|| l_assignment.ASGT_END);
982 end if;
983 --
984 if
985 -- if the link does not exist as at session date
986 (lpi_session_date NOT between l_link.link_start and l_link.link_end)
987 or
988 -- or if the link and assignment never overlap
989 NOT (l_link.link_end >= l_assignment.asgt_start
990 and l_link.link_start <= l_assignment.asgt_end)
991 then
992 --
993 raise no_current_eligibility;
994 --
995 end if;
996 --
997 if g_debug then
998 hr_utility.set_location(l_procedure_name,3);
999 end if;
1000 --
1001 -- Check that the parameters are valid (doing it here allows us to
1002 -- encompass a check that the cursors returned values correctly)
1003 --
1004 hr_general.assert_condition (all_parameters_are_valid
1005 and l_assignment.asgt_start is not null -- p_assignment_id is a valid row
1006 and l_link.link_start is not null -- p_element_link_id is a valid row
1007 );
1008 --
1009 if not
1010 (p_session_date between l_assignment.asgt_start and l_assignment.asgt_end)
1011 then
1012 --
1013 -- Assignment does not exist at session date, or the user does not have
1014 -- the appropriate privileges to edit the assignment as at the session
1015 -- date, raise an error
1016 --
1017 if g_debug then
1018 hr_utility.set_location(l_procedure_name,4);
1019 end if;
1020 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1021 hr_utility.set_message_token('PROCEDURE',
1022 'hr_entry.get_eligibility_period');
1023 hr_utility.set_message_token('STEP','4');
1024 hr_utility.raise_error;
1025 --
1026 end if;
1027 --
1028 if g_debug then
1029 hr_utility.set_location(l_procedure_name,5);
1030 end if;
1031 --
1032 -- Get the minimum date on which the assignment is eligible for the
1033 -- link in an unbroken period encompassing the session date. The cursor
1034 -- looks for the latest date prior to the session date on which there is
1035 -- NO eligibility for the link, then adds one to that date. This is done to
1036 -- cater for the following situation:
1037 --
1038 -- Asgt +---------+---------------+--------+-------------->
1039 -- Link |-------------------------------------------->
1040 --
1041 -- Eligible? |----YES--------| NO |-----YES------>
1042 --
1043 -- Session date X
1044 --
1045 -- Ref Points A B
1046 --
1047 -- If we selected the minimum start date on which the assignment
1048 -- IS eligible for the link, we would not take account of the gap
1049 -- in eligibility which occurs prior to the session date. In other
1050 -- words, we would get the date at point A instead of at point B,
1051 -- which is the correct date. If the session date is such that
1052 -- there is no row prior to the session date that is not eligible
1053 -- for the link, then we must take the start date of the assignment
1054 -- as the returned date. However, also remember that the link may
1055 -- start between the returned date and the session date so use
1056 -- the greater of these dates as the minimum eligibility date.
1057 --
1058 -- We return all date track assignment pieces
1059 -- backwards in time, looking for the first
1060 -- piece that is not eligible. This gives us
1061 -- our minimum eligibility date.
1062 --
1063 -- Bugfix 5135065
1064 -- Initialize time period start and end dates
1065 --
1066 -- NOTE:
1067 -- The time period dates should only have been passed in from chk_element_-
1068 -- entry_main where they will have been derived for a non-recurring
1069 -- entry, or an equivalent entry type that behaves like a non-recurring
1070 -- entry, e.g. a retropay entry, and hence will represent the start and end
1071 -- dates of the payroll period. The time period dates should be null in all
1072 -- other cases, and will be initialized to appropriate start of time/end of
1073 -- time values below.
1074 --
1075 -- We want to consider the time period start and end dates when dealing
1076 -- specifically with non-recurring entries in order to reduce the number of
1077 -- 'assignment pieces' that need to be examined and hence reduce the number
1078 -- of calls to the expensive assignment_eligibile_for_link procedure. For
1079 -- example, we are not interested in assignment pieces that end before the
1080 -- start date of the time period, or that begin after the end date of the
1081 -- time period, since non-recurring entries CANNOT exist beyond the time
1082 -- period they are created in.
1083 --
1084 -- Default the time period start date to the 'start of time' if null...
1085 l_time_period_start_date := nvl(p_time_period_start_date, hr_general.start_of_time);
1086 -- Default the time period end date to the 'end of time' if null...
1087 l_time_period_end_date := nvl(p_time_period_end_date, hr_general.end_of_time);
1088 --
1089 if g_debug then
1090 hr_utility.trace(' l_time_period_start_date: '||to_char(l_time_period_start_date,'DD-MON-YYYY'));
1091 hr_utility.trace(' l_time_period_end_date: '||to_char(l_time_period_end_date,'DD-MON-YYYY'));
1092 end if;
1093 -- Bugfix 5135065
1094 -- We are not interested in assignment pieces that end before the beginning
1095 -- of the time period, so we pass the time period start date to csr_minimum
1096 for c1rec in csr_minimum(p_assignment_id, lpi_session_date, l_time_period_start_date) loop
1097 if(hr_entry.assignment_eligible_for_link(
1098 p_assignment_id,
1099 p_element_link_id,
1100 least (c1rec.effective_end_date, l_link.link_end),
1101 p_creator_type) = 'N')
1102 then
1103 -- As soon as we have found an assignment row
1104 -- that exists but is not eligible, we set
1105 -- the minimum date and exit the loop.
1106 P_MIN_ELIGIBILITY_DATE := greatest ((c1rec.effective_end_date + 1),
1107 l_link.link_start);
1108 exit;
1109 end if;
1110 end loop;
1111
1112 if g_debug then
1113 hr_utility.trace(' After csr_minimum, P_MIN_ELIGIBILITY_DATE : '|| P_MIN_ELIGIBILITY_DATE);
1114 end if;
1115 --
1116 -- Bugfix 4114282, handle the potentially null min eligibility date...
1117 --
1118 if(P_MIN_ELIGIBILITY_DATE is null) then
1119 P_MIN_ELIGIBILITY_DATE := greatest (l_assignment.asgt_start, l_link.link_start);
1120 end if;
1121 --
1122 if g_debug then
1123 hr_utility.trace (' P_MIN_ELIGIBILITY_DATE = '||to_char (P_MIN_ELIGIBILITY_DATE));
1124 end if;
1125 if P_MIN_ELIGIBILITY_DATE > lpi_session_date then
1126 raise no_current_eligibility;
1127 end if;
1128 --
1129 if g_debug then
1130 hr_utility.set_location(l_procedure_name,6);
1131 end if;
1132 --
1133 -- Get the maximum date on which the assignment is eligible for the
1134 -- link in an unbroken period encompassing the session date. See above
1135 -- for an explanation of the approach taken in this cursor; obviously we
1136 -- are looking for the other end of the eligibility period. The two cursors
1137 -- cannot be combined because we need to reverse the session date restriction.
1138 --
1139
1140 --
1141 -- We return all date track assignment pieces
1142 -- forwards in time, looking for the first
1143 -- piece that is not eligible. This gives us
1144 -- our maximum eligibility date.
1145
1146 -- Bugfix 5135065
1147 --
1148 -- NOTE:
1149 -- We are not interested in assignment pieces that start after the end
1150 -- of the time period, so we pass the time period end date to csr_maximum.
1151 -- This should reduce the number of times we need to call the expensive
1152 -- assignment_eligible_for_link function.
1153 -- See above explanation.
1154 for c1rec in csr_maximum(p_assignment_id, lpi_session_date, l_time_period_end_date) loop
1155 if(hr_entry.assignment_eligible_for_link(
1156 p_assignment_id,
1157 p_element_link_id,
1158 greatest (c1rec.effective_start_date, l_link.link_start),
1159 p_creator_type) = 'N')
1160 then
1161 -- As soon as we have found an assignment row
1162 -- that exists but is not eligible, we set
1163 -- the minimum date and exit the loop.
1164 P_MAX_ELIGIBILITY_DATE := least ((c1rec.effective_start_date - 1),
1165 l_link.link_end);
1166
1167 -- Bug 14045109: For terminated assignment, max elig. date for entry should
1168 -- be set as per termination rule of element since for element links and
1169 -- assignments with pay basis, asg_elig_for_link fails
1170
1171 begin
1172 -- Get termination dates for employee
1173 select ser.*
1174 into l_service_rec
1175 from per_periods_of_service ser
1176 where ser.person_id = (select distinct person_id
1177 from per_all_assignments_f
1178 where assignment_id = p_assignment_id)
1179 and (c1rec.effective_start_date - 1) between ser.date_start
1180 and nvl(ser.actual_termination_date, hr_general.end_of_time);
1181
1182 -- Get terminaation rule for element
1183 select pet.post_termination_rule
1184 into l_post_termination_rule
1185 from pay_element_types_f pet
1186 ,pay_element_links_f pel
1187 where pel.element_link_id = p_element_link_id
1188 and l_link.link_start between pel.effective_start_date
1189 and pel.effective_end_date
1190 and pel.element_type_id = pet.element_type_id
1191 and l_link.link_start between pet.effective_start_date
1192 and pet.effective_end_date;
1193
1194 l_eff_term_date := null;
1195
1196 if g_debug then
1197 hr_utility.trace ('ATD: '||l_service_rec.actual_termination_date);
1198 hr_utility.trace ('LSP: '||l_service_rec.last_standard_process_date);
1199 hr_utility.trace ('FPD: '||l_service_rec.final_process_date);
1200 end if;
1201
1202 -- if terminated employee, set element termination rule date as max elig date
1203 if (l_service_rec.actual_termination_date is not null or
1204 l_service_rec.final_process_date is not null or
1205 l_service_rec.last_standard_process_date is not null) then
1206
1207 select decode (l_post_termination_rule, 'A', l_service_rec.actual_termination_date
1208 , 'L', l_service_rec.last_standard_process_date
1209 , 'F', l_service_rec.final_process_date, null)
1210 into l_eff_term_date
1211 from dual;
1212
1213 if g_debug then
1214 hr_utility.trace ('Terminated Employee. l_eff_term_date = '||l_eff_term_date);
1215 end if;
1216
1217 end if;
1218
1219 if l_eff_term_date is not null then
1220 P_MAX_ELIGIBILITY_DATE := l_eff_term_date;
1221 end if;
1222
1223 exception
1224 when others then
1225 null;
1226 end;
1227 -- end of bug fix 14045109
1228
1229 exit;
1230 end if;
1231 end loop;
1232
1233 if g_debug then
1234 hr_utility.trace(' After csr_maximum, P_MAX_ELIGIBILITY_DATE : '|| P_MAX_ELIGIBILITY_DATE);
1235 end if;
1236
1237 -- If the max date is null, there were no rows returned.
1238 if(P_MAX_ELIGIBILITY_DATE is null) then
1239 P_MAX_ELIGIBILITY_DATE := least (l_assignment.asgt_end, l_link.link_end);
1240 end if;
1241
1242 --
1243 if g_debug then
1244 hr_utility.trace (' P_MAX_ELIGIBILITY_DATE = '||to_char (P_MAX_ELIGIBILITY_DATE));
1245 end if;
1246 if P_MAX_ELIGIBILITY_DATE < lpi_session_date then
1247 raise no_current_eligibility;
1248 end if;
1249 --
1250 if g_debug then
1251 hr_utility.set_location(l_procedure_name,7);
1252 end if;
1253 --
1254 exception
1255 --
1256 when no_current_eligibility
1257 then
1258 --
1259 -- Provide a helpful error message explaining which assignment
1260 -- and element failed, and on what date.
1261 --
1262 if g_debug then
1263 hr_utility.set_location (l_procedure_name,999);
1264 end if;
1265 --
1266 declare
1267 --
1268 cursor csr_error_element is
1269 --
1270 -- Get the name of the element for which there was no eligibility.
1271 -- NB We know p_element_link_id and lpi_session_date are valid because
1272 -- a value_error would have been raised by the assert_condition call
1273 -- in the main body code otherwise.
1274 --
1275 select elt_tl.element_name
1276 from pay_element_types_f_tl ELT_TL,
1277 pay_element_types_f ELT,
1278 pay_element_links_f LINK
1279 where elt.element_type_id = link.element_type_id
1280 and elt_tl.element_type_id = elt.element_type_id
1281 and P_ELEMENT_LINK_ID = link.element_link_id
1282 and userenv('LANG') = elt_tl.language
1283 and lpi_session_date between link.effective_start_date
1284 and link.effective_end_date
1285 and lpi_session_date between elt.effective_start_date
1286 and elt.effective_end_date;
1287 --
1288 cursor csr_error_assignment is
1289 --
1290 -- Get the number of the assignment which was not eligible.
1291 -- NB We know p_assignment_id and lpi_session_date are valid because
1292 -- a value_error would have been raised by the assert_condition call
1293 -- in the main body code otherwise.
1294 --
1295 select assignment_number
1296 from per_assignments_f
1297 where assignment_id = P_ASSIGNMENT_ID
1298 and lpi_session_date between effective_start_date
1299 and effective_end_date;
1300 --
1301 l_assignment csr_error_assignment%rowtype;
1302 l_element csr_error_element%rowtype;
1303 --
1304 begin
1305 --
1306 open csr_error_element;
1307 fetch csr_error_element into l_element;
1308 close csr_error_element;
1309 --
1310 open csr_error_assignment;
1311 fetch csr_error_assignment into l_assignment;
1312 close csr_error_assignment;
1313 --
1314 hr_utility.set_message(801, 'HR_51271_ELE_NOT_ELIGIBLE');
1315 hr_utility.set_message_token ('ELEMENT_NAME', l_element.element_name);
1316 hr_utility.set_message_token ('ASSIGNMENT_NUMBER', l_assignment.assignment_number);
1317 hr_utility.set_message_token ('SESSION_DATE', to_char (lpi_session_date));
1318 hr_utility.raise_error;
1319 --
1320 end;
1321 --
1322 end get_eligibility_period;
1323 --------------------------------------------------------------------------------
1324 -- NAME
1325 -- hr_entry.get_eligibility_period
1326 --
1327 -- DESCRIPTION
1328 -- Bugfix 5135065
1329 -- Overloaded version provided for backwards compatibility. Refer to new
1330 -- get_eligibility_period procedure for description.
1331 --
1332 procedure get_eligibility_period (
1333 p_assignment_id in number,
1334 p_element_link_id in number,
1335 p_session_date in date,
1336 p_min_eligibility_date in out nocopy date,
1337 p_max_eligibility_date in out nocopy date
1338 ) is
1339 begin
1340 --
1341 -- Call the new get_eligibility_period procedure, passing in null
1342 -- for the time period start and end dates
1343 --
1344 get_eligibility_period (
1345 p_assignment_id => p_assignment_id
1346 ,p_element_link_id => p_element_link_id
1347 ,p_session_date => p_session_date
1348 -- Bugfix 5135065
1349 -- Set time period start and end dates to null
1350 ,p_creator_type => null
1351 ,p_time_period_start_date => null
1352 ,p_time_period_end_date => null
1353 ,p_min_eligibility_date => p_min_eligibility_date
1354 ,p_max_eligibility_date => p_max_eligibility_date
1355 );
1356 --
1357 end get_eligibility_period;
1358 --------------------------------------------------------------------------------
1359 -- NAME
1360 -- hr_entry.entry_asg_pay_link_dates
1361 --
1362 -- DESCRIPTION
1363 -- This procedure returns the min(effective_start/end_date) for a specified
1364 -- element link and payroll. Also, if the specified employee assignment has
1365 -- been terminated the element termination date as of the termination rule is
1366 -- returned.
1367 --
1368 procedure entry_asg_pay_link_dates (
1369 p_assignment_id in number,
1370 p_element_link_id in number,
1371 p_session_date in date,
1372 p_element_term_rule_date out nocopy date,
1373 p_element_link_start_date out nocopy date,
1374 p_element_link_end_date out nocopy date,
1375 p_payroll_start_date out nocopy date,
1376 p_payroll_end_date out nocopy date,
1377 p_entry_mode in boolean default true
1378 )
1379 is
1380 --
1381 v_element_term_rule_date date;
1382 v_element_link_start_date date;
1383 v_element_link_end_date date;
1384 v_payroll_start_date date;
1385 v_payroll_end_date date;
1386 v_asg_term_date date;
1387 v_post_termination_rule varchar2(30);
1388 v_processing_type varchar2(30);
1389 v_period_of_service_id number;
1390 -- Bugfix 5616075
1391 v_actual_termination_date date;
1392 v_last_standard_process_date date;
1393 v_final_process_date date;
1394 v_employee_terminated boolean := false;
1395 v_orig_term_rule_date_func varchar2(30); -- value of 'EE_ORIG_TERM_RULE_DATE_FUNC' action parameter
1396 v_action_param_found boolean;
1397 --
1398 v_primary_flag varchar2(30);
1399 --
1400 -- Procedure Parameter Name Description
1401 -- ========================== ==================================================
1402 -- p_assignment_id Holds the employee assignment id.
1403 -- p_element_link_id Holds the element link id.
1404 -- p_session_date Holds the current session effective date.
1405 -- p_element_term_rule_date Holds the element termination date.
1406 -- p_element_link_start_date Holds the element link start date.
1407 -- p_element_link_end_date Holds the element link end date.
1408 -- p_payroll_start_date Holds the payroll start date.
1409 -- p_payroll_end_date Holds the payroll end date.
1410 --
1411 -- Local Parameter Name Description
1412 -- ========================== ==================================================
1413 -- v_element_term_rule_date Holds the element termination date.
1414 -- v_element_link_start_date Holds the element link start date.
1415 -- v_element_link_end_date Holds the element link end date.
1416 -- v_payroll_start_date Holds the payroll start date.
1417 -- v_payroll_end_date Holds the payroll end date.
1418 --
1419 begin
1420 g_debug := hr_utility.debug_enabled;
1421 --
1422 -- Ensure all mandatory parameters exist.
1423 --
1424 hr_general.assert_condition (p_assignment_id is not null
1425 and p_element_link_id is not null
1426 and p_session_date is not null
1427 and p_session_date = trunc (p_session_date));
1428 --
1429 if g_debug then
1430 --
1431 hr_utility.trace('begin hr_entry.entry_asg_pay_link_dates');
1432 hr_utility.trace(' p_session_date:' || To_Char(p_session_date,'DD-MON-YYYY'));
1433 hr_utility.trace(' p_assignment_id:' || p_assignment_id);
1434 hr_utility.trace(' p_element_link_id:' || p_element_link_id);
1435 --
1436 end if;
1437 --
1438 -- Select the element termination processing rule, assignment period of
1439 -- service and assignment primary flag
1440 --
1441 if g_debug then
1442 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 1);
1443 end if;
1444 --
1445 begin
1446 --
1447 select asg.period_of_service_id,
1448 asg.primary_flag,
1449 -- Bugfix 5616075
1450 pos.actual_termination_date,
1451 pos.last_standard_process_date,
1452 pos.final_process_date
1453 into v_period_of_service_id,
1454 v_primary_flag,
1455 v_actual_termination_date,
1456 v_last_standard_process_date,
1457 v_final_process_date
1458 from per_assignments_f asg,
1459 per_periods_of_service pos
1460 where asg.assignment_id = p_assignment_id
1461 and asg.period_of_service_id = pos.period_of_service_id (+)
1462 and p_session_date between asg.effective_start_date
1463 and asg.effective_end_date;
1464
1465 if g_debug then
1466 hr_utility.trace(' v_period_of_service_id : '|| v_period_of_service_id);
1467 hr_utility.trace(' v_primary_flag : '|| v_primary_flag);
1468 hr_utility.trace(' v_actual_termination_date : '|| v_actual_termination_date);
1469 hr_utility.trace(' v_last_standard_process_date : '|| v_last_standard_process_date);
1470 hr_utility.trace(' v_final_process_date : '|| v_final_process_date);
1471 end if;
1472 --
1473 select pet.post_termination_rule,
1474 pet.processing_type
1475 into v_post_termination_rule,
1476 v_processing_type
1477 from pay_element_types_f pet,
1478 pay_element_links_f pel
1479 where p_session_date between pel.effective_start_date
1480 and pel.effective_end_date
1481 and pel.element_link_id = p_element_link_id
1482 and pet.element_type_id = pel.element_type_id
1483 and p_session_date between pet.effective_start_date
1484 and pet.effective_end_date;
1485 --
1486 exception
1487 --
1488 when NO_DATA_FOUND then
1489 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1490 hr_utility.set_message_token('PROCEDURE',
1491 'hr_entry.entry_asg_pay_link_dates');
1492 hr_utility.set_message_token('STEP','2');
1493 end;
1494 --
1495 if g_debug then
1496 --
1497 hr_utility.trace(' v_post_termination_rule :' || v_post_termination_rule);
1498 hr_utility.trace(' v_processing_type :' || v_processing_type);
1499 --
1500 -- Actual termination date...
1501 if v_actual_termination_date is not null then
1502 hr_utility.trace(' v_actual_termination_date>' ||
1503 to_char(v_actual_termination_date, 'DD-MON-YYYY') || '<');
1504 else
1505 hr_utility.trace(' v_actual_termination_date><');
1506 end if;
1507 --
1508 -- Last standard process date...
1509 if v_last_standard_process_date is not null then
1510 hr_utility.trace(' v_last_standard_process_date>' ||
1511 to_char(v_last_standard_process_date, 'DD-MON-YYYY') || '<');
1512 else
1513 hr_utility.trace(' v_last_standard_process_date><');
1514 end if;
1515 --
1516 -- Final process date...
1517 if v_final_process_date is not null then
1518 hr_utility.trace(' v_final_process_date>' ||
1519 to_char(v_final_process_date, 'DD-MON-YYYY') || '<');
1520 else
1521 hr_utility.trace(' v_final_process_date><');
1522 end if;
1523 --
1524 end if; -- if g_debug.
1525 --
1526 -- Bugfix 5616075
1527 -- Get value of EE_ORIG_TERM_RULE_DATE_FUNC action parameter
1528 --
1529 pay_core_utils.get_action_parameter (
1530 'EE_ORIG_TERM_RULE_DATE_FUNC',
1531 v_orig_term_rule_date_func,
1532 v_action_param_found
1533 );
1534 --
1535 if not v_action_param_found then
1536 -- Default to 'N' so we use the new behaviour
1537 v_orig_term_rule_date_func := 'N';
1538 end if;
1539 --
1540 if g_debug then
1541 hr_utility.trace(' v_orig_term_rule_date_func: '||v_orig_term_rule_date_func);
1542 end if;
1543 --
1544 v_employee_terminated :=
1545 (v_actual_termination_date is not null or
1546 v_last_standard_process_date is not null or
1547 v_final_process_date is not null);
1548 --
1549 -- Original behaviour:
1550 -- -------------------
1551 -- If the employee assignment is primary then check to see if terminated then
1552 -- select the correct element termination date.
1553 --
1554 -- New behaviour:
1555 -- --------------
1556 -- Since bug 5616075...
1557 -- Treat primary and secondary assignments the same when the employee is
1558 -- terminated.
1559 --
1560 if (v_orig_term_rule_date_func = 'N' and v_employee_terminated) or -- New behaviour
1561 (v_orig_term_rule_date_func = 'Y' and v_primary_flag = 'Y') then -- Old behaviour
1562 --
1563 if g_debug then
1564 hr_utility.trace(' employee terminated');
1565 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 2);
1566 hr_utility.trace(' v_post_termination_rule :' || v_post_termination_rule);
1567 end if;
1568 --
1569 -- Get the termination rule date
1570 --
1571 if v_post_termination_rule = 'L' then
1572 v_element_term_rule_date := nvl(v_last_standard_process_date, hr_general.end_of_time);
1573 elsif v_post_termination_rule = 'F' then
1574 v_element_term_rule_date := nvl(v_final_process_date, hr_general.end_of_time);
1575 else
1576 v_element_term_rule_date := nvl(v_actual_termination_date, hr_general.end_of_time);
1577 end if;
1578 --
1579 -- Bug 382760. If termination rule is Actual Termination or Last Standard
1580 -- Process and element is Nonrecurring then entries close down on the last day
1581 -- of the pay period. Only recurring entries close down on the termination
1582 -- date.
1583 --
1584 if v_post_termination_rule in ('A','L') and v_processing_type = 'N' then
1585 --
1586 begin
1587 --
1588 -- Bugfix 5487637
1589 -- Get the end date of the period in which the element termination
1590 -- rule date occurs...
1591 --
1592 select ptp.end_date
1593 into v_element_term_rule_date
1594 from per_all_assignments_f asg,
1595 per_time_periods ptp
1596 where asg.assignment_id = p_assignment_id
1597 and p_session_date between asg.effective_start_date and asg.effective_end_date
1598 and asg.payroll_id = ptp.payroll_id
1599 and v_element_term_rule_date between ptp.start_date and ptp.end_date;
1600 --
1601 exception
1602 --
1603 when no_data_found then
1604 -- It's possible a NO_DATA_FOUND error was raised by the period end
1605 -- date fetch, in which case we want to retain the (unmodified)
1606 -- v_element_term_rule_date. A 'NVL' achieves this for us...
1607 v_element_term_rule_date := nvl(v_element_term_rule_date,hr_general.end_of_time);
1608 --
1609 end;
1610 --
1611 end if;
1612 --
1613 -- Bug 7555483
1614 -- A primary assignment can become secondary in future and can get terminated afterwards.
1615 -- If an Element Entry is made in period when it was primary ,Entry End date was not populating
1616 -- as the code checks the future assignmnet status of TERM/END only for secondary assignment.
1617 -- Commented the check "elsif v_primary_flag <> 'Y'" and the else part
1618 -- Now code will check for future TERM/END status for both primary and secondary assignment .
1619
1620 /* Commenting for Bug 7555483 elsif v_primary_flag <> 'Y' then */
1621 else
1622 --
1623 -- Logic for non-terminated, primary/Secondary assignment...
1624 --
1625 if g_debug then
1626 hr_utility.trace(' employee NOT terminated');
1627 hr_utility.trace(' primary/Secondary assignment');
1628 end if;
1629 --
1630 -- The assignment is NOT a PRIMARY assignment therefore we need to check
1631 -- if the current or future assignment has an assignment status of
1632 -- 'TERM_ASSIGN' or 'END_ASSIGN'
1633 --
1634 -- First we need to check if the current or future assignment rows have
1635 -- a 'TERM_ASSIGN' status.
1636 --
1637 if g_debug then
1638 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 3);
1639 end if;
1640 begin
1641 select min(asg.effective_start_date)
1642 into v_asg_term_date
1643 from per_assignments_f asg,
1644 per_assignment_status_types ast
1645 where asg.assignment_id = p_assignment_id
1646 and asg.effective_end_date >= p_session_date
1647 and asg.assignment_status_type_id = ast.assignment_status_type_id
1648 and ast.per_system_status = 'TERM_ASSIGN';
1649 end;
1650 --
1651 -- If the employee assignment does NOT have a 'TERM_ASSIGN' then we must check
1652 -- to see if an 'END' exists.
1653 --
1654 if v_asg_term_date is null then
1655 --
1656 if g_debug then
1657 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 4);
1658 end if;
1659 --
1660 begin
1661 select asg.effective_start_date
1662 into v_asg_term_date
1663 from per_assignments_f asg,
1664 per_assignment_status_types ast
1665 where asg.assignment_id = p_assignment_id
1666 and asg.effective_end_date >= p_session_date
1667 and asg.assignment_status_type_id = ast.assignment_status_type_id
1668 and ast.per_system_status = 'END';
1669 exception
1670 when NO_DATA_FOUND then NULL;
1671 end;
1672 --
1673 -- As the employee assignment does have the 'END' status the element
1674 -- termination processing rule will always be the effective_start_date.
1675 --
1676 if v_asg_term_date is null then
1677 v_element_term_rule_date := hr_general.end_of_time;
1678 else
1679 v_element_term_rule_date := v_asg_term_date;
1680 end if;
1681 --
1682 -- As the employee assignment does have the 'TERM_ASSIGN' status we must
1683 -- derive the element termination processing rule from the following rules:
1684 -- If ((termination processing rule = 'A') or
1685 -- (termination processing rule = 'L' and
1686 -- assignment is not to a payroll) then
1687 -- termination processing date = the min(asg.effective_start_date)
1688 -- ElsIf (the termination processing rule = 'L' and
1689 -- assignment is to payroll) then
1690 -- termination processing date = last date of current period as of
1691 -- the min(asg.effective_start_date)
1692 -- ElsIf the termination processing rule = 'F' then
1693 -- termination processing date = max(aasg.effective_end_date)
1694 -- End If
1695 --
1696 else
1697 --
1698 if (v_post_termination_rule = 'A') then
1699 --
1700 -- Bugfix 4710356
1701 -- Set the term rule end date to the last day of the payroll
1702 -- period for non-recurring entries. This will allow the
1703 -- entry to be created for the entire payroll period, even
1704 -- though the assignment is 'terminated' in the middle of
1705 -- the period. This new behaviour is consistent with the rules
1706 -- for creating non-recurring entries against terminated
1707 -- primary assignments.
1708 --
1709 if v_processing_type = 'N' then
1710 begin
1711 select ptp.end_date
1712 into v_element_term_rule_date
1713 from per_time_periods ptp,
1714 per_assignments_f asg
1715 where asg.assignment_id = p_assignment_id
1716 and v_asg_term_date between asg.effective_start_date and asg.effective_end_date
1717 and asg.payroll_id is not null
1718 and ptp.payroll_id = asg.payroll_id
1719 and v_asg_term_date between ptp.start_date and ptp.end_date;
1720 exception
1721 when NO_DATA_FOUND then
1722 v_element_term_rule_date := v_asg_term_date - 1;
1723 end;
1724 else
1725 v_element_term_rule_date := v_asg_term_date - 1;
1726 end if;
1727 elsif (v_post_termination_rule = 'F') then
1728 if g_debug then
1729 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 5);
1730 end if;
1731 begin
1732 select max(asg.effective_end_date)
1733 into v_element_term_rule_date
1734 from per_assignments_f asg
1735 where asg.assignment_id = p_assignment_id
1736 and asg.effective_start_date >= v_asg_term_date;
1737 end;
1738 elsif (v_post_termination_rule = 'L') then
1739 if g_debug then
1740 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 6);
1741 end if;
1742 /*
1743 begin
1744 select ptp.end_date
1745 into v_element_term_rule_date
1746 from per_time_periods ptp,
1747 per_assignments_f asg
1748 where asg.assignment_id = p_assignment_id
1749 and v_asg_term_date
1750 between asg.effective_start_date
1751 and asg.effective_end_date
1752 and asg.payroll_id is not null
1753 and ptp.payroll_id = asg.payroll_id
1754 and v_asg_term_date
1755 between ptp.start_date
1756 and ptp.end_date;
1757 exception
1758 when NO_DATA_FOUND then
1759 v_element_term_rule_date := v_asg_term_date - 1;
1760 end;
1761 */
1762 --
1763 -- bugfix 1010165,
1764 -- when creating ALUs on TERM assignments, periodicity is not a concern,
1765 -- previously the code was trying to get the beginning of the TERM assignment,
1766 -- find the period effective at this date, and return the end date of this
1767 -- period as the element termination rule,
1768 -- this could be less the ED (the ESD of the EL), thus error
1769 -- HR_6370_ELE_ENTRY_NO_TERM was returned,
1770 --
1771 -- for Last Standard Process entries on TERM assignments, periods do not
1772 -- apply, the entry can span out to the EED of the TERM assignment,
1773 -- nb. the EED of the ALU is limited by the EED of the EL and TERM assignment
1774 --
1775 /*v_element_term_rule_date := v_asg_term_date; Added for Bug 8485543 and commented below*/
1776
1777 begin
1778 select max(asg.effective_end_date)
1779 into v_element_term_rule_date
1780 from per_assignments_f asg
1781 where asg.assignment_id = p_assignment_id
1782 and asg.effective_start_date >= v_asg_term_date;
1783 end;
1784 --
1785 end if;
1786 --
1787 end if;
1788 --
1789 /*Commented for Bug 7555483 Begin comment */
1790 -- else
1791 --
1792 -- Logic for non-terminated, primary assignment...
1793 --
1794 -- if g_debug then
1795 -- hr_utility.trace(' employee NOT terminated');
1796 -- hr_utility.trace(' PRIMARY assignment');
1797 -- end if;
1798 --
1799 -- v_element_term_rule_date := hr_general.end_of_time;
1800 --
1801 /*End Comment*/
1802 end if;
1803 --
1804 if g_debug then
1805 hr_utility.trace(' v_element_term_rule_date: ' || to_char(v_element_term_rule_date,'DD-MON-YYYY'));
1806 end if;
1807 --
1808 -- check to see if the v_element_term_rule_date is being set to before
1809 -- the session date.
1810 --
1811 if ((v_element_term_rule_date <> hr_general.end_of_time) and
1812 (v_element_term_rule_date < p_session_date) and p_entry_mode) then
1813 --
1814 hr_utility.set_message(801, 'HR_6370_ELE_ENTRY_NO_TERM');
1815 hr_utility.raise_error;
1816 --
1817 end if;
1818 --
1819 -- Select the minimum and maximum element link dates.
1820 --
1821 if g_debug then
1822 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 7);
1823 end if;
1824 --
1825 begin
1826 select min(pel.effective_start_date),
1827 max(pel.effective_end_date)
1828 into v_element_link_start_date,
1829 v_element_link_end_date
1830 from pay_element_links_f pel
1831 where pel.element_link_id = p_element_link_id;
1832 end;
1833 --
1834 if (v_element_link_start_date > p_session_date) or
1835 (v_element_link_end_date < p_session_date) then
1836 hr_utility.set_message(801, 'HR_6132_ELE_ENTRY_LINK_MISSING');
1837 hr_utility.raise_error;
1838 end if;
1839 --
1840 -- If the assignment is to a payroll then,
1841 -- we must select the minimum and maximum effective dates of the payroll.
1842 --
1843 if g_debug then
1844 hr_utility.set_location('hr_entry.entry_asg_pay_link_dates', 8);
1845 end if;
1846 begin
1847 select min(pay.effective_start_date),
1848 max(pay.effective_end_date)
1849 into v_payroll_start_date,
1850 v_payroll_end_date
1851 from pay_all_payrolls_f pay,
1852 per_all_assignments_f asg
1853 where p_session_date
1854 between asg.effective_start_date
1855 and asg.effective_end_date
1856 and asg.assignment_id = p_assignment_id
1857 and asg.payroll_id is not null
1858 and pay.payroll_id = asg.payroll_id;
1859 exception
1860 when NO_DATA_FOUND then null;
1861 end;
1862 --
1863 if ((v_payroll_start_date is not null and
1864 v_payroll_end_date is not null) and
1865 (v_payroll_start_date > p_session_date or
1866 v_payroll_end_date < p_session_date)) then
1867 hr_utility.set_message(801, 'HR_6399_ELE_ENTRY_NO_PAYROLL');
1868 hr_utility.raise_error;
1869 end if;
1870 --
1871 -- Set values to be returned by procedure.
1872 --
1873 p_element_term_rule_date := v_element_term_rule_date;
1874 p_element_link_start_date := v_element_link_start_date;
1875 p_element_link_end_date := v_element_link_end_date;
1876 p_payroll_start_date := v_payroll_start_date;
1877 p_payroll_end_date := v_payroll_end_date;
1878 --
1879 if g_debug then
1880 hr_utility.trace(' end hr_entry.entry_asg_pay_link_dates');
1881 end if;
1882 --
1883 end entry_asg_pay_link_dates;
1884 --
1885 -- NAME
1886 -- hr_entry.recurring_entry_end_date
1887 --
1888 -- DESCRIPTION
1889 -- This function is used to return the valid effective end of a recurring entry.
1890 -- The effective end date is determined by selecting the least date of:
1891 -- 1) If the p_overlap_chk is set to 'Y' then we must check to see if any
1892 -- recurring entries of the same link for the assignment exist in the future.
1893 -- If yes, then we must take the min(effective_start_date) -1
1894 -- 2) Selecting the minimum (effective_start_date - 1) from the
1895 -- employee assignment
1896 -- where the employee assignment is NOT eligible to the element.
1897 -- e.g.
1898 -- A B
1899 -- |---------------------------|-------|---------|-----------> assignment
1900 -- |---------------------------------------------------------> element link
1901 -- Between positions A, B the assignment has been updated and is not
1902 -- eligible for the element link. The employee assignment is only
1903 -- eligible to the element link upto position A and past position B.
1904 -- Therefore, it the session date was before position A the date returned
1905 -- would be (position A effective_start_date - 1).
1906 -- 3) Selecting the termination processing rule end date if the current or
1907 -- future employee assignment has been terminated.
1908 -- 4) Selecting the effective_end_date of the element link.
1909 --
1910 function recurring_entry_end_date
1911 (
1912 p_assignment_id in number,
1913 p_element_link_id in number,
1914 p_session_date in date,
1915 p_overlap_chk in varchar2 default 'Y',
1916 p_mult_entries_allowed_flag in varchar2,
1917 p_element_entry_id in number,
1918 p_original_entry_id in number
1919 ) return date is
1920 --
1921 v_out_date_not_required date;
1922 v_asg_max_eligibility_date date;
1923 v_element_term_rule_date date;
1924 v_element_link_end_date date;
1925 v_recurring_end_date date;
1926 v_min_max_all varchar2(3);
1927 v_future_recurring_end_date date;
1928 v_error_flag varchar2(1);
1929 v_current_effective_end_date date;
1930 --
1931 -- Function Parameter Name Description
1932 -- ========================== ==================================================
1933 -- p_assignment_id Holds the employee assignment id.
1934 -- p_element_link_id Holds the element link id.
1935 -- p_session_date Holds the current session effective date.
1936 --
1937 -- Local Parameter Name Description
1938 -- ========================== ==================================================
1939 -- v_out_date_not_required Holds a returned out date from sub-procedures
1940 -- which is not required.
1941 -- v_asg_max_eligibility_date Holds the maximum assignment eligibility date.
1942 -- v_element_term_rule_date Holds the element's termination processing rule
1943 -- date.
1944 -- v_element_link_end_date Holds the maximum effective_end_date of the
1945 -- specified element link.
1946 -- v_recurring_end_date Holds the end date of the recurring entry which is
1947 -- returned by the function.
1948 --
1949 begin
1950 g_debug := hr_utility.debug_enabled;
1951
1952 if g_debug then
1953 hr_utility.trace('In hr_entry.recurring_entry_end_date');
1954 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
1955 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
1956 hr_utility.trace(' p_session_date : '|| p_session_date);
1957 hr_utility.trace(' p_overlap_chk : '|| p_overlap_chk);
1958 hr_utility.trace(' p_mult_entries_allowed_flag : '|| p_mult_entries_allowed_flag);
1959 hr_utility.trace(' p_element_entry_id : '|| p_element_entry_id);
1960 hr_utility.trace(' p_original_entry_id : '|| p_original_entry_id);
1961 end if;
1962 --
1963 -- Initialiize local parameters
1964 --
1965 v_future_recurring_end_date := hr_general.end_of_time;
1966 v_error_flag := 'N';
1967 --
1968 -- Ensure all mandatory parameters exist.
1969 --
1970 hr_general.assert_condition (p_assignment_id is not null
1971 and p_element_link_id is not null
1972 and p_session_date is not null
1973 and p_session_date = trunc (p_session_date));
1974 --
1975 -- If the element_entry_id exists then we must be doing a date-effective
1976 -- delete next/future changes therefore set the current_effective_end_date
1977 --
1978 if p_element_entry_id is not null then
1979 if g_debug then
1980 hr_utility.set_location('hr_entry.recurring_entry_end_date', 0);
1981 end if;
1982 begin
1983 select e.effective_end_date
1984 into v_current_effective_end_date
1985 from pay_element_entries_f e
1986 where e.element_entry_id = p_element_entry_id
1987 and p_session_date
1988 between e.effective_start_date
1989 and e.effective_end_date;
1990 exception
1991 when NO_DATA_FOUND then NULL;
1992 end;
1993 end if;
1994
1995 hr_utility.trace(' v_current_effective_end_date : '|| v_current_effective_end_date);
1996 --
1997 -- If the p_overlap_chk is set to 'Y' then we must check to see if any
1998 -- recurring entries of the same link for the assignment exist in the future.
1999 -- If yes, then we must take the min(effective_start_date) -1
2000 --
2001 if upper (p_overlap_chk) = 'Y' then
2002 if g_debug then
2003 hr_utility.set_location('hr_entry.recurring_entry_end_date', 1);
2004 end if;
2005 begin
2006 -- INDEX hint added following NHS project recommendation
2007 select /*+ INDEX(pee, pay_element_entries_f_n51) */
2008 nvl(min(pee.effective_start_date) - 1, hr_general.end_of_time)
2009 into v_future_recurring_end_date
2010 from pay_element_entries_f pee
2011 where pee.entry_type = 'E'
2012 and pee.assignment_id = p_assignment_id
2013 and pee.element_link_id = p_element_link_id
2014 and pee.element_entry_id <> nvl(p_element_entry_id,0)
2015 and ((p_mult_entries_allowed_flag = 'Y' and
2016 nvl(pee.original_entry_id,pee.element_entry_id) =
2017 nvl(p_original_entry_id,p_element_entry_id))
2018 or (p_mult_entries_allowed_flag = 'N'))
2019 and pee.effective_start_date > p_session_date;
2020 end;
2021
2022 hr_utility.trace(' v_future_recurring_end_date : '|| v_future_recurring_end_date);
2023 --
2024 -- If we are doing a date-effective delete then we must ensure that the
2025 -- date returned is not the same as the current effective_end_date.
2026 --
2027 if ((v_current_effective_end_date is not null and
2028 v_current_effective_end_date = v_future_recurring_end_date) or
2029 v_future_recurring_end_date < p_session_date) then
2030 hr_utility.set_message(801, 'HR_7699_ELE_ENTRY_REC_EXISTS');
2031 hr_utility.raise_error;
2032 end if;
2033 --
2034 end if;
2035 --
2036 -- Bug 5867658.
2037 -- Ensure assignment is visible to (possibly secure) user before continuing.
2038 -- If the assignment is not visible then we want to raise a helpful message.
2039 -- This might happen when a secure user is hiring an applicant and the
2040 -- appropriate security has not yet been setup on the assignment, previously
2041 -- an obsure error message (ORA-06502: PL/SQL: Numeric or Value Error ) was
2042 -- being raised.
2043 --
2044 chk_asg_visible(p_assignment_id, p_session_date);
2045 --
2046 -- Get the end date of the link.
2047 --
2048 hr_entry.entry_asg_pay_link_dates (p_assignment_id,
2049 p_element_link_id,
2050 p_session_date,
2051 v_element_term_rule_date,
2052 v_out_date_not_required,
2053 v_element_link_end_date,
2054 v_out_date_not_required,
2055 v_out_date_not_required);
2056 --
2057 --
2058 -- Find the minimum assignment (effective_start_date - 1) when the current or
2059 -- future assignment changes is NOT eligible to the element link.
2060 --
2061 hr_entry.get_eligibility_period (p_assignment_id,
2062 p_element_link_id,
2063 p_session_date,
2064 v_out_date_not_required,
2065 v_asg_max_eligibility_date);
2066 --
2067 -- Now set the recurring end date to be returned.
2068 -- Note: We use the NVL function on v_payroll_end_date because if the
2069 -- assignment is not to a payroll then the v_payroll_end_date is
2070 -- going to be null.
2071 --
2072 v_recurring_end_date := least(v_asg_max_eligibility_date,
2073 v_element_term_rule_date,
2074 v_element_link_end_date,
2075 v_future_recurring_end_date);
2076 --
2077 -- If the v_recurring_end_date = v_current_effective_end_date then we know
2078 -- that the end date is trying to be set to the current effective end date.
2079 -- We must error, being specific as to why you cannot extend the effective
2080 -- end.
2081 --
2082 if (v_current_effective_end_date is not null and
2083 v_recurring_end_date = v_current_effective_end_date) then
2084 --
2085 -- Check to see if the error was at the element link.
2086 --
2087 if v_element_link_end_date = v_current_effective_end_date then
2088 hr_utility.set_message(801, 'HR_6281_ELE_ENTRY_DT_DEL_LINK');
2089 hr_utility.raise_error;
2090 end if;
2091 --
2092 -- Check to see if the error was at the element termination processing rule.
2093 --
2094 if v_element_term_rule_date = v_current_effective_end_date then
2095 hr_utility.set_message(801, 'HR_6283_ELE_ENTRY_DT_ELE_DEL');
2096 hr_utility.raise_error;
2097 end if;
2098 --
2099 -- Check to see if the error was at the eligibility level.
2100 --
2101 if v_asg_max_eligibility_date = v_current_effective_end_date then
2102 hr_utility.set_message(801, 'HR_6284_ELE_ENTRY_DT_ASG_DEL');
2103 hr_utility.raise_error;
2104 end if;
2105 end if;
2106 --
2107 -- Return the recurring effective end date
2108 --
2109 return v_recurring_end_date;
2110 --
2111 end recurring_entry_end_date;
2112 --
2113 -- NAME
2114 -- hr_entry.chk_element_entry_eligibility
2115 --
2116 -- DESCRIPTION
2117 -- This procedure is used to check if entries (which are defined below) are
2118 -- eligble to be inserted/deleted.
2119 --
2120 -- The checks performed within this procedure are as follows:
2121 -- 1) Ensure that the entry exists within the duration of the element link.
2122 -- 2) If the employee assignment has been terminated then ensure that the
2123 -- entry does not exist past the termination processing rule date.
2124 -- 3) Ensure that the element entry which is being inserted is eligible
2125 -- through its link/assignment criteria.
2126 --
2127 -- This procedure should never be called when:
2128 -- 1) Insert RECURRING element entries (because these checks are done when
2129 -- generating the effective_end_date of the recurring entry).
2130 -- 2) When Updating an ENTRY.
2131 -- 3) When deleting an entry which is 'ZAP' or 'DELETE'.
2132 --
2133 -- This procedure is only called when:
2134 -- 1) Inserting an NONRECURRING element entry
2135 -- (which is defined as: Nonrecurring, Additional, Override, Adjustment,
2136 -- Balance Adjustment etc).
2137 -- e.g. when (p_usage = 'INSERT' and
2138 -- ((p_processing_type = 'R' and
2139 -- p_entry_type <> 'E') or
2140 -- p_processing_type = 'N'))
2141 --
2142 -- 2) DateTrack deleting (Next/Future Changes) of a RECURRING element entry.
2143 -- e.g. (p_dt_delete_mode = 'DELETE_NEXT_CHANGE' or
2144 -- p_dt_delete_mode = 'FUTURE_CHANGE')
2145 --
2146 -- Parameter Passing when calling the procedure:
2147 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2148 -- Parameter Description
2149 -- ======================= ====================================================
2150 -- p_assignment_id The current employee asignment id.
2151 -- p_element_link_id The current element link id for the entry.
2152 -- p_session_date The current session or effective date of operation.
2153 -- p_usage Set to either 'INSERT' if check is for NONRECURRING
2154 -- entry or 'DELETE' for DateTrack delete operations.
2155 -- p_validation_start_date The start date for the checks.
2156 -- p_validation_end_date The end date for the checks.
2157 -- p_time_period_start_date The start date of the time period in which we should
2158 -- check the eligibility of the assignment for the link
2159 -- Should be the payroll period start date for a non-recurring entry
2160 -- Should be NULL otherwise.
2161 -- p_time_period_end_date The start date of the time period in which we should
2162 -- check the eligibility of the assignment for the link
2163 -- Should be the payroll period start date for a non-recurring entry
2164 -- Should be NULL otherwise.
2165 --
2166 PROCEDURE chk_element_entry_eligibility (p_assignment_id in number,
2167 p_element_link_id in number,
2168 p_session_date in date,
2169 p_usage in varchar2,
2170 p_creator_type in varchar2,
2171 p_validation_start_date in date,
2172 p_validation_end_date in date,
2173 -- Bugfix 5135065
2174 -- Added time period start and end date parameters
2175 p_time_period_start_date in date,
2176 p_time_period_end_date in date,
2177 p_min_eligibility_date out nocopy date,
2178 p_max_eligibility_date out nocopy date) is
2179 v_element_term_rule_date date;
2180 v_element_link_start_date date;
2181 v_element_link_end_date date;
2182 v_min_eligibility_date date;
2183 v_max_eligibility_date date;
2184 v_out_date_not_required date;
2185 --
2186 -- Procedure Parameter Name Description
2187 -- ========================== =================================================
2188 -- p_assignment_id Holds the current employee assignment id.
2189 -- p_element_link_id Holds the element link id.
2190 -- p_session_date Holds the current session effective date.
2191 -- p_usage Holds either 'INSERT' or 'DELETE'.
2192 -- p_validation_start_date Holds the validation start date of entry.
2193 -- p_validation_end_date Holds the validation end date of entry.
2194 --
2195 -- Local Parameter Name Description
2196 -- ========================== =================================================
2197 -- v_element_term_rule_date Holds the element termination date.
2198 -- v_element_link_start_date Holds the element link start date.
2199 -- v_element_link_end_date Holds the element link end date.
2200 -- v_min_eligibility_date Holds the minimum assignment link eligibility
2201 -- date.
2202 -- v_max_eligibility_date Holds the maximum assignment link eligibility
2203 -- date.
2204 begin
2205
2206 if g_debug then
2207 hr_utility.trace('In hr_entry.chk_element_entry_eligibility');
2208 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
2209 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
2210 hr_utility.trace(' p_session_date : '|| p_session_date);
2211 hr_utility.trace(' p_usage : '|| p_usage);
2212 hr_utility.trace(' p_creator_type : '|| p_creator_type);
2213 hr_utility.trace(' p_validation_start_date : '|| p_validation_start_date);
2214 hr_utility.trace(' p_validation_end_date : '|| p_validation_end_date);
2215 hr_utility.trace(' p_time_period_start_date : '|| p_time_period_start_date);
2216 hr_utility.trace(' p_time_period_end_date : '|| p_time_period_end_date);
2217 end if;
2218 --
2219 -- Bug 5867658.
2220 -- Ensure assignment is visible to (possibly secure) user before continuing.
2221 -- If the assignment is not visible then we want to raise a helpful message.
2222 --
2223 chk_asg_visible(p_assignment_id, p_session_date);
2224 --
2225 -- We must ensure that the entry is eligible through the payroll and link dates.
2226 --
2227 hr_entry.entry_asg_pay_link_dates (p_assignment_id,
2228 p_element_link_id,
2229 p_session_date,
2230 v_element_term_rule_date,
2231 v_element_link_start_date,
2232 v_element_link_end_date,
2233 v_out_date_not_required,
2234 v_out_date_not_required);
2235 --
2236 -- Ensure that the element entry which is being inserted is eligible
2237 -- through its link/assignment criteria.
2238 --
2239 hr_entry.get_eligibility_period (p_assignment_id,
2240 p_element_link_id,
2241 p_session_date,
2242 -- Bugfix 5135065
2243 p_creator_type,
2244 p_time_period_start_date,
2245 p_time_period_end_date,
2246 v_min_eligibility_date,
2247 v_max_eligibility_date);
2248
2249 p_min_eligibility_date := v_min_eligibility_date;
2250 p_max_eligibility_date := v_max_eligibility_date;
2251 --
2252 if p_usage = 'INSERT' then
2253 --
2254 -- Bugfix 4114282
2255 -- We now allow for a nonrecurring entry to be created for part of a
2256 -- payroll period, even when the link only exists for part of that
2257 -- period, as long as there is eligibility for the element.
2258 -- Therefore, we no longer need this check...
2259 /*
2260 --
2261 -- Ensure that the link exists for the duration of the entry
2262 -- when inserting an nonrecurring entry.
2263 --
2264 if (greatest(p_validation_start_date,v_min_eligibility_date) < v_element_link_start_date) or
2265 (least(p_validation_end_date,v_max_eligibility_date) > v_element_link_end_date) then
2266 hr_utility.set_message(801, 'HR_6132_ELE_ENTRY_LINK_MISSING');
2267 hr_utility.raise_error;
2268 end if;
2269 */
2270 --
2271 -- If the employee assignment has been terminated then we must ensure that
2272 -- the validation end date is before the element termination date.
2273 --
2274 if (v_element_term_rule_date is not null and
2275 (v_element_term_rule_date < p_validation_end_date)) then
2276 hr_utility.set_message(801, 'HR_6370_ELE_ENTRY_NO_TERM');
2277 hr_utility.raise_error;
2278 end if;
2279 --
2280 else
2281 --
2282 -- Ensure that the element link does not terminate before the
2283 -- validation_end_date.
2284 --
2285 if (p_validation_end_date > v_element_link_end_date) then
2286 hr_utility.set_message(801, 'HR_6281_ELE_ENTRY_DT_DEL_LINK');
2287 hr_utility.raise_error;
2288 end if;
2289 --
2290 -- If the employee assignment has been terminated then we must ensure that
2291 -- the validation end date is before the element termination date.
2292 --
2293 if (v_element_term_rule_date is not null and
2294 (v_element_term_rule_date < p_validation_end_date)) then
2295 hr_utility.set_message(801, 'HR_6283_ELE_ENTRY_DT_ELE_DEL');
2296 hr_utility.raise_error;
2297 end if;
2298 --
2299 end if;
2300 --
2301 -- Only check eligibility date against validation end date
2302 -- for non INSERT cases. This is part of a change to
2303 -- fix bug 2183279.
2304 if(p_usage <> 'INSERT') then
2305 if (v_max_eligibility_date < p_validation_end_date) then
2306 hr_utility.set_message(801, 'HR_6284_ELE_ENTRY_DT_ASG_DEL');
2307 hr_utility.raise_error;
2308 end if;
2309 end if;
2310 --
2311 end chk_element_entry_eligibility;
2312 --
2313 -- NAME
2314 -- hr_entry.chk_element_entry_eligbility
2315 --
2316 -- DESCRIPTION
2317 -- Bugfix 5135065
2318 -- Overloaded version provided for backwards compatibility
2319 -- See new chk_element_entry_eligibility [sic] for description
2320 --
2321 PROCEDURE chk_element_entry_eligbility (p_assignment_id in number,
2322 p_element_link_id in number,
2323 p_session_date in date,
2324 p_usage in varchar2,
2325 p_validation_start_date in date,
2326 p_validation_end_date in date,
2327 p_min_eligibility_date out nocopy date,
2328 p_max_eligibility_date out nocopy date) is
2329 begin
2330 --
2331 -- Call the new chk_element_entry_eligibility procedure, passing in null
2332 -- for the time period start and end dates
2333 --
2334 chk_element_entry_eligibility (
2335 p_assignment_id => p_assignment_id
2336 ,p_element_link_id => p_element_link_id
2337 ,p_session_date => p_session_date
2338 ,p_usage => p_usage
2339 ,p_creator_type => null
2340 ,p_validation_start_date => p_validation_start_date
2341 ,p_validation_end_date => p_validation_end_date
2342 -- Bugfix 5135065
2343 -- Set the time period start and end dates to NULL
2344 ,p_time_period_start_date => null
2345 ,p_time_period_end_date => null
2346 ,p_min_eligibility_date => p_min_eligibility_date
2347 ,p_max_eligibility_date => p_max_eligibility_date
2348 );
2349 --
2350 end chk_element_entry_eligbility;
2351 --
2352 -- NAME
2353 -- hr_entry.chk_element_entry_open
2354 --
2355 -- DESCRIPTION
2356 -- This procedure does the following checks:
2357 -- 1) Ensure that the element type is not closed for entry currently
2358 -- or in the future by determining the value of the
2359 -- CLOSED_FOR_ENTRY_FLAG attribute on PAY_ELEMENT_TYPES_F.
2360 -- 2) If the employee assignment is to a payroll then ensure that
2361 -- the current and future periods as of session date are open.
2362 -- If the period is closed, you can only change entries providing
2363 -- they are not to be processed in a payroll run.
2364 --
2365 procedure chk_element_entry_open
2366 (
2367 p_element_type_id in number,
2368 p_session_date in date,
2369 p_validation_start_date in date,
2370 p_validation_end_date in date,
2371 p_assignment_id in number
2372 ) is
2373 --
2374 l_element_name pay_element_types_f.element_name%TYPE;
2375 l_legislation_code pay_element_types_f.legislation_code%TYPE;
2376 l_us_except boolean := FALSE;
2377
2378 cursor csr_element_type
2379 (
2380 p_element_type_id number,
2381 p_validation_start_date date,
2382 p_validation_end_date date
2383 ) is
2384 select et_tl.element_name,
2385 et.closed_for_entry_flag,
2386 et.legislation_code
2387 from pay_element_types_f_tl et_tl,
2388 pay_element_types_f et
2389 where et.element_type_id = et_tl.element_type_id
2390 and et.element_type_id = p_element_type_id
2391 and userenv('LANG') = et_tl.language
2392 and et.effective_start_date <= p_validation_end_date
2393 and et.effective_end_date >= p_validation_start_date;
2394 --
2395 cursor csr_time_period
2396 (
2397 p_assignment_id number,
2398 p_validation_start_date date,
2399 p_validation_end_date date
2400 ) is
2401 select tp.status
2402 from per_time_periods tp,
2403 per_assignments_f asg
2404 where asg.assignment_id = p_assignment_id
2405 and asg.payroll_id is not null
2406 and asg.effective_start_date <= p_validation_end_date
2407 and asg.effective_end_date >= p_validation_start_date
2408 and tp.payroll_id = asg.payroll_id
2409 and tp.end_date >= p_validation_start_date
2410 and tp.start_date <= p_validation_end_date
2411 and tp.end_date >= asg.effective_start_date
2412 and tp.start_date <= asg.effective_end_date
2413 and tp.status='C';
2414 --
2415 begin
2416 --
2417 if g_debug then
2418 hr_utility.trace('In hr_entry.chk_element_entry_open');
2419 hr_utility.trace(' p_element_type_id : '|| p_element_type_id);
2420 hr_utility.trace(' p_session_date : '|| p_session_date);
2421 hr_utility.trace(' p_validation_start_date : '|| p_validation_start_date);
2422 hr_utility.trace(' p_validation_end_date : '|| p_validation_end_date);
2423 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
2424 end if;
2425 -- LOCK the element type table in share mode so that no other user can take
2426 -- out an exclusive lock to change the data. This provides a stable view
2427 -- of the element type table. See if the element type has been closed for
2428 -- entry over the period of change. If it has then error !
2429 --lock table pay_element_types_f in share mode;
2430 --
2431 for v_element_type in csr_element_type(p_element_type_id,
2432 p_validation_start_date,
2433 p_validation_end_date) loop
2434 --
2435 if v_element_type.closed_for_entry_flag = 'Y' then
2436 --
2437 hr_utility.set_message(801, 'HR_6064_ELE_ENTRY_CLOSED_ELE');
2438 hr_utility.set_message_token('element_name',v_element_type.element_name);
2439 hr_utility.raise_error;
2440 --
2441 end if;
2442 l_element_name := v_element_type.element_name;
2443 l_legislation_code := v_element_type.legislation_code;
2444 --
2445 end loop;
2446 --
2447 -- LOCK the payroll table in share mode so that no other user can take
2448 -- out an exclusive lock to change the data. As all changes to time periods
2449 -- require an exclusive lock to be taken out on the payroll it provides a
2450 -- stable view of the time period table. See if a time period has been
2451 -- closed over the period of change. If it has then error !
2452 --lock table pay_payrolls_f in share mode;
2453 --
2454 if hr_entry.entry_process_in_run(p_element_type_id, p_session_date) then
2455 --
2456 for v_time_period in csr_time_period(p_assignment_id,
2457 p_validation_start_date,
2458 p_validation_end_date) loop
2459 --
2460 if csr_time_period%found then
2461 --
2462 -- Error will not be raised for VERTEX, Workers Compensation element with
2463 -- Legislation code as US. Bug No 506819
2464 -- Handle the fact the legislation_code may be null. Bug 1633313.
2465
2466 l_us_except := FALSE;
2467
2468 if (l_legislation_code is not null AND
2469 l_legislation_code = 'US' AND
2470 l_element_name in ('US_TAX_VERTEX','VERTEX','Workers Compensation')) then
2471 l_us_except := TRUE;
2472 end if;
2473
2474 if not l_us_except then
2475 hr_utility.set_message(801, 'HR_6074_ELE_ENTRY_CLOSE_PERIOD');
2476 hr_utility.raise_error;
2477 end if;
2478 --
2479 end if;
2480 --
2481 end loop;
2482 --
2483 end if;
2484 --
2485 end chk_element_entry_open;
2486 --
2487 -- NAME
2488 -- hr_entry.derive_default_value
2489 --
2490 -- DESCRIPTION
2491 -- This procedure is used to return default screen and database formatted
2492 -- values in either a cold or hot format for the specified link and
2493 -- input value. The default value can be for Minimum, Maximum or Default
2494 -- values.
2495 -- Therefore, it hot defaults are being used the returned database value
2496 -- will be null but, the return screen value will be encapsulated in
2497 -- double-quotation marks.
2498 --
2499 PROCEDURE derive_default_value (p_element_link_id in number,
2500 p_input_value_id in number,
2501 p_session_date in date,
2502 p_input_currency_code in varchar2,
2503 p_min_max_def in varchar2
2504 default 'DEF',
2505 v_screen_format_value out nocopy varchar2,
2506 v_database_format_value out nocopy varchar2) is
2507 v_hot_default_flag varchar2(30);
2508 v_default_value varchar2(60);
2509 v_minimum_value varchar2(60);
2510 v_maximum_value varchar2(60);
2511 v_uom varchar2(60);
2512 v_value_format_in varchar2(60);
2513 -- --
2514 -- Enhancement 2793978
2515 -- Size of v_value_format_out increased to handle screen format of
2516 -- value set validated entry values.
2517 v_value_format_out varchar2(240);
2518 -- --
2519 v_lookup_type varchar2(30);
2520 v_value_set_id number(10);
2521 --
2522 -- Procedure Parameter Name Description
2523 -- ========================== =================================================
2524 -- p_element_link_id Holds the element link id.
2525 -- p_input_value_id Holds the input value id.
2526 -- p_session_date Holds the current session effective date.
2527 -- p_input_currency_code Holds the input currency code for money uom's.
2528 -- p_min_max_def Determines which default value is to be
2529 -- specified. Valid values are MINL, MAXL,
2530 -- DEF or DEFL.
2531 -- DEFL, MINL and MAXL are used to return the
2532 -- default value to be used at link level.
2533 -- DEF is used to return the default value at
2534 -- entry level.
2535 -- v_screen_format_value Returns the screen format value.
2536 -- v_database_format_value Returns the database format value.
2537 --
2538 -- Local Parameter Name Description
2539 -- ========================== ==================================================
2540 -- v_hot_default_flag Determines if the input value is hot defaulted.
2541 -- v_default_value Holds the default value.
2542 -- v_minimum_value Holds the minimum default value.
2543 -- v_maximum_value Holds the maximum default value.
2544 -- v_uom Holds the unit of measure for the specified
2545 -- input value.
2546 -- v_value_format_in Holds the selected database format value to
2547 -- be converted into a screen format.
2548 -- v_value_format_out Holds the screen format value which has been
2549 -- converted.
2550 begin
2551 g_debug := hr_utility.debug_enabled;
2552 --
2553 -- Ensure that the p_min_max_def parameters contains either MINL, MAXL, DEF
2554 -- oe DEFL as a value.
2555 --
2556 if (p_min_max_def = 'MINL' or
2557 p_min_max_def = 'MAXL' or
2558 p_min_max_def = 'DEFL' or
2559 p_min_max_def = 'DEF') then
2560 null;
2561 else
2562 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2563 hr_utility.set_message_token('PROCEDURE','hr_entry.derive_default_value');
2564 hr_utility.set_message_token('STEP',1);
2565 hr_utility.raise_error;
2566 end if;
2567 --
2568 -- Need to determine the input value unit of measure and if it is using
2569 -- hot or cold defaults.
2570 --
2571 if g_debug then
2572 hr_utility.set_location('hr_entry.derive_default_value', 2);
2573 end if;
2574 begin
2575 select iv.uom,
2576 iv.hot_default_flag,
2577 iv.lookup_type,
2578 iv.value_set_id
2579 into v_uom,
2580 v_hot_default_flag,
2581 v_lookup_type,
2582 v_value_set_id
2583 from pay_input_values_f iv
2584 where iv.input_value_id = p_input_value_id
2585 and p_session_date
2586 between iv.effective_start_date and iv.effective_end_date;
2587 exception
2588 when NO_DATA_FOUND then
2589 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2590 hr_utility.set_message_token('PROCEDURE',
2591 'hr_entry.derive_default_value');
2592 hr_utility.set_message_token('STEP','2');
2593 hr_utility.raise_error;
2594 end;
2595 --
2596 -- If using cold defaults then, we must select the value at the link level.
2597 --
2598 if v_hot_default_flag = 'N' then
2599 if g_debug then
2600 hr_utility.set_location('hr_entry.derive_default_value', 3);
2601 end if;
2602 begin
2603 -- INDEX hint added following NHS project recommendation
2604 select /*+ INDEX(l, pay_link_input_values_f_n2) */
2605 l.default_value,
2606 l.min_value,
2607 l.max_value
2608 into v_default_value,
2609 v_minimum_value,
2610 v_maximum_value
2611 from pay_link_input_values_f l
2612 where l.input_value_id = p_input_value_id
2613 and l.element_link_id = p_element_link_id
2614 and p_session_date
2615 between l.effective_start_date and l.effective_end_date;
2616 exception
2617 when NO_DATA_FOUND then
2618 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2619 hr_utility.set_message_token('PROCEDURE',
2620 'hr_entry.derive_default_value');
2621 hr_utility.set_message_token('STEP','3');
2622 hr_utility.raise_error;
2623 end;
2624 else
2625 --
2626 -- The input is hot defaulted therefore we can deduce that the values held
2627 -- on the database are going to be null. However, we still need to select
2628 -- the hot default values and encapsulated in quotations (").
2629 --
2630 if (p_min_max_def = 'DEF') then
2631 if g_debug then
2632 hr_utility.set_location('hr_entry.derive_default_value', 4);
2633 end if;
2634 begin
2635 select decode(l.default_value,
2636 '',i.default_value,
2637 l.default_value)
2638 into v_default_value
2639 from pay_link_input_values_f l,
2640 pay_input_values_f i
2641 where i.input_value_id = p_input_value_id
2642 and l.input_value_id = i.input_value_id
2643 and l.element_link_id = p_element_link_id
2644 and p_session_date
2645 between i.effective_start_date and i.effective_end_date
2646 and p_session_date
2647 between l.effective_start_date and l.effective_end_date;
2648 exception
2649 when NO_DATA_FOUND then
2650 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2651 hr_utility.set_message_token('PROCEDURE',
2652 'hr_entry.derive_default_value');
2653 hr_utility.set_message_token('STEP','4');
2654 hr_utility.raise_error;
2655 end;
2656 else
2657 if g_debug then
2658 hr_utility.set_location('hr_entry.derive_default_value', 5);
2659 end if;
2660 begin
2661 select i.default_value,
2662 i.min_value,
2663 i.max_value
2664 into v_default_value,
2665 v_minimum_value,
2666 v_maximum_value
2667 from pay_input_values_f i
2668 where i.input_value_id = p_input_value_id
2669 and p_session_date
2670 between i.effective_start_date and i.effective_end_date;
2671 exception
2672 when NO_DATA_FOUND then
2673 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2674 hr_utility.set_message_token('PROCEDURE',
2675 'hr_entry.derive_default_value');
2676 hr_utility.set_message_token('STEP','5');
2677 hr_utility.raise_error;
2678 end;
2679 end if;
2680 --
2681 end if;
2682 --
2683 -- Set the format value in parameter.
2684 --
2685 if (p_min_max_def = 'DEF' or
2686 p_min_max_def = 'DEFL') then
2687 v_value_format_in := v_default_value;
2688 elsif p_min_max_def = 'MINL' then
2689 v_value_format_in := v_minimum_value;
2690 elsif p_min_max_def = 'MAXL' then
2691 v_value_format_in := v_maximum_value;
2692 end if;
2693 --
2694 -- Now format the required valued to the display value required.
2695 -- If the value is a lookup then we must select the meaning from the
2696 -- lookup table.
2697 --
2698 if (v_lookup_type is not null and
2699 v_value_format_in is not null) then
2700 if g_debug then
2701 hr_utility.set_location('hr_entry.derive_default_value', 6);
2702 end if;
2703 begin
2704 select h.meaning
2705 into v_value_format_out
2706 from hr_lookups h
2707 where h.lookup_type = v_lookup_type
2708 and h.lookup_code = v_value_format_in;
2709 exception
2710 when NO_DATA_FOUND then
2711 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2712 hr_utility.set_message_token('PROCEDURE',
2713 'hr_entry.derive_default_value');
2714 hr_utility.set_message_token('STEP','6');
2715 hr_utility.raise_error;
2716 end;
2717 --
2718 -- Enhancement 2793978
2719 -- If the value uses a value set for validation we need to decode
2720 -- the value to its corresponding value set meaning
2721 --
2722 elsif (v_value_set_id is not null and
2723 v_value_format_in is not null) then
2724 if g_debug then
2725 hr_utility.set_location('hr_entry.derive_default_value', 7);
2726 end if;
2727 v_value_format_out := pay_input_values_pkg.decode_vset_value(
2728 v_value_set_id,v_value_format_in);
2729 if v_value_format_out is null then
2730 --
2731 -- The value must have been invalid for the value set since no
2732 -- corresponding meaning was found, raise an error
2733 --
2734 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2735 hr_utility.set_message_token('PROCEDURE',
2736 'hr_entry.derive_default_value');
2737 hr_utility.set_message_token('STEP','7');
2738 hr_utility.raise_error;
2739 end if;
2740 else
2741 hr_chkfmt.changeformat (v_value_format_in,
2742 v_value_format_out,
2743 v_uom,
2744 p_input_currency_code);
2745 end if;
2746 --
2747 -- If the element is hot defaulted then
2748 -- set the display value = encapsulate the formatted value in '"'
2749 -- set the database value = null
2750 -- else
2751 -- set the display value = formatted value
2752 -- set the database value = format in value
2753 -- End If;
2754 --
2755 if v_hot_default_flag = 'Y' then
2756 if v_value_format_out is NULL then
2757 v_screen_format_value := v_value_format_out;
2758 else
2759 v_screen_format_value := '"'||v_value_format_out||'"';
2760 end if;
2761 v_database_format_value := '';
2762 else
2763 v_screen_format_value := v_value_format_out;
2764 v_database_format_value := v_value_format_in;
2765 end if;
2766 --
2767 end derive_default_value;
2768 --
2769 -- NAME
2770 -- hr_entry.chk_mandatory_input_value
2771 --
2772 -- DESCRIPTION
2773 -- This procedure produces an error is any input value which is defined as
2774 -- having a mandatory value is null.
2775 --
2776 PROCEDURE chk_mandatory_input_value (p_input_value_id in number,
2777 p_entry_value in varchar2,
2778 p_session_date in date,
2779 p_element_link_id in number) is
2780 v_hot_default_flag varchar2(30);
2781 v_mandatory_flag varchar2(30);
2782 v_name varchar2(80);
2783 v_default_value varchar2(60);
2784 --
2785 begin
2786 g_debug := hr_utility.debug_enabled;
2787 if g_debug then
2788 hr_utility.set_location ('hr_entry.chk_mandatory_input_value',1);
2789 end if;
2790 hr_general.assert_condition (p_session_date = trunc (p_session_date)
2791 and p_input_value_id is not null
2792 and p_session_date is not null);
2793 --
2794 if (p_input_value_id is not null and
2795 p_entry_value is null) then
2796 if g_debug then
2797 hr_utility.set_location('hr_entry.chk_mandatory_input_value', 1);
2798 end if;
2799 --
2800 -- Select the hot/mandatory flag details.
2801 --
2802 begin
2803 select i.hot_default_flag,
2804 i.mandatory_flag,
2805 i_tl.name
2806 into v_hot_default_flag,
2807 v_mandatory_flag,
2808 v_name
2809 from pay_input_values_f_tl i_tl,
2810 pay_input_values_f i
2811 where i.input_value_id = i_tl.input_value_id
2812 and i.input_value_id = p_input_value_id
2813 and userenv('LANG') = i_tl.language
2814 and p_session_date
2815 between i.effective_start_date
2816 and i.effective_end_date;
2817 exception
2818 when NO_DATA_FOUND then
2819 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2820 hr_utility.set_message_token('PROCEDURE',
2821 'hr_entry.chk_mandatory_input_value');
2822 hr_utility.set_message_token('STEP','1');
2823 hr_utility.raise_error;
2824 end;
2825 --
2826 -- If the input value is mandatory ensure a value exists.
2827 --
2828 if v_mandatory_flag = 'Y' then
2829 if v_hot_default_flag = 'N' then
2830 --
2831 -- Cold check.
2832 --
2833 hr_utility.set_message(801, 'HR_6127_ELE_ENTRY_VALUE_MAND');
2834 hr_utility.set_message_token('INPUT_VALUE_NAME',v_name);
2835 hr_utility.raise_error;
2836 else
2837 --
2838 -- Hot check.
2839 --
2840 if g_debug then
2841 hr_utility.set_location('hr_entry.chk_mandatory_input_value', 5);
2842 end if;
2843 begin
2844 select nvl(l.default_value,i.default_value)
2845 into v_default_value
2846 from pay_link_input_values_f l,
2847 pay_input_values_f i
2848 where i.input_value_id = p_input_value_id
2849 and l.input_value_id = i.input_value_id
2850 and l.element_link_id = p_element_link_id
2851 and p_session_date
2852 between i.effective_start_date and i.effective_end_date
2853 and p_session_date
2854 between l.effective_start_date and l.effective_end_date;
2855 end;
2856 --
2857 if v_default_value is null then
2858 hr_utility.set_message(801, 'HR_6128_ELE_ENTRY_MAND_HOT');
2859 hr_utility.set_message_token('INPUT_VALUE_NAME',v_name);
2860 hr_utility.raise_error;
2861 end if;
2862 end if;
2863 end if;
2864 end if;
2865 end chk_mandatory_input_value;
2866 --
2867 procedure chk_mandatory_entry_values
2868 (
2869 p_element_link_id number,
2870 p_validation_start_date date,
2871 p_num_entry_values number,
2872 p_input_value_id_tbl hr_entry.number_table,
2873 p_entry_value_tbl hr_entry.varchar2_table
2874 ) is
2875 --
2876 begin
2877 if g_debug then
2878 hr_utility.set_location ('hr_entry.chk_mandatory_entry_values',1);
2879 end if;
2880 hr_general.assert_condition (
2881 p_validation_start_date = trunc (p_validation_start_date));
2882 --
2883 -- For every entry value make sure that a value has been supplied if it
2884 -- is mandatory.
2885 if p_num_entry_values > 0 then
2886 --
2887 for v_loop in 1..p_num_entry_values loop
2888 --
2889 hr_entry.chk_mandatory_input_value(p_input_value_id_tbl(v_loop),
2890 p_entry_value_tbl(v_loop),
2891 p_validation_start_date,
2892 p_element_link_id);
2893 --
2894 end loop;
2895 --
2896 end if;
2897 --
2898 end chk_mandatory_entry_values;
2899 --
2900 -- NAME
2901 -- hr_entry.chk_element_entry
2902 --
2903 -- DESCRIPTION
2904 -- This procedure is used for referential/standard checks when inserting/
2905 -- updating or deleteing element enries.
2906 --
2907 -- Procedure Parameter Name Description
2908 -- ========================== =================================================
2909 -- p_element_entry_id Holds the element entry id.
2910 -- p_session_date Holds the current session effective date.
2911 -- p_element_link_id Holds the element link id.
2912 -- p_assignment_id Holds the current employee assignment id.
2913 -- p_entry_type Holds the entry type of the entry.
2914 -- p_effective_start_date Holds the effective start date of entry.
2915 -- p_effective_end_date Holds the effective end date of entry.
2916 -- p_validation_start_date Holds the validation start date of entry.
2917 -- p_validation_end_date Holds the validation end date of entry.
2918 -- p_dt_update_mode If entry is being date effectively updated then
2919 -- the update mode is set.
2920 -- p_dt_delete_mode If entry is being date effectively deleted then
2921 -- the delete mode is set.
2922 -- p_usage Determines the commit operation. Valid values are
2923 -- INSERT, UPDATE or DELETE.
2924 -- p_target_entry_id If the entry is an adjustment for a recurring
2925 -- entry then the target entry id holds the parent
2926 -- entry id.
2927 --
2928 procedure chk_element_entry
2929 (
2930 p_element_entry_id in number,
2931 p_original_entry_id in number,
2932 p_session_date in date,
2933 p_element_link_id in number,
2934 p_assignment_id in number,
2935 p_entry_type in varchar2,
2936 p_effective_start_date in out nocopy date,
2937 p_effective_end_date in out nocopy date,
2938 p_validation_start_date in date,
2939 p_validation_end_date in date,
2940 p_dt_update_mode in varchar2,
2941 p_dt_delete_mode in varchar2,
2942 p_usage in varchar2,
2943 p_target_entry_id in number
2944 ) is
2945 begin
2946 g_debug := hr_utility.debug_enabled;
2947
2948 if g_debug then
2949
2950 hr_utility.trace('In hr_entry.chk_element_entry');
2951 hr_utility.trace(' p_element_entry_id : '|| p_element_entry_id);
2952 hr_utility.trace(' p_original_entry_id : '|| p_original_entry_id);
2953 hr_utility.trace(' p_session_date : '|| p_session_date);
2954 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
2955 hr_utility.trace(' p_assignment_id : '|| p_assignment_id);
2956 hr_utility.trace(' p_entry_type : '|| p_entry_type);
2957 hr_utility.trace(' p_effective_start_date : '|| p_effective_start_date);
2958 hr_utility.trace(' p_effective_end_date : '|| p_effective_end_date);
2959 hr_utility.trace(' p_validation_start_date : '|| p_validation_start_date);
2960 hr_utility.trace(' p_validation_end_date : '|| p_validation_end_date);
2961 hr_utility.trace(' p_dt_update_mode : '|| p_dt_update_mode);
2962 hr_utility.trace(' p_dt_delete_mode : '|| p_dt_delete_mode);
2963 hr_utility.trace(' p_usage : '|| p_usage);
2964 hr_utility.trace(' p_target_entry_id : '|| p_target_entry_id);
2965
2966 end if;
2967 --
2968 -- simply call chk_element_entry_main with a null p_creator_type
2969 --
2970 chk_element_entry_main
2971 (
2972 p_element_entry_id,
2973 p_original_entry_id,
2974 p_session_date,
2975 p_element_link_id,
2976 p_assignment_id,
2977 p_entry_type,
2978 p_effective_start_date,
2979 p_effective_end_date,
2980 p_validation_start_date,
2981 p_validation_end_date,
2982 p_dt_update_mode,
2983 p_dt_delete_mode,
2984 p_usage,
2985 p_target_entry_id,
2986 null
2987 );
2988 --
2989 end chk_element_entry;
2990 --
2991 -- NAME
2992 -- hr_entry.chk_element_entry_main
2993 --
2994 -- DESCRIPTION
2995 -- This procedure is used for referential/standard checks when inserting/
2996 -- updating or deleteing element enries.
2997 --
2998 -- Procedure Parameter Name Description
2999 -- ========================== =================================================
3000 -- p_element_entry_id Holds the element entry id.
3001 -- p_session_date Holds the current session effective date.
3002 -- p_element_link_id Holds the element link id.
3003 -- p_assignment_id Holds the current employee assignment id.
3004 -- p_entry_type Holds the entry type of the entry.
3005 -- p_effective_start_date Holds the effective start date of entry.
3006 -- p_effective_end_date Holds the effective end date of entry.
3007 -- p_validation_start_date Holds the validation start date of entry.
3008 -- p_validation_end_date Holds the validation end date of entry.
3009 -- p_dt_update_mode If entry is being date effectively updated then
3010 -- the update mode is set.
3011 -- p_dt_delete_mode If entry is being date effectively deleted then
3012 -- the delete mode is set.
3013 -- p_usage Determines the commit operation. Valid values are
3014 -- INSERT, UPDATE or DELETE.
3015 -- p_target_entry_id If the entry is an adjustment for a recurring
3016 -- entry then the target entry id holds the parent
3017 -- entry id.
3018 -- p_creator_type If the creator type is RetroPay Element ('EE', 'RR'),
3019 -- allow more than one additional entry per period.
3020 --
3021 procedure chk_element_entry_main
3022 (
3023 p_element_entry_id in number,
3024 p_original_entry_id in number,
3025 p_session_date in date,
3026 p_element_link_id in number,
3027 p_assignment_id in number,
3028 p_entry_type in varchar2,
3029 p_effective_start_date in out nocopy date,
3030 p_effective_end_date in out nocopy date,
3031 p_validation_start_date in date,
3032 p_validation_end_date in date,
3033 p_dt_update_mode in varchar2,
3034 p_dt_delete_mode in varchar2,
3035 p_usage in varchar2,
3036 p_target_entry_id in number,
3037 p_creator_type in varchar2
3038 ) is
3039 --
3040 -- Local Variables
3041 --
3042 v_error_flag varchar2(1) := 'N';
3043 v_validation_start_date date;
3044 v_validation_end_date date;
3045 v_payroll_id number;
3046 v_period_start_date date;
3047 v_period_end_date date;
3048 v_loop_counter number;
3049 v_mand_input_value_id number;
3050 v_mand_entry_value varchar2(60);
3051 v_number_of_input_values number;
3052 v_counter_not_required number;
3053 v_element_type_id number;
3054 v_processing_type varchar2(30);
3055 v_mult_entries_allowed_flag varchar2(30);
3056 v_third_party_pay_only_flag varchar2(30);
3057 v_element_name varchar2(80);
3058 v_classification_name varchar2(80);
3059 v_assignment_number varchar2(30);
3060 v_assignment_type varchar2(1);
3061 v_min_date date;
3062 v_max_date date;
3063
3064 v_max_eligible_date date;--8798020
3065 v_min_eligible_date date;--8798020
3066 v_post_termination_rule varchar2(1);
3067 v_actual_termination_date date default null;
3068
3069 -- wmcveagh, bug 493056
3070 v_element_effective_start_date date;
3071 --
3072 cursor c_element_start_date is
3073 select effective_start_date
3074 from pay_element_entries_f
3075 where element_entry_id = p_target_entry_id;
3076 --
3077 cursor c_cwk_element_check is
3078 -- Should not allow these PTO elements for CWKs
3079 select 'Y'
3080 from pay_element_links_f pel
3081 ,pay_element_types_f pet
3082 ,pay_accrual_plans pap
3083 where pel.element_link_id = p_element_link_id
3084 and p_session_date between pel.effective_start_date
3085 and pel.effective_end_date
3086 and pel.element_type_id = pet.element_type_id
3087 and p_session_date between pet.effective_start_date
3088 and pet.effective_end_date
3089 and pet.element_type_id = pap.accrual_plan_element_type_id
3090 union all
3091 -- Should not allow these absence elements for CWKs
3092 select 'Y'
3093 from pay_element_links_f pel
3094 ,pay_element_types_f pet
3095 ,pay_input_values_f piv
3096 ,per_absence_attendance_types abt
3097 where pel.element_link_id = p_element_link_id
3098 and p_session_date between pel.effective_start_date
3099 and pel.effective_end_date
3100 and pel.element_type_id = pet.element_type_id
3101 and p_session_date between pet.effective_start_date
3102 and pet.effective_end_date
3103 and pet.element_type_id = piv.element_type_id
3104 and p_session_date between piv.effective_start_date
3105 and piv.effective_end_date
3106 and piv.input_value_id = abt.input_value_id
3107 and abt.input_value_id is not null;
3108 --
3109 v_dummy varchar2(1);
3110
3111 begin
3112 g_debug := hr_utility.debug_enabled;
3113 --
3114 if g_debug then
3115 hr_utility.set_location('hr_entry.chk_element_entry_main', 5);
3116 end if;
3117 --
3118 -- Fetch element type information relevent to the creation of element
3119 -- entries
3120 --
3121 begin
3122 select et.element_type_id,
3123 et.processing_type,
3124 et.multiple_entries_allowed_flag,
3125 et.third_party_pay_only_flag,
3126 -- Bugfix 2866619
3127 -- Need element classification for comparison purposes
3128 et.element_name,
3129 ec.classification_name
3130 into v_element_type_id,
3131 v_processing_type,
3132 v_mult_entries_allowed_flag,
3133 v_third_party_pay_only_flag,
3134 v_element_name,
3135 v_classification_name
3136 from pay_element_links_f el,
3137 pay_element_types_f et,
3138 pay_element_classifications ec
3139 where el.element_link_id = p_element_link_id
3140 and et.element_type_id = el.element_type_id
3141 and et.classification_id = ec.classification_id
3142 and p_session_date between el.effective_start_date
3143 and el.effective_end_date
3144 and p_session_date between et.effective_start_date
3145 and et.effective_end_date;
3146
3147 if g_debug then
3148 hr_utility.trace(' v_element_type_id : '|| v_element_type_id);
3149 hr_utility.trace(' v_processing_type : '|| v_processing_type);
3150 hr_utility.trace(' v_mult_entries_allowed_flag : '|| v_mult_entries_allowed_flag);
3151 hr_utility.trace(' v_third_party_pay_only_flag : '|| v_third_party_pay_only_flag);
3152 hr_utility.trace(' v_element_name : '|| v_element_name);
3153 hr_utility.trace(' v_classification_name : '|| v_classification_name);
3154
3155 end if;
3156 --
3157 -- Bugfix 2866619
3158 -- Need assignment type for comparison purposes
3159 --
3160 select asg.assignment_type, asg.assignment_number
3161 into v_assignment_type, v_assignment_number
3162 from per_all_assignments_f asg
3163 where asg.assignment_id = p_assignment_id
3164 and p_session_date between asg.effective_start_date
3165 and asg.effective_end_date;
3166 --
3167
3168 if g_debug then
3169 hr_utility.trace(' v_assignment_type : '|| v_assignment_type);
3170 hr_utility.trace(' v_assignment_number : '|| v_assignment_number);
3171 end if;
3172
3173 exception
3174 when no_data_found then
3175 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3176 hr_utility.set_message_token('PROCEDURE',
3177 'hr_entry.chk_element_entry');
3178 hr_utility.set_message_token('STEP','1');
3179 hr_utility.raise_error;
3180 end;
3181 --
3182 -- Bugfix 2866619
3183 -- Check the assignment type to ensure assignment is eligible for
3184 -- entry
3185 --
3186 if v_assignment_type = 'C' and (v_processing_type <> 'R'
3187 or v_classification_name <> 'Information') then
3188 --
3189 -- Contingent workers can only have recurring entries of
3190 -- 'Information' classification
3191 --
3192 hr_utility.set_message(801, 'HR_33155_ENTRY_INVALID_FOR_CWK');
3193 hr_utility.set_message_token('ELEMENT_NAME',v_element_name);
3194 hr_utility.set_message_token('ASSIGNMENT_NUMBER',v_assignment_number);
3195 hr_utility.set_message_token('SESSION_DATE',to_char(p_session_date));
3196 hr_utility.raise_error;
3197 --
3198 elsif v_assignment_type = 'C' and v_processing_type = 'R'
3199 and v_classification_name = 'Information' then
3200 --
3201 -- Contingent workers have some exception to few PTO and Absece elements.
3202 --
3203 open c_cwk_element_check;
3204 fetch c_cwk_element_check into v_dummy;
3205 --
3206 if c_cwk_element_check%found then
3207 close c_cwk_element_check;
3208 --
3209 hr_utility.set_message(801, 'HR_33155_ENTRY_INVALID_FOR_CWK');
3210 hr_utility.set_message_token('ELEMENT_NAME',v_element_name);
3211 hr_utility.set_message_token('ASSIGNMENT_NUMBER',v_assignment_number);
3212 hr_utility.set_message_token('SESSION_DATE',to_char(p_session_date));
3213 hr_utility.raise_error;
3214 --
3215 end if;
3216 close c_cwk_element_check;
3217 --
3218 end if;
3219 --
3220 -- If the commit usage is insert then set the validation start and end dates
3221 -- to the effective start and end dates.
3222 --
3223 if (p_entry_type = 'E' and
3224 v_processing_type = 'R') then
3225 --
3226 -- As the entry is recurring:
3227 --
3228 if ((p_usage = 'INSERT' or
3229 p_dt_delete_mode = 'FUTURE_CHANGE') or
3230 (p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
3231 p_validation_end_date = hr_general.end_of_time)) then
3232 --
3233 if g_debug then
3234 hr_utility.set_location('hr_entry.chk_element_entry_main', 10);
3235 end if;
3236 --
3237 -- If we are inserting or doing date-effective delete next/future
3238 -- changes then we must set the effective_end_date to be passed back
3239 -- to the form and, also set the validation_end_date NB. this will take
3240 -- into account if multiple entries are allowed.
3241 --
3242 v_validation_end_date := hr_entry.recurring_entry_end_date
3243 (p_assignment_id,
3244 p_element_link_id,
3245 p_session_date,
3246 'Y',
3247 v_mult_entries_allowed_flag,
3248 p_element_entry_id,
3249 p_original_entry_id);
3250 --
3251 if p_usage = 'INSERT' then
3252 v_validation_start_date := p_session_date;
3253 else
3254 v_validation_start_date := p_validation_start_date;
3255 end if;
3256 --
3257 -- We must be doing either a date-effective update or ZAP therefore
3258 -- set the validation_start/end_date to the validation_start/end_date
3259 -- passed in through the form.
3260 --
3261 else
3262 v_validation_start_date := p_validation_start_date;
3263 v_validation_end_date := p_validation_end_date;
3264 end if;
3265
3266 if g_debug then
3267 hr_utility.trace(' v_validation_start_date : '|| v_validation_start_date);
3268 hr_utility.trace(' v_validation_end_date : '|| v_validation_end_date);
3269 end if;
3270 --
3271 -- As the entry is nonrecurring:
3272 --
3273 else
3274 --
3275 if g_debug then
3276 hr_utility.set_location('hr_entry.chk_element_entry_main', 15);
3277 end if;
3278 /*Bug 8798020 Added below call to get the eligibility dates */
3279 /*Bug 12829063 Passing the creator type as p_creator_type instead of null*/
3280 If nvl(p_dt_delete_mode,'NULL') <> 'ZAP' then
3281 hr_entry.chk_element_entry_eligibility (p_assignment_id =>p_assignment_id,
3282 p_element_link_id =>p_element_link_id,
3283 p_session_date =>p_session_date,
3284 p_usage =>p_usage,
3285 p_creator_type => p_creator_type,
3286 p_validation_start_date =>v_validation_start_date,
3287 p_validation_end_date =>v_validation_end_date,
3288 p_time_period_start_date =>v_period_start_date,
3289 p_time_period_end_date =>v_period_end_date,
3290 p_min_eligibility_date =>v_min_eligible_date,
3291 p_max_eligibility_date =>v_max_eligible_date
3292 );
3293
3294 if g_debug then
3295 hr_utility.trace(' v_min_eligible_date : '|| to_char(v_min_eligible_date,'DD-MON-YYYY'));
3296 hr_utility.trace(' v_max_eligible_date : '|| to_char(v_max_eligible_date,'DD-MON-YYYY'));
3297 end if;
3298
3299 --
3300 -- Validate that it is OK to create a nonrecurring entry ie. assignment
3301 -- is to a payroll and also a time period exists. Also calculate the start
3302 -- and end dates of the nonrecurring entry taking into account changes in
3303 -- payroll.
3304 --
3305 hr_entry.get_nonrecurring_dates(p_assignment_id,
3306 p_session_date,
3307 v_validation_start_date,
3308 v_validation_end_date,
3309 v_payroll_id,
3310 v_period_start_date,
3311 v_period_end_date);
3312 --
3313 if g_debug then
3314 hr_utility.trace(' v_payroll_id : '|| v_payroll_id);
3315 hr_utility.trace(' v_period_start_date: '||to_char(v_period_start_date,'DD-MON-YYYY'));
3316 hr_utility.trace(' v_period_end_date: '||to_char(v_period_end_date,'DD-MON-YYYY'));
3317 hr_utility.trace(' v_validation_start_date: '||to_char(v_validation_start_date,'DD-MON-YYYY'));
3318 hr_utility.trace(' v_validation_end_date: '||to_char(v_validation_end_date,'DD-MON-YYYY'));
3319 end if;
3320
3321 /*Bug 8798020 Added below code to take the eligible dates */
3322 v_validation_start_date :=greatest(v_validation_start_date,nvl(v_min_eligible_date,v_validation_start_date));
3323
3324 /* Bug 13467399 Added error handling and removed call to return_termination_rule*/
3325 /*Bug 13015339 Added below code to calculate the correct validation end date for actual termination elements*/
3326 begin
3327 select distinct pet.POST_TERMINATION_RULE
3328 into v_post_termination_rule
3329 from pay_element_links_f pel,
3330 pay_element_types_f pet
3331 where pel.element_type_id=pet.element_type_id
3332 and pel.element_link_id=p_element_link_id
3333 and p_session_date between pet.effective_start_date and pet.effective_end_date
3334 and p_session_date between pel.effective_start_date and pel.effective_end_date;
3335
3336 select min(pos.actual_termination_date)
3337 into v_actual_termination_date
3338 from per_periods_of_service pos,
3339 per_assignments_f pa
3340 where pos.person_id = pa.person_id
3341 and pa.assignment_id = p_assignment_id
3342 and p_session_date
3343 between pa.effective_start_date
3344 and pa.effective_end_date
3345 and pos.actual_termination_date is not null
3346 and pos.actual_termination_date >= p_session_date;
3347
3348 exception
3349 when no_data_found then
3350 hr_utility.trace('Post termination rule is null');
3351 hr_utility.trace('Or Actual termination date is null');
3352 v_post_termination_rule := null;
3353 v_actual_termination_date := null;
3354 end;
3355
3356 /* v_actual_termination_date := return_termination_date(p_assignment_id,p_session_date); */
3357
3358 if v_post_termination_rule = 'A' then
3359 v_validation_end_date :=least(v_validation_end_date,nvl(v_max_eligible_date,v_validation_end_date),nvl(v_actual_termination_date,v_validation_end_date));
3360 else
3361 v_validation_end_date :=least(v_validation_end_date,nvl(v_max_eligible_date,v_validation_end_date));
3362
3363 end if;
3364
3365 end if; /*Bug 8816456 Extended end if for p_dt_delete_mode <> 'ZAP' as in ZAP mode we dont need non_recurring_dates call*/
3366 --
3367 -- wmcveagh, bug 493056. Use element effective start date rather than
3368 -- validation start date if vsd is later.
3369 --
3370 if g_debug then
3371 hr_utility.set_location('hr_entry.chk_element_entry_main', 17);
3372 end if;
3373 --
3374 open c_element_start_date ;
3375 fetch c_element_start_date into v_element_effective_start_date;
3376 close c_element_start_date;
3377 --
3378 hr_utility.trace(' v_element_effective_start_date : '|| v_element_effective_start_date);
3379 --
3380 if v_validation_start_date < v_element_effective_start_date then
3381 if g_debug then
3382 hr_utility.set_location('hr_entry.chk_element_entry_main', 18);
3383 end if;
3384 v_validation_start_date := v_element_effective_start_date;
3385 end if;
3386 --
3387 end if;
3388
3389 hr_utility.trace(' v_validation_start_date : '|| v_validation_start_date);
3390 --
3391 -- Only do check when the entry is being created or extended.
3392 --
3393 if ((p_usage = 'INSERT' or
3394 p_dt_delete_mode = 'FUTURE_CHANGE') or
3395 (p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
3396 p_validation_end_date = hr_general.end_of_time)) then
3397 --
3398 if g_debug then
3399 hr_utility.set_location('hr_entry.chk_element_entry_main', 20);
3400 end if;
3401 -- start 115.26
3402 -- bugfix 1827998
3403 declare
3404 l_validation_start_date date;
3405 l_validation_end_date date;
3406 begin
3407 if p_usage = 'INSERT' then
3408 dt_api.validate_dt_mode(
3409 p_effective_date => p_session_date --*
3410 ,p_datetrack_mode => 'INSERT' --*
3411 ,p_base_table_name => 'pay_element_entries_f'
3412 ,p_base_key_column => 'element_entry_id'
3413 ,p_base_key_value => p_element_entry_id --*
3414 ,p_parent_table_name1 => 'per_all_assignments_f'
3415 ,p_parent_key_column1 => 'assignment_id'
3416 ,p_parent_key_value1 => p_assignment_id
3417 ,p_enforce_foreign_locking => true
3418 ,p_validation_start_date => l_validation_start_date
3419 ,p_validation_end_date => l_validation_end_date);
3420 end if;
3421 end;
3422 -- end 115.26
3423 --
3424 -- Make sure the entry does not overlap if multiple entries are not
3425 -- allowed.
3426 --
3427 if p_creator_type not in ('EE', 'RR', 'AD', 'AE', 'NR', 'PR') then
3428 hr_entry.chk_entry_overlap(p_element_entry_id,
3429 p_assignment_id,
3430 p_element_link_id,
3431 v_processing_type,
3432 p_entry_type,
3433 v_mult_entries_allowed_flag,
3434 v_validation_start_date,
3435 v_validation_end_date,
3436 v_period_start_date,
3437 v_period_end_date);
3438 --
3439 end if;
3440 end if;
3441 --
3442 if g_debug then
3443 hr_utility.set_location('hr_entry.chk_element_entry_main', 25);
3444 end if;
3445 --
3446 -- Call the procedure: chk_element_entry_open
3447 -- This procedure does common checks for insert/update/delete
3448 -- commit actions.
3449 --
3450 -- 10633382 if validation is already passed on adjustment entry don't repeat check on target
3451 if nvl(p_element_entry_id,-2) <> nvl(g_target_entry_id,-1) then
3452 hr_entry.chk_element_entry_open(v_element_type_id,
3453 p_session_date,
3454 v_validation_start_date,
3455 v_validation_end_date,
3456 p_assignment_id);
3457 end if;
3458 g_target_entry_id := p_target_entry_id;
3459
3460 --
3461 -- If inserting an entry which is NOT a standard Recurring entry then
3462 -- perform various date validation.
3463 --
3464 if (p_usage = 'INSERT' and
3465 ((v_processing_type = 'R' and
3466 p_entry_type <> 'E') or
3467 v_processing_type = 'N')) then
3468 --
3469 if g_debug then
3470 hr_utility.set_location('hr_entry.chk_element_entry_main', 30);
3471 end if;
3472 --
3473 -- We must ensure that the entry is eligible to be inserted.
3474 --
3475 -- Bugfix 5135065
3476 -- Pass v_period_start_date and v_period_end_date to
3477 -- chk_element_entry_eligibility
3478 -- These should only have been derived if the entry is non-recurring
3479 -- In this case we can greatly reduce the number of calls made
3480 -- by get_eligibility_period to the expensive assignment_eligible_for_link
3481 -- function.
3482 hr_entry.chk_element_entry_eligibility(p_assignment_id,
3483 p_element_link_id,
3484 p_session_date,
3485 p_usage,
3486 p_creator_type,
3487 v_validation_start_date,
3488 v_validation_end_date,
3489 v_period_start_date,
3490 v_period_end_date,
3491 v_min_date,
3492 v_max_date);
3493 --
3494 end if;
3495 --
3496 -- Additional entry insert checks.
3497 --
3498 if (p_entry_type = 'D' and
3499 p_creator_type not in ('EE', 'RR', 'AD', 'AE', 'NR', 'PR') and
3500 p_usage = 'INSERT') then
3501 --
3502 if g_debug then
3503 hr_utility.set_location('hr_entry.chk_element_entry_main', 35);
3504 end if;
3505 --
3506 -- Additional Entry is trying to be inserted therefore, check for
3507 -- another existing Additional Entry within the current period.
3508 begin
3509 -- INDEX and FIRST_ROWS hints added following NHS project recommendation
3510 select 'Y'
3511 into v_error_flag
3512 from sys.dual
3513 where exists
3514 (select /*+ FIRST_ROWS(1)
3515 INDEX(pee, pay_element_entries_f_n51 */ 1
3516 from pay_element_entries_f pee
3517 where pee.entry_type = p_entry_type
3518 and pee.assignment_id = p_assignment_id
3519 and pee.element_link_id = p_element_link_id
3520 and pee.effective_start_date >= v_period_start_date
3521 and pee.effective_end_date <= v_period_end_date);
3522 exception
3523 when no_data_found then null;
3524 end;
3525 --
3526 if v_error_flag = 'Y' then
3527 hr_utility.set_message(801, 'HR_7700_ELE_ENTRY_REC_EXISTS');
3528 hr_utility.raise_error;
3529 end if;
3530 --
3531 end if;
3532 --
3533 -- Standard recurring entry checks.
3534 --
3535 if ((p_entry_type = 'E' and
3536 v_processing_type = 'R') and
3537 (p_dt_delete_mode = 'ZAP' or
3538 p_dt_delete_mode = 'DELETE')) then
3539 --
3540 if g_debug then
3541 hr_utility.set_location('hr_entry.chk_element_entry_main', 40);
3542 end if;
3543 --
3544 -- If the p_dt_delete_mode is not null then need to ensure that
3545 -- the entry being deleted does not orphan any adjustments.
3546 begin
3547 -- INDEX and FIRST_ROWS hints added following NHS project recommendation
3548 select 'Y'
3549 into v_error_flag
3550 from sys.dual
3551 where exists
3552 (select /*+ FIRST_ROWS(1)
3553 INDEX(pee, pay_element_entries_f_n51 */ 1
3554 from pay_element_entries_f pee
3555 where pee.target_entry_id = p_element_entry_id
3556 and pee.element_link_id = p_element_link_id
3557 and pee.assignment_id = p_assignment_id
3558 and pee.effective_start_date <= v_validation_end_date
3559 and pee.effective_end_date >= v_validation_start_date);
3560 exception
3561 when no_data_found then null;
3562 end;
3563 --
3564 if v_error_flag = 'Y' then
3565 hr_utility.set_message(801, 'HR_6304_ELE_ENTRY_DT_DEL_ADJ');
3566 hr_utility.raise_error;
3567 end if;
3568 --
3569 end if;
3570 --
3571 -- Override entry checks.
3572 --
3573 if (p_entry_type = 'S' and
3574 p_usage = 'INSERT') then
3575 --
3576 if g_debug then
3577 hr_utility.set_location('hr_entry.chk_element_entry_main', 45);
3578 end if;
3579 --
3580 -- Ensure that no other override exists within the current
3581 -- period for the same element for the assignment.
3582 begin
3583 -- INDEX and FIRST_ROWS hints added following NHS project recommendation
3584 select 'Y'
3585 into v_error_flag
3586 from sys.dual
3587 where exists
3588 (select /*+ FIRST_ROWS(1)
3589 INDEX(pee, pay_element_entries_f_n51 */ 1
3590 from pay_element_entries_f pee
3591 where pee.entry_type = 'S'
3592 and pee.assignment_id = p_assignment_id
3593 and pee.element_link_id = p_element_link_id
3594 and pee.effective_start_date >= v_period_start_date
3595 and pee.effective_end_date <= v_period_end_date);
3596 exception
3597 when no_data_found then null;
3598 end;
3599 --
3600 if v_error_flag = 'Y' then
3601 hr_utility.set_message(801, 'HR_6187_ELE_ENTRY_OVER_EXISTS');
3602 hr_utility.raise_error;
3603 end if;
3604 --
3605 if g_debug then
3606 hr_utility.set_location('hr_entry.chk_element_entry_main', 50);
3607 end if;
3608 --
3609 -- Ensure that an adjustment entry does not exist when trying to
3610 -- insert override.
3611 --
3612 begin
3613 -- INDEX and FIRST_ROWS hints added following NHS project recommendation
3614 select 'Y'
3615 into v_error_flag
3616 from sys.dual
3617 where exists
3618 (select /*+ FIRST_ROWS(1)
3619 INDEX(pee, pay_element_entries_f_n51 */ 1
3620 from pay_element_entries_f pee
3621 where pee.entry_type in ('R','A')
3622 and pee.assignment_id = p_assignment_id
3623 and pee.element_link_id = p_element_link_id
3624 and pee.effective_start_date >= v_period_start_date
3625 and pee.effective_end_date <= v_period_end_date);
3626 exception
3627 when no_data_found then null;
3628 end;
3629 --
3630 if v_error_flag = 'Y' then
3631 hr_utility.set_message(801, 'HR_6189_ELE_ENTRY_ADJ_EXISTS');
3632 hr_utility.raise_error;
3633 end if;
3634 --
3635 -- You are not allowed to insert an override entry
3636 -- for an element type that has the third party only
3637 -- flag set to 'Y'.
3638 if v_third_party_pay_only_flag = 'Y' then
3639 hr_utility.set_message(801, 'PAY_289106_CANT_OVER_THIRD');
3640 hr_utility.raise_error;
3641 end if;
3642 --
3643 end if;
3644 --
3645 -- Standard adjustment entry insert checks.
3646 --
3647 if ((p_entry_type = 'R' or
3648 p_entry_type = 'A') and
3649 p_usage = 'INSERT') then
3650 --
3651 if g_debug then
3652 hr_utility.set_location('hr_entry.chk_element_entry_main', 55);
3653 end if;
3654 --
3655 -- Ensure that the parent entry of the adjustment exists for the
3656 -- duration of the adjustment.
3657 --
3658 begin
3659 -- FIRST_ROWS hint added following NHS project recommendation
3660 select 'Y'
3661 into v_error_flag
3662 from sys.dual
3663 where not exists
3664 (select /*+ FIRST_ROWS(1) */ 1
3665 from pay_element_entries_f pee
3666 where pee.assignment_id = p_assignment_id
3667 and pee.element_entry_id = p_target_entry_id
3668 having min(pee.effective_start_date) <=
3669 v_validation_start_date
3670 and max(pee.effective_end_date) >=
3671 v_validation_end_date);
3672 exception
3673 when no_data_found then null;
3674 end;
3675 --
3676 if v_error_flag = 'Y' then
3677 hr_utility.set_message(801, 'HR_6194_ELE_ENTRY_ADJ_PARENT');
3678 hr_utility.raise_error;
3679 end if;
3680 --
3681 if g_debug then
3682 hr_utility.set_location('hr_entry.chk_element_entry_main', 60);
3683 end if;
3684 --
3685 -- Ensure that an override does not exist for the entry which is to be
3686 -- adjusted.
3687 begin
3688 -- INDEX and FIRST_ROWS hints added following NHS project recommendation
3689 select 'Y'
3690 into v_error_flag
3691 from sys.dual
3692 where exists
3693 (select /*+ FIRST_ROWS(1)
3694 INDEX(pee, pay_element_entries_f_n51 */ 1
3695 from pay_element_entries_f pee
3696 where pee.entry_type = 'S'
3697 and pee.assignment_id = p_assignment_id
3698 and pee.element_link_id = p_element_link_id
3699 and pee.effective_start_date >= v_period_start_date
3700 and pee.effective_end_date <= v_period_end_date);
3701 --
3702 exception
3703 when no_data_found then null;
3704 end;
3705 --
3706 if v_error_flag = 'Y' then
3707 hr_utility.set_message(801, 'HR_6195_ELE_ENTRY_OADJ_EXISTS');
3708 hr_utility.raise_error;
3709 end if;
3710 --
3711 if g_debug then
3712 hr_utility.set_location('hr_entry.chk_element_entry_main', 65);
3713 end if;
3714 --
3715 -- Ensure that an existing adjustment for this entry does not exist for
3716 -- the current period/assignment.
3717 begin
3718 -- FIRST_ROWS hint added following NHS project recommendation
3719 select 'Y'
3720 into v_error_flag
3721 from sys.dual
3722 where exists
3723 (select /*+ FIRST_ROWS(1) */ 1
3724 from pay_element_entries_f pee
3725 where pee.entry_type in ('R','A')
3726 and pee.assignment_id = p_assignment_id
3727 and pee.target_entry_id = p_target_entry_id
3728 and pee.effective_start_date >= v_period_start_date
3729 and pee.effective_end_date <= v_period_end_date);
3730 --
3731 exception
3732 when no_data_found then null;
3733 end;
3734 --
3735 if v_error_flag = 'Y' then
3736 hr_utility.set_message(801, 'HR_6196_ELE_ENTRY_ADJ_EXISTS');
3737 hr_utility.raise_error;
3738 end if;
3739 --
3740 end if;
3741 --
3742 -- Return the effective start and end dates of the entry being
3743 -- vslidated NB. this is only valid in certain circumstances ie.
3744 --
3745 -- DT Mode = INSERT
3746 -- Valid dates = p_effective_start_date / p_effective_end_date
3747 -- '' = DELETE_NEXT_CHANGE and validation_end_date = EOT
3748 -- Valid dates = p_effective_end_date
3749 -- '' = DELETE_FUTURE_CHANGES
3750 -- Valid dates = p_effective_end_date
3751 --
3752 -- In all other cases the dates are not valid and should not beused to
3753 -- populate EFECTIVE_START_DATE and EFFECTIVE_END_DATE of the row being
3754 -- processed.
3755 --
3756 if p_entry_type = 'E' and
3757 v_processing_type = 'R' and
3758 ((p_usage = 'INSERT' or
3759 p_dt_delete_mode = 'FUTURE_CHANGE') or
3760 (p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
3761 p_validation_end_date = hr_general.end_of_time)) then
3762 p_effective_end_date := v_validation_end_date;
3763 elsif (p_entry_type = 'E' and v_processing_type = 'N') or
3764 p_entry_type <> 'E' then
3765 p_effective_start_date := greatest(v_validation_start_date,
3766 nvl(v_min_date, v_validation_start_date));
3767 p_effective_end_date := least(v_validation_end_date,
3768 nvl(v_max_date, v_validation_end_date));
3769 end if;
3770 --
3771 end chk_element_entry_main;
3772 --
3773 -- NAME
3774 -- hr_entry.ins_3p_ent_values
3775 --
3776 -- DESCRIPTION
3777 -- This function is used for third party inserts into:
3778 -- PAY_ELEMENT_ENTRIES_F (If an abscence, or DT functions are being used).
3779 -- PAY_ELEMENT_ENTRY_VALUES_F (Entry Values are always inserted).
3780 -- PAY_RUN_RESULTS (If nonrecurring).
3781 -- PAY_RUN_RESULT_VBALUES (If nonrecurring).
3782 --
3783 --
3784 procedure ins_3p_ent_values
3785 (
3786 p_element_link_id number,
3787 p_element_entry_id number,
3788 p_session_date date,
3789 p_num_entry_values number,
3790 p_input_value_id_tbl hr_entry.number_table,
3791 p_entry_value_tbl hr_entry.varchar2_table
3792 ) is
3793 --
3794 -- cursor to fetch balance adjustment element entry values
3795 cursor get_b_eevs(p_element_entry_id number,
3796 p_session_date date ) is
3797 select peev.input_value_id,
3798 piv.uom,
3799 peev.screen_entry_value value
3800 from pay_input_values_f piv,
3801 pay_element_entry_values_f peev
3802 where peev.element_entry_id = p_element_entry_id
3803 and piv.input_value_id = peev.input_value_id
3804 and p_session_date between peev.effective_start_date
3805 and peev.effective_end_date
3806 and p_session_date between piv.effective_start_date
3807 and piv.effective_end_date;
3808 -- Local variables
3809 v_run_result_id number;
3810 v_status varchar2(1);
3811 v_exchange_rate number(20,10);
3812 v_element_entry_id number;
3813 v_assignment_id number;
3814 v_entry_type varchar2(30);
3815 v_creator_type varchar2(30);
3816 v_creator_id number;
3817 v_effective_start_date date;
3818 v_effective_end_date date;
3819 v_cost_allocation_keyflex_id number;
3820 v_element_type_id number;
3821 v_processing_type varchar2(30);
3822 v_input_currency_code varchar2(30);
3823 v_output_currency_code varchar2(30);
3824 v_entry_count number := 0;
3825 v_jurisdiction varchar2(30);
3826 v_currency_type varchar2(30);
3827 v_bg_id number(16);
3828 v_amount number;
3829
3830
3831 --
3832 begin
3833 --
3834 if g_debug then
3835 hr_utility.set_location('hr_entry.ins_3p_entry_values', 1);
3836 end if;
3837 begin
3838 --
3839 select ee.element_entry_id,
3840 ee.assignment_id,
3841 ee.entry_type,
3842 ee.creator_type,
3843 ee.creator_id,
3844 ee.effective_start_date,
3845 ee.effective_end_date,
3846 ee.cost_allocation_keyflex_id,
3847 et.element_type_id,
3848 et.processing_type,
3849 et.input_currency_code,
3850 et.output_currency_code
3851 into v_element_entry_id,
3852 v_assignment_id,
3853 v_entry_type,
3854 v_creator_type,
3855 v_creator_id,
3856 v_effective_start_date,
3857 v_effective_end_date,
3858 v_cost_allocation_keyflex_id,
3859 v_element_type_id,
3860 v_processing_type,
3861 v_input_currency_code,
3862 v_output_currency_code
3863 from pay_element_entries_f ee,
3864 pay_element_links_f el,
3865 pay_element_types_f et
3866 where ee.element_entry_id = p_element_entry_id
3867 and el.element_link_id = ee.element_link_id
3868 and et.element_type_id = el.element_type_id
3869 and p_session_date between ee.effective_start_date
3870 and ee.effective_end_date
3871 and p_session_date between el.effective_start_date
3872 and el.effective_end_date
3873 and p_session_date between et.effective_start_date
3874 and et.effective_end_date;
3875 --
3876 exception
3877 when NO_DATA_FOUND then
3878 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3879 hr_utility.set_message_token('PROCEDURE',
3880 'hr_entry.ins_3p_entry_values');
3881 hr_utility.set_message_token('STEP','6');
3882 hr_utility.raise_error;
3883 end;
3884 --
3885 if g_debug then
3886 hr_utility.set_location('hr_entry.ins_3p_entry_values', 2);
3887 end if;
3888 --
3889 -- Insert all the entry values if there are any.
3890 if p_num_entry_values > 0 then
3891 --
3892 -- NB. mandatory checks are not applied to adjustments.
3893 if not v_entry_type in ('A','R') then
3894 --
3895 hr_entry.chk_mandatory_entry_values
3896 (p_element_link_id,
3897 p_session_date,
3898 p_num_entry_values,
3899 p_input_value_id_tbl,
3900 p_entry_value_tbl);
3901 --
3902 end if;
3903 --
3904 -- See how many entry rows currently exist NB. if there is more than one
3905 -- then the the entry is being updated otherwise it is being inserted.
3906 begin
3907 select count(*)
3908 into v_entry_count
3909 from pay_element_entries_f ee
3910 where ee.element_entry_id = p_element_entry_id;
3911 exception
3912 when no_data_found then null;
3913 end;
3914 --
3915 -- Element entry is being created so need to create new element entry
3916 -- values.
3917 if v_entry_count = 1 then
3918 --
3919 -- Bug 4347283. Changed to use forall for bulk insert.
3920 --
3921 forall v_loop in 1..p_num_entry_values
3922 --
3923 insert into pay_element_entry_values_f
3924 (element_entry_value_id,
3925 effective_start_date,
3926 effective_end_date,
3927 input_value_id,
3928 element_entry_id,
3929 screen_entry_value)
3930 values
3931 (pay_element_entry_values_s.nextval,
3932 v_effective_start_date,
3933 v_effective_end_date,
3934 p_input_value_id_tbl(v_loop),
3935 p_element_entry_id,
3936 p_entry_value_tbl(v_loop));
3937 --
3938 -- Element entry is being updated so need to copy the element entry
3939 -- values.
3940 else
3941 --
3942 -- Bug 4347283. Changed to use forall for bulk insert.
3943 --
3944 forall v_loop in 1..p_num_entry_values
3945 --
3946 --
3947 -- WWbug 273821
3948 -- Added + 0 to eev.input_value_id to disable the use of
3949 -- index PAY_ELEMENT_ENTRY_VALUES_F_N1
3950 --
3951 insert into pay_element_entry_values_f
3952 (element_entry_value_id,
3953 effective_start_date,
3954 effective_end_date,
3955 input_value_id,
3956 element_entry_id,
3957 screen_entry_value)
3958 select
3959 eev.element_entry_value_id,
3960 v_effective_start_date,
3961 v_effective_end_date,
3962 eev.input_value_id,
3963 p_element_entry_id,
3964 p_entry_value_tbl(v_loop)
3965 from pay_element_entry_values_f eev
3966 where eev.element_entry_id = p_element_entry_id
3967 and eev.input_value_id + 0 = p_input_value_id_tbl(v_loop)
3968 and p_session_date - 1 between eev.effective_start_date
3969 and eev.effective_end_date;
3970 --
3971 end if;
3972 --
3973 end if;
3974 --
3975 -- If the entry is nonrecurring ONLY or is a balance adjustment then insert
3976 -- run result and run result values providing the input currency =
3977 -- output currency and the element can be processed in a run.
3978 --
3979 -- Enhancement 3205906
3980 -- We no longer wish to create run results automatically for nonrecurring
3981 -- entries or balance adjustments.
3982 --
3983 /*
3984 if ((v_entry_type = 'E' and
3985 v_processing_type = 'N' and
3986 v_input_currency_code = v_output_currency_code) or
3987 v_entry_type = 'B') then
3988 --
3989 -- Ensure that the entry can be processed in a run.
3990 if hr_entry.entry_process_in_run(v_element_type_id, p_session_date) then
3991 --
3992 -- Set the processing status. If the entry is a balance adjustment then
3993 -- the run result status must be set to processed.
3994 if v_entry_type = 'B' then
3995 --
3996 v_status := 'P';
3997 --
3998 -- If the input currency <> output currency then select the exchange
3999 -- rate
4000 if v_input_currency_code <> v_output_currency_code then
4001 begin
4002 if g_debug then
4003 hr_utility.set_location('hr_entry.ins_3p_entry_values', 4);
4004 end if;
4005 --
4006 select business_group_id
4007 into v_bg_id
4008 from per_assignments_f pas
4009 where pas.assignment_id = v_assignment_id
4010 and p_session_date between pas.effective_start_date
4011 and pas.effective_end_date
4012 and rownum=1;
4013 --
4014 v_currency_type:=hr_currency_pkg.get_rate_type
4015 (v_bg_id,p_session_date,'P');
4016 if (v_currency_type is NULL)
4017 then
4018 hr_utility.set_message(801,'HR_52349_NO_RATE_TYPE');
4019 hr_utility.raise_error;
4020 end if;
4021 end;
4022 --
4023 end if;
4024 --
4025 else
4026 --
4027 v_status := 'U';
4028 --
4029 end if;
4030 --
4031 -- Get the next sequenced run_result_id. Step 5.
4032 v_run_result_id := hr_entry.generate_run_result_id;
4033 --
4034 -- Insert Run Result. Step 6.
4035 if g_debug then
4036 hr_utility.set_location('hr_entry.ins_3p_entry_values', 6);
4037 end if;
4038 -- First get the Jurisdiction if one exists.
4039 begin
4040 select eev.screen_entry_value
4041 into v_jurisdiction
4042 from pay_element_entry_values_f eev,
4043 pay_input_values_f piv,
4044 pay_element_entries_f pee
4045 where pee.element_entry_id = v_element_entry_id
4046 and eev.element_entry_id = pee.element_entry_id
4047 and eev.input_value_id = piv.input_value_id
4048 and piv.name = 'Jurisdiction'
4049 and p_session_date between pee.effective_start_date
4050 and pee.effective_end_date
4051 and p_session_date between eev.effective_start_date
4052 and eev.effective_end_date
4053 and p_session_date between piv.effective_start_date
4054 and piv.effective_end_date;
4055 exception
4056 when no_data_found then
4057 v_jurisdiction := null;
4058 end;
4059 --
4060 begin
4061 --
4062 insert into pay_run_results
4063 (run_result_id,
4064 element_type_id,
4065 assignment_action_id,
4066 entry_type,
4067 source_id,
4068 source_type,
4069 status,
4070 jurisdiction_code)
4071 values
4072 (v_run_result_id,
4073 v_element_type_id,
4074 null,
4075 v_entry_type,
4076 v_element_entry_id,
4077 'E',
4078 v_status,
4079 v_jurisdiction);
4080 --
4081 exception
4082 when NO_DATA_FOUND then
4083 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4084 hr_utility.set_message_token('PROCEDURE',
4085 'hr_entry.ins_3p_entry_values');
4086 hr_utility.set_message_token('STEP','6');
4087 hr_utility.raise_error;
4088 end;
4089 --
4090 -- Insert Run Result Values. Step 7.
4091 if (v_entry_type = 'B' and
4092 v_input_currency_code <> v_output_currency_code) then
4093 --
4094 -- insert run results values converting all money uom's to the output
4095 -- currency value.
4096 if g_debug then
4097 hr_utility.set_location('hr_entry.ins_3p_entry_values', 7);
4098 end if;
4099 begin
4100
4101 for peev in get_b_eevs(p_element_entry_id, p_session_date) loop
4102
4103 if (peev.uom='M')
4104 then
4105 begin
4106 v_amount:=hr_currency_pkg.convert_amount(v_input_currency_code,
4107 v_output_currency_code,
4108 p_session_date,
4109 peev.value,
4110 v_currency_type);
4111 exception
4112 when gl_currency_api.NO_RATE then
4113 hr_utility.set_message(801,'HR_6405_PAYM_NO_EXCHANGE_RATE');
4114 hr_utility.set_message_token('RATE1', v_input_currency_code);
4115 hr_utility.set_message_token('RATE2', v_output_currency_code);
4116 hr_utility.raise_error;
4117 when gl_currency_api.INVALID_CURRENCY then
4118 hr_utility.set_message(801,'HR_52350_INVALID_CURRENCY');
4119 hr_utility.set_message_token('RATE1', v_input_currency_code);
4120 hr_utility.set_message_token('RATE2', v_output_currency_code);
4121 hr_utility.raise_error;
4122 end;
4123 else
4124 v_amount:=peev.value;
4125 end if;
4126 --
4127 insert into pay_run_result_values
4128 (input_value_id,
4129 run_result_id,
4130 result_value)
4131 values
4132 (peev.input_value_id,
4133 v_run_result_id,
4134 v_amount);
4135 end loop;
4136 --
4137 exception
4138 when no_data_found then
4139 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4140 hr_utility.set_message_token('PROCEDURE',
4141 'hr_entry.ins_3p_entry_values');
4142 hr_utility.set_message_token('STEP','7');
4143 hr_utility.raise_error;
4144 end;
4145
4146 --
4147 -- insert run result values which do not have to be converted.
4148 else
4149 --
4150 if g_debug then
4151 hr_utility.set_location('hr_entry.ins_3p_entry_values', 8);
4152 end if;
4153 begin
4154 --
4155 insert into pay_run_result_values
4156 (input_value_id,
4157 run_result_id,
4158 result_value)
4159 select
4160 peev.input_value_id,
4161 v_run_result_id,
4162 peev.screen_entry_value
4163 from pay_element_entry_values_f peev
4164 where peev.element_entry_id = v_element_entry_id
4165 and p_session_date between peev.effective_start_date
4166 and peev.effective_end_date;
4167 --
4168 exception
4169 when no_data_found then
4170 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4171 hr_utility.set_message_token('PROCEDURE',
4172 'hr_entry.ins_3p_entry_values');
4173 hr_utility.set_message_token('STEP','8');
4174 hr_utility.raise_error;
4175 end;
4176 --
4177 end if;
4178 --
4179 end if;
4180 --
4181 end if;
4182 */
4183 -- End of Enhancement 3205906
4184 --
4185 end ins_3p_ent_values;
4186 --
4187 procedure ins_3p_entry_values
4188 (
4189 p_element_link_id number,
4190 p_element_entry_id number,
4191 p_session_date date,
4192 p_num_entry_values number,
4193 p_input_value_id_tbl hr_entry.number_table,
4194 p_entry_value_tbl hr_entry.varchar2_table
4195 ) is
4196 --
4197 -- Local Variables
4198 v_num_entry_values number;
4199 v_input_value_id_tbl hr_entry.number_table;
4200 v_entry_value_tbl hr_entry.varchar2_table;
4201 --
4202 begin
4203 --
4204 v_num_entry_values := p_num_entry_values;
4205 v_input_value_id_tbl := p_input_value_id_tbl;
4206 v_entry_value_tbl := p_entry_value_tbl;
4207 --
4208 -- hr_entry_api.conv_table_to_table
4209 -- ('DB',
4210 -- p_session_date,
4211 -- null,
4212 -- p_element_link_id,
4213 -- v_num_entry_values,
4214 -- v_input_value_id_tbl,
4215 -- v_entry_value_tbl);
4216 --
4217 hr_entry.ins_3p_ent_values
4218 (p_element_link_id,
4219 p_element_entry_id,
4220 p_session_date,
4221 v_num_entry_values,
4222 v_input_value_id_tbl,
4223 v_entry_value_tbl);
4224 --
4225 end ins_3p_entry_values;
4226 --
4227 procedure ins_3p_entry_values
4228 (
4229 p_element_link_id number,
4230 p_element_entry_id number,
4231 p_session_date date,
4232 /** sbilling **/
4233 p_creator_type varchar2,
4234 p_entry_type varchar2,
4235 p_input_value_id1 number,
4236 p_input_value_id2 number,
4237 p_input_value_id3 number,
4238 p_input_value_id4 number,
4239 p_input_value_id5 number,
4240 p_input_value_id6 number,
4241 p_input_value_id7 number,
4242 p_input_value_id8 number,
4243 p_input_value_id9 number,
4244 p_input_value_id10 number,
4245 p_input_value_id11 number,
4246 p_input_value_id12 number,
4247 p_input_value_id13 number,
4248 p_input_value_id14 number,
4249 p_input_value_id15 number,
4250 p_entry_value1 varchar2,
4251 p_entry_value2 varchar2,
4252 p_entry_value3 varchar2,
4253 p_entry_value4 varchar2,
4254 p_entry_value5 varchar2,
4255 p_entry_value6 varchar2,
4256 p_entry_value7 varchar2,
4257 p_entry_value8 varchar2,
4258 p_entry_value9 varchar2,
4259 p_entry_value10 varchar2,
4260 p_entry_value11 varchar2,
4261 p_entry_value12 varchar2,
4262 p_entry_value13 varchar2,
4263 p_entry_value14 varchar2,
4264 p_entry_value15 varchar2
4265 ) is
4266 --
4267 -- Local variables
4268 v_num_entry_values number;
4269 v_input_value_id_tbl hr_entry.number_table;
4270 v_entry_value_tbl hr_entry.varchar2_table;
4271 --
4272 begin
4273 --
4274 -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
4275 -- into two tables to be passed into the overloaded version of
4276 -- ins_3p_entry_values. The overloaded version is capable of handling
4277 -- unlimited numbers of entry values.
4278 hr_entry_api.conv_entry_values_to_table
4279 ('DB',
4280 null,
4281 p_element_link_id,
4282 p_session_date,
4283 /** sbilling **/
4284 p_creator_type,
4285 p_entry_type,
4286 p_input_value_id1,
4287 p_input_value_id2,
4288 p_input_value_id3,
4289 p_input_value_id4,
4290 p_input_value_id5,
4291 p_input_value_id6,
4292 p_input_value_id7,
4293 p_input_value_id8,
4294 p_input_value_id9,
4295 p_input_value_id10,
4296 p_input_value_id11,
4297 p_input_value_id12,
4298 p_input_value_id13,
4299 p_input_value_id14,
4300 p_input_value_id15,
4301 p_entry_value1,
4302 p_entry_value2,
4303 p_entry_value3,
4304 p_entry_value4,
4305 p_entry_value5,
4306 p_entry_value6,
4307 p_entry_value7,
4308 p_entry_value8,
4309 p_entry_value9,
4310 p_entry_value10,
4311 p_entry_value11,
4312 p_entry_value12,
4313 p_entry_value13,
4314 p_entry_value14,
4315 p_entry_value15,
4316 v_num_entry_values,
4317 v_input_value_id_tbl,
4318 v_entry_value_tbl);
4319 --
4320 hr_entry.ins_3p_ent_values
4321 (p_element_link_id,
4322 p_element_entry_id,
4323 p_session_date,
4324 v_num_entry_values,
4325 v_input_value_id_tbl,
4326 v_entry_value_tbl);
4327 --
4328 end ins_3p_entry_values;
4329 --
4330
4331 -------------------------------------------------------------------------
4332 -- NAME delete_covered_dependants
4333 --
4334 -- DESCRIPTION Deals with calls to update the covered dependents of an
4335 -- Element entry.
4336 -- Called by : hr_entry.del_3p_entry_values
4337 -- : hrentmnt.validate_adjust_entry
4338 --
4339 ----------------------------------------------------------------------------
4340
4341 procedure delete_covered_dependants
4342
4343 (p_validation_start_date date,
4344 p_element_entry_id number,
4345 p_start_date date DEFAULT NULL,
4346 p_end_date date DEFAULT NULL) is
4347 --
4348 -- Set of covered dependants which are children of the deleted element entry
4349 -- and which overlap or are later than the deletion date
4350 -- (in the case of zap this will be all children because
4351 -- p_validation_start_date will be the beginning of time).
4352 -- If p_end_date is NULL change the start date of the covered dependent
4353 cursor csr_covered_dependents is
4354 select rowid,
4355 dep.*
4356 from ben_covered_dependents_f DEP
4357 where dep.effective_end_date >= p_validation_start_date
4358 and dep.element_entry_id = p_element_entry_id;
4359 --
4360
4361 v_start_date date;
4362 v_end_date date;
4363
4364 begin
4365 --
4366 for dependant in csr_covered_dependents LOOP
4367 --
4368 if dependant.effective_start_date >= p_validation_start_date then
4369 --
4370 -- If the dependant starts after the entry deletion date then delete it
4371 --
4372 ben_covered_dependents_pkg.delete_row (dependant.rowid);
4373 --
4374 else
4375 --
4376 -- The dependant must overlap the entry deletion date so update it to
4377 -- have the same end date. NB This will only apply to DELETE mode
4378 -- because ZAP will have a validation_start_date of the beginning of
4379 -- time and so this condition will never be satisfied.
4380 --
4381
4382 v_start_date := NVL(p_start_date,dependant.effective_start_date);
4383 v_end_date := NVL(p_end_date,dependant.effective_end_date);
4384
4385 ben_covered_dependents_pkg.update_row (
4386 --
4387 p_covered_dependent_id => dependant.covered_dependent_id,
4388 p_rowid => dependant.rowid,
4389 p_contact_relationship_id => dependant.contact_relationship_id,
4390 p_element_entry_id => dependant.element_entry_id,
4391 p_effective_start_date => v_start_date,
4392 p_effective_end_date => v_end_date);
4393 --
4394 end if;
4395 --
4396 end loop;
4397 --
4398 end delete_covered_dependants;
4399
4400 ----------------------------------------------------------------------------
4401 --
4402 -- NAME hr_entry.delete_beneficiaries
4403 --
4404 -- DESCRIPTION deals with calls to update the beneficiaries of a given
4405 -- element entry.
4406 -- Called by :- hr_entry.del_3p_entry_values
4407 -- hrentmnt.validate_adjust_entry
4408 --
4409 ----------------------------------------------------------------------------
4410
4411 procedure delete_beneficiaries
4412 (p_validation_start_date date,
4413 p_element_entry_id number,
4414 p_start_date date DEFAULT NULL,
4415 p_end_date date DEFAULT NULL)
4416
4417 is
4418 --
4419 -- Set of beneficiaries which are children of the deleted element entry
4420 -- and which overlap or are later than the deletion date
4421 -- (in the case of zap this will be all children because
4422 -- p_validation_start_date will be the beginning of time).
4423 -- If p_end_date is null change the start date of the covered beneficiary
4424
4425 cursor csr_beneficiaries is
4426 select rowid,
4427 ben.*
4428 from ben_beneficiaries_f BEN
4429 where ben.effective_end_date >= p_validation_start_date
4430 and ben.element_entry_id = p_element_entry_id;
4431 --
4432 v_start_date date;
4433 v_end_date date;
4434
4435 begin
4436 --
4437 for entry_beneficiary in csr_beneficiaries LOOP
4438 --
4439 if entry_beneficiary.effective_start_date >= p_validation_start_date then
4440 --
4441 -- If the beneficiary starts after the entry deletion date then delete it
4442 --
4443 ben_beneficiaries_pkg.delete_row (entry_beneficiary.rowid);
4444 --
4445 else
4446 --
4447 -- The beneficiary must overlap the entry deletion date so update it to
4448 -- have the same end date. NB This will only apply to DELETE mode
4449 -- because ZAP will have a validation_start_date of the beginning of
4450 -- time and so this condition will never be satisfied.
4451 --
4452
4453 v_start_date := NVL(p_start_date,entry_beneficiary.effective_start_date);
4454 v_end_date := NVL(p_end_date,entry_beneficiary.effective_end_date);
4455
4456 ben_beneficiaries_pkg.update_row (
4457 --
4458 p_rowid => entry_beneficiary.rowid,
4459 p_source_type => entry_beneficiary.source_type,
4460 p_source_id => entry_beneficiary.source_id,
4461 p_element_entry_id => entry_beneficiary.element_entry_id,
4462 p_benefit_level => entry_beneficiary.benefit_level,
4463 p_proportion => entry_beneficiary.proportion,
4464 p_beneficiary_id => entry_beneficiary.beneficiary_id,
4465 p_effective_start_date => v_start_date,
4466 p_effective_end_date => v_end_date);
4467 --
4468 end if;
4469 --
4470 end loop;
4471 --
4472 end delete_beneficiaries;
4473 -------------------------
4474
4475 -- NAME
4476 -- hr_entry.del_3p_entry_values
4477 --
4478 -- DESCRIPTION
4479 -- This procedure is used for third party deletes from:
4480 -- PAY_ELEMENT_ENTRIES_F (If an abscence, or DT functions are being used).
4481 -- PAY_ELEMENT_ENTRY_VALUES_F (Entry Values are always deleted).
4482 -- PAY_RUN_RESULTS (If nonrecurring, and exist).
4483 -- PAY_RUN_RESULT_VALUES (If nonrecurring, and exist).
4484 -- BEN_COVERED_DEPENDENTS_F (sic)
4485 -- BEN_BENEFICIARIES_F
4486 --
4487
4488 PROCEDURE del_3p_entry_values
4489 (
4490 p_assignment_id in number,
4491 p_element_entry_id in number,
4492 p_element_type_id in number,
4493 p_element_link_id in number,
4494 p_entry_type in varchar2,
4495 p_processing_type in varchar2,
4496 p_creator_type in varchar2,
4497 p_creator_id in varchar2,
4498 p_dt_delete_mode in varchar2,
4499 p_session_date in date,
4500 p_validation_start_date in date,
4501 p_validation_end_date in date
4502 ) is
4503 --------------------------------------
4504 --
4505 -- Local Variables
4506 --
4507 v_dt_delete_mode varchar2(30);
4508 v_status varchar2(30);
4509 v_process_in_run_flag varchar2(1);
4510 --------------------------------------
4511
4512 procedure extend_beneficiaries is
4513 --
4514 -- Extend the end dates of child beneficiaries
4515 --
4516 cursor csr_beneficiaries is
4517 --
4518 -- Fetch all child beneficiaries which have an end date the same as the
4519 -- parent entry's end date prior to the extension of the end date.
4520 --
4521 select rowid,
4522 ben.*
4523 from ben_beneficiaries_f BEN
4524 where ben.element_entry_id = p_element_entry_id
4525 and ben.effective_end_date = p_validation_start_date -1;
4526 --
4527 begin
4528 --
4529 if p_validation_end_date = hr_general.end_of_time then
4530 --
4531 -- If the validation end date is the end of time then we are on the
4532 -- last date-effective row and we must be extending it out to the end
4533 -- of time. In this case only, also extend the child beneficiary rows
4534 -- which end on the same date as the entry.
4535 --
4536 for entry_beneficiary in csr_beneficiaries LOOP
4537 --
4538 ben_beneficiaries_pkg.update_row (
4539 --
4540 p_rowid => entry_beneficiary.rowid,
4541 p_source_type => entry_beneficiary.source_type,
4542 p_source_id => entry_beneficiary.source_id,
4543 p_element_entry_id => entry_beneficiary.element_entry_id,
4544 p_benefit_level => entry_beneficiary.benefit_level,
4545 p_proportion => entry_beneficiary.proportion,
4546 p_beneficiary_id => entry_beneficiary.beneficiary_id,
4547 p_effective_start_date =>entry_beneficiary.effective_start_date,
4548 p_effective_end_date => p_validation_end_date);
4549 --
4550 end loop;
4551 --
4552 end if;
4553 --
4554 end extend_beneficiaries;
4555 -------------------------
4556 procedure extend_dependants is
4557 --
4558 -- Extend the end dates of the child dependants
4559 --
4560 cursor csr_dependants is
4561 --
4562 -- Fetch all child dependants which have an end date the same as the
4563 -- parent entry's end date prior to the extension of the end date.
4564 --
4565 select rowid,
4566 dep.*
4567 from ben_covered_dependents_f DEP
4568 where dep.element_entry_id = p_element_entry_id
4569 and dep.effective_end_date = p_validation_start_date -1;
4570 --
4571 begin
4572 --
4573 if p_validation_start_date = hr_general.end_of_time then
4574 --
4575 -- If the validation end date is the end of time then we are on the
4576 -- last date-effective row and we must be extending it out to the end
4577 -- of time. In this case only, also extend the child dependant rows
4578 -- which end on the same date as the entry.
4579 --
4580 for dependant in csr_dependants LOOP
4581 --
4582 ben_covered_dependents_pkg.update_row (
4583 --
4584 p_covered_dependent_id => dependant.covered_dependent_id,
4585 p_rowid => dependant.rowid,
4586 p_contact_relationship_id=> dependant.contact_relationship_id,
4587 p_element_entry_id => dependant.element_entry_id,
4588 p_effective_start_date => dependant.effective_start_date,
4589 p_effective_end_date => p_validation_end_date);
4590 --
4591 end loop;
4592 --
4593 end if;
4594 --
4595 end extend_dependants;
4596 ----------------------
4597 begin
4598 g_debug := hr_utility.debug_enabled;
4599 --
4600 if g_debug then
4601 hr_utility.set_location('hr_entry.del_3p_entry_values', 1);
4602 end if;
4603 --
4604 -- Fix for 1904110.
4605 -- The delete mode is always set to the parameter passed in because
4606 -- the delete of the entry values should always use the same mode
4607 -- as the entry. See hr_entry_api.del_ele_entry_param_val
4608 --
4609 v_dt_delete_mode := p_dt_delete_mode;
4610 --
4611 if (v_dt_delete_mode = 'ZAP'
4612 or v_dt_delete_mode = 'DELETE') then
4613 --
4614 -- Delete rows in child tables which would be orphaned by the entry deletion
4615 --
4616 -- Bug fix 519738 - call procedure with new list of parameters, p_start_date defaults to null
4617 hr_entry.delete_beneficiaries(
4618 p_element_entry_id => p_element_entry_id,
4619 p_end_date => p_session_date,
4620 p_validation_start_date => p_validation_start_date);
4621 -- Bug fix 519738 - call procedure with new list of parameters, p_start_date defaults to NULL
4622 hr_entry.delete_covered_dependants(
4623 p_element_entry_id => p_element_entry_id,
4624 p_end_date => p_session_date,
4625 p_validation_start_date => p_validation_start_date);
4626 --
4627 -- Find out if the element is processable in a payroll run
4628 --
4629 if hr_entry.entry_process_in_run(p_element_type_id, p_session_date) then
4630 v_process_in_run_flag := 'Y';
4631 else
4632 v_process_in_run_flag := 'N';
4633 end if;
4634 --
4635 if v_dt_delete_mode = 'ZAP' then
4636 --
4637 -- Enhancement 3205906
4638 -- No longer need to delete run results for nonrecurring entries and
4639 -- balance adjustments. These are no longer automatically created.
4640 /*
4641 if (p_processing_type = 'N' or
4642 p_entry_type = 'B') then
4643 --
4644 -- Check to see if the entry is a balance adjustment. If, yes then
4645 -- set the status to 'P' for processed.
4646 --
4647 if p_entry_type = 'B' then
4648 v_status := 'P';
4649 else
4650 v_status := 'U';
4651 end if;
4652 --
4653 -- delete any run result values providing the entry can be processed
4654 -- in a payroll run.
4655 --
4656 -- DT_DELETE_MODE: ZAP
4657 -- Step 2:
4658 --
4659 if v_process_in_run_flag = 'Y' then
4660 if g_debug then
4661 hr_utility.set_location('hr_entry.del_3p_entry_values', 2);
4662 end if;
4663 begin
4664 delete from pay_run_result_values rrv
4665 where rrv.run_result_id =
4666 (select rr.run_result_id
4667 from pay_run_results rr
4668 where rr.element_type_id + 0 = p_element_type_id
4669 and rr.entry_type = p_entry_type
4670 and rr.source_id = p_element_entry_id
4671 and rr.source_type = 'E'
4672 and rr.status = v_status);
4673 end;
4674 --
4675 -- delete any unprocessed run results.
4676 -- DT_DELETE_MODE: ZAP
4677 -- Step 3:
4678 --
4679 if g_debug then
4680 hr_utility.set_location('hr_entry.del_3p_entry_values', 3);
4681 end if;
4682 begin
4683 delete from pay_run_results rr
4684 where rr.element_type_id + 0 = p_element_type_id
4685 and rr.entry_type = p_entry_type
4686 and rr.source_id = p_element_entry_id
4687 and rr.source_type = 'E'
4688 and rr.status = v_status;
4689 end;
4690 end if;
4691 end if;
4692 */
4693 -- End of Enhancement 3205906
4694 --
4695 -- delete element entry values.
4696 -- DT_DELETE_MODE: ZAP
4697 -- Step 4:
4698 --
4699 if (p_creator_type = 'F'
4700 or p_element_entry_id is not null) then
4701 if g_debug then
4702 hr_utility.set_location('hr_entry.del_3p_entry_values', 4);
4703 end if;
4704 begin
4705 delete from pay_element_entry_values_f eev
4706 where eev.element_entry_id = p_element_entry_id;
4707 end;
4708 else
4709 --
4710 -- As the entry being deleted was not created by the entry form
4711 -- (e.g. absence) we must delete the entry values.
4712 -- DT_DELETE_MODE: ZAP
4713 -- Notes:
4714 -- 1) Sql needs to be tuned.
4715 -- 2) This is specific to absences.
4716 -- Step 5:
4717 --
4718 if g_debug then
4719 hr_utility.set_location('hr_entry.del_3p_entry_values', 5);
4720 end if;
4721 begin
4722 delete from pay_element_entry_values_f eev
4723 where eev.element_entry_id in
4724 (select ee.element_entry_id
4725 from pay_element_entries_f ee
4726 where ee.creator_type = p_creator_type
4727 and ee.creator_id = p_creator_id
4728 and ee.entry_type = p_entry_type
4729 and ee.element_link_id = p_element_link_id
4730 and ee.assignment_id = p_assignment_id);
4731 end;
4732 end if;
4733 --
4734 -- We only need to delete from element entries where the entry is a
4735 -- standard recurring entry which could be datetracked.
4736 --
4737 if (p_processing_type = 'R' and
4738 p_entry_type = 'E') then
4739 --
4740 -- delete element entry
4741 -- DT_DELETE_MODE: ZAP
4742 -- Step 6:
4743 --
4744 if g_debug then
4745 hr_utility.set_location('hr_entry.del_3p_entry_values', 6);
4746 end if;
4747 begin
4748 delete from pay_element_entries_f ee
4749 where ee.element_entry_id = p_element_entry_id;
4750 exception
4751 when NO_DATA_FOUND then NULL;
4752 end;
4753 --
4754 elsif p_creator_type = 'A' then
4755 --
4756 -- As the entry is an absence then delete all entries for the absence
4757 -- attendence.
4758 --
4759 if g_debug then
4760 hr_utility.set_location('hr_entry.del_3p_entry_values', 7);
4761 end if;
4762 begin
4763 delete from pay_element_entries_f ee
4764 where ee.creator_type = p_creator_type
4765 and ee.creator_id = p_creator_id
4766 and ee.entry_type = p_entry_type
4767 and ee.element_link_id = p_element_link_id
4768 and ee.assignment_id = p_assignment_id;
4769 end;
4770 end if;
4771 --
4772 elsif v_dt_delete_mode = 'DELETE' then
4773 --
4774 -- set the effective end date on element entry values.
4775 -- DT_DELETE_MODE: DELETE
4776 -- Step 8:
4777 --
4778 if g_debug then
4779 hr_utility.set_location('hr_entry.del_3p_entry_values', 8);
4780 end if;
4781 begin
4782 update pay_element_entry_values_f eev
4783 set eev.effective_end_date = p_session_date
4784 where eev.element_entry_id = p_element_entry_id
4785 and p_session_date between eev.effective_start_date
4786 and eev.effective_end_date;
4787 exception
4788 when NO_DATA_FOUND then
4789 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4790 hr_utility.set_message_token('PROCEDURE','hr_entry.del_3p_entry_values');
4791 hr_utility.set_message_token('STEP','8');
4792 hr_utility.raise_error;
4793 end;
4794 --
4795 if g_debug then
4796 hr_utility.set_location('hr_entry.del_3p_entry_values', 9);
4797 end if;
4798 begin
4799 delete from pay_element_entry_values_f eev
4800 where eev.element_entry_id = p_element_entry_id
4801 and eev.effective_start_date >= p_validation_start_date;
4802 end;
4803 --
4804 end if;
4805 --
4806 elsif (v_dt_delete_mode = 'DELETE_NEXT_CHANGE'
4807 or v_dt_delete_mode = 'FUTURE_CHANGE') then
4808 --
4809 -- delete element entry values between the validation start/end dates.
4810 -- DT_DELETE_MODE: DELETE_NEXT_CHANGE/FUTURE_CHANGE
4811 -- Step 9:
4812 --
4813 if g_debug then
4814 hr_utility.set_location('hr_entry.del_3p_entry_values', 9);
4815 end if;
4816 begin
4817 delete from pay_element_entry_values_f eev
4818 where eev.element_entry_id = p_element_entry_id
4819 and (
4820 (eev.effective_end_date between p_validation_start_date
4821 and p_validation_end_date)
4822 or (eev.effective_start_date between p_validation_start_date
4823 and p_validation_end_date));
4824 end;
4825 --
4826 -- Update the current effective_end_date on the entry values rows.
4827 -- DT_DELETE_MODE: DELETE_NEXT_CHANGE/FUTURE_CHANGE
4828 -- Step 10:
4829 if g_debug then
4830 hr_utility.set_location('hr_entry.del_3p_entry_values', 10);
4831 end if;
4832 begin
4833 update pay_element_entry_values_f eev
4834 -- bug 384948. Changed set clouse to supply effective_end_date of the
4835 -- element entry as opposite to p_validation_end_date.
4836 set eev.effective_end_date = (select effective_end_date
4837 from pay_element_entries_f
4838 where element_entry_id = p_element_entry_id
4839 and effective_start_date = eev.effective_start_date)
4840 where eev.element_entry_id = p_element_entry_id
4841 and p_session_date between eev.effective_start_date
4842 and eev.effective_end_date;
4843 exception
4844 when NO_DATA_FOUND then
4845 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4846 hr_utility.set_message_token('PROCEDURE','hr_entry.del_3p_entry_values');
4847 hr_utility.set_message_token('STEP','10');
4848 hr_utility.raise_error;
4849 end;
4850 --
4851 extend_beneficiaries;
4852 extend_dependants;
4853 --
4854 end if;
4855 --
4856 end del_3p_entry_values;
4857 --
4858 procedure upd_3p_ent_values
4859 (
4860 p_element_entry_id number,
4861 p_element_type_id number,
4862 p_element_link_id number,
4863 p_cost_allocation_keyflex_id number,
4864 p_entry_type varchar2,
4865 p_processing_type varchar2,
4866 p_creator_type varchar2,
4867 p_creator_id number,
4868 p_assignment_id number,
4869 p_input_currency_code varchar2,
4870 p_output_currency_code varchar2,
4871 p_validation_start_date date,
4872 p_validation_end_date date,
4873 p_session_date date,
4874 p_dt_update_mode varchar2,
4875 p_num_entry_values number,
4876 p_input_value_id_tbl hr_entry.number_table,
4877 p_entry_value_tbl hr_entry.varchar2_table
4878 ) is
4879 --
4880 -- Local Variables
4881 v_return_entry_id number;
4882 --
4883 begin
4884 if g_debug then
4885 hr_utility.set_location('hr_entry.upd_3p_entry_values', 1);
4886 end if;
4887 hr_general.assert_condition (p_processing_type is not null
4888 and p_dt_update_mode is not null
4889 and p_element_entry_id is not null
4890 and p_element_type_id is not null
4891 and p_element_link_id is not null
4892 and p_entry_type is not null
4893 and p_assignment_id is not null
4894 and p_validation_start_date is not null
4895 and p_validation_end_date is not null
4896 and p_session_date is not null
4897 and p_validation_start_date = trunc
4898 (p_validation_start_date)
4899 and p_validation_end_date = trunc
4900 (p_validation_end_date)
4901 and p_session_date = trunc (p_session_date));
4902 --
4903 --
4904 -- If the entry is nonrecurring, additional, adjustment, override or the
4905 -- update mode is 'CORRECTION' then:
4906 -- Step 1:
4907 --
4908 if (p_dt_update_mode = 'CORRECTION' or -- DT Correction
4909 p_processing_type = 'N' or -- Nonrecurring Entry
4910 p_entry_type = 'D' or -- Additional
4911 p_entry_type = 'S' or -- Override
4912 p_entry_type = 'R' or -- Replacement Adjustment
4913 p_entry_type = 'A') then -- Additive Adjustment
4914 --
4915 if p_num_entry_values > 0 then
4916 --
4917 -- NB. mandatory checks are not applied to adjustments.
4918 if not p_entry_type in ('A','R') then
4919 --
4920 hr_entry.chk_mandatory_entry_values
4921 (p_element_link_id,
4922 p_session_date,
4923 p_num_entry_values,
4924 p_input_value_id_tbl,
4925 p_entry_value_tbl);
4926 --
4927 end if;
4928 --
4929 for v_loop in 1..p_num_entry_values loop
4930 --
4931 begin
4932 --
4933 --
4934 -- WWbug 273821
4935 -- Added + 0 to eev.input_value_id to disable the use of
4936 -- index PAY_ELEMENT_ENTRY_VALUES_F_N1
4937 --
4938 update pay_element_entry_values_f eev
4939 set eev.screen_entry_value = p_entry_value_tbl(v_loop)
4940 where eev.element_entry_id = p_element_entry_id
4941 and eev.input_value_id + 0 = p_input_value_id_tbl(v_loop)
4942 and p_validation_start_date between eev.effective_start_date
4943 and eev.effective_end_date;
4944 --
4945 exception
4946 when NO_DATA_FOUND then
4947 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4948 hr_utility.set_message_token('PROCEDURE',
4949 'hr_entry.upd_3p_entry_values');
4950 hr_utility.set_message_token('STEP','1');
4951 hr_utility.raise_error;
4952 end;
4953 --
4954 end loop;
4955 --
4956 end if;
4957 --
4958 end if;
4959 --
4960 -- If the entry which has been updated is just nonrecurring
4961 -- and has not been processed and the input currency = output currency then
4962 -- update the run result values.
4963 -- Step 3:
4964 --
4965 if (p_processing_type = 'N' and
4966 -- p_creator_type <> 'A' and
4967 p_entry_type = 'E' and
4968 p_input_currency_code = p_output_currency_code and
4969 hr_entry.entry_process_in_run(p_element_type_id, p_session_date)) then
4970 if g_debug then
4971 hr_utility.set_location('hr_entry.upd_3p_entry_values', 3);
4972 end if;
4973 begin
4974 UPDATE PAY_RUN_RESULT_VALUES PRRV1
4975 SET PRRV1.RESULT_VALUE =
4976 (SELECT PEEV1.SCREEN_ENTRY_VALUE
4977 FROM PAY_ELEMENT_ENTRY_VALUES_F PEEV1
4978 WHERE p_session_date
4979 BETWEEN PEEV1.EFFECTIVE_START_DATE
4980 AND PEEV1.EFFECTIVE_END_DATE
4981 AND PEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
4982 AND PEEV1.INPUT_VALUE_ID + 0 = PRRV1.INPUT_VALUE_ID)
4983 WHERE PRRV1.RUN_RESULT_ID =
4984 (SELECT PRR1.RUN_RESULT_ID
4985 FROM PAY_RUN_RESULTS PRR1
4986 WHERE PRR1.SOURCE_ID = p_element_entry_id
4987 AND PRR1.SOURCE_TYPE = 'E'
4988 AND PRR1.STATUS = 'U'
4989 AND PRR1.ELEMENT_TYPE_ID +0 = p_element_type_id);
4990 exception
4991 when NO_DATA_FOUND then NULL;
4992 end;
4993 end if;
4994 --
4995 -- If a datetrack UPDATE or UPDATE_CHANGE_INSERT or UPDATE_OVERRIDE
4996 -- has taken place then:
4997 -- Step 4:
4998 --
4999 if ((p_dt_update_mode = 'UPDATE' or
5000 p_dt_update_mode = 'UPDATE_CHANGE_INSERT' or
5001 p_dt_update_mode = 'UPDATE_OVERRIDE') and
5002 p_processing_type = 'R') then
5003 if g_debug then
5004 hr_utility.set_location('hr_entry.upd_3p_entry_values', 4);
5005 end if;
5006 begin
5007 UPDATE PAY_ELEMENT_ENTRY_VALUES_F PEEV1
5008 SET PEEV1.EFFECTIVE_END_DATE = p_validation_start_date - 1
5009 WHERE PEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
5010 AND p_session_date
5011 BETWEEN PEEV1.EFFECTIVE_START_DATE AND PEEV1.EFFECTIVE_END_DATE;
5012 exception
5013 when NO_DATA_FOUND then
5014 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5015 hr_utility.set_message_token('PROCEDURE','hr_entry.upd_3p_entry_values');
5016 hr_utility.set_message_token('STEP','4');
5017 hr_utility.raise_error;
5018 end;
5019 --
5020 -- If the update mode = 'UPDATE_OVERRIDE' then delete all entry values that
5021 -- are greater or equal to the validation_start_date
5022 -- Step 4:
5023 --
5024 if p_dt_update_mode = 'UPDATE_OVERRIDE' then
5025 if g_debug then
5026 hr_utility.set_location('hr_entry.upd_3p_entry_values', 5);
5027 end if;
5028 begin
5029 DELETE FROM PAY_ELEMENT_ENTRY_VALUES_F PEEV1
5030 WHERE PEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
5031 AND PEEV1.EFFECTIVE_START_DATE >= p_validation_start_date;
5032 end;
5033 end if;
5034 --
5035 -- Insert new updated element entry rows.
5036 --
5037 hr_entry.ins_3p_entry_values
5038 (p_element_link_id,
5039 p_element_entry_id,
5040 p_session_date,
5041 p_num_entry_values,
5042 p_input_value_id_tbl,
5043 p_entry_value_tbl);
5044 --
5045 end if;
5046 --
5047 end upd_3p_ent_values;
5048 --
5049 -- NAME
5050 -- hr_entry.upd_3p_entry_values
5051 --
5052 -- DESCRIPTION
5053 -- This procedure is used for third party updates into:
5054 -- PAY_ELEMENT_ENTRY_VALUES_F
5055 -- PAY_RUN_RESULTS (If nonrecurring).
5056 -- PAY_RUN_RESULT_VALUES (If nonrecurring).
5057 --
5058 -- NB. this procedure is OVERLOADED !
5059 --
5060 procedure upd_3p_entry_values
5061 (
5062 p_element_entry_id number,
5063 p_element_type_id number,
5064 p_element_link_id number,
5065 p_cost_allocation_keyflex_id number,
5066 p_entry_type varchar2,
5067 p_processing_type varchar2,
5068 p_creator_type varchar2,
5069 p_creator_id number,
5070 p_assignment_id number,
5071 p_input_currency_code varchar2,
5072 p_output_currency_code varchar2,
5073 p_validation_start_date date,
5074 p_validation_end_date date,
5075 p_session_date date,
5076 p_dt_update_mode varchar2,
5077 p_num_entry_values number,
5078 p_input_value_id_tbl hr_entry.number_table,
5079 p_entry_value_tbl hr_entry.varchar2_table
5080 ) is
5081 --
5082 -- Local Variables
5083 v_return_entry_id number;
5084 v_num_entry_values number;
5085 v_input_value_id_tbl hr_entry.number_table;
5086 v_entry_value_tbl hr_entry.varchar2_table;
5087 --
5088 begin
5089 --
5090 v_num_entry_values := p_num_entry_values;
5091 v_input_value_id_tbl := p_input_value_id_tbl;
5092 v_entry_value_tbl := p_entry_value_tbl;
5093 --
5094 hr_entry_api.conv_table_to_table
5095 ('DB',
5096 p_session_date,
5097 p_element_entry_id,
5098 null,
5099 v_num_entry_values,
5100 /** sbilling **/
5101 p_creator_type,
5102 p_entry_type,
5103 v_input_value_id_tbl,
5104 v_entry_value_tbl);
5105 --
5106 hr_entry.upd_3p_ent_values
5107 (p_element_entry_id,
5108 p_element_type_id,
5109 p_element_link_id,
5110 p_cost_allocation_keyflex_id,
5111 p_entry_type,
5112 p_processing_type,
5113 p_creator_type,
5114 p_creator_id,
5115 p_assignment_id,
5116 p_input_currency_code,
5117 p_output_currency_code,
5118 p_validation_start_date,
5119 p_validation_end_date,
5120 p_session_date,
5121 p_dt_update_mode,
5122 v_num_entry_values,
5123 v_input_value_id_tbl,
5124 v_entry_value_tbl);
5125 --
5126 end upd_3p_entry_values;
5127 --
5128 -- NAME
5129 -- hr_entry.upd_3p_entry_values
5130 --
5131 -- DESCRIPTION
5132 -- This procedure is used for third party updates into:
5133 -- PAY_ELEMENT_ENTRY_VALUES_F
5134 -- PAY_RUN_RESULTS (If nonrecurring).
5135 -- PAY_RUN_RESULT_VALUES (If nonrecurring).
5136 --
5137 -- NB. this Procedure is OVERLOADED !
5138 --
5139 procedure upd_3p_entry_values
5140 (
5141 p_element_entry_id number,
5142 p_element_type_id number,
5143 p_element_link_id number,
5144 p_cost_allocation_keyflex_id number,
5145 p_entry_type varchar2,
5146 p_processing_type varchar2,
5147 p_creator_type varchar2,
5148 p_creator_id number,
5149 p_assignment_id number,
5150 p_input_currency_code varchar2,
5151 p_output_currency_code varchar2,
5152 p_validation_start_date date,
5153 p_validation_end_date date,
5154 p_session_date date,
5155 p_dt_update_mode varchar2,
5156 p_input_value_id1 number,
5157 p_input_value_id2 number,
5158 p_input_value_id3 number,
5159 p_input_value_id4 number,
5160 p_input_value_id5 number,
5161 p_input_value_id6 number,
5162 p_input_value_id7 number,
5163 p_input_value_id8 number,
5164 p_input_value_id9 number,
5165 p_input_value_id10 number,
5166 p_input_value_id11 number,
5167 p_input_value_id12 number,
5168 p_input_value_id13 number,
5169 p_input_value_id14 number,
5170 p_input_value_id15 number,
5171 p_entry_value1 varchar2,
5172 p_entry_value2 varchar2,
5173 p_entry_value3 varchar2,
5174 p_entry_value4 varchar2,
5175 p_entry_value5 varchar2,
5176 p_entry_value6 varchar2,
5177 p_entry_value7 varchar2,
5178 p_entry_value8 varchar2,
5179 p_entry_value9 varchar2,
5180 p_entry_value10 varchar2,
5181 p_entry_value11 varchar2,
5182 p_entry_value12 varchar2,
5183 p_entry_value13 varchar2,
5184 p_entry_value14 varchar2,
5185 p_entry_value15 varchar2
5186 ) is
5187 --
5188 -- Local variables
5189 v_num_entry_values number;
5190 v_input_value_id_tbl hr_entry.number_table;
5191 v_entry_value_tbl hr_entry.varchar2_table;
5192 --
5193 begin
5194 g_debug := hr_utility.debug_enabled;
5195 --
5196 if g_debug then
5197 hr_utility.set_location('hr_entry.upd_3p_entry_values',100);
5198 end if;
5199 --
5200 -- Convert entry value details ie. INPUT_VALUE_ID and SCREEN_ENTRY_VALUE
5201 -- into two tables to be passed into the overloaded version of
5202 -- upd_3p_entry_values. The overloaded version is capable of handling
5203 -- unlimited numbers of entry values.
5204 hr_entry_api.conv_entry_values_to_table
5205 ('DB',
5206 p_element_entry_id,
5207 null,
5208 p_session_date,
5209 /** sbilling **/
5210 p_creator_type,
5211 p_entry_type,
5212 p_input_value_id1,
5213 p_input_value_id2,
5214 p_input_value_id3,
5215 p_input_value_id4,
5216 p_input_value_id5,
5217 p_input_value_id6,
5218 p_input_value_id7,
5219 p_input_value_id8,
5220 p_input_value_id9,
5221 p_input_value_id10,
5222 p_input_value_id11,
5223 p_input_value_id12,
5224 p_input_value_id13,
5225 p_input_value_id14,
5226 p_input_value_id15,
5227 p_entry_value1,
5228 p_entry_value2,
5229 p_entry_value3,
5230 p_entry_value4,
5231 p_entry_value5,
5232 p_entry_value6,
5233 p_entry_value7,
5234 p_entry_value8,
5235 p_entry_value9,
5236 p_entry_value10,
5237 p_entry_value11,
5238 p_entry_value12,
5239 p_entry_value13,
5240 p_entry_value14,
5241 p_entry_value15,
5242 v_num_entry_values,
5243 v_input_value_id_tbl,
5244 v_entry_value_tbl);
5245 --
5246 if g_debug then
5247 hr_utility.set_location('hr_entry.upd_3p_entry_values',105);
5248 end if;
5249 --
5250 hr_entry.upd_3p_ent_values
5251 (p_element_entry_id,
5252 p_element_type_id,
5253 p_element_link_id,
5254 p_cost_allocation_keyflex_id,
5255 p_entry_type,
5256 p_processing_type,
5257 p_creator_type,
5258 p_creator_id,
5259 p_assignment_id,
5260 p_input_currency_code,
5261 p_output_currency_code,
5262 p_validation_start_date,
5263 p_validation_end_date,
5264 p_session_date,
5265 p_dt_update_mode,
5266 v_num_entry_values,
5267 v_input_value_id_tbl,
5268 v_entry_value_tbl);
5269 --
5270 end upd_3p_entry_values;
5271 --
5272 -- NAME
5273 -- hr_entry.trigger_workload_shifting
5274 --
5275 -- DESCRIPTION
5276 -- This procedure is used for triggering workload shifting.
5277 --
5278 PROCEDURE trigger_workload_shifting(p_mode varchar2,
5279 p_assignment_id number,
5280 p_effective_start_date date,
5281 p_effective_end_date date) is
5282 v_assignment_action_id number;
5283 --
5284 begin
5285 -- Workload shifting is NOT used.
5286 return;
5287 --
5288 -- Find the latest assignment action for the assignment.
5289 --
5290 -- If it is
5291 --
5292 -- 1. A Payroll Run
5293 -- 2. It is Completed ie. processed.
5294 -- 3. The period in which it was earned overlaps with a change in
5295 -- assignment or element entry criteria.
5296 -- 4. The payroll has workload shifting enabled and matches the type of
5297 -- change ie. Element Entry or Assignment.
5298 -- 5. The change does not cross more than one payroll run in the latest
5299 -- period.
5300 --
5301 -- then Mark for Retry the assignment action
5302 --
5303 /* -- irrelevant, commented out.
5304 update pay_assignment_actions paa
5305 set paa.action_status = 'M'
5306 where paa.assignment_action_id =
5307 (select aa.assignment_action_id
5308 from pay_assignment_actions aa,
5309 pay_payroll_actions pa,
5310 per_assignments_f asg,
5311 per_time_periods tim,
5312 pay_payrolls_f pp
5313 where pa.effective_date =
5314 (select max(pa2.effective_date)
5315 from pay_payroll_actions pa2,
5316 pay_assignment_actions aa2
5317 where aa2.assignment_id = p_assignment_id
5318 and pa2.payroll_action_id = aa2.payroll_action_id)
5319 and pa.action_sequence =
5320 (select max(pa3.action_sequence)
5321 from pay_payroll_actions pa3,
5322 pay_assignment_actions aa3
5323 where aa3.assignment_id = p_assignment_id
5324 and pa3.payroll_action_id = aa3.payroll_action_id
5325 and pa3.effective_date = pa.effective_date)
5326 and not exists
5327 (select null
5328 from pay_payroll_actions pa4,
5329 pay_assignment_actions aa4
5330 where aa4.assignment_id = p_assignment_id
5331 and aa4.payroll_action_id = pa4.payroll_action_id
5332 and pa4.action_type = 'R'
5333 and pa4.action_sequence < pa.action_sequence
5334 and nvl(pa4.date_earned,pa4.effective_date) between
5335 greatest(tim.start_date,p_effective_start_date) and
5336 pa.effective_date)
5337 and pa.action_type = 'R'
5338 and aa.payroll_action_id = pa.payroll_action_id
5339 and aa.action_status = 'C'
5340 and aa.assignment_id = p_assignment_id
5341 and asg.assignment_id = aa.assignment_id
5342 and pp.payroll_id = asg.payroll_id
5343 and tim.payroll_id = pp.payroll_id
5344 and nvl(pa.date_earned,pa.effective_date)
5345 between asg.effective_start_date
5346 and asg.effective_end_date
5347 and nvl(pa.date_earned,pa.effective_date)
5348 between pp.effective_start_date
5349 and pp.effective_end_date
5350 and nvl(pa.date_earned,pa.effective_date)
5351 between tim.start_date
5352 and tim.end_date
5353 and nvl(pa.date_earned,pa.effective_date)
5354 between p_effective_start_date
5355 and p_effective_end_date
5356 and p_effective_start_date <= tim.end_date
5357 and p_effective_end_date >= tim.start_date
5358 and ((p_mode = 'ELEMENT_ENTRY'
5359 and (pp.workload_shifting_level = 'E' or
5360 pp.workload_shifting_level = 'A'))
5361 or (p_mode = 'ASSIGNMENT'
5362 and pp.workload_shifting_level = 'A')));
5363 */
5364 --
5365 end trigger_workload_shifting;
5366 --
5367 -- NAME
5368 -- hr_entry.check_format
5369 --
5370 -- DESCRIPTION
5371 -- Makes sure that the entry value is correct for the UOM and also convert the
5372 -- screen value into the database value ie. internal format.
5373 --
5374 procedure check_format
5375 (
5376 p_element_link_id in number,
5377 p_input_value_id in number,
5378 p_session_date in date,
5379 p_formatted_value in out nocopy varchar2,
5380 p_database_value in out nocopy varchar2,
5381 p_nullok in varchar2 default 'Y',
5382 p_min_max_failure in out nocopy varchar2,
5383 p_warning_or_error out nocopy varchar2,
5384 p_minimum_value out nocopy varchar2,
5385 p_maximum_value out nocopy varchar2
5386 ) is
5387 --
5388 -- Local Variables
5389 v_checkformat_error boolean := false;
5390 -- --
5391 --v_message_text varchar2(80);
5392 v_message_text HR_LOOKUPS.meaning%TYPE;
5393 -- --
5394 v_uom varchar2(30);
5395 v_hot_default_flag varchar2(30);
5396 v_input_currency_code varchar2(30);
5397 v_minimum_value varchar2(60);
5398 v_maximum_value varchar2(60);
5399 v_warning_or_error varchar2(30);
5400 v_formatted_min_value varchar2(60);
5401 v_formatted_max_value varchar2(60);
5402 --
5403 begin
5404 g_debug := hr_utility.debug_enabled;
5405 --
5406 if g_debug then
5407 hr_utility.set_location('hr_entry.check_format',5);
5408 hr_utility.trace(' p_element_link_id : '|| p_element_link_id);
5409 hr_utility.trace(' p_input_value_id : '|| p_input_value_id);
5410 hr_utility.trace(' p_session_date : '|| p_session_date);
5411 hr_utility.trace(' p_formatted_value : '|| p_formatted_value);
5412 hr_utility.trace(' p_database_value : '|| p_database_value);
5413 hr_utility.trace(' p_nullok : '|| p_nullok);
5414 hr_utility.trace(' p_min_max_failure : '|| p_min_max_failure);
5415 end if;
5416 --
5417 -- Get uom , min / max and hot default details for the entry value
5418 -- being validated.
5419 begin
5420 --
5421 -- Dave Harris, 13-Oct-1994
5422 -- Bug G1420: restricted pay_element_types_f using date effective dates.
5423 --
5424 -- INDEX hint added following NHS project recommendation
5425 select /*+ INDEX(liv, pay_link_input_values_f_n2) */
5426 iv.uom,
5427 iv.hot_default_flag,
5428 et.input_currency_code,
5429 decode(iv.hot_default_flag,
5430 'Y',nvl(liv.min_value,
5431 iv.min_value)
5432 ,liv.min_value),
5433 decode(iv.hot_default_flag,
5434 'Y',nvl(liv.max_value,
5435 iv.max_value)
5436 ,liv.max_value),
5437 decode(iv.hot_default_flag,
5438 'Y',nvl(liv.warning_or_error,
5439 iv.warning_or_error)
5440 ,liv.warning_or_error)
5441 into v_uom,
5442 v_hot_default_flag,
5443 v_input_currency_code,
5444 v_minimum_value,
5445 v_maximum_value,
5446 v_warning_or_error
5447 from pay_link_input_values_f liv,
5448 pay_input_values_f iv,
5449 pay_element_types_f et
5450 where liv.element_link_id = p_element_link_id
5451 and liv.input_value_id = p_input_value_id
5452 and iv.input_value_id = liv.input_value_id
5453 and et.element_type_id = iv.element_type_id
5454 and p_session_date between liv.effective_start_date
5455 and liv.effective_end_date
5456 and p_session_date between iv.effective_start_date
5457 and iv.effective_end_date
5458 and p_session_date between et.effective_start_date
5459 and et.effective_end_date;
5460 --
5461 -- Bug 1123084, always set up this value.
5462 p_warning_or_error := v_warning_or_error;
5463 --
5464 exception
5465 when no_data_found then
5466 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5467 hr_utility.set_message_token('PROCEDURE',
5468 'hr_entry.check_format');
5469 hr_utility.set_message_token('STEP','1');
5470 hr_utility.raise_error;
5471 end;
5472 --
5473 if g_debug then
5474 hr_utility.set_location('hr_entry.check_format',10);
5475 hr_utility.trace(' v_uom : '|| v_uom);
5476 hr_utility.trace(' v_hot_default_flag : '|| v_hot_default_flag);
5477 hr_utility.trace(' v_input_currency_code : '|| v_input_currency_code);
5478 hr_utility.trace(' v_minimum_value : '|| v_minimum_value);
5479 hr_utility.trace(' v_maximum_value : '|| v_maximum_value);
5480 hr_utility.trace(' v_warning_or_error : '|| v_warning_or_error);
5481 end if;
5482 --
5483 IF ((v_uom = 'M') AND (v_input_currency_code IS NULL)) THEN
5484 hr_utility.set_message (801,'HR_51106_ELEMENT_CURR_REQ');
5485 hr_utility.raise_error;
5486 END IF;
5487 --
5488 if v_minimum_value is not null then
5489 --
5490 hr_chkfmt.changeformat(v_minimum_value,
5491 v_formatted_min_value,
5492 v_uom,
5493 v_input_currency_code);
5494 --
5495 end if;
5496 --
5497 if v_maximum_value is not null then
5498 --
5499 hr_chkfmt.changeformat(v_maximum_value,
5500 v_formatted_max_value,
5501 v_uom,
5502 v_input_currency_code);
5503 --
5504 end if;
5505 --
5506 -- Now format the value.
5507 begin
5508 hr_chkfmt.checkformat(p_formatted_value,
5509 v_uom,
5510 p_database_value,
5511 v_minimum_value,
5512 v_maximum_value,
5513 p_nullok,
5514 p_min_max_failure,
5515 v_input_currency_code);
5516 exception
5517 when hr_utility.hr_error then
5518 v_checkformat_error := true;
5519 end;
5520 --
5521 if g_debug then
5522 hr_utility.set_location('hr_entry.check_format',10);
5523 end if;
5524 --
5525 -- Value is not correct for unit of measure
5526 if (v_checkformat_error) then
5527 --
5528 begin
5529 --
5530 select meaning
5531 into v_message_text
5532 from hr_lookups
5533 where lookup_type = 'UNITS'
5534 and lookup_code = v_uom;
5535 --
5536 exception
5537 when no_data_found then
5538 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5539 hr_utility.set_message_token('PROCEDURE', 'hr_entry.check_format');
5540 hr_utility.set_message_token('STEP', '2');
5541 hr_utility.raise_error;
5542 end;
5543 --
5544 hr_utility.set_message(801, 'PAY_6306_INPUT_VALUE_FORMAT');
5545 hr_utility.set_message_token('UNIT_OF_MEASURE', v_message_text);
5546 hr_utility.raise_error;
5547 --
5548 end if;
5549 --
5550 if g_debug then
5551 hr_utility.set_location('hr_entry.check_format',15);
5552 end if;
5553 --
5554 -- Minimum / maximum conditions have been broken by value
5555 if p_min_max_failure = 'F' then
5556 --
5557 if g_debug then
5558 hr_utility.set_location('hr_entry.check_format',20);
5559 end if;
5560 --
5561 -- If minimum value was specified, translate into screen format for use
5562 -- in error meessages
5563 if v_minimum_value is not null then
5564 p_minimum_value := v_formatted_min_value;
5565 end if;
5566 --
5567 if g_debug then
5568 hr_utility.set_location('hr_entry.check_format',25);
5569 end if;
5570 --
5571 -- If maximum value was specified, translate into screen format for use
5572 -- in error meessages
5573 if v_maximum_value is not null then
5574 p_maximum_value := v_formatted_max_value;
5575 end if;
5576 --
5577 end if;
5578 --
5579 end check_format;
5580 --
5581 -- NAME
5582 -- hr_entry.maintain_cost_keyflex
5583 --
5584 -- DESCRIPTION
5585 --
5586 function maintain_cost_keyflex(
5587 p_cost_keyflex_structure in number,
5588 p_cost_allocation_keyflex_id in number,
5589 p_concatenated_segments in varchar2,
5590 p_summary_flag in varchar2,
5591 p_start_date_active in date,
5592 p_end_date_active in date,
5593 p_segment1 in varchar2,
5594 p_segment2 in varchar2,
5595 p_segment3 in varchar2,
5596 p_segment4 in varchar2,
5597 p_segment5 in varchar2,
5598 p_segment6 in varchar2,
5599 p_segment7 in varchar2,
5600 p_segment8 in varchar2,
5601 p_segment9 in varchar2,
5602 p_segment10 in varchar2,
5603 p_segment11 in varchar2,
5604 p_segment12 in varchar2,
5605 p_segment13 in varchar2,
5606 p_segment14 in varchar2,
5607 p_segment15 in varchar2,
5608 p_segment16 in varchar2,
5609 p_segment17 in varchar2,
5610 p_segment18 in varchar2,
5611 p_segment19 in varchar2,
5612 p_segment20 in varchar2,
5613 p_segment21 in varchar2,
5614 p_segment22 in varchar2,
5615 p_segment23 in varchar2,
5616 p_segment24 in varchar2,
5617 p_segment25 in varchar2,
5618 p_segment26 in varchar2,
5619 p_segment27 in varchar2,
5620 p_segment28 in varchar2,
5621 p_segment29 in varchar2,
5622 p_segment30 in varchar2)
5623 return number is
5624 --
5625 cursor csr_cost_alloc_exists is
5626 select pca.cost_allocation_keyflex_id
5627 from pay_cost_allocation_keyflex pca
5628 where pca.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
5629 --
5630 cursor get_seg_order is
5631 select substr(application_column_name,8,2)
5632 from fnd_id_flex_segments_vl
5633 where id_flex_code = 'COST'
5634 and id_flex_num = p_cost_keyflex_structure
5635 and application_id = 801
5636 and enabled_flag = 'Y'
5637 and display_flag = 'Y'
5638 order by segment_num;
5639 --
5640 l_dummy number;
5641 l_cost_allocation_keyflex_id number := p_cost_allocation_keyflex_id;
5642 --
5643 type segment_table is table of varchar2(60)
5644 index by binary_integer;
5645 segment segment_table;
5646 i number;
5647 sql_curs number;
5648 rows_processed integer;
5649 statem varchar2(2000);
5650 --
5651 l_delimiter varchar2(1);
5652 l_concat_string varchar2(2000);
5653 -- l_concatenated_segments varchar2(240);
5654 -- bugfix 1856433
5655 l_concatenated_segments varchar2(2000);
5656 l_disp_no number;
5657 first_seg boolean;
5658 --
5659 v_cal_cost_segs varchar2(3); -- user profile
5660 l_are_dynamic_inserts_allowed varchar2(2) := null;
5661 --
5662 begin
5663 g_debug := hr_utility.debug_enabled;
5664
5665 -- A cost_keyflex_id has been specified so confirm it still is valid.
5666 if (l_cost_allocation_keyflex_id is not null and
5667 l_cost_allocation_keyflex_id <> -1) then
5668 --
5669 open csr_cost_alloc_exists;
5670 fetch csr_cost_alloc_exists into l_dummy;
5671 --
5672 -- Keyflex does not exist so need to rederive a cost_keyflex_id.
5673 if csr_cost_alloc_exists%notfound then
5674 l_cost_allocation_keyflex_id := -1;
5675 -- Keyflex does exist.
5676 else
5677 l_cost_allocation_keyflex_id := p_cost_allocation_keyflex_id;
5678 end if;
5679 --
5680 close csr_cost_alloc_exists;
5681 --
5682 end if;
5683
5684 if (l_cost_allocation_keyflex_id = -1) then
5685 --
5686 -- Need to check for a partial value.
5687 --
5688 if g_debug then
5689 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 1);
5690 end if;
5691 --
5692 segment(1) := p_segment1;
5693 segment(2) := p_segment2;
5694 segment(3) := p_segment3;
5695 segment(4) := p_segment4;
5696 segment(5) := p_segment5;
5697 segment(6) := p_segment6;
5698 segment(7) := p_segment7;
5699 segment(8) := p_segment8;
5700 segment(9) := p_segment9;
5701 segment(10) := p_segment10;
5702 segment(11) := p_segment11;
5703 segment(12) := p_segment12;
5704 segment(13) := p_segment13;
5705 segment(14) := p_segment14;
5706 segment(15) := p_segment15;
5707 segment(16) := p_segment16;
5708 segment(17) := p_segment17;
5709 segment(18) := p_segment18;
5710 segment(19) := p_segment19;
5711 segment(20) := p_segment20;
5712 segment(21) := p_segment21;
5713 segment(22) := p_segment22;
5714 segment(23) := p_segment23;
5715 segment(24) := p_segment24;
5716 segment(25) := p_segment25;
5717 segment(26) := p_segment26;
5718 segment(27) := p_segment27;
5719 segment(28) := p_segment28;
5720 segment(29) := p_segment29;
5721 segment(30) := p_segment30;
5722 --
5723 statem := '
5724 select cost_allocation_keyflex_id
5725 from pay_cost_allocation_keyflex c
5726 where c.id_flex_num = :p_cost_keyflex_structure
5727 and c.enabled_flag = ''Y''';
5728 --
5729 for i in 1..30 loop
5730 if segment(i) is null then
5731 statem := statem || ' and c.segment'||i||' is null';
5732 else
5733 statem := statem || ' and c.segment'||i||' = :p_segment'||i;
5734 end if;
5735 end loop;
5736 --
5737 if g_debug then
5738 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 2);
5739 end if;
5740 --
5741 sql_curs := dbms_sql.open_cursor;
5742 --
5743 dbms_sql.parse(sql_curs,
5744 statem,
5745 dbms_sql.v7);
5746 --
5747 dbms_sql.bind_variable(sql_curs, 'p_cost_keyflex_structure', p_cost_keyflex_structure);
5748 --
5749 for i in 1..30 loop
5750 if segment(i) is not null then
5751 dbms_sql.bind_variable(sql_curs, 'p_segment'||i, segment(i));
5752 end if;
5753 end loop;
5754 dbms_sql.define_column(sql_curs, 1, l_cost_allocation_keyflex_id);
5755 --
5756 if g_debug then
5757 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 3);
5758 end if;
5759 --
5760 rows_processed := dbms_sql.execute(sql_curs);
5761 --
5762 if g_debug then
5763 hr_utility.set_location('hr_entry.maintain_cost_keyflex ', 4);
5764 end if;
5765 --
5766 if dbms_sql.fetch_rows(sql_curs) > 0 then
5767 --
5768 if g_debug then
5769 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 5);
5770 end if;
5771 dbms_sql.column_value(sql_curs, 1, l_cost_allocation_keyflex_id);
5772 --
5773 if (l_cost_allocation_keyflex_id is null)
5774 then
5775 if g_debug then
5776 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 6);
5777 end if;
5778 l_cost_allocation_keyflex_id := -1;
5779 end if;
5780 else
5781 if g_debug then
5782 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 7);
5783 end if;
5784 l_cost_allocation_keyflex_id := -1;
5785 end if;
5786 --
5787 dbms_sql.close_cursor(sql_curs);
5788 --
5789 --
5790 -- Check to see if the cost allocation keyflex combination already
5791 -- exists.
5792 -- If it doesn't then, insert the required row.
5793 --
5794 if (l_cost_allocation_keyflex_id = -1) THEN
5795
5796 --Bug # 5860023
5797 --Check whether dynamice inserts are allowed.
5798 --If not allowed then raise a suitable error message.
5799
5800 BEGIN
5801
5802 select 'Y' into l_are_dynamic_inserts_allowed
5803 from fnd_id_flex_structures_vl
5804 where id_flex_code = 'COST'
5805 and id_flex_num = p_cost_keyflex_structure
5806 and application_id = 801
5807 and enabled_flag = 'Y'
5808 and dynamic_inserts_allowed_flag = 'Y';
5809
5810 exception
5811 when no_data_found then
5812 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 7);
5813 hr_utility.set_message(801, 'PAY_34809_CANT_INS_INTO_CSTKFF');
5814 hr_utility.raise_error;
5815 end;
5816
5817 --
5818 -- Select the next sequence value for the cost allocation keyflex.
5819 --
5820 if g_debug then
5821 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 8);
5822 end if;
5823 begin
5824 select pay_cost_allocation_keyflex_s.nextval
5825 into l_cost_allocation_keyflex_id
5826 from sys.dual;
5827 exception
5828 when NO_DATA_FOUND then
5829 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
5830 hr_utility.set_message_token('PROCEDURE',
5831 'hr_entry.maintain_cost_keyflex');
5832 hr_utility.set_message_token('STEP','5');
5833 hr_utility.raise_error;
5834 end;
5835 --
5836 -- Calculate concatenated_segments if a null value was passed in
5837 --
5838 if p_concatenated_segments is null then
5839 --
5840 if g_debug then
5841 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 15);
5842 end if;
5843 -- get delimiter
5844 l_delimiter := fnd_flex_ext.get_delimiter
5845 ('PAY'
5846 ,'COST'
5847 ,p_cost_keyflex_structure
5848 );
5849 --
5850 if g_debug then
5851 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 20);
5852 end if;
5853 --
5854 first_seg := true;
5855
5856 open get_seg_order;
5857 loop
5858 fetch get_seg_order into l_disp_no;
5859 exit when get_seg_order%NOTFOUND;
5860
5861 if first_seg = false then
5862 l_concat_string := l_concat_string || l_delimiter;
5863 else
5864 first_seg := false;
5865 end if;
5866
5867 if segment(l_disp_no) is not null then
5868 l_concat_string := l_concat_string || segment(l_disp_no);
5869 end if;
5870
5871 end loop;
5872 close get_seg_order;
5873 --
5874 l_concatenated_segments := substr(l_concat_string, 1, 240);
5875 else
5876 --
5877 if g_debug then
5878 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 25);
5879 end if;
5880 --
5881 l_concatenated_segments := p_concatenated_segments;
5882 end if;
5883 --
5884 -- Perform Flexfield Validation: if COST_VAL_SEGS pay_action_parameter = 'Y'
5885 --
5886 begin
5887 select parameter_value
5888 into v_cal_cost_segs
5889 from pay_action_parameters
5890 where parameter_name = 'COST_VAL_SEGS';
5891 exception
5892 when others then
5893 v_cal_cost_segs := 'N';
5894 end;
5895
5896 if (v_cal_cost_segs = 'Y') then
5897 --
5898 if fnd_flex_keyval.validate_segs
5899 (operation => 'CHECK_SEGMENTS'
5900 ,appl_short_name => 'PAY'
5901 ,key_flex_code => 'COST'
5902 ,structure_number => p_cost_keyflex_structure
5903 ,concat_segments => l_concatenated_segments
5904 ,allow_nulls => TRUE
5905 ,values_or_ids => 'V'
5906 ) = FALSE
5907 then
5908 --
5909 if g_debug then
5910 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 27);
5911 end if;
5912 --
5913 -- Handle error raised to create a nice error message!
5914 --
5915 hr_message.parse_encoded(p_encoded_error =>
5916 FND_FLEX_KEYVAL.encoded_error_message);
5917 fnd_message.raise_error;
5918 else
5919 if g_debug then
5920 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 28);
5921 end if;
5922
5923 end if;
5924 end if;
5925 --
5926 -- Insert the new row.
5927 --
5928 if g_debug then
5929 hr_utility.set_location('hr_entry.maintain_cost_keyflex', 30);
5930 end if;
5931 begin
5932 insert into pay_cost_allocation_keyflex
5933 (cost_allocation_keyflex_id
5934 ,concatenated_segments
5935 ,id_flex_num
5936 ,last_update_date
5937 ,last_updated_by
5938 ,summary_flag
5939 ,enabled_flag
5940 ,start_date_active
5941 ,end_date_active
5942 ,segment1
5943 ,segment2
5944 ,segment3
5945 ,segment4
5946 ,segment5
5947 ,segment6
5948 ,segment7
5949 ,segment8
5950 ,segment9
5951 ,segment10
5952 ,segment11
5953 ,segment12
5954 ,segment13
5955 ,segment14
5956 ,segment15
5957 ,segment16
5958 ,segment17
5959 ,segment18
5960 ,segment19
5961 ,segment20
5962 ,segment21
5963 ,segment22
5964 ,segment23
5965 ,segment24
5966 ,segment25
5967 ,segment26
5968 ,segment27
5969 ,segment28
5970 ,segment29
5971 ,segment30)
5972 values
5973 (l_cost_allocation_keyflex_id
5974 ,l_concatenated_segments
5975 ,p_cost_keyflex_structure
5976 ,null
5977 ,null
5978 ,p_summary_flag
5979 ,'Y'
5980 ,p_start_date_active
5981 ,p_end_date_active
5982 ,p_segment1
5983 ,p_segment2
5984 ,p_segment3
5985 ,p_segment4
5986 ,p_segment5
5987 ,p_segment6
5988 ,p_segment7
5989 ,p_segment8
5990 ,p_segment9
5991 ,p_segment10
5992 ,p_segment11
5993 ,p_segment12
5994 ,p_segment13
5995 ,p_segment14
5996 ,p_segment15
5997 ,p_segment16
5998 ,p_segment17
5999 ,p_segment18
6000 ,p_segment19
6001 ,p_segment20
6002 ,p_segment21
6003 ,p_segment22
6004 ,p_segment23
6005 ,p_segment24
6006 ,p_segment25
6007 ,p_segment26
6008 ,p_segment27
6009 ,p_segment28
6010 ,p_segment29
6011 ,p_segment30);
6012 end;
6013 --
6014 end if;
6015 --
6016 return(l_cost_allocation_keyflex_id);
6017 --
6018 end if;
6019 --
6020 return(l_cost_allocation_keyflex_id);
6021 --
6022 end maintain_cost_keyflex;
6023 --
6024 --
6025 -- NAME
6026 -- hr_entry.return_entry_display_status
6027 --
6028 -- DESCRIPTION
6029 -- Used by PAYEEMEE/PAYWSMEE to return current entry statuses during a
6030 -- post-query.
6031 --
6032 procedure return_entry_display_status(p_element_entry_id in number,
6033 p_element_type_id in number,
6034 p_element_link_id in number,
6035 p_assignment_id in number,
6036 p_entry_type in varchar2,
6037 p_session_date in date,
6038 p_additional out nocopy varchar2,
6039 p_adjustment out nocopy varchar2,
6040 p_overridden out nocopy varchar2,
6041 p_processed out nocopy varchar2) is
6042 l_run_result_id number;
6043 l_payroll_id number;
6044 l_overridden varchar2(30) := 'N';
6045 l_start_date date;
6046 l_end_date date;
6047 l_skip_further_checks varchar2(30) := 'N';
6048 l_run_result_status varchar2(30);
6049 begin
6050 g_debug := hr_utility.debug_enabled;
6051 --
6052 -- We need to get the standard assignment payroll and time period details
6053 --
6054 if g_debug then
6055 hr_utility.set_location('hr_entry.return_entry_display_status', 1);
6056 end if;
6057 begin
6058 select a.payroll_id,
6059 t.start_date,
6060 t.end_date
6061 into l_payroll_id,
6062 l_start_date,
6063 l_end_date
6064 from per_time_periods t,
6065 per_assignments_f a
6066 where a.assignment_id = p_assignment_id
6067 and p_session_date
6068 between a.effective_start_date
6069 and a.effective_end_date
6070 and a.payroll_id is not null
6071 and t.payroll_id = a.payroll_id
6072 and p_session_date
6073 between t.start_date
6074 and t.end_date;
6075 exception
6076 when NO_DATA_FOUND then
6077 NULL;
6078 end;
6079 --
6080 -- 1: lets set the additional value.
6081 --
6082 if g_debug then
6083 hr_utility.set_location('hr_entry.return_entry_display_status', 5);
6084 end if;
6085 if (p_entry_type = 'D') then
6086 p_additional := 'Y';
6087 else
6088 p_additional := 'N';
6089 end if;
6090 --
6091 -- 2: lets see if current entry has been processed.
6092 --
6093 if g_debug then
6094 hr_utility.set_location('hr_entry.return_entry_display_status', 10);
6095 end if;
6096 if (l_payroll_id is not null) then
6097 begin
6098 select max(ppr.run_result_id)
6099 into l_run_result_id
6100 from pay_run_results ppr,
6101 pay_assignment_actions paa,
6102 pay_payroll_actions ppa,
6103 pay_action_classifications pac
6104 where ppr.source_id = p_element_entry_id
6105 and ppr.source_type = 'E'
6106 and ppr.entry_type = p_entry_type
6107 and ppr.status <> 'U'
6108 and ppr.element_type_id = p_element_type_id
6109 and ppr.assignment_action_id = paa.assignment_action_id
6110 and paa.assignment_id = p_assignment_id
6111 and paa.action_status = 'C'
6112 and paa.payroll_action_id = ppa.payroll_action_id
6113 and ppa.payroll_id = l_payroll_id
6114 and pac.classification_name = 'QP_PAYRUN'
6115 and ppa.action_type = pac.action_type
6116 and ppa.effective_date
6117 between l_start_date
6118 and l_end_date;
6119 end;
6120 --
6121 -- If the run result does not exist then set l_processed to 'N'.
6122 -- If the run result was processed and is an override then it cannot itself
6123 -- be overridden or adjusted.
6124 --
6125 if g_debug then
6126 hr_utility.set_location('hr_entry.return_entry_display_status', 15);
6127 end if;
6128 if (l_run_result_id is not null and
6129 p_entry_type = 'S') then
6130 p_processed := 'Y';
6131 p_overridden := 'N';
6132 p_adjustment := 'N';
6133 l_skip_further_checks := 'Y';
6134 elsif (l_run_result_id is null) then
6135 p_processed := 'N';
6136 else
6137 p_processed := 'Y';
6138 --
6139 -- As the entry was processed we need to check if it was overridden or
6140 -- Adjusted.
6141 --
6142 if g_debug then
6143 hr_utility.set_location('hr_entry.return_entry_display_status', 20);
6144 end if;
6145 begin
6146 select prr.status
6147 into l_run_result_status
6148 from pay_run_results prr
6149 where prr.run_result_id = l_run_result_id;
6150 exception
6151 when NO_DATA_FOUND then
6152 l_run_result_status := 'UNKNOWN';
6153 end;
6154 --
6155 -- If the l_run_result_status is NOT in 'PA', 'R', 'O' then set the
6156 -- l_override, l_adjusted to 'N' and to don't do any further checks.
6157 --
6158 if g_debug then
6159 hr_utility.set_location('hr_entry.return_entry_display_status', 25);
6160 end if;
6161 if (l_run_result_status <> 'PA' or
6162 l_run_result_status <> 'R' or
6163 l_run_result_status <> 'O') then
6164 p_overridden := 'N';
6165 p_adjustment := 'N';
6166 l_skip_further_checks := 'Y';
6167 end if;
6168 end if;
6169 end if;
6170 --
6171 -- If we need to do further checks then we must check to see if the entry
6172 -- is overridden or adjusted.
6173 --
6174 if (l_skip_further_checks = 'N') then
6175 --
6176 -- 3: Check to see if the entry is overridden.
6177 --
6178 if g_debug then
6179 hr_utility.set_location('hr_entry.return_entry_display_status', 30);
6180 end if;
6181 begin
6182 select 'Y'
6183 into l_overridden
6184 from pay_element_entries_f pee
6185 where p_session_date
6186 between pee.effective_start_date
6187 and pee.effective_end_date
6188 and pee.entry_type = 'S'
6189 and pee.assignment_id = p_assignment_id
6190 and pee.element_link_id = p_element_link_id;
6191 exception
6192 when NO_DATA_FOUND then
6193 p_overridden := 'N';
6194 end;
6195 --
6196 p_overridden := l_overridden;
6197 --
6198 -- 4: If the entry is NOT overridden then check to see if has been adjusted.
6199 --
6200 if (l_overridden = 'N') then
6201 if g_debug then
6202 hr_utility.set_location('hr_entry.return_entry_display_status', 35);
6203 end if;
6204 begin
6205 select 'Y'
6206 into p_adjustment
6207 from pay_element_entries_f pee
6208 where p_session_date
6209 between pee.effective_start_date
6210 and pee.effective_end_date
6211 and (pee.entry_type = 'R'
6212 or pee.entry_type = 'A')
6213 and pee.assignment_id = p_assignment_id
6214 and pee.element_link_id = p_element_link_id
6215 and pee.target_entry_id = p_element_entry_id;
6216 exception
6217 when NO_DATA_FOUND then
6218 p_adjustment := 'N';
6219 end;
6220 end if;
6221 end if;
6222 --
6223 end return_entry_display_status;
6224 --
6225 -- NAME
6226 -- hr_entry.chk_creator_type
6227 --
6228 -- DESCRIPTION
6229 -- Used by PAYEEMEE/PAYWSMEE to restrict DT operations according to the
6230 -- creator type ie. cannot update a balance adjustment etc ...
6231 --
6232 procedure chk_creator_type(p_element_entry_id in number,
6233 p_creator_type in varchar2,
6234 p_quickpay_mode in varchar2,
6235 p_dml_operation in varchar2,
6236 p_dt_update_mode in varchar2,
6237 p_dt_delete_mode in varchar2,
6238 p_validation_start_date in date,
6239 p_validation_end_date in date) is
6240 -- --
6241 --l_creator_meaning varchar2(80);
6242 l_creator_meaning HR_LOOKUPS.meaning%TYPE;
6243 -- --
6244 l_error_flag varchar2(30) := 'N';
6245 l_dt_update_mode varchar2(30) := nvl(p_dt_update_mode, 'CORRECTION');
6246 l_dt_delete_mode varchar2(30) := nvl(p_dt_delete_mode, 'ZAP');
6247 begin
6248 g_debug := hr_utility.debug_enabled;
6249 if (p_creator_type = 'A' or
6250 p_creator_type = 'M' or
6251 p_creator_type = 'S' or
6252 p_creator_type = 'UT' or
6253 p_creator_type = 'B' or
6254 (p_creator_type = 'Q' and
6255 p_quickpay_mode = 'E') or
6256 (p_creator_type = 'SP' and
6257 ((p_dml_operation = 'DELETE' and
6258 l_dt_delete_mode = 'ZAP') or
6259 (p_dml_operation = 'UPDATE' and
6260 l_dt_update_mode = 'CORRECTION')))) then
6261 --
6262 -- We must error because we cannot Update or Delete an entry which is for:
6263 -- A: Absence
6264 -- M: SMP
6265 -- S: SSP
6266 -- Q: QuickPay
6267 -- UT: Us Tax
6268 -- B: Balance Adjustment
6269 -- SP: Salary Admin
6270 --
6271 if g_debug then
6272 hr_utility.set_location('hr_entry.chk_creator_type', 5);
6273 end if;
6274 begin
6275 select h.meaning
6276 into l_creator_meaning
6277 from hr_lookups h
6278 where h.lookup_type = 'CREATOR_TYPE'
6279 and h.lookup_code = p_creator_type;
6280 exception
6281 when NO_DATA_FOUND then
6282 null;
6283 end;
6284 --
6285 if (p_dml_operation = 'UPDATE') then
6286 hr_utility.set_message(801, 'HR_7014_ELE_ENTRY_CREATOR_UPD');
6287 hr_utility.set_message_token('CREATOR_MEANING', l_creator_meaning);
6288 hr_utility.raise_error;
6289 else
6290 hr_utility.set_message(801, 'HR_7015_ELE_ENTRY_CREATOR_DEL');
6291 hr_utility.set_message_token('CREATOR_MEANING', l_creator_meaning);
6292 hr_utility.raise_error;
6293 end if;
6294 --
6295 -- If the creator_type = 'F' then we need to ensure that we are NOT extending
6296 -- or removing entries where a parent 'SP' (Salary Admin) record exists.
6297 --
6298
6299 --
6300 -- If the creator type = 'F' then we need to ensure that we are NOT extending
6301 -- or removing entries where a parent 'SP' (Salary Admin) record exists.
6302 -- Also,
6303 -- If the creator type = 'SP' then we need to ensure that we are NOT extending
6304 -- or removing entries where a parent 'SP' (Salary Admin) record exists.
6305 --
6306 elsif ((p_creator_type = 'F' and
6307 (p_dml_operation = 'DELETE' or
6308 (p_dml_operation = 'UPDATE' and
6309 l_dt_update_mode = 'UPDATE_OVERRIDE'))) or
6310 (p_creator_type = 'SP' and
6311 ((p_dml_operation = 'DELETE' and
6312 l_dt_delete_mode <> 'ZAP') or
6313 (p_dml_operation = 'UPDATE' and
6314 l_dt_update_mode = 'UPDATE_OVERRIDE')))) then
6315 --
6316 if g_debug then
6317 hr_utility.set_location('hr_entry.chk_creator_type', 10);
6318 end if;
6319 begin
6320 select 'Y'
6321 into l_error_flag
6322 from sys.dual
6323 where exists
6324 (select 1
6325 from pay_element_entries_f pee
6326 where pee.element_entry_id = p_element_entry_id
6327 and pee.creator_type = 'SP'
6328 and pee.effective_start_date >= p_validation_start_date);
6329 exception
6330 when NO_DATA_FOUND then
6331 NULL;
6332 end;
6333 --
6334 -- Check to see if the Salary Admin Entry exists:
6335 --
6336 if (l_error_flag = 'Y') then
6337 hr_utility.set_message(801, 'HR_7017_ELE_ENTRY_SP_CORRECT');
6338 hr_utility.raise_error;
6339 end if;
6340 end if;
6341 --
6342 end chk_creator_type;
6343 --
6344 -------------------------------------------------------------------------
6345 -- NAME maintain_covered_dependants
6346 --
6347 -- DESCRIPTION Deals with calls to update the covered dependents of an
6348 -- Element entry.
6349 -- Called by :
6350 -- : hrentmnt.maintain_dependent_entities
6351 --
6352 ----------------------------------------------------------------------------
6353
6354 procedure maintain_covered_dependants
6355
6356 (p_element_entry_id in number,
6357 p_element_entry_ESD in date,
6358 p_element_entry_EED in date,
6359 p_new_element_entry_id in number,
6360 p_new_element_entry_ESD in date,
6361 p_new_element_entry_EED in date)
6362 is
6363 --
6364 -- Set of covered dependants which are children of the updated element entry
6365 --
6366 cursor csr_covered_dependents is
6367 select rowid,
6368 dep.*
6369 from ben_covered_dependents_f DEP
6370 where p_element_entry_EED between dep.effective_start_date and dep.effective_end_date
6371 and dep.element_entry_id = p_element_entry_id
6372 and not exists ( select null from ben_covered_dependents_f DEP2
6373 where dep2.element_entry_id = p_new_element_entry_id
6374 and dep2.contact_relationship_id = dep.contact_relationship_id
6375 and dep2.effective_start_date between
6376 p_new_element_entry_ESD and p_new_element_entry_EED);
6377 --
6378
6379 v_start_date date;
6380 v_end_date date;
6381 l_rowid rowid;
6382 l_covered_dependent_id ben_covered_dependents_f.COVERED_DEPENDENT_ID%type;
6383 l_action varchar2(10);
6384
6385 begin
6386
6387 if p_element_entry_EED < p_element_entry_ESD then
6388
6389 -- bug 10008908 cater for datetrack events that cause entry to be deleted and replaced
6390 -- with new entry- point dependent rows at new replacement entry
6391
6392 if g_debug then
6393 hr_utility.trace('hr_entry.maintain_covered_dependants. entry replaced. dependants entry_id:'||
6394 to_char(p_element_entry_id)||
6395 ' switched to ' ||to_char(p_new_element_entry_id));
6396 end if;
6397
6398 -- bug 13031629 , fix : do not update element_entry_id if there are no future element_entries
6399
6400 if p_new_element_entry_id is null then
6401 update ben_covered_dependents_f set effective_end_date = p_element_entry_EED
6402 where element_entry_id = p_element_entry_id;
6403 else
6404 update ben_covered_dependents_f set element_entry_id = p_new_element_entry_id
6405 where element_entry_id = p_element_entry_id;
6406 end if;
6407 --
6408 else
6409 for dependant in csr_covered_dependents LOOP
6410
6411 --
6412
6413 if dependant.effective_end_date > p_element_entry_EED then
6414 --
6415 -- set the end date for covered_dependent to old entry end date
6416 --
6417 v_start_date := dependant.effective_start_date;
6418 v_end_date := p_element_entry_EED;
6419
6420
6421 ben_covered_dependents_pkg.update_row (
6422 --
6423 p_covered_dependent_id => dependant.covered_dependent_id,
6424 p_rowid => dependant.rowid,
6425 p_contact_relationship_id => dependant.contact_relationship_id,
6426 p_element_entry_id => dependant.element_entry_id,
6427 p_effective_start_date => v_start_date,
6428 p_effective_end_date => v_end_date);
6429 --
6430 end if;
6431
6432 -- create the new covered_dependent rows for the new entry
6433
6434 v_start_date := p_new_element_entry_ESD;
6435 v_end_date := least(dependant.effective_end_date,p_new_element_entry_EED);
6436 l_covered_dependent_id := null;
6437
6438 --bug 13031629 , fix : do not insert if there are no future element_entries
6439
6440 if ( (p_new_element_entry_id is NOT NULL) and (p_new_element_entry_ESD is NOT NULL)
6441 and (p_new_element_entry_EED is NOT NULL) ) then
6442
6443 ben_covered_dependents_pkg.insert_row (
6444 --
6445 p_covered_dependent_id => l_covered_dependent_id,
6446 p_rowid => l_rowid,
6447 p_contact_relationship_id => dependant.contact_relationship_id,
6448 p_element_entry_id => p_new_element_entry_id,
6449 p_effective_start_date => p_new_element_entry_ESD,
6450 p_effective_end_date => p_new_element_entry_EED);
6451 end if;
6452 --
6453 --
6454 end loop;
6455 end if;
6456
6457 end maintain_covered_dependants;
6458 -------------------------------------------------------------------------
6459 -- NAME maintain_beneficiaries
6460 --
6461 -- DESCRIPTION Deals with calls to update the beneficiaries of an
6462 -- Element entry.
6463 -- Called by :
6464 -- : hrentmnt.maintain_dependent_entities
6465 --
6466 ----------------------------------------------------------------------------
6467
6468 procedure maintain_beneficiaries
6469
6470 (p_element_entry_id in number,
6471 p_element_entry_ESD in date,
6472 p_element_entry_EED in date,
6473 p_new_element_entry_id in number,
6474 p_new_element_entry_ESD in date,
6475 p_new_element_entry_EED in date)
6476 is
6477 --
6478 -- Set of beneficiaries which are children of the updated element entry
6479 --
6480 cursor csr_beneficiaries is
6481 select rowid,
6482 ben.*
6483 from ben_beneficiaries_f BEN
6484 where p_element_entry_EED between ben.effective_start_date and ben.effective_end_date
6485 and ben.element_entry_id = p_element_entry_id
6486 and not exists ( select null from ben_beneficiaries_f BEN2
6487 where ben2.element_entry_id = p_new_element_entry_id
6488 and ben2.source_id = ben.source_id
6489 and ben2.effective_start_date between
6490 p_new_element_entry_ESD and p_new_element_entry_EED);
6491 --
6492
6493 v_start_date date;
6494 v_end_date date;
6495 l_rowid rowid;
6496 l_beneficiary_id ben_beneficiaries_f.BENEFICIARY_ID%type;
6497
6498 begin
6499
6500 if p_element_entry_EED < p_element_entry_ESD then
6501
6502 -- bug 10008908 cater for datetrack events that cause entry to be deleted and replaced
6503 -- with new entry- point dependent rows at new replacement entry
6504
6505 -- bug 13031629 , fix : do not update element_entry_id if there are no future element_entries
6506
6507 if p_new_element_entry_id is null then
6508 update ben_beneficiaries_f set effective_end_date = p_element_entry_EED
6509 where element_entry_id = p_element_entry_id;
6510 else
6511 update ben_beneficiaries_f set element_entry_id = p_new_element_entry_id
6512 where element_entry_id = p_element_entry_id;
6513 end if;
6514 --
6515 else
6516
6517 --
6518 for beneficiary in csr_beneficiaries LOOP
6519
6520 --
6521 if beneficiary.effective_end_date <> p_element_entry_EED then
6522 --
6523 -- set the end date for beneficiary to old entry end date
6524 --
6525 v_start_date := beneficiary.effective_start_date;
6526 v_end_date := p_element_entry_EED;
6527
6528 ben_beneficiaries_pkg.update_row (
6529 --
6530 p_rowid => beneficiary.rowid,
6531 p_source_type => beneficiary.source_type,
6532 p_source_id => beneficiary.source_id,
6533 p_element_entry_id => beneficiary.element_entry_id,
6534 p_benefit_level => beneficiary.benefit_level,
6535 p_proportion => beneficiary.proportion,
6536 p_beneficiary_id => beneficiary.beneficiary_id,
6537 p_effective_start_date => v_start_date,
6538 p_effective_end_date => v_end_date);
6539 --
6540 end if;
6541
6542 -- create the new beneficiaries rows for the new entry
6543
6544 v_start_date := p_new_element_entry_ESD;
6545 v_end_date := least(beneficiary.effective_end_date,p_new_element_entry_EED);
6546 l_beneficiary_id := null;
6547
6548 --bug 13031629 , fix : do not insert if there are no future element_entries
6549
6550 if ( (p_new_element_entry_id is NOT NULL)
6551 and (p_new_element_entry_ESD is NOT NULL)
6552 and (p_new_element_entry_EED is NOT NULL) ) then
6553 ben_beneficiaries_pkg.insert_row (
6554 --
6555 p_rowid => l_rowid,
6556 p_beneficiary_id => l_beneficiary_id,
6557 p_source_type => beneficiary.source_type,
6558 p_source_id => beneficiary.source_id,
6559 p_element_entry_id => p_new_element_entry_id,
6560 p_benefit_level => beneficiary.benefit_level,
6561 p_proportion => beneficiary.proportion,
6562 p_effective_start_date => p_new_element_entry_ESD,
6563 p_effective_end_date => p_new_element_entry_EED);
6564 end if;
6565 --
6566 --
6567 end loop;
6568 end if;
6569 end maintain_beneficiaries;
6570 end hr_entry;
[Home] [Help]