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