1 package body hrspine as
2 /* $Header: pespines.pkb 120.2.12010000.2 2008/08/06 09:38:02 ubhat ship $ */
3 --
4 --
5 --Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
6 --
7 /*
8 NAME
9 pespines.pkb
10 ***************************************************************************
11 ********** SEE PROCEDURE SPINE FOR NEW FULL PROCESS DESCRIPTION ***********
12 ***************************************************************************
13
14 DESCRIPTION
15 Procedure for the spinal automatic incrementing process.
16 Its job is to move each entitled employee up to the
17 next point on his pay spine (thus increasing his salary), unless
18 he has reached the maximum point allowed on his grade or
19 assignment. An employee is 'entitled' to be incremented if he
20 is on the input parent spine, and the increment flag on his
21 placement is 'Y'. Additionally he may have to satisfy partial
22 group matching on his assignment flex.
23 --
24 MODULE DESCRIPTION
25 --
26 IF concatenated segments specified
27 LOOP for the number of segments
28 extract and align segments
29 END LOOP
30 END IF
31 LOOP for each assignment
32 --
33 FETCH Cursor - Details of spinal placements entitled to be incremented
34 including the next step each will be set to.
35 - Note this is not neccessarily the next sequence number,
36 but the step with the lowest sequence number for the spine
37 which is greater than the current step's sequence.
38 --
39 UPDATE Placement to end on P_Effective_Run_Date
40 --
41 INSERT New placement starting on P_Effective_Run_Date until the end
42 of time. Has same values as the curent placement.
43 END LOOP
44 --
45 NOTES
46 adapted from an earlier release of a 'C' program for spinal incrementing,
47 first written by AMcI.
48 This PL/SQL procedure is called by the run report screen (SRS). The
49 group keyflex is passed to this procedure as a concatenated string, (or
50 null when no group keyflex is specified). When the concatenated string
51 is null, no group partial matching is performed and all valid employees
52 are incremented.
53 When a People group keyflex has been specified, the procedure has to first
54 separate the concatenated string into the individual segments. A further
55 complication is that the display order of the segments as passed to
56 this procedure may be a different order to the way it is stored on the
57 people group table. The segments are re-aligned to match the segments
58 on the people group table before the partial matching is performed.
59 --
60 MODIFIED (DD-MON-YYYY)
61 mwcallag 17-JUN-1993 - created.
62 rfine 23-NOV-1994 - Suppressed index on business_group_id
63 amills 05-DEC-1995 - Changed date format to DDMMYYYY for
64 translation.
65 dkerr 14-MAR-1996 - 349633 - Removed hard-coded month names
66 jrhodes 18-FEB-1997 - 626703 - Added Application_ID and ID_Flex_Code
67 to query on FND_ID_FLEX_STRUCTURES
68 and FND_ID_FLEX_SEGMENTS
69 stlocke 13-JUN-2001 - Updated the process so that it calls the api
70 rather doing the dml in this package.
71 The process now also passes the increment_number,
72 and performs processing to allow reporting.
73 stlocke 12-DEC-2001 - Added additional params and processing to allow
74 the user to specify a business rule to increment
75 by such as employee's date of birth.
76
77 rem Change List
78 rem ===========
79 rem
80 rem Version Date Author Comment
81 rem -------+-----------+--------------+----------------------------------------
82 rem 115.39 13-MAR-2002 stlocke Changed main select cursors so as to remove
83 rem per_qualifications and hr_soft_coding_keyflex
84 rem tables as if no data in tables then cursor
85 rem will return no rows.
86 rem 115.40 14-MAR-2002 stlocke Commented in commit and exit
87 rem 115.41 25-APR-2002 stlocke Added functions used for employee increment
88 rem results report.
89 rem 115.42 30-APR-2002 stlocke Added code in increment available to see
90 rem if the special ceiling has been set, if so
91 rem the set grade ceiling to same value.
92 rem 115.43 10-MAY-2002 stlocke Fixed issues in employee increment results
93 rem functions for returning last values where
94 rem grade scale has changed but there has been an
95 rem update return null as values for old.
96 rem 115.44 14-MAY-2002 stlocke Updated so that id grade scale ceiling is step
97 rem assignment is on and the special ceiling is
98 rem higher, process will increment assignment.
99 rem 115.45 17-MAY-2002 stlocke Added full details on each section of
100 rem the increment process
101 rem 115.47 06-JUN-2002 stlocke Completed infile documentation.
102 rem 115.49 08-APR-2003 vbanner Added error handling.
103 rem Bug 2444703.
104 rem 115.50 04-JUL-2003 vanantha Bug 2999551.
105 rem Modified cursor csr_assignment_business_rule
106 rem to handle 'leapyear' problem
107 rem 115.51 15-JUN-2005 bshukla Bug 4432200 - Removed hard coding of
108 rem schema 'psp'
109 rem 115.53 12-May-2008 brsinha Bug 6969602. Changed cursor csr_assignment_business_rule
110 rem in the procudre spine.
111 rem ==========================================================================
112 */
113
114 /* ------------------------------------------------------------------- --
115 -- Function to return the spinal point that was updated --
116 -- ------------------------------------------------------------------- */
117
118 Function func_old_spinal_point
119 (p_placement_id in number
120 ,p_effective_start_date in date
121 ,p_step_id in number
122 ,p_grade_rate in number) return varchar2 as
123
124 Cursor csr_old_spinal_point is
125 select substr(psp1.spinal_point,1,20)
126 from per_spinal_point_placements_f spp1,
127 per_spinal_point_placements_f spp,
128 per_spinal_points psp1,
129 per_spinal_point_steps_f sps1,
130 per_spinal_point_steps_f sps2
131 --pay_grade_rules_f pgr2
132 where spp1.step_id <> p_step_id -- 343
133 and spp1.effective_end_date = p_effective_start_date -1
134 and spp.effective_start_date = p_effective_start_date
135 and spp1.placement_id = p_placement_id
136 and psp1.parent_spine_id = spp1.parent_spine_id
137 and spp1.step_id = sps1.step_id
138 and spp.step_id = sps2.step_id
139 and sps1.grade_spine_id = sps2.grade_spine_id
140 and spp.placement_id = spp1.placement_id
141 and sps1.spinal_point_id = psp1.spinal_point_id;
142 --
143 l_old_spinal_point varchar2(20);
144 --
145 begin
146 --
147 open csr_old_spinal_point;
148 fetch csr_old_spinal_point into l_old_spinal_point;
149 if csr_old_spinal_point%notfound then
150
151 -- Need to return a message if there is no previous spinal point
152
153 l_old_spinal_point := 'Updated First Record';
154
155 end if;
156
157 close csr_old_spinal_point;
158
159 return l_old_spinal_point;
160
161 end func_old_spinal_point;
162
163 /* ------------------------------------------------------------------- --
164 -- Function to return the previous spinal value --
165 -- ------------------------------------------------------------------- */
166
167 Function func_old_spinal_value
168 (p_placement_id in number
169 ,p_effective_start_date in date
170 ,p_step_id in number
171 ,p_grade_rate in number) return number as
172
173 Cursor csr_old_spinal_value is
174 select pgr2.value
175 from per_spinal_point_placements_f spp1,
176 per_spinal_point_placements_f spp,
177 per_spinal_points psp1,
178 per_spinal_point_steps_f sps1,
179 pay_grade_rules_f pgr2,
180 per_spinal_point_steps_f sps2
181 where p_step_id <> spp1.step_id
182 and spp1.effective_end_date = p_effective_start_date -1
183 and spp.effective_start_date = p_effective_start_date
184 and spp1.placement_id = p_placement_id
185 and psp1.parent_spine_id = spp1.parent_spine_id
186 and spp1.step_id = sps1.step_id
187 and spp.step_id = sps2.step_id
188 and sps1.grade_spine_id = sps2.grade_spine_id
189 and spp.placement_id = spp1.placement_id
190 and sps1.spinal_point_id = psp1.spinal_point_id
191 and pgr2.grade_or_spinal_point_id = sps1.spinal_point_id
192 and pgr2.rate_id = (select min(rate_id)
193 from pay_grade_rules_f pgr4
194 where pgr4.grade_or_spinal_point_id = pgr2.grade_or_spinal_point_id
195 and (to_number(p_grade_rate) = pgr4.rate_id
196 or to_number(p_grade_rate) is null));
197
198 l_old_spinal_value number;
199
200 begin
201
202 open csr_old_spinal_value;
203 fetch csr_old_spinal_value into l_old_spinal_value;
204 if csr_old_spinal_value%notfound then
205
206 -- If this is the first spinal point of the current grade then return a value of zero
207
208 l_old_spinal_value := 0;
209
210 end if;
211
212 close csr_old_spinal_value;
213
214 return l_old_spinal_value;
215
216 end func_old_spinal_value;
217
218 /* ------------------------------------------------------------------- */
219 -- Function to check that the record was an increment --
220 -- ------------------------------------------------------------------- --
221 -- --
222 -- This process works by opening each cursor stated below to see if --
223 -- they can return a record. If they can then the record should not be --
224 -- displayed for the reason stated above each cursor. --
225 -- --
226 /* ------------------------------------------------------------------- */
227
228 Function func_increment
229 (p_placement_id in number
230 ,p_effective_start_date in date
231 ,p_step_id in number
232 ,p_grade_spine_id in number) return number as
233
234 -- Local variables set for each cursor, set to -1 as this can never be a
235 -- placement id
236
237 l_increment1 number := -1;
238 l_increment2 number := -1;
239 l_increment3 number := -1;
240 l_increment4 number := -1;
241
242 /* *** First Ever SPP Record Catch *** */
243
244 Cursor csr_increment1 is
245 select spp.placement_id
246 from per_spinal_point_placements_f spp
247 where spp.object_version_number = 1
248 and spp.placement_id = p_placement_id
249 and spp.effective_start_date = p_effective_start_date;
250
251 /* *** No step update in record, so no grade increment */
252
253 Cursor csr_increment2 is
254 select spp.placement_id
255 from per_spinal_point_placements_f spp,
256 per_spinal_point_steps_f sps
257 where spp.step_id = p_step_id
258 and spp.placement_id = p_placement_id
259 and spp.effective_end_date = p_effective_start_date -1
260 and sps.grade_spine_id = p_grade_spine_id
261 and sps.step_id = spp.step_id;
262
263 /* *** Step was auto created when grade scale changed on assignment form *** */
264
265 Cursor csr_increment3 is
266 select spp.placement_id
267 from per_spinal_point_placements_f spp,
268 per_spinal_point_steps sps
269 where spp.placement_id = p_placement_id
270 and spp.effective_start_date = p_effective_start_date
271 and sps.grade_spine_id <> p_grade_spine_id
272 and spp.last_updated_by = -1
273 and spp.reason = '';
274
275 /* *** First Spinal Point Of Grade Scale *** */
276
277 Cursor csr_increment4 is
278 select spp.placement_id
279 from per_spinal_point_placements_f spp,
280 per_spinal_point_steps_f sps,
281 per_spinal_points psp
282 where spp.placement_id = p_placement_id
283 and spp.effective_start_date = p_effective_start_date
284 and spp.step_id = p_step_id
285 and sps.step_id = spp.step_id
286 and sps.spinal_point_id = psp.spinal_point_id
287 and psp.spinal_point_id = (select psp1.spinal_point_id
288 from per_spinal_points psp1
289 where psp1.parent_spine_id = spp.parent_spine_id
290 and psp1.sequence = (select min(sequence)
291 from per_spinal_points psp2,
292 per_grade_spines_f pgs
293 where psp2.parent_spine_id = pgs.parent_spine_id
294 and pgs.grade_spine_id = p_grade_spine_id
295 and psp2.parent_spine_id = psp1.parent_spine_id));
296
297 -- Cursor to return all placement id's where they
298 -- have not been found by any of the previous cursors.
299
300 Cursor csr_all_placements is
301 select distinct spp.placement_id
302 from per_spinal_point_placements_f spp,
303 per_spinal_point_steps_f sps
304 where spp.placement_id = p_placement_id
305 and spp.effective_start_date = p_effective_start_date
306 and spp.step_id = sps.step_id
307 and spp.step_id = p_step_id
308 and (spp.placement_id <> l_increment1
309 and spp.placement_id <> l_increment2
310 and spp.placement_id <> l_increment3
311 and spp.placement_id <> l_increment4);
312
313 l_dummy number := -1;
314 l_function varchar2(30) := 'Increment Function';
315
316 begin
317
318 hr_utility.set_location(l_function,10);
319 hr_utility.set_location('p_placement_id - '||p_placement_id,50);
320 hr_utility.set_location('p_effective_start_date - '||p_effective_start_date,50);
321 hr_utility.set_location('p_step_id - '||p_step_id,50);
322 hr_utility.set_location('p_grade_spine_id - '||p_grade_spine_id,50);
323
324 open csr_increment1;
325 fetch csr_increment1 into l_increment1;
326 close csr_increment1;
327
328 hr_utility.set_location('l_increment1 (1st SPP Record) - '||l_increment1,60);
329
330 open csr_increment2;
331 fetch csr_increment2 into l_increment2;
332 close csr_increment2;
333
334 hr_utility.set_location('l_increment2 (No Step Change) - '||l_increment2,60);
335
336 open csr_increment3;
337 fetch csr_increment3 into l_increment3;
338 close csr_increment3;
339
340 hr_utility.set_location('l_increment3 (Grade Scale Auto Change) - '||l_increment3,60);
341
342 open csr_increment4;
343 fetch csr_increment4 into l_increment4;
344 close csr_increment4;
345
346 hr_utility.set_location('l_increment4 (1st Grade Spinal Point) - '||l_increment4,60);
347
348 open csr_all_placements;
349 fetch csr_all_placements into l_dummy;
350 close csr_all_placements;
351
352 hr_utility.set_location('l_dummy (Placement ID to be returned) - '||l_dummy,70);
353
354 hr_utility.set_location('End of '||l_function,80);
355
356 return l_dummy;
357
358 end func_increment;
359
360 /* ------------------------------------------------------------------- */
361 -- Procedure to check if the busines rule is being passed. --
362 -- If it is to test if the effective date is between the business rule --
363 -- date from and the business rule date to. --
364 -- --
365 -- Processing logic of procedure - --
366 -- --
367 -- 1. Check p_business_rule parameter set, if not then check that none --
368 -- of the other business rule parameters are being passed. --
369 -- 2. Check that p_business_rule is a valid business rule --
370 -- 3. Check that both the business rule dates fram and to are set --
371 -- 4. Check that the date_from parameter is not grater than the --
372 -- date_to parameter. --
373 -- 5. Check that the gap between date_from and date_to is not greater --
374 -- than 1 year. --
375 -- 6. Check the dependant_date is valid. --
376 -- 7. Check that the effectvie date is between date_from and date_to. --
377 -- --
378 /* ------------------------------------------------------------------- */
379
380 Procedure business_rule_check
381 (p_effective_date in date
382 ,p_business_rule in varchar2
383 ,p_br_date_from in date
384 ,p_br_date_to in date
385 ,p_year_from in number
386 ,p_year_to in number
387 ,p_dependant_date in varchar2
388 ,p_br_flag out nocopy boolean) is
389
390 l_br_flag boolean;
391 l_br_date_from date;
392 l_br_date_to date;
393 l_package varchar2(30) := 'Business Rule Check';
394
395 begin
396
397 hr_utility.set_location(l_package,1);
398 hr_utility.set_location('p_business_rule - '||p_business_rule,1);
399 hr_utility.set_location('p_br_date_from - '||p_br_date_from,1);
400 hr_utility.set_location('p_br_date_to - '||p_br_date_to,1);
401 hr_utility.set_location('p_dependant_date - '||p_dependant_date,1);
402
403 --
404 -- Check if the business rule has not been set that all other business rule
405 -- only parameters are null, else raise an error.
406 --
407
408 if p_business_rule is null then
409 if p_br_date_from is not null
410 or p_br_date_to is not null
411 or p_dependant_date is not null then
412
413 fnd_message.set_name('PER', 'HR_289506_SPP_BR_NULL');
414 hr_utility.raise_error;
415
416 end if;
417
418 end if;
419
420 --
421 -- Check that the business rule and all dates etc are valid
422 --
423
424 l_br_date_from := p_br_date_from;
425 l_br_date_to := p_br_date_to;
426
427 if p_business_rule is not null then
428
429 --
430 -- Check the business rule is valid
431 --
432 -- Currently business rules available are:
433 --
434 -- Anniversary Of Joining (AOJ)
435 -- Date Of Birth (DOB)
436 -- Adjusted Service Date (ASD)
437 -- Latest Hire Date (LHD)
438 --
439
440 if p_business_rule not IN ('AOJ','DOB','ASD','LHD') then
441
442 fnd_message.set_name('PER', 'HR_289507_SPP_BR_INVALID');
443 hr_utility.raise_error;
444
445 end if;
446
447 --
448 -- Check to see what dates have been passed, if they are both null then set
449 -- them to the effective date, so that the business rule check succeeds.
450 --
451 if p_br_date_from is null
452 or p_br_date_to is null then
453
454 fnd_message.set_name('PER', 'HR_289510_SPP_BR_DATE_NULL');
455 hr_utility.raise_error;
456
457 elsif nvl(l_br_date_to,l_br_date_from) < nvl(l_br_date_from,l_br_date_to) then
458
459 -- The business rule date from is greater than the date to
460 -- so raise an error accordingly
461
462 fnd_message.set_name('PER', 'HR_289500_SPP_BR_DATE');
463 hr_utility.raise_error;
464
465 elsif nvl(l_br_date_to,l_br_date_from) >= nvl(l_br_date_from,l_br_date_to) then
466
467 -- The business rule dates are valid, now check that the gap between is not
468 -- greater than 1 year, coded to include problems with leap year.
469
470 if months_between (l_br_date_to,l_br_date_from) >= 12 then
471
472 -- Gap is greater than one year, so raise error
473
474 fnd_message.set_name('PER', 'HR_289501_SPP_BR_YEAR_GREATER');
475 hr_utility.raise_error;
476
477 end if;
478
479 end if;
480
481 --
482 -- Check that the dependant date is set
483 --
484 if p_dependant_date is null then
485
486 -- raise error as process doesn't know what date to increment assignments on
487
488 fnd_message.set_name('PER', 'HR_289502_SPP_DEPEND_DATE');
489 hr_utility.raise_error;
490
491 end if;
492
493 --
494 -- Check that the effective date is between the date from and the date to
495 --
496
497 if (p_effective_date <= nvl(p_br_date_to,p_effective_date)
498 and p_effective_date >= nvl(p_br_date_from,p_effective_date)) then
499
500 l_br_flag := true;
501
502 else
503
504 --
505 -- Raise error due to effective date not being between business rule dates
506 --
507
508 fnd_message.set_name('PER', 'HR_289503_SPP_EFF_BR_DATE');
509 hr_utility.raise_error;
510
511 end if;
512
513 --
514 -- Check that the year from year is less than the year to year
515 --
516
517 if nvl(p_year_from,p_year_to) > nvl(p_year_to,p_year_from) then
518
519 --
520 -- Raise error due to year from being greater than the year to
521 --
522
523 fnd_message.set_name('PER', 'HR_289504_SPP_BR_YEAR_FROM_TO');
524 hr_utility.raise_error;
525
526 end if;
527
528 else
529
530 -- Business rule is not being used
531
532 l_br_flag := false;
533
534 end if;
535
536 p_br_flag := l_br_flag;
537
538 end business_rule_check;
539
540 /* -------------------------------------------------------------------- */
541 -- Procedure to update the spinal point and then to generate the --
542 -- relevant reports, depending on whether the process complete --
543 -- successfully --
544 -- --
545 -- Processing logic of procedure - --
546 -- --
547 -- 1. Set local update and exception variable from the passed values --
548 -- 2. If l_update = Y then --
549 -- a. Get the next sequence unitl the increment_number = l_count of --
550 -- the sequence fetch. --
551 -- b. Check if the new spinal_point is equal to either the grade max --
552 -- point or the ceiling set. --
553 -- c. Get the step_id for the new point. --
554 -- d. Call to the update_spp process to update the table data. --
555 -- e. Call to the process to populate the what if report to report --
556 -- on the assignments that have been updated. --
557 -- End if --
558 -- 3. If l_exception_report1 = Y then --
559 -- a. Call to the process to populate the exception report to report --
560 -- on assignments that failed to be incremented or that were --
561 -- incremented but hit some kind of ceiling whilst being updated. --
562 -- End if --
563 /* -------------------------------------------------------------------- */
564
565 Procedure update_report_exception
566 (p_datetrack_mode in varchar2
567 ,p_effective_date in date
568 ,p_placement_id in number
569 ,p_object_version_number in number
570 ,p_increment_number in number
571 ,p_reason in varchar2
572 ,p_effective_start_date in date
573 ,p_assignment_id in number
574 ,p_parent_spine_id in number
575 ,p_spinal_point_id in number
576 ,p_rate_id in number
577 ,p_lc_step_id in number
578 ,p_exception_report1 in varchar2
579 ,p_end_date in date
580 ,p_orig_increment_number in number
581 ,p_sequence_number in number
582 ,p_grade_spine_id in number
583 ,p_update in varchar2
584 ,p_max_special_sequence_number in number
585 ,p_max_special_spinal_point in number
586 ,p_max_sequence_number in number
587 ,p_max_spinal_point in number) is
588
589 l_next_sequence_number number;
590 l_next_spinal_point number;
591 l_exception_report1 varchar2(2);
592 l_update varchar2(2);
593 l_zero number :=0; -- constant zero
594 l_new_step_id number;
595 l_effective_start_date date;
596 l_effective_end_date date;
597 l_increment number;
598 l_object_version_number number;
599
600 --
601 -- cursor to get the next sequence number
602 -- and the next spinal point for the grade
603 --
604 cursor csr_next_sequence(p_sequence number,
605 p_grade_spine_id number,
606 p_effective_date date) is
607 select sequence,spinal_point_id
608 from per_spinal_point_steps_f
609 where sequence > p_sequence
610 and grade_spine_id = p_grade_spine_id
611 and p_effective_date between effective_start_date
612 and effective_end_date
613 order by sequence;
614 --
615 begin
616 --
617 l_update := p_update;
618 l_exception_report1 := p_exception_report1;
619 --
620 -- ------------------------------------------------------------------
621 -- gets the sequence number and the next spinal point id
622 -- of the grade and loops until l_zero = increment by number
623 -- ------------------------------------------------------------------
624 if l_update = 'Y' then
625
626 open csr_next_sequence(p_sequence_number,
627 p_grade_spine_id,
628 p_effective_date);
629 loop
630
631 FETCH csr_next_sequence into l_next_sequence_number,
632 l_next_spinal_point;
633
634 if csr_next_sequence%notfound then
635 hr_utility.set_location('MAX POINT REACHED!',20);
636 l_update := 'N';
637 l_exception_report1 := 'Y';
638 exit;
639
640 else
641 hr_utility.set_location('FOUND',21);
642 l_zero := l_zero+1;
643 l_update := 'Y';
644
645 if p_increment_number = l_zero then
646 exit;
647 end if;
648 end if;
649 end loop;
650 close csr_next_sequence;
651
652 l_zero := 0;
653 --
654 -- ------------------------------------------------------------------
655 -- Check if the new spinal_point_id is equal to either of the max
656 -- spinal point id's. If so report after update
657 -- ------------------------------------------------------------------
658 --
659 if (l_next_sequence_number = p_max_sequence_number
660 and l_next_spinal_point = p_max_spinal_point)
661 or (l_next_sequence_number = p_max_special_sequence_number
662 and l_next_spinal_point = p_max_special_spinal_point)
663 then
664 l_exception_report1 := 'Y';
665 end if;
666 --
667 -- ------------------------------------------------------------------
668 -- select statement to get new step id for assignment
669 -- ------------------------------------------------------------------
670 --
671 BEGIN
672 hr_utility.set_location('get step id',1313);
673 hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1313);
674 hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1313);
675 hr_utility.set_location('l_next_sequence_number '
676 ||l_next_sequence_number,1313);
677 select step_id
678 into l_new_step_id
679 from per_spinal_point_steps_f
680 where sequence = l_next_sequence_number
681 and grade_spine_id = p_grade_spine_id
682 and spinal_point_id = l_next_spinal_point;
683 EXCEPTION
684 WHEN NO_DATA_FOUND
685 THEN
686 hr_utility.set_location('NO_DATA_FOUND',1314);
687 hr_utility.set_location('step id',1314);
688 hr_utility.set_location('not found for l_next_sequence_number'
689 ||l_next_sequence_number, 1314);
690 hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id,1314);
691 hr_utility.set_location('l_next_spinal_point '||l_next_spinal_point,1314);
692 END;
693 --
694 -- Now update all the records using the api
695 --
696 l_effective_start_date := p_effective_start_date;
697 l_effective_end_date := p_end_date;
698 l_object_version_number := p_object_version_number;
699 --
700 --
701 hr_sp_placement_api.update_spp
702 (p_datetrack_mode => p_datetrack_mode
703 ,p_effective_date => P_Effective_Date
704 ,p_placement_id => p_placement_id
705 ,p_object_version_number => l_object_version_number
706 ,p_step_id => l_new_step_id
707 ,p_increment_number => p_increment_number
708 ,p_reason => p_reason
709 ,p_effective_start_date => l_effective_start_date
710 ,p_effective_end_date => l_effective_end_date
711 );
712 --
713 hr_utility.set_location ('Before HR_SPP_EXCEPTION_DATA.populate_spp_table',101);
714 hr_utility.set_location ('l_exception_report_1 = '||l_exception_report1,151);
715 --
716 ----------------------------------------------------------------------------
717 -- Update the what if table with the updated record
718 -- --------------------------------------------------------------------------
719 hr_utility.set_location ('HR_SPP_WI_DATA.populate_spp_wi_table',121);
720 hr_utility.set_location ('HR_SPP_WI_DATA - Placement:'||p_placement_id,121);
721 hr_utility.set_location ('HR_SPP_WI_DATA - Assignment:'||p_assignment_id,121);
722 --
723 HR_SPP_WI_DATA.populate_spp_wi_table
724 (p_placement_id => p_placement_id
725 ,p_assignment_id => p_assignment_id
726 ,p_effective_date => P_Effective_Date
727 ,p_parent_spine_id => p_parent_spine_id
728 ,p_step_id => l_new_step_id
729 ,p_spinal_point_id => p_spinal_point_id
730 ,p_rate_id => p_rate_id);
731 --
732 l_update := 'Y';
733
734 else
735
736 l_new_step_id := p_lc_step_id;
737
738 end if;
739
740
741 -- --------------------------------------------------------------------------
742 -- Now if l_exception_report_1 or l_exception_report_2 is set to 'Y' then the
743 -- record has to be reported
744 -- --------------------------------------------------------------------------
745 --
746 if (l_exception_report1 = 'Y')
747 then
748 hr_utility.set_location
749 ('Populating HR_SPP_EXCEPTION_DATA.populate_spp_table',102);
750 -- vik failing with no data found here.
751 -- but why doesn't the comment show up on the trace?
752 HR_SPP_EXCEPTION_DATA.populate_spp_table
753 (p_effective_date => P_Effective_Date
754 ,p_placement_id => p_placement_id
755 ,p_effective_start_date => p_effective_start_date
756 ,p_effective_end_date => p_end_date
757 ,p_assignment_id => p_assignment_id
758 ,p_parent_spine_id => p_parent_spine_id
759 ,p_increment_number => p_increment_number
760 ,p_original_increment_number => p_orig_increment_number
761 ,p_sequence_number => p_sequence_number
762 ,p_next_sequence_number => l_next_sequence_number
763 ,p_spinal_point_id => p_spinal_point_id
764 ,p_step_id => p_lc_step_id
765 ,p_new_step_id => l_new_step_id
766 ,p_grade_spine_id => p_grade_spine_id
767 ,p_update => l_update
768 );
769 --
770 end if;
771 --
772 end update_report_exception;
773 --
774 /* -------------------------------------------------------------------- */
775 -- Process to check that the current assignment can be incremented --
776 -- Also retrieves relevant values to use --
777 -- --
778 -- Processing logic of procedure - --
779 -- --
780 -- 1. Set datetrack mode according to the end date. --
781 -- 2. Get step_id and seuence number for future step record if it --
782 -- exists. --
783 -- 3. Get max ceiling step for grade scale and the assignment special --
784 -- ceiling step, then set to highest step based on sequence. --
785 -- 4. Check if assignment is already at a limit. --
786 -- 5. Get sequence for the ceiling. --
787 -- 6. Get max number of steps left on the current grade. --
788 -- 7. Compare the increment number and this number ad set accordingly. --
789 -- --
790 /* -------------------------------------------------------------------- */
791
792 procedure check_increment_available
793 (p_placement_id in number
794 ,p_end_date in date
795 ,p_effective_date in date
796 ,p_effective_start_date in date
797 ,p_datetrack_mode in out nocopy varchar2
798 ,p_assignment_id in number
799 ,p_parent_spine_id in number
800 ,p_grade_spine_id in number
801 ,p_step_id in number
802 ,p_sequence_number in number
803 ,p_increment_number in number
804 ,p_exception_report1 out nocopy varchar2
805 ,p_update out nocopy varchar2
806 ,p_increment out nocopy number
807 ,p_max_sequence_number out nocopy number
808 ,p_max_special_sequence_number out nocopy number
809 ,p_max_spinal_point_id out nocopy number
810 ,p_max_special_spinal_point_id out nocopy number) is
811
812
813 l_max_end_date date;
814 l_datetrack_mode varchar2(30);
815 l_future_sequence_id number;
816 l_update varchar2(2); -- Able to update record
817 l_increment number; -- new increment number
818 l_max_ceiling_step_id number;
819 l_max_ceiling_sequence number;
820 l_special_ceiling_sequence number;
821 l_special_ceiling_step_id number;
822 l_grade_max_step_id number;
823 l_grade_max_sequence number;
824 l_max_sequence_number number;
825 l_max_special_sequence_number number;
826 l_max_spinal_point_id number;
827 l_max_special_spinal_point_id number;
828 l_max_count number;
829 l_exception_report1 varchar2(2);
830 l_future_grade_spine_id number;
831
832
833 begin
834
835 hr_utility.set_location('Entering check_increment_available procedure',5);
836
837 l_future_sequence_id := null;
838 l_update := 'Y';
839
840 -- ------------------------------------------------------------------
841 -- set the datetrack mode according to the end date
842 -- ------------------------------------------------------------------
843 BEGIN
844 select max(effective_end_date)
845 into l_max_end_date
846 from per_spinal_point_placements_f
847 where placement_id = p_placement_id;
848 EXCEPTION
849 WHEN NO_DATA_FOUND
850 THEN
851 hr_utility.set_location('NO_DATA_FOUND',1315);
852 hr_utility.set_location('max(effective_end_date) ',1315);
853 hr_utility.set_location('not found for p_placement_id '
854 ||p_placement_id, 1315);
855 END;
856 --
857 if (p_end_date = l_max_end_date)
858 and (P_Effective_Date <> p_effective_start_date)
859 then
860 hr_utility.set_location('Datetrack mode = UPDATE',10);
861 l_datetrack_mode := 'UPDATE';
862 elsif
863 (P_Effective_Date = p_effective_start_date)
864 then
865 hr_utility.set_location('Datetrack mode = CORRECTION',10);
866 l_datetrack_mode := 'CORRECTION';
867 else
868 hr_utility.set_location('Datetrack mode = UPDATE_CHANGE_INSERT',10);
869 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
870 end if;
871 --
872 p_datetrack_mode := l_datetrack_mode;
873 --
874 -- ------------------------------------------------------------------
875 -- Get the step_id,sequence number for the future record
876 -- ------------------------------------------------------------------
877 hr_utility.set_location('max_end_date:'||l_max_end_date,170);
878 hr_utility.set_location('L_End_Date:'||p_end_date,170);
879 hr_utility.set_location('p_placement_id - '||p_placement_id,333);
880 if p_end_date <> hr_api.g_eot
881 and p_end_date <> l_max_end_date
882 then
883 BEGIN
884 select nvl(sps.sequence,-99),nvl(sps.grade_spine_id,-99)
885 into l_future_sequence_id,
886 l_future_grade_spine_id
887 from per_spinal_point_steps_f sps,
888 per_spinal_point_placements_f spp
889 where spp.effective_start_date = p_end_date + 1
890 and spp.placement_id = p_placement_id
891 and spp.step_id = sps.step_id;
892 EXCEPTION
893 WHEN NO_DATA_FOUND
894 THEN
895 hr_utility.set_location('NO_DATA_FOUND',1316);
896 hr_utility.set_location('sps.grade_spine_id ',1316);
897 hr_utility.set_location('not found for p_placement_id '
898 ||p_placement_id, 1316);
899 END;
900 --
901 if nvl(l_future_grade_spine_id,-99) <> p_grade_spine_id
902 then
903 --
904 l_future_sequence_id := -99;
905 --
906 end if;
907 --
908 -- Report on the future record
909 --
910 l_exception_report1 := 'Y';
911 --
912 end if;
913 --
914 hr_utility.set_location('Ceiling fetch',170);
915 --
916 -- ------------------------------------------------------------------
917 -- Get the max ceiling step_id for the pay scale and the special
918 -- ceiling step id for the assignment
919 -- ------------------------------------------------------------------
920 BEGIN
921 select pgs.ceiling_step_id, nvl(paa.special_ceiling_step_id,pgs.ceiling_step_id)
922 into l_max_ceiling_step_id,l_special_ceiling_step_id
923 from per_grade_spines_f pgs,
924 per_all_assignments_f paa
925 where paa.assignment_id = p_assignment_id
926 and pgs.parent_spine_id = p_parent_spine_id
927 and pgs.grade_spine_id = p_grade_spine_id
928 and pgs.grade_id = paa.grade_id
929 and P_Effective_Date between pgs.effective_start_date
930 and pgs.effective_end_date
931 and P_Effective_Date between paa.effective_start_date
932 and paa.effective_end_date;
933 EXCEPTION
934 WHEN NO_DATA_FOUND
935 THEN
936 hr_utility.set_location('NO_DATA_FOUND',1400);
937 hr_utility.set_location('pgs ceiling_step_id ',1400);
938 hr_utility.set_location('not found for p_assignment_id'
939 ||p_assignment_id, 1400);
940 hr_utility.set_location('p_parent_spine_id '||p_parent_spine_id, 1400);
941 hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1400);
942 END;
943 --
944 hr_utility.set_location('Max step and sequence fetch',170);
945
946 if l_max_ceiling_step_id <> l_special_ceiling_step_id then -- 115.42 Update
947
948 l_max_ceiling_step_id := l_special_ceiling_step_id;
949
950 end if;
951 --
952 BEGIN
953 select sequence
954 into l_special_ceiling_sequence
955 from per_spinal_point_steps_f sps
956 where sps.grade_spine_id = p_grade_spine_id
957 and sps.step_id = l_special_ceiling_step_id
958 and p_effective_date between effective_start_date and effective_end_date;
959 EXCEPTION
960 WHEN NO_DATA_FOUND
961 THEN
962 hr_utility.set_location('NO_DATA_FOUND',1500);
963 hr_utility.set_location('special_ceiling_sequence ',1500);
964 hr_utility.set_location('not found for l_special_ceiling_step_id '
965 ||l_special_ceiling_step_id, 1500);
966 hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
967 END;
968 -- ------------------------------------------------------------------
969 -- Get the max step_id and seuence for the grade
970 -- ------------------------------------------------------------------
971 BEGIN
972 select sps.step_id, sequence
973 into l_grade_max_step_id, l_grade_max_sequence
974 from per_spinal_point_steps_f sps
975 where sps.grade_spine_id = p_grade_spine_id
976 and sps.sequence = (select max(sequence)
977 from per_spinal_point_steps_f psp1
978 where grade_spine_id = p_grade_spine_id
979 and p_effective_date
980 between effective_start_date and effective_end_date);
981 EXCEPTION
982 WHEN NO_DATA_FOUND
983 THEN
984 hr_utility.set_location('NO_DATA_FOUND',1500);
985 hr_utility.set_location('sequence and step id',1500);
986 hr_utility.set_location('not found for max grade sequence and ', 1500);
987 hr_utility.set_location('p_grade_spine_id '||p_grade_spine_id, 1500);
988 END;
989 --
990 if l_special_ceiling_sequence <> l_grade_max_sequence
991 then
992 l_grade_max_step_id := l_special_ceiling_step_id;
993 l_grade_max_sequence := l_special_ceiling_sequence;
994 end if;
995 --
996 hr_utility.set_location(' ***** ANOTHER PROBLEM ***** ',333);
997 hr_utility.set_location('l_grade_max_step_id - '||l_grade_max_step_id,333);
998 hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
999 hr_utility.set_location('l_special_ceiling_step_id - '||l_special_ceiling_step_id,333);
1000 hr_utility.set_location('l_special_ceiling_sequence - '||l_special_ceiling_sequence,333);
1001 hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1002 --
1003 if l_future_sequence_id = -99 or l_future_sequence_id is null
1004 then
1005 l_future_sequence_id := l_grade_max_sequence;
1006 end if;
1007 --
1008 hr_utility.set_location('l_grade_max_step_id:'||l_grade_max_step_id,200);
1009 hr_utility.set_location('l_grade_max_sequence:'||l_grade_max_sequence,200);
1010 --
1011 -- ------------------------------------------------------------------
1012 -- Check if assignment already at a grade limit
1013 -- ------------------------------------------------------------------
1014 hr_utility.set_location('Ceiling Reached?',201);
1015 hr_utility.set_location('Step ID '||p_step_id,201);
1016 hr_utility.set_location('l_special_ceiling_step_id '||l_special_ceiling_step_id,201);
1017 hr_utility.set_location('max_ceiling_step_id '||l_max_ceiling_step_id,201);
1018 --
1019 if (l_special_ceiling_step_id = p_step_id
1020 or l_grade_max_step_id = p_step_id)
1021 then
1022 l_update := 'N';
1023 l_exception_report1 := 'Y';
1024 end if;
1025 --
1026 hr_utility.set_location('l_update '||l_update,201);
1027 --
1028 -- ------------------------------------------------------------------
1029 -- Get the sequence number for the two ceiling step id's along with
1030 -- the spinal_point_id for the max ceiling
1031 -- ------------------------------------------------------------------
1032 hr_utility.set_location('sequence number for the two ceiling',202);
1033 hr_utility.set_location(l_max_ceiling_step_id,203);
1034 hr_utility.set_location(l_special_ceiling_step_id,204);
1035 BEGIN
1036 select psp1.sequence,
1037 nvl(psp2.sequence,99999999),
1038 psp1.spinal_point_id,
1039 psp2.spinal_point_id
1040 into l_max_sequence_number,
1041 l_max_special_sequence_number,
1042 l_max_spinal_point_id,
1043 l_max_special_spinal_point_id
1044 from per_spinal_points psp1,
1045 per_spinal_points psp2,
1046 per_spinal_point_steps_f sps1,
1047 per_spinal_point_steps_f sps2
1048 where psp1.spinal_point_id = sps1.spinal_point_id
1049 and psp2.spinal_point_id = sps2.spinal_point_id
1050 and sps1.step_id = l_max_ceiling_step_id
1051 and sps2.step_id = l_special_ceiling_step_id;
1052 EXCEPTION
1053 WHEN NO_DATA_FOUND
1054 THEN
1055 hr_utility.set_location('NO_DATA_FOUND',1600);
1056 hr_utility.set_location('sequence and point ids ',1600);
1057 hr_utility.set_location('not found for l_max_ceiling_step_id '
1058 ||l_max_ceiling_step_id, 1600);
1059 hr_utility.set_location('and l_special_ceiling_step_id '
1060 ||l_special_ceiling_step_id, 1600);
1061 END;
1062 --
1063 -- ------------------------------------------------------------------
1064 -- Select the max number of steps left for the grade - Also compare
1065 -- the two limits and stop if reached
1066 -- ------------------------------------------------------------------
1067 --
1068 hr_utility.set_location('******* CEILING ERROR CATCH *********',333);
1069 hr_utility.set_location('l_max_sequence_number - '||l_max_sequence_number,333);
1070 hr_utility.set_location('l_max_special_sequence_number - '||l_max_special_sequence_number,333);
1071 hr_utility.set_location('l_grade_max_sequence - '||l_grade_max_sequence,333);
1072 hr_utility.set_location('l_future_sequence_id - '||l_future_sequence_id,333);
1073 hr_utility.set_location('p_sequence_number - '||p_sequence_number,333);
1074 BEGIN
1075 select count(sps.sequence)
1076 into l_max_count
1077 from per_spinal_point_steps_f sps
1078 where sps.sequence > p_sequence_number
1079 and sps.grade_spine_id = p_grade_spine_id
1080 and sps.sequence <= l_max_sequence_number
1081 and sps.sequence <= l_max_special_sequence_number
1082 and sps.sequence <= l_grade_max_sequence
1083 and sps.sequence <= l_future_sequence_id
1084 and p_effective_date between sps.effective_start_date
1085 and sps.effective_end_date
1086 order by sps.sequence;
1087 EXCEPTION
1088 WHEN NO_DATA_FOUND
1089 THEN
1090 hr_utility.set_location('NO_DATA_FOUND',1700);
1091 hr_utility.set_location('max spinal point count ',1700);
1092 hr_utility.set_location('not found for p_grade_spine_id '
1093 ||p_grade_spine_id, 1700);
1094 hr_utility.set_location('and > p_sequence_number '
1095 ||p_sequence_number, 1700);
1096 hr_utility.set_location('l_max_sequence_number '
1097 ||l_max_sequence_number, 1700);
1098 hr_utility.set_location('l_max_special_sequence_number '
1099 ||l_max_special_sequence_number, 1700);
1100 hr_utility.set_location('l_future_sequence_id '
1101 ||l_future_sequence_id, 1700);
1102 hr_utility.set_location('l_grade_max_sequence '
1103 ||l_grade_max_sequence, 1700);
1104 END;
1105 hr_utility.set_location ('hrspine.spine', 1222);
1106 hr_utility.set_location ('count(sps.sequence):'||L_Max_Count,12222);
1107 -- ------------------------------------------------------------------
1108 -- Checks the increment by number is not greater than
1109 -- the max number of steps left for the grade
1110 -- ------------------------------------------------------------------
1111 --
1112 hr_utility.set_location('Assign Value to l_increment',140);
1113 --
1114 l_increment := p_increment_number;
1115 --
1116 hr_utility.set_location('Value = '||l_increment,140);
1117 hr_utility.set_location('Max Count Value = '||L_Max_Count,141);
1118 --
1119 if l_increment > L_Max_Count
1120 then
1121 l_increment := L_Max_Count;
1122 l_exception_report1 := 'Y';
1123 end if;
1124 --
1125 if L_Max_Count = 0
1126 then
1127 hr_utility.set_location('L_Max_Count = 0 so update denied',141);
1128 l_update := 'N';
1129 end if;
1130 --
1131 p_increment := l_increment;
1132 p_update := l_update;
1133 p_exception_report1 := l_exception_report1;
1134 l_max_sequence_number := p_max_sequence_number;
1135 l_max_special_sequence_number := p_max_special_sequence_number;
1136 l_max_spinal_point_id := p_max_spinal_point_id;
1137 l_max_special_spinal_point_id := p_max_special_spinal_point_id;
1138 --
1139 hr_utility.set_location('Exiting check_increment_available procedure',15);
1140 --
1141 end check_increment_available;
1142 --
1143 /* -------------------------------------------------------------------- */
1144 -- Procedure checks that other dependant parameters are all valid --
1145 -- -------------------------------------------------------------------- --
1146 -- --
1147 -- Check that where parameters depend on other parameters to be --
1148 -- processed that they exist and are valid. For instance if --
1149 -- qualification status is not null, qualification type must also not --
1150 -- be null. --
1151 -- --
1152 -- Processing logic of procedure - --
1153 -- --
1154 -- 1. Check organization structure parameters. --
1155 -- 2. Check qualification parameters. --
1156 -- 3. Check legal entity parameters. --
1157 -- --
1158 /* -------------------------------------------------------------------- */
1159 --
1160 procedure constraint_check
1161 (p_org_structure_top_node in number default null
1162 ,p_org_structure_ver_id in number default null
1163 ,p_qual_status in varchar2 default null
1164 ,p_qual_type in number default null
1165 ,p_business_group_id in number
1166 ,p_legal_entity in number default null) is
1167 --
1168 l_dummy number;
1169 --
1170 --
1171 -- Legal Entity check cursor
1172 --
1173 cursor csr_legal_entity is
1174 select 1
1175 from hr_legal_entities
1176 where organization_id = p_legal_entity
1177 and business_group_id = p_business_group_id;
1178 --
1179 begin
1180 --
1181 --
1182 -- Check if org_structure - Either both parameters have to be passed
1183 -- or neither.
1184 --
1185
1186 if p_org_structure_top_node is not null
1187 or p_org_structure_ver_id is not null
1188 then
1189 if p_org_structure_top_node is null
1190 or p_org_structure_ver_id is null
1191 then
1192 -- Raise error
1193 fnd_message.set_name('PER', 'HR_289280_SPP_INC_ORG_STRUCT');
1194 hr_utility.raise_error;
1195 end if;
1196 end if;
1197 --
1198 -- Check qualifications - Either both parameters have to be passed
1199 -- or neither.
1200 --
1201 if p_qual_status is not null
1202 then
1203 if p_qual_type is null
1204 then
1205 -- Raise error
1206 fnd_message.set_name('PER', 'HR_289290_SPP_INC_QUAL');
1207 hr_utility.raise_error;
1208 end if;
1209 end if;
1210 --
1211 --
1212 -- Legal Entity check - US only
1213 --
1214 --
1215 if p_legal_entity is not null then
1216 open csr_legal_entity;
1217 fetch csr_legal_entity into l_dummy;
1218 if csr_legal_entity%notfound then
1219 fnd_message.set_name('PER', 'HR_289291_SPP_INC_LEGAL_ENTITY');
1220 hr_utility.raise_error;
1221 end if;
1222 close csr_legal_entity;
1223 end if;
1224 --
1225 end constraint_check;
1226 --
1227 /*----------------------------- spine ---------------------------------*/
1228 /*
1229 NAME
1230 spine - entry point for the spinal incrementing process
1231 -----------
1232 DESCRIPTION
1233 -----------
1234 The process has been updated to supply many new features to the increment
1235 process. The first and most import one is that an assignment can be
1236 incremented by greater than just one point. This is determined by a new
1237 parameter on the grade step placement form, Increment Number, this is a
1238 mandatory parameter. This allows the user to change the number of steps
1239 that an assignment goes up by each time.
1240 (Within this process there is a check procedure that finds the number of
1241 valid steps left for the assignment on its current grade and if the
1242 increment number is greater than this then the number is set to the number
1243 of steps left on the grade.)
1244
1245 The second main new feature is the abbility to run the process in a 'What If'
1246 mode. What this means is that you can run the increment process in a rollback
1247 mode. Because of the addition or reporting that has been added to the process
1248 it is possible to run the Increment process but without commiting the database,
1249 so that you can see who is incremented and by how much, who is not incremented,
1250 and who is incremented but reaches some kind of ceiling during the process.
1251
1252 There has also been the addition of many new parameters to the Increment process
1253 to allow the user to restrict which assignments are incremented.
1254
1255 In addition to standard restriction parameters that maps to table columns there
1256 has also been the addition of business rule parameters. What these do is to
1257 allow the user to increment an assignment based on say the employee's date of
1258 birth. This also allows the restriction for a date period, so you could specify
1259 that you wish to increment all those employee's that have a birthday in march.
1260
1261 There are also an additional two parameters that allow you to specify an age range,
1262 so for the example just given you could also specify 21 - 30 so you would only
1263 increment employee's who had a birthday in march and were between the ages of 21
1264 to 30. This increment will be performed based on the business rule effective date.
1265 This means that the increment can be done on the date of birth for each employee,
1266 the start of the next month etc.
1267 */
1268
1269
1270 /* -------------------------------------------------------------------- */
1271 -- Main Entry Point To Increment Procedure --
1272 -- -------------------------------------------------------------------- --
1273 -- --
1274 -- Depending on if the business rule parameters have been set call the --
1275 -- correct code and try to increment the valid assignments. --
1276 -- --
1277 -- Processing logic of procedure - --
1278 -- --
1279 -- 1. Check business group id passed is valid. --
1280 -- 2. Call the businss rule check procedure. --
1281 -- 3. Call the constraint check procedure --
1282 -- 4. If the concat segs parameter is not null then correctly set --
1283 -- concat seg values. --
1284 -- 5. If business_rule is null then --
1285 -- a. Call check increment available procedure. --
1286 -- b. Call update report exception procedure. --
1287 -- --
1288 -- 6. Else if business_rule is not null then --
1289 -- a. Check date_from and date_to parameters for year overlap. --
1290 -- b. Set local dates / years accordingly. --
1291 -- c. Call cursor to return all assignment info for business rule --
1292 -- process, loop following until no more rows. --
1293 -- d. When no data found set the local variables to null. --
1294 -- e. If l_first_call flag = FALSE then --
1295 -- 1. Check if the copied assign param matches fetched assign_id --
1296 -- and set local parameters accordingly. --
1297 -- 2. Depending on business rule selected, check dates for copy --
1298 -- to fetch version and set local params accordingly. --
1299 -- 3. Check if the year loops over an end of year and set the --
1300 -- appropriate year accordingly etc. --
1301 -- 4. Depending on the dependant date set the effective dates --
1302 -- accordingly. --
1303 -- 5. Fetch details of assignment to be incremented using main --
1304 -- select cursor. --
1305 -- 6. Call check increment available procedure. --
1306 -- 7. Call update report exception procedure. --
1307 -- 7. Update table per_parent_spines to set the date for --
1308 -- last_automatic_increment_date. --
1309 -- --
1310 /* -------------------------------------------------------------------- */
1311
1312 procedure spine
1313 (
1314 P_Parent_Spine_ID in number default null,
1315 P_Effective_Date in date,
1316 p_id_flex_num in number default null,
1317 p_concat_segs in varchar2 default null,
1318 P_Business_Group_ID in number,
1319 p_collective_agreement_id in number default null,
1320 p_person_id in number default null,
1321 p_payroll_id in number default null,
1322 p_organization_id in number default null,
1323 p_legal_entity in number default null,
1324 p_org_structure_ver_id in number default null,
1325 p_qual_type in number default null,
1326 p_qual_status in varchar2 default null,
1327 p_org_structure_top_node in number default null,
1328 p_rate_id in number default null,
1329 p_business_rule in varchar2 default null,
1330 p_dependant_date in varchar2 default null,
1331 p_br_date_from in date default null,
1332 p_br_date_to in date default null,
1333 p_year_from in number default null,
1334 p_year_to in number default null,
1335 p_message_number out nocopy varchar2
1336 ) is
1337 --
1338 -- this cursor is used to get the order of the segments from the foundation
1339 -- table
1340 --
1341 cursor c1 is
1342 select application_column_name
1343 from fnd_id_flex_segments
1344 where id_flex_num = p_id_flex_num
1345 and application_id = 801
1346 and id_flex_code = 'GRP'
1347 and enabled_flag = 'Y'
1348 order by segment_num;
1349 --
1350 -- the list of local segments:
1351 --
1352 l_seg1 pay_people_groups.segment1%type;
1353 l_seg2 pay_people_groups.segment2%type;
1354 l_seg3 pay_people_groups.segment3%type;
1355 l_seg4 pay_people_groups.segment4%type;
1356 l_seg5 pay_people_groups.segment5%type;
1357 l_seg6 pay_people_groups.segment6%type;
1358 l_seg7 pay_people_groups.segment7%type;
1359 l_seg8 pay_people_groups.segment8%type;
1360 l_seg9 pay_people_groups.segment9%type;
1361 l_seg10 pay_people_groups.segment10%type;
1362 l_seg11 pay_people_groups.segment11%type;
1363 l_seg12 pay_people_groups.segment12%type;
1364 l_seg13 pay_people_groups.segment13%type;
1365 l_seg14 pay_people_groups.segment14%type;
1366 l_seg15 pay_people_groups.segment15%type;
1367 l_seg16 pay_people_groups.segment16%type;
1368 l_seg17 pay_people_groups.segment17%type;
1369 l_seg18 pay_people_groups.segment18%type;
1370 l_seg19 pay_people_groups.segment19%type;
1371 l_seg20 pay_people_groups.segment20%type;
1372 l_seg21 pay_people_groups.segment21%type;
1373 l_seg22 pay_people_groups.segment22%type;
1374 l_seg23 pay_people_groups.segment23%type;
1375 l_seg24 pay_people_groups.segment24%type;
1376 l_seg25 pay_people_groups.segment25%type;
1377 l_seg26 pay_people_groups.segment26%type;
1378 l_seg27 pay_people_groups.segment27%type;
1379 l_seg28 pay_people_groups.segment28%type;
1380 l_seg29 pay_people_groups.segment29%type;
1381 l_seg30 pay_people_groups.segment30%type;
1382 --
1383 l_seg_value pay_people_groups.segment1%type;
1384 l_concat_sep varchar2(1); -- the concatenated delimiter
1385 l_count number := 0; -- the count of the individual segments
1386 l_pos number := 1; -- the position of the individual segment
1387 l_pos_sep number; -- the position of the separator
1388 l_length number; -- the length of the string
1389 l_message_number_count number;
1390 --
1391 l_increment number; -- local increment number
1392 LC_Placement_ID number;
1393 LC_Step_ID number;
1394 LC_Assignment_ID number;
1395 LC_Auto_Increment_Flag varchar2 (1);
1396 LC_Spinal_Point_ID number;
1397 LC_New_Step_ID number;
1398 LC_Parent_Spine_ID number;
1399 L_Pass_String varchar2 (16) := '***************';
1400 L_End_Date date;
1401 LC_Sequence_Number number; -- current sequence number
1402 LC_Increment_Number number; -- increment number for assignment
1403 LC_Effective_Start_Date date;
1404 --
1405 --
1406 -- Parameters added when api created for dml on per_spinal_point_placements_f
1407 --
1408 l_datetrack_mode varchar2(30) := 'UPDATE_CHANGE_INSERT';
1409 l_reason varchar2(30) := 'AI';
1410 LC_Object_Version_Number per_spinal_point_placements_f.object_version_number%TYPE;
1411 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
1412 l_grade_spine_id per_grade_spines_f.grade_spine_id%TYPE;
1413 l_update varchar2(2);
1414 l_update2 varchar2(2);
1415 l_exception_report1 varchar2(2);
1416 l_exception_report2 varchar2(2);
1417 --
1418 /* Parameters added for temp addition to process for business rule inclusion */
1419 --
1420 l_br_flag boolean;
1421 l_effective_date date;
1422 l_max_sequence_number number;
1423 l_max_special_sequence_number number;
1424 l_max_spinal_point_id number;
1425 l_max_special_spinal_point_id number;
1426 l_br_assignment_id number;
1427 l_br_date_of_birth date;
1428 l_br_annivarsary_of_joining date;
1429 l_br_latest_hire_date date;
1430 l_br_adjusted_svc_date date;
1431 l_payroll_id number;
1432 l_copy_br_assignment_id number;
1433 l_copy_br_date_of_birth date;
1434 l_copy_br_anniv_of_joining date;
1435 l_copy_br_latest_hire_date date;
1436 l_copy_br_adjusted_svc_date date;
1437 l_copy_payroll_id number;
1438 l_duplicate_flag boolean := FALSE;
1439 l_duplicate_error_flag boolean := FALSE;
1440 l_first_call_flag boolean := TRUE;
1441 l_br_update varchar2(2) := 'N';
1442 l_temp_year varchar2(30);
1443 l_effective_year varchar2(30);
1444 l_effective_month varchar2(30);
1445 l_earliest_start_date date := to_date('01/01/1901','DD/MM/YYYY');
1446 --
1447 l_br_date_from_temp1 varchar2(30);
1448 l_br_date_to_temp1 varchar2(30);
1449 l_year_from_temp varchar2(5);
1450 l_year_to_temp varchar2(5);
1451 l_year_temp varchar2(5);
1452 --
1453 l_br_date_from date;
1454 l_br_date_to date;
1455 l_year_loop varchar2(2);
1456 l_year_loop_count number := 1;
1457 --
1458 -- Main select cursor to find all assignments that are eligble to have an
1459 -- increment
1460 -- This returns all data needed to perform the increment
1461 -- All variables from increment process are in where clause of cursor
1462 --
1463 CURSOR C_Spinal_Placements_Cursor IS
1464 SELECT distinct spp.placement_id,
1465 spp.step_id,
1466 spp.assignment_id,
1467 spp.auto_increment_flag,
1468 sps.spinal_point_id,
1469 spp.parent_spine_id,
1470 psp.sequence,
1471 NVL(spp.increment_number,0),
1472 spp.object_version_number,
1473 spp.effective_start_date,
1474 spp.effective_end_date,
1475 pgs.grade_spine_id
1476 FROM per_all_assignments_f asg,
1477 per_grade_spines_f pgs,
1478 per_spinal_points psp1,
1479 per_spinal_point_steps_f sps1,
1480 per_spinal_points psp,
1481 per_spinal_point_steps_f sps,
1482 per_spinal_point_placements_f spp
1483 WHERE (p_parent_spine_id is null -- PARENT_SPINE_ID
1484 OR
1485 (spp.parent_spine_id is not null
1486 and spp.parent_spine_id = P_Parent_Spine_ID))
1487 AND spp.business_group_id = P_Business_Group_ID
1488 AND spp.step_id = sps.step_id
1489 AND sps.grade_spine_id = pgs.grade_spine_id
1490 AND sps.spinal_point_id = psp.spinal_point_id
1491 AND spp.auto_increment_flag = 'Y'
1492 AND psp1.parent_spine_id = spp.parent_spine_id
1493 AND sps1.grade_spine_id = sps.grade_spine_id
1494 AND sps1.spinal_point_id = psp1.spinal_point_id
1495 AND pgs.grade_id = asg.grade_id
1496 AND asg.assignment_id = spp.assignment_id
1497 AND (p_collective_agreement_id is NULL -- COLLECTIVE_AGREEMENT_ID
1498 OR
1499 (asg.collective_agreement_id is not null
1500 and asg.collective_agreement_id = p_collective_agreement_id))
1501 AND (p_business_rule is NULL -- Only use assignment id if business rule is not null
1502 OR
1503 (p_business_rule is not null
1504 and asg.assignment_id = l_copy_br_assignment_id))
1505 AND (p_person_id is NULL -- PERSON_ID
1506 OR
1507 (asg.person_id is not null
1508 and asg.person_id = p_person_id))
1509 AND (p_payroll_id is NULL -- PAYROLL_ID
1510 OR
1511 (asg.payroll_id is not null
1512 and asg.payroll_id = p_payroll_id))
1513 AND (p_organization_id is NULL -- ORGANIZATION_ID
1514 OR
1515 (asg.organization_id is not null
1516 and asg.organization_id = p_organization_id))
1517 AND (p_legal_entity is NULL -- LEGAL ENTITY
1518 OR
1519 exists (select 1
1520 from hr_soft_coding_keyflex sck
1521 where asg.soft_coding_keyflex_id is not null
1522 and asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1523 and sck.segment1 = p_legal_entity))
1524 AND (p_qual_type is NULL
1525 OR
1526 exists (select 1
1527 from per_qualifications pq
1528 where asg.person_id = pq.person_id
1529 and pq.qualification_type_id = p_qual_type))
1530 AND (p_qual_status is NULL
1531 OR
1532 exists (select 1
1533 from per_qualifications pq
1534 where asg.person_id = pq.person_id
1535 and pq.qualification_type_id = p_qual_type
1536 and pq.status = p_qual_status))
1537 AND (p_org_structure_ver_id is NULL -- OGANIZATION HIERARCHY
1538 OR
1539 (exists
1540 (select 1
1541 from per_org_structure_elements ose
1542 where ose.org_structure_version_id = p_org_structure_ver_id
1543 and asg.organization_id = ose.organization_id_child
1544 connect by prior ose.organization_id_child = ose.organization_id_parent
1545 and ose.org_structure_version_id = p_org_structure_ver_id
1546 start with ose.organization_id_parent = p_org_structure_top_node
1547 and ose.org_structure_version_id = p_org_structure_ver_id)))
1548 AND l_effective_date BETWEEN spp.effective_start_date
1549 AND spp.effective_end_date
1550 AND l_effective_date BETWEEN sps.effective_start_date
1551 AND sps.effective_end_date
1552 AND l_effective_date BETWEEN pgs.effective_start_date
1553 AND pgs.effective_end_date
1554 AND l_effective_date BETWEEN asg.effective_start_date
1555 AND asg.effective_end_date
1556 AND l_effective_date BETWEEN sps1.effective_start_date
1557 AND sps1.effective_end_date
1558 AND psp1.sequence = psp.sequence;
1559
1560 --
1561 -- Now declare the group match cursor. This is the same as that above,
1562 -- but does additional restriction by NOT NULL groups of the input keyflex.
1563 --
1564 CURSOR C_Key_Spinal_Placements_Cursor IS
1565 SELECT distinct spp.placement_id,
1566 spp.step_id,
1567 spp.assignment_id,
1568 spp.auto_increment_flag,
1569 sps.spinal_point_id,
1570 spp.parent_spine_id,
1571 psp.sequence,
1572 NVL(spp.increment_number,0),
1573 spp.object_version_number,
1574 spp.effective_start_date,
1575 spp.effective_end_date,
1576 pgs.grade_spine_id
1577 FROM per_all_assignments_f asg,
1578 pay_people_groups ppg,
1579 per_grade_spines_f pgs,
1580 per_spinal_points psp1,
1581 per_spinal_point_steps_f sps1,
1582 per_spinal_points psp,
1583 per_spinal_point_steps_f sps,
1584 per_spinal_point_placements_f spp
1585 WHERE (p_parent_spine_id is null -- PARENT_SPINE_ID
1586 OR
1587 (spp.parent_spine_id is not null
1588 and spp.parent_spine_id = P_Parent_Spine_ID))
1589 AND spp.business_group_id = P_Business_Group_ID
1590 AND spp.step_id = sps.step_id
1591 AND sps.grade_spine_id = pgs.grade_spine_id
1592 AND sps.spinal_point_id = psp.spinal_point_id
1593 AND spp.auto_increment_flag = 'Y'
1594 AND psp1.parent_spine_id = spp.parent_spine_id
1595 AND sps1.grade_spine_id = sps.grade_spine_id
1596 AND sps1.spinal_point_id = psp1.spinal_point_id
1597 AND pgs.grade_id = asg.grade_id
1598 AND asg.assignment_id = spp.assignment_id
1599 AND (p_collective_agreement_id is NULL -- COLLECTIVE_AGREEMENT_ID
1600 OR
1601 (asg.collective_agreement_id is not null
1602 and asg.collective_agreement_id = p_collective_agreement_id))
1603 AND (p_business_rule is NULL -- Only use assignment id if business rule is not null
1604 OR
1605 (p_business_rule is not null
1606 and asg.assignment_id = l_copy_br_assignment_id))
1607 AND (p_person_id is NULL -- PERSON_ID
1608 OR
1609 (asg.person_id is not null
1610 and asg.person_id = p_person_id))
1611 AND (p_payroll_id is NULL -- PAYROLL_ID
1612 OR
1613 (asg.payroll_id is not null
1614 and asg.payroll_id = p_payroll_id))
1615 AND (p_organization_id is NULL -- ORGANIZATION_ID
1616 OR
1617 (asg.organization_id is not null
1618 and asg.organization_id = p_organization_id))
1619 AND (p_legal_entity is NULL -- LEGAL ENTITY
1620 OR
1621 exists (select 1
1622 from hr_soft_coding_keyflex sck
1623 where asg.soft_coding_keyflex_id is not null
1624 and asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1625 and sck.segment1 = p_legal_entity))
1626 AND (p_qual_type is NULL
1627 OR
1628 exists (select 1
1629 from per_qualifications pq
1630 where asg.person_id = pq.person_id
1631 and pq.qualification_type_id = p_qual_type))
1632 AND (p_qual_status is NULL
1633 OR
1634 exists (select 1
1635 from per_qualifications pq
1636 where asg.person_id = pq.person_id
1637 and pq.qualification_type_id = p_qual_type
1638 and pq.status = p_qual_status))
1639 AND (p_org_structure_ver_id is NULL -- OGANIZATION HIERARCHY
1640 OR
1641 (exists
1642 (select 1
1643 from per_org_structure_elements ose
1644 where ose.org_structure_version_id = p_org_structure_ver_id
1645 and asg.organization_id = ose.organization_id_child
1646 connect by prior ose.organization_id_child = ose.organization_id_parent
1647 and ose.org_structure_version_id = p_org_structure_ver_id
1648 start with ose.organization_id_parent = p_org_structure_top_node
1649 and ose.org_structure_version_id = p_org_structure_ver_id)))
1650 AND l_effective_date BETWEEN spp.effective_start_date
1651 AND spp.effective_end_date
1652 AND l_effective_date BETWEEN sps.effective_start_date
1653 AND sps.effective_end_date
1654 AND l_effective_date BETWEEN pgs.effective_start_date
1655 AND pgs.effective_end_date
1656 AND l_effective_date BETWEEN asg.effective_start_date
1657 AND asg.effective_end_date
1658 AND l_effective_date BETWEEN sps1.effective_start_date
1659 AND sps1.effective_end_date
1660 AND l_effective_date BETWEEN sps.effective_start_date
1661 AND asg.effective_end_date
1662 AND psp1.sequence = psp.sequence
1663 AND asg.people_group_id = ppg.people_group_id
1664 AND NVL(l_seg1, NVL( ppg.Segment1, L_Pass_String)) =
1665 NVL(ppg.Segment1, L_Pass_String)
1666 AND NVL(l_seg2, NVL( ppg.Segment2, L_Pass_String)) =
1667 NVL(ppg.Segment2, L_Pass_String)
1668 AND NVL(l_seg3, NVL( ppg.Segment3, L_Pass_String)) =
1669 NVL(ppg.Segment3, L_Pass_String)
1670 AND NVL(l_seg4, NVL( ppg.Segment4, L_Pass_String)) =
1671 NVL(ppg.Segment4, L_Pass_String)
1672 AND NVL(l_seg5, NVL( ppg.Segment5, L_Pass_String)) =
1673 NVL(ppg.Segment5, L_Pass_String)
1674 AND NVL(l_seg6, NVL( ppg.Segment6, L_Pass_String)) =
1675 NVL(ppg.Segment6, L_Pass_String)
1676 AND NVL(l_seg7, NVL( ppg.Segment7, L_Pass_String)) =
1677 NVL(ppg.Segment7, L_Pass_String)
1678 AND NVL(l_seg8, NVL( ppg.Segment8, L_Pass_String)) =
1679 NVL(ppg.Segment8, L_Pass_String)
1680 AND NVL(l_seg9, NVL( ppg.Segment9, L_Pass_String)) =
1681 NVL(ppg.Segment9, L_Pass_String)
1682 AND NVL(l_seg10, NVL( ppg.Segment10, L_Pass_String)) =
1683 NVL(ppg.Segment10, L_Pass_String)
1684 AND NVL(l_seg11, NVL( ppg.Segment11, L_Pass_String)) =
1685 NVL(ppg.Segment11, L_Pass_String)
1686 AND NVL(l_seg12, NVL( ppg.Segment12, L_Pass_String)) =
1687 NVL(ppg.Segment12, L_Pass_String)
1688 AND NVL(l_seg13, NVL( ppg.Segment13, L_Pass_String)) =
1689 NVL(ppg.Segment13, L_Pass_String)
1690 AND NVL(l_seg14, NVL( ppg.Segment14, L_Pass_String)) =
1691 NVL(ppg.Segment14, L_Pass_String)
1692 AND NVL(l_seg15, NVL( ppg.Segment15, L_Pass_String)) =
1693 NVL(ppg.Segment15, L_Pass_String)
1694 AND NVL(l_seg16, NVL( ppg.Segment16, L_Pass_String)) =
1695 NVL(ppg.Segment16, L_Pass_String)
1696 AND NVL(l_seg17, NVL( ppg.Segment17, L_Pass_String)) =
1697 NVL(ppg.Segment17, L_Pass_String)
1698 AND NVL(l_seg18, NVL( ppg.Segment18, L_Pass_String)) =
1699 NVL(ppg.Segment18, L_Pass_String)
1700 AND NVL(l_seg19, NVL( ppg.Segment19, L_Pass_String)) =
1701 NVL(ppg.Segment19, L_Pass_String)
1702 AND NVL(l_seg20, NVL( ppg.Segment20, L_Pass_String)) =
1703 NVL(ppg.Segment20, L_Pass_String)
1704 AND NVL(l_seg21, NVL( ppg.Segment21, L_Pass_String)) =
1705 NVL(ppg.Segment21, L_Pass_String)
1706 AND NVL(l_seg22, NVL( ppg.Segment22, L_Pass_String)) =
1707 NVL(ppg.Segment22, L_Pass_String)
1708 AND NVL(l_seg23, NVL( ppg.Segment23, L_Pass_String)) =
1709 NVL(ppg.Segment23, L_Pass_String)
1710 AND NVL(l_seg24, NVL( ppg.Segment24, L_Pass_String)) =
1711 NVL(ppg.Segment24, L_Pass_String)
1712 AND NVL(l_seg25, NVL( ppg.Segment25, L_Pass_String)) =
1713 NVL(ppg.Segment25, L_Pass_String)
1714 AND NVL(l_seg26, NVL( ppg.Segment26, L_Pass_String)) =
1715 NVL(ppg.Segment26, L_Pass_String)
1716 AND NVL(l_seg27, NVL( ppg.Segment27, L_Pass_String)) =
1717 NVL(ppg.Segment27, L_Pass_String)
1718 AND NVL(l_seg28, NVL( ppg.Segment28, L_Pass_String)) =
1719 NVL(ppg.Segment28, L_Pass_String)
1720 AND NVL(l_seg29, NVL( ppg.Segment29, L_Pass_String)) =
1721 NVL(ppg.Segment29, L_Pass_String)
1722 AND NVL(l_seg30, NVL( ppg.Segment30, L_Pass_String)) =
1723 NVL(ppg.Segment30, L_Pass_String);
1724 --
1725 --
1726 -- Cursor to get all assignments and there effective dates if business rule is used
1727 -- This simply returns all assignments that could be incremented, and the business rule
1728 -- dates so that the rest of the processing can determine when the increment should be
1729 -- taking place and then use the cursors above to determin if they are eligible
1730 --
1731 cursor csr_assignment_business_rule is
1732 select paa.assignment_id,
1733 pap.date_of_birth,
1734 pos.date_start,
1735 pap.original_date_of_hire,
1736 pos.adjusted_svc_date,
1737 paa.payroll_id
1738 from per_all_people_f pap,
1739 per_periods_of_service pos,
1740 per_all_assignments_f paa,
1741 per_spinal_point_placements_f spp
1742 where spp.assignment_id = paa.assignment_id
1743 and paa.person_id = pap.person_id
1744 and pos.person_id = pap.person_id
1745 and spp.business_group_id = p_business_group_id
1746 and spp.effective_end_date >= l_br_date_from
1747 and (('AOJ' = p_business_rule -- ***** ANNIVERSARY OF JOINING ***** --Bug #2999551
1748 and add_months(pap.original_date_of_hire, (to_number(substr(l_year_temp, 2, 4))- to_number(to_char(pap.original_date_of_hire,'YYYY')))*12)
1749 between l_br_date_from and l_br_date_to
1750 and paa.period_of_service_id = pos.period_of_service_id
1751 and pap.original_date_of_hire between decode(p_year_to, null, l_earliest_start_date,
1752 add_months(l_br_date_from, - (p_year_to * 12)))
1753 and decode(p_year_from, null, l_br_date_to,
1754 add_months(l_br_date_to, - (p_year_from * 12))))
1755 or ('DOB' = p_business_rule -- ***** DATE OF BIRTH ***** --Bug #2999551
1756 and add_months(pap.date_of_birth, (to_number(substr(l_year_temp, 2, 4))-to_number(to_char(pap.date_of_birth,'YYYY')))*12)
1757 between l_br_date_from and l_br_date_to
1758 and pap.date_of_birth between decode(p_year_to, null, l_earliest_start_date,
1759 add_months(l_br_date_from, - (p_year_to * 12)))
1760 and decode(p_year_from, null, l_br_date_to,
1761 add_months(l_br_date_to, - (p_year_from * 12))))
1762 or ('ASD' = p_business_rule -- ***** AJUSTED SERVICE DATE *****
1763 and pos.adjusted_svc_date is not null --Bug #2999551
1764 AND paa.period_of_service_id = pos.period_of_service_id -- bug 6969602
1765 and add_months(pos.adjusted_svc_date, (to_number(substr(l_year_temp, 2, 4))- to_number(to_char(pos.adjusted_svc_date,'YYYY')))*12)
1766 between l_br_date_from and l_br_date_to
1767 and pos.adjusted_svc_date between decode(p_year_to, null, l_earliest_start_date,
1768 add_months(l_br_date_from, - (p_year_to * 12)))
1769 and decode(p_year_from, null, l_br_date_to,
1770 add_months(l_br_date_to, - (p_year_from * 12))))
1771 or ('LHD' = p_business_rule -- ***** LATEST HIRE DATE ***** --Bug # 2999551
1772 and add_months(pos.date_start, (to_number(substr(l_year_temp, 2, 4))- to_number(to_char(pos.date_start,'YYYY')))*12)
1773 between l_br_date_from and l_br_date_to
1774 and paa.period_of_service_id = pos.period_of_service_id
1775 and pos.date_start between decode(p_year_to, null, l_earliest_start_date,
1776 add_months(l_br_date_from, - (p_year_to * 12)))
1777 and decode(p_year_from, null, l_br_date_to,
1778 add_months(l_br_date_to, - (p_year_from * 12)))))
1779 order by paa.assignment_id;
1780
1781 --
1782 -- Cursor to get the next pay period
1783 --
1784 cursor csr_pay_period is
1785 select ptp.start_date
1786 from per_time_periods ptp
1787 where ptp.payroll_id = l_copy_payroll_id
1788 and ptp.start_date > l_effective_date;
1789 --
1790 BEGIN
1791 --
1792 -- Check that the business group passed is valid
1793 --
1794 hr_api.validate_bus_grp_id
1795 (p_business_group_id => P_Business_Group_ID);
1796 --
1797
1798 --
1799 -- Check if the business rule is being used and if so that all required parameters are valid
1800 --
1801 business_rule_check
1802 (p_effective_date => p_effective_date
1803 ,p_business_rule => p_business_rule
1804 ,p_br_date_from => p_br_date_from
1805 ,p_br_date_to => p_br_date_to
1806 ,p_year_to => p_year_to
1807 ,p_year_from => p_year_from
1808 ,p_dependant_date => p_dependant_date
1809 ,p_br_flag => l_br_flag);
1810
1811 -- hr_utility.set_location('HRSPINE - Business Rule = '||l_br_flag,55);
1812
1813 --
1814 -- Check that all the constraints are correct
1815 --
1816 constraint_check
1817 (p_org_structure_top_node => p_org_structure_top_node
1818 ,p_org_structure_ver_id => p_org_structure_ver_id
1819 ,p_qual_status => p_qual_status
1820 ,p_qual_type => p_qual_type
1821 ,p_business_group_id => p_business_group_id
1822 ,p_legal_entity => p_legal_entity);
1823
1824 hr_utility.trace ('concat string = ' || p_concat_segs);
1825 --
1826 if (p_concat_segs is not null)
1827 then
1828 hr_utility.set_location ('hrspine.spine', 2);
1829 --
1830 BEGIN
1831 select concatenated_segment_delimiter
1832 into l_concat_sep
1833 from fnd_id_flex_structures
1834 where id_flex_num = p_id_flex_num
1835 and application_id = 801
1836 and id_flex_code = 'GRP';
1837 EXCEPTION
1838 WHEN NO_DATA_FOUND
1839 THEN
1840 hr_utility.set_location('NO_DATA_FOUND',1900);
1841 hr_utility.set_location('concatenated_segment_delimiter ',1900);
1842 hr_utility.set_location('not found for p_id_flex_num '
1843 ||p_id_flex_num, 1900);
1844 END;
1845 --
1846 -- loop through for each segment, and re-arrange into the correct
1847 -- segment order as held on the table pay_people_groups
1848 --
1849 for c1rec in c1 loop
1850 l_count := l_count + 1;
1851 l_pos_sep := instr (p_concat_segs, l_concat_sep, 1, l_count);
1852 --
1853 if (l_pos_sep = 0) then -- the search failed (end of string)
1854 l_seg_value := rtrim (substr (p_concat_segs, l_pos));
1855 else
1856 l_length := l_pos_sep - l_pos;
1857 l_seg_value := substr (p_concat_segs, l_pos, l_length);
1858 end if;
1859 l_pos := l_pos + l_length + 1; -- skip on to next segment
1860 hr_utility.trace ((c1rec.application_column_name || ' = ')
1861 || l_seg_value);
1862 if (c1rec.application_column_name = 'SEGMENT1') then
1863 l_seg1 := l_seg_value;
1864 elsif (c1rec.application_column_name = 'SEGMENT2') then
1865 l_seg2 := l_seg_value;
1866 elsif (c1rec.application_column_name = 'SEGMENT3') then
1867 l_seg3 := l_seg_value;
1868 elsif (c1rec.application_column_name = 'SEGMENT4') then
1869 l_seg4 := l_seg_value;
1870 elsif (c1rec.application_column_name = 'SEGMENT5') then
1871 l_seg5 := l_seg_value;
1872 elsif (c1rec.application_column_name = 'SEGMENT6') then
1873 l_seg6 := l_seg_value;
1874 elsif (c1rec.application_column_name = 'SEGMENT7') then
1875 l_seg7 := l_seg_value;
1876 elsif (c1rec.application_column_name = 'SEGMENT8') then
1877 l_seg8 := l_seg_value;
1878 elsif (c1rec.application_column_name = 'SEGMENT9') then
1879 l_seg9 := l_seg_value;
1880 elsif (c1rec.application_column_name = 'SEGMENT10') then
1881 l_seg10 := l_seg_value;
1882 elsif (c1rec.application_column_name = 'SEGMENT11') then
1883 l_seg11 := l_seg_value;
1884 elsif (c1rec.application_column_name = 'SEGMENT12') then
1885 l_seg12 := l_seg_value;
1886 elsif (c1rec.application_column_name = 'SEGMENT13') then
1887 l_seg13 := l_seg_value;
1888 elsif (c1rec.application_column_name = 'SEGMENT14') then
1889 l_seg14 := l_seg_value;
1890 elsif (c1rec.application_column_name = 'SEGMENT15') then
1891 l_seg15 := l_seg_value;
1892 elsif (c1rec.application_column_name = 'SEGMENT16') then
1893 l_seg16 := l_seg_value;
1894 elsif (c1rec.application_column_name = 'SEGMENT17') then
1895 l_seg17 := l_seg_value;
1896 elsif (c1rec.application_column_name = 'SEGMENT18') then
1897 l_seg18 := l_seg_value;
1898 elsif (c1rec.application_column_name = 'SEGMENT19') then
1899 l_seg19 := l_seg_value;
1900 elsif (c1rec.application_column_name = 'SEGMENT20') then
1901 l_seg20 := l_seg_value;
1902 elsif (c1rec.application_column_name = 'SEGMENT21') then
1903 l_seg21 := l_seg_value;
1904 elsif (c1rec.application_column_name = 'SEGMENT22') then
1905 l_seg22 := l_seg_value;
1906 elsif (c1rec.application_column_name = 'SEGMENT23') then
1907 l_seg23 := l_seg_value;
1908 elsif (c1rec.application_column_name = 'SEGMENT24') then
1909 l_seg24 := l_seg_value;
1910 elsif (c1rec.application_column_name = 'SEGMENT25') then
1911 l_seg25 := l_seg_value;
1912 elsif (c1rec.application_column_name = 'SEGMENT26') then
1913 l_seg26 := l_seg_value;
1914 elsif (c1rec.application_column_name = 'SEGMENT27') then
1915 l_seg27 := l_seg_value;
1916 elsif (c1rec.application_column_name = 'SEGMENT28') then
1917 l_seg28 := l_seg_value;
1918 elsif (c1rec.application_column_name = 'SEGMENT29') then
1919 l_seg29 := l_seg_value;
1920 elsif (c1rec.application_column_name = 'SEGMENT30') then
1921 l_seg30 := l_seg_value;
1922 else
1923 hr_utility.trace ('ERROR : unknown application column name');
1924 end if;
1925 end loop;
1926 hr_utility.set_location ('hrspine.spine', 4);
1927 hr_utility.set_location('Seg 1 - '||l_seg1,4);
1928 hr_utility.set_location('Seg 15 - '||l_seg15,4);
1929 end if;
1930 --
1931 -- Set the count to see if any data is updated
1932 --
1933 l_message_number_count := 0;
1934 --
1935 -- If the user is not using a business rule then use the old cursor
1936 --
1937 if l_br_flag = FALSE then
1938 --
1939 l_effective_date := p_effective_date;
1940 --
1941 -- Open the correct cursor
1942 --
1943 if (p_concat_segs is null)
1944 then
1945 open C_Spinal_Placements_Cursor;
1946 else
1947 open C_Key_Spinal_Placements_Cursor;
1948 end if;
1949 --
1950 LOOP
1951 if (p_concat_segs is null) then
1952 --
1953 -- fetch standard cursor, no group partial matching
1954 --
1955 /*** vik ***/
1956 hr_utility.set_location ('hrspine.spine', 51);
1957 FETCH C_Spinal_Placements_Cursor
1958 INTO LC_Placement_ID,
1959 LC_Step_ID,
1960 LC_Assignment_ID,
1961 LC_Auto_Increment_Flag,
1962 LC_Spinal_Point_ID,
1963 LC_Parent_Spine_ID,
1964 LC_Sequence_Number,
1965 LC_Increment_Number,
1966 LC_Object_Version_Number,
1967 LC_Effective_Start_Date,
1968 L_End_Date,
1969 l_grade_spine_id;
1970 -- vik
1971 if (C_Spinal_Placements_Cursor%notfound)
1972 then
1973 hr_utility.set_location ('hrspine.spine', 69);
1974 hr_utility.set_location ('no data found', 69);
1975 hr_utility.set_location ('in C_Spinal_Placements_Cursor',69);
1976 hr_utility.set_location ('for'||TO_CHAR(LC_Assignment_ID),69);
1977 hr_utility.set_location ('and for'||TO_CHAR(LC_Placement_ID),69);
1978 hr_utility.set_location ('for'|| P_Parent_Spine_ID,69);
1979 else
1980 hr_utility.set_location ('hrspine.spine', 79);
1981 hr_utility.set_location ('C_Spinal_Placements_Cur success',79);
1982 hr_utility.set_location ('for '||TO_CHAR(LC_Assignment_ID),79);
1983 hr_utility.set_location ('and for '||TO_CHAR(LC_Placement_ID),79);
1984 hr_utility.set_location ('for'|| P_Parent_Spine_ID,79);
1985 end if;
1986 --
1987 l_message_number_count := l_message_number_count + 1;
1988 --
1989 EXIT when C_Spinal_Placements_Cursor%notfound;
1990 --
1991 else
1992 --
1993 -- fetch key cursor, with group partial matching
1994 --
1995 hr_utility.set_location ('hrspine.spine', 6);
1996 FETCH C_Key_Spinal_Placements_Cursor
1997 INTO LC_Placement_ID,
1998 LC_Step_ID,
1999 LC_Assignment_ID,
2000 LC_Auto_Increment_Flag,
2001 LC_Spinal_Point_ID,
2002 LC_Parent_Spine_ID,
2003 LC_Sequence_Number,
2004 LC_Increment_Number,
2005 LC_Object_Version_Number,
2006 LC_Effective_Start_Date,
2007 L_End_Date,
2008 l_grade_spine_id;
2009 --
2010 l_message_number_count := l_message_number_count + 1;
2011 --
2012 EXIT when C_Key_Spinal_Placements_Cursor%notfound;
2013 end if;
2014 --
2015 --
2016 -- Call the procedure to check if assignment selected can be
2017 -- incremented
2018 --
2019 check_increment_available
2020 (p_placement_id => LC_Placement_ID
2021 ,p_end_date => L_End_Date
2022 ,p_effective_date => l_effective_date
2023 ,p_effective_start_date => LC_Effective_Start_Date
2024 ,p_datetrack_mode => l_datetrack_mode
2025 ,p_assignment_id => LC_Assignment_ID
2026 ,p_parent_spine_id => LC_Parent_Spine_ID
2027 ,p_grade_spine_id => l_grade_spine_id
2028 ,p_step_id => LC_Step_ID
2029 ,p_sequence_number => LC_Sequence_Number
2030 ,p_increment_number => LC_Increment_Number
2031 ,p_exception_report1 => l_exception_report1
2032 ,p_update => l_update
2033 ,p_increment => l_increment
2034 ,p_max_sequence_number => l_max_sequence_number
2035 ,p_max_special_sequence_number => l_max_special_sequence_number
2036 ,p_max_spinal_point_id => l_max_spinal_point_id
2037 ,p_max_special_spinal_point_id => l_max_special_spinal_point_id );
2038
2039 --
2040 -- Call procedure to update the record and report on records
2041 -- updated and missed
2042 --
2043
2044 update_report_exception
2045 (p_datetrack_mode => l_datetrack_mode
2046 ,p_effective_date => l_effective_date
2047 ,p_placement_id => LC_Placement_ID
2048 ,p_object_version_number => LC_Object_Version_Number
2049 ,p_increment_number => l_increment
2050 ,p_reason => l_reason
2051 ,p_effective_start_date => LC_Effective_Start_Date
2052 ,p_assignment_id => LC_Assignment_ID
2053 ,p_parent_spine_id => LC_Parent_Spine_ID
2054 ,p_spinal_point_id => LC_Spinal_Point_ID
2055 ,p_rate_id => p_rate_id
2056 ,p_lc_step_id => LC_Step_ID
2057 ,p_exception_report1 => l_exception_report1
2058 ,p_end_date => L_End_Date
2059 ,p_orig_increment_number => LC_Increment_Number
2060 ,p_sequence_number => LC_Sequence_Number
2061 ,p_grade_spine_id => l_grade_spine_id
2062 ,p_update => l_update
2063 ,p_max_special_sequence_number => l_max_special_sequence_number
2064 ,p_max_special_spinal_point => l_max_special_spinal_point_id
2065 ,p_max_sequence_number => l_max_sequence_number
2066 ,p_max_spinal_point => l_max_spinal_point_id);
2067
2068 l_exception_report1 := 'N';
2069
2070 END LOOP;
2071
2072 Elsif l_br_flag = TRUE then
2073
2074 -- ================================================================================
2075 -- Business Rule Processing
2076 -- ================================================================================
2077
2078 if to_date(to_char(p_br_date_to,'YYYY'),'YYYY') > to_date(to_char(p_br_date_from,'YYYY'),'YYYY') then
2079
2080 hr_utility.set_location('Setting new Business Rule dates',1);
2081
2082 l_year_from_temp := substr(to_char(p_br_date_from,'DD-MM-RRRR'),6,5); -- Year to
2083 hr_utility.set_location(l_year_from_temp,1);
2084
2085 l_br_date_to_temp1 := '31-12'||l_year_from_temp;
2086 hr_utility.set_location(l_br_date_to_temp1,1);
2087
2088 l_year_to_temp := substr(to_char(p_br_date_to,'DD-MM-RRRR'),6,5); -- Year from
2089 hr_utility.set_location(l_year_to_temp,2);
2090
2091 l_br_date_from_temp1 := '01-01'||l_year_to_temp;
2092 hr_utility.set_location(l_br_date_from_temp1,2);
2093
2094 l_year_loop := 'Y';
2095
2096 else
2097
2098 l_year_loop := 'N';
2099 l_year_loop_count := 2;
2100
2101 end if;
2102
2103 LOOP
2104 -- Year wrap issue, set the effective dates accordingly
2105 -- The reason ther is a problem is that you cant determine which year to look
2106 -- up a date, so the process has to devide into two sections, run through once
2107 -- for the end of the first year and then again for the start of the second year
2108
2109 if l_year_loop = 'Y' and l_year_loop_count = 1 then
2110
2111 hr_utility.set_location('== p_br_date_from == '||p_br_date_from,3);
2112 hr_utility.set_location('== l_br_date_to_temp1 == '||l_br_date_to_temp1,3);
2113 l_br_date_from := to_date(to_char(p_br_date_from,'DD-MM-RRRR'),'DD-MM-RRRR');
2114 l_br_date_to := to_date(l_br_date_to_temp1,'DD-MM-RRRR');
2115 l_year_temp := l_year_from_temp;
2116
2117 elsif l_year_loop = 'Y' and l_year_loop_count = 2 then
2118
2119 l_br_date_from := to_date(l_br_date_from_temp1,'DD-MM-RRRR');
2120 l_br_date_to := to_date(to_char(p_br_date_to,'DD-MM-RRRR'),'DD-MM-RRRR');
2121 l_year_temp := l_year_to_temp;
2122
2123 else
2124
2125 l_br_date_from := p_br_date_from;
2126 l_br_date_to := p_br_date_to;
2127 l_year_temp := substr(to_char(p_br_date_from,'DD-MM-RRRR'),6,5);
2128
2129 end if;
2130
2131
2132 hr_utility.set_location('Business Rule Set +++++++++++',10);
2133
2134 open csr_assignment_business_rule;
2135
2136 LOOP
2137
2138 -- ================================================================================
2139 -- First find all the people who have a grade step placement for the business group
2140 -- ================================================================================
2141
2142 hr_utility.set_location('p_business_rule - '||p_business_rule,10);
2143 hr_utility.set_location('p_br_date_from - '||l_br_date_from,10);
2144 hr_utility.set_location('p_br_date_to - '||l_br_date_to,10);
2145 hr_utility.set_location('p_year_from - '||p_year_from,10);
2146 hr_utility.set_location('p_year_to - '||p_year_to,10);
2147 hr_utility.set_location('l_year_temp - '||l_year_temp,10);
2148 hr_utility.set_location('l_earliest_start_date - '||to_char(l_earliest_start_date,'DD-MM-YYYY'),10);
2149
2150 fetch csr_assignment_business_rule into l_br_assignment_id,
2151 l_br_date_of_birth,
2152 l_br_annivarsary_of_joining,
2153 l_br_latest_hire_date,
2154 l_br_adjusted_svc_date,
2155 l_payroll_id;
2156
2157 if csr_assignment_business_rule%notfound then
2158
2159 -- Last record has been already fetched so set all variables to null so that when they are
2160 -- compared later they will differ, as values currently will match
2161
2162 l_br_assignment_id := null;
2163 l_br_date_of_birth := null;
2164 l_br_annivarsary_of_joining := null;
2165 l_br_latest_hire_date := null;
2166 l_br_adjusted_svc_date := null;
2167 l_payroll_id := null;
2168
2169 end if;
2170
2171 hr_utility.set_location('l_br_assignment_id,'||l_br_assignment_id,15);
2172 hr_utility.set_location('l_br_date_of_birth,'||l_br_date_of_birth,15);
2173 hr_utility.set_location('l_br_annivarsary_of_joining,'||l_br_annivarsary_of_joining,15);
2174 hr_utility.set_location('l_br_latest_hire_date,'||l_br_latest_hire_date,15);
2175 hr_utility.set_location('l_br_adjusted_svc_date,'||l_br_adjusted_svc_date,15);
2176 hr_utility.set_location('l_copy_br_assignment_id = '||l_copy_br_assignment_id,16);
2177 hr_utility.set_location('l_br_assignment_id = '||l_br_assignment_id,16);
2178
2179 l_br_update := 'N';
2180
2181 if l_first_call_flag = FALSE then
2182
2183 hr_utility.set_location('l_first_call_flag = FALSE',20);
2184
2185 -- Check if the next record has the same assignment id
2186
2187 if l_copy_br_assignment_id = l_br_assignment_id then
2188
2189 hr_utility.set_location('Assignments Match - Duplicates',25);
2190
2191 l_duplicate_flag := TRUE;
2192
2193 else
2194
2195 hr_utility.set_location('Assignments Don''t Match - No Duplicates',25);
2196
2197 l_duplicate_flag := FALSE;
2198 l_br_update := 'Y';
2199
2200 end if;
2201
2202 -- ================================================================================
2203 -- Find out the business rule being used and set the effetive date accordingly
2204 -- ================================================================================
2205
2206 if p_business_rule = 'DOB' then -- ** DATE OF BIRTH ** --
2207
2208 hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2209
2210 if l_duplicate_flag = TRUE
2211 and l_copy_br_date_of_birth <> l_br_date_of_birth then
2212
2213 l_br_update := 'N';
2214 l_duplicate_error_flag := TRUE; -- Incase next record is same assignment
2215
2216 end if;
2217
2218 -- Check that the date of birth is not null
2219 if l_copy_br_date_of_birth is null then
2220
2221 l_br_update := 'N';
2222
2223 else
2224
2225 l_effective_date := l_copy_br_date_of_birth;
2226
2227 end if;
2228
2229 elsif p_business_rule = 'AOJ' then -- ** ANNIVERSARY OF JOINING ** --
2230
2231 hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2232
2233 if l_duplicate_flag = TRUE
2234 and l_copy_br_anniv_of_joining <> l_br_annivarsary_of_joining then
2235
2236 hr_utility.set_location('Anniversary Of Joining Changed!',1);
2237 l_br_update := 'N';
2238 l_duplicate_error_flag := TRUE; -- Incase next record is same assignment
2239
2240 end if;
2241
2242 l_effective_date := l_copy_br_anniv_of_joining;
2243
2244 elsif p_business_rule = 'ASD' then -- ** ADJUSTED SERVICE DATE ** --
2245
2246 hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2247
2248 if l_duplicate_flag = TRUE
2249 and l_copy_br_adjusted_svc_date <>l_br_adjusted_svc_date then
2250
2251 l_br_update := 'N';
2252 l_duplicate_error_flag := TRUE; -- Incase next record is same assignment
2253
2254 end if;
2255
2256 -- Check that the adjusted service date is not null
2257
2258 if l_copy_br_adjusted_svc_date is null then
2259
2260 l_br_update := 'N';
2261
2262 else
2263
2264 l_effective_date := l_copy_br_adjusted_svc_date;
2265
2266 end if;
2267
2268 elsif p_business_rule = 'LHD' then -- ** LATEST HIRE DATE ** --
2269
2270 hr_utility.set_location('p_business_rule - '||p_business_rule,30);
2271
2272 if l_duplicate_flag = TRUE
2273 and l_copy_br_latest_hire_date <> l_br_latest_hire_date then
2274
2275 hr_utility.set_location('Duplicate but Latest Hire Date Changed',1);
2276 l_br_update := 'N';
2277 l_duplicate_error_flag := TRUE; -- Incase next record is same assignment
2278
2279 end if;
2280
2281 l_effective_date := l_copy_br_latest_hire_date;
2282
2283 else
2284
2285 -- Invalid Business Rule
2286 hr_utility.set_location('Invalid Business Rule',1);
2287
2288 fnd_message.set_name('PER', 'HR_289507_SPP_BR_INVALID');
2289 hr_utility.raise_error;
2290
2291 end if;
2292
2293
2294 -- ==================================================================================================
2295 -- Check if the from and to date wrap over a year, if so get the appropiate year for employee
2296 -- ==================================================================================================
2297
2298 l_temp_year := to_char(l_effective_date,'DD/MM')||'/';
2299 if l_temp_year = '29/02/' then
2300 l_effective_date := l_effective_date + 1;
2301 end if;
2302 if to_date(to_char(p_br_date_to,'YYYY'),'YYYY') > to_date(to_char(p_br_date_from,'YYYY'),'YYYY') then
2303
2304 hr_utility.set_location('Business Rule Dates Over Two Years',31);
2305
2306 -- use l_effective_year for l_br_date_from year
2307 -- and l_effective_month for l_br_date_to year
2308 -- and l_temp_year for l_effective_date
2309 l_effective_year := to_char(p_br_date_from, 'YYYY');
2310 l_effective_month := to_char(p_br_date_to, 'YYYY');
2311 l_temp_year := to_char(l_effective_date,'DD/MM')||'/';
2312
2313 hr_utility.set_location('l_temp_year '||l_temp_year,75);
2314
2315 if to_date(l_temp_year||l_effective_year,'DD-MM-YYYY') >= p_br_date_from then
2316
2317 hr_utility.set_location('End of year',31);
2318
2319 l_effective_year := to_char(p_br_date_from,'YYYY');
2320 l_effective_month := to_char(l_effective_date,'DD/MM')||'/';
2321 l_temp_year := l_effective_month||l_effective_year;
2322
2323 l_effective_date := to_date(l_temp_year,'DD/MM/YYYY');
2324
2325 elsif to_date(l_temp_year||l_effective_month,'DD-MM-YYYY') <= p_br_date_to then
2326
2327 hr_utility.set_location('Start of year',31);
2328
2329 l_effective_year := to_char(p_br_date_to,'YYYY');
2330 l_effective_month := to_char(l_effective_date,'DD/MM')||'/';
2331 l_temp_year := l_effective_month||l_effective_year;
2332
2333 l_effective_date := to_date(l_temp_year,'DD/MM/YYYY');
2334
2335 end if;
2336
2337 elsif to_date(to_char(p_br_date_to,'YYYY'),'YYYY') = to_date(to_char(p_br_date_from,'YYYY'),'YYYY') then
2338
2339 hr_utility.set_location('Business Rule Over One Year',31);
2340
2341 l_effective_year := to_char(p_br_date_from,'YYYY');
2342 l_effective_month := to_char(l_effective_date,'DD/MM')||'/';
2343 l_temp_year := l_effective_month||l_effective_year;
2344
2345 hr_utility.set_location('l_temp_year '||l_temp_year,31);
2346
2347 l_effective_date := to_date(l_temp_year,'DD/MM/YYYY');
2348
2349 end if;
2350
2351 -- ===============================================================================
2352 -- Dependant Rule Date Set
2353 --
2354 -- Depending on the dependant date selected in the concurrent process set the
2355 -- effective date to use
2356 --
2357 -- ===============================================================================
2358
2359 hr_utility.set_location('--------- Dependant Date Rule - '||p_dependant_date,32);
2360
2361 if p_dependant_date = 'DD1' then -- ## Effective Date ##
2362
2363 hr_utility.set_location('Dependant Date Rule - Effectvie Date',33);
2364
2365 l_effective_date := p_effective_date;
2366
2367 elsif p_dependant_date = 'DD2' then -- ## Business Rule effective Date ##
2368
2369 hr_utility.set_location('Dependant Date Rule - Business Rule Effective Date',33);
2370
2371 l_effective_date := l_effective_date;
2372
2373 elsif p_dependant_date = 'DD3' then -- ## Start Of Next Month ##
2374
2375 hr_utility.set_location('Dependant Date Rule - Start Of Next Month',33);
2376 hr_utility.set_location('l_effective_date = '||l_effective_date,33);
2377
2378 l_effective_date := last_day(l_effective_date)+1;
2379
2380 elsif p_dependant_date = 'DD4' then -- ## Start Of next pay period ##
2381
2382 hr_utility.set_location('Pay Period Dependant Date',33);
2383
2384 if l_copy_payroll_id is null then
2385
2386 hr_utility.set_location('Null Payroll Id',33);
2387
2388 l_update2 := 'N';
2389 l_exception_report2 := 'Y';
2390
2391 else
2392
2393 l_update2 := 'Y';
2394 l_exception_report2 := 'N';
2395 open csr_pay_period;
2396 fetch csr_pay_period into l_effective_date;
2397 close csr_pay_period;
2398
2399 hr_utility.set_location('Payroll Exists - l_effective_date - '||l_effective_date,33);
2400
2401 end if;
2402
2403 else
2404
2405 -- Invalid Dependant Date
2406
2407 fnd_message.set_name('PER', 'HR_289508_SPP_BR_DEP_DATE');
2408 hr_utility.raise_error;
2409
2410 end if; -- End of dependant date set
2411
2412 hr_utility.set_location('l_br_update - '||l_br_update,40);
2413 hr_utility.set_location('++ New effective date - '||l_effective_date,40);
2414
2415 if l_br_update = 'Y'
2416
2417 and l_duplicate_flag = FALSE
2418 and l_duplicate_error_flag = FALSE then
2419
2420 hr_utility.set_location('++++++++++++++ UPDATING ++++++++++++++++++',45);
2421
2422 -- ================================================================================
2423 -- Call the main cursor to retrieve all information required - Assignment Id used
2424 -- ================================================================================
2425
2426 -- Open the correct cursor
2427
2428 if (p_concat_segs is null) then
2429 open C_Spinal_Placements_Cursor;
2430 else
2431 open C_Key_Spinal_Placements_Cursor;
2432 end if;
2433
2434 LOOP
2435
2436 if (p_concat_segs is null) then
2437 --
2438 -- fetch standard cursor, no group partial matching
2439 --
2440 hr_utility.set_location ('hrspine.spine', 52);
2441 FETCH C_Spinal_Placements_Cursor
2442 INTO LC_Placement_ID,
2443 LC_Step_ID,
2444 LC_Assignment_ID,
2445 LC_Auto_Increment_Flag,
2446 LC_Spinal_Point_ID,
2447 LC_Parent_Spine_ID,
2448 LC_Sequence_Number,
2449 LC_Increment_Number,
2450 LC_Object_Version_Number,
2451 LC_Effective_Start_Date,
2452 L_End_Date,
2453 l_grade_spine_id;
2454
2455 l_message_number_count := l_message_number_count + 1;
2456
2457 EXIT when C_Spinal_Placements_Cursor%notfound;
2458
2459 else
2460 --
2461 -- fetch key cursor, with group partial matching
2462 --
2463 hr_utility.set_location ('hrspine.spine', 6);
2464 FETCH C_Key_Spinal_Placements_Cursor
2465 INTO LC_Placement_ID,
2466 LC_Step_ID,
2467 LC_Assignment_ID,
2468 LC_Auto_Increment_Flag,
2469 LC_Spinal_Point_ID,
2470 LC_Parent_Spine_ID,
2471 LC_Sequence_Number,
2472 LC_Increment_Number,
2473 LC_Object_Version_Number,
2474 LC_Effective_Start_Date,
2475 L_End_Date,
2476 l_grade_spine_id;
2477
2478 l_message_number_count := l_message_number_count + 1;
2479
2480 EXIT when C_Key_Spinal_Placements_Cursor%notfound;
2481 end if;
2482 --
2483 hr_utility.set_location('Placement Found - '||LC_Placement_ID,45);
2484 hr_utility.set_location('############# Effective Date ###### '||l_effective_date,45);
2485 --
2486 -- Call the procedure to heck if assignment selected can be incremented
2487 --
2488
2489 check_increment_available
2490 (p_placement_id => LC_Placement_ID
2491 ,p_end_date => L_End_Date
2492 ,p_effective_date => l_effective_date
2493 ,p_effective_start_date => LC_Effective_Start_Date
2494 ,p_datetrack_mode => l_datetrack_mode
2495 ,p_assignment_id => LC_Assignment_ID
2496 ,p_parent_spine_id => LC_Parent_Spine_ID
2497 ,p_grade_spine_id => l_grade_spine_id
2498 ,p_step_id => LC_Step_ID
2499 ,p_sequence_number => LC_Sequence_Number
2500 ,p_increment_number => LC_Increment_Number
2501 ,p_exception_report1 => l_exception_report1
2502 ,p_update => l_update
2503 ,p_increment => l_increment
2504 ,p_max_sequence_number => l_max_sequence_number
2505 ,p_max_special_sequence_number => l_max_special_sequence_number
2506 ,p_max_spinal_point_id => l_max_spinal_point_id
2507 ,p_max_special_spinal_point_id => l_max_special_spinal_point_id );
2508
2509 hr_utility.set_location('############# Effective Date ###### '||l_effective_date,45);
2510
2511 if l_update = 'N' or l_update2 = 'N' then
2512
2513 l_update := 'N';
2514
2515 end if;
2516
2517 if l_exception_report1 = 'Y' or l_exception_report2 = 'Y' then
2518
2519 l_exception_report1 := 'Y';
2520
2521 end if;
2522 --
2523 -- Call procedure to update the record and report on records
2524 -- updated and missed
2525 --
2526
2527 update_report_exception
2528 (p_datetrack_mode => l_datetrack_mode
2529 ,p_effective_date => l_effective_date
2530 ,p_placement_id => LC_Placement_ID
2531 ,p_object_version_number => LC_Object_Version_Number
2532 ,p_increment_number => l_increment
2533 ,p_reason => l_reason
2534 ,p_effective_start_date => LC_Effective_Start_Date
2535 ,p_assignment_id => LC_Assignment_ID
2536 ,p_parent_spine_id => LC_Parent_Spine_ID
2537 ,p_spinal_point_id => LC_Spinal_Point_ID
2538 ,p_rate_id => p_rate_id
2539 ,p_lc_step_id => LC_Step_ID
2540 ,p_exception_report1 => l_exception_report1
2541 ,p_end_date => L_End_Date
2542 ,p_orig_increment_number => LC_Increment_Number
2543 ,p_sequence_number => LC_Sequence_Number
2544 ,p_grade_spine_id => l_grade_spine_id
2545 ,p_update => l_update
2546 ,p_max_special_sequence_number => l_max_special_sequence_number
2547 ,p_max_special_spinal_point => l_max_special_spinal_point_id
2548 ,p_max_sequence_number => l_max_sequence_number
2549 ,p_max_spinal_point => l_max_spinal_point_id);
2550
2551 l_exception_report1 := 'N';
2552 l_duplicate_error_flag := FALSE;
2553
2554 END LOOP;
2555
2556 -- close the correct cursor
2557
2558 if (p_concat_segs is null) then
2559 close C_Spinal_Placements_Cursor;
2560 else
2561 close C_Key_Spinal_Placements_Cursor;
2562 end if;
2563
2564 end if; -- l_br_update = Y
2565
2566 else
2567
2568 exit when csr_assignment_business_rule%notfound;
2569
2570 end if; -- l_first_call_flag
2571
2572 -- assign the new parameters to the copy local parameters so that
2573 -- when the loop start again
2574 -- the next record can be retrieved into the orginal parameters
2575
2576 l_copy_br_assignment_id := l_br_assignment_id;
2577 l_copy_br_date_of_birth := l_br_date_of_birth;
2578 l_copy_br_anniv_of_joining := l_br_annivarsary_of_joining;
2579 l_copy_br_latest_hire_date := l_br_latest_hire_date;
2580 l_copy_br_adjusted_svc_date := l_br_adjusted_svc_date;
2581 l_copy_payroll_id := l_payroll_id;
2582
2583
2584 if l_first_call_flag = TRUE then
2585
2586 l_first_call_flag := FALSE;
2587
2588 else
2589
2590 exit when csr_assignment_business_rule%notfound;
2591
2592 end if;
2593
2594 END LOOP;
2595
2596 close csr_assignment_business_rule;
2597
2598 l_first_call_flag := TRUE;
2599
2600 exit when l_year_loop_count = 2;
2601
2602 l_year_loop_count := l_year_loop_count + 1;
2603
2604 END LOOP; -- Year wrap issue
2605
2606
2607 End if;
2608
2609 -- -----------------------------------------------------------
2610 -- set last auto inc. date on parent spine
2611 -- -----------------------------------------------------------
2612 hr_utility.set_location ('hrspine.spine', 11);
2613 UPDATE per_parent_spines
2614 SET last_automatic_increment_date = P_Effective_Date,
2615 last_update_date = sysdate,
2616 program_update_date = sysdate
2617 WHERE parent_spine_id = P_Parent_Spine_ID;
2618 --
2619 -- Set the out parameter
2620 --
2621 if l_message_number_count = 0 then
2622 --
2623 p_message_number := 'PER_289573_SPP_NO_DATA';
2624 hr_utility.set_location ('officially no data.spine', 11);
2625 --
2626 else
2627 --
2628 p_message_number := null;
2629 hr_utility.set_location ('hrspine.spine shd be sweet', 11);
2630 end if;
2631 hr_utility.set_location ('hrspine.spine', 12);
2632 --
2633 END spine;
2634 --
2635 END hrspine;
[Home] [Help]