1 package body hrentmnt as
2 /* $Header: pyentmnt.pkb 120.32.12010000.6 2008/09/30 06:24:40 salogana ship $ */
3 --
4 g_debug boolean := hr_utility.debug_enabled;
5 indent varchar2 (32767) := null;
6 g_package constant varchar2 (32) := 'hrentmnt.';
7 --
8 -- Record type to cache an element entry for use when splitting into two.
9 --
10 type t_ele_entry_rec is record
11 (cost_allocation_keyflex_id number,
12 creator_type varchar2(10),
13 entry_type varchar2(1),
14 comment_id number,
15 creator_id number,
16 reason varchar2(30),
17 target_entry_id number,
18
19 subpriority number,
20 personal_payment_method_id number,
21 date_earned date,
22
23 balance_adj_cost_flag varchar2(30),
24 source_asg_action_id number,
25 source_link_id number,
26 source_trigger_entry number,
27 source_period number,
28 source_run_type number,
29 source_start_date date,
30 source_end_date date,
31
32 attribute_category varchar2(30),
33 attribute1 varchar2(150),
34 attribute2 varchar2(150),
35 attribute3 varchar2(150),
36 attribute4 varchar2(150),
37 attribute5 varchar2(150),
38 attribute6 varchar2(150),
39 attribute7 varchar2(150),
40 attribute8 varchar2(150),
41 attribute9 varchar2(150),
42 attribute10 varchar2(150),
43 attribute11 varchar2(150),
44 attribute12 varchar2(150),
45 attribute13 varchar2(150),
46 attribute14 varchar2(150),
47 attribute15 varchar2(150),
48 attribute16 varchar2(150),
49 attribute17 varchar2(150),
50 attribute18 varchar2(150),
51 attribute19 varchar2(150),
52 attribute20 varchar2(150),
53
54 entry_information_category varchar2(30),
55 entry_information1 varchar2(150),
56 entry_information2 varchar2(150),
57 entry_information3 varchar2(150),
58 entry_information4 varchar2(150),
59 entry_information5 varchar2(150),
60 entry_information6 varchar2(150),
61 entry_information7 varchar2(150),
62 entry_information8 varchar2(150),
63 entry_information9 varchar2(150),
64 entry_information10 varchar2(150),
65 entry_information11 varchar2(150),
66 entry_information12 varchar2(150),
67 entry_information13 varchar2(150),
68 entry_information14 varchar2(150),
69 entry_information15 varchar2(150),
70 entry_information16 varchar2(150),
71 entry_information17 varchar2(150),
72 entry_information18 varchar2(150),
73 entry_information19 varchar2(150),
74 entry_information20 varchar2(150),
75 entry_information21 varchar2(150),
76 entry_information22 varchar2(150),
77 entry_information23 varchar2(150),
78 entry_information24 varchar2(150),
79 entry_information25 varchar2(150),
80 entry_information26 varchar2(150),
81 entry_information27 varchar2(150),
82 entry_information28 varchar2(150),
83 entry_information29 varchar2(150),
84 entry_information30 varchar2(150));
85 --
86 -- Record type and ref cursor type to hold assignment eligibility criteria, i.e.
87 -- payroll_id, organization_id, people_group_id etc.
88 -- Used by recreate_cached_entry and val_nonrec_entries.
89 type t_asg_criteria_rec is record (
90 organization_id number,
91 people_group_id number,
92 job_id number,
93 position_id number,
94 grade_id number,
95 location_id number,
96 employment_category varchar2(30),
97 payroll_id number,
98 pay_basis_id number,
99 business_group_id number);
100 --
101 type t_asg_criteria_cur is ref cursor return t_asg_criteria_rec;
102 --
103 -- Record type and ref cursor type to hold min/max dates of an element link
104 -- that matches certain eligibility criteria.
105 -- Used by recreate_cached_entry and val_nonrec_entries when fetching
106 -- alternative element links for existing invalidated entries.
107 type t_eligible_links_rec is record (
108 element_link_id number,
109 effective_start_date date,
110 effective_end_date date);
111 --
112 type t_eligible_links_cur is ref cursor return t_eligible_links_rec;
113 --
114 ------------------------------------------------------------------------------
115 -- NAME --
116 -- hrentmnt.min_eligibility_date --
117 -- --
118 -- DESCRIPTION --
119 -- Calculates the minimum date an element entries start date could be set --
120 -- to taking into account the eligibility of the element entry over time. --
121 -- NOTES --
122 -- New functionality added in response to WWBug 278071. This is used within --
123 -- adjust_entries_pqc to tighten up the setting of element entry start --
124 -- date in response to changing personal qualifying conditions. --
125 ------------------------------------------------------------------------------
126 --
127 function min_eligibility_date
128 (
129 p_element_link_id number,
130 p_assignment_id number,
131 p_range_start_date date,
132 p_range_end_date date
133 ) return date is
134 --
135 -- Returns the eligibility criteria for an element link.
136 --
137 cursor csr_link
138 (
139 p_element_link_id number
140 ) is
141 select EL.element_link_id,
142 EL.effective_start_date,
143 EL.link_to_all_payrolls_flag,
144 EL.payroll_id,
145 EL.job_id,
146 EL.grade_id,
147 EL.position_id,
148 EL.organization_id,
149 EL.location_id,
150 EL.pay_basis_id,
151 EL.employment_category,
152 EL.people_group_id
153 from pay_element_links_f EL
154 where EL.element_link_id = p_element_link_id
155 order by EL.effective_start_date;
156 --
157 -- Fetches all assignment rows for a given assignment that match specific
158 -- elgibility criteria over a range of dates NB. they are returned in
159 -- reverse order.
160 --
161 -- EL |-------------------------------A--------------------------------->
162 --
163 -- ASG |----A---|----A----|------B-----|-----A-----|------A-----|----B--->
164 --
165 -- Range |---------------------------------------------------->
166 --
167 -- Fetch 1 |------A-----|
168 -- Fetch 2 |-----A-----|
169 -- Fetch 3 |----A----|
170 --
171 cursor csr_assignment
172 (
173 p_element_link_id number,
174 p_assignment_id number,
175 p_range_start_date date,
176 p_range_end_date date,
177 p_payroll_id number,
178 p_link_to_all_payrolls_flag varchar2,
179 p_job_id number,
180 p_grade_id number,
181 p_position_id number,
182 p_organization_id number,
183 p_location_id number,
184 p_pay_basis_id number,
185 p_employment_category varchar2,
186 p_people_group_id number
187 ) is
188 select ASG.effective_start_date,
189 ASG.effective_end_date
190 from per_all_assignments_f ASG
191 where ASG.assignment_id = p_assignment_id
192 and ASG.assignment_type = 'E'
193 and ASG.effective_start_date <= p_range_end_date
194 and ASG.effective_end_date >= p_range_start_date
195 and ((p_payroll_id is not null and
196 p_payroll_id = ASG.payroll_id)
197 or (p_link_to_all_payrolls_flag = 'Y' and
198 ASG.payroll_id is not null)
199 or (p_link_to_all_payrolls_flag = 'N' and
200 p_payroll_id is null))
201 and (p_job_id is null or
202 p_job_id = ASG.job_id)
203 and (p_grade_id is null or
204 p_grade_id = ASG.grade_id)
205 and (p_position_id is null or
206 p_position_id = ASG.position_id)
207 and (p_organization_id is null or
208 p_organization_id = ASG.organization_id)
209 and (p_location_id is null or
210 p_location_id = ASG.location_id)
211 and (p_pay_basis_id is null or
212 p_pay_basis_id = ASG.pay_basis_id)
213 and (p_employment_category is null or
214 p_employment_category = ASG.employment_category)
215 and (p_people_group_id is null or
216 exists
217 (select null
218 from pay_assignment_link_usages_f ALU
219 where ALU.assignment_id = p_assignment_id
220 and ALU.element_link_id = p_element_link_id
221 and ALU.effective_start_date <= ASG.effective_end_date
222 and ALU.effective_end_date >= ASG.effective_start_date))
223 order by ASG.effective_start_date desc;
224 --
225 -- Record to hold a row fetched using the csr_link cursor.
226 --
227 v_link_rec csr_link%rowtype;
228 --
229 -- Record to hold a row fetched using the csr_link cursor.
230 --
231 v_asg_rec csr_assignment%rowtype;
232 --
233 -- Variables to hold the start and ends dates of the current assignment row.
234 --
235 v_asg_start_date date;
236 v_asg_end_date date;
237 --
238 procedure check_parameters is
239 begin
240 --
241 hr_utility.trace('In min_eligibility_date');
242 hr_utility.trace ('');
243 hr_utility.trace (' p_element_link_id = '
244 ||to_char (p_element_link_id));
245 hr_utility.trace (' p_assignment_id'
246 ||to_char (p_assignment_id));
247 hr_utility.trace (' p_range_start_date'
248 ||to_char (p_range_start_date));
249 hr_utility.trace (' p_range_end_date'
250 ||to_char (p_range_end_date));
251 hr_utility.trace ('');
252 --
253 end check_parameters;
254 --
255 begin
256 --
257 if g_debug then
258 check_parameters;
259 end if;
260 --
261 --
262 -- Fetch the first date effective row of the link NB. the criteria cannot
263 -- be date effectively changed on a link so the criteria should be the same
264 -- for all rows.
265 --
266 open csr_link(p_element_link_id);
267 fetch csr_link into v_link_rec;
268 if csr_link%notfound then
269 close csr_link;
270 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
271 hr_utility.set_message_token('PROCEDURE', 'hrentmnt.min_eligibility_date');
272 hr_utility.set_message_token('STEP','1');
273 hr_utility.raise_error;
274 end if;
275 close csr_link;
276 --
277 --
278 -- Open the cursor ready for processing.
279 --
280 open csr_assignment
281 (p_element_link_id,
282 p_assignment_id,
283 p_range_start_date,
284 p_range_end_date,
285 v_link_rec.payroll_id,
286 v_link_rec.link_to_all_payrolls_flag,
287 v_link_rec.job_id,
288 v_link_rec.grade_id,
289 v_link_rec.position_id,
290 v_link_rec.organization_id,
291 v_link_rec.location_id,
292 v_link_rec.pay_basis_id,
293 v_link_rec.employment_category,
294 v_link_rec.people_group_id);
295 --
296 --
297 -- Get first assignment row that matches the criteria. The assumption is
298 -- that the there should be at least one row that matches. This assumption
299 -- is based on the intended use of this function ie. an entry should already
300 -- exist for the assignment / link combination at the end of the seerch
301 -- range.
302 --
303 fetch csr_assignment into v_asg_rec;
304 if csr_assignment%notfound then
305 close csr_assignment;
306 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
307 hr_utility.set_message_token('PROCEDURE', 'hrentmnt.min_eligibility_date');
308 hr_utility.set_message_token('STEP','2');
309 hr_utility.raise_error;
310 end if;
311 --
312 --
313 -- Initialise variables holding the start and end dates of the current
314 -- assignment row from the cursor.
315 --
316 v_asg_start_date := v_asg_rec.effective_start_date;
317 v_asg_end_date := v_asg_rec.effective_end_date;
318 --
319 -- Fetch all the assignment rows that match the criteria over the range of
320 -- dates NB. they are returned in reverse order ie. latest first. Each row
321 -- is compared to the previous row to see if the eligibility criteria is
322 -- contiguous. If not then we have found the minimum date on which an
323 -- element entry could start given that it existed at the end of the range
324 -- of dates over which the assignment rows were found ie.
325 --
326 -- EE |-------------------------------------->
327 --
328 -- EL |----------------------------A------------------------------->
329 -- .
330 -- ASG |---A--|-B--|--A--|------A------|--------A-------|-----A----->
331 -- .
332 -- Range |---------------------|
333 -- '
334 -- New EE |------------------------------------------------>
335 --
336 loop
337 --
338 --
339 -- Get next assignment row that matches the criteria.
340 --
341 fetch csr_assignment into v_asg_rec;
342 --
343 -- Exit the loop if there are no more assignment rows or the new assignment
344 -- row is not contiguous with the previous assignment row found ie. there
345 -- is a break in eligibility.
346 --
347 exit when (csr_assignment%notfound or
348 v_asg_rec.effective_end_date <> v_asg_start_date - 1);
349 --
350 -- Set variables to hold the start and end dates of the current
351 -- assignment row from the cursor. These can then be used for comparisons
352 -- during the next iteration of the loop.
353 --
354 v_asg_start_date := v_asg_rec.effective_start_date;
355 v_asg_end_date := v_asg_rec.effective_end_date;
356 --
357 end loop;
358 --
359 --
360 close csr_assignment;
361 --
362 -- Calculate the minimum eligibility date based on when the element link
363 -- starts and the assignments eligibility for the link ie.
364 --
365 -- EL |----------------------------A------------------------------>
366 --
367 -- ASG |------A------|------A-------|---------------B----------------->
368 --
369 -- Range |--------------------------|
370 --
371 -- Compare |----------.... (EL)
372 -- |------A---.... (ASG)
373 --
374 -- Greatest |----------... (EL)
375 --
376 return(greatest(v_link_rec.effective_start_date,v_asg_start_date));
377 --
378 end min_eligibility_date;
379 --
380 ------------------------------------------------------------------------------
381 -- NAME --
382 -- hrentmnt.maintain_alu_asg --
383 -- --
384 -- DESCRIPTION --
385 -- Maintains the ALU's for an assignment. An ALU represnts the intersection --
386 -- of the people group flexfield on the assignment and element link. The --
387 -- ALU is used to speed up quickpicks where the partial matching of 30 --
388 -- columns causes performance problems. --
389 ------------------------------------------------------------------------------
390 --
391 procedure maintain_alu_asg
392 (
393 p_assignment_id number,
394 p_business_group_id number,
395 p_dt_mode varchar2,
396 p_old_people_group_id number,
397 p_new_people_group_id number
398 ) is
399 --
400 -- user defined types
401 --
402 type t_asg_rec is record
403 (effective_start_date date,
404 effective_end_date date,
405 people_group_id number,
406 id_flex_num number);
407 --
408 -- Bugfix 3720575
409 -- Added these extra user-defined types to enable bulk-inserts...
410 type t_alu_id is table of pay_assignment_link_usages_f.assignment_link_usage_id%type
411 index by binary_integer;
412 --
413 type t_alu_start_date is table of pay_assignment_link_usages_f.effective_start_date%type
414 index by binary_integer;
415 --
416 type t_alu_end_date is table of pay_assignment_link_usages_f.effective_end_date%type
417 index by binary_integer;
418 --
419 type t_alu_link_id is table of pay_assignment_link_usages_f.element_link_id%type
420 index by binary_integer;
421 --
422 -- find all instances of the assignment that has a people group
423 --
424 cursor csr_assignment
425 (
426 p_assignment_id number
427 ) is
428 select asg.effective_start_date,
429 asg.effective_end_date,
430 asg.people_group_id,
431 ppg.id_flex_num
432 from per_all_assignments_f asg,
433 pay_people_groups ppg
434 where asg.assignment_id = p_assignment_id
435 and asg.people_group_id is not null
436 and asg.assignment_type not in ('A' ,'O') -- non-applicant assignments only
437 and ppg.people_group_id = asg.people_group_id
438 order by asg.effective_start_date;
439 --
440 -- find all element links that are match the people group
441 --
442 cursor csr_link
443 (
444 p_id_flex_num number,
445 p_business_group_id number,
446 p_people_group_id number,
447 p_effective_start_date date,
448 p_effective_end_date date
449 ) is
450 select el.element_link_id,
451 min(el.effective_start_date) effective_start_date,
452 max(el.effective_end_date) effective_end_date
453 from pay_element_links_f el,
454 pay_people_groups el_pg,
455 pay_people_groups asg_pg
456 where asg_pg.id_flex_num = p_id_flex_num
457 and asg_pg.people_group_id = p_people_group_id
458 and el_pg.id_flex_num = asg_pg.id_flex_num
459 and el.business_group_id + 0 = p_business_group_id
460 and el.effective_start_date <= p_effective_end_date
461 and el.effective_end_date >= p_effective_start_date
462 and el_pg.people_group_id = el.people_group_id
463 and (el_pg.segment1 is null or el_pg.segment1 = asg_pg.segment1)
464 and (el_pg.segment2 is null or el_pg.segment2 = asg_pg.segment2)
465 and (el_pg.segment3 is null or el_pg.segment3 = asg_pg.segment3)
466 and (el_pg.segment4 is null or el_pg.segment4 = asg_pg.segment4)
467 and (el_pg.segment5 is null or el_pg.segment5 = asg_pg.segment5)
468 and (el_pg.segment6 is null or el_pg.segment6 = asg_pg.segment6)
469 and (el_pg.segment7 is null or el_pg.segment7 = asg_pg.segment7)
470 and (el_pg.segment8 is null or el_pg.segment8 = asg_pg.segment8)
471 and (el_pg.segment9 is null or el_pg.segment9 = asg_pg.segment9)
472 and (el_pg.segment10 is null or el_pg.segment10 = asg_pg.segment10)
473 and (el_pg.segment11 is null or el_pg.segment11 = asg_pg.segment11)
474 and (el_pg.segment12 is null or el_pg.segment12 = asg_pg.segment12)
475 and (el_pg.segment13 is null or el_pg.segment13 = asg_pg.segment13)
476 and (el_pg.segment14 is null or el_pg.segment14 = asg_pg.segment14)
477 and (el_pg.segment15 is null or el_pg.segment15 = asg_pg.segment15)
478 and (el_pg.segment16 is null or el_pg.segment16 = asg_pg.segment16)
479 and (el_pg.segment17 is null or el_pg.segment17 = asg_pg.segment17)
480 and (el_pg.segment18 is null or el_pg.segment18 = asg_pg.segment18)
481 and (el_pg.segment19 is null or el_pg.segment19 = asg_pg.segment19)
482 and (el_pg.segment20 is null or el_pg.segment20 = asg_pg.segment20)
483 and (el_pg.segment21 is null or el_pg.segment21 = asg_pg.segment21)
484 and (el_pg.segment22 is null or el_pg.segment22 = asg_pg.segment22)
485 and (el_pg.segment23 is null or el_pg.segment23 = asg_pg.segment23)
486 and (el_pg.segment24 is null or el_pg.segment24 = asg_pg.segment24)
487 and (el_pg.segment25 is null or el_pg.segment25 = asg_pg.segment25)
488 and (el_pg.segment26 is null or el_pg.segment26 = asg_pg.segment26)
489 and (el_pg.segment27 is null or el_pg.segment27 = asg_pg.segment27)
490 and (el_pg.segment28 is null or el_pg.segment28 = asg_pg.segment28)
491 and (el_pg.segment29 is null or el_pg.segment29 = asg_pg.segment29)
492 and (el_pg.segment30 is null or el_pg.segment30 = asg_pg.segment30)
493 group by el.element_link_id;
494 --
495 -- local variables
496 --
497 v_assignment t_asg_rec;
498 v_asg_start_date date;
499 v_asg_end_date date;
500 v_people_group_id number;
501 v_id_flex_num number;
502 v_alu_start_date date;
503 v_alu_end_date date;
504 v_alu_term_date date;
505 v_dummy_date date;
506 -- Bugfix 3720575
507 -- Added these extra local variables types to enable bulk-inserts...
508 v_counter number := 0;
509 v_alu_id_tab t_alu_id;
510 v_alu_start_date_tab t_alu_start_date;
511 v_alu_end_date_tab t_alu_end_date;
512 v_alu_link_id_tab t_alu_link_id;
513 --
514 l_proc varchar2 (72);
515 --
516 procedure check_parameters is
517 begin
518 --
519 hr_utility.trace('In '||l_proc);
520 hr_utility.trace ('');
521 hr_utility.trace (' p_assignment_id = '
522 ||to_char (p_assignment_id));
523 --
524 hr_utility.trace (' p_business_group_id = '
525 ||to_char (p_business_group_id));
526 --
527 hr_utility.trace (' p_dt_mode = '
528 ||p_dt_mode);
529 --
530 hr_utility.trace (' p_old_people_group_id = '
531 ||to_char (p_old_people_group_id));
532 --
533 hr_utility.trace (' p_new_people_group_id = '
534 ||to_char (p_new_people_group_id));
535 --
536 hr_utility.trace ('');
537 --
538 end check_parameters;
539 --
540 begin
541 --
542 if g_debug then
543 l_proc := g_package||'maintain_alu_asg';
544 check_parameters;
545 end if;
546 --
547 -- Bugfix 3720575
548 -- Only rebuild Assignment Link Usages when there has been a genuine change
549 -- in people group.
550 --
551 -- N.B: When both p_old_people_group_id and p_new_people_group_id are null
552 -- we want to perform the rebuild, this is probably because the calling
553 -- code is not setting these 2 params properly.
554 --
555 if (nvl(p_old_people_group_id,-1) <> nvl(p_new_people_group_id,-2)
556 and p_dt_mode in ('UPDATE',
557 'UPDATE_CHANGE_INSERT',
558 'CORRECTION')
559 )
560 -- Always rebuild for the following DT modes:
561 or p_dt_mode in ('INSERT',
562 'UPDATE_OVERRIDE',
563 'DELETE',
564 'FUTURE_CHANGE',
565 'DELETE_NEXT_CHANGE',
566 'ZAP')
567 then
568 --
569 -- Delete all the alu's for the assignment
570 --
571 delete from pay_assignment_link_usages_f alu
572 where alu.assignment_id = p_assignment_id;
573 --
574 --
575 open csr_assignment(p_assignment_id);
576 --
577 -- get first assignment record to initialise variables
578 --
579 fetch csr_assignment into v_assignment;
580 if csr_assignment%found then
581 --
582 -- set up variables for use in loop
583 --
584 v_asg_start_date := v_assignment.effective_start_date;
585 v_asg_end_date := v_assignment.effective_end_date;
586 v_people_group_id := v_assignment.people_group_id;
587 v_id_flex_num := v_assignment.id_flex_num;
588 --
589 while csr_assignment%found loop
590 --
591 --
592 -- get next assignment record
593 --
594 fetch csr_assignment into v_assignment;
595 --
596 -- detect change of people group , non-contiguous people groups or
597 -- that the last record has been read
598 --
599 if csr_assignment%notfound or not
600 (v_assignment.people_group_id = v_people_group_id and
601 v_assignment.effective_start_date = v_asg_end_date + 1) then
602 --
603 --
604 -- find all links that overlap with the assignment and have the same
605 -- people group as the assignment
606 --
607 for v_link in csr_link(v_id_flex_num,
608 p_business_group_id,
609 v_people_group_id,
610 v_asg_start_date,
611 v_asg_end_date) loop
612 --
613 -- calculate the start date of the alu which is the greatest of
614 -- the start dates of the link and assignment
615 --
616 v_alu_start_date := greatest(v_asg_start_date,
617 v_link.effective_start_date);
618 --
619 --
620 -- find the termination date of the alu if the person has been
621 -- terminated ie. taking inot account the termination processing
622 -- rule of the element type. if no termination has taken place
623 -- then the date returned is the end of time.
624 -- nb. v_dummy_date is used to soak up some out parameters that
625 -- are not required.
626 --
627 -- Bug 5202396.
628 -- The check for termination rule caused a significant performance
629 -- issue. Since ALU is only a part of the link eligibility rules,
630 -- we can determine the end date with the link and the assignment.
631 --
632 /***
633 hr_entry.entry_asg_pay_link_dates(p_assignment_id,
634 v_link.element_link_id,
635 v_alu_start_date,
636 v_alu_term_date,
637 v_dummy_date,
638 v_dummy_date,
639 v_dummy_date,
640 v_dummy_date,
641 false);
642 ***/
643 --
644 -- calculate the end date of the alu which is the least of the
645 -- end dates of the link and assignment.
646 --
647 v_alu_end_date := least(v_asg_end_date,
648 v_link.effective_end_date);
649 --
650 -- Make sure that the alu start date is on or before the end date
651 --
652 if v_alu_start_date <= v_alu_end_date then
653 --
654 -- Bugfix 3720575
655 -- Cache the ALU details so they can be bulk-inserted later...
656 --
657 v_counter := v_counter + 1;
658 --
659 -- Bug 5202396.
660 -- The sequence values are now directly obtained in the
661 -- insert statement.
662 --
663 -- select pay_assignment_link_usages_s.nextval
664 -- into v_alu_id_tab(v_counter)
665 -- from dual;
666 --
667 v_alu_start_date_tab(v_counter) := v_alu_start_date;
668 v_alu_end_date_tab(v_counter) := v_alu_end_date;
669 v_alu_link_id_tab(v_counter) := v_link.element_link_id;
670 --
671 end if;
672 --
673 end loop;
674 --
675 -- reset start and end dates
676 --
677 v_asg_start_date := v_assignment.effective_start_date;
678 v_asg_end_date := v_assignment.effective_end_date;
679 --
680 else
681 --
682 -- increment end date of assignment
683 --
684 v_asg_end_date := v_assignment.effective_end_date;
685 --
686 end if;
687 --
688 -- save value for future comparison
689 --
690 v_people_group_id := v_assignment.people_group_id;
691 v_id_flex_num := v_assignment.id_flex_num;
692 --
693 -- Bugfix 3720575
694 -- Create the ALUs in bulk
695 --
696 forall i in 1..v_counter
697 insert into pay_assignment_link_usages_f
698 (assignment_link_usage_id,
699 effective_start_date,
700 effective_end_date,
701 element_link_id,
702 assignment_id)
703 values
704 (
705 pay_assignment_link_usages_s.nextval,
706 v_alu_start_date_tab(i),
707 v_alu_end_date_tab(i),
708 v_alu_link_id_tab(i),
709 p_assignment_id
710 );
711 --
712 v_counter := 0;
713 v_alu_id_tab.delete;
714 v_alu_start_date_tab.delete;
715 v_alu_end_date_tab.delete;
716 v_alu_link_id_tab.delete;
717 --
718 end loop;
719 --
720 end if;
721 --
722 close csr_assignment;
723 --
724 end if;
725 --
726 if g_debug then
727 hr_utility.trace('Out '||l_proc);
728 end if;
729 --
730 end maintain_alu_asg;
731 --
732 ------------------------------------------------------------------------------
733 -- NAME --
734 -- hrentmnt.remove_pay_proposals --
735 -- --
736 -- DESCRIPTION --
737 -- Salary Admin specific procedure that will remove a pay proposal if there --
738 -- are no element entries for the pay proposal. --
739 ------------------------------------------------------------------------------
740 --
741 procedure remove_pay_proposals
742 (
743 p_assignment_id number,
744 p_pay_proposal_id number
745 ) is
746 begin
747 --
748 -- Remove the pay proposal if there are no element entries for it.
749 --
750 delete from per_pay_proposals pp
751 where pp.assignment_id = p_assignment_id
752 and pp.pay_proposal_id = p_pay_proposal_id
753 and not exists
754 (select null
755 from pay_element_entries_f ee
756 where ee.assignment_id = pp.assignment_id
757 and ee.creator_type = 'SP'
758 and ee.creator_id = pp.pay_proposal_id);
759 end remove_pay_proposals;
760 --
761 ------------------------------------------------------------------------------
762 -- NAME --
763 -- hrentmnt.remove_quickpay_inclusions --
764 -- --
765 -- DESCRIPTION --
766 -- Removes any quickpay inclusions for which the element entry no longer --
767 -- date effectively exists NB. the procedure is used with the assumption --
768 -- that the caller has identified the period over which the element entry --
769 -- is being removed. Added due to WW Bug 269356. --
770 ------------------------------------------------------------------------------
771 --
772 procedure remove_quickpay_inclusions
773 (
774 p_element_entry_id number,
775 p_validation_start_date date,
776 p_validation_end_date date
777 ) is
778 begin
779 --
780 -- Remove any quickpay inclusions for the element entry where the element
781 -- entry no longer date effectively exists.
782 --
783 delete from pay_quickpay_inclusions pqi
784 where pqi.element_entry_id = p_element_entry_id
785 and exists
786 (select null
787 from pay_assignment_actions paa,
788 pay_payroll_actions ppa
789 where paa.assignment_action_id = pqi.assignment_action_id
790 and ppa.payroll_action_id = paa.payroll_action_id
791 and ppa.date_earned between p_validation_start_date
792 and p_validation_end_date);
793 --
794 end remove_quickpay_inclusions;
795 --
796 ------------------------------------------------------------------------------
797 -- NAME --
798 -- hrentmnt.remove_quickpay_exclusions --
799 -- --
800 -- DESCRIPTION --
801 -- Introduced via enhancement 3368211
802 -- Removes any quickpay exclusions for which the element entry no longer --
803 -- date effectively exists NB. the procedure is used with the assumption --
804 -- that the caller has identified the period over which the element entry --
805 -- is being removed. --
806 ------------------------------------------------------------------------------
807 --
808 procedure remove_quickpay_exclusions
809 (
810 p_element_entry_id number,
811 p_validation_start_date date,
812 p_validation_end_date date
813 ) is
814 begin
815 --
816 -- Remove any quickpay exclusions for the element entry where the element
817 -- entry no longer date effectively exists.
818 --
819 delete from pay_quickpay_exclusions pqe
820 where pqe.element_entry_id = p_element_entry_id
821 and exists
822 (select null
823 from pay_assignment_actions paa,
824 pay_payroll_actions ppa
825 where paa.assignment_action_id = pqe.assignment_action_id
826 and ppa.payroll_action_id = paa.payroll_action_id
827 and ppa.date_earned between p_validation_start_date
828 and p_validation_end_date);
829 --
830 end remove_quickpay_exclusions;
831 --
832 ------------------------------------------------------------------------------
833 -- NAME --
834 -- hrentmnt.check_payroll_changes_asg --
835 -- --
836 -- DESCRIPTION --
837 -- Makes sure that payroll exists for the lifetime of the assignment that --
838 -- uses it and also makes sure that no assignment actions are orphaned by a --
839 -- change in payroll. --
840 ------------------------------------------------------------------------------
841 --
842 procedure check_payroll_changes_asg
843 (
844 p_assignment_id number,
845 p_payroll_id number,
846 p_dt_mode varchar2,
847 p_validation_start_date date,
848 p_validation_end_date date
849 ) is
850 --
851 -- local variables
852 --
853 v_check_failed varchar2(1) := 'N';
854 --
855 --
856 cursor csr_del_or_zap is
857 select pa.effective_date
858 from pay_assignment_actions aa,
859 pay_payroll_actions pa
860 where aa.assignment_id = p_assignment_id
861 and pa.action_type not in ('X', 'BEE')
862 and pa.payroll_action_id = aa.payroll_action_id
863 and ((pa.effective_date >= p_validation_start_date)
864 or
865 (pa.date_earned >= p_validation_start_date));
866 --
867 cursor csr_not_insert is
868 select pa.effective_date
869 from pay_assignment_actions aa,
870 pay_payroll_actions pa
871 where aa.assignment_id = p_assignment_id
872 and pa.payroll_action_id = aa.payroll_action_id
873 and pa.action_type not in ('X', 'BEE')
874 and ((pa.effective_date between p_validation_start_date
875 and p_validation_end_date)
876 or
877 (pa.date_earned between p_validation_start_date
878 and p_validation_end_date))
879 and not (exists
880 (select null
881 from per_all_assignments_f asg
882 where asg.assignment_id = p_assignment_id
883 and pa.effective_date
884 between asg.effective_start_date
885 and asg.effective_end_date
886 and asg.payroll_id + 0 = p_payroll_id)
887 and exists
888 (select null
889 from per_all_assignments_f asg
890 where asg.assignment_id = p_assignment_id
891 and nvl(pa.date_earned,pa.effective_date)
892 between asg.effective_start_date
893 and asg.effective_end_date
894 and asg.payroll_id + 0 = p_payroll_id));
895 --
896 cursor csr_valid_payroll (p_date date) is
897 select 'Y'
898 from sys.dual
899 where not exists
900 (select null
901 from per_all_assignments_f asg
902 where asg.assignment_id = p_assignment_id
903 and p_date
904 between asg.effective_start_date
905 and asg.effective_end_date
906 and asg.payroll_id + 0 = p_payroll_id);
907 --
908 l_dummy varchar2(1);
909 l_date date;
910 --
911 procedure check_parameters is
912 --
913 begin
914 --
915 hr_utility.trace('In check_payroll_changes_asg');
916 hr_utility.trace ('');
917 hr_utility.trace (' p_assignment_id = '
918 ||to_char (p_assignment_id));
919 hr_utility.trace (' p_payroll_id = '
920 ||to_char (p_payroll_id));
921 hr_utility.trace (' p_dt_mode = '
922 ||p_dt_mode);
923 hr_utility.trace (' p_validation_start_date = '
924 ||to_char (p_validation_start_date));
925 hr_utility.trace (' p_validation_end_date = '
926 ||to_char (p_validation_end_date));
927 hr_utility.trace ('');
928 --
929 end check_parameters;
930 --
931 --
932 begin
933 --
934 g_debug := hr_utility.debug_enabled;
935 if g_debug then
936 check_parameters;
937 end if;
938 --
939 -- check to see that when a payroll is used on the assignment it exists for
940 -- the duration of the assignments use of the payroll.
941 --
942 if p_payroll_id is not null and not
943 (p_dt_mode = 'DELETE' or
944 p_dt_mode = 'ZAP') then
945 --
946 --
947 begin
948 select 'Y'
949 into v_check_failed
950 from sys.dual
951 where exists
952 (select null
953 from pay_payrolls_f pay
954 where pay.payroll_id = p_payroll_id
955 and p_validation_end_date >
956 (select max(pay2.effective_end_date)
957 from pay_payrolls_f pay2
958 where pay2.payroll_id = pay.payroll_id));
959 --
960 if v_check_failed = 'Y' then
961 hr_utility.set_message(801,'HR_6590_ASS_PYRLL_NOT_EXIST');
962 hr_utility.raise_error;
963 end if;
964 --
965 exception
966 when no_data_found then null;
967 end;
968 --
969 end if;
970 --
971 -- Checks to see if there are any assignment actions for the assignment
972 -- which are for a payroll that is not on the assignment record at the
973 -- point in time the assignment action was processed ie. there are no
974 -- orphaned assignment actions nb. the check is different for 'delete' and
975 -- 'zap' modes because there is not an new payroll to take into account.
976 --
977 if p_dt_mode = 'DELETE' or
978 p_dt_mode = 'ZAP' then
979 --
980 --
981 -- begin
982 -- select 'Y'
983 -- into v_check_failed
984 -- from sys.dual
985 -- where exists
986 -- (select null
987 -- from pay_assignment_actions aa,
988 -- pay_payroll_actions pa
989 -- where aa.assignment_id = p_assignment_id
990 -- and pa.action_type not in ('X', 'BEE')
991 -- and pa.payroll_action_id = aa.payroll_action_id
992 -- and ((pa.effective_date >= p_validation_start_date)
993 -- or
994 -- (pa.date_earned >= p_validation_start_date)));
995 -- exception
996 -- when no_data_found then null;
997 -- end;
998 --
999 --
1000 for l_rec in csr_del_or_zap loop
1001 --
1002 if l_rec.effective_date >= p_validation_start_date then
1003 l_date := l_rec.effective_date;
1004 v_check_failed := 'Y';
1005 exit;
1006 else
1007 hr_utility.set_message(801,'PAY_449682_RUN_EXISTS_FOR_DE');
1008 hr_utility.set_warning;
1009 end if;
1010 --
1011 end loop;
1012 --
1013 --
1014 -- Check if there assignment actions that would be invalidated by the
1015 -- change to the assignment.
1016 --
1017 elsif not p_dt_mode = 'INSERT' then
1018 --
1019 --
1020 -- begin
1021 -- select 'Y'
1022 -- into v_check_failed
1023 -- from sys.dual
1024 -- where exists
1025 -- (select null
1026 -- from pay_assignment_actions aa,
1027 -- pay_payroll_actions pa
1028 -- where aa.assignment_id = p_assignment_id
1029 -- and pa.payroll_action_id = aa.payroll_action_id
1030 -- and pa.action_type not in ('X', 'BEE')
1031 -- and ((pa.effective_date between p_validation_start_date
1032 -- and p_validation_end_date)
1033 -- or
1034 -- (pa.date_earned between p_validation_start_date
1035 -- and p_validation_end_date))
1036 -- and not (exists
1037 -- (select null
1038 -- from per_all_assignments_f asg
1039 -- where asg.assignment_id = p_assignment_id
1040 -- and pa.effective_date
1041 -- between asg.effective_start_date
1042 -- and asg.effective_end_date
1043 -- and asg.payroll_id + 0 = p_payroll_id)
1044 -- and exists
1045 -- (select null
1046 -- from per_all_assignments_f asg
1047 -- where asg.assignment_id = p_assignment_id
1048 -- and nvl(pa.date_earned,pa.effective_date)
1049 -- between asg.effective_start_date
1050 -- and asg.effective_end_date
1051 -- and asg.payroll_id + 0 = p_payroll_id)));
1052 -- exception
1053 -- when no_data_found then null;
1054 -- end;
1055 --
1056 for l_rec in csr_not_insert loop
1057 --
1058 if (l_rec.effective_date >= p_validation_start_date and
1059 l_rec.effective_date <= p_validation_end_date) then
1060 --
1061 open csr_valid_payroll(l_rec.effective_date);
1062 fetch csr_valid_payroll into l_dummy;
1063 if csr_valid_payroll%found then
1064 l_date := l_rec.effective_date;
1065 v_check_failed := 'Y';
1066 exit;
1067 else
1068 --
1069 hr_utility.set_message(801,'PAY_449682_RUN_EXISTS_FOR_DE');
1070 hr_utility.set_warning;
1071 --
1072 end if;
1073 close csr_valid_payroll;
1074 --
1075 else
1076 --
1077 hr_utility.set_message(801,'PAY_449682_RUN_EXISTS_FOR_DE');
1078 hr_utility.set_warning;
1079 --
1080 end if;
1081 --
1082 end loop;
1083 --
1084 end if;
1085 --
1086 if v_check_failed = 'Y' then
1087 hr_utility.set_message(801,'HR_449757_ASS_ACTIONS_EXIST');
1088 hr_utility.set_message_token('1', fnd_date.date_to_displaydate(l_date));
1089 hr_utility.raise_error;
1090 end if;
1091 --
1092 end check_payroll_changes_asg;
1093 --
1094 ------------------------------------------------------------------------------
1095 -- NAME --
1096 -- hrentmnt.cache_element_entry --
1097 -- --
1098 -- DESCRIPTION --
1099 -- When splitting element entries ie. when there is a change in assignment --
1100 -- criteria, the entry values are cached and used when creating the second --
1101 -- part of the entry. --
1102 ------------------------------------------------------------------------------
1103 --
1104 procedure cache_element_entry
1105 (
1106 p_element_entry_id number,
1107 p_date date,
1108 p_ele_entry_rec out nocopy hrentmnt.t_ele_entry_rec,
1109 p_num_entry_values out nocopy number,
1110 p_input_value_id_tbl out nocopy hr_entry.number_table,
1111 p_entry_value_tbl out nocopy hr_entry.varchar2_table
1112 ) is
1113 --
1114 -- Finds all entry values for an entry.
1115 --
1116 cursor csr_entry_values
1117 (
1118 p_element_entry_id number,
1119 p_date date
1120 ) is
1121 select eev.input_value_id,
1122 eev.screen_entry_value,
1123 iv.uom,
1124 -- change 115.30
1125 iv.LOOKUP_TYPE,
1126 -- Bugfix 2827092
1127 iv.value_set_id,
1128 et.input_currency_code
1129 from pay_element_entry_values_f eev,
1130 pay_input_values_f iv,
1131 pay_element_types_f et
1132 where eev.element_entry_id = p_element_entry_id
1133 and iv.input_value_id = eev.input_value_id
1134 and et.element_type_id = iv.element_type_id
1135 and eev.effective_end_date = p_date
1136 and p_date between iv.effective_start_date
1137 and iv.effective_end_date
1138 and p_date between et.effective_start_date
1139 and et.effective_end_date;
1140 --
1141 -- Local Variables
1142 --
1143 v_ele_entry_rec hrentmnt.t_ele_entry_rec;
1144 v_num_values number := 0;
1145 v_input_value_id_tbl hr_entry.number_table;
1146 v_entry_value_tbl hr_entry.varchar2_table;
1147 v_db_format varchar2(60);
1148 v_screen_format varchar2(80);
1149 --
1150 procedure check_parameters is
1151 --
1152 begin
1153 --
1154 hr_utility.trace('In hrentmnt.cache_element_entry');
1155 --
1156 hr_utility.trace ('');
1157 hr_utility.trace (' p_element_entry_id = '
1158 ||to_char (p_element_entry_id));
1159 hr_utility.trace (' p_date = '
1160 ||to_char (p_date));
1161 hr_utility.trace ('');
1162 --
1163 end check_parameters;
1164 --
1165 begin
1166 --
1167 if g_debug then
1168 check_parameters;
1169 end if;
1170 --
1171 -- Fetch entry information into a record.
1172 --
1173 begin
1174 select ee.cost_allocation_keyflex_id,
1175 ee.creator_type,
1176 ee.entry_type,
1177 ee.comment_id,
1178 ee.creator_id,
1179 ee.reason,
1180 ee.target_entry_id,
1181 ee.subpriority,
1182 ee.personal_payment_method_id,
1183 ee.date_earned,
1184 ee.balance_adj_cost_flag,
1185 ee.source_asg_action_id,
1186 ee.source_link_id,
1187 ee.source_trigger_entry,
1188 ee.source_period,
1189 ee.source_run_type,
1190 ee.source_start_date,
1191 ee.source_end_date,
1192 ee.attribute_category,
1193 ee.attribute1,
1194 ee.attribute2,
1195 ee.attribute3,
1196 ee.attribute4,
1197 ee.attribute5,
1198 ee.attribute6,
1199 ee.attribute7,
1200 ee.attribute8,
1201 ee.attribute9,
1202 ee.attribute10,
1203 ee.attribute11,
1204 ee.attribute12,
1205 ee.attribute13,
1206 ee.attribute14,
1207 ee.attribute15,
1208 ee.attribute16,
1209 ee.attribute17,
1210 ee.attribute18,
1211 ee.attribute19,
1212 ee.attribute20,
1213 ee.entry_information_category,
1214 ee.entry_information1,
1215 ee.entry_information2,
1216 ee.entry_information3,
1217 ee.entry_information4,
1218 ee.entry_information5,
1219 ee.entry_information6,
1220 ee.entry_information7,
1221 ee.entry_information8,
1222 ee.entry_information9,
1223 ee.entry_information10,
1224 ee.entry_information11,
1225 ee.entry_information12,
1226 ee.entry_information13,
1227 ee.entry_information14,
1228 ee.entry_information15,
1229 ee.entry_information16,
1230 ee.entry_information17,
1231 ee.entry_information18,
1232 ee.entry_information19,
1233 ee.entry_information20,
1234 ee.entry_information21,
1235 ee.entry_information22,
1236 ee.entry_information23,
1237 ee.entry_information24,
1238 ee.entry_information25,
1239 ee.entry_information26,
1240 ee.entry_information27,
1241 ee.entry_information28,
1242 ee.entry_information29,
1243 ee.entry_information30
1244 into v_ele_entry_rec
1245 from pay_element_entries_f ee
1246 where ee.element_entry_id = p_element_entry_id
1247 and p_date between ee.effective_start_date
1248 and ee.effective_end_date;
1249 exception
1250 when no_data_found then
1251 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1252 hr_utility.set_message_token('PROCEDURE',
1253 'hrentmnt.cache_element_entry');
1254 hr_utility.set_message_token('STEP','1');
1255 hr_utility.raise_error;
1256 end;
1257 --
1258 -- Retrieve all entry values for the element entry and convert into screen
1259 -- format. Store these in a table to be used later by the element entry
1260 -- api call hr_entry_api.insert_element_entry.
1261 --
1262 for v_entry_value in csr_entry_values(p_element_entry_id,
1263 p_date) loop
1264 --
1265 v_screen_format := null; -- must be null at each loop.
1266 v_num_values := v_num_values + 1;
1267 v_input_value_id_tbl(v_num_values) := v_entry_value.input_value_id;
1268 --
1269 v_db_format := v_entry_value.screen_entry_value;
1270
1271 -- start of change 115.30
1272 if g_debug then
1273 hr_utility.trace('*****before*****');
1274 hr_utility.trace('*****v_entry_value.lookup_type>' ||
1275 v_entry_value.lookup_type || '<');
1276 hr_utility.trace('*****v_entry_value.value_set_id>' ||
1277 v_entry_value.value_set_id || '<');
1278 hr_utility.trace('*****v_entry_value.screen_entry_value>' ||
1279 v_entry_value.screen_entry_value || '<');
1280 end if;
1281 --
1282 -- if entry has lookup and the entry_value is not null,
1283 -- do special processing
1284 --
1285 if v_entry_value.lookup_type is not null
1286 and v_entry_value.screen_entry_value is not null
1287 then
1288 SELECT meaning
1289 INTO v_screen_format
1290 FROM HR_LOOKUPS
1291 WHERE lookup_type = v_entry_value.lookup_type
1292 and lookup_code = v_entry_value.screen_entry_value;
1293 -- Bugfix 2827092
1294 elsif v_entry_value.value_set_id is not null
1295 and v_entry_value.screen_entry_value is not null
1296 then
1297 --
1298 -- Entry value is validated by a value set
1299 -- We need to derive the screen format
1300 --
1301 v_screen_format := pay_input_values_pkg.decode_vset_value(
1302 p_value_set_id => v_entry_value.value_set_id,
1303 p_value_set_value => v_entry_value.screen_entry_value);
1304 --
1305 else
1306 --
1307 -- Convert entry value from DB format to screen format.
1308 --
1309 hr_chkfmt.changeformat
1310 (v_db_format,
1311 v_screen_format,
1312 v_entry_value.uom,
1313 v_entry_value.input_currency_code);
1314 end if;
1315
1316 if g_debug then
1317 hr_utility.trace('*****v_screen_format>' || v_screen_format || '<');
1318 hr_utility.trace('*****v_db_format>' || v_db_format || '<');
1319 hr_utility.trace('*****after*****');
1320 end if;
1321 -- end of change 115.30
1322
1323 --
1324 -- Store screen format in table.
1325 --
1326 v_entry_value_tbl(v_num_values) := v_screen_format;
1327 --
1328 end loop;
1329 --
1330 -- Assign out variables ie. entry record and tables containing entry values.
1331 --
1332 p_ele_entry_rec := v_ele_entry_rec;
1333 p_num_entry_values := v_num_values;
1334 p_input_value_id_tbl := v_input_value_id_tbl;
1335 p_entry_value_tbl := v_entry_value_tbl;
1336 --
1337 if g_debug then
1338 hr_utility.trace('Out hrentmnt.cache_element_entry');
1339 end if;
1340 --
1341 end cache_element_entry;
1342 --
1343 ------------------------------------------------------------------------------
1344 -- NAME --
1345 -- hrentmnt.check_entry_overridden --
1346 -- --
1347 -- DESCRIPTION --
1348 -- Checks to see if a change in the existence of a recurring element entry --
1349 -- will leave an override for it that will exist outside the recurring --
1350 -- entry. --
1351 ------------------------------------------------------------------------------
1352 --
1353 procedure check_entry_overridden
1354 (
1355 p_assignment_id number,
1356 p_element_entry_id number,
1357 p_validation_start_date date,
1358 p_validation_end_date date
1359 ) is
1360 --
1361 -- Local Variables
1362 --
1363 v_entry_overridden varchar2(1) := 'N';
1364 --
1365 procedure check_parameters is
1366 --
1367 begin
1368 --
1369 hr_utility.trace('In hrentmnt.check_entry_overridden');
1370 hr_utility.trace ('');
1371 hr_utility.trace(' p_assignment_id = '
1372 ||to_char (p_assignment_id));
1373 hr_utility.trace(' p_element_entry_id = '
1374 ||to_char (p_element_entry_id));
1375 hr_utility.trace(' p_validation_start_date = '
1376 ||to_char (p_validation_start_date));
1377 hr_utility.trace(' p_validation_end_date = '
1378 ||to_char (p_validation_end_date));
1379 hr_utility.trace ('');
1380 --
1381 end check_parameters;
1382 --
1383 begin
1384 --
1385 if g_debug then
1386 check_parameters;
1387 end if;
1388 --
1389 -- Make sure that the recurring entry about to be altered ie. deleted or
1390 -- shortened does not have an adjustment over the time of change. An
1391 -- adjustment's target entry should exist for the duration of the
1392 -- adjustment.
1393 --
1394 begin
1395 select 'Y'
1396 into v_entry_overridden
1397 from sys.dual
1398 where exists
1399 (select null
1400 from pay_element_entries_f ee
1401 where ee.assignment_id = p_assignment_id
1402 and ee.entry_type in ('A','R')
1403 and ee.target_entry_id = p_element_entry_id
1404 and ee.effective_start_date <= p_validation_end_date
1405 and ee.effective_end_date >= p_validation_start_date);
1406 exception
1407 when no_data_found then null;
1408 end;
1409 --
1410 if v_entry_overridden = 'Y' then
1411 hr_utility.set_message(801, 'HR_6304_ELE_ENTRY_DT_DEL_ADJ');
1412 hr_utility.raise_error;
1413 end if;
1414 --
1415 end check_entry_overridden;
1416 --
1417 ------------------------------------------------------------------------------
1418 -- NAME --
1419 -- hrentmnt.validate_adjust_entry --
1420 -- --
1421 -- DESCRIPTION --
1422 -- Validates and adjusts recurring element entries. It accepts 2 modes ie. --
1423 -- UPDATE and DELETE NB. only UPDATE will result in changes to an --
1424 -- element entry. The other 2 modes only carry out validation / 3rd party --
1425 -- logic as it is assumed the DML is done elsewhere. This should be called --
1426 -- whenever an element entry is created , removed or changed. --
1427 ------------------------------------------------------------------------------
1428 --
1429 procedure validate_adjust_entry
1430 (
1431 p_mode varchar2,
1432 p_assignment_id number,
1433 p_element_entry_id number,
1434 p_start_or_end_date varchar2,
1435 p_old_date date,
1436 p_new_date date,
1437 p_effective_start_date date,
1438 p_effective_end_date date,
1439 p_entries_changed in out nocopy varchar2
1440 ) is
1441 --
1442 -- Cursor to fetch element information Nb. there are two distinct parts to
1443 -- the statement. The first part returns if the element entry is used for
1444 -- salary admin while the second returns if the element entry is not used
1445 -- for salary admin.
1446 --
1447 cursor csr_ele_info is
1448 select EL.element_type_id element_type_id,
1449 'Y' salary_element
1450 from pay_element_entries_f EE,
1451 pay_element_links_f EL
1452 where EE.element_entry_id = p_element_entry_id
1453 and EL.element_link_id = EE.element_link_id
1454 and nvl(p_old_date,p_effective_start_date) between
1455 EE.effective_start_date and EE.effective_end_date
1456 and nvl(p_old_date,p_effective_start_date) between
1457 EL.effective_start_date and EL.effective_end_date
1458 and exists (select null
1459 from pay_input_values_f IV,
1460 per_pay_bases PB
1461 where IV.element_type_id = EL.element_type_id
1462 and PB.input_value_id = IV.input_value_id)
1463 UNION ALL
1464 select EL.element_type_id element_type_id,
1465 'N' salary_element
1466 from pay_element_entries_f EE,
1467 pay_element_links_f EL
1468 where EE.element_entry_id = p_element_entry_id
1469 and EL.element_link_id = EE.element_link_id
1470 and nvl(p_old_date,p_effective_start_date) between
1471 EE.effective_start_date and EE.effective_end_date
1472 and nvl(p_old_date,p_effective_start_date) between
1473 EL.effective_start_date and EL.effective_end_date
1474 and not exists (select null
1475 from pay_input_values_f IV,
1476 per_pay_bases PB
1477 where IV.element_type_id = EL.element_type_id
1478 and PB.input_value_id = IV.input_value_id);
1479 --
1480 -- Local Variables
1481 --
1482 v_element_type_id number;
1483 v_change_start_date date;
1484 v_change_end_date date;
1485 v_ele_info_rec csr_ele_info%rowtype;
1486 --
1487 procedure check_parameters is
1488 --
1489 begin
1490 --
1491 hr_utility.trace('In hrentmnt.validate_adjust_entry');
1492 hr_utility.trace ('');
1493 hr_utility.trace (' p_mode = '
1494 ||p_mode);
1495 hr_utility.trace (' p_assignment_id = '
1496 ||to_char (p_assignment_id));
1497 hr_utility.trace (' p_element_entry_id = '
1498 ||to_char (p_element_entry_id));
1499 hr_utility.trace (' p_start_or_end_date = '
1500 ||p_start_or_end_date);
1501 hr_utility.trace (' p_old_date = '
1502 ||to_char (p_old_date));
1503 hr_utility.trace (' p_new_date = '
1504 ||to_char (p_new_date));
1505 hr_utility.trace (' p_effective_start_date = '
1506 ||to_char (p_effective_start_date));
1507 hr_utility.trace (' p_effective_end_date = '
1508 ||to_char (p_effective_end_date));
1509 hr_utility.trace (' p_entries_changed = '
1510 ||p_entries_changed);
1511 hr_utility.trace ('');
1512 --
1513 end check_parameters;
1514 --
1515 begin
1516 --
1517 if g_debug then
1518 check_parameters;
1519 end if;
1520 --
1521 -- Get information about the element entry for future use.
1522 --
1523 open csr_ele_info;
1524 fetch csr_ele_info into v_ele_info_rec;
1525 if csr_ele_info%notfound then
1526 close csr_ele_info;
1527 --
1528 -- The cursor unexpectedly returned no rows. Present an error message
1529 -- which includes all the procedure's parameters to assist diagnosis of
1530 -- why this error occurred.
1531 --
1532 hr_utility.set_message(801, 'HR_51058_PAY_VALIDATE_ADJUST');
1533 hr_utility.set_message_token('P_MODE',p_mode);
1534 hr_utility.set_message_token('P_ASSIGNMENT_ID',p_assignment_id);
1535 hr_utility.set_message_token('P_ELEMENT_ENTRY_ID',p_element_entry_id);
1536 hr_utility.set_message_token('P_START_OR_END_DATE',p_start_or_end_date);
1537 hr_utility.set_message_token('P_OLD_DATE',p_old_date);
1538 hr_utility.set_message_token('P_NEW_DATE',p_new_date);
1539 hr_utility.set_message_token('P_EFFECTIVE_START_DATE',p_effective_start_date);
1540 hr_utility.set_message_token('P_EFFECTIVE_END_DATE',p_effective_end_date);
1541 hr_utility.set_message_token('P_ENTRIES_CHANGED',p_entries_changed);
1542 hr_utility.raise_error;
1543 end if;
1544 close csr_ele_info;
1545 --
1546 -- Set the dates over which the change is taking place.
1547 --
1548 if p_mode = 'DELETE' then
1549 --
1550 v_change_start_date := p_effective_start_date;
1551 v_change_end_date := p_effective_end_date;
1552 --
1553 elsif p_mode = 'UPDATE' then
1554 --
1555 v_change_start_date := least(p_old_date,p_new_date);
1556 v_change_end_date := greatest(p_old_date,p_new_date);
1557 --
1558 end if;
1559 --
1560 hr_entry.trigger_workload_shifting
1561 ('ELEMENT_ENTRY',
1562 p_assignment_id,
1563 v_change_start_date,
1564 v_change_end_date);
1565 --
1566 hr_entry.chk_element_entry_open
1567 (v_ele_info_rec.element_type_id,
1568 v_change_start_date,
1569 v_change_start_date,
1570 v_change_end_date,
1571 p_assignment_id);
1572 --
1573 -- When removing or shortening element entries need to
1574 --
1575 -- make sure that the element entry has not been overidden.
1576 -- remove orphaned quickpay inclusions.
1577 -- set flag to indicate that element entries have been affected.
1578 --
1579 if p_mode = 'DELETE' or
1580 (p_mode = 'UPDATE' and
1581 ((p_start_or_end_date = 'START' and
1582 p_old_date < p_new_date) or
1583 (p_start_or_end_date = 'END' and
1584 p_old_date > p_new_date))) then
1585 --
1586 hrentmnt.check_entry_overridden
1587 (p_assignment_id,
1588 p_element_entry_id,
1589 v_change_start_date,
1590 v_change_end_date);
1591 --
1592 -- Enhancement 3368211
1593 --
1594 -- Delete both QuickPay Inclusions AND Exclusions.
1595 --
1596 -- There is a chance the element entry id exists in both tables if
1597 -- any QuickPay assignment actions were created before the QuickPay
1598 -- Exclusions data model was in use.
1599 --
1600 hrentmnt.remove_quickpay_exclusions
1601 (p_element_entry_id,
1602 v_change_start_date,
1603 v_change_end_date);
1604 --
1605 hrentmnt.remove_quickpay_inclusions
1606 (p_element_entry_id,
1607 v_change_start_date,
1608 v_change_end_date);
1609 --
1610 -- Set flag to identify that element entries have been shortened
1611 -- or removed. If the entry is a salary entry then set the flag to 'S' so
1612 -- that a special warning can be given to the user. Once the flag has been
1613 -- set to 'S' then make sure that it cannot be set to 'Y' as this would
1614 -- cover the fact that a salary element entry had been affected.
1615 --
1616 if p_entries_changed = 'S' then
1617 null;
1618 else
1619 if v_ele_info_rec.salary_element = 'Y' then
1620 p_entries_changed := 'S';
1621 else
1622 p_entries_changed := 'Y';
1623 end if;
1624 end if;
1625 --
1626 end if;
1627
1628 -- Update the covered dependents and beneficiaries for the element entry
1629 -- This should be called in both DELETE and UPDATE modes.
1630 --
1631 if p_start_or_end_date = 'START' then
1632 hr_entry.delete_covered_dependants(
1633 p_validation_start_date => p_old_date,
1634 p_element_entry_id => p_element_entry_id,
1635 p_start_date => p_new_date);
1636
1637 hr_entry.delete_beneficiaries(
1638 p_validation_start_date => p_old_date,
1639 p_element_entry_id => p_element_entry_id,
1640 p_start_date => p_new_date);
1641
1642 elsif p_start_or_end_date = 'END' then
1643 hr_entry.delete_covered_dependants(
1644 p_validation_start_date => p_old_date,
1645 p_element_entry_id => p_element_entry_id,
1646 p_end_date => p_new_date);
1647
1648 hr_entry.delete_beneficiaries(
1649 p_validation_start_date => p_old_date,
1650 p_element_entry_id => p_element_entry_id,
1651 p_end_date => p_new_date);
1652
1653 end if;
1654
1655 --
1656 -- When updating an element entry do the DML to move the start or end date.
1657 --
1658 if p_mode = 'UPDATE' then
1659 --
1660 if p_start_or_end_date = 'START' then
1661 --
1662 --
1663 update pay_element_entries_f ee
1664 set ee.effective_start_date = p_new_date,
1665 ee.updating_action_id = decode(ee.updating_action_type, 'S', ee.updating_action_id,
1666 null),
1667 ee.updating_action_type = decode(ee.updating_action_type, 'S', 'S', null)
1668 where ee.element_entry_id = p_element_entry_id
1669 and ee.effective_start_date = p_old_date;
1670 --
1671 update pay_element_entry_values_f eev
1672 set eev.effective_start_date = p_new_date
1673 where eev.element_entry_id = p_element_entry_id
1674 and eev.effective_start_date = p_old_date;
1675 --
1676 elsif p_start_or_end_date = 'END' then
1677 --
1678 --
1679 update pay_element_entries_f ee
1680 set ee.effective_end_date = p_new_date,
1681 ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
1682 null),
1683 ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
1684 where ee.element_entry_id = p_element_entry_id
1685 and ee.effective_end_date = p_old_date;
1686 --
1687 update pay_element_entry_values_f eev
1688 set eev.effective_end_date = p_new_date
1689 where eev.element_entry_id = p_element_entry_id
1690 and eev.effective_end_date = p_old_date;
1691 --
1692 end if;
1693 --
1694 end if;
1695 --
1696 if g_debug then
1697 hr_utility.trace('Out hrentmnt.validate_adjust_entry');
1698 end if;
1699 --
1700 end validate_adjust_entry;
1701 --
1702 ------------------------------------------------------------------------------
1703 -- NAME --
1704 -- hrentmnt.validate_purge --
1705 -- --
1706 -- DESCRIPTION --
1707 -- Validates whether it is legal to purge a particular element entry --
1708 -- depending on the setting of the HR_ELE_ENTRY_PURGE_CONTROL profile and --
1709 -- the setting of the elements non_payments_flag. --
1710 -- Because of the circumstances of calling, the only real way to tell if --
1711 -- element has been purged is to actually see if any rows remain on the --
1712 -- database. i.e. the user doesn't specify a dt mode that we can use. --
1713 ------------------------------------------------------------------------------
1714 --
1715 procedure validate_purge
1716 (
1717 p_element_entry_id in number,
1718 p_element_link_id in varchar2
1719 ) is
1720 l_prof_value varchar2(30);
1721 l_count number;
1722 l_non_payments_flag varchar2(30);
1723 begin
1724 -- Read the prof value.
1725 fnd_profile.get('HR_ELE_ENTRY_PURGE_CONTROL', l_prof_value);
1726 --
1727 -- See if profile is set in such a way that
1728 -- we need to perform validation.
1729 if l_prof_value is not null and l_prof_value <> 'A' then
1730 -- Use the element_link_id to obtain the non_payment_flag
1731 -- on the element classification. We are not concerned
1732 -- with datetrack joins here.
1733 begin
1734 select nvl(pec.non_payments_flag, 'N')
1735 into l_non_payments_flag
1736 from pay_element_links_f pel,
1737 pay_element_types_f pet,
1738 pay_element_classifications pec
1739 where pel.element_link_id = p_element_link_id
1740 and pet.element_type_id = pel.element_type_id
1741 and pec.classification_id = pet.classification_id
1742 and not exists (
1743 select null
1744 from pay_element_entries_f pee
1745 where pee.element_entry_id = p_element_entry_id)
1746 and rownum = 1;
1747 exception
1748 -- If we didn't get a row, this means that we haven't
1749 -- purged the entry. We therefore don't care and
1750 -- can immediately return.
1751 when no_data_found then return;
1752 end;
1753 --
1754 -- Check whether we need to raise error.
1755 if l_prof_value = 'N' or
1756 (l_prof_value = 'I' and l_non_payments_flag = 'N')
1757 then
1758 -- Either no entries can be purged or attempting to
1759 -- purge a payments type of entry when we shouldn't.
1760 hr_utility.set_message (800,'HR_33000_ENTRY_CANT_PURGE');
1761 hr_utility.raise_error;
1762 end if;
1763 --
1764 end if;
1765 --
1766 end validate_purge;
1767 --
1768 procedure maintain_dependent_entities(
1769 p_element_entry_id in number,
1770 p_element_entry_ESD in date,
1771 p_element_entry_EED in date,
1772 p_new_element_entry_id in number,
1773 p_new_element_entry_ESD in date,
1774 p_new_element_entry_EED in date)
1775 is
1776 begin
1777 hr_utility.trace('> hrentmnt.maintain_dependent_entities');
1778
1779 if g_debug then
1780 hr_utility.trace('p_element_entry_id >' || p_element_entry_id || '<');
1781 hr_utility.trace('p_element_entry_ESD >' || p_element_entry_ESD || '<');
1782 hr_utility.trace('p_element_entry_EED >' || p_element_entry_EED || '<');
1783
1784 hr_utility.trace('p_new_element_entry_id >' || p_new_element_entry_id || '<');
1785 hr_utility.trace('p_new_element_entry_ESD>' || p_new_element_entry_ESD || '<');
1786 hr_utility.trace('p_new_element_entry_EED>' || p_new_element_entry_EED || '<');
1787 end if;
1788
1789 null;
1790
1791 end maintain_dependent_entities;
1792 --
1793 ------------------------------------------------------------------------------
1794 -- NAME --
1795 -- hrentmnt.open_asg_criteria_cur --
1796 -- --
1797 -- SINCE --
1798 -- Bugfix 5584631 --
1799 -- --
1800 -- DESCRIPTION --
1801 -- --
1802 -- USAGES --
1803 -- Used by recreate_cached_entry and val_nonrec_entries. --
1804 ------------------------------------------------------------------------------
1805 procedure open_asg_criteria_cur (
1806 p_assignment_id in number,
1807 p_validation_start_date in date,
1808 p_asg_criteria_cv in out nocopy t_asg_criteria_cur -- cursor variable
1809 )
1810 is
1811 begin
1812 --
1813 open p_asg_criteria_cv for
1814 select asg.organization_id,
1815 asg.people_group_id,
1816 asg.job_id,
1817 asg.position_id,
1818 asg.grade_id,
1819 asg.location_id,
1820 asg.employment_category,
1821 asg.payroll_id,
1822 asg.pay_basis_id,
1823 asg.business_group_id
1824 from per_all_assignments_f asg
1825 where asg.assignment_id = p_assignment_id
1826 and p_validation_start_date between
1827 asg.effective_start_date and asg.effective_end_date;
1828 --
1829 end open_asg_criteria_cur;
1830 --
1831 ------------------------------------------------------------------------------
1832 -- NAME --
1833 -- hrentmnt.open_eligible_links_cur --
1834 -- --
1835 -- SINCE --
1836 -- Bugfix 5584631 --
1837 -- --
1838 -- DESCRIPTION --
1839 -- Opens a ref cursor variable for a cursor of element links for given --
1840 -- eligibility criteria and element type. --
1841 -- The OUT parameter, p_eligible_links_cv, is a pointer to the cursor that --
1842 -- is successfully opened. --
1843 -- --
1844 -- USAGES --
1845 -- Used by recreate_cached_entry and val_nonrec_entries. --
1846 ------------------------------------------------------------------------------
1847 procedure open_eligible_links_cur (
1848 p_assignment_id in number,
1849 p_validation_start_date in date,
1850 p_validation_end_date in date,
1851 p_element_type_id in number,
1852 p_organization_id in number,
1853 p_people_group_id in number,
1854 p_job_id in number,
1855 p_position_id in number,
1856 p_grade_id in number,
1857 p_location_id in number,
1858 p_employment_category in varchar2,
1859 p_payroll_id in number,
1860 p_pay_basis_id in number,
1861 p_business_group_id in number,
1862 p_eligible_links_cv in out nocopy t_eligible_links_cur -- cursor variable
1863 )
1864 is
1865 begin
1866 --
1867 open p_eligible_links_cv for
1868 select el.element_link_id,
1869 min(el.effective_start_date) effective_start_date,
1870 max(el.effective_end_date) effective_end_date
1871 from pay_element_links_f el
1872 where el.element_type_id = p_element_type_id
1873 and el.standard_link_flag = 'N'
1874 and el.business_group_id = p_business_group_id
1875 --
1876 -- make sure EL piece overlaps validation period
1877 --
1878 -- Bugfix 4627931
1879 -- Ensure the EL exists as at the validation start date.
1880 -- This is because hr_entry_api expects the EL to exist as at this date
1881 -- and raises error APP-PAY-07027 if it does not.
1882 -- Also, we should not re-create entries after there has been any gap in
1883 -- eligibility after the validation start date.
1884 and p_validation_start_date between el.effective_start_date and el.effective_end_date
1885 --
1886 -- match crieria on EL to that on asg
1887 --
1888 and (
1889 (el.payroll_id is not null and
1890 el.payroll_id = p_payroll_id)
1891 or
1892 (el.link_to_all_payrolls_flag = 'Y' and
1893 p_payroll_id is not null)
1894 or
1895 (el.link_to_all_payrolls_flag = 'N' and
1896 el.payroll_id is null)
1897 )
1898 and (el.job_id is null or
1899 el.job_id = p_job_id)
1900 and (el.grade_id is null or
1901 el.grade_id = p_grade_id)
1902 and (el.position_id is null or
1903 el.position_id = p_position_id)
1904 and (el.organization_id is null or
1905 el.organization_id = p_organization_id)
1906 and (el.location_id is null or
1907 el.location_id = p_location_id)
1908 and (el.employment_category is null or
1909 el.employment_category = p_employment_category)
1910 and (
1911 el.pay_basis_id = p_pay_basis_id
1912 or
1913 el.pay_basis_id is null and
1914 not exists
1915 (select pb.pay_basis_id
1916 from per_pay_bases pb,
1917 pay_input_values_f iv
1918 where iv.element_type_id = el.element_type_id
1919 and p_validation_start_date between
1920 iv.effective_start_date and iv.effective_end_date
1921 and pb.input_value_id = iv.input_value_id
1922 and pb.business_group_id = p_business_group_id
1923 )
1924 or
1925 el.pay_basis_id is null and
1926 exists
1927 (select pb.pay_basis_id
1928 from per_pay_bases pb,
1929 pay_input_values_f iv
1930 where iv.element_type_id = el.element_type_id
1931 and p_validation_start_date between
1932 iv.effective_start_date and iv.effective_end_date
1933 and pb.input_value_id = iv.input_value_id
1934 and pb.pay_basis_id = p_pay_basis_id
1935 )
1936 or
1937 el.pay_basis_id is null and
1938 p_pay_basis_id is null and
1939 exists
1940 (select pb.pay_basis_id
1941 from per_pay_bases pb,
1942 pay_input_values_f iv
1943 where iv.element_type_id = el.element_type_id
1944 and p_validation_start_date between
1945 iv.effective_start_date and iv.effective_end_date
1946 and pb.input_value_id = iv.input_value_id
1947 and pb.business_group_id = p_business_group_id
1948 )
1949 )
1950 and (el.people_group_id is null
1951 or exists
1952 (select null
1953 from pay_assignment_link_usages_f alu
1954 where alu.assignment_id = p_assignment_id
1955 and alu.element_link_id = el.element_link_id
1956 and alu.effective_start_date <= p_validation_end_date
1957 and alu.effective_end_date >= p_validation_start_date
1958 )
1959 )
1960 group by el.element_link_id;
1961 --
1962 end open_eligible_links_cur;
1963 --
1964 ------------------------------------------------------------------------------
1965 -- NAME --
1966 -- hrentmnt.recreate_cached_entry --
1967 -- --
1968 -- SINCE --
1969 -- Bugfix 5247607 --
1970 -- --
1971 -- DESCRIPTION --
1972 -- Attempts to recreate a deleted/end-dated recurring entry from cache, via --
1973 -- a different link if a suitable one exists. --
1974 -- The OUT parameter, p_entry_recreated, will be set to TRUE if the cached --
1975 -- entry is successfully recreated. --
1976 -- Otherwise, it will be FALSE. --
1977 ------------------------------------------------------------------------------
1978 procedure recreate_cached_entry (
1979 p_assignment_id in number,
1980 p_element_type_id in number,
1981 p_element_link_id in number,
1982 p_element_entry_id in number,
1983 p_validation_start_date in date,
1984 p_validation_end_date in date,
1985 p_ee_effective_start_date in date,
1986 p_ee_effective_end_date in date,
1987 p_ee_creator_type in varchar2,
1988 p_rec_ee in hrentmnt.t_ele_entry_rec,
1989 p_num_eevs in number,
1990 p_tbl_ivids in hr_entry.number_table,
1991 p_tbl_eevs in hr_entry.varchar2_table,
1992 p_entry_recreated out nocopy boolean
1993 )
1994 is
1995 --
1996 l_proc varchar2(80) := 'hrentmnt.recreate_cached_entry';
1997 l_asg_criteria_cv t_asg_criteria_cur;
1998 l_rec_asg_criteria t_asg_criteria_rec;
1999 l_eligible_links_cv t_eligible_links_cur;
2000 rec_eligible_links t_eligible_links_rec;
2001 l_entry_recreated boolean := false;
2002 l_prof_value varchar2(30);
2003 l_eeid_out number;
2004 l_calc_ee_esd date;
2005 l_calc_ee_eed date;
2006 l_ee_eed_out date;
2007 --
2008 begin
2009 --
2010 if g_debug then
2011 hr_utility.set_location(l_proc,10);
2012 hr_utility.trace('********** BEFORE OAB **********');
2013 end if;
2014 --
2015 -- Read the prof value
2016 --
2017 fnd_profile.get('PAY_ORIG_EL_BEHAVE', l_prof_value);
2018 if g_debug then
2019 hr_utility.trace('l_prof_value>' || l_prof_value || '<');
2020 end if;
2021 --
2022 -- Check if any other entries can be created up to the REE's EED,
2023 -- create REE from VSD to REE's EED
2024 -- nb. do not look for any new EEs if the EE being updated is a 'SP' EE
2025 --
2026 if l_prof_value is null
2027 -- change 115.28
2028 and p_ee_creator_type <> 'SP' then
2029 --
2030 if g_debug then
2031 hr_utility.set_location(l_proc,20);
2032 hr_utility.trace('EE being updated>' || p_element_entry_id || '<');
2033 hr_utility.trace('VSD>' || p_validation_start_date || '<');
2034 hr_utility.trace('VED>' || p_validation_end_date || '<');
2035 hr_utility.trace('EE ESD>' || p_ee_effective_start_date || '<');
2036 hr_utility.trace('EE EED>' || p_ee_effective_end_date || '<');
2037 end if;
2038 --
2039 -- Fetch the assignment criteria
2040 open_asg_criteria_cur(p_assignment_id, p_validation_start_date, l_asg_criteria_cv);
2041 fetch l_asg_criteria_cv into l_rec_asg_criteria;
2042 close l_asg_criteria_cv;
2043 --
2044 open_eligible_links_cur (
2045 p_assignment_id,
2046 -- Bugfix 5629530
2047 greatest(p_ee_effective_start_date, p_validation_start_date),
2048 least(p_ee_effective_end_date, p_validation_end_date),
2049 p_element_type_id,
2050 l_rec_asg_criteria.organization_id,
2051 l_rec_asg_criteria.people_group_id,
2052 l_rec_asg_criteria.job_id,
2053 l_rec_asg_criteria.position_id,
2054 l_rec_asg_criteria.grade_id,
2055 l_rec_asg_criteria.location_id,
2056 l_rec_asg_criteria.employment_category,
2057 l_rec_asg_criteria.payroll_id,
2058 l_rec_asg_criteria.pay_basis_id,
2059 l_rec_asg_criteria.business_group_id,
2060 l_eligible_links_cv
2061 );
2062 --
2063 loop
2064 --
2065 if g_debug then
2066 hr_utility.set_location(l_proc,25);
2067 end if;
2068 --
2069 fetch l_eligible_links_cv into rec_eligible_links;
2070 exit when l_eligible_links_cv%notfound;
2071 --
2072 if g_debug then
2073 hr_utility.set_location(l_proc,30);
2074 hr_utility.trace('***** element_link_id>' ||
2075 rec_eligible_links.element_link_id || '<');
2076 hr_utility.trace('***** ESD of EL>' ||
2077 rec_eligible_links.effective_start_date || '<');
2078 hr_utility.trace('***** EED of EL>' ||
2079 rec_eligible_links.effective_end_date || '<');
2080 end if;
2081 --
2082 l_calc_ee_esd := greatest(p_ee_effective_start_date,
2083 p_validation_start_date);
2084 l_calc_ee_eed := least(p_ee_effective_end_date,
2085 rec_eligible_links.effective_end_date);
2086 --
2087 hr_entry_api.insert_element_entry(
2088 p_effective_start_date => l_calc_ee_esd,
2089 p_effective_end_date => l_ee_eed_out,
2090 p_element_entry_id => l_eeid_out,
2091 p_assignment_id => p_assignment_id,
2092 p_element_link_id => rec_eligible_links.element_link_id,
2093 p_creator_type => p_rec_ee.creator_type,
2094 p_entry_type => p_rec_ee.entry_type,
2095 p_cost_allocation_keyflex_id
2096 => p_rec_ee.cost_allocation_keyflex_id,
2097 p_comment_id => p_rec_ee.comment_id,
2098 p_creator_id => p_rec_ee.creator_id,
2099 p_reason => p_rec_ee.reason,
2100 p_target_entry_id => p_rec_ee.target_entry_id,
2101 p_subpriority => p_rec_ee.subpriority,
2102 p_personal_payment_method_id
2103 => p_rec_ee.personal_payment_method_id,
2104 p_date_earned => p_rec_ee.date_earned,
2105 p_attribute_category => p_rec_ee.attribute_category,
2106 p_attribute1 => p_rec_ee.attribute1,
2107 p_attribute2 => p_rec_ee.attribute2,
2108 p_attribute3 => p_rec_ee.attribute3,
2109 p_attribute4 => p_rec_ee.attribute4,
2110 p_attribute5 => p_rec_ee.attribute5,
2111 p_attribute6 => p_rec_ee.attribute6,
2112 p_attribute7 => p_rec_ee.attribute7,
2113 p_attribute8 => p_rec_ee.attribute8,
2114 p_attribute9 => p_rec_ee.attribute9,
2115 p_attribute10 => p_rec_ee.attribute10,
2116 p_attribute11 => p_rec_ee.attribute11,
2117 p_attribute12 => p_rec_ee.attribute12,
2118 p_attribute13 => p_rec_ee.attribute13,
2119 p_attribute14 => p_rec_ee.attribute14,
2120 p_attribute15 => p_rec_ee.attribute15,
2121 p_attribute16 => p_rec_ee.attribute16,
2122 p_attribute17 => p_rec_ee.attribute17,
2123 p_attribute18 => p_rec_ee.attribute18,
2124 p_attribute19 => p_rec_ee.attribute19,
2125 p_attribute20 => p_rec_ee.attribute20,
2126 p_entry_information_category
2127 => p_rec_ee.entry_information_category,
2128 p_entry_information1 => p_rec_ee.entry_information1,
2129 p_entry_information2 => p_rec_ee.entry_information2,
2130 p_entry_information3 => p_rec_ee.entry_information3,
2131 p_entry_information4 => p_rec_ee.entry_information4,
2132 p_entry_information5 => p_rec_ee.entry_information5,
2133 p_entry_information6 => p_rec_ee.entry_information6,
2134 p_entry_information7 => p_rec_ee.entry_information7,
2135 p_entry_information8 => p_rec_ee.entry_information8,
2136 p_entry_information9 => p_rec_ee.entry_information9,
2137 p_entry_information10 => p_rec_ee.entry_information10,
2138 p_entry_information11 => p_rec_ee.entry_information11,
2139 p_entry_information12 => p_rec_ee.entry_information12,
2140 p_entry_information13 => p_rec_ee.entry_information13,
2141 p_entry_information14 => p_rec_ee.entry_information14,
2142 p_entry_information15 => p_rec_ee.entry_information15,
2143 p_entry_information16 => p_rec_ee.entry_information16,
2144 p_entry_information17 => p_rec_ee.entry_information17,
2145 p_entry_information18 => p_rec_ee.entry_information18,
2146 p_entry_information19 => p_rec_ee.entry_information19,
2147 p_entry_information20 => p_rec_ee.entry_information20,
2148 p_entry_information21 => p_rec_ee.entry_information21,
2149 p_entry_information22 => p_rec_ee.entry_information22,
2150 p_entry_information23 => p_rec_ee.entry_information23,
2151 p_entry_information24 => p_rec_ee.entry_information24,
2152 p_entry_information25 => p_rec_ee.entry_information25,
2153 p_entry_information26 => p_rec_ee.entry_information26,
2154 p_entry_information27 => p_rec_ee.entry_information27,
2155 p_entry_information28 => p_rec_ee.entry_information28,
2156 p_entry_information29 => p_rec_ee.entry_information29,
2157 p_entry_information30 => p_rec_ee.entry_information30,
2158 p_num_entry_values => p_num_eevs,
2159 p_input_value_id_tbl => p_tbl_ivids,
2160 p_entry_value_tbl => p_tbl_eevs
2161 );
2162 --
2163 if l_eeid_out is not null then
2164 -- Set flag to denote entry has been recreated
2165 l_entry_recreated := true;
2166 end if;
2167 --
2168 if g_debug then
2169 --
2170 hr_utility.set_location(l_proc,40);
2171 hr_utility.trace('***** new EE>' || l_eeid_out || '<');
2172 hr_utility.trace('***** actual ESD of new EE>' ||
2173 l_calc_ee_esd || '<');
2174 hr_utility.trace('***** actual EED of new EE>' ||
2175 l_ee_eed_out || '<');
2176 --
2177 end if;
2178 --
2179 if l_ee_eed_out > l_calc_ee_eed then
2180 --
2181 -- the above call creates the EE upto the least of the:
2182 -- EL's EED or
2183 -- asg piece's EED
2184 --
2185 -- if the EED of the created EE is greater than the calc
2186 -- EED, bring it back
2187 -- nb. only 1 EE exists at this stage, therefore no need
2188 -- to use ESD and EED
2189 --
2190 if g_debug then
2191 hr_utility.set_location(l_proc,50);
2192 hr_utility.trace('***** bring EED of new EE back');
2193 end if;
2194 --
2195 -- Change l_ee_eed_out here, for separate update to
2196 -- pay_element_entries_f, below, which *always* occurs.
2197 --
2198 l_ee_eed_out := l_calc_ee_eed;
2199 --
2200 update pay_element_entry_values_f eev
2201 set eev.effective_end_date = l_ee_eed_out
2202 where eev.element_entry_id = l_eeid_out;
2203 --
2204 end if;
2205 --
2206 -- Set attributes on pay_element_entries_f that are not supported by API.
2207 -- Also, effective_end_date of new entry is brought back here, if
2208 -- necessary.
2209 --
2210 update pay_element_entries_f pee
2211 set pee.effective_end_date = l_ee_eed_out,
2212 pee.balance_adj_cost_flag = p_rec_ee.balance_adj_cost_flag,
2213 pee.source_asg_action_id = p_rec_ee.source_asg_action_id,
2214 pee.source_link_id = p_rec_ee.source_link_id,
2215 pee.source_trigger_entry = p_rec_ee.source_trigger_entry,
2216 pee.source_period = p_rec_ee.source_period,
2217 pee.source_run_type = p_rec_ee.source_run_type,
2218 pee.source_start_date = p_rec_ee.source_start_date,
2219 pee.source_end_date = p_rec_ee.source_end_date
2220 where pee.element_entry_id = l_eeid_out
2221 and l_calc_ee_esd between
2222 pee.effective_start_date and pee.effective_end_date;
2223 --
2224 end loop;
2225 --
2226 close l_eligible_links_cv;
2227 --
2228 end if;
2229 --
2230 -- call routine to maintain entities with FKs to element entry
2231 -- row just date ended and newly created
2232 --
2233 if g_debug then
2234 hr_utility.set_location(l_proc,60);
2235 hr_utility.trace('***** maintain dependent entities');
2236 end if;
2237 --
2238 maintain_dependent_entities(
2239 p_element_entry_id,
2240 p_ee_effective_start_date,
2241 p_validation_start_date - 1,
2242 l_eeid_out,
2243 l_calc_ee_esd,
2244 l_calc_ee_eed
2245 );
2246 --
2247 if g_debug then
2248 hr_utility.set_location(l_proc,70);
2249 hr_utility.trace('********** AFTER OAB **********');
2250 end if;
2251 --
2252 p_entry_recreated := l_entry_recreated;
2253 --
2254 end recreate_cached_entry;
2255 --
2256 ------------------------------------------------------------------------------
2257 -- NAME --
2258 -- hrentmnt.remove_ineligible_recurring --
2259 -- --
2260 -- DESCRIPTION --
2261 -- Removes any recurring element entries for a particular assignment and --
2262 -- element link that exist within a specified period of time that are not --
2263 -- eligible, or exist beyond the start of the assignment's TERM_ASSIGN status.
2264 ------------------------------------------------------------------------------
2265 --
2266 procedure remove_ineligible_recurring
2267 (
2268 p_assignment_id number,
2269 p_entries_changed in out nocopy varchar2,
2270 p_validation_start_date date,
2271 p_validation_end_date date,
2272 p_dt_mode varchar2 default null
2273 ) is
2274 --
2275 -- Local Cursors
2276 --
2277 cursor csr_orphaned_entries
2278 (
2279 p_assignment_id number
2280 ) is
2281 select distinct ee.element_entry_id
2282 from pay_element_entries_f ee
2283 where ee.assignment_id = p_assignment_id;
2284 --
2285 cursor csr_entry
2286 (
2287 p_assignment_id number,
2288 p_validation_start_date date,
2289 p_validation_end_date date
2290 ) is
2291 select
2292 distinct
2293 ee.element_entry_id,
2294 ee.creator_type,
2295 ee.creator_id,
2296 ee.effective_start_date,
2297 ee.effective_end_date,
2298 el.element_link_id,
2299 el.standard_link_flag,
2300 el.element_type_id
2301 from
2302 pay_element_entries_f ee,
2303 pay_element_links_f el,
2304 pay_element_types_f et
2305 where ee.assignment_id = p_assignment_id
2306 and ee.effective_start_date <= p_validation_end_date
2307 and ee.effective_end_date >= p_validation_start_date
2308 and ee.entry_type='E'
2309 and ee.element_link_id=el.element_link_id
2310 and el.effective_start_date <= ee.effective_end_date
2311 and el.effective_end_date >= ee.effective_start_date
2312 -- start of change 115.18 --
2313 and ee.effective_start_date between
2314 el.effective_start_date and el.effective_end_date
2315 -- end of change 115.18 --
2316 and el.element_type_id=et.element_type_id
2317 and et.effective_start_date <= el.effective_end_date
2318 and et.effective_end_date >= el.effective_start_date
2319 -- start of change 115.19 --
2320 and ee.effective_start_date between
2321 et.effective_start_date and et.effective_end_date
2322 -- end of change 115.19 --
2323 and et.processing_type='R'
2324 and (
2325 not exists
2326 (select null
2327 from per_all_assignments_f asg
2328 where asg.assignment_id = ee.assignment_id
2329 /* Added Benefits assignment type to the below code to ensure
2330 removal of entries wont happen in the case of benifits
2331 assignment type also */
2332 and (asg.assignment_type = 'E' or asg.assignment_type='B')
2333 and asg.effective_start_date <= p_validation_end_date
2334 and asg.effective_end_date >= p_validation_start_date
2335 and ((el.payroll_id is not null and
2336 el.payroll_id = asg.payroll_id)
2337 or (el.link_to_all_payrolls_flag = 'Y' and
2338 asg.payroll_id is not null)
2339 or (el.payroll_id is null and
2340 el.link_to_all_payrolls_flag = 'N'))
2341 and (el.job_id is null or
2342 el.job_id = asg.job_id)
2343 and (el.grade_id is null or
2344 el.grade_id = asg.grade_id)
2345 and (el.position_id is null or
2346 el.position_id = asg.position_id)
2347 and (el.organization_id is null or
2348 el.organization_id = asg.organization_id)
2349 and (el.location_id is null or
2350 el.location_id = asg.location_id)
2351 -- start of change 115.22 --
2352 and (
2353 --
2354 -- if EL is associated with a pay basis then this clause fails
2355 --
2356 el.pay_basis_id is null and
2357 NOT EXISTS
2358 (SELECT pb.pay_basis_id
2359 FROM PER_PAY_BASES pb,
2360 PAY_INPUT_VALUES_F iv,
2361 PER_PAY_PROPOSALS pp
2362 WHERE iv.element_type_id = el.element_type_id
2363 and iv.effective_start_date <= asg.effective_start_date
2364 and iv.effective_end_date >= asg.effective_start_date
2365 and pb.input_value_id = iv.input_value_id
2366 and pb.business_group_id = asg.business_group_id
2367 and pp.assignment_id = asg.assignment_id /*fix 176449*/
2368 )
2369 or
2370 --
2371 -- if EL is associated with a pay basis then the associated
2372 -- PB_ID must match the PB_ID on ASG
2373 --
2374 el.pay_basis_id is null and
2375 EXISTS
2376 (SELECT pb.pay_basis_id
2377 FROM PER_PAY_BASES pb,
2378 PAY_INPUT_VALUES_F iv
2379 WHERE iv.element_type_id = el.element_type_id
2380 and iv.effective_start_date <= asg.effective_start_date
2381 and iv.effective_end_date >= asg.effective_start_date
2382 and pb.input_value_id =
2383 iv.input_value_id
2384 and pb.pay_basis_id = asg.pay_basis_id
2385 )
2386 -- change 115.26
2387 or
2388 el.standard_link_flag = 'Y' and
2389 el.pay_basis_id is null and
2390 asg.pay_basis_id is null and
2391 EXISTS
2392 (SELECT pb.pay_basis_id
2393 FROM PER_PAY_BASES pb,
2394 PAY_INPUT_VALUES_F iv
2395 WHERE iv.element_type_id = el.element_type_id
2396 and iv.effective_start_date <= asg.effective_start_date
2397 and iv.effective_end_date >= asg.effective_start_date
2398 and pb.input_value_id =
2399 iv.input_value_id
2400 and pb.business_group_id = asg.business_group_id
2401 )
2402 or
2403 el.pay_basis_id = asg.pay_basis_id
2404 )
2405 -- end of change 115.22 --
2406 and (el.employment_category is null or
2407 el.employment_category = asg.employment_category)
2408 and (el.people_group_id is null or
2409 exists
2410 (select null
2411 from pay_assignment_link_usages_f alu
2412 where alu.assignment_id = ee.assignment_id
2413 and alu.element_link_id = ee.element_link_id
2414 and alu.effective_start_date <=
2415 asg.effective_end_date
2416 and alu.effective_end_date >=
2417 asg.effective_start_date))))
2418 ;
2419 --
2420 procedure check_parameters
2421 is
2422 --
2423 begin
2424 --
2425 hr_utility.trace('In hrentmnt.remove_ineligible_recurring');
2426 hr_utility.trace ('');
2427 hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
2428 hr_utility.trace (' p_entries_changed = '||p_entries_changed);
2429 hr_utility.trace (' p_validation_start_date = '||to_char(p_validation_start_date,'DD-MON-YYYY'));
2430 hr_utility.trace (' p_validation_end_date = '||to_char(p_validation_end_date,'DD-MON-YYYY'));
2431 hr_utility.trace (' p_dt_mode = '||p_dt_mode);
2432 hr_utility.trace ('');
2433 --
2434 end check_parameters;
2435 --
2436 --
2437 --
2438 -- Bugfix 4358408
2439 -- The 'do normal delete of REE', 'bring end date of current REE
2440 -- backwards' and 'move start date of current REE forwards' logic
2441 -- within this procedure has all been re-created as modular
2442 -- procedures (do_normal_delete_of_ree, bring_ree_end_date_backwards
2443 -- and bring_ree_start_date_forwards respectively).
2444 --
2445 procedure do_normal_delete_of_ree (
2446 p_assignment_id in number,
2447 p_element_type_id in number,
2448 p_element_link_id in number,
2449 p_element_entry_id in number,
2450 p_validation_start_date in date,
2451 p_validation_end_date in date,
2452 p_ee_effective_start_date in date,
2453 p_ee_effective_end_date in date,
2454 p_ee_creator_type in varchar2,
2455 p_ee_creator_id in number,
2456 p_entries_changed in out nocopy varchar2
2457 )
2458 is
2459 --
2460 l_rec_ee hrentmnt.t_ele_entry_rec;
2461 l_num_eevs number := 0;
2462 l_tbl_ivids hr_entry.number_table;
2463 l_tbl_eevs hr_entry.varchar2_table;
2464 l_entry_recreated boolean := false;
2465 --
2466 begin
2467 --
2468 if g_debug then
2469 hr_utility.trace ('***** doing normal delete of REE');
2470 hr_utility.trace ('***** caching EE before delete');
2471 end if;
2472 --
2473 hrentmnt.cache_element_entry(
2474 p_element_entry_id,
2475 p_ee_effective_end_date,
2476 l_rec_ee,
2477 l_num_eevs,
2478 l_tbl_ivids,
2479 l_tbl_eevs);
2480 --
2481 hrentmnt.validate_adjust_entry
2482 ('DELETE',
2483 p_assignment_id,
2484 p_element_entry_id,
2485 null,
2486 null,
2487 null,
2488 p_ee_effective_start_date,
2489 p_ee_effective_end_date,
2490 p_entries_changed);
2491 --
2492 delete from pay_element_entry_values_f eev
2493 where eev.element_entry_id = p_element_entry_id
2494 and eev.effective_start_date >= p_ee_effective_start_date
2495 and eev.effective_end_date <= p_ee_effective_end_date;
2496 --
2497 delete from pay_element_entries_f ee
2498 where ee.element_entry_id = p_element_entry_id
2499 and ee.effective_start_date >= p_ee_effective_start_date
2500 and ee.effective_end_date <= p_ee_effective_end_date;
2501 --
2502 -- Attempt to recreate cached entry before doing further deletes
2503 --
2504 recreate_cached_entry (
2505 p_assignment_id,
2506 p_element_type_id,
2507 p_element_link_id,
2508 p_element_entry_id,
2509 p_validation_start_date,
2510 p_validation_end_date,
2511 p_ee_effective_start_date,
2512 p_ee_effective_end_date,
2513 p_ee_creator_type,
2514 l_rec_ee,
2515 l_num_eevs,
2516 l_tbl_ivids,
2517 l_tbl_eevs,
2518 l_entry_recreated
2519 );
2520 --
2521 if not l_entry_recreated then
2522 --
2523 -- Cached entry was not recreated so proceed with deletes
2524 if g_debug then
2525 hr_utility.trace(' Cached entry not recreated. Continuing with delete.');
2526 end if;
2527 --
2528 -- Only delete grossup balance exclusion rows if we are purging the
2529 -- entry.
2530 --
2531 delete from pay_grossup_bal_exclusions exc
2532 where exc.source_id = p_element_entry_id
2533 and exc.source_type = 'EE'
2534 and not exists
2535 ( select null
2536 from pay_element_entries_f pee
2537 where pee.element_entry_id = p_element_entry_id);
2538 --
2539 -- Call the routine that checks whether an illegal purge
2540 -- has occurred (i.e. disallowed by profile).
2541 hrentmnt.validate_purge(p_element_entry_id, p_element_link_id);
2542 --
2543 -- Salary Admin entry is being removed. See if the pay proposal is used by
2544 -- any other entry. If not then it is removed.
2545 --
2546 if p_ee_creator_type = 'SP' then
2547 --
2548 hrentmnt.remove_pay_proposals
2549 (p_assignment_id,
2550 p_ee_creator_id);
2551 --
2552 end if;
2553 --
2554 end if;
2555 --
2556 end do_normal_delete_of_ree;
2557 --
2558 --
2559 -- bring_ree_end_date_backwards:
2560 -- ***** updating "end" date of current REE backwards *****
2561 -- ***** VSD|<----- |VED *****
2562 -- ***** current |-----------------| *****
2563 --
2564 procedure bring_ree_end_date_backwards (
2565 p_assignment_id in number,
2566 p_element_type_id in number,
2567 p_element_link_id in number,
2568 p_element_entry_id in number,
2569 p_validation_start_date in date,
2570 p_validation_end_date in date,
2571 p_ee_effective_start_date in date,
2572 p_ee_effective_end_date in date,
2573 p_ee_creator_type in varchar2,
2574 p_ee_creator_id in number,
2575 p_entries_changed in out nocopy varchar2
2576 )
2577 is
2578 --
2579 CURSOR csr_bus_group(p_assignment_id number) IS
2580 SELECT business_group_id
2581 FROM per_all_assignments_f
2582 WHERE assignment_id = p_assignment_id;
2583
2584 /*Cursor added for bug:7440183for getting leg code */
2585 CURSOR csr_leg_grp(p_business_group_id number) IS
2586 SELECT LEGISLATION_CODE
2587 FROM per_business_groups
2588 where business_group_id=p_business_group_id;
2589
2590 --
2591 l_rec_ee hrentmnt.t_ele_entry_rec;
2592 l_num_eevs number := 0;
2593 l_tbl_ivids hr_entry.number_table;
2594 l_tbl_eevs hr_entry.varchar2_table;
2595 l_entry_recreated boolean := false;
2596 l_dyt_mode varchar2(75);
2597 l_business_group_id number;
2598 l_legislation_code varchar2(10);
2599 --
2600 begin
2601 --
2602 if g_debug then
2603 hr_utility.trace ('***** updating "end" date of current REE backwards *****');
2604 hr_utility.trace ('***** VSD|<----- |VED *****');
2605 hr_utility.trace ('***** current |-----------------| *****');
2606 end if;
2607 --
2608 hrentmnt.validate_adjust_entry
2609 ('DELETE',
2610 p_assignment_id,
2611 p_element_entry_id,
2612 null,
2613 null,
2614 null,
2615 --
2616 -- bugfix 1115901
2617 --
2618 p_validation_start_date,
2619 p_ee_effective_end_date,
2620 p_entries_changed);
2621 --
2622 -- update first piece of REE that crosses eligibility boundary,
2623 -- do not delete as this REE cannot be recreated later,
2624 -- update its EED so that it is a day less than the VSD
2625 --
2626 UPDATE PAY_ELEMENT_ENTRY_VALUES_F eev
2627 SET eev.effective_end_date = p_validation_start_date - 1
2628 WHERE eev.element_entry_id = p_element_entry_id
2629 and eev.effective_start_date < p_validation_start_date
2630 -- Change 115.60
2631 -- and eev.effective_end_date > p_validation_start_date
2632 and eev.effective_end_date >= p_validation_start_date
2633 -- End of change 115.60
2634 ;
2635 --
2636 UPDATE PAY_ELEMENT_ENTRIES_F ee
2637 SET ee.effective_end_date = p_validation_start_date - 1,
2638 ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
2639 null),
2640 ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
2641 WHERE ee.element_entry_id = p_element_entry_id
2642 and ee.effective_start_date < p_validation_start_date
2643 -- Change 115.60
2644 -- and ee.effective_end_date > p_validation_start_date
2645 and ee.effective_end_date >= p_validation_start_date
2646 -- End of change 115.60
2647 ;
2648 --
2649 -- take a copy of the updated EE as these details will be used
2650 -- to create the new EE
2651 -- Bugfix 4520103
2652 -- Use p_validation_start_date - 1 as this is the new effective end
2653 -- date of the entry.
2654 --
2655 hrentmnt.cache_element_entry(
2656 p_element_entry_id,
2657 p_validation_start_date - 1,
2658 l_rec_ee,
2659 l_num_eevs,
2660 l_tbl_ivids,
2661 l_tbl_eevs);
2662 --
2663 recreate_cached_entry (
2664 p_assignment_id,
2665 p_element_type_id,
2666 p_element_link_id,
2667 p_element_entry_id,
2668 p_validation_start_date,
2669 p_validation_end_date,
2670 p_ee_effective_start_date,
2671 p_ee_effective_end_date,
2672 p_ee_creator_type,
2673 l_rec_ee,
2674 l_num_eevs,
2675 l_tbl_ivids,
2676 l_tbl_eevs,
2677 l_entry_recreated
2678 );
2679
2680 -- Bug 6164943 - Log an event in PAY_PROCESS_EVENTS if the entry is end dated
2681 -- permanently
2682
2683 if(not l_entry_recreated) then
2684 --
2685 l_dyt_mode := pay_dyn_triggers.g_dyt_mode;
2686 pay_dyn_triggers.g_dyt_mode := 'DELETE';
2687 --
2688 open csr_bus_group(p_assignment_id);
2689 fetch csr_bus_group into l_business_group_id;
2690 close csr_bus_group;
2691 --
2692 open csr_leg_grp(l_business_group_id);
2693 fetch csr_leg_grp into l_legislation_code;
2694 close csr_leg_grp;
2695
2696 /* Changed the null value for p_legislation_code to the actual
2697 leg code for fixing bug 7440183 */
2698
2699 pay_continuous_calc.element_entries_ard(
2700 p_business_group_id => l_business_group_id,
2701 p_legislation_code => l_legislation_code,
2702 p_assignment_id => p_assignment_id,
2703 p_old_ELEMENT_ENTRY_ID => p_element_entry_id,
2704 p_old_effective_start_date => p_ee_effective_start_date,
2705 p_new_effective_start_date => p_ee_effective_start_date,
2706 p_old_effective_end_date => p_ee_effective_end_date,
2707 p_new_effective_end_date => p_validation_start_date -1,
2708 p_old_ELEMENT_TYPE_ID => p_element_type_id
2709 );
2710 --
2711 pay_dyn_triggers.g_dyt_mode := l_dyt_mode;
2712 --
2713 end if;
2714 --
2715 end bring_ree_end_date_backwards;
2716 --
2717 --
2718 -- bring_ree_start_date_forwards:
2719 -- ***** updating "start" date of current REE forwards *****
2720 -- ***** VSD| ----->|VED *****
2721 -- ***** current |-----------------> *****
2722 --
2723 procedure bring_ree_start_date_forwards (
2724 p_assignment_id in number,
2725 p_element_entry_id in number,
2726 p_ee_effective_start_date in date,
2727 p_validation_end_date in date,
2728 p_ee_creator_type in varchar2,
2729 p_ee_creator_id in number,
2730 p_entries_changed in out nocopy varchar2
2731 )
2732 is
2733 begin
2734 --
2735 if g_debug then
2736 hr_utility.trace ('***** updating "start" date of current REE forwards *****');
2737 hr_utility.trace ('***** VSD| ----->|VED *****');
2738 hr_utility.trace ('***** current |-----------------> *****');
2739 end if;
2740 --
2741 hrentmnt.validate_adjust_entry
2742 ('DELETE',
2743 p_assignment_id,
2744 p_element_entry_id,
2745 null,
2746 null,
2747 null,
2748 p_ee_effective_start_date,
2749 p_validation_end_date,
2750 p_entries_changed);
2751 --
2752 -- update piece of REE that crosses eligibility boundary,
2753 -- do not delete as this REE cannot be recreated later,
2754 -- update its EED so that it is a day greater than the VSD
2755 --
2756 UPDATE PAY_ELEMENT_ENTRY_VALUES_F eev
2757 SET eev.effective_start_date = p_validation_end_date + 1
2758 WHERE eev.element_entry_id = p_element_entry_id
2759 -- Change 115.60
2760 -- and eev.effective_start_date < p_validation_end_date
2761 and eev.effective_start_date <= p_validation_end_date
2762 -- End of change 115.60
2763 and eev.effective_end_date > p_validation_end_date
2764 ;
2765
2766 UPDATE PAY_ELEMENT_ENTRIES_F ee
2767 SET ee.effective_start_date = p_validation_end_date + 1,
2768 ee.updating_action_id = decode(ee.updating_action_type, 'S', ee.updating_action_id, null),
2769 ee.updating_action_type = decode(ee.updating_action_type, 'S', 'S', null)
2770 WHERE ee.element_entry_id = p_element_entry_id
2771 -- Change 115.60
2772 -- and ee.effective_start_date < p_validation_end_date
2773 and ee.effective_start_date <= p_validation_end_date
2774 -- End of change 115.60
2775 and ee.effective_end_date > p_validation_end_date
2776 ;
2777 -- end of change 115.21 --
2778
2779 -- start of change 115.22 --
2780 --
2781 -- also maintain salary proposal change date
2782 --
2783 if p_ee_creator_type = 'SP' then
2784 if g_debug then
2785 hr_utility.trace ('***** maintain end date of PP >' ||
2786 (p_validation_end_date + 1) || '<');
2787 end if;
2788 UPDATE PER_PAY_PROPOSALS pp
2789 SET pp.change_date = p_validation_end_date + 1
2790 WHERE pp.assignment_id = p_assignment_id
2791 and pp.pay_proposal_id = p_ee_creator_id
2792 ;
2793 end if;
2794 -- end of change 115.22 --
2795 --
2796 end bring_ree_start_date_forwards;
2797 --
2798 begin
2799 --
2800 if g_debug then
2801 check_parameters;
2802 end if;
2803 --
2804 -- Bugfix 2725909
2805 -- When datetrack mode is ZAP, remove all entries and entry values, as
2806 -- parent assignment has been removed, and then exit.
2807 --
2808 if p_dt_mode = 'ZAP' then
2809 --
2810 if g_debug then
2811 --
2812 hr_utility.trace('ZAP orphaned entries and entry values');
2813 --
2814 end if;
2815 --
2816 -- Remove orphaned entries and entry values
2817 --
2818 for v_entry in csr_orphaned_entries(p_assignment_id) loop
2819 --
2820 delete from pay_element_entry_values_f eev
2821 where eev.element_entry_id = v_entry.element_entry_id;
2822 --
2823 delete from pay_element_entries_f ee
2824 where ee.element_entry_id = v_entry.element_entry_id;
2825 --
2826 end loop;
2827 --
2828 if g_debug then
2829 --
2830 hr_utility.trace('Out hrentmnt.remove_ineligible_recurring');
2831 --
2832 end if;
2833 --
2834 return;
2835 --
2836 end if;
2837 --
2838 -- Retrieve all recurring entries for the assignment that are no longer
2839 -- valid ie. assignment and link no longer matches over the existence of the
2840 -- element entry.
2841 --
2842 for v_entry in csr_entry(p_assignment_id,
2843 p_validation_start_date, p_validation_end_date) loop
2844 --
2845 -- bug 891323,
2846 -- if a non-SL'ed REE is crossing eligibility boundary then
2847 -- do special processing as it cannot be recreated later
2848 --
2849 if v_entry.effective_start_date < p_validation_start_date and
2850 -- change 115.23 --
2851 v_entry.effective_end_date >= p_validation_start_date then
2852 --
2853 -- ***** updating "end" date of current REE backwards *****
2854 -- ***** VSD|<----- |VED *****
2855 -- ***** current |-----------------| *****
2856 --
2857 bring_ree_end_date_backwards (
2858 p_assignment_id => p_assignment_id,
2859 p_element_type_id => v_entry.element_type_id,
2860 p_element_link_id => v_entry.element_link_id,
2861 p_element_entry_id => v_entry.element_entry_id,
2862 p_validation_start_date => p_validation_start_date,
2863 p_validation_end_date => p_validation_end_date,
2864 p_ee_effective_start_date => v_entry.effective_start_date,
2865 p_ee_effective_end_date => v_entry.effective_end_date,
2866 p_ee_creator_type => v_entry.creator_type,
2867 p_ee_creator_id => v_entry.creator_id,
2868 p_entries_changed => p_entries_changed
2869 );
2870 --
2871 -- start of change 115.21 --
2872 -- change 115.23 --
2873 elsif v_entry.effective_start_date <= p_validation_end_date and
2874 v_entry.effective_end_date > p_validation_end_date then
2875 --
2876 -- ***** updating "start" date of current REE forwards *****
2877 -- ***** VSD| ----->|VED *****
2878 -- ***** current |-----------------> *****
2879 --
2880 bring_ree_start_date_forwards (
2881 p_assignment_id => p_assignment_id,
2882 p_element_entry_id => v_entry.element_entry_id,
2883 p_ee_effective_start_date => v_entry.effective_start_date,
2884 p_validation_end_date => p_validation_end_date,
2885 p_ee_creator_type => v_entry.creator_type,
2886 p_ee_creator_id => v_entry.creator_id,
2887 p_entries_changed => p_entries_changed
2888 );
2889 --
2890 -- start of change 115.23 --
2891 elsif v_entry.effective_start_date >= p_validation_start_date and
2892 v_entry.effective_end_date <= p_validation_end_date then
2893 -- end of change 115.23 --
2894 --
2895 -- Entry exists entirely within the validation period.
2896 -- Do normal delete of REE.
2897 --
2898 -- Bugfix 5247607
2899 -- Now, whenever a recurring entry is deleted as a result of an
2900 -- assignment criteria change, we always look for a suitable
2901 -- alternative link under which the entry can be recreated. A new
2902 -- procedure, recreate_cached_entry, has been created to do this
2903 -- for us. This is now called from do_normal_delete_of_ree.
2904 --
2905 do_normal_delete_of_ree (
2906 p_assignment_id => p_assignment_id,
2907 p_element_type_id => v_entry.element_type_id,
2908 p_element_link_id => v_entry.element_link_id,
2909 p_element_entry_id => v_entry.element_entry_id,
2910 p_validation_start_date => p_validation_start_date,
2911 p_validation_end_date => p_validation_end_date,
2912 p_ee_effective_start_date => v_entry.effective_start_date,
2913 p_ee_effective_end_date => v_entry.effective_end_date,
2914 p_ee_creator_type => v_entry.creator_type,
2915 p_ee_creator_id => v_entry.creator_id,
2916 p_entries_changed => p_entries_changed
2917 );
2918 --
2919 else
2920 -- start of change 115.23 --
2921 if g_debug then
2922 hr_utility.trace ('***** not adjusting, REE outside validation range *****');
2923 hr_utility.trace ('***** VSD| |VED *****');
2924 hr_utility.trace ('***** current |----| *****');
2925 hr_utility.trace ('***** or *****');
2926 hr_utility.trace ('***** current |-------> *****');
2927 end if;
2928 -- end of change 115.23 --
2929 end if;
2930 end loop;
2931 --
2932 if g_debug then
2933 hr_utility.trace('Out hrentmnt.remove_ineligible_recurring');
2934 end if;
2935 --
2936 end remove_ineligible_recurring;
2937 --
2938 ------------------------------------------------------------------------------
2939 -- NAME --
2940 -- hrentmnt.remove_ineligible_nonrecurring --
2941 -- --
2942 -- DESCRIPTION --
2943 -- Deletes any nonrecurring element entries that will be made ineligible by --
2944 -- a change in assignment criteria. The delete runs after the change to the --
2945 -- assignment has taken place. --
2946 -- NB. val_nonrec_entries should be run first. This makes sure that only --
2947 -- processed nonrecurring and unprocessed personnel element entries --
2948 -- are deleted. --
2949 ------------------------------------------------------------------------------
2950 --
2951 procedure remove_ineligible_nonrecurring
2952 (
2953 p_assignment_id number,
2954 p_validation_start_date date,
2955 p_validation_end_date date,
2956 p_entries_changed in out nocopy varchar2
2957 ) is
2958 --Added for 6809717
2959 l_obj_ver_num number;
2960 l_eff_str_date date;
2961 l_eff_end_date date;
2962 l_del_war boolean;
2963 --
2964 cursor csr_entry
2965 (
2966 p_assignment_id number,
2967 p_validation_start_date date,
2968 p_validation_end_date date
2969 ) is
2970 select ee.element_entry_id,
2971 ee.effective_start_date,
2972 ee.effective_end_date,
2973 ee.element_link_id
2974 from pay_element_entries_f ee
2975 where ee.assignment_id = p_assignment_id
2976 and ee.effective_start_date <= p_validation_end_date
2977 and ee.effective_end_date >= p_validation_start_date
2978 and ee.creator_type in ('F','H')
2979 and ((ee.entry_type <> 'E')
2980 or (ee.entry_type = 'E' and
2981 exists
2982 (select null
2983 from pay_element_links_f el,
2984 pay_element_types_f et
2985 where el.element_link_id = ee.element_link_id
2986 and el.element_type_id = et.element_type_id
2987 and et.processing_type = 'N')))
2988 and not exists
2989 (select null
2990 from per_all_assignments_f asg,
2991 pay_element_links_f el
2992 where el.element_link_id = ee.element_link_id
2993 and asg.assignment_id = ee.assignment_id
2994 and asg.assignment_type = 'E'
2995 and asg.effective_start_date <= ee.effective_end_date
2996 -- changed to validation start date not effective start date
2997 -- wmcveagh bug 586139 17/2/98
2998 and asg.effective_end_date >= p_validation_start_date
2999 and el.effective_start_date <= ee.effective_end_date
3000 and el.effective_end_date >= ee.effective_start_date
3001 and el.effective_start_date <= asg.effective_end_date
3002 and el.effective_end_date >= asg.effective_start_date
3003 and ((el.payroll_id is not null and
3004 el.payroll_id = asg.payroll_id)
3005 or (el.link_to_all_payrolls_flag = 'Y' and
3006 asg.payroll_id is not null)
3007 or (el.payroll_id is null and
3008 el.link_to_all_payrolls_flag = 'N'))
3009 and (el.job_id is null or
3010 el.job_id = asg.job_id)
3011 and (el.grade_id is null or
3012 el.grade_id = asg.grade_id)
3013 and (el.position_id is null or
3014 el.position_id = asg.position_id)
3015 and (el.organization_id is null or
3016 el.organization_id = asg.organization_id)
3017 and (el.location_id is null or
3018 el.location_id = asg.location_id)
3019 and (el.pay_basis_id is null or
3020 el.pay_basis_id = asg.pay_basis_id)
3021 and (el.employment_category is null or
3022 el.employment_category = asg.employment_category)
3023 and (el.people_group_id is null or
3024 exists
3025 (select null
3026 from pay_assignment_link_usages_f alu
3027 where alu.assignment_id = ee.assignment_id
3028 and alu.element_link_id = ee.element_link_id
3029 and alu.effective_start_date <=
3030 asg.effective_end_date
3031 and alu.effective_end_date >=
3032 asg.effective_start_date)));
3033 --
3034 procedure check_parameters is
3035 begin
3036 hr_utility.trace('In hrentmnt.remove_ineligible_nonrecurring');
3037 --
3038 hr_utility.trace ('');
3039 hr_utility.trace (' p_assignment_id = '
3040 ||to_char(p_assignment_id));
3041 hr_utility.trace (' p_validation_start_date = '
3042 ||to_char(p_validation_start_date));
3043 hr_utility.trace (' p_validation_end_date = '
3044 ||to_char(p_validation_end_date));
3045 hr_utility.trace (' p_entries_changed = '
3046 ||p_entries_changed);
3047 hr_utility.trace ('');
3048 --
3049 end check_parameters;
3050 --
3051 begin
3052 --
3053 if g_debug then
3054 check_parameters;
3055 end if;
3056 --
3057 -- Retrieve all nonrecurring entries for the assignment that are no longer
3058 -- valid ie. assignment and link no longer matches over the existence of the
3059 -- element entry.
3060 --
3061 for v_entry in csr_entry(p_assignment_id,
3062 p_validation_start_date,
3063 p_validation_end_date) loop
3064 --
3065 --
3066 hrentmnt.validate_adjust_entry
3067 ('DELETE',
3068 p_assignment_id,
3069 v_entry.element_entry_id,
3070 null,
3071 null,
3072 null,
3073 v_entry.effective_start_date,
3074 v_entry.effective_end_date,
3075 p_entries_changed);
3076 --
3077 --
3078 delete from pay_run_results rr
3079 where rr.status not like 'P%'
3080 and rr.source_type = 'E'
3081 and rr.source_id = v_entry.element_entry_id;
3082 --
3083 --
3084 /*Added for 6809717 To enable end dating of invalidated entries
3085 the following code has been added*/
3086
3087 if g_debug then
3088 hr_utility.trace('before delete');
3089 end if;
3090
3091 select OBJECT_VERSION_NUMBER into l_obj_ver_num
3092 from pay_element_entries_f
3093 where p_validation_start_date-1 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
3094 and element_entry_id=v_entry.element_entry_id;
3095
3096 if g_debug then
3097 hr_utility.trace(' obj vber no'||l_obj_ver_num);
3098 end if;
3099 pay_element_entry_api.delete_element_entry
3100 (p_datetrack_delete_mode => 'DELETE'
3101 ,p_effective_date => p_validation_start_date-1
3102 ,p_element_entry_id => v_entry.element_entry_id
3103 ,p_object_version_number => l_obj_ver_num
3104 ,p_effective_start_date =>l_eff_str_date
3105 ,p_effective_end_date =>l_eff_end_date
3106 ,p_delete_warning =>l_del_war);
3107
3108 --Commented the below code as part of 6809717
3109 /* delete from pay_element_entry_values_f eev
3110 where eev.element_entry_id = v_entry.element_entry_id;
3111 --
3112 delete from pay_element_entries_f ee
3113 where ee.element_entry_id = v_entry.element_entry_id;*/
3114 --
3115 -- Call the routine that checks whether an illegal purge
3116 -- has occurred (i.e. disallowed by profile).
3117 hrentmnt.validate_purge(v_entry.element_entry_id, v_entry.element_link_id);
3118 --
3119 end loop;
3120 --
3121 if g_debug then
3122 hr_utility.trace('Out hrentmnt.remove_ineligible_nonrecurring');
3123 end if;
3124 --
3125 end remove_ineligible_nonrecurring;
3126 --
3127 ------------------------------------------------------------------------------
3128 -- NAME --
3129 -- hrentmnt.return_entry_dates --
3130 -- --
3131 -- DESCRIPTION --
3132 -- Given an assignment and element link this returns the start and end --
3133 -- dates of the element entry taking into account personal qualifying --
3134 -- conditions and also any future terminations of the assignment NB. for --
3135 -- discretionary element entries the personal qualifying conditions are not --
3136 -- taken into account as the user is allowed to ignore these when creating --
3137 -- an entry. --
3138 ------------------------------------------------------------------------------
3139 --
3140 procedure return_entry_dates
3141 (
3142 p_assignment_id number,
3143 p_asg_start_date date,
3144 p_element_link_id number,
3145 p_link_start_date date,
3146 p_standard_link_flag varchar2,
3147 p_entry_start_date out nocopy date,
3148 p_entry_end_date out nocopy date
3149 ) is
3150 --
3151 -- Local Variables
3152 --
3153 v_los_date date;
3154 v_age_date date;
3155 v_pqc_start_date date;
3156 v_entry_start_date date;
3157 v_entry_end_date date;
3158 --
3159 procedure check_parameters is
3160 --
3161 begin
3162 --
3163 hr_utility.trace('In hrentmnt.return_entry_dates');
3164 hr_utility.trace ('');
3165 hr_utility.trace (' p_assignment_id = '
3166 ||to_char (p_assignment_id));
3167 hr_utility.trace (' p_asg_start_date = '
3168 ||to_char (p_asg_start_date));
3169 hr_utility.trace (' p_element_link_id = '
3170 ||to_char (p_element_link_id));
3171 hr_utility.trace (' p_link_start_date = '
3172 ||to_char (p_link_start_date));
3173 hr_utility.trace (' p_standard_link_flag = '
3174 ||p_standard_link_flag);
3175 hr_utility.trace ('');
3176 --
3177 end check_parameters;
3178 --
3179 begin
3180 --
3181 if g_debug then
3182 check_parameters;
3183 end if;
3184 --
3185 -- Only take into account personal qualifying conditions for standard
3186 -- element entries.
3187 --
3188 if p_standard_link_flag = 'Y' then
3189 --
3190 hr_entry.return_qualifying_conditions
3191 (p_assignment_id,
3192 p_element_link_id,
3193 greatest(p_link_start_date,p_asg_start_date),
3194 v_los_date,
3195 v_age_date);
3196 --
3197 v_pqc_start_date := least(nvl(v_los_date,v_age_date),
3198 nvl(v_age_date,v_los_date));
3199 --
3200 -- Discretionary entry so do not apply personal qualifying conditions.
3201 --
3202 else
3203 --
3204 v_pqc_start_date := p_asg_start_date;
3205 --
3206 end if;
3207 --
3208 v_entry_start_date := greatest(p_link_start_date,
3209 p_asg_start_date,
3210 nvl(v_pqc_start_date,p_asg_start_date));
3211 --
3212 -- Calculate the element entry end date taking into account future
3213 -- terminations.
3214 --
3215 v_entry_end_date := hr_entry.recurring_entry_end_date
3216 (p_assignment_id,
3217 p_element_link_id,
3218 greatest(p_link_start_date,p_asg_start_date),
3219 'N',
3220 'N',
3221 null,
3222 null);
3223 --
3224 -- It is possible that personal qualifying conditions can result in an
3225 -- entry not being able to start until after the person has become
3226 -- ineligible for it.
3227 --
3228 -- It is not possible for elements to end before they start
3229 --
3230 if v_entry_start_date <= v_entry_end_date then
3231 p_entry_start_date := v_entry_start_date;
3232 p_entry_end_date := v_entry_end_date;
3233 end if;
3234 --
3235 if g_debug then
3236 hr_utility.trace('Out hrentmnt.return_entry_dates');
3237 end if;
3238 --
3239 end return_entry_dates;
3240 --
3241 ------------------------------------------------------------------------------
3242 -- NAME --
3243 -- hrentmnt.mult_ent_allowed_flag --
3244 -- --
3245 -- DESCRIPTION --
3246 -- Simple function to return a flag indicating if multiple entries are --
3247 -- allowed for a particular element type. --
3248 ------------------------------------------------------------------------------
3249 --
3250 function mult_ent_allowed_flag
3251 (
3252 p_element_link_id number
3253 ) return varchar2 is
3254 --
3255 -- Are multiple entries allowed.
3256 --
3257 cursor csr_element
3258 (
3259 p_element_link_id number
3260 ) is
3261 select et.multiple_entries_allowed_flag
3262 from pay_element_links_f el,
3263 pay_element_types_f et
3264 where el.element_link_id = p_element_link_id
3265 and et.element_type_id = el.element_type_id;
3266 --
3267 -- Local Variables
3268 --
3269 v_mult_ent_allowed_flag varchar2(30);
3270 --
3271 procedure check_parameters is
3272 --
3273 begin
3274 --
3275 hr_utility.trace('In hrentmnt.mult_ent_allowed_flag');
3276 hr_utility.trace ('');
3277 hr_utility.trace (' p_element_link_id = '
3278 ||to_char (p_element_link_id));
3279 hr_utility.trace ('');
3280 --
3281 end check_parameters;
3282 --
3283 begin
3284 --
3285 if g_debug then
3286 check_parameters;
3287 end if;
3288 --
3289 open csr_element(p_element_link_id);
3290 fetch csr_element into v_mult_ent_allowed_flag;
3291 if csr_element%notfound then
3292 close csr_element;
3293 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3294 hr_utility.set_message_token('PROCEDURE',
3295 'hrentmnt.mult_ent_allowed_flag');
3296 hr_utility.set_message_token('STEP','1');
3297 hr_utility.raise_error;
3298 end if;
3299 close csr_element;
3300 --
3301 return v_mult_ent_allowed_flag;
3302 --
3303 end mult_ent_allowed_flag;
3304 --
3305 ------------------------------------------------------------------------------
3306 -- NAME --
3307 -- hrentmnt.val_nonrec_entries --
3308 -- --
3309 -- DESCRIPTION --
3310 -- Checks to see if there are nonrecurring element entries that will be --
3311 -- made ineligible by a change in assignment criteria. The check runs --
3312 -- after the change to the assignment has taken place. The nonrecurring --
3313 -- element entries that are validated are : --
3314 -- --
3315 -- Balance Adjustment processed / unprocessed --
3316 -- Override unprocessed --
3317 -- Replacement Adjustment '' '' --
3318 -- Additive Adjustment '' '' --
3319 -- Additional Entry '' '' --
3320 -- Payroll Nonrecurring '' '' --
3321 -- Special Entries ie. SSP, Quickpay etc ... processed / unprocessed --
3322 -- --
3323 -- NB. the nonrecurring entry is still valid providing the element link --
3324 -- and assignment match for at least one day during the duration of --
3325 -- the entry. --
3326 -- --
3327 -- As this check is run during a batch operation it is not possible to --
3328 -- return specific details for each nonrecurring entry that has been --
3329 -- made ineligible so an error is only raised for the first one found. --
3330 ------------------------------------------------------------------------------
3331 --
3332 procedure val_nonrec_entries
3333 (
3334 p_assignment_id number,
3335 p_validation_start_date date,
3336 p_validation_end_date date
3337 ) is
3338 --
3339 -- Cursor returns all nonrecurring entries for an assignment over a
3340 -- specified time period that are unprocessed overrides, replacement
3341 -- adjustments, additive adjustments, additional entries or nonrecurring
3342 -- payroll entries. It also returns special nonrecurring entries
3343 -- irrespective of them having been processed or not ie. Quickpay
3344 -- entries etc ...
3345 --
3346 cursor csr_entry
3347 (
3348 p_assignment_id number,
3349 p_validation_start_date date,
3350 p_validation_end_date date,
3351 p_adjust_ee_source varchar2
3352 ) is
3353 select ee.*
3354 from pay_element_entries_f ee
3355 ,pay_element_types_f et
3356 where ee.assignment_id = p_assignment_id
3357 and ee.effective_start_date <= p_validation_end_date
3358 and ee.effective_end_date >= p_validation_start_date
3359 and ee.element_type_id = et.element_type_id
3360 and ee.effective_start_date between et.effective_start_date
3361 and et.effective_end_date
3362 --
3363 -- Restrict to nonrecurring entries
3364 --
3365 and (ee.entry_type in ('S','R','A','D')
3366 or (ee.entry_type = 'E'
3367 and et.process_in_run_flag = 'Y'
3368 and et.processing_type = 'N'))
3369 --
3370 -- Restrict to ordinary creator-type entries which have been processed
3371 -- or to special creator-type entries regardless of processing.
3372 --
3373 and ((ee.creator_type in ('F','H')
3374 and not exists
3375 (select null
3376 from pay_run_results rr
3377 where rr.source_id = decode(ee.entry_type,
3378 'A', decode (p_adjust_ee_source,
3379 'T', ee.target_entry_id,
3380 ee.element_entry_id),
3381 'R', decode (p_adjust_ee_source,
3382 'T', ee.target_entry_id,
3383 ee.element_entry_id),
3384 ee.element_entry_id)
3385 and rr.source_type = 'E'
3386 and rr.entry_type = ee.entry_type
3387 and rr.status like 'P%'))
3388 or (ee.creator_type not in ('F','H')))
3389 --
3390 -- Restrict to entries for links which no longer match the assignment
3391 -- criteria.
3392 --
3393 and not exists
3394 (select null
3395 from per_all_assignments_f asg,
3396 pay_element_links_f el
3397 where asg.assignment_id = ee.assignment_id
3398 and el.element_link_id = ee.element_link_id
3399 and asg.assignment_type = 'E'
3400 and asg.effective_start_date <= ee.effective_end_date
3401 and asg.effective_end_date >= ee.effective_end_date -- bug 6485636
3402 and el.effective_start_date <= ee.effective_end_date
3403 and el.effective_end_date >= ee.effective_start_date
3404 and el.effective_start_date <= asg.effective_end_date
3405 and el.effective_end_date >= asg.effective_start_date
3406 --
3407 -- and the link does NOT match the assignment criteria
3408 --
3409 and ((el.payroll_id is not null
3410 and el.payroll_id = asg.payroll_id)
3411 or (el.link_to_all_payrolls_flag = 'Y'
3412 and asg.payroll_id is not null)
3413 or (el.payroll_id is null and
3414 el.link_to_all_payrolls_flag = 'N'))
3415 and (el.job_id is null
3416 or el.job_id = asg.job_id)
3417 and (el.grade_id is null
3418 or el.grade_id = asg.grade_id)
3419 and (el.position_id is null
3420 or el.position_id = asg.position_id)
3421 and (el.organization_id is null
3422 or el.organization_id = asg.organization_id)
3423 and (el.location_id is null
3424 or el.location_id = asg.location_id)
3425 and (el.pay_basis_id is null
3426 or el.pay_basis_id = asg.pay_basis_id)
3427 and (el.employment_category is null
3428 or el.employment_category = asg.employment_category)
3429 and (el.people_group_id is null
3430 or exists (select null
3431 from pay_assignment_link_usages_f alu
3432 where alu.assignment_id = ee.assignment_id
3433 and alu.element_link_id = ee.element_link_id
3434 and alu.effective_start_date <=
3435 asg.effective_end_date
3436 and alu.effective_end_date >=
3437 asg.effective_start_date)));
3438 --
3439 -- Local types
3440 --
3441 type t_element_entry_table_rec is record (
3442 element_entry_id dbms_sql.number_table,
3443 element_link_id dbms_sql.number_table,
3444 effective_start_date dbms_sql.date_table,
3445 effective_end_date dbms_sql.date_table
3446 );
3447 --
3448 -- Local variables
3449 --
3450 l_counter number := 1;
3451 l_entry_table t_element_entry_table_rec;
3452 l_asg_criteria_cv t_asg_criteria_cur; -- cursor variable for assignment criteria
3453 l_asg_criteria_rec t_asg_criteria_rec;
3454 l_eligible_links_cv t_eligible_links_cur; -- cursor variable for eligible links for invalidated entries
3455 l_eligible_links_rec t_eligible_links_rec;
3456 l_link_suitable boolean;
3457 l_creator_type_meaning varchar2(60);
3458 l_adjust_ee_source varchar2(1);
3459 l_proc varchar2(80) := 'hrentmnt.val_nonrec_entries';
3460 --
3461 l_obj_ver_num number;
3462 l_eff_str_date date;
3463 l_eff_end_date date;
3464 l_del_war boolean;
3465 begin
3466 --
3467 if g_debug then
3468 hr_utility.set_location(l_proc, 10);
3469 hr_utility.trace(' p_assignment_id = ' ||to_char(p_assignment_id));
3470 hr_utility.trace(' p_validation_start_date = ' ||to_char(p_validation_start_date));
3471 hr_utility.trace(' p_validation_end_date = ' ||to_char(p_validation_end_date));
3472 end if;
3473 --
3474 -- Get the assignment criteria
3475 open_asg_criteria_cur(p_assignment_id, p_validation_start_date, l_asg_criteria_cv);
3476 fetch l_asg_criteria_cv into l_asg_criteria_rec;
3477 close l_asg_criteria_cv;
3478 --
3479 -- Get the legisative rule ADJUSTMENT_EE_SOURCE.
3480 --
3481 begin
3482 --
3483 if g_debug then
3484 hr_utility.set_location(l_proc, 20);
3485 end if;
3486 --
3487 select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/ plr.rule_mode
3488 into l_adjust_ee_source
3489 from pay_legislation_rules plr,
3490 per_business_groups_perf pbg,
3491 per_all_assignments_f paf
3492 where paf.assignment_id = p_assignment_id
3493 and p_validation_start_date between paf.effective_start_date
3494 and paf.effective_end_date
3495 and paf.business_group_id = pbg.business_group_id
3496 and pbg.legislation_code = plr.legislation_code
3497 and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
3498 --
3499 exception
3500 when no_data_found then
3501 l_adjust_ee_source := 'A';
3502 end;
3503 --
3504 if g_debug then
3505 hr_utility.trace(' l_adjust_ee_source: '||l_adjust_ee_source);
3506 end if;
3507 --
3508 -- Bugfix 5584631
3509 -- Loop through all invalidated nonrecurring entries.
3510 -- Look to see if there exists an alternative link for each invalidated entry
3511 -- Raise an error if no alternative link exists for ANY of the invalidated
3512 -- entries.
3513 -- If ALL invalidated nonrecurring entries have an alternative link available
3514 -- then DO NOT raise an error but update the element_link_id stamped on the
3515 -- invalidated entries to point to the new links.
3516 --
3517 for r_entry in csr_entry (
3518 p_assignment_id,
3519 p_validation_start_date,
3520 p_validation_end_date,
3521 l_adjust_ee_source
3522 ) loop
3523 --
3524 if g_debug then
3525 hr_utility.set_location(l_proc, 30);
3526 end if;
3527 --
3528 -- Look for an alternative element link based on the invalidated entry's
3529 -- element type and the assignment criteria
3530 open_eligible_links_cur (
3531 p_assignment_id,
3532 -- Bugfix 6809717 added greatest , least to the following two lines
3533 greatest(r_entry.effective_start_date,p_validation_start_date), -- link must span lifetime of entry
3534 least(r_entry.effective_end_date,p_validation_end_date),
3535 r_entry.element_type_id,
3536 l_asg_criteria_rec.organization_id,
3537 l_asg_criteria_rec.people_group_id,
3538 l_asg_criteria_rec.job_id,
3539 l_asg_criteria_rec.position_id,
3540 l_asg_criteria_rec.grade_id,
3541 l_asg_criteria_rec.location_id,
3542 l_asg_criteria_rec.employment_category,
3543 l_asg_criteria_rec.payroll_id,
3544 l_asg_criteria_rec.pay_basis_id,
3545 l_asg_criteria_rec.business_group_id,
3546 l_eligible_links_cv
3547 );
3548 --
3549 fetch l_eligible_links_cv into l_eligible_links_rec;
3550 --
3551 -- Determine if a suitable alternative link has been found
3552 -- i.e. link must span lifetime of entry
3553 --
3554 l_link_suitable :=
3555 l_eligible_links_cv%found and
3556 l_eligible_links_rec.effective_start_date <= r_entry.effective_start_date and
3557 l_eligible_links_rec.effective_end_date >= r_entry.effective_end_date;
3558 --
3559 close l_eligible_links_cv;
3560 --
3561 if not l_link_suitable then
3562 --
3563 if g_debug then
3564 hr_utility.set_location(l_proc, 40);
3565 end if;
3566 --
3567 -- No alternative link found.
3568 -- Clear down l_entry_table and raise error.
3569 --
3570 l_entry_table.element_entry_id.delete;
3571 l_entry_table.element_link_id.delete;
3572 l_entry_table.effective_start_date.delete;
3573 l_entry_table.effective_end_date.delete;
3574 --
3575 if r_entry.creator_type not in ('F','H') then
3576 --
3577 -- Special entry was invalidated eg. Balance Adjustment, Quickpay etc .
3578 -- Lookup the entry type for reporting in error.
3579 --
3580 if g_debug then
3581 hr_utility.set_location(l_proc, 50);
3582 end if;
3583 --
3584 select hl.meaning
3585 into l_creator_type_meaning
3586 from hr_lookups hl
3587 where hl.lookup_type = 'CREATOR_TYPE'
3588 and hl.lookup_code = r_entry.creator_type;
3589 --
3590 hr_utility.set_message(801,'HR_6589_ASS_SPCL_NONREC_EXIST');
3591 hr_utility.set_message_token('TYPE',l_creator_type_meaning);
3592 hr_utility.raise_error;
3593 --
3594 else
3595 --
3596 -- An unprocessed nonrecurring entry was invalidated. Includes
3597 -- overrides etc .
3598 --
3599 if g_debug then
3600 hr_utility.set_location(l_proc, 60);
3601 end if;
3602 --Bug 6809717 Added the following code to end date entries
3603 select OBJECT_VERSION_NUMBER into l_obj_ver_num
3604 from pay_element_entries_f
3605 where p_validation_start_date-1 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
3606 and element_entry_id=r_entry.element_entry_id;
3607
3608 if g_debug then
3609 hr_utility.trace(' obj vber no'||l_obj_ver_num);
3610 end if;
3611 pay_element_entry_api.delete_element_entry
3612 (p_datetrack_delete_mode => 'DELETE'
3613 ,p_effective_date => p_validation_start_date-1
3614 ,p_element_entry_id => r_entry.element_entry_id
3615 ,p_object_version_number => l_obj_ver_num
3616 ,p_effective_start_date =>l_eff_str_date
3617 ,p_effective_end_date =>l_eff_end_date
3618 ,p_delete_warning =>l_del_war);
3619 -- hr_utility.set_message(801,'HR_6588_ASS_UNPROC_NONREC');
3620 -- hr_utility.raise_error;
3621 --
3622 end if;
3623 --
3624 else
3625 --
3626 -- A suitable alternative link was found
3627 -- Store the new link id with the entry details for a bulk update later
3628 --
3629 if g_debug then
3630 hr_utility.set_location(l_proc, 70);
3631 hr_utility.trace(' Alt element_link_id found for entry '||to_char(r_entry.element_entry_id));
3632 hr_utility.trace(' Old element_link_id: '||to_char(r_entry.element_link_id)||', new element_link_id: '||to_char(l_eligible_links_rec.element_link_id));
3633 end if;
3634 --
3635 l_entry_table.element_entry_id(l_counter) := r_entry.element_entry_id;
3636 l_entry_table.element_link_id(l_counter) := l_eligible_links_rec.element_link_id;
3637 l_entry_table.effective_start_date(l_counter) := r_entry.effective_start_date;
3638 l_entry_table.effective_end_date(l_counter) := r_entry.effective_end_date;
3639 --
3640 l_counter := l_counter + 1;
3641 --
3642 end if;
3643 --
3644 end loop;
3645 --
3646 if l_entry_table.element_entry_id.count > 0 then
3647 --
3648 if g_debug then
3649 hr_utility.set_location(l_proc, 80);
3650 hr_utility.trace('Doing bulk update of element_link_id');
3651 end if;
3652 --
3653 -- Do bulk update of element entries, to point to their respective new
3654 -- alternative links
3655 --
3656 forall i in 1 .. l_entry_table.element_entry_id.count
3657 update pay_element_entries_f
3658 set element_link_id = l_entry_table.element_link_id(i)
3659 where element_entry_id = l_entry_table.element_entry_id(i)
3660 and effective_start_date = l_entry_table.effective_start_date(i)
3661 and effective_end_date = l_entry_table.effective_end_date(i);
3662 --
3663 end if;
3664 --
3665 if g_debug then
3666 hr_utility.set_location(l_proc, 90);
3667 end if;
3668 --
3669 end val_nonrec_entries;
3670 --
3671 ------------------------------------------------------------------------------
3672 -- NAME --
3673 -- hrentmnt.adjust_nonrecurring_entries --
3674 -- --
3675 -- DESCRIPTION --
3676 -- Adjusts nonrecurring entries when there is a change in payroll or the --
3677 -- assignment ends. Nonrecurring entries now only exist for the duration of --
3678 -- the period for which the payroll / assignment exists. --
3679 ------------------------------------------------------------------------------
3680 --
3681 procedure adjust_nonrecurring_entries
3682 (
3683 p_assignment_id number,
3684 p_val_start_date_minus_one date,
3685 p_val_end_date_plus_one date,
3686 p_entries_changed in out nocopy varchar2,
3687 p_dt_mode VARCHAR2 DEFAULT NULL /* Added for Bug No : 6835808 */
3688 ) is
3689 --
3690 -- Finds all nonrecurring entries that overlap with the period of change of
3691 -- the assignment.
3692 --
3693 cursor csr_entry
3694 (
3695 p_assignment_id number,
3696 p_val_start_date_minus_one date,
3697 p_val_end_date_plus_one date
3698 ) is
3699 select ee.element_entry_id,
3700 ee.effective_start_date,
3701 ee.effective_end_date
3702 from pay_element_entries_f ee
3703 where ee.assignment_id = p_assignment_id
3704 and ee.effective_start_date <= p_val_end_date_plus_one
3705 and ee.effective_end_date >= p_val_start_date_minus_one
3706 and ((ee.entry_type <> 'E') or
3707 (ee.entry_type = 'E' and
3708 exists
3709 (select null
3710 from pay_element_links_f el,
3711 pay_element_types_f et
3712 where el.element_link_id = ee.element_link_id
3713 and et.element_type_id = el.element_type_id
3714 and et.processing_type = 'N')));
3715 --
3716 -- Local Variables
3717 --
3718 v_effective_start_date date;
3719 v_effective_end_date date;
3720 v_payroll_id number;
3721 v_period_start_date date;
3722 v_period_end_date date;
3723 v_session_date date;
3724 --Added for bug:6809717
3725 v_val_date date;
3726 v_alu_cnt number;
3727 v_vale_start_date date;
3728 v_start_date_check number;
3729 v_end_date_check number;
3730 v_chng_date date;
3731 procedure check_parameters is
3732 --
3733 begin
3734 --
3735 hr_utility.trace ('In hrentmnt.adjust_nonrecurring_entries');
3736 hr_utility.trace ('');
3737 hr_utility.trace (' p_assignment_id = '
3738 ||to_char (p_assignment_id));
3739 hr_utility.trace (' p_val_start_date_minus_one = '
3740 ||to_char (p_val_start_date_minus_one));
3741 hr_utility.trace (' p_val_end_date_plus_one = '
3742 ||to_char (p_val_end_date_plus_one));
3743 hr_utility.trace (' p_entries_changed = '
3744 ||p_entries_changed);
3745 hr_utility.trace ('');
3746 --
3747 end check_parameters;
3748 --
3749 begin
3750 --
3751 if g_debug then
3752 check_parameters;
3753 end if;
3754 --
3755 v_vale_start_date:=hr_general.start_of_time;
3756 -- Retrieve all nonrecurring entries that overlap the period of change
3757 /* Bug : 6809717 Added the following block of code to calculate the validation start date
3758 properly in case of people group change in assignment information*/
3759 --Start
3760 if p_dt_mode='DELETE_NEXT_CHANGE' then
3761 select count(*) into v_alu_cnt from pay_assignment_link_usages_f where
3762 assignment_id=p_assignment_id
3763 and effective_start_date>=p_val_start_date_minus_one
3764 and effective_end_date>=p_val_start_date_minus_one;
3765 if g_debug then
3766 hr_utility.trace ('1');
3767 end if;
3768 --
3769
3770 select count(*) into v_start_date_check from per_time_periods where payroll_id in(
3771 select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
3772 and p_val_start_date_minus_one between effective_start_date and effective_end_date)
3773 and p_val_start_date_minus_one between start_date and end_date;
3774
3775 select count(*) into v_end_date_check from per_time_periods where payroll_id in(
3776 select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
3777 and p_val_end_date_plus_one between effective_start_date and effective_end_date)
3778 and p_val_end_date_plus_one between start_date and end_date;
3779
3780 if g_debug then
3781 hr_utility.trace ('2');
3782 end if;
3783 --
3784 if v_start_date_check=0 and v_end_date_check=0 then
3785 v_chng_date:=p_val_start_date_minus_one;
3786 elsif v_start_date_check=0 then
3787 v_chng_date:=p_val_end_date_plus_one;
3788 else
3789 v_chng_date:=p_val_start_date_minus_one;
3790 end if;
3791 if g_debug then
3792 hr_utility.trace ('3');
3793 end if;
3794 --
3795 if v_start_date_check<>0 or v_end_date_check<>0 then
3796 select end_date into v_vale_start_date from per_time_periods where payroll_id in(
3797 select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
3798 and v_chng_date between effective_start_date and effective_end_date)
3799 and v_chng_date between start_date and end_date;
3800 end if;
3801
3802 if g_debug then
3803 hr_utility.trace ('4');
3804 end if;
3805 --
3806 if v_vale_start_date<>p_val_start_date_minus_one and v_alu_cnt=0 then
3807 v_vale_start_date:=p_val_start_date_minus_one;
3808 else
3809 v_vale_start_date:=p_val_start_date_minus_one+1;
3810 end if;
3811 else
3812 v_vale_start_date:=p_val_start_date_minus_one+1;
3813 end if;
3814 --End
3815 for v_entry in csr_entry(p_assignment_id,
3816 v_vale_start_date,
3817 p_val_end_date_plus_one) loop
3818
3819 -- Added 1 with p_val_start_date_minus_one to ensure that entries whichever
3820 -- created in past wasnt touched during this check.
3821
3822
3823 -- If nonrecurring entry existed before the period of change then
3824 -- calculate its new dates using the effective date of change otherwise use
3825 -- the end date of the entry NB. entries that are within the period of
3826 -- change will either be invalid or if they are link to all payrolls then
3827 -- their dates may have to be changed.
3828 --
3829
3830 -- Bug 6485636. Modified the logic of passing v_session_date, as it was creating
3831 -- erroneous entries for non-recurring element entries with their periods not
3832 -- matching the payroll periods and hence not being processed.
3833 -- Now, (p_val_start_date_minus_one +1) is passed as v_session_date when the date of
3834 -- payroll change lies in the period of the entry. As the date lies in
3835 -- the updated assignment period, the new payroll period will be used to update the
3836 -- non-recurring entry period. Hence, passing the session_date
3837 -- as (p_val_start_date_minus_one +1) will give correct dates from get_nonrecurring_dates().
3838
3839 /* Altered the following code for Bug No:6835808 which has previously has fix for Bug No:6722391 */
3840 if p_dt_mode='DELETE' then
3841 /* Code for termination of employee */
3842 if v_entry.effective_start_date <= p_val_start_date_minus_one then
3843 v_session_date := v_entry.effective_start_date;
3844 else
3845 v_session_date := v_entry.effective_end_date;
3846 end if;
3847 else
3848 /* Code for termination of payroll or Assignment */
3849 if v_entry.effective_start_date > p_val_start_date_minus_one then
3850 v_session_date := v_entry.effective_start_date;
3851 else
3852 v_session_date := p_val_start_date_minus_one+1;
3853 end if;
3854 end if;
3855
3856 --
3857 -- Calculate the start and end dates of the nonrecurring entry.
3858 --
3859 hr_entry.get_nonrecurring_dates
3860 (p_assignment_id,
3861 v_session_date,
3862 v_effective_start_date,
3863 v_effective_end_date,
3864 v_payroll_id,
3865 v_period_start_date,
3866 v_period_end_date);
3867 --
3868 -- If current start date is wrong then adjust nonrecurring entry.
3869 --
3870 if v_entry.effective_start_date <> v_effective_start_date then
3871 hrentmnt.validate_adjust_entry
3872 ('UPDATE',
3873 p_assignment_id,
3874 v_entry.element_entry_id,
3875 'START',
3876 v_entry.effective_start_date,
3877 v_effective_start_date,
3878 null,
3879 null,
3880 p_entries_changed);
3881 end if;
3882 --
3883 -- If current end date is wrong then adjust nonrecurring entry.
3884 --
3885 if v_entry.effective_end_date <> v_effective_end_date then
3886 hrentmnt.validate_adjust_entry
3887 ('UPDATE',
3888 p_assignment_id,
3889 v_entry.element_entry_id,
3890 'END',
3891 v_entry.effective_end_date,
3892 v_effective_end_date,
3893 null,
3894 null,
3895 p_entries_changed);
3896 end if;
3897 --
3898 end loop;
3899 --
3900 if g_debug then
3901 hr_utility.trace ('Out hrentmnt.adjust_nonrecurring_entries');
3902 end if;
3903 --
3904 end adjust_nonrecurring_entries;
3905 --
3906
3907 function get_entry_info_category(
3908 p_assignment_id in number,
3909 p_effective_date in date,
3910 p_element_link_id in number)
3911 return varchar2
3912 is
3913 cursor csr_entry_info_category(b_assignment_id in number,
3914 b_effective_date in date,
3915 b_element_link_id in number) is
3916 SELECT dfc.descriptive_flex_context_code
3917 FROM PER_BUSINESS_GROUPS_PERF bg,
3918 PAY_ELEMENT_LINKS_F el,
3919 PAY_ELEMENT_TYPES_F et,
3920 PAY_ELEMENT_CLASSIFICATIONS ec,
3921 FND_DESCR_FLEX_CONTEXTS dfc
3922 WHERE bg.business_group_id =
3923 el.business_group_id
3924 and el.element_link_id = b_element_link_id
3925 and b_effective_date between
3926 el.effective_start_date and el.effective_end_date
3927 and et.element_type_id =
3928 el.element_type_id
3929 and b_effective_date between
3930 et.effective_start_date and et.effective_end_date
3931 and ec.classification_id =
3932 et.classification_id
3933 and dfc.descriptive_flex_context_code = upper(
3934 bg.legislation_code || '_' || ec.classification_name)
3935 and dfc.application_id = 801
3936 and dfc.descriptive_flexfield_name = 'Element Entry Developer DF';
3937
3938 l_category fnd_descr_flex_contexts.descriptive_flex_context_code%type;
3939 begin
3940 null;
3941 open csr_entry_info_category(p_assignment_id, p_effective_date,
3942 p_element_link_id);
3943 fetch csr_entry_info_category into l_category;
3944 close csr_entry_info_category;
3945
3946 return l_category;
3947 end get_entry_info_category;
3948
3949 ------------------------------------------------------------------------------
3950 -- NAME --
3951 -- hrentmnt.adjust_recurring_entries --
3952 -- --
3953 -- DESCRIPTION --
3954 -- When passed a table containing what an element entry should look like --
3955 -- for a particular assignment and element link, it finds and adjusts all --
3956 -- current entries that represent the same element entry so that they are --
3957 -- consistent eg. --
3958 -- --
3959 -- Calc Entry |---------------------| |---------------> --
3960 -- Current Entry |--------------------------------------------------> --
3961 -- --
3962 -- The current entry would be split into 2 to look exactly like the --
3963 -- calculated entry.
3964 ------------------------------------------------------------------------------
3965 --
3966 procedure adjust_recurring_entries
3967 (
3968 p_dt_mode varchar2,
3969 p_assignment_id number,
3970 p_element_link_id number,
3971 p_standard_link_flag varchar2,
3972 p_mult_ent_allowed_flag varchar2,
3973 p_validation_start_date date,
3974 p_validation_end_date date,
3975 p_val_start_date_minus_one date,
3976 p_val_end_date_plus_one date,
3977 p_entry_count number,
3978 p_entry_start_date_tbl hrentmnt.t_date_table,
3979 p_entry_end_date_tbl hrentmnt.t_date_table,
3980 p_entries_changed in out nocopy varchar2,
3981 p_old_hire_date date
3982 ) is
3983 --
3984 cursor csr_distinct_entries
3985 (
3986 p_mult_ent_allowed_flag varchar2,
3987 p_assignment_id number,
3988 p_element_link_id number,
3989 p_entry_start_date date,
3990 p_entry_end_date date
3991 ) is
3992 select distinct nvl(ee.original_entry_id,ee.element_entry_id)
3993 from pay_element_entries_f ee
3994 where p_mult_ent_allowed_flag = 'Y'
3995 and ee.entry_type = 'E'
3996 and ee.assignment_id = p_assignment_id
3997 and ee.element_link_id = p_element_link_id
3998 and ee.effective_start_date <= p_entry_end_date
3999 and ee.effective_end_date >= p_entry_start_date
4000 UNION ALL
4001 select to_number(null)
4002 from sys.dual
4003 where p_mult_ent_allowed_flag = 'N'
4004 and exists
4005 (select null
4006 from pay_element_entries_f ee
4007 where ee.entry_type = 'E'
4008 and ee.assignment_id = p_assignment_id
4009 and ee.element_link_id = p_element_link_id
4010 and ee.effective_start_date <= p_entry_end_date
4011 and ee.effective_end_date >= p_entry_start_date);
4012 --
4013 -- Tuned in response to WWBug 273820. Splitting the SQL statement into 3
4014 -- distinct selects allows the use of indexes on all 3 selects. When the
4015 -- SQL was one select the combination of OR's and NVL's disabled all the
4016 -- available indexes.
4017 --
4018 cursor csr_entry
4019 (
4020 p_mult_ent_allowed_flag varchar2,
4021 p_element_entry_id number,
4022 p_assignment_id number,
4023 p_element_link_id number,
4024 p_entry_start_date date,
4025 p_entry_end_date date
4026 ) is
4027 select ee.element_entry_id,
4028 ee.original_entry_id,
4029 ee.effective_start_date,
4030 ee.effective_end_date,
4031 ee.element_link_id,
4032 ee.creator_type
4033 from pay_element_entries_f ee
4034 where ee.entry_type = 'E'
4035 and ee.effective_start_date <= p_entry_end_date
4036 and ee.effective_end_date >= p_entry_start_date
4037 and p_mult_ent_allowed_flag = 'Y'
4038 and ee.element_entry_id = p_element_entry_id
4039 and ee.original_entry_id is null
4040 UNION ALL
4041 select ee.element_entry_id,
4042 ee.original_entry_id,
4043 ee.effective_start_date,
4044 ee.effective_end_date,
4045 ee.element_link_id,
4046 ee.creator_type
4047 from pay_element_entries_f ee
4048 where ee.entry_type = 'E'
4049 and ee.effective_start_date <= p_entry_end_date
4050 and ee.effective_end_date >= p_entry_start_date
4051 and p_mult_ent_allowed_flag = 'Y'
4052 and ee.original_entry_id = p_element_entry_id
4053 UNION ALL
4054 select ee.element_entry_id,
4055 ee.original_entry_id,
4056 ee.effective_start_date,
4057 ee.effective_end_date,
4058 ee.element_link_id,
4059 ee.creator_type
4060 from pay_element_entries_f ee
4061 where ee.entry_type = 'E'
4062 and ee.effective_start_date <= p_entry_end_date
4063 and ee.effective_end_date >= p_entry_start_date
4064 and p_mult_ent_allowed_flag = 'N'
4065 and ee.assignment_id = p_assignment_id
4066 and ee.element_link_id = p_element_link_id
4067 order by 3;
4068 --
4069 v_entry csr_entry%rowtype;
4070 v_distinct_entry_id number;
4071 v_first_entry_adjusted boolean := false;
4072 v_dummy_date date;
4073 v_element_entry_id number;
4074 v_first_element_entry_id number;
4075 v_last_element_entry_id number;
4076 v_entry_start_date date;
4077 v_entry_end_date date;
4078 v_calc_entry_start_date date;
4079 v_calc_entry_end_date date;
4080 v_ele_entry_rec hrentmnt.t_ele_entry_rec;
4081 v_num_entry_values number := 0;
4082 v_input_value_id_tbl hr_entry.number_table;
4083 v_entry_value_tbl hr_entry.varchar2_table;
4084
4085 -- bugfix 1691062
4086 l_category fnd_descr_flex_contexts.descriptive_flex_context_code%type;
4087 --
4088 procedure check_parameters is
4089 --
4090 begin
4091 --
4092 hr_utility.trace('In hrentmnt.adjust_recurring_entries');
4093 hr_utility.trace ('');
4094 hr_utility.trace (' p_dt_mode = '
4095 ||p_dt_mode);
4096 hr_utility.trace (' p_assignment_id = '
4097 ||to_char (p_assignment_id));
4098 hr_utility.trace (' p_element_link_id = '
4099 ||to_char (p_element_link_id));
4100 hr_utility.trace (' p_standard_link_flag = '
4101 ||p_standard_link_flag);
4102 hr_utility.trace (' p_mult_ent_allowed_flag = '
4103 ||p_mult_ent_allowed_flag);
4104 hr_utility.trace (' p_validation_start_date = '
4105 ||to_char (p_validation_start_date));
4106 hr_utility.trace (' p_validation_end_date = '
4107 ||to_char (p_validation_end_date));
4108 hr_utility.trace (' p_val_start_date_minus_one = '
4109 ||to_char (p_val_start_date_minus_one));
4110 hr_utility.trace (' p_val_end_date_plus_one = '
4111 ||to_char (p_val_end_date_plus_one));
4112 --
4113 hr_utility.trace (' p_entry_count = '
4114 ||to_char (p_entry_count));
4115 hr_utility.trace (' p_entries_changed = '
4116 ||p_entries_changed);
4117 hr_utility.trace ('');
4118 --
4119 end check_parameters;
4120 --
4121 function standard_element (
4122 --
4123 -- Returns TRUE if the element link specified is a standard
4124 -- one.
4125 --
4126 p_element_link_id in number,
4127 p_effective_date in date)
4128 --
4129 return boolean is
4130 --
4131 cursor csr_standard_link is
4132 --
4133 select standard_link_flag
4134 from pay_element_links_f
4135 where element_link_id = p_element_link_id
4136 and p_effective_date between effective_start_date
4137 and effective_end_date;
4138 --
4139 l_standard varchar2 (30) := 'N';
4140 no_link_found exception;
4141 --
4142 begin
4143 --
4144 open csr_standard_link;
4145 fetch csr_standard_link into l_standard;
4146 --
4147 if csr_standard_link%notfound then
4148 close csr_standard_link;
4149 raise no_link_found;
4150 else
4151 close csr_standard_link;
4152 end if;
4153 --
4154 return (l_standard = 'Y');
4155 --
4156 EXCEPTION
4157 --
4158 when no_link_found then
4159 --
4160 fnd_message.set_name ('PAY','HR_6153_ALL_PROCEDURE_FAIL');
4161 fnd_message.set_token ('PROCEDURE',
4162 'hrentmnt.adjust_recurring_entries');
4163 fnd_message.set_token ('STEP','1');
4164 fnd_message.raise_error;
4165 --
4166 end standard_element;
4167 --
4168 begin
4169 --
4170 if g_debug then
4171 check_parameters;
4172 end if;
4173 --
4174 -- Find all distinct element entries that match the calculated element entry
4175 -- and overlap with the period of change NB. it is possible that multiple
4176 -- element entries exist and each must be processed separately ie.
4177 --
4178 -- period of change |--------------------------------|
4179 -- current entry 1 |--------------------|
4180 -- current entry 2 |--------------------|
4181 -- current entry 3 |--------------------|
4182 --
4183 open csr_distinct_entries(p_mult_ent_allowed_flag,
4184 p_assignment_id,
4185 p_element_link_id,
4186 p_val_start_date_minus_one,
4187 p_val_end_date_plus_one);
4188 --
4189 -- Get the first distinct current element entry.
4190 --
4191 fetch csr_distinct_entries into v_distinct_entry_id;
4192 --
4193 -- A current element entry matches the calculated element entry.
4194 --
4195 if csr_distinct_entries%found then
4196 --
4197 -- Continue for all distinct current element entries.
4198 --
4199 if g_debug then
4200 hr_utility.trace('Distinct entries found');
4201 end if;
4202 --
4203 while csr_distinct_entries%found loop
4204 --
4205 -- Loop around for each part of the calculated element entry NB. it is
4206 -- possible that a change can split an element entry which is then
4207 -- represented by 2 calculated element entries ie.
4208 --
4209 -- period of change |------------------|
4210 -- current entry |------------------------|
4211 -- calc entry |--| |--|
4212 --
4213 -- and find any current element entries that overlap with it.
4214 --
4215 for v_loop in 1..p_entry_count loop
4216 --
4217 -- Initialse the start and end dates of calculated element entry.
4218 --
4219 v_calc_entry_start_date := p_entry_start_date_tbl(v_loop);
4220 v_calc_entry_end_date := p_entry_end_date_tbl(v_loop);
4221 --
4222 if g_debug then
4223 hr_utility.trace('esd : ' || v_calc_entry_start_date);
4224 hr_utility.trace('eed : ' || v_calc_entry_end_date);
4225 end if;
4226 --
4227 -- Initialise the flag.
4228 --
4229 v_first_entry_adjusted := false;
4230 --
4231 -- Find current element entries that overlap with calculated element
4232 -- entry (for distinct element entry).
4233 --
4234 open csr_entry(p_mult_ent_allowed_flag,
4235 v_distinct_entry_id,
4236 p_assignment_id,
4237 p_element_link_id,
4238 v_calc_entry_start_date,
4239 v_calc_entry_end_date);
4240 --
4241 -- Get the first current element entry (for distinct element entry).
4242 --
4243 fetch csr_entry into v_entry;
4244 --
4245 -- Current element entry has been found (for distinct element entry).
4246 --
4247 if csr_entry%found then
4248 --
4249 -- Initialse the start and end dates of current element entry.
4250 --
4251 v_first_element_entry_id := v_entry.element_entry_id;
4252 v_last_element_entry_id := v_entry.element_entry_id;
4253 v_entry_start_date := v_entry.effective_start_date;
4254 v_entry_end_date := v_entry.effective_end_date;
4255 --
4256 -- Take a copy of the current element entry as this may be needed
4257 -- later.
4258 --
4259 hrentmnt.cache_element_entry
4260 (v_entry.element_entry_id,
4261 v_entry.effective_end_date,
4262 v_ele_entry_rec,
4263 v_num_entry_values,
4264 v_input_value_id_tbl,
4265 v_entry_value_tbl);
4266 --
4267 -- Continue for all current element entries that overlap with the
4268 -- calculated element entry (for current distinct element entry).
4269 --
4270 while csr_entry%found loop
4271 --
4272 -- Get next current element entry that overlaps with the
4273 -- calculated element entry (for distinct element entry).
4274 --
4275 fetch csr_entry into v_entry;
4276 --
4277 -- Either there are no more current element entries that overlap
4278 -- with the calculated element entry or the next element entry
4279 -- is not contiguous with the previous one ie.
4280 --
4281 -- calc entry |--------------------------------->
4282 -- current entry |---|----|---|-----| |----->
4283 -- 1 2 3 4
4284 --
4285 -- This can occur when the element entries have been previously
4286 -- split.
4287 --
4288 -- Now have the start and end dates of the calculated and
4289 -- current element entries which are then compared iand the
4290 -- current element entry is adjusted accordingly to be in line
4291 -- with the calculated element entry.
4292 --
4293 -- NB. although several element entries may exist only the first
4294 -- and last could possible be changed ie. 1 and 4.
4295 --
4296 if csr_entry%notfound or
4297 v_entry.effective_start_date <> v_entry_end_date + 1 then
4298 --
4299 -- Existing element entry starts before the calculated start
4300 -- date of new entry so need move the start date forwards.
4301 --
4302 -- calc entry |------------------------>
4303 -- current entry |---------------------------->
4304 --
4305 if v_entry_start_date < v_calc_entry_start_date and
4306 v_calc_entry_start_date > p_validation_start_date and
4307 v_calc_entry_start_date <= p_val_end_date_plus_one and
4308 not v_first_entry_adjusted then
4309
4310 if g_debug then
4311 hr_utility.trace ('***** bringing "start" date of current REE forwards *****');
4312 hr_utility.trace ('***** calc |-------------> *****');
4313 hr_utility.trace ('***** -----> *****');
4314 hr_utility.trace ('***** current |--------------------> *****');
4315 end if;
4316 --
4317 -- Validate the adjustment, maintain referential integrity,
4318 -- and adjust the element entry.
4319 --
4320 hrentmnt.validate_adjust_entry
4321 ('UPDATE',
4322 p_assignment_id,
4323 v_first_element_entry_id,
4324 'START',
4325 v_entry_start_date,
4326 v_calc_entry_start_date,
4327 null,
4328 null,
4329 p_entries_changed);
4330 --
4331 -- Flag that the EFFECTIVE_START_DATE of an element entry
4332 -- has been adjusted.
4333 --
4334 v_first_entry_adjusted := true;
4335 --
4336 -- Existing element entry starts after the calculated start date
4337 -- of new entry so need move the start date backwards. NB This
4338 -- should only be done for standard elements.
4339 --
4340 -- calc entry |------------------------>
4341 -- current entry |------------------->
4342 --
4343 --
4344 -- bugfix 725811
4345 -- if EEs have been created from the Salary Admin form,
4346 -- then it is valid to pull the EE's ESD
4347 -- backwards in line with the calculated ESD,
4348 -- only in the case where the old hire date is the
4349 -- same as the current entry start date (fix 1711753).
4350 -- nb. this breaks the rule that EEs associated
4351 -- with a non-standard element link should NOT have their
4352 -- ESD/EED altered programatically, however in the case of
4353 -- Salary Proposal EEs this is ok
4354 --
4355 elsif v_entry_start_date > v_calc_entry_start_date and
4356 v_calc_entry_start_date >= p_validation_start_date and
4357 v_calc_entry_start_date < p_val_end_date_plus_one and
4358 not v_first_entry_adjusted and
4359 (standard_element(v_entry.element_link_id,
4360 v_calc_entry_start_date)
4361 or
4362 (v_entry.creator_type = 'SP'
4363 and p_old_hire_date = v_entry_start_date)
4364 )
4365 then
4366
4367 if g_debug then
4368 hr_utility.trace ('***** bringing "start" date of current REE backwards *****');
4369 hr_utility.trace ('***** calc |--------------------> *****');
4370 hr_utility.trace ('***** <----- *****');
4371 hr_utility.trace ('***** current |-------------> *****');
4372 end if;
4373 --
4374 -- Validate the adjustment, maintain referential integrity,
4375 -- and adjust the element entry.
4376 --
4377 hrentmnt.validate_adjust_entry
4378 ('UPDATE',
4379 p_assignment_id,
4380 v_first_element_entry_id,
4381 'START',
4382 v_entry_start_date,
4383 v_calc_entry_start_date,
4384 null,
4385 null,
4386 p_entries_changed);
4387 --
4388 -- Flag that the EFFECTIVE_START_DATE of an element entry
4389 -- has been adjusted.
4390 --
4391 v_first_entry_adjusted := true;
4392 --
4393 end if;
4394 --
4395 -- Existing element entry ends after the calculated end date
4396 -- of new entry so need move the end date backwards.
4397 --
4398 -- calc entry |----------------|
4399 -- current entry |------------------------>
4400 --
4401 if v_entry_end_date > v_calc_entry_end_date then
4402
4403 if g_debug then
4404 hr_utility.trace ('***** bringing "end" date of current REE backwards *****');
4405 hr_utility.trace ('***** calc |-------------| *****');
4406 hr_utility.trace ('***** <----- *****');
4407 hr_utility.trace ('***** current |--------------------> *****');
4408 end if;
4409 hrentmnt.validate_adjust_entry
4410 ('UPDATE',
4411 p_assignment_id,
4412 v_last_element_entry_id,
4413 'END',
4414 v_entry_end_date,
4415 p_entry_end_date_tbl(v_loop),
4416 null,
4417 null,
4418 p_entries_changed);
4419 --
4420 -- Existing element entry ends before the calculated end date
4421 -- of new entry so need move the end date forwards. NB This
4422 -- should only be done for standard elements.
4423 -- Bug 514895. Enabled for non standard elements, this makes above sentence
4424 -- invalid.
4425 --
4426 -- calc entry |------------------------------->
4427 -- current entry |------------------|
4428 --
4429 -- NB. if the user is doing a CORRECTION then only extend the
4430 -- end date of the current element entry if it exists up to
4431 -- the day before the change ie. (WWBug 268814)
4432 --
4433 -- calc entry |--------------------------|
4434 -- current entry |-----------| (do not extend)
4435 -- current entry |--| (extend)
4436 --
4437 -- The same rule applies to DELETE_NEXT_CHANGE as this
4438 -- effectively corrects the next row using the values from the
4439 -- earlier row.
4440 --
4441 -- NB. if the user is doing an UPDATE then do not extend the
4442 -- end date of the current element entry. The updating of an
4443 -- assignment can only result in the shutting down of
4444 -- existing element entries or the creation of new ones
4445 -- ie. (WWBug 283275).
4446 --
4447 -- The same rule applies to UPDATE_CHANGE_INSERT.
4448 --
4449 -- calc entry |--------------------------|
4450 -- current entry |-----------| (do not extend)
4451 --
4452 -- Bug 514895. Removed condition for standard element. See comments above.
4453 --
4454
4455 --
4456 -- bug 911328,
4457 -- when in CORRECT mode only update REE that are SL'ed,
4458 -- ie. it possible that a non-SL'ed REE may end a
4459 -- day before the VSD,
4460 -- in this case its EED should be left alone
4461 --
4462 elsif v_entry_end_date < v_calc_entry_end_date and
4463 v_entry_end_date >= p_val_start_date_minus_one and
4464 p_dt_mode <> 'UPDATE' and
4465 p_dt_mode <> 'UPDATE_CHANGE_INSERT' and
4466 --
4467 -- boundary cases --
4468 (
4469 -- Bugfix 4221603
4470 -- In UPDATE_OVERRIDE mode, only move
4471 -- end date forwards when the entry is
4472 -- a standard entry.
4473 (p_dt_mode = 'UPDATE_OVERRIDE' and
4474 standard_element(v_entry.element_link_id,
4475 v_calc_entry_end_date) and
4476 -- Bugfix 4765204
4477 -- Only continue if entry ends within the
4478 -- 'range of change'.
4479 p_validation_start_date <= v_entry_end_date
4480 )
4481 or
4482 -- Or when the entry was created by a
4483 -- Salary Proposal
4484 (p_dt_mode = 'UPDATE_OVERRIDE' and
4485 v_entry.creator_type = 'SP' and
4486 -- Bugfix 4765204
4487 -- Only continue if entry ends within the
4488 -- 'range of change'.
4489 p_validation_start_date <= v_entry_end_date
4490 )
4491 or
4492 (p_dt_mode <> 'UPDATE_OVERRIDE')
4493 ) and
4494 (
4495 (p_dt_mode = 'DELETE_NEXT_CHANGE' and
4496 p_val_start_date_minus_one = v_entry_end_date and
4497 standard_element(v_entry.element_link_id,
4498 v_calc_entry_end_date)
4499 )
4500 or
4501 (p_dt_mode = 'DELETE_NEXT_CHANGE' and
4502 p_val_start_date_minus_one = v_entry_end_date and
4503 --Bug 6809717 Added F to the following code to adjust recurring
4504 --entries properly
4505 v_entry.creator_type in ('SP', 'UT','F') -- 2437795
4506 )
4507 or
4508 (p_dt_mode <> 'DELETE_NEXT_CHANGE')
4509 ) and
4510 -- Bugfix 4354757
4511 -- Only move end date forwards in FUTURE_CHANGE mode
4512 -- for standard entries, salary proposal entries and
4513 -- US tax entries (to be consistent with
4514 -- DELETE_NEXT_CHANGE mode).
4515 (
4516 (p_dt_mode = 'FUTURE_CHANGE' and
4517 p_val_start_date_minus_one = v_entry_end_date and
4518 standard_element(v_entry.element_link_id,
4519 v_calc_entry_end_date)
4520 )
4521 or
4522 (p_dt_mode = 'FUTURE_CHANGE' and
4523 p_val_start_date_minus_one = v_entry_end_date and
4524 v_entry.creator_type in ('SP', 'UT')
4525 )
4526 or
4527 (p_dt_mode <> 'FUTURE_CHANGE')
4528 ) and
4529 (
4530 (p_dt_mode = 'CORRECTION' and
4531 p_val_start_date_minus_one = v_entry_end_date and
4532 standard_element(v_entry.element_link_id,
4533 v_calc_entry_end_date)
4534 )
4535 or
4536 (p_dt_mode = 'CORRECTION' and
4537 p_val_start_date_minus_one = v_entry_end_date and
4538 v_entry.creator_type = 'SP'
4539 )
4540 or
4541 (p_dt_mode <> 'CORRECTION')
4542 ) then
4543
4544 if g_debug then
4545 hr_utility.trace ('***** bringing "end" date of current REE forwards *****');
4546 hr_utility.trace ('***** calc |--------------------> *****');
4547 hr_utility.trace ('***** -----> *****');
4548 hr_utility.trace ('***** current |--------------| *****');
4549 end if;
4550 --
4551 -- Another current entry exists in the future so only move the
4552 -- end date to the day before the this element entry.
4553 --
4554 if csr_entry%found then
4555 --
4556 -- Validate the adjustment, maintain referential integrity,
4557 -- and adjust the element entry.
4558 --
4559 hrentmnt.validate_adjust_entry
4560 ('UPDATE',
4561 p_assignment_id,
4562 v_last_element_entry_id,
4563 'END',
4564 v_entry_end_date,
4565 v_entry.effective_start_date - 1,
4566 null,
4567 null,
4568 p_entries_changed);
4569 --
4570 else
4571 --
4572 -- Validate the adjustment, maintain referential integrity,
4573 -- and adjust the element entry.
4574 --
4575 hrentmnt.validate_adjust_entry
4576 ('UPDATE',
4577 p_assignment_id,
4578 v_last_element_entry_id,
4579 'END',
4580 v_entry_end_date,
4581 v_calc_entry_end_date,
4582 null,
4583 null,
4584 p_entries_changed);
4585 --
4586 end if;
4587 --
4588 end if;
4589 --
4590 -- A new element entry has been found ie. not contiguous so hold
4591 -- information about this new element entry.
4592 --
4593 v_last_element_entry_id := v_entry.element_entry_id;
4594 v_entry_start_date := v_entry.effective_start_date;
4595 v_entry_end_date := v_entry.effective_end_date;
4596 --
4597 else
4598 --
4599 -- A contiguous current element entry has been found so update
4600 -- the current element entry end date.
4601 --
4602 v_last_element_entry_id := v_entry.element_entry_id;
4603 v_entry_end_date := v_entry.effective_end_date;
4604 --
4605 end if;
4606 --
4607 end loop;
4608 --
4609 -- Current element entry has been split and first part of new
4610 -- element entry has been created by adjusting the existing current
4611 -- element entry. Need to create a new element entry for the
4612 -- element entry on the other side of the split NB. this uses the
4613 -- cached values from the first element entry ie.
4614 --
4615 -- calc entry |----| |----|
4616 -- current entry |------------------------|
4617 -- new entry |----| (created by adjusting the current entry)
4618 -- new entry |----| (created from new)
4619 --
4620 --
4621 -- Create element entry.
4622 -- Do it only when its calculated start date is within a validation
4623 -- period (bug 398360).
4624 -- However, do not do so if the entry has a creator type
4625 -- of SP (salary proposal) - fix 2298468..
4626
4627 elsif (v_calc_entry_start_date >= p_validation_start_date and
4628 v_ele_entry_rec.creator_type <> 'SP') then
4629 --
4630 if g_debug then
4631 hr_utility.trace('Original_entry_id : ' || v_distinct_entry_id);
4632 end if;
4633 hr_entry_api.insert_element_entry
4634 (p_effective_start_date => v_calc_entry_start_date,
4635 p_effective_end_date => v_dummy_date,
4636 p_element_entry_id => v_element_entry_id,
4637 p_original_entry_id => v_distinct_entry_id,
4638 p_assignment_id => p_assignment_id,
4639 p_element_link_id => p_element_link_id,
4640 p_creator_type => v_ele_entry_rec.creator_type,
4641 p_entry_type => v_ele_entry_rec.entry_type,
4642 p_cost_allocation_keyflex_id
4643 => v_ele_entry_rec.cost_allocation_keyflex_id,
4644 p_comment_id => v_ele_entry_rec.comment_id,
4645 p_creator_id => v_ele_entry_rec.creator_id,
4646 p_reason => v_ele_entry_rec.reason,
4647 p_target_entry_id => v_ele_entry_rec.target_entry_id,
4648 p_subpriority => v_ele_entry_rec.subpriority,
4649 p_personal_payment_method_id
4650 => v_ele_entry_rec.personal_payment_method_id,
4651 p_date_earned => v_ele_entry_rec.date_earned,
4652 p_attribute_category => v_ele_entry_rec.attribute_category,
4653 p_attribute1 => v_ele_entry_rec.attribute1,
4654 p_attribute2 => v_ele_entry_rec.attribute2,
4655 p_attribute3 => v_ele_entry_rec.attribute3,
4656 p_attribute4 => v_ele_entry_rec.attribute4,
4657 p_attribute5 => v_ele_entry_rec.attribute5,
4658 p_attribute6 => v_ele_entry_rec.attribute6,
4659 p_attribute7 => v_ele_entry_rec.attribute7,
4660 p_attribute8 => v_ele_entry_rec.attribute8,
4661 p_attribute9 => v_ele_entry_rec.attribute9,
4662 p_attribute10 => v_ele_entry_rec.attribute10,
4663 p_attribute11 => v_ele_entry_rec.attribute11,
4664 p_attribute12 => v_ele_entry_rec.attribute12,
4665 p_attribute13 => v_ele_entry_rec.attribute13,
4666 p_attribute14 => v_ele_entry_rec.attribute14,
4667 p_attribute15 => v_ele_entry_rec.attribute15,
4668 p_attribute16 => v_ele_entry_rec.attribute16,
4669 p_attribute17 => v_ele_entry_rec.attribute17,
4670 p_attribute18 => v_ele_entry_rec.attribute18,
4671 p_attribute19 => v_ele_entry_rec.attribute19,
4672 p_attribute20 => v_ele_entry_rec.attribute20,
4673 p_entry_information_category
4674 => v_ele_entry_rec.entry_information_category,
4675 p_entry_information1 => v_ele_entry_rec.entry_information1,
4676 p_entry_information2 => v_ele_entry_rec.entry_information2,
4677 p_entry_information3 => v_ele_entry_rec.entry_information3,
4678 p_entry_information4 => v_ele_entry_rec.entry_information4,
4679 p_entry_information5 => v_ele_entry_rec.entry_information5,
4680 p_entry_information6 => v_ele_entry_rec.entry_information6,
4681 p_entry_information7 => v_ele_entry_rec.entry_information7,
4682 p_entry_information8 => v_ele_entry_rec.entry_information8,
4683 p_entry_information9 => v_ele_entry_rec.entry_information9,
4684 p_entry_information10 => v_ele_entry_rec.entry_information10,
4685 p_entry_information11 => v_ele_entry_rec.entry_information11,
4686 p_entry_information12 => v_ele_entry_rec.entry_information12,
4687 p_entry_information13 => v_ele_entry_rec.entry_information13,
4688 p_entry_information14 => v_ele_entry_rec.entry_information14,
4689 p_entry_information15 => v_ele_entry_rec.entry_information15,
4690 p_entry_information16 => v_ele_entry_rec.entry_information16,
4691 p_entry_information17 => v_ele_entry_rec.entry_information17,
4692 p_entry_information18 => v_ele_entry_rec.entry_information18,
4693 p_entry_information19 => v_ele_entry_rec.entry_information19,
4694 p_entry_information20 => v_ele_entry_rec.entry_information20,
4695 p_entry_information21 => v_ele_entry_rec.entry_information21,
4696 p_entry_information22 => v_ele_entry_rec.entry_information22,
4697 p_entry_information23 => v_ele_entry_rec.entry_information23,
4698 p_entry_information24 => v_ele_entry_rec.entry_information24,
4699 p_entry_information25 => v_ele_entry_rec.entry_information25,
4700 p_entry_information26 => v_ele_entry_rec.entry_information26,
4701 p_entry_information27 => v_ele_entry_rec.entry_information27,
4702 p_entry_information28 => v_ele_entry_rec.entry_information28,
4703 p_entry_information29 => v_ele_entry_rec.entry_information29,
4704 p_entry_information30 => v_ele_entry_rec.entry_information30,
4705 p_num_entry_values => v_num_entry_values,
4706 p_input_value_id_tbl => v_input_value_id_tbl,
4707 p_entry_value_tbl => v_entry_value_tbl);
4708
4709 -- ** remove ** following update when api supports
4710 -- the attributes that are being updated.
4711 update pay_element_entries_f pee
4712 set pee.balance_adj_cost_flag = v_ele_entry_rec.balance_adj_cost_flag,
4713 pee.source_asg_action_id = v_ele_entry_rec.source_asg_action_id,
4714 pee.source_link_id = v_ele_entry_rec.source_link_id,
4715 pee.source_trigger_entry = v_ele_entry_rec.source_trigger_entry,
4716 pee.source_period = v_ele_entry_rec.source_period,
4717 pee.source_run_type = v_ele_entry_rec.source_run_type,
4718 pee.source_start_date = v_ele_entry_rec.source_start_date,
4719 pee.source_end_date = v_ele_entry_rec.source_end_date
4720 where pee.element_entry_id = v_element_entry_id
4721 and v_calc_entry_start_date between
4722 pee.effective_start_date and pee.effective_end_date;
4723 --
4724 end if;
4725 --
4726 close csr_entry;
4727 --
4728 end loop;
4729 --
4730 -- Get the next distinct element entry.
4731 --
4732 fetch csr_distinct_entries into v_distinct_entry_id;
4733 --
4734 end loop;
4735 --
4736 -- No distinct element entries exist for the calculated entry so create
4737 -- new element entries.
4738 --
4739 else
4740 --
4741 -- for each calculated element entry.
4742 --
4743 for v_loop in 1..p_entry_count loop
4744 --
4745 -- Set the start date for each new element entry.
4746 --
4747 v_calc_entry_start_date := p_entry_start_date_tbl(v_loop);
4748 --
4749 -- Create new element entry.
4750 -- Do it only when its calculated start date is within a validation
4751 -- period (bug 398360).
4752 --
4753 if v_calc_entry_start_date >= p_validation_start_date then
4754 --
4755 if g_debug then
4756 hr_utility.trace('********** ASG criteria delta');
4757 hr_utility.trace('********** for SL call EE insert interface');
4758 end if;
4759 hr_entry_api.insert_element_entry
4760 (p_effective_start_date => v_calc_entry_start_date,
4761 p_effective_end_date => v_dummy_date,
4762 p_element_entry_id => v_element_entry_id,
4763 p_assignment_id => p_assignment_id,
4764 p_element_link_id => p_element_link_id,
4765 p_creator_type => 'F',
4766 p_entry_type => 'E');
4767 --
4768 -- bugfix 1691062,
4769 -- check if the element entry could potentially make use of the DDFF,
4770 -- ie. entry information category is not null,
4771 -- if so, then entry_information_category needs to be set on database
4772 --
4773 -- nb. this is only done for new entries created,
4774 -- not entries that are being maintained
4775 --
4776 l_category := get_entry_info_category(p_assignment_id,
4777 v_calc_entry_start_date,
4778 p_element_link_id);
4779 --
4780 -- cannot use insert interface as other DDFF column are not present
4781 -- and these may have validation set up
4782 --
4783 if l_category is not null then
4784 if g_debug then
4785 hr_utility.trace('********** ASG criteria delta');
4786 hr_utility.trace('********** l_category>' || l_category || '<');
4787 end if;
4788
4789 --
4790 -- only single element entry row exists, no need for effective dates
4791 --
4792 UPDATE pay_element_entries_f
4793 SET entry_information_category = l_category
4794 WHERE element_entry_id = v_element_entry_id;
4795 end if;
4796 --
4797 end if;
4798 --
4799 end loop;
4800 --
4801 end if;
4802 --
4803 close csr_distinct_entries;
4804 --
4805 if g_debug then
4806 hr_utility.trace('Out hrentmnt.adjust_recurring_entries');
4807 end if;
4808 --
4809 end adjust_recurring_entries;
4810 --
4811 ------------------------------------------------------------------------------
4812 -- NAME --
4813 -- hrentmnt.adjust_entries_pqc --
4814 -- --
4815 -- DESCRIPTION --
4816 -- Adjusts element entries when the personal qualifying conditions for the --
4817 -- assignment are changed ie. DOB or probation period NB. this is only --
4818 -- applied for standard element entries as the user is able to override --
4819 -- personal qualifying conditions when creating discretionary element --
4820 -- entries. --
4821 -- NOTES --
4822 -- Only existing standard element entries are adjusted due to a change in --
4823 -- personal qualifying conditions ie. no new standard element entries are --
4824 -- created during this process. --
4825 ------------------------------------------------------------------------------
4826 --
4827 procedure adjust_entries_pqc
4828 (
4829 p_assignment_id number,
4830 p_entries_changed in out nocopy varchar2
4831 ) is
4832 --
4833 -- Local Cursors
4834 --
4835 cursor csr_standard_entries is
4836 select ee.element_entry_id,
4837 ee.creator_type,
4838 ee.creator_id,
4839 ee.effective_start_date,
4840 ee.effective_end_date,
4841 ee.element_link_id
4842 from pay_element_entries_f ee
4843 where ee.assignment_id = p_assignment_id
4844 and ee.entry_type = 'E'
4845 and exists
4846 (select null
4847 from pay_element_links_f el,
4848 pay_element_types_f et
4849 where el.element_link_id = ee.element_link_id
4850 and el.standard_link_flag = 'Y'
4851 and et.element_type_id = el.element_type_id
4852 and et.processing_type = 'R')
4853 order by ee.element_link_id, ee.effective_start_date;
4854 --
4855 -- Local Variables
4856 --
4857 v_current_element_link_id number := -1;
4858 v_los_date date;
4859 v_age_date date;
4860 v_start_date_qc date;
4861 v_new_entry_start_date date;
4862 v_min_entry_start_date date;
4863 v_first_entry_adjusted boolean := false;
4864 --
4865 procedure check_parameters is
4866 --
4867 begin
4868 --
4869 hr_utility.trace('In hrentmnt.adjust_entries_pqc');
4870 hr_utility.trace ('');
4871 hr_utility.trace (' p_assignment_id = '
4872 ||to_char (p_assignment_id));
4873 hr_utility.trace (' p_entries_changed = '
4874 ||p_entries_changed);
4875 hr_utility.trace ('');
4876 --
4877 end check_parameters;
4878 --
4879 begin
4880 --
4881 if g_debug then
4882 check_parameters;
4883 end if;
4884 --
4885 -- Find all standard element entries for the assignment / element link NB.
4886 -- each date effective instance is returned in order for each element entry
4887 -- ie.
4888 --
4889 -- EE1 |-----------|-----------|----------|------------->
4890 --
4891 -- loop 1 |-----------|
4892 -- loop 2 |-----------|
4893 -- loop 3 |----------|
4894 -- loop 4 |------------->
4895 --
4896 -- "
4897 -- | QC start date
4898 --
4899 -- This is repeated for each element entry.
4900 --
4901 -- Each row is compared against the qualifying condition start date. Each
4902 -- row that exists completely before is removed, the first row that overlaps
4903 -- with the qualifying condition start date is adjusted accordingly and then
4904 -- all subsequent rows are left alone eg.
4905 --
4906 -- loop 1 row exists before start date so remove.
4907 -- loop 2 row exists before start date so remove.
4908 -- loop 3 row overlaps with QC start date so adjust.
4909 -- loop 4 leave row alone.
4910 --
4911 for v_entry in csr_standard_entries loop
4912 --
4913 -- Check to see if the qualifying condition start date has already been
4914 -- obtained for the element entry.
4915 --
4916 if v_current_element_link_id <> v_entry.element_link_id then
4917 --
4918 -- Get the start date of the element entry accordinbg to the qualifying
4919 -- conditions.
4920 --
4921 hr_entry.return_qualifying_conditions
4922 (p_assignment_id,
4923 v_entry.element_link_id,
4924 v_entry.effective_start_date,
4925 v_los_date,
4926 v_age_date);
4927 --
4928 -- Calculate the element entry start date according to the qualifying
4929 -- conditions. Changed due to WWBug 272990.
4930 --
4931 v_start_date_qc := least(nvl(v_los_date,v_age_date),
4932 nvl(v_age_date,v_los_date));
4933 v_first_entry_adjusted := false;
4934 v_current_element_link_id := v_entry.element_link_id;
4935 --
4936 end if;
4937 --
4938 -- Element entry start date is not the same as that according to the
4939 -- qualifying conditions.
4940 --
4941 if v_start_date_qc is not null and
4942 v_start_date_qc <> v_entry.effective_start_date then
4943 --
4944 -- Element entry ends before it is eligible according to the
4945 -- qualifying conditions ie.
4946 --
4947 -- EE |--------------------------|
4948 -- "
4949 -- | QC date
4950 --
4951 if v_start_date_qc > v_entry.effective_end_date then
4952 --
4953 -- Remove element entry that exists before qualifying conditions.
4954 --
4955 hrentmnt.validate_adjust_entry
4956 ('DELETE',
4957 p_assignment_id,
4958 v_entry.element_entry_id,
4959 null,
4960 null,
4961 null,
4962 v_entry.effective_start_date,
4963 v_entry.effective_end_date,
4964 p_entries_changed);
4965 --
4966 -- Remove element entry values.
4967 --
4968 delete from pay_element_entry_values_f eev
4969 where eev.element_entry_id = v_entry.element_entry_id
4970 and eev.effective_start_date = v_entry.effective_start_date
4971 and eev.effective_end_date = v_entry.effective_end_date;
4972 --
4973 -- Remove element entries.
4974 --
4975 delete from pay_element_entries_f ee
4976 where ee.element_entry_id = v_entry.element_entry_id
4977 and ee.effective_start_date = v_entry.effective_start_date
4978 and ee.effective_end_date = v_entry.effective_end_date;
4979 --
4980 -- Call the routine that checks whether an illegal purge
4981 -- has occurred (i.e. disallowed by profile).
4982 hrentmnt.validate_purge(v_entry.element_entry_id,
4983 v_entry.element_link_id);
4984 --
4985 -- Salary Admin entry is being removed. See if the pay proposal is
4986 -- used by any other entry. If not then it is removed.
4987 --
4988 if v_entry.creator_type = 'SP' then
4989 --
4990 hrentmnt.remove_pay_proposals
4991 (p_assignment_id,
4992 v_entry.creator_id);
4993 --
4994 end if;
4995 --
4996 -- Element entry start date does match the qualifying condition
4997 -- start date ie.
4998 --
4999 -- EE |----------|---------|----->
5000 -- "
5001 -- | QC date
5002 --
5003 -- or
5004 --
5005 -- EE |-------|--------|--------->
5006 -- "
5007 -- | QC date
5008 --
5009 -- NB. only adjust the first date effective instance of each
5010 -- element entry. Any date effective instances of the element entry
5011 -- that exist completely before the qualifying condition start date
5012 -- will have already been removed.
5013 --
5014 elsif not v_first_entry_adjusted then
5015 --
5016 -- The change in qualifying conditions has meant that the start date
5017 -- of the element entry has to be moved back ie.
5018 --
5019 -- EE |-----------|-----------|----------|------------->
5020 -- "
5021 -- | QC start date
5022 --
5023 -- Must make sure that the element entry is only moved back as far as
5024 -- it is eligible ie. the assignments criteria may be different
5025 -- earlier such that it is not eligible for the element entry ie.
5026 --
5027 -- ASG |--A--|--B--|--B--|-------------------B-------------------->
5028 --
5029 -- EL |-------------------------------B-------------------------->
5030 --
5031 -- EE |-----------|-----------|----------|------------->
5032 -- "
5033 -- | QC start date
5034 --
5035 -- New EE |---------------|----.........
5036 --
5037 -- ie. back to point where element entry ceased to be eligible.
5038 --
5039 if v_start_date_qc < v_entry.effective_start_date then
5040 --
5041 -- Get the earliest date the element entry could be moved back to
5042 -- ie. as far back as the eligibility allows.
5043 --
5044 v_min_entry_start_date := min_eligibility_date
5045 (v_current_element_link_id,
5046 p_assignment_id,
5047 v_start_date_qc,
5048 v_entry.effective_start_date);
5049 --
5050 -- The element entry start date must fall within the time where
5051 -- the element entry is eligible ie. if the minimum possible start
5052 -- date is aftere the start date according to the qualifying
5053 -- conditions then the minimum eligibility date must be used.
5054 --
5055 v_new_entry_start_date := greatest(v_min_entry_start_date,
5056 v_start_date_qc);
5057 --
5058 -- The change in qualifying conditions has meant that the start date
5059 -- of the element entry has to be moved forward. This change in
5060 -- element entry start date always falls within the date effective
5061 -- lifetime of an existing element entry which means the element
5062 -- entry will be eligible for the assignment and therefore no extra
5063 -- validation relating to eligibility is required ie.
5064 --
5065 -- EE |-----------|-----------|----------|------------->
5066 -- "
5067 -- | QC start date
5068 --
5069 else
5070 v_new_entry_start_date := v_start_date_qc;
5071 end if;
5072 --
5073 -- Adjust element entry to bring element entry start date into line
5074 -- with the qualifying condition start date NB. if the new date is
5075 -- restricted to the current date due to earlier eligibility changes
5076 -- to the assignment then the start date cannot be altered ie.
5077 --
5078 -- EL |-----------------------A------------------------------->
5079 --
5080 -- ASG |----A-----|--------B--------|------------A------------->
5081 --
5082 -- EE |-------------------------->
5083 -- ^
5084 -- | current pqc date
5085 -- ^
5086 -- | new pqc date
5087 --
5088 -- In this case the element entry cannot have its start date altered
5089 -- as this would result in it existing during a period of time when it
5090 -- was not eligible.
5091 --
5092 if v_new_entry_start_date <> v_entry.effective_start_date then
5093 hrentmnt.validate_adjust_entry
5094 ('UPDATE',
5095 p_assignment_id,
5096 v_entry.element_entry_id,
5097 'START',
5098 v_entry.effective_start_date,
5099 v_new_entry_start_date,
5100 null,
5101 null,
5102 p_entries_changed);
5103 end if;
5104 --
5105 -- Indicate that the element entry has had its start date assessed
5106 -- relative to the qualifying condition start date.
5107 --
5108 v_first_entry_adjusted := true;
5109 --
5110 end if;
5111 --
5112 end if;
5113 --
5114 end loop;
5115 --
5116 if g_debug then
5117 hr_utility.trace('Out hrentmnt.adjust_entries_pqc');
5118 end if;
5119 --
5120 end adjust_entries_pqc;
5121 --
5122 ------------------------------------------------------------------------------
5123 -- NAME --
5124 -- hrentmnt.adjust_entries_cncl_term --
5125 -- --
5126 -- DESCRIPTION --
5127 -- Adjusts element entries that have been closed down during a termination. --
5128 -- When the termination is cancelled all recurring element entries that --
5129 -- were closed down are opened up again NB. any nonrecurring entries that --
5130 -- removed cannot be recreated.
5131 ------------------------------------------------------------------------------
5132 --
5133 procedure adjust_entries_cncl_term
5134 (
5135 p_business_group_id number,
5136 p_assignment_id number,
5137 p_actual_term_date date,
5138 p_last_standard_date date,
5139 p_final_process_date date,
5140 p_entries_changed in out nocopy varchar2,
5141 p_dt_mode varchar2,
5142 p_old_people_group_id number,
5143 p_new_people_group_id number
5144 ) is
5145 --
5146 -- Find all recurring element entries that are ended according to their
5147 -- termination processing rule.
5148 --
5149 -- Bugfix 2249308:
5150 -- Ignore entries that were previously stopped by a formula result
5151 -- rule. We do not wish to extend entries of this type.
5152 --
5153 cursor csr_entry
5154 (
5155 p_assignment_id number,
5156 p_actual_term_date date,
5157 p_last_standard_date date,
5158 p_final_process_date date,
5159 p_sot date
5160 ) is
5161 select ee.element_entry_id,
5162 ee.effective_start_date,
5163 ee.effective_end_date,
5164 ee.element_link_id
5165 from pay_element_entries_f ee
5166 where ee.assignment_id = p_assignment_id
5167 and ee.entry_type = 'E'
5168 and nvl(ee.updating_action_type,'null') <> 'S' -- Bugfix 2249308
5169 and exists
5170 (select null
5171 from pay_element_links_f el,
5172 pay_element_types_f et
5173 where el.element_link_id = ee.element_link_id
5174 and el.element_type_id = et.element_type_id
5175 and et.processing_type = 'R')
5176 and ee.effective_end_date =
5177 (select decode(et.post_termination_rule,
5178 'A',nvl(p_actual_term_date,p_sot),
5179 'L',nvl(p_last_standard_date,p_sot),
5180 'F',nvl(p_final_process_date,p_sot),
5181 p_sot)
5182 from pay_element_links_f el,
5183 pay_element_types_f et
5184 where el.element_link_id = ee.element_link_id
5185 and et.element_type_id = el.element_type_id
5186 and ee.effective_start_date between el.effective_start_date
5187 and el.effective_end_date
5188 and ee.effective_start_date between et.effective_start_date
5189 and et.effective_end_date);
5190 --
5191 -- Local Constants
5192 --
5193 c_sot constant date := to_date('01/01/0001','DD/MM/YYYY');
5194 --
5195 -- Local Variables
5196 --
5197 v_entry_end_date date;
5198 v_entries_changed varchar2(1);
5199 --
5200 procedure check_parameters is
5201 --
5202 begin
5203 --
5204 hr_utility.trace('In hrentmnt.adjust_entries_cncl_term');
5205 hr_utility.trace ('');
5206 hr_utility.trace (' p_business_group_id = '
5207 ||to_char (p_business_group_id));
5208 hr_utility.trace (' p_assignment_id = '
5209 ||to_char (p_assignment_id));
5210 hr_utility.trace (' p_actual_term_date = '
5211 ||to_char (p_actual_term_date));
5212 hr_utility.trace (' p_last_standard_date = '
5213 ||to_char (p_last_standard_date));
5214 hr_utility.trace (' p_final_process_date = '
5215 ||to_char (p_final_process_date));
5216 hr_utility.trace (' p_entries_changed = '
5217 ||p_entries_changed);
5218 hr_utility.trace ('');
5219 --
5220 end check_parameters;
5221 --
5222 begin
5223 --
5224 if g_debug then
5225 check_parameters;
5226 end if;
5227 --
5228 -- Maintain assignment link usages for the assignment.
5229 --
5230 hrentmnt.maintain_alu_asg
5231 (p_assignment_id,
5232 p_business_group_id,
5233 p_dt_mode,
5234 p_old_people_group_id,
5235 p_new_people_group_id);
5236 --
5237 -- The final process date has been set for the termination so the assignment
5238 -- will have been date effectively ended.
5239 --
5240 if p_final_process_date is not null then
5241 --
5242 -- Open up any nonrecurring entries which had been closed down when the
5243 -- assignment was date effectively ended.
5244 --
5245 -- WWBUG 314279. Switched the ordering of c_sot and p_final_process_date
5246 -- so that they agree with the parameters.
5247 -- c_sot -> validation_start_date
5248 -- p_final_process_date -> validation_end_date
5249 -- rathers than the other way around
5250 --
5251 hrentmnt.adjust_nonrecurring_entries
5252 (p_assignment_id,
5253 c_sot,
5254 p_final_process_date,
5255 v_entries_changed,
5256 p_dt_mode);
5257 --
5258 end if;
5259 --
5260 -- Loop for all recurring entries which have been closed down during the
5261 -- employees termination.
5262 --
5263 for v_entry in csr_entry(p_assignment_id,
5264 p_actual_term_date,
5265 p_last_standard_date,
5266 p_final_process_date,
5267 c_sot) loop
5268 --
5269 -- Get the true end date of the recurring entry.
5270 --
5271 v_entry_end_date := hr_entry.recurring_entry_end_date
5272 (p_assignment_id,
5273 v_entry.element_link_id,
5274 v_entry.effective_start_date,
5275 'N',
5276 'N',
5277 null,
5278 null);
5279 --
5280 -- The recurring entry end date is less than the true end date of the
5281 -- element entry.
5282 --
5283 if v_entry.effective_end_date < v_entry_end_date then
5284 --
5285 -- Extend the recurring entry end date to the correct date.
5286 --
5287 hrentmnt.validate_adjust_entry
5288 ('UPDATE',
5289 p_assignment_id,
5290 v_entry.element_entry_id,
5291 'END',
5292 v_entry.effective_end_date,
5293 v_entry_end_date,
5294 null,
5295 null,
5296 p_entries_changed);
5297 --
5298 end if;
5299 --
5300 end loop;
5301 --
5302 if g_debug then
5303 hr_utility.trace('Out hrentmnt.adjust_entries_cncl_term');
5304 end if;
5305 --
5306 end adjust_entries_cncl_term;
5307 --
5308 ------------------------------------------------------------------------------
5309 -- NAME --
5310 -- hrentmnt.adjust_entries_cncl_hire --
5311 -- --
5312 -- DESCRIPTION --
5313 -- Adjusts element entries that are no longer valid when the hiring of a --
5314 -- person to an assignment is cancelled. The assignment ceases to exist as --
5315 -- an employee assignment so it is no longer valid to have element entries. --
5316 ------------------------------------------------------------------------------
5317 --
5318 procedure adjust_entries_cncl_hire
5319 (
5320 p_business_group_id number,
5321 p_assignment_id number,
5322 p_validation_start_date date,
5323 p_validation_end_date date,
5324 p_entries_changed in out nocopy varchar2,
5325 p_dt_mode varchar2,
5326 p_old_people_group_id number,
5327 p_new_people_group_id number
5328 ) is
5329 procedure check_parameters is
5330 --
5331 begin
5332 --
5333 hr_utility.trace('In hrentmnt.adjust_entries_cncl_hire');
5334 hr_utility.trace ('');
5335 hr_utility.trace (' p_business_group_id = '
5336 ||to_char (p_business_group_id));
5337 hr_utility.trace (' p_assignment_id = '
5338 ||to_char (p_assignment_id));
5339 hr_utility.trace (' p_validation_start_date = '
5340 ||to_char (p_validation_start_date));
5341 hr_utility.trace (' p_validation_end_date = '
5342 ||to_char (p_validation_end_date));
5343 hr_utility.trace (' p_entries_changed = '
5344 ||p_entries_changed);
5345 hr_utility.trace ('');
5346 --
5347 end check_parameters;
5348 --
5349 begin
5350 --
5351 if g_debug then
5352 check_parameters;
5353 end if;
5354 --
5355 -- Maintain assignment link usages for the assignment.
5356 --
5357 hrentmnt.maintain_alu_asg
5358 (p_assignment_id,
5359 p_business_group_id,
5360 p_dt_mode,
5361 p_old_people_group_id,
5362 p_new_people_group_id
5363 );
5364 --
5365 -- Make sure there are no nonrecurring entries that have been made
5366 -- invalid by a change in assignment criteria.
5367 --
5368 hrentmnt.val_nonrec_entries
5369 (p_assignment_id,
5370 p_validation_start_date,
5371 p_validation_end_date);
5372 --
5373 -- Remove any recurring entries which are no longer valid.
5374 --
5375 /*
5376 hrentmnt.remove_ineligible_recurring
5377 (p_assignment_id,
5378 p_entries_changed);
5379 Added the p_dt_mode parameter to the following call to enable purging of
5380 entries if cancel hire is being done*/
5381 hrentmnt.remove_ineligible_recurring
5382 (p_assignment_id,
5383 p_entries_changed,
5384 p_validation_start_date,
5385 p_validation_end_date,
5386 p_dt_mode);
5387 --
5388 -- Remove any nonrecurring entries which are no longer valid.
5389 --
5390 hrentmnt.remove_ineligible_nonrecurring
5391 (p_assignment_id,
5392 p_validation_start_date,
5393 p_validation_end_date,
5394 p_entries_changed);
5395 --
5396 if g_debug then
5397 hr_utility.trace('Out hrentmnt.adjust_entries_cncl_hire');
5398 end if;
5399 --
5400 end adjust_entries_cncl_hire;
5401 --
5402 ------------------------------------------------------------------------------
5403 -- NAME --
5404 -- hrentmnt.adjust_entries_asg_criteria --
5405 -- --
5406 -- DESCRIPTION --
5407 -- Adjusts element entries that are affected by changes in assignment --
5408 -- criteria. --
5409 ------------------------------------------------------------------------------
5410 --
5411 procedure adjust_entries_asg_criteria
5412 (
5413 p_business_group_id number,
5414 p_assignment_id number,
5415 p_dt_mode varchar2,
5416 p_old_payroll_id number,
5417 p_new_payroll_id number,
5418 p_validation_start_date date,
5419 p_validation_end_date date,
5420 p_entries_changed in out nocopy varchar2,
5421 p_old_hire_date date,
5422 p_old_people_group_id number,
5423 p_new_people_group_id number
5424 ) is
5425 --
5426 type t_asg_rec is record
5427 (effective_start_date date,
5428 effective_end_date date);
5429 --
5430 cursor csr_link
5431 --
5432 -- Finds all standard links and also any links for which the assignment has
5433 -- entries NB. only those links that exist during the time over which the
5434 -- assignment has been changed are returned.
5435 -- also see bugs 2167881 and 2610904.
5436 --
5437 (
5438 p_business_group_id number,
5439 p_assignment_id number,
5440 p_validation_start_date date,
5441 p_validation_end_date date,
5442 p_val_start_date_minus_one date,
5443 p_val_end_date_plus_one date
5444 ) is
5445 select el.element_link_id,
5446 min(el.effective_start_date) effective_start_date,
5447 max(el.effective_end_date) effective_end_date,
5448 el.link_to_all_payrolls_flag,
5449 el.payroll_id,
5450 el.job_id,
5451 el.grade_id,
5452 el.position_id,
5453 el.organization_id,
5454 el.location_id,
5455 el.pay_basis_id,
5456 el.employment_category,
5457 el.people_group_id,
5458 el.element_type_id,
5459 el.standard_link_flag
5460 from pay_element_links_f el
5461 where el.business_group_id = p_business_group_id
5462 and ((el.standard_link_flag = 'Y' and
5463 exists
5464 (select null
5465 from pay_element_links_f el2
5466 where el2.element_link_id = el.element_link_id
5467 and el.effective_start_date <= p_validation_end_date
5468 and el.effective_end_date >= p_validation_start_date))
5469 or (el.standard_link_flag = 'N' and
5470 exists
5471 (
5472 -- change 115.40
5473 --select null
5474 select /*+ index(ee pay_element_entries_f_n51) */ null
5475 from pay_element_entries_f ee,
5476 pay_element_types_f et
5477 where ee.element_type_id = et.element_type_id
5478 and et.processing_type = 'R'
5479 and ee.entry_type = 'E'
5480 and ee.assignment_id = p_assignment_id
5481 and ee.element_link_id = el.element_link_id
5482 and ee.effective_start_date <= p_val_end_date_plus_one
5483 and ee.effective_end_date >= p_val_start_date_minus_one)
5484 -- start of change 115.16 --
5485 -- Ensure this non-standard link has not been changed from/to a standard link
5486 and NOT EXISTS
5487 (SELECT null
5488 FROM PAY_ELEMENT_LINKS_F el_sub
5489 WHERE el_sub.element_link_id = el.element_link_id
5490 and el_sub.standard_link_flag = 'Y'
5491 )
5492 -- end of change 115.16 --
5493 ))
5494 -- Change 115.60
5495 -- Bugfix 2121907
5496 -- Ensure no entries exist for this element link and assignment
5497 and not exists (
5498 select null
5499 from pay_element_entries_f ee
5500 where ee.assignment_id = p_assignment_id
5501 and ee.element_link_id = el.element_link_id
5502 and ee.effective_start_date <= p_validation_end_date
5503 and ee.effective_end_date >= p_validation_start_date
5504 and p_dt_mode in
5505 ('UPDATE','CORRECTION','UPDATE_CHANGE_INSERT'))
5506 -- End of change 115.60
5507 group by el.element_link_id,
5508 el.link_to_all_payrolls_flag,
5509 el.payroll_id,
5510 el.job_id,
5511 el.grade_id,
5512 el.position_id,
5513 el.organization_id,
5514 el.location_id,
5515 el.pay_basis_id,
5516 el.employment_category,
5517 el.people_group_id,
5518 el.element_type_id,
5519 el.standard_link_flag
5520 ;
5521 --
5522 -- Finds all assignment pieces that match the element link eg.
5523 --
5524 -- EL |-----------------------------A------------------------------>
5525 --
5526 -- ASG |---A----|----A----|-----B-----|-----A------|-----A----->
5527 --
5528 -- pieces |--------|---------| |------------|----------->
5529 --
5530 -- These pieces can then be assembled into possible element entries that
5531 -- need to be created ie.
5532 --
5533 -- EE |------------------|
5534 --
5535 -- EE |------------------------>
5536 --
5537 -- also see bugs 2167881 and 2610904.
5538 cursor csr_assignment
5539 (
5540 p_element_link_id number,
5541 p_link_start_date date,
5542 p_link_end_date date,
5543 p_assignment_id number,
5544 p_val_start_date_minus_one date,
5545 p_val_end_date_plus_one date,
5546 p_payroll_id number,
5547 p_link_to_all_payrolls_flag varchar2,
5548 p_job_id number,
5549 p_grade_id number,
5550 p_position_id number,
5551 p_organization_id number,
5552 p_location_id number,
5553 p_pay_basis_id number,
5554 p_employment_category varchar2,
5555 p_people_group_id number
5556 ) is
5557 select asg.effective_start_date,
5558 asg.effective_end_date
5559 from per_all_assignments_f asg
5560 where asg.assignment_id = p_assignment_id
5561 and asg.assignment_type = 'E'
5562 and asg.effective_start_date <= p_val_end_date_plus_one
5563 and asg.effective_end_date >= p_val_start_date_minus_one
5564 and asg.effective_start_date <= p_link_end_date
5565 and asg.effective_end_date >= p_link_start_date
5566 and ((p_payroll_id is not null and
5567 p_payroll_id = asg.payroll_id)
5568 or (p_link_to_all_payrolls_flag = 'Y' and
5569 asg.payroll_id is not null)
5570 or (p_link_to_all_payrolls_flag = 'N' and
5571 p_payroll_id is null))
5572 and (p_job_id is null or
5573 p_job_id = asg.job_id)
5574 and (p_grade_id is null or
5575 p_grade_id = asg.grade_id)
5576 and (p_position_id is null or
5577 p_position_id = asg.position_id)
5578 and (p_organization_id is null or
5579 p_organization_id = asg.organization_id)
5580 and (p_location_id is null or
5581 p_location_id = asg.location_id)
5582 and (
5583 --
5584 -- null passed down from EL,
5585 -- if EL is NOT associated with a pay basis then return true
5586 --
5587 p_pay_basis_id is null and
5588 NOT EXISTS
5589 (SELECT /*+ ORDERED INDEX(pb PER_PAY_BASES_N1)*/
5590 pb.pay_basis_id
5591 FROM PAY_ELEMENT_LINKS_F el,
5592 PAY_INPUT_VALUES_F iv,
5593 PER_PAY_BASES pb
5594 WHERE el.element_link_id = p_element_link_id
5595 and el.effective_start_date <= asg.effective_start_date
5596 and el.effective_end_date >= asg.effective_start_date
5597 and iv.element_type_id =
5598 el.element_type_id
5599 and iv.effective_start_date <= el.effective_start_date
5600 and iv.effective_end_date >= el.effective_start_date
5601 and pb.input_value_id =
5602 iv.input_value_id
5603 and pb.business_group_id = asg.business_group_id
5604 )
5605 or
5606 --
5607 -- null passed down from EL,
5608 -- if EL is associated with a pay basis then the associated PB_ID
5609 -- must match the PB_ID on ASG
5610 --
5611 p_pay_basis_id is null and
5612 EXISTS
5613 (SELECT pb.pay_basis_id
5614 FROM PER_PAY_BASES pb,
5615 PAY_INPUT_VALUES_F iv,
5616 PAY_ELEMENT_LINKS_F el
5617 WHERE el.element_link_id = p_element_link_id
5618 and el.effective_start_date <= asg.effective_start_date
5619 and el.effective_end_date >= asg.effective_start_date
5620 and iv.element_type_id =
5621 el.element_type_id
5622 and iv.effective_start_date <= el.effective_start_date
5623 and iv.effective_end_date >= el.effective_start_date
5624 and pb.input_value_id =
5625 iv.input_value_id
5626 and pb.pay_basis_id = asg.pay_basis_id
5627 )
5628 -- change 115.26
5629 or
5630 p_pay_basis_id is null and
5631 asg.pay_basis_id is null and
5632 EXISTS
5633 (SELECT /*+ ORDERED INDEX(pb PER_PAY_BASES_N1)*/
5634 pb.pay_basis_id
5635 FROM PAY_ELEMENT_LINKS_F el,
5636 PAY_INPUT_VALUES_F iv,
5637 PER_PAY_BASES pb
5638 WHERE el.element_link_id = p_element_link_id
5639 and el.effective_start_date <= asg.effective_start_date
5640 and el.effective_end_date >= asg.effective_start_date
5641 and iv.element_type_id =
5642 el.element_type_id
5643 and iv.effective_start_date <= el.effective_start_date
5644 and iv.effective_end_date >= el.effective_start_date
5645 and pb.input_value_id =
5646 iv.input_value_id
5647 and pb.business_group_id = asg.business_group_id
5648 )
5649 or
5650 p_pay_basis_id = asg.pay_basis_id
5651 )
5652 and (p_employment_category is null or
5653 p_employment_category = asg.employment_category)
5654 and (p_people_group_id is null or
5655 exists
5656 (select null
5657 from pay_assignment_link_usages_f alu
5658 where alu.assignment_id = p_assignment_id
5659 and alu.element_link_id = p_element_link_id
5660 and alu.effective_start_date <= asg.effective_end_date
5661 and alu.effective_end_date >= asg.effective_start_date))
5662 order by asg.effective_start_date
5663 for update;
5664 --
5665 -- Local Variables
5666 --
5667 v_mult_ent_allowed_flag varchar2(30);
5668 v_assignment t_asg_rec;
5669 v_asg_start_date date;
5670 v_asg_end_date date;
5671 v_entry_start_date date;
5672 v_entry_end_date date;
5673 v_val_start_date_minus_one date;
5674 v_val_end_date_plus_one date;
5675 v_entry_count number := 0;
5676 v_entry_start_date_tbl hrentmnt.t_date_table;
5677 v_entry_end_date_tbl hrentmnt.t_date_table;
5678 v_message_name varchar2(30);
5679 v_appl_short_name varchar2(30);
5680 l_proc constant varchar2 (72) := g_package||'adjust_entries_asg_criteria';
5681 --
5682 procedure check_parameters is
5683 --
5684 begin
5685 --
5686 hr_utility.trace('In '||l_proc);
5687 hr_utility.trace ('');
5688 hr_utility.trace (' p_business_group_id = '
5689 ||to_char (p_business_group_id));
5690 --
5691 hr_utility.trace (' p_assignment_id = '
5692 ||to_char (p_assignment_id));
5693 --
5694 hr_utility.trace (' p_dt_mode = '
5695 ||p_dt_mode);
5696 --
5697 hr_utility.trace (' p_old_payroll_id = '
5698 ||to_char (p_old_payroll_id));
5699 --
5700 hr_utility.trace (' p_new_payroll_id = '
5701 ||to_char (p_new_payroll_id));
5702 --
5703 hr_utility.trace (' p_validation_start_date = '
5704 ||to_char (p_validation_start_date));
5705 --
5706 hr_utility.trace (' p_validation_end_date = '
5707 ||to_char (p_validation_end_date));
5708 --
5709 hr_utility.trace (' p_entries_changed = '
5710 ||p_entries_changed);
5711 --
5712 hr_utility.trace (' p_old_hire_date = '
5713 ||to_char(p_old_hire_date));
5714 --
5715 hr_utility.trace (' p_old_people_group_id = '
5716 ||to_char(p_old_people_group_id));
5717 --
5718 hr_utility.trace (' p_new_people_group_id = '
5719 ||to_char(p_new_people_group_id));
5720 --
5721 hr_utility.trace ('');
5722 --
5723 end check_parameters;
5724 --
5725 begin
5726 --
5727 if g_debug then
5728 check_parameters;
5729 end if;
5730 --
5731 if p_validation_start_date = hr_general.start_of_time then
5732 v_val_start_date_minus_one := hr_general.start_of_time;
5733 else
5734 v_val_start_date_minus_one := p_validation_start_date -1;
5735 end if;
5736 --
5737 if p_validation_end_date = hr_general.end_of_time then
5738 v_val_end_date_plus_one := hr_general.end_of_time;
5739 else
5740 v_val_end_date_plus_one := p_validation_end_date +1;
5741 end if;
5742 --
5743 -- Maintain assignment link usages for the assignment.
5744 --
5745 hrentmnt.maintain_alu_asg (p_assignment_id,
5746 p_business_group_id,
5747 p_dt_mode,
5748 p_old_people_group_id,
5749 p_new_people_group_id);
5750 --
5751 -- Make sure there are no nonrecurring entries that have been made
5752 -- invalid by a change in assignment criteria.
5753 --
5754 hrentmnt.val_nonrec_entries ( p_assignment_id,
5755 p_validation_start_date,
5756 p_validation_end_date);
5757 --
5758 -- Remove any nonrecurring entries which are no longer valid.
5759 --
5760 hrentmnt.remove_ineligible_nonrecurring ( p_assignment_id,
5761 p_validation_start_date,
5762 p_validation_end_date,
5763 p_entries_changed);
5764 --
5765 -- Only adjust nonrecurring entries when there has been a change of payroll
5766 --
5767 if (p_old_payroll_id <> p_new_payroll_id
5768 and p_dt_mode in ( 'UPDATE',
5769 'UPDATE_CHANGE_INSERT',
5770 'UPDATE_OVERRIDE',
5771 'CORRECTION') )
5772 --
5773 or p_dt_mode in ( 'DELETE',
5774 'FUTURE_CHANGE',
5775 'DELETE_NEXT_CHANGE')
5776 then
5777 --
5778 -- Adjust any nonrecurring entries which have been affected by a change
5779 -- of payroll.
5780 --
5781 hrentmnt.adjust_nonrecurring_entries ( p_assignment_id,
5782 v_val_start_date_minus_one,
5783 v_val_end_date_plus_one,
5784 p_entries_changed,
5785 p_dt_mode);
5786 --
5787 end if;
5788 --
5789 -- Remove any recurring entries that are no longer eligible NB. this makes
5790 -- the adjustment of entries easier.
5791 --
5792 hrentmnt.remove_ineligible_recurring(p_assignment_id, p_entries_changed,p_validation_start_date,p_validation_end_date,p_dt_mode);
5793 --
5794 -- By this stage, all that should be left to do is to adjust the dates of
5795 -- any entries which cross the boundary of the criteria change and to insert
5796 -- any standard entries for which the assignment is eligible as a result of
5797 -- the change.
5798 --
5799 -- Find all links that are either standard links or links for which the
5800 -- assignment already has recurring entries.
5801 --
5802 for v_link in csr_link ( p_business_group_id,
5803 p_assignment_id,
5804 p_validation_start_date,
5805 p_validation_end_date,
5806 v_val_start_date_minus_one,
5807 v_val_end_date_plus_one)
5808 LOOP
5809 --
5810 -- No links match the assignment so stop processing.
5811 --
5812 if v_link.element_link_id is null then
5813 exit;
5814 end if;
5815 --
5816 open csr_assignment ( v_link.element_link_id,
5817 v_link.effective_start_date,
5818 v_link.effective_end_date,
5819 p_assignment_id,
5820 v_val_start_date_minus_one,
5821 v_val_end_date_plus_one,
5822 v_link.payroll_id,
5823 v_link.link_to_all_payrolls_flag,
5824 v_link.job_id,
5825 v_link.grade_id,
5826 v_link.position_id,
5827 v_link.organization_id,
5828 v_link.location_id,
5829 v_link.pay_basis_id,
5830 v_link.employment_category,
5831 v_link.people_group_id);
5832 --
5833 -- Get first assignment piece matching the standard link.
5834 --
5835 fetch csr_assignment into v_assignment;
5836 --
5837 -- Assignment matching standard link has been found.
5838 --
5839 if csr_assignment%found then
5840 --
5841 v_asg_start_date := v_assignment.effective_start_date;
5842 v_asg_end_date := v_assignment.effective_end_date;
5843 --
5844 -- Loop for all assignment pieces that match the standard link.
5845 --
5846 while csr_assignment%found loop
5847 --
5848 -- Get next piece of assignment.
5849 --
5850 fetch csr_assignment into v_assignment;
5851 --
5852 -- No more Assignment pieces exist or piece is is not contiguous with
5853 -- the previous piece of assignment.
5854 --
5855 if csr_assignment%notfound
5856 or v_assignment.effective_start_date <> v_asg_end_date + 1
5857 then
5858 --
5859 -- Calculate the element entry dates NB. need to check to see if the
5860 -- element entry cannot be created because the assignment has been
5861 -- terminated in the future and the end date according to the
5862 -- termination rule is before the calculated start date of the
5863 -- element entry. This should not stop other entries being created !
5864 --
5865 begin
5866 --
5867 hrentmnt.return_entry_dates (p_assignment_id,
5868 v_asg_start_date,
5869 v_link.element_link_id,
5870 v_link.effective_start_date,
5871 v_link.standard_link_flag,
5872 v_entry_start_date,
5873 v_entry_end_date);
5874 --
5875 exception
5876 --
5877 when hr_utility.hr_error then
5878 --
5879 hr_utility.get_message_details(v_message_name,v_appl_short_name);
5880 --
5881 if v_message_name = 'HR_6370_ELE_ENTRY_NO_TERM' then
5882 --
5883 v_entry_start_date := null;
5884 v_message_name := null;
5885 v_appl_short_name := null;
5886 --
5887 else
5888 --
5889 raise;
5890 --
5891 end if;
5892 --
5893 end;
5894 --
5895 -- Bug 2950302 : remove restriction on putting details of
5896 -- entry into array when assignment has been terminated,
5897 -- as originally introduced for bugs 425686 and 476600.
5898 --
5899 -- Put details of entry into array for subsequent processing.
5900 --
5901 v_entry_count := v_entry_count + 1;
5902 v_entry_start_date_tbl(v_entry_count) := v_entry_start_date;
5903 v_entry_end_date_tbl(v_entry_count) := v_entry_end_date;
5904 --
5905 --
5906 -- Reset the variables to the effective dates of the new assignment.
5907 --
5908 v_asg_start_date := v_assignment.effective_start_date;
5909 v_asg_end_date := v_assignment.effective_end_date;
5910 --
5911 -- Assignment piece is for the same assignment as the previous piece
5912 -- and it is contiguous with the previous piece.
5913 --
5914 else
5915 --
5916 -- Increment the end date of the assignment.
5917 --
5918 v_asg_end_date := v_assignment.effective_end_date;
5919 --
5920 end if;
5921 --
5922 end loop;
5923 --
5924 -- Have found some entries which may need changing.
5925 --
5926 if v_entry_count > 0 then
5927 --
5928 -- Compare the calculated element entries with those on the system
5929 -- and adjust as necessary.
5930 --
5931 if g_debug then
5932 hr_utility.trace('v_entry_count : ' || v_entry_count);
5933 end if;
5934 --
5935 -- See if multiple entries are allowed.
5936 --
5937 v_mult_ent_allowed_flag := hrentmnt.mult_ent_allowed_flag
5938 (v_link.element_link_id);
5939 --
5940 hrentmnt.adjust_recurring_entries ( p_dt_mode,
5941 p_assignment_id,
5942 v_link.element_link_id,
5943 v_link.standard_link_flag,
5944 v_mult_ent_allowed_flag,
5945 p_validation_start_date,
5946 p_validation_end_date,
5947 v_val_start_date_minus_one,
5948 v_val_end_date_plus_one,
5949 v_entry_count,
5950 v_entry_start_date_tbl,
5951 v_entry_end_date_tbl,
5952 p_entries_changed,
5953 p_old_hire_date);
5954 --
5955 -- Reset the number of entries found.
5956 --
5957 v_entry_count := 0;
5958 --
5959 end if;
5960 --
5961 end if;
5962 --
5963 close csr_assignment;
5964 --
5965 end loop;
5966 --
5967 if g_debug then
5968 hr_utility.trace('Out hrentmnt.adjust_entries_asg_criteria');
5969 end if;
5970 --
5971 end adjust_entries_asg_criteria;
5972 --
5973 ------------------------------------------------------------------------------
5974 -- NAME --
5975 -- hrentmnt.maintain_entries_el --
5976 -- --
5977 -- DESCRIPTION --
5978 -- Creates element entries on creation of a standard element link. --
5979 -- If p_assignment_id is specified, the element entry is created only for --
5980 -- the assignment. By default all of the eligible assignments will be --
5981 -- processed. --
5982 ------------------------------------------------------------------------------
5983 --
5984 procedure maintain_entries_el
5985 (
5986 p_business_group_id number,
5987 p_element_link_id number,
5988 p_element_type_id number,
5989 p_effective_start_date date,
5990 p_effective_end_date date,
5991 p_payroll_id number,
5992 p_link_to_all_payrolls_flag varchar2,
5993 p_job_id number,
5994 p_grade_id number,
5995 p_position_id number,
5996 p_organization_id number,
5997 p_location_id number,
5998 p_pay_basis_id number,
5999 p_employment_category varchar2,
6000 p_people_group_id number,
6001 p_assignment_id number default null
6002 ) is
6003 --
6004 -- Finds all assignment pieces that match the standard element link eg.
6005 --
6006 -- EL |-----------------------------A------------------------------>
6007 --
6008 -- ASG |---A----|----A----|-----B-----|-----A------|-----A----->
6009 --
6010 -- pieces |--------|---------| |------------|----------->
6011 --
6012 -- These pieces can then be assembled into possible element entries that
6013 -- need to be created ie.
6014 --
6015 -- EE |------------------|
6016 --
6017 -- EE |------------------------>
6018 --
6019 type t_asg_rec is record
6020 (assignment_id number,
6021 effective_start_date date,
6022 effective_end_date date);
6023 --
6024 cursor csr_assignment
6025 (
6026 p_element_link_id number,
6027 p_element_type_id number,
6028 p_business_group_id number,
6029 p_effective_start_date date,
6030 p_effective_end_date date,
6031 p_payroll_id number,
6032 p_link_to_all_payrolls_flag varchar2,
6033 p_job_id number,
6034 p_grade_id number,
6035 p_position_id number,
6036 p_organization_id number,
6037 p_location_id number,
6038 p_pay_basis_id number,
6039 p_employment_category varchar2,
6040 p_people_group_id number,
6041 p_assignment_id number
6042 ) is
6043 select asg.assignment_id,
6044 asg.effective_start_date,
6045 asg.effective_end_date
6046 from per_all_assignments_f asg
6047 where ( ( p_assignment_id is null
6048 and asg.business_group_id = p_business_group_id)
6049 or (asg.assignment_id = p_assignment_id))
6050 and asg.assignment_type = 'E'
6051 and asg.effective_start_date <= p_effective_end_date
6052 and asg.effective_end_date >= p_effective_start_date
6053 and ((p_payroll_id is not null and
6054 p_payroll_id = asg.payroll_id)
6055 or (p_link_to_all_payrolls_flag = 'Y' and
6056 asg.payroll_id is not null)
6057 or (p_link_to_all_payrolls_flag = 'N' and
6058 p_payroll_id is null))
6059 and (p_job_id is null or
6060 p_job_id = asg.job_id)
6061 and (p_grade_id is null or
6062 p_grade_id = asg.grade_id)
6063 and (p_position_id is null or
6064 p_position_id = asg.position_id)
6065 and (p_organization_id is null or
6066 p_organization_id = asg.organization_id)
6067 and (p_location_id is null or
6068 p_location_id = asg.location_id)
6069 and (p_employment_category is null or
6070 p_employment_category = asg.employment_category)
6071 and (p_people_group_id is null or
6072 exists
6073 (select null
6074 from pay_assignment_link_usages_f alu
6075 where alu.assignment_id = asg.assignment_id
6076 and alu.element_link_id = p_element_link_id
6077 and alu.effective_start_date <= asg.effective_end_date
6078 and alu.effective_end_date >= asg.effective_start_date))
6079 and (p_pay_basis_id = asg.pay_basis_id
6080 or (p_pay_basis_id is null and
6081 (asg.pay_basis_id is null
6082 -- Indirect salary basis check.
6083 or not exists
6084 (select pb.pay_basis_id
6085 from per_pay_bases pb,
6086 pay_input_values_f iv
6087 where iv.element_type_id = p_element_type_id
6088 and pb.input_value_id = iv.input_value_id
6089 and pb.business_group_id = p_business_group_id
6090 )
6091 or exists
6092 (select pb.pay_basis_id
6093 from per_pay_bases pb,
6094 pay_input_values_f iv
6095 where iv.element_type_id = p_element_type_id
6096 and pb.input_value_id = iv.input_value_id
6097 and pb.pay_basis_id = asg.pay_basis_id
6098 )
6099 )
6100 )
6101 )
6102 --
6103 -- ensure no entries exist for the assignment
6104 --
6105 and not exists
6106 (select null from pay_element_entries_f pee
6107 where pee.assignment_id = asg.assignment_id
6108 and pee.element_link_id = p_element_link_id
6109 )
6110 order by asg.assignment_id, asg.effective_start_date
6111 for update nowait;
6112 --
6113 -- Check if the assignment is visible with the secure view. Bug 5512101.
6114 --
6115 cursor csr_sec_asg(p_asgid number)
6116 is
6117 select 1
6118 from per_assignments_f
6119 where assignment_id = p_asgid;
6120
6121 --
6122 -- local variables
6123 --
6124 v_assignment t_asg_rec;
6125 v_asg_id number;
6126 v_asg_start_date date;
6127 v_asg_end_date date;
6128 v_entry_start_date date;
6129 v_entry_end_date date;
6130 v_dummy_number number;
6131 v_num_entry_values number;
6132 v_input_value_id_tbl hr_entry.number_table;
6133 v_entry_value_tbl hr_entry.varchar2_table;
6134 v_message_name varchar2(30);
6135 v_appl_short_name varchar2(30);
6136 v_asg_visible number;
6137 -- bugfix 1691062
6138 l_category fnd_descr_flex_contexts.descriptive_flex_context_code%type;
6139 --
6140 procedure check_parameters is
6141 --
6142 begin
6143 --
6144 hr_utility.trace ('hrentmnt.maintain_entries_el');
6145 hr_utility.trace ('');
6146 hr_utility.trace (' p_business_group_id = '
6147 ||to_char (p_business_group_id));
6148 hr_utility.trace (' p_element_link_id = '
6149 ||to_char (p_element_link_id));
6150 hr_utility.trace (' p_element_type_id = '
6151 ||to_char (p_element_type_id));
6152 hr_utility.trace (' p_effective_start_date = '
6153 ||to_char (p_effective_start_date));
6154 hr_utility.trace (' p_effective_end_date = '
6155 ||to_char (p_effective_end_date));
6156 hr_utility.trace (' p_payroll_id = '
6157 ||to_char (p_payroll_id));
6158 hr_utility.trace (' p_link_to_all_payrolls_flag = '
6159 ||p_link_to_all_payrolls_flag);
6160 hr_utility.trace (' p_job_id = '
6161 ||to_char (p_job_id));
6162 hr_utility.trace (' p_grade_id = '
6163 ||to_char (p_grade_id));
6164 hr_utility.trace (' p_position_id = '
6165 ||to_char (p_position_id));
6166 hr_utility.trace (' p_organization_id = '
6167 ||to_char (p_organization_id));
6168 hr_utility.trace (' p_location_id = '
6169 ||to_char (p_location_id));
6170 hr_utility.trace (' p_pay_basis_id = '
6171 ||to_char (p_pay_basis_id));
6172 hr_utility.trace (' p_employment_category = '
6173 ||p_employment_category);
6174 hr_utility.trace (' p_people_group_id = '
6175 ||to_char (p_people_group_id));
6176 hr_utility.trace ('');
6177 --
6178 end check_parameters;
6179 --
6180 begin
6181 g_debug := hr_utility.debug_enabled;
6182 --
6183 if g_debug then
6184 check_parameters;
6185 end if;
6186 --
6187 -- Open assignment cursor ready for processing.
6188 --
6189 begin
6190 open csr_assignment(p_element_link_id,
6191 p_element_type_id,
6192 p_business_group_id,
6193 p_effective_start_date,
6194 p_effective_end_date,
6195 p_payroll_id,
6196 p_link_to_all_payrolls_flag,
6197 p_job_id,
6198 p_grade_id,
6199 p_position_id,
6200 p_organization_id,
6201 p_location_id,
6202 p_pay_basis_id,
6203 p_employment_category,
6204 p_people_group_id,
6205 p_assignment_id);
6206 exception
6207 when hr_api.object_locked then
6208 --
6209 -- Failed to lock the assignment.
6210 --
6211 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
6212 hr_utility.set_message_token('TABLE_NAME', 'per_all_assignments_f');
6213 hr_utility.raise_error;
6214 end;
6215 --
6216 -- Get first assignment piece matching the standard link.
6217 --
6218 fetch csr_assignment into v_assignment;
6219 --
6220 -- Assignment matching standard link has been found.
6221 --
6222 if csr_assignment%found then
6223 --
6224 -- Initialise variables for the assignment.
6225 --
6226 v_asg_id := v_assignment.assignment_id;
6227 v_asg_start_date := v_assignment.effective_start_date;
6228 v_asg_end_date := v_assignment.effective_end_date;
6229 --
6230 -- Check to see if this assignment is visible to a secure user.
6231 -- Bug 5512101.
6232 --
6233 open csr_sec_asg(v_asg_id);
6234 fetch csr_sec_asg into v_asg_visible;
6235 if csr_sec_asg%notfound then
6236 --
6237 if g_debug then
6238 hr_utility.trace('Assignment ID Not Found: '||v_asg_id);
6239 end if;
6240 --
6241 -- The user is not authorized to process this assignment.
6242 --
6243 close csr_sec_asg;
6244 close csr_assignment;
6245 --
6246 hr_utility.set_message(801,'PAY_33449_STD_LINK_SEC_ASG');
6247 hr_utility.raise_error;
6248 end if;
6249 close csr_sec_asg;
6250
6251 --
6252 -- Loop for all assignment pieces that match the standard link.
6253 --
6254 while csr_assignment%found loop
6255 --
6256 -- Get next piece of assignment.
6257 --
6258 fetch csr_assignment into v_assignment;
6259 --
6260 -- Assignment piece is not for the same assignment previously found or
6261 -- it is but is is not contiguous with the previous pice of assignment.
6262 --
6263 if csr_assignment%notfound or not
6264 (v_assignment.assignment_id = v_asg_id and
6265 v_assignment.effective_start_date = v_asg_end_date + 1) then
6266 --
6267 -- Calculate the element entry dates NB. need to check to see if the
6268 -- element entry cannot be created because the assignment has been
6269 -- terminated in the future and the end date according to the
6270 -- termination rule is before the calculated start date of the element
6271 -- entry. This should not stop other entries being created !
6272 --
6273 begin
6274 hrentmnt.return_entry_dates
6275 (v_asg_id,
6276 v_asg_start_date,
6277 p_element_link_id,
6278 p_effective_start_date,
6279 'Y',
6280 v_entry_start_date,
6281 v_entry_end_date);
6282 exception
6283 when hr_utility.hr_error then
6284 hr_utility.get_message_details(v_message_name,v_appl_short_name);
6285 if v_message_name = 'HR_6370_ELE_ENTRY_NO_TERM' then
6286 v_entry_start_date := null;
6287 v_message_name := null;
6288 v_appl_short_name := null;
6289 else
6290 raise;
6291 end if;
6292 end;
6293 --
6294 -- An element entry needs to be created.
6295 --
6296 if v_entry_start_date is not null then
6297 --
6298 -- Create new element netry for standard link.
6299 --
6300 if g_debug then
6301 hr_utility.trace('********** SL creation');
6302 hr_utility.trace('********** for SL call EE insert interface');
6303 end if;
6304 hr_entry_api.insert_element_entry
6305 (p_effective_start_date => v_entry_start_date,
6306 p_effective_end_date => v_entry_end_date,
6307 p_element_entry_id => v_dummy_number,
6308 p_assignment_id => v_asg_id,
6309 p_element_link_id => p_element_link_id,
6310 p_creator_type => 'F',
6311 p_entry_type => 'E');
6312
6313 -- bugfix 1691062
6314 l_category := get_entry_info_category(v_asg_id,
6315 v_entry_start_date,
6316 p_element_link_id);
6317 if l_category is not null then
6318 if g_debug then
6319 hr_utility.trace('********** SL creation');
6320 hr_utility.trace('********** l_category>' || l_category || '<');
6321 end if;
6322
6323 UPDATE pay_element_entries_f
6324 SET entry_information_category = l_category
6325 WHERE element_entry_id = v_dummy_number;
6326 end if;
6327
6328 end if;
6329 --
6330 -- Reset the variables to the effective dates of the new assignment.
6331 --
6332 v_asg_start_date := v_assignment.effective_start_date;
6333 v_asg_end_date := v_assignment.effective_end_date;
6334 --
6335 -- Assignment piece is for the same assignment as the previous piece
6336 -- and it is contiguous with the previous piece.
6337 --
6338 else
6339 --
6340 -- Increment the end date of the assignment.
6341 --
6342 v_asg_end_date := v_assignment.effective_end_date;
6343 --
6344 end if;
6345 --
6346 -- Keep track of the current assignment being processed.
6347 --
6348 v_asg_id := v_assignment.assignment_id;
6349 --
6350 end loop;
6351 --
6352 end if;
6353 --
6354 -- Close assignment cursor as all assignments have been processed.
6355 --
6356 close csr_assignment;
6357 --
6358 if g_debug then
6359 hr_utility.trace ('Out hrentmnt.maintain_entries_el');
6360 end if;
6361 --
6362 end maintain_entries_el;
6363 --
6364 --
6365 --
6366 procedure dump_info(p_assignment_id in number,
6367 p_business_group_id in number)
6368 is
6369 cursor csr_assignment(b_assignment_id number,
6370 b_business_group_id number) is
6371 SELECT asg.assignment_id,
6372 asg.effective_start_date,
6373 asg.effective_end_date,
6374 asg.primary_flag,
6375 --
6376 -- begin criteria used for EL matching
6377 --
6378 asg.organization_id,
6379 asg.people_group_id,
6380 asg.job_id,
6381 asg.position_id,
6382 asg.grade_id,
6383 asg.location_id,
6384 asg.employment_category,
6385 asg.payroll_id,
6386 asg.pay_basis_id
6387 --
6388 -- end criteria used for EL matching
6389 --
6390 FROM PER_ALL_ASSIGNMENTS_F asg
6391 WHERE asg.assignment_id = b_assignment_id
6392 and asg.business_group_id = b_business_group_id
6393 ORDER BY
6394 asg.assignment_id,
6395 asg.effective_start_date
6396 ;
6397
6398 cursor ee is
6399 select distinct
6400 pee.element_entry_id,
6401 pee.entry_type,
6402 pee.creator_type,
6403 to_char(pee.effective_start_date, 'YYYY/MM/DD') esd,
6404 to_char(pee.effective_end_date, 'YYYY/MM/DD') eed,
6405 pel.element_link_id,
6406 pel.element_type_id,
6407 pel.payroll_id,
6408 pel.job_id,
6409 pel.position_id,
6410 pel.people_group_id,
6411 pel.organization_id,
6412 pel.location_id,
6413 pel.grade_id,
6414 pel.pay_basis_id,
6415 pel.link_to_all_payrolls_flag,
6416 pel.standard_link_flag
6417 from pay_element_entries_f pee,
6418 pay_element_links_f pel
6419 where pee.assignment_id = p_assignment_id
6420 and pel.element_link_id = pee.element_link_id;
6421
6422 begin
6423 hr_utility.trace('***** dump start *****');
6424
6425 for rec_assignment in csr_assignment(p_assignment_id,
6426 p_business_group_id) loop
6427 hr_utility.trace('rec_assignment.assignment_id>' ||
6428 rec_assignment.assignment_id || '<');
6429 hr_utility.trace(' rec_assignment.effective_start_date>' ||
6430 rec_assignment.effective_start_date || '<');
6431 hr_utility.trace(' rec_assignment.effective_end_date>' ||
6432 rec_assignment.effective_end_date || '<');
6433 hr_utility.trace(' rec_assignment.primary_flag>' ||
6434 rec_assignment.primary_flag || '<');
6435 hr_utility.trace(' rec_assignment.organization_id>' ||
6436 rec_assignment.organization_id || '<');
6437 hr_utility.trace(' rec_assignment.people_group_id>' ||
6438 rec_assignment.people_group_id || '<');
6439 hr_utility.trace(' rec_assignment.job_id>' ||
6440 rec_assignment.job_id || '<');
6441 hr_utility.trace(' rec_assignment.position_id>' ||
6442 rec_assignment.position_id || '<');
6443 hr_utility.trace(' rec_assignment.grade_id>' ||
6444 rec_assignment.grade_id || '<');
6445 hr_utility.trace(' rec_assignment.location_id>' ||
6446 rec_assignment.location_id || '<');
6447 hr_utility.trace(' rec_assignment.employment_category>' ||
6448 rec_assignment.employment_category || '<');
6449 hr_utility.trace(' rec_assignment.payroll_id>' ||
6450 rec_assignment.payroll_id || '<');
6451 hr_utility.trace(' rec_assignment.pay_basis_id>' ||
6452 rec_assignment.pay_basis_id || '<');
6453 end loop;
6454
6455 hr_utility.trace('>>> Element Entry info <<<');
6456 hr_utility.trace('EEID ET CT ESD EED ELID ETID PYID JOB POS PGRP ORG LOC GRD PBID LFLG SLFLG');
6457
6458 for eerec in ee loop
6459
6460 hr_utility.trace(
6461 eerec.element_entry_id || ' ' ||
6462 eerec.entry_type || ' ' ||
6463 eerec.creator_type || ' ' ||
6464 eerec.esd || ' ' ||
6465 eerec.eed || ' ' ||
6466 eerec.element_link_id || ' ' ||
6467 eerec.element_type_id || ' ' ||
6468 nvl(to_char(eerec.payroll_id), '*') || ' ' ||
6469 nvl(to_char(eerec.job_id), '*') || ' ' ||
6470 nvl(to_char(eerec.position_id), '*') || ' ' ||
6471 nvl(to_char(eerec.people_group_id), '*') || ' ' ||
6472 nvl(to_char(eerec.organization_id), '*') || ' ' ||
6473 nvl(to_char(eerec.location_id), '*') || ' ' ||
6474 nvl(to_char(eerec.grade_id), '*') || ' ' ||
6475 nvl(to_char(eerec.pay_basis_id), '*') || ' ' ||
6476 eerec.link_to_all_payrolls_flag || ' ' ||
6477 eerec.standard_link_flag);
6478
6479 end loop;
6480
6481 hr_utility.trace('***** dump end *****');
6482 end dump_info;
6483 --
6484 --
6485 --
6486 ------------------------------------------------------------------------------
6487 -- NAME --
6488 -- hrentmnt.maintain_entries_asg --
6489 -- --
6490 -- DESCRIPTION --
6491 -- This forms the interface into the procedures that maintain element --
6492 -- entries when affected by various events ie. --
6493 -- --
6494 -- CHANGE_PQC : changes in personal qualifying conditions. --
6495 -- CNCL_TERM : a termination is cancelled. --
6496 -- CNCL_HIRE : the hiring of a person is cancelled. --
6497 -- ASG_CRITERIA : assignment criteria has chnaged. --
6498 -- HIRE_APPL : an applicant is hired. --
6499 ------------------------------------------------------------------------------
6500 --
6501 procedure maintain_entries_asg
6502 (
6503 p_assignment_id number,
6504 p_old_payroll_id number,
6505 p_new_payroll_id number,
6506 p_business_group_id number,
6507 p_operation varchar2,
6508 p_actual_term_date date,
6509 p_last_standard_date date,
6510 p_final_process_date date,
6511 p_dt_mode varchar2,
6512 p_validation_start_date date,
6513 p_validation_end_date date,
6514 p_entries_changed in out nocopy varchar2,
6515 p_old_hire_date date default null,
6516 p_old_people_group_id number default null,
6517 p_new_people_group_id number default null
6518 ) is
6519 --
6520 procedure check_parameters is
6521 --
6522 begin
6523 --
6524 hr_utility.trace('In hrentmnt.maintain_entries_asg');
6525 hr_utility.trace ('');
6526 hr_utility.trace (' p_assignment_id = '
6527 ||to_char (p_assignment_id));
6528 hr_utility.trace (' p_old_payroll_id = '
6529 ||to_char (p_old_payroll_id));
6530 hr_utility.trace (' p_new_payroll_id = '
6531 ||to_char (p_new_payroll_id));
6532 hr_utility.trace (' p_business_group_id = '
6533 ||to_char (p_business_group_id));
6534 hr_utility.trace (' p_operation = '
6535 ||p_operation);
6536 hr_utility.trace (' p_actual_term_date = '
6537 ||to_char (p_actual_term_date));
6538 hr_utility.trace (' p_last_standard_date = '
6539 ||to_char (p_last_standard_date));
6540 hr_utility.trace (' p_final_process_date = '
6541 ||to_char (p_final_process_date));
6542 hr_utility.trace (' p_dt_mode = '
6543 ||p_dt_mode);
6544 hr_utility.trace (' p_validation_start_date = '
6545 ||to_char (p_validation_start_date));
6546 hr_utility.trace (' p_validation_end_date = '
6547 ||to_char (p_validation_end_date));
6548 hr_utility.trace (' p_entries_changed = '
6549 ||p_entries_changed);
6550 hr_utility.trace (' p_old_hire_date = '
6551 ||to_char(p_old_hire_date));
6552 hr_utility.trace (' p_old_people_group_id = '
6553 ||to_char(p_old_people_group_id));
6554 hr_utility.trace (' p_new_people_group_id = '
6555 ||to_char(p_new_people_group_id));
6556 hr_utility.trace ('');
6557 --
6558 end check_parameters;
6559 --
6560 begin
6561 --
6562 null;
6563 -- --
6564 begin
6565 g_debug := hr_utility.debug_enabled;
6566 --
6567 if g_debug then
6568 check_parameters;
6569 end if;
6570 --
6571 p_entries_changed := null;
6572 --
6573 -- Qualifying conditions have changed.
6574 --
6575 if p_operation = 'CHANGE_PQC' then
6576 hrentmnt.adjust_entries_pqc
6577 (p_assignment_id,
6578 p_entries_changed);
6579 --
6580 -- An employee termination is being cancelled.
6581 --
6582 elsif p_operation = 'CNCL_TERM' then
6583 hrentmnt.adjust_entries_cncl_term
6584 (p_business_group_id,
6585 p_assignment_id,
6586 p_actual_term_date,
6587 p_last_standard_date,
6588 p_final_process_date,
6589 p_entries_changed,
6590 p_dt_mode,
6591 p_old_people_group_id,
6592 p_new_people_group_id);
6593 --
6594 -- The hiring of an employee has been cancelled.
6595 --
6596 elsif p_operation = 'CNCL_HIRE' then
6597 hrentmnt.adjust_entries_cncl_hire
6598 (p_business_group_id,
6599 p_assignment_id,
6600 p_validation_start_date,
6601 p_validation_end_date,
6602 p_entries_changed,
6603 p_dt_mode,
6604 p_old_people_group_id,
6605 p_new_people_group_id);
6606 --
6607 -- An employees assignment has been changed.
6608 --
6609 elsif p_operation ='ASG_CRITERIA' then
6610 hrentmnt.adjust_entries_asg_criteria
6611 (p_business_group_id,
6612 p_assignment_id,
6613 p_dt_mode,
6614 p_old_payroll_id,
6615 p_new_payroll_id,
6616 p_validation_start_date,
6617 p_validation_end_date,
6618 p_entries_changed,
6619 p_old_hire_date,
6620 p_old_people_group_id,
6621 p_new_people_group_id);
6622 --
6623 -- An applicant has been hired
6624 --
6625 elsif p_operation = 'HIRE_APPL' then
6626 --
6627 -- Bugfix 5182845
6628 -- We need to ensure that the ALUs are rebuilt, since ALUs are never
6629 -- maintained for applicants. Therefore, even if the old and new people
6630 -- group ids are the same, the ALUs won't have been built previously.
6631 -- In order to force a rebuild of the ALUs, the old and new people group
6632 -- ids must differ. We modify the old people group id to ensure this.
6633 -- The min value of the pay_people_groups_s sequence is 1, so setting
6634 -- p_old_people_group_id to 0 will guarantee the old and new people group
6635 -- ids will always differ.
6636 --
6637 hrentmnt.adjust_entries_asg_criteria
6638 (p_business_group_id,
6639 p_assignment_id,
6640 p_dt_mode,
6641 p_old_payroll_id,
6642 p_new_payroll_id,
6643 p_validation_start_date,
6644 p_validation_end_date,
6645 p_entries_changed,
6646 p_old_hire_date,
6647 0, -- p_old_new_people_group_id
6648 p_new_people_group_id);
6649 end if;
6650 --
6651 if g_debug then
6652 hr_utility.trace('Out hrentmnt.maintain_entries_asg');
6653 end if;
6654 --
6655 exception
6656 when others then
6657 --
6658 -- if an error occurs, print the message and raise the error again
6659 if g_debug then
6660
6661 hr_utility.trace('****************************************');
6662 hr_utility.trace('Sqlcode>' || Sqlcode || '<');
6663 hr_utility.trace('Sqlerrm>' || Sqlerrm || '<');
6664 hr_utility.trace('****************************************');
6665
6666 dump_info(p_assignment_id, p_business_group_id);
6667
6668 end if;
6669 raise;
6670 end;
6671 -- --
6672 end maintain_entries_asg;
6673 --
6674 ------------------------------------------------------------------------------
6675 -- NAME --
6676 -- hrentmnt.maintain_entries_asg --
6677 -- --
6678 -- DESCRIPTION --
6679 -- Overloaded version to allow backward compatibility. --
6680 ------------------------------------------------------------------------------
6681 --
6682 procedure maintain_entries_asg
6683 (
6684 p_assignment_id number,
6685 p_business_group_id number,
6686 p_operation varchar2,
6687 p_actual_term_date date,
6688 p_last_standard_date date,
6689 p_final_process_date date,
6690 p_dt_mode varchar2,
6691 p_validation_start_date date,
6692 p_validation_end_date date
6693 ) is
6694 --
6695 -- Local Variables
6696 --
6697 v_entries_changed varchar2(1);
6698 --
6699 procedure check_parameters is
6700 --
6701 begin
6702 --
6703 hr_utility.trace ('In hrentmnt.maintain_entries_asg');
6704 hr_utility.trace ('');
6705 hr_utility.trace (' p_assignment_id = '
6706 ||to_char (p_assignment_id));
6707 hr_utility.trace (' p_business_group_id = '
6708 ||to_char (p_business_group_id));
6709 hr_utility.trace (' p_operation = '
6710 ||p_operation);
6711 hr_utility.trace (' p_actual_term_date = '
6712 ||to_char (p_actual_term_date));
6713 hr_utility.trace (' p_last_standard_date = '
6714 ||to_char (p_last_standard_date));
6715 hr_utility.trace (' p_final_process_date = '
6716 ||to_char (p_final_process_date));
6717 hr_utility.trace (' p_dt_mode = '
6718 ||p_dt_mode);
6719 hr_utility.trace (' p_validation_start_date = '
6720 ||to_char (p_validation_start_date));
6721 hr_utility.trace (' p_validation_end_date = '
6722 ||to_char (p_validation_end_date));
6723 hr_utility.trace ('');
6724 --
6725 end check_parameters;
6726 --
6727 begin
6728 null;
6729 -- --
6730 begin
6731 g_debug := hr_utility.debug_enabled;
6732 --
6733 if g_debug then
6734 check_parameters;
6735 end if;
6736 --
6737 hrentmnt.maintain_entries_asg
6738 (p_assignment_id,
6739 1,
6740 2,
6741 p_business_group_id,
6742 p_operation,
6743 p_actual_term_date,
6744 p_last_standard_date,
6745 p_final_process_date,
6746 p_dt_mode,
6747 p_validation_start_date,
6748 p_validation_end_date,
6749 v_entries_changed,
6750 null, -- p_old_people_group_id
6751 null); -- p_new_people_group_id
6752 --
6753 exception
6754 when others then
6755 --
6756 -- if an error occurs, print the message and raise the error again
6757 --
6758 if g_debug then
6759 hr_utility.trace('***** over');
6760 hr_utility.trace('Sqlcode>' || Sqlcode || '<');
6761 hr_utility.trace('Sqlerrm>' || Sqlerrm || '<');
6762 hr_utility.trace('*****');
6763 end if;
6764 raise;
6765 end;
6766 -- --
6767 end maintain_entries_asg;
6768 --
6769 ------------------------------------------------------------------------------
6770 -- NAME --
6771 -- hrentmnt.check_opmu --
6772 -- --
6773 -- DESCRIPTION --
6774 -- Ensures that on transfer of Payroll (on the Assignment) or when a change --
6775 -- causes the Payroll to change in the future that Personal Payment Methods --
6776 -- have corresponding Org Pay Methods that are used by the new Payroll. --
6777 -- i.e. that Personal Payment Methods are not invalidated. --
6778 ------------------------------------------------------------------------------
6779 --
6780 procedure check_opmu
6781 (
6782 p_assignment_id number,
6783 p_payroll_id number,
6784 p_dt_mode varchar2,
6785 p_validation_start_date date,
6786 p_validation_end_date date
6787 ) is
6788 --
6789 cursor csr_personal_payment_methods
6790 (
6791 p_assignment_id number,
6792 p_validation_start_date date,
6793 p_validation_end_date date
6794 ) is
6795 select ppm.personal_payment_method_id,
6796 ppm.org_payment_method_id,
6797 greatest(ppm.effective_start_date,p_validation_start_date)
6798 start_date,
6799 least(ppm.effective_end_date,p_validation_end_date) end_date
6800 from pay_personal_payment_methods_f ppm
6801 where ppm.assignment_id = p_assignment_id
6802 and ppm.effective_start_date <= p_validation_end_date
6803 and ppm.effective_end_date >= p_validation_start_date;
6804 --
6805 -- Local Variables
6806 --
6807 no_opmu varchar2(1) := 'N';
6808 --
6809 procedure check_parameters is
6810 --
6811 begin
6812 --
6813 hr_utility.trace ('In hrentmnt.check_opmu');
6814 hr_utility.trace ('');
6815 hr_utility.trace (' p_assignment_id = '
6816 ||to_char (p_assignment_id));
6817 hr_utility.trace (' p_payroll_id = '
6818 ||to_char (p_payroll_id));
6819 hr_utility.trace (' p_dt_mode = '
6820 ||p_dt_mode);
6821 hr_utility.trace (' p_validation_start_date = '
6822 ||to_char (p_validation_start_date));
6823 hr_utility.trace (' p_validation_end_date = '
6824 ||to_char (p_validation_end_date));
6825 hr_utility.trace ('');
6826 --
6827 end check_parameters;
6828 --
6829 begin
6830 g_debug := hr_utility.debug_enabled;
6831 --
6832 if g_debug then
6833 check_parameters;
6834 end if;
6835 --
6836 -- If the mode is DELETE or ZAP then merely check to see if there will be
6837 -- any PPMs left after the end date of the Assignment
6838 --
6839 if p_dt_mode = 'DELETE' or p_dt_mode = 'ZAP' then
6840 --
6841 begin
6842 select 'Y'
6843 into no_opmu
6844 from sys.dual
6845 where exists
6846 (select null
6847 from pay_personal_payment_methods_f ppm
6848 where assignment_id = p_assignment_id
6849 and ppm.effective_end_date >= p_validation_start_date);
6850 exception
6851 when no_data_found then
6852 if g_debug then
6853 hr_utility.trace('No opmu for DELETE or ZAP mode');
6854 end if;
6855 end;
6856 --
6857 -- Otherwise check that the existing PPMs remain valid after the Transfer
6858 --
6859 elsif not p_dt_mode = 'INSERT' then
6860 --
6861 -- For each PPM for the Assignment that has any part of its life within the
6862 -- Assignment Validation Start Date and Validation End Date Range if the
6863 -- following statements returns 'Y' then ERROR because OPMUs are
6864 -- invalidated by the change of PAYROLL.
6865 --
6866 for v_ppm in csr_personal_payment_methods(p_assignment_id,
6867 p_validation_start_date,
6868 p_validation_end_date) loop
6869 --
6870 -- first ensure that there is a valid opmu on the start date of the ppm
6871 -- if there isn't then flag an error
6872 --
6873 begin
6874 select 'Y'
6875 into no_opmu
6876 from sys.dual
6877 where not exists
6878 (select null
6879 from pay_org_pay_method_usages_f opmu,
6880 pay_payrolls_f p
6881 where v_ppm.start_date between opmu.effective_start_date
6882 and opmu.effective_end_date
6883 and opmu.org_payment_method_id =
6884 v_ppm.org_payment_method_id
6885 and opmu.effective_start_date
6886 between p.effective_start_date and p.effective_end_date
6887 and opmu.payroll_id = p.payroll_id
6888 and p.payroll_id = p_payroll_id);
6889 exception
6890 when no_data_found then null;
6891 end;
6892 --
6893 if no_opmu = 'Y' then
6894 if g_debug then
6895 hr_utility.trace('No opmu from first check.');
6896 end if;
6897 exit;
6898 end if;
6899 --
6900 -- Now ensure that there is a valid OPMU for the lifetime of the PPM.
6901 -- For each OPMU that is valid ensure that if it ends before the end of
6902 -- the PPM then there is at least one other OPMU that is valid on the day
6903 -- after the end date of the OPMU currently being considered. If there is
6904 -- no such OPMU then an error is flagged.
6905 --
6906 begin
6907 select 'Y'
6908 into no_opmu
6909 from sys.dual
6910 where exists
6911 (select null
6912 from pay_org_pay_method_usages_f opmu,
6913 pay_payrolls_f p
6914 where opmu.effective_start_date <= v_ppm.end_date
6915 and opmu.effective_end_date >= v_ppm.start_date
6916 and opmu.org_payment_method_id =
6917 v_ppm.org_payment_method_id
6918 and opmu.effective_start_date between
6919 p.effective_start_date and p.effective_end_date
6920 and opmu.payroll_id = p.payroll_id
6921 and p.payroll_id = p_payroll_id
6922 and opmu.effective_end_date < v_ppm.end_date
6923 and not exists
6924 (select null
6925 from pay_org_pay_method_usages_f opmu2,
6926 pay_payrolls_f p2
6927 where opmu2.effective_start_date <=
6928 opmu.effective_end_date + 1
6929 and opmu2.effective_end_date >
6930 opmu.effective_end_date
6931 and opmu2.org_payment_method_id =
6932 v_ppm.org_payment_method_id
6933 and opmu2.payroll_id = p2.payroll_id
6934 and p2.payroll_id = p_payroll_id
6935 and opmu2.effective_start_date between
6936 p2.effective_start_date and p2.effective_end_date));
6937 exception
6938 when no_data_found then null;
6939 end;
6940 --
6941 if no_opmu = 'Y' then
6942 if g_debug then
6943 hr_utility.trace('No opmu from second check.');
6944 end if;
6945 exit;
6946 end if;
6947 --
6948 end loop;
6949 --
6950 end if;
6951 --
6952 if no_opmu = 'Y' then
6953 hr_utility.set_message(801,'HR_6844_ASS_PPM_INVALID');
6954 hr_utility.raise_error;
6955 end if;
6956 --
6957 if g_debug then
6958 hr_utility.trace ('Out hrentmnt.check_opmu');
6959 end if;
6960 --
6961 end check_opmu;
6962 --
6963
6964 ------------------------------------------------------------------------------
6965 -- NAME --
6966 -- hr_entry_api.move_fpd_entries --
6967 -- --
6968 -- DESCRIPTION --
6969 -- This procedure should be called from HR code to carry out entry changes --
6970 -- when final process date is changed. --
6971 ------------------------------------------------------------------------------
6972
6973 procedure move_fpd_entries
6974 (
6975 p_assignment_id in number
6976 ,p_period_of_service_id in number
6977 ,p_new_final_process_date in date
6978 ,p_old_final_process_date in date
6979 )
6980 is
6981 l_session_date date;
6982 l_new_esd date;
6983 l_new_eed date;
6984 l_object_version_number number;
6985 l_warnings_exist boolean;
6986 l_new_final_process_date date;
6987 l_proc varchar2(80):= 'hrentmnt.move_fpd_entries';
6988 --
6989 cursor process_entries(c_old_final_process_date date, c_assignment_id number) is
6990 select asg.assignment_id,
6991 ee.element_entry_id,
6992 ee.element_link_id,
6993 ee.original_entry_id,
6994 ee.effective_start_date,
6995 ee.effective_end_date,
6996 ee.target_entry_id,
6997 ee.entry_type,
6998 ee.creator_type,
6999 et.processing_type,
7000 ee.updating_action_id,
7001 ee.object_version_number
7002 from per_all_assignments_f asg
7003 , pay_element_entries_f ee
7004 , pay_element_types_f et
7005 where asg.assignment_id = c_assignment_id
7006 and asg.assignment_id = ee.assignment_id
7007 and ee.element_type_id = et.element_type_id
7008 and et.post_termination_rule = 'F'
7009 and c_old_final_process_date between ee.effective_start_date and ee.effective_end_date
7010 and c_old_final_process_date between et.effective_start_date and et.effective_end_date;
7011
7012 BEGIN
7013 hr_utility.set_location(l_proc, 10);
7014
7015 SAVEPOINT move_fpd_entries;
7016
7017 l_new_final_process_date := p_new_final_process_date;
7018
7019 if l_new_final_process_date is null then
7020 l_new_final_process_date := hr_general.end_of_time;
7021 end if;
7022
7023
7024 -- Get all the entries of termination rule type F which could be affected by change in fpd.
7025 for r_entry in process_entries(p_old_final_process_date,p_assignment_id)
7026 loop
7027 --
7028 hr_utility.set_location(l_proc,20);
7029 hr_utility.trace('EE processing type: '||r_entry.processing_type);
7030 hr_utility.trace('Old EE effective end date: '||to_char(r_entry.effective_end_date,'DD-MON-YYYY'));
7031
7032 l_object_version_number := r_entry.object_version_number;
7033 l_new_esd := r_entry.effective_start_date;
7034 l_new_eed := r_entry.effective_end_date;
7035 l_session_date := least(r_entry.effective_end_date, l_new_final_process_date);
7036
7037 if l_new_final_process_date < r_entry.effective_start_date then
7038
7039 /* Code to ZAP the element entry*/
7040 hr_utility.set_location(l_proc,30);
7041
7042 pay_element_entry_api.delete_element_entry
7043 (p_validate => false
7044 ,p_datetrack_delete_mode => 'ZAP'
7045 ,p_effective_date => l_session_date
7046 ,p_element_entry_id => r_entry.element_entry_id
7047 ,p_object_version_number => r_entry.object_version_number
7048 ,p_effective_start_date => l_new_esd
7049 ,p_effective_end_date => l_new_eed
7050 ,p_delete_warning => l_warnings_exist
7051 );
7052
7053 else
7054 if r_entry.processing_type = 'N' then
7055 hr_utility.set_location(l_proc,40);
7056 -- Non-recurring entry
7057 -- Derive new effective end date using chk_element_entry_main
7058 -- This will ensure the payroll period is considered
7059 hr_entry.chk_element_entry_main
7060 (
7061 p_element_entry_id => r_entry.element_entry_id,
7062 p_original_entry_id => r_entry.original_entry_id,
7063 p_session_date => l_session_date,
7064 p_element_link_id => r_entry.element_link_id,
7065 p_assignment_id => r_entry.assignment_id,
7066 p_entry_type => r_entry.entry_type,
7067 p_effective_start_date => l_new_esd,
7068 p_effective_end_date => l_new_eed,
7069 p_validation_start_date => r_entry.effective_start_date,
7070 p_validation_end_date => hr_general.end_of_time,
7071 p_dt_update_mode => 'CORRECTION',
7072 p_dt_delete_mode => null,
7073 p_usage => 'UPDATE',
7074 p_target_entry_id => r_entry.target_entry_id,
7075 p_creator_type => r_entry.creator_type
7076 );
7077 else
7078 hr_utility.set_location(l_proc,50);
7079 -- Recurring entry
7080 -- Derive new effective end date using get_eligibility_period
7081 -- This will ensure entry is not extended beyond lifetime of link
7082 hr_entry.get_eligibility_period
7083 (
7084 p_assignment_id => r_entry.assignment_id,
7085 p_element_link_id => r_entry.element_link_id,
7086 p_session_date => l_session_date,
7087 p_min_eligibility_date => l_new_esd,
7088 p_max_eligibility_date => l_new_eed
7089 );
7090 end if;
7091 --
7092 hr_utility.trace('New EE effective end date: '||to_char(l_new_eed,'DD-MON-YYYY'));
7093 --
7094 -- Now we have the new effective end date
7095 -- Perform checks to make sure we don't move the EED unnecessarily
7096
7097 if l_new_final_process_date > p_old_final_process_date then
7098 -- Moving FPD forwards
7099 -- Only change effective end date of entry if it wasn't earlier than the old FPD
7100 -- i.e. the entry may have been stopped by something other than the termination
7101 -- Added a condition to check that the entries do not have the updating_action_id - which
7102 -- means that the entry was updated by UPDATE_RECURRING or STOP_RECURRING formula result rules.
7103
7104 if r_entry.effective_end_date >= p_old_final_process_date
7105 AND r_entry.updating_action_id IS NULL THEN -- bug 7315564
7106
7107 hr_utility.set_location(l_proc,60);
7108
7109 update pay_element_entry_values_f
7110 set effective_end_date = l_new_eed
7111 where element_entry_id = r_entry.element_entry_id
7112 and p_old_final_process_date between effective_start_date and effective_end_date;
7113
7114 update pay_element_entries_f
7115 set effective_end_date = l_new_eed
7116 where element_entry_id = r_entry.element_entry_id
7117 and p_old_final_process_date between effective_start_date and effective_end_date;
7118
7119 end if;
7120
7121 else
7122 -- Moving FPD backwards
7123 -- Only change effective end date of entry if it is greater than the new FPD
7124 if r_entry.effective_end_date > l_new_final_process_date then
7125
7126 hr_utility.set_location(l_proc,70);
7127
7128 update pay_element_entry_values_f
7129 set effective_end_date = l_new_eed
7130 where element_entry_id = r_entry.element_entry_id
7131 and p_old_final_process_date between effective_start_date and effective_end_date
7132 and l_new_eed >= effective_start_date;
7133
7134 update pay_element_entries_f
7135 set effective_end_date = l_new_eed
7136 where element_entry_id = r_entry.element_entry_id
7137 and p_old_final_process_date between effective_start_date and effective_end_date
7138 and l_new_eed >= effective_start_date;
7139
7140 end if;
7141 end if;
7142 end if;
7143 end loop;
7144
7145 EXCEPTION
7146 --
7147 WHEN OTHERS THEN
7148
7149 ROLLBACK TO move_fpd_entries;
7150 RAISE;
7151
7152 END move_fpd_entries;
7153 --
7154 end hrentmnt;
[Home] [Help]