[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE Placement to end on P_Effective_Run_Date
--
INSERT New placement starting on P_Effective_Run_Date until the end
of time. Has same values as the curent placement.
END LOOP
--
NOTES
adapted from an earlier release of a 'C' program for spinal incrementing,
first written by AMcI.
This PL/SQL procedure is called by the run report screen (SRS). The
group keyflex is passed to this procedure as a concatenated string, (or
null when no group keyflex is specified). When the concatenated string
is null, no group partial matching is performed and all valid employees
are incremented.
When a People group keyflex has been specified, the procedure has to first
separate the concatenated string into the individual segments. A further
complication is that the display order of the segments as passed to
this procedure may be a different order to the way it is stored on the
people group table. The segments are re-aligned to match the segments
on the people group table before the partial matching is performed.
--
MODIFIED (DD-MON-YYYY)
mwcallag 17-JUN-1993 - created.
rfine 23-NOV-1994 - Suppressed index on business_group_id
amills 05-DEC-1995 - Changed date format to DDMMYYYY for
translation.
dkerr 14-MAR-1996 - 349633 - Removed hard-coded month names
jrhodes 18-FEB-1997 - 626703 - Added Application_ID and ID_Flex_Code
to query on FND_ID_FLEX_STRUCTURES
and FND_ID_FLEX_SEGMENTS
stlocke 13-JUN-2001 - Updated the process so that it calls the api
rather doing the dml in this package.
The process now also passes the increment_number,
and performs processing to allow reporting.
stlocke 12-DEC-2001 - Added additional params and processing to allow
the user to specify a business rule to increment
by such as employee's date of birth.
rem Change List
rem ===========
rem
rem Version Date Author Comment
rem -------+-----------+--------------+----------------------------------------
rem 115.39 13-MAR-2002 stlocke Changed main select cursors so as to remove
rem per_qualifications and hr_soft_coding_keyflex
rem tables as if no data in tables then cursor
rem will return no rows.
rem 115.40 14-MAR-2002 stlocke Commented in commit and exit
rem 115.41 25-APR-2002 stlocke Added functions used for employee increment
rem results report.
rem 115.42 30-APR-2002 stlocke Added code in increment available to see
rem if the special ceiling has been set, if so
rem the set grade ceiling to same value.
rem 115.43 10-MAY-2002 stlocke Fixed issues in employee increment results
rem functions for returning last values where
rem grade scale has changed but there has been an
rem update return null as values for old.
rem 115.44 14-MAY-2002 stlocke Updated so that id grade scale ceiling is step
rem assignment is on and the special ceiling is
rem higher, process will increment assignment.
rem 115.45 17-MAY-2002 stlocke Added full details on each section of
rem the increment process
rem 115.47 06-JUN-2002 stlocke Completed infile documentation.
rem 115.49 08-APR-2003 vbanner Added error handling.
rem Bug 2444703.
rem 115.50 04-JUL-2003 vanantha Bug 2999551.
rem Modified cursor csr_assignment_business_rule
rem to handle 'leapyear' problem
rem 115.51 15-JUN-2005 bshukla Bug 4432200 - Removed hard coding of
rem schema 'psp'
rem 115.53 12-May-2008 brsinha Bug 6969602. Changed cursor csr_assignment_business_rule
rem in the procudre spine.
rem 115.54 8-Dec-2011 hchintal Changed cursor C_Spinal_Placements_Cursor
rem for bug #12727056
rem ==========================================================================
*/
/* ------------------------------------------------------------------- --
-- Function to return the spinal point that was updated --
-- ------------------------------------------------------------------- */
Function func_old_spinal_point
(p_placement_id in number
,p_effective_start_date in date
,p_step_id in number
,p_grade_rate in number) return varchar2 as
Cursor csr_old_spinal_point is
select substr(psp1.spinal_point,1,20)
from per_spinal_point_placements_f spp1,
per_spinal_point_placements_f spp,
per_spinal_points psp1,
per_spinal_point_steps_f sps1,
per_spinal_point_steps_f sps2
--pay_grade_rules_f pgr2
where spp1.step_id <> p_step_id -- 343
and spp1.effective_end_date = p_effective_start_date -1
and spp.effective_start_date = p_effective_start_date
and spp1.placement_id = p_placement_id
and psp1.parent_spine_id = spp1.parent_spine_id
and spp1.step_id = sps1.step_id
and spp.step_id = sps2.step_id
and sps1.grade_spine_id = sps2.grade_spine_id
and spp.placement_id = spp1.placement_id
and sps1.spinal_point_id = psp1.spinal_point_id;
l_old_spinal_point := 'Updated First Record';
select pgr2.value
from per_spinal_point_placements_f spp1,
per_spinal_point_placements_f spp,
per_spinal_points psp1,
per_spinal_point_steps_f sps1,
pay_grade_rules_f pgr2,
per_spinal_point_steps_f sps2
where p_step_id <> spp1.step_id
and spp1.effective_end_date = p_effective_start_date -1
and spp.effective_start_date = p_effective_start_date
and spp1.placement_id = p_placement_id
and psp1.parent_spine_id = spp1.parent_spine_id
and spp1.step_id = sps1.step_id
and spp.step_id = sps2.step_id
and sps1.grade_spine_id = sps2.grade_spine_id
and spp.placement_id = spp1.placement_id
and sps1.spinal_point_id = psp1.spinal_point_id
and pgr2.grade_or_spinal_point_id = sps1.spinal_point_id
and pgr2.rate_id = (select min(rate_id)
from pay_grade_rules_f pgr4
where pgr4.grade_or_spinal_point_id = pgr2.grade_or_spinal_point_id
and (to_number(p_grade_rate) = pgr4.rate_id
or to_number(p_grade_rate) is null));
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.object_version_number = 1
and spp.placement_id = p_placement_id
and spp.effective_start_date = p_effective_start_date;
/* *** No step update in record, so no grade increment */
Cursor csr_increment2 is
select spp.placement_id
from per_spinal_point_placements_f spp,
per_spinal_point_steps_f sps
where spp.step_id = p_step_id
and spp.placement_id = p_placement_id
and spp.effective_end_date = p_effective_start_date -1
and sps.grade_spine_id = p_grade_spine_id
and sps.step_id = spp.step_id;
select spp.placement_id
from per_spinal_point_placements_f spp,
per_spinal_point_steps sps
where spp.placement_id = p_placement_id
and spp.effective_start_date = p_effective_start_date
and sps.grade_spine_id <> p_grade_spine_id
and spp.last_updated_by = -1
and spp.reason = '';
select spp.placement_id
from per_spinal_point_placements_f spp,
per_spinal_point_steps_f sps,
per_spinal_points psp
where spp.placement_id = p_placement_id
and spp.effective_start_date = p_effective_start_date
and spp.step_id = p_step_id
and sps.step_id = spp.step_id
and sps.spinal_point_id = psp.spinal_point_id
and psp.spinal_point_id = (select psp1.spinal_point_id
from per_spinal_points psp1
where psp1.parent_spine_id = spp.parent_spine_id
and psp1.sequence = (select min(sequence)
from per_spinal_points psp2,
per_grade_spines_f pgs
where psp2.parent_spine_id = pgs.parent_spine_id
and pgs.grade_spine_id = p_grade_spine_id
and psp2.parent_spine_id = psp1.parent_spine_id));
select distinct spp.placement_id
from per_spinal_point_placements_f spp,
per_spinal_point_steps_f sps
where spp.placement_id = p_placement_id
and spp.effective_start_date = p_effective_start_date
and spp.step_id = sps.step_id
and spp.step_id = p_step_id
and (spp.placement_id <> l_increment1
and spp.placement_id <> l_increment2
and spp.placement_id <> l_increment3
and spp.placement_id <> l_increment4);
Procedure update_report_exception
(p_datetrack_mode in varchar2
,p_effective_date in date
,p_placement_id in number
,p_object_version_number in number
,p_increment_number in number
,p_reason in varchar2
,p_effective_start_date in date
,p_assignment_id in number
,p_parent_spine_id in number
,p_spinal_point_id in number
,p_rate_id in number
,p_lc_step_id in number
,p_exception_report1 in varchar2
,p_end_date in date
,p_orig_increment_number in number
,p_sequence_number in number
,p_grade_spine_id in number
,p_update in varchar2
,p_max_special_sequence_number in number
,p_max_special_spinal_point in number
,p_max_sequence_number in number
,p_max_spinal_point in number) is
l_next_sequence_number number;
l_update varchar2(2);
select sequence,spinal_point_id
from per_spinal_point_steps_f
where sequence > p_sequence
and grade_spine_id = p_grade_spine_id
and p_effective_date between effective_start_date
and effective_end_date
order by sequence;
l_update := p_update;
if l_update = 'Y' then
open csr_next_sequence(p_sequence_number,
p_grade_spine_id,
p_effective_date);
l_update := 'N';
l_update := 'Y';
select step_id
into l_new_step_id
from per_spinal_point_steps_f
where sequence = l_next_sequence_number
and grade_spine_id = p_grade_spine_id
and spinal_point_id = l_next_spinal_point;
hr_sp_placement_api.update_spp
(p_datetrack_mode => p_datetrack_mode
,p_effective_date => P_Effective_Date
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_step_id => l_new_step_id
,p_increment_number => p_increment_number
,p_reason => p_reason
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
l_update := 'Y';
,p_update => l_update
);
end update_report_exception;
,p_update out nocopy varchar2
,p_increment out nocopy number
,p_max_sequence_number out nocopy number
,p_max_special_sequence_number out nocopy number
,p_max_spinal_point_id out nocopy number
,p_max_special_spinal_point_id out nocopy number) is
l_max_end_date date;
l_update varchar2(2); -- Able to update record
l_update := 'Y';
select max(effective_end_date)
into l_max_end_date
from per_spinal_point_placements_f
where placement_id = p_placement_id;
hr_utility.set_location('Datetrack mode = UPDATE',10);
l_datetrack_mode := 'UPDATE';
hr_utility.set_location('Datetrack mode = UPDATE_CHANGE_INSERT',10);
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
select nvl(sps.sequence,-99),nvl(sps.grade_spine_id,-99)
into l_future_sequence_id,
l_future_grade_spine_id
from per_spinal_point_steps_f sps,
per_spinal_point_placements_f spp
where spp.effective_start_date = p_end_date + 1
and spp.placement_id = p_placement_id
and spp.step_id = sps.step_id;
select pgs.ceiling_step_id, nvl(paa.special_ceiling_step_id,pgs.ceiling_step_id)
into l_max_ceiling_step_id,l_special_ceiling_step_id
from per_grade_spines_f pgs,
per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and pgs.parent_spine_id = p_parent_spine_id
and pgs.grade_spine_id = p_grade_spine_id
and pgs.grade_id = paa.grade_id
and P_Effective_Date between pgs.effective_start_date
and pgs.effective_end_date
and P_Effective_Date between paa.effective_start_date
and paa.effective_end_date;
if l_max_ceiling_step_id <> l_special_ceiling_step_id then -- 115.42 Update
l_max_ceiling_step_id := l_special_ceiling_step_id;
select sequence
into l_special_ceiling_sequence
from per_spinal_point_steps_f sps
where sps.grade_spine_id = p_grade_spine_id
and sps.step_id = l_special_ceiling_step_id
and p_effective_date between effective_start_date and effective_end_date;
select sps.step_id, sequence
into l_grade_max_step_id, l_grade_max_sequence
from per_spinal_point_steps_f sps
where sps.grade_spine_id = p_grade_spine_id
and sps.sequence = (select max(sequence)
from per_spinal_point_steps_f psp1
where grade_spine_id = p_grade_spine_id
and p_effective_date
between effective_start_date and effective_end_date);
l_update := 'N';
hr_utility.set_location('l_update '||l_update,201);
select psp1.sequence,
nvl(psp2.sequence,99999999),
psp1.spinal_point_id,
psp2.spinal_point_id
into l_max_sequence_number,
l_max_special_sequence_number,
l_max_spinal_point_id,
l_max_special_spinal_point_id
from per_spinal_points psp1,
per_spinal_points psp2,
per_spinal_point_steps_f sps1,
per_spinal_point_steps_f sps2
where psp1.spinal_point_id = sps1.spinal_point_id
and psp2.spinal_point_id = sps2.spinal_point_id
and sps1.step_id = l_max_ceiling_step_id
and sps2.step_id = l_special_ceiling_step_id;
select count(sps.sequence)
into l_max_count
from per_spinal_point_steps_f sps
where sps.sequence > p_sequence_number
and sps.grade_spine_id = p_grade_spine_id
and sps.sequence <= l_max_sequence_number
and sps.sequence <= l_max_special_sequence_number
and sps.sequence <= l_grade_max_sequence
and sps.sequence <= l_future_sequence_id
and p_effective_date between sps.effective_start_date
and sps.effective_end_date
order by sps.sequence;
hr_utility.set_location('L_Max_Count = 0 so update denied',141);
l_update := 'N';
p_update := l_update;
select 1
from hr_legal_entities
where organization_id = p_legal_entity
and business_group_id = p_business_group_id;
The process has been updated to supply many new features to the increment
process. The first and most import one is that an assignment can be
incremented by greater than just one point. This is determined by a new
parameter on the grade step placement form, Increment Number, this is a
mandatory parameter. This allows the user to change the number of steps
that an assignment goes up by each time.
(Within this process there is a check procedure that finds the number of
valid steps left for the assignment on its current grade and if the
increment number is greater than this then the number is set to the number
of steps left on the grade.)
The second main new feature is the abbility to run the process in a 'What If'
mode. What this means is that you can run the increment process in a rollback
mode. Because of the addition or reporting that has been added to the process
it is possible to run the Increment process but without commiting the database,
so that you can see who is incremented and by how much, who is not incremented,
and who is incremented but reaches some kind of ceiling during the process.
There has also been the addition of many new parameters to the Increment process
to allow the user to restrict which assignments are incremented.
In addition to standard restriction parameters that maps to table columns there
has also been the addition of business rule parameters. What these do is to
allow the user to increment an assignment based on say the employee's date of
birth. This also allows the restriction for a date period, so you could specify
that you wish to increment all those employee's that have a birthday in march.
There are also an additional two parameters that allow you to specify an age range,
so for the example just given you could also specify 21 - 30 so you would only
increment employee's who had a birthday in march and were between the ages of 21
to 30. This increment will be performed based on the business rule effective date.
This means that the increment can be done on the date of birth for each employee,
the start of the next month etc.
*/
/* -------------------------------------------------------------------- */
-- Main Entry Point To Increment Procedure --
-- -------------------------------------------------------------------- --
-- --
-- Depending on if the business rule parameters have been set call the --
-- correct code and try to increment the valid assignments. --
-- --
-- Processing logic of procedure - --
-- --
-- 1. Check business group id passed is valid. --
-- 2. Call the businss rule check procedure. --
-- 3. Call the constraint check procedure --
-- 4. If the concat segs parameter is not null then correctly set --
-- concat seg values. --
-- 5. If business_rule is null then --
-- a. Call check increment available procedure. --
-- b. Call update report exception procedure. --
-- --
-- 6. Else if business_rule is not null then --
-- a. Check date_from and date_to parameters for year overlap. --
-- b. Set local dates / years accordingly. --
-- c. Call cursor to return all assignment info for business rule --
-- process, loop following until no more rows. --
-- d. When no data found set the local variables to null. --
-- e. If l_first_call flag = FALSE then --
-- 1. Check if the copied assign param matches fetched assign_id --
-- and set local parameters accordingly. --
-- 2. Depending on business rule selected, check dates for copy --
-- to fetch version and set local params accordingly. --
-- 3. Check if the year loops over an end of year and set the --
-- appropriate year accordingly etc. --
-- 4. Depending on the dependant date set the effective dates --
-- accordingly. --
-- 5. Fetch details of assignment to be incremented using main --
-- select cursor. --
-- 6. Call check increment available procedure. --
-- 7. Call update report exception procedure. --
-- 7. Update table per_parent_spines to set the date for --
-- last_automatic_increment_date. --
-- --
/* -------------------------------------------------------------------- */
procedure spine
(
P_Parent_Spine_ID in number default null,
P_Effective_Date in date,
p_id_flex_num in number default null,
p_concat_segs in varchar2 default null,
P_Business_Group_ID in number,
p_collective_agreement_id in number default null,
p_person_id in number default null,
p_payroll_id in number default null,
p_organization_id in number default null,
p_legal_entity in number default null,
p_org_structure_ver_id in number default null,
p_qual_type in number default null,
p_qual_status in varchar2 default null,
p_org_structure_top_node in number default null,
p_rate_id in number default null,
p_business_rule in varchar2 default null,
p_dependant_date in varchar2 default null,
p_br_date_from in date default null,
p_br_date_to in date default null,
p_year_from in number default null,
p_year_to in number default null,
p_message_number out nocopy varchar2
) is
--
-- this cursor is used to get the order of the segments from the foundation
-- table
--
cursor c1 is
select application_column_name
from fnd_id_flex_segments
where id_flex_num = p_id_flex_num
and application_id = 801
and id_flex_code = 'GRP'
and enabled_flag = 'Y'
order by segment_num;
l_datetrack_mode varchar2(30) := 'UPDATE_CHANGE_INSERT';
l_update varchar2(2);
l_update2 varchar2(2);
l_br_update varchar2(2) := 'N';
SELECT distinct spp.placement_id,
spp.step_id,
spp.assignment_id,
spp.auto_increment_flag,
sps.spinal_point_id,
spp.parent_spine_id,
psp.sequence,
NVL(spp.increment_number,0),
spp.object_version_number,
spp.effective_start_date,
spp.effective_end_date,
pgs.grade_spine_id
FROM per_all_assignments_f asg,
per_grade_spines_f pgs,
per_spinal_points psp1,
per_spinal_point_steps_f sps1,
per_spinal_points psp,
per_spinal_point_steps_f sps,
per_spinal_point_placements_f spp,
per_periods_of_service pps --12727056
WHERE (p_parent_spine_id is null -- PARENT_SPINE_ID
OR
(spp.parent_spine_id is not null
and spp.parent_spine_id = P_Parent_Spine_ID))
AND spp.business_group_id = P_Business_Group_ID
AND spp.step_id = sps.step_id
AND sps.grade_spine_id = pgs.grade_spine_id
AND sps.spinal_point_id = psp.spinal_point_id
AND spp.auto_increment_flag = 'Y'
AND psp1.parent_spine_id = spp.parent_spine_id
AND sps1.grade_spine_id = sps.grade_spine_id
AND sps1.spinal_point_id = psp1.spinal_point_id
AND pgs.grade_id = asg.grade_id
AND asg.assignment_id = spp.assignment_id
AND (p_collective_agreement_id is NULL -- COLLECTIVE_AGREEMENT_ID
OR
(asg.collective_agreement_id is not null
and asg.collective_agreement_id = p_collective_agreement_id))
AND (p_business_rule is NULL -- Only use assignment id if business rule is not null
OR
(p_business_rule is not null
and asg.assignment_id = l_copy_br_assignment_id))
AND (p_person_id is NULL -- PERSON_ID
OR
(asg.person_id is not null
and asg.person_id = p_person_id))
AND (p_payroll_id is NULL -- PAYROLL_ID
OR
(asg.payroll_id is not null
and asg.payroll_id = p_payroll_id))
AND (p_organization_id is NULL -- ORGANIZATION_ID
OR
(asg.organization_id is not null
and asg.organization_id = p_organization_id))
AND (p_legal_entity is NULL -- LEGAL ENTITY
OR
exists (select 1
from hr_soft_coding_keyflex sck
where asg.soft_coding_keyflex_id is not null
and asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = p_legal_entity))
AND (p_qual_type is NULL
OR
exists (select 1
from per_qualifications pq
where asg.person_id = pq.person_id
and pq.qualification_type_id = p_qual_type))
AND (p_qual_status is NULL
OR
exists (select 1
from per_qualifications pq
where asg.person_id = pq.person_id
and pq.qualification_type_id = p_qual_type
and pq.status = p_qual_status))
AND (p_org_structure_ver_id is NULL -- OGANIZATION HIERARCHY
OR
(exists
(select 1
from per_org_structure_elements ose
where ose.org_structure_version_id = p_org_structure_ver_id
and asg.organization_id = ose.organization_id_child
connect by prior ose.organization_id_child = ose.organization_id_parent
and ose.org_structure_version_id = p_org_structure_ver_id
start with ose.organization_id_parent = p_org_structure_top_node
and ose.org_structure_version_id = p_org_structure_ver_id)))
AND l_effective_date BETWEEN spp.effective_start_date
AND spp.effective_end_date
AND l_effective_date BETWEEN sps.effective_start_date
AND sps.effective_end_date
AND l_effective_date BETWEEN pgs.effective_start_date
AND pgs.effective_end_date
AND l_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND l_effective_date BETWEEN sps1.effective_start_date
AND sps1.effective_end_date
AND psp1.sequence = psp.sequence
--12727056 Start
AND asg.period_of_service_id = pps.period_of_service_id
AND l_effective_date BETWEEN pps.date_start
AND nvl(pps.actual_termination_date, pps.date_start);
SELECT distinct spp.placement_id,
spp.step_id,
spp.assignment_id,
spp.auto_increment_flag,
sps.spinal_point_id,
spp.parent_spine_id,
psp.sequence,
NVL(spp.increment_number,0),
spp.object_version_number,
spp.effective_start_date,
spp.effective_end_date,
pgs.grade_spine_id
FROM per_all_assignments_f asg,
pay_people_groups ppg,
per_grade_spines_f pgs,
per_spinal_points psp1,
per_spinal_point_steps_f sps1,
per_spinal_points psp,
per_spinal_point_steps_f sps,
per_spinal_point_placements_f spp
WHERE (p_parent_spine_id is null -- PARENT_SPINE_ID
OR
(spp.parent_spine_id is not null
and spp.parent_spine_id = P_Parent_Spine_ID))
AND spp.business_group_id = P_Business_Group_ID
AND spp.step_id = sps.step_id
AND sps.grade_spine_id = pgs.grade_spine_id
AND sps.spinal_point_id = psp.spinal_point_id
AND spp.auto_increment_flag = 'Y'
AND psp1.parent_spine_id = spp.parent_spine_id
AND sps1.grade_spine_id = sps.grade_spine_id
AND sps1.spinal_point_id = psp1.spinal_point_id
AND pgs.grade_id = asg.grade_id
AND asg.assignment_id = spp.assignment_id
AND (p_collective_agreement_id is NULL -- COLLECTIVE_AGREEMENT_ID
OR
(asg.collective_agreement_id is not null
and asg.collective_agreement_id = p_collective_agreement_id))
AND (p_business_rule is NULL -- Only use assignment id if business rule is not null
OR
(p_business_rule is not null
and asg.assignment_id = l_copy_br_assignment_id))
AND (p_person_id is NULL -- PERSON_ID
OR
(asg.person_id is not null
and asg.person_id = p_person_id))
AND (p_payroll_id is NULL -- PAYROLL_ID
OR
(asg.payroll_id is not null
and asg.payroll_id = p_payroll_id))
AND (p_organization_id is NULL -- ORGANIZATION_ID
OR
(asg.organization_id is not null
and asg.organization_id = p_organization_id))
AND (p_legal_entity is NULL -- LEGAL ENTITY
OR
exists (select 1
from hr_soft_coding_keyflex sck
where asg.soft_coding_keyflex_id is not null
and asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = p_legal_entity))
AND (p_qual_type is NULL
OR
exists (select 1
from per_qualifications pq
where asg.person_id = pq.person_id
and pq.qualification_type_id = p_qual_type))
AND (p_qual_status is NULL
OR
exists (select 1
from per_qualifications pq
where asg.person_id = pq.person_id
and pq.qualification_type_id = p_qual_type
and pq.status = p_qual_status))
AND (p_org_structure_ver_id is NULL -- OGANIZATION HIERARCHY
OR
(exists
(select 1
from per_org_structure_elements ose
where ose.org_structure_version_id = p_org_structure_ver_id
and asg.organization_id = ose.organization_id_child
connect by prior ose.organization_id_child = ose.organization_id_parent
and ose.org_structure_version_id = p_org_structure_ver_id
start with ose.organization_id_parent = p_org_structure_top_node
and ose.org_structure_version_id = p_org_structure_ver_id)))
AND l_effective_date BETWEEN spp.effective_start_date
AND spp.effective_end_date
AND l_effective_date BETWEEN sps.effective_start_date
AND sps.effective_end_date
AND l_effective_date BETWEEN pgs.effective_start_date
AND pgs.effective_end_date
AND l_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND l_effective_date BETWEEN sps1.effective_start_date
AND sps1.effective_end_date
AND l_effective_date BETWEEN sps.effective_start_date
AND asg.effective_end_date
AND psp1.sequence = psp.sequence
AND asg.people_group_id = ppg.people_group_id
AND NVL(l_seg1, NVL( ppg.Segment1, L_Pass_String)) =
NVL(ppg.Segment1, L_Pass_String)
AND NVL(l_seg2, NVL( ppg.Segment2, L_Pass_String)) =
NVL(ppg.Segment2, L_Pass_String)
AND NVL(l_seg3, NVL( ppg.Segment3, L_Pass_String)) =
NVL(ppg.Segment3, L_Pass_String)
AND NVL(l_seg4, NVL( ppg.Segment4, L_Pass_String)) =
NVL(ppg.Segment4, L_Pass_String)
AND NVL(l_seg5, NVL( ppg.Segment5, L_Pass_String)) =
NVL(ppg.Segment5, L_Pass_String)
AND NVL(l_seg6, NVL( ppg.Segment6, L_Pass_String)) =
NVL(ppg.Segment6, L_Pass_String)
AND NVL(l_seg7, NVL( ppg.Segment7, L_Pass_String)) =
NVL(ppg.Segment7, L_Pass_String)
AND NVL(l_seg8, NVL( ppg.Segment8, L_Pass_String)) =
NVL(ppg.Segment8, L_Pass_String)
AND NVL(l_seg9, NVL( ppg.Segment9, L_Pass_String)) =
NVL(ppg.Segment9, L_Pass_String)
AND NVL(l_seg10, NVL( ppg.Segment10, L_Pass_String)) =
NVL(ppg.Segment10, L_Pass_String)
AND NVL(l_seg11, NVL( ppg.Segment11, L_Pass_String)) =
NVL(ppg.Segment11, L_Pass_String)
AND NVL(l_seg12, NVL( ppg.Segment12, L_Pass_String)) =
NVL(ppg.Segment12, L_Pass_String)
AND NVL(l_seg13, NVL( ppg.Segment13, L_Pass_String)) =
NVL(ppg.Segment13, L_Pass_String)
AND NVL(l_seg14, NVL( ppg.Segment14, L_Pass_String)) =
NVL(ppg.Segment14, L_Pass_String)
AND NVL(l_seg15, NVL( ppg.Segment15, L_Pass_String)) =
NVL(ppg.Segment15, L_Pass_String)
AND NVL(l_seg16, NVL( ppg.Segment16, L_Pass_String)) =
NVL(ppg.Segment16, L_Pass_String)
AND NVL(l_seg17, NVL( ppg.Segment17, L_Pass_String)) =
NVL(ppg.Segment17, L_Pass_String)
AND NVL(l_seg18, NVL( ppg.Segment18, L_Pass_String)) =
NVL(ppg.Segment18, L_Pass_String)
AND NVL(l_seg19, NVL( ppg.Segment19, L_Pass_String)) =
NVL(ppg.Segment19, L_Pass_String)
AND NVL(l_seg20, NVL( ppg.Segment20, L_Pass_String)) =
NVL(ppg.Segment20, L_Pass_String)
AND NVL(l_seg21, NVL( ppg.Segment21, L_Pass_String)) =
NVL(ppg.Segment21, L_Pass_String)
AND NVL(l_seg22, NVL( ppg.Segment22, L_Pass_String)) =
NVL(ppg.Segment22, L_Pass_String)
AND NVL(l_seg23, NVL( ppg.Segment23, L_Pass_String)) =
NVL(ppg.Segment23, L_Pass_String)
AND NVL(l_seg24, NVL( ppg.Segment24, L_Pass_String)) =
NVL(ppg.Segment24, L_Pass_String)
AND NVL(l_seg25, NVL( ppg.Segment25, L_Pass_String)) =
NVL(ppg.Segment25, L_Pass_String)
AND NVL(l_seg26, NVL( ppg.Segment26, L_Pass_String)) =
NVL(ppg.Segment26, L_Pass_String)
AND NVL(l_seg27, NVL( ppg.Segment27, L_Pass_String)) =
NVL(ppg.Segment27, L_Pass_String)
AND NVL(l_seg28, NVL( ppg.Segment28, L_Pass_String)) =
NVL(ppg.Segment28, L_Pass_String)
AND NVL(l_seg29, NVL( ppg.Segment29, L_Pass_String)) =
NVL(ppg.Segment29, L_Pass_String)
AND NVL(l_seg30, NVL( ppg.Segment30, L_Pass_String)) =
NVL(ppg.Segment30, L_Pass_String);
select paa.assignment_id,
pap.date_of_birth,
pos.date_start,
pap.original_date_of_hire,
pos.adjusted_svc_date,
paa.payroll_id
from per_all_people_f pap,
per_periods_of_service pos,
per_all_assignments_f paa,
per_spinal_point_placements_f spp
where spp.assignment_id = paa.assignment_id
and paa.person_id = pap.person_id
and pos.person_id = pap.person_id
and spp.business_group_id = p_business_group_id
and spp.effective_end_date >= l_br_date_from
and (('AOJ' = p_business_rule -- ***** ANNIVERSARY OF JOINING ***** --Bug #2999551
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)
between l_br_date_from and l_br_date_to
and paa.period_of_service_id = pos.period_of_service_id
and pap.original_date_of_hire between decode(p_year_to, null, l_earliest_start_date,
add_months(l_br_date_from, - (p_year_to * 12)))
and decode(p_year_from, null, l_br_date_to,
add_months(l_br_date_to, - (p_year_from * 12))))
or ('DOB' = p_business_rule -- ***** DATE OF BIRTH ***** --Bug #2999551
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)
between l_br_date_from and l_br_date_to
and pap.date_of_birth between decode(p_year_to, null, l_earliest_start_date,
add_months(l_br_date_from, - (p_year_to * 12)))
and decode(p_year_from, null, l_br_date_to,
add_months(l_br_date_to, - (p_year_from * 12))))
or ('ASD' = p_business_rule -- ***** AJUSTED SERVICE DATE *****
and pos.adjusted_svc_date is not null --Bug #2999551
AND paa.period_of_service_id = pos.period_of_service_id -- bug 6969602
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)
between l_br_date_from and l_br_date_to
and pos.adjusted_svc_date between decode(p_year_to, null, l_earliest_start_date,
add_months(l_br_date_from, - (p_year_to * 12)))
and decode(p_year_from, null, l_br_date_to,
add_months(l_br_date_to, - (p_year_from * 12))))
or ('LHD' = p_business_rule -- ***** LATEST HIRE DATE ***** --Bug # 2999551
and add_months(pos.date_start, (to_number(substr(l_year_temp, 2, 4))- to_number(to_char(pos.date_start,'YYYY')))*12)
between l_br_date_from and l_br_date_to
and paa.period_of_service_id = pos.period_of_service_id
and pos.date_start between decode(p_year_to, null, l_earliest_start_date,
add_months(l_br_date_from, - (p_year_to * 12)))
and decode(p_year_from, null, l_br_date_to,
add_months(l_br_date_to, - (p_year_from * 12)))))
order by paa.assignment_id;
select ptp.start_date
from per_time_periods ptp
where ptp.payroll_id = l_copy_payroll_id
and ptp.start_date > l_effective_date;
select concatenated_segment_delimiter
into l_concat_sep
from fnd_id_flex_structures
where id_flex_num = p_id_flex_num
and application_id = 801
and id_flex_code = 'GRP';
-- Call the procedure to check if assignment selected can be
-- incremented
--
check_increment_available
(p_placement_id => LC_Placement_ID
,p_end_date => L_End_Date
,p_effective_date => l_effective_date
,p_effective_start_date => LC_Effective_Start_Date
,p_datetrack_mode => l_datetrack_mode
,p_assignment_id => LC_Assignment_ID
,p_parent_spine_id => LC_Parent_Spine_ID
,p_grade_spine_id => l_grade_spine_id
,p_step_id => LC_Step_ID
,p_sequence_number => LC_Sequence_Number
,p_increment_number => LC_Increment_Number
,p_exception_report1 => l_exception_report1
,p_update => l_update
,p_increment => l_increment
,p_max_sequence_number => l_max_sequence_number
,p_max_special_sequence_number => l_max_special_sequence_number
,p_max_spinal_point_id => l_max_spinal_point_id
,p_max_special_spinal_point_id => l_max_special_spinal_point_id );
-- Call procedure to update the record and report on records
-- updated and missed
--
update_report_exception
(p_datetrack_mode => l_datetrack_mode
,p_effective_date => l_effective_date
,p_placement_id => LC_Placement_ID
,p_object_version_number => LC_Object_Version_Number
,p_increment_number => l_increment
,p_reason => l_reason
,p_effective_start_date => LC_Effective_Start_Date
,p_assignment_id => LC_Assignment_ID
,p_parent_spine_id => LC_Parent_Spine_ID
,p_spinal_point_id => LC_Spinal_Point_ID
,p_rate_id => p_rate_id
,p_lc_step_id => LC_Step_ID
,p_exception_report1 => l_exception_report1
,p_end_date => L_End_Date
,p_orig_increment_number => LC_Increment_Number
,p_sequence_number => LC_Sequence_Number
,p_grade_spine_id => l_grade_spine_id
,p_update => l_update
,p_max_special_sequence_number => l_max_special_sequence_number
,p_max_special_spinal_point => l_max_special_spinal_point_id
,p_max_sequence_number => l_max_sequence_number
,p_max_spinal_point => l_max_spinal_point_id);
l_br_update := 'N';
l_br_update := 'Y';
l_br_update := 'N';
l_br_update := 'N';
l_br_update := 'N';
l_br_update := 'N';
l_br_update := 'N';
l_br_update := 'N';
-- Depending on the dependant date selected in the concurrent process set the
-- effective date to use
--
-- ===============================================================================
hr_utility.set_location('--------- Dependant Date Rule - '||p_dependant_date,32);
l_update2 := 'N';
l_update2 := 'Y';
hr_utility.set_location('l_br_update - '||l_br_update,40);
if l_br_update = 'Y'
and l_duplicate_flag = FALSE
and l_duplicate_error_flag = FALSE then
hr_utility.set_location('++++++++++++++ UPDATING ++++++++++++++++++',45);
,p_update => l_update
,p_increment => l_increment
,p_max_sequence_number => l_max_sequence_number
,p_max_special_sequence_number => l_max_special_sequence_number
,p_max_spinal_point_id => l_max_spinal_point_id
,p_max_special_spinal_point_id => l_max_special_spinal_point_id );
if l_update = 'N' or l_update2 = 'N' then
l_update := 'N';
update_report_exception
(p_datetrack_mode => l_datetrack_mode
,p_effective_date => l_effective_date
,p_placement_id => LC_Placement_ID
,p_object_version_number => LC_Object_Version_Number
,p_increment_number => l_increment
,p_reason => l_reason
,p_effective_start_date => LC_Effective_Start_Date
,p_assignment_id => LC_Assignment_ID
,p_parent_spine_id => LC_Parent_Spine_ID
,p_spinal_point_id => LC_Spinal_Point_ID
,p_rate_id => p_rate_id
,p_lc_step_id => LC_Step_ID
,p_exception_report1 => l_exception_report1
,p_end_date => L_End_Date
,p_orig_increment_number => LC_Increment_Number
,p_sequence_number => LC_Sequence_Number
,p_grade_spine_id => l_grade_spine_id
,p_update => l_update
,p_max_special_sequence_number => l_max_special_sequence_number
,p_max_special_spinal_point => l_max_special_spinal_point_id
,p_max_sequence_number => l_max_sequence_number
,p_max_spinal_point => l_max_spinal_point_id);
end if; -- l_br_update = Y
UPDATE per_parent_spines
SET last_automatic_increment_date = P_Effective_Date,
last_update_date = sysdate,
program_update_date = sysdate
WHERE parent_spine_id = P_Parent_Spine_ID;