[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| FUNCTION: update_object_version
|| DESCRIPTION: Update the object version number in the transaction step
|| to pass the invalid object api error for Save for Later.
||=======================================================================
*/
PROCEDURE update_object_version
(p_transaction_step_id in number
,p_login_person_id in number) IS
CURSOR csr_new_object_number(p_period_of_service_id in number) is
SELECT object_version_number
FROM per_periods_of_service pps
where period_of_service_id = p_period_of_service_id;
l_proc constant varchar2(100) := g_package || ' update_object_version';
END update_object_version;
PROCEDURE update_per_details(
p_validate in number default 0
,p_effective_date in date
,p_period_of_service_id in number
,p_actual_termination_date in date
,p_rehire_recommendation in varchar2 default hr_api.g_varchar2
,p_rehire_reason in varchar2 default hr_api.g_varchar2
) IS
l_person_id per_all_people_f.person_id%TYPE;
l_proc varchar2(100) := g_package ||'update_per_details';
select per.person_id
, per.employee_number
, per.object_version_number
from per_all_people_f per
, per_business_groups bus
, per_periods_of_service pds
, per_person_types pet
where pds.period_of_service_id = p_period_of_service_id
and bus.business_group_id = pds.business_group_id
and per.person_id = pds.person_id
and p_actual_termination_date + 1 between per.effective_start_date
and per.effective_end_date
and pet.person_type_id = per.person_type_id;
SAVEPOINT update_person_details;
hr_person_api.update_person (
p_validate => l_validate
,p_effective_date => p_effective_date + 1
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_rehire_recommendation => p_rehire_recommendation
,p_rehire_reason => p_rehire_reason
,p_full_name => l_full_name
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
ROLLBACK TO update_person_details;
rollback TO update_person_details;
END update_per_details;
SELECT ppf.person_id
FROM per_all_people_f ppf -- Bug 2130066 fix
,per_all_assignments_f paf
,per_periods_of_service ppos
WHERE paf.supervisor_id = l_number_value
AND paf.person_id = ppf.person_id
AND ppf.person_id = ppos.person_id
AND ppf.current_employee_flag = 'Y'
AND l_effective_date between ppf.effective_start_date
and ppf.effective_end_date -1 --added -1 for Bug 12360962
-- AND paf.primary_flag = 'Y' -- commented to support multiple assignments
AND paf.assignment_type = 'E'
AND l_effective_date between paf.effective_start_date
and paf.effective_end_date
AND l_effective_date between ppos.date_start
and nvl(ppos.actual_termination_date
,l_effective_date)
and ((dummy = 'N' and paf.supervisor_assignment_id=l_asg_number_value)
OR dummy = 'Y')
UNION -- CWK Phase III Changes.
SELECT ppf.person_id
FROM per_all_people_f ppf -- Bug 2130066 fix
,per_all_assignments_f paf
,per_periods_of_placement ppop
WHERE paf.supervisor_id = l_number_value
AND paf.person_id = ppf.person_id
AND ppf.person_id = ppop.person_id
AND ppf.current_npw_flag = 'Y'
AND l_effective_date between ppf.effective_start_date
and ppf.effective_end_date -1 --added -1 for Bug 12360962
-- AND paf.primary_flag = 'Y' -- commented to support multiple assignments
AND paf.assignment_type = 'C'
AND l_effective_date between paf.effective_start_date
and paf.effective_end_date
AND l_effective_date between ppop.date_start
and nvl(ppop.actual_termination_date
,l_effective_date)
and ((dummy = 'N' and paf.supervisor_assignment_id=l_asg_number_value)
OR dummy = 'Y');
SELECT WAAV.TEXT_VALUE Value
FROM WF_ACTIVITY_ATTR_VALUES WAAV
WHERE WAAV.PROCESS_ACTIVITY_ID = actid
AND WAAV.NAME = name;
select primary_flag into dummy from per_all_assignments_f
where assignment_id=l_asg_number_value and l_effective_date between effective_start_date
and effective_end_date;
update_pds_details_err exception;
select business_group_id
into l_business_group_id
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
hr_utility.trace('Calling hr_termination_ss.update_pds_details ' || l_proc);
hr_termination_ss.update_pds_details
(p_validate => 0
,p_effective_date => p_effective_date
,p_period_of_service_id => p_period_of_service_id
,p_termination_accepted_person => p_termination_accepted_person
,p_accepted_termination_date => p_accepted_termination_date
,p_object_version_number => p_object_version_number
,p_comments => p_comments
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_pds_information_category => l_pds_information_category -- bug 2881583
,p_pds_information1 => p_pds_information1
,p_pds_information2 => p_pds_information2
,p_pds_information3 => p_pds_information3
,p_pds_information4 => p_pds_information4
,p_pds_information5 => p_pds_information5
,p_pds_information6 => p_pds_information6
,p_pds_information7 => p_pds_information7
,p_pds_information8 => p_pds_information8
,p_pds_information9 => p_pds_information9
,p_pds_information10 => l_pds_information10 -- bug 2881583
,p_pds_information11 => p_pds_information11
,p_pds_information12 => p_pds_information12
,p_pds_information13 => p_pds_information13
,p_pds_information14 => p_pds_information14
,p_pds_information15 => p_pds_information15
,p_pds_information16 => p_pds_information16
,p_pds_information17 => p_pds_information17
,p_pds_information18 => p_pds_information18
,p_pds_information19 => p_pds_information19
,p_pds_information20 => p_pds_information20
,p_pds_information21 => p_pds_information21
,p_pds_information22 => p_pds_information22
,p_pds_information23 => p_pds_information23
,p_pds_information24 => p_pds_information24
,p_pds_information25 => p_pds_information25
,p_pds_information26 => p_pds_information26
,p_pds_information27 => p_pds_information27
,p_pds_information28 => p_pds_information28
,p_pds_information29 => p_pds_information29
,p_pds_information30 => p_pds_information30
);
hr_utility.trace('Calling hr_termination_ss.update_per_details ' || l_proc);
update_per_details(
p_validate => 0, -- false
p_effective_date => p_effective_date,
p_period_of_service_id => p_period_of_service_id,
p_actual_termination_date => p_actual_termination_date,
p_rehire_recommendation => p_rehire_recommendation,
p_rehire_reason => p_rehire_reason);
|| PROCEDURE: update_pds_details
||---------------------------------------------------------------------------
||
|| Description:
|| This procedure will call the actual API -
|| hr_periods_of_service_api.update_pds_details
||
|| Pre Conditions:
||
|| In Arguments:
|| Contains entire list of parameters that are defined in the actual
|| API. For details see pepdsapi.pkb file.
||
|| out nocopy Arguments:
||
|| In out nocopy Arguments:
||
|| Post Success:
|| Executes the API call.
||
|| Post Failure:
|| Raises an exception
||
|| Access Status:
|| Public.
||
||===========================================================================
*/
PROCEDURE update_pds_details
(p_validate in number default 0
,p_effective_date in date
,p_period_of_service_id in number
,p_termination_accepted_person in number default hr_api.g_number
,p_accepted_termination_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_comments in varchar2 default hr_api.g_varchar2
,p_leaving_reason in varchar2 default hr_api.g_varchar2
,p_notified_termination_date in date default hr_api.g_date
,p_projected_termination_date in date default hr_api.g_date
,p_attribute_category in varchar2 default hr_api.g_varchar2
,p_attribute1 in varchar2 default hr_api.g_varchar2
,p_attribute2 in varchar2 default hr_api.g_varchar2
,p_attribute3 in varchar2 default hr_api.g_varchar2
,p_attribute4 in varchar2 default hr_api.g_varchar2
,p_attribute5 in varchar2 default hr_api.g_varchar2
,p_attribute6 in varchar2 default hr_api.g_varchar2
,p_attribute7 in varchar2 default hr_api.g_varchar2
,p_attribute8 in varchar2 default hr_api.g_varchar2
,p_attribute9 in varchar2 default hr_api.g_varchar2
,p_attribute10 in varchar2 default hr_api.g_varchar2
,p_attribute11 in varchar2 default hr_api.g_varchar2
,p_attribute12 in varchar2 default hr_api.g_varchar2
,p_attribute13 in varchar2 default hr_api.g_varchar2
,p_attribute14 in varchar2 default hr_api.g_varchar2
,p_attribute15 in varchar2 default hr_api.g_varchar2
,p_attribute16 in varchar2 default hr_api.g_varchar2
,p_attribute17 in varchar2 default hr_api.g_varchar2
,p_attribute18 in varchar2 default hr_api.g_varchar2
,p_attribute19 in varchar2 default hr_api.g_varchar2
,p_attribute20 in varchar2 default hr_api.g_varchar2
,p_pds_information_category in varchar2 default hr_api.g_varchar2
,p_pds_information1 in varchar2 default hr_api.g_varchar2
,p_pds_information2 in varchar2 default hr_api.g_varchar2
,p_pds_information3 in varchar2 default hr_api.g_varchar2
,p_pds_information4 in varchar2 default hr_api.g_varchar2
,p_pds_information5 in varchar2 default hr_api.g_varchar2
,p_pds_information6 in varchar2 default hr_api.g_varchar2
,p_pds_information7 in varchar2 default hr_api.g_varchar2
,p_pds_information8 in varchar2 default hr_api.g_varchar2
,p_pds_information9 in varchar2 default hr_api.g_varchar2
,p_pds_information10 in varchar2 default hr_api.g_varchar2
,p_pds_information11 in varchar2 default hr_api.g_varchar2
,p_pds_information12 in varchar2 default hr_api.g_varchar2
,p_pds_information13 in varchar2 default hr_api.g_varchar2
,p_pds_information14 in varchar2 default hr_api.g_varchar2
,p_pds_information15 in varchar2 default hr_api.g_varchar2
,p_pds_information16 in varchar2 default hr_api.g_varchar2
,p_pds_information17 in varchar2 default hr_api.g_varchar2
,p_pds_information18 in varchar2 default hr_api.g_varchar2
,p_pds_information19 in varchar2 default hr_api.g_varchar2
,p_pds_information20 in varchar2 default hr_api.g_varchar2
,p_pds_information21 in varchar2 default hr_api.g_varchar2
,p_pds_information22 in varchar2 default hr_api.g_varchar2
,p_pds_information23 in varchar2 default hr_api.g_varchar2
,p_pds_information24 in varchar2 default hr_api.g_varchar2
,p_pds_information25 in varchar2 default hr_api.g_varchar2
,p_pds_information26 in varchar2 default hr_api.g_varchar2
,p_pds_information27 in varchar2 default hr_api.g_varchar2
,p_pds_information28 in varchar2 default hr_api.g_varchar2
,p_pds_information29 in varchar2 default hr_api.g_varchar2
,p_pds_information30 in varchar2 default hr_api.g_varchar2
)
IS
l_proc constant varchar2(100) := g_package || ' update_pds_details';
hr_periods_of_service_api.update_pds_details
(p_validate => hr_java_conv_util_ss.get_boolean (p_number => p_validate)
,p_effective_date => p_effective_date
,p_period_of_service_id => p_period_of_service_id
,p_termination_accepted_person => p_termination_accepted_person
,p_accepted_termination_date => p_accepted_termination_date
,p_object_version_number => p_object_version_number
,p_comments => p_comments
,p_leaving_reason => p_leaving_reason
,p_notified_termination_date => p_notified_termination_date
,p_projected_termination_date => p_projected_termination_date
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_pds_information_category => p_pds_information_category
,p_pds_information1 => p_pds_information1
,p_pds_information2 => p_pds_information2
,p_pds_information3 => p_pds_information3
,p_pds_information4 => p_pds_information4
,p_pds_information5 => p_pds_information5
,p_pds_information6 => p_pds_information6
,p_pds_information7 => p_pds_information7
,p_pds_information8 => p_pds_information8
,p_pds_information9 => p_pds_information9
,p_pds_information10 => p_pds_information10
,p_pds_information11 => p_pds_information11
,p_pds_information12 => p_pds_information12
,p_pds_information13 => p_pds_information13
,p_pds_information14 => p_pds_information14
,p_pds_information15 => p_pds_information15
,p_pds_information16 => p_pds_information16
,p_pds_information17 => p_pds_information17
,p_pds_information18 => p_pds_information18
,p_pds_information19 => p_pds_information19
,p_pds_information20 => p_pds_information20
,p_pds_information21 => p_pds_information21
,p_pds_information22 => p_pds_information22
,p_pds_information23 => p_pds_information23
,p_pds_information24 => p_pds_information24
,p_pds_information25 => p_pds_information25
,p_pds_information26 => p_pds_information26
,p_pds_information27 => p_pds_information27
,p_pds_information28 => p_pds_information28
,p_pds_information29 => p_pds_information29
,p_pds_information30 => p_pds_information30);
END update_pds_details;
|| : and call the APIs in update mode
||=======================================================================
*/
PROCEDURE process_api (
p_validate IN BOOLEAN DEFAULT FALSE,
p_transaction_step_id IN NUMBER DEFAULT NULL,
p_effective_date IN VARCHAR2 DEFAULT NULL
)
IS
lrt_termination hr_termination_ss.rt_termination;
select business_group_id
into l_business_group_id
from per_periods_of_service
where period_of_service_id = ln_period_of_service_id;
hr_periods_of_service_api.update_pds_details (
p_validate => FALSE,
p_effective_date => ld_actual_term_date,
p_period_of_service_id => ln_period_of_service_id,
p_object_version_number => ln_object_version_number,
p_comments => ll_term_comments,
p_leaving_reason => lv_term_reason,
p_notified_termination_date => ld_notified_term_date,
p_projected_termination_date => ld_projected_termination_date,
p_attribute_category => lv_attribute_category,
p_attribute1 => lt_term_flex(1),
p_attribute2 => lt_term_flex(2),
p_attribute3 => lt_term_flex(3),
p_attribute4 => lt_term_flex(4),
p_attribute5 => lt_term_flex(5),
p_attribute6 => lt_term_flex(6),
p_attribute7 => lt_term_flex(7),
p_attribute8 => lt_term_flex(8),
p_attribute9 => lt_term_flex(9),
p_attribute10 => lt_term_flex(10),
p_attribute11 => lt_term_flex(11),
p_attribute12 => lt_term_flex(12),
p_attribute13 => lt_term_flex(13),
p_attribute14 => lt_term_flex(14),
p_attribute15 => lt_term_flex(15),
p_attribute16 => lt_term_flex(16),
p_attribute17 => lt_term_flex(17),
p_attribute18 => lt_term_flex(18),
p_attribute19 => lt_term_flex(19),
p_attribute20 => lt_term_flex(20),
---- DDF Enhancement : Save DDF Segments data as well.
p_pds_information_category => lv_pds_information_category,
p_pds_information1 => lt_term_flex(21),
p_pds_information2 => lt_term_flex(22),
p_pds_information3 => lt_term_flex(23),
p_pds_information4 => lt_term_flex(24),
p_pds_information5 => lt_term_flex(25),
p_pds_information6 => lt_term_flex(26),
p_pds_information7 => lt_term_flex(27),
p_pds_information8 => lt_term_flex(28),
p_pds_information9 => lt_term_flex(29),
p_pds_information10 => lt_term_flex(30),
p_pds_information11 => lt_term_flex(31),
p_pds_information12 => lt_term_flex(32),
p_pds_information13 => lt_term_flex(33),
p_pds_information14 => lt_term_flex(34),
p_pds_information15 => lt_term_flex(35),
p_pds_information16 => lt_term_flex(36),
p_pds_information17 => lt_term_flex(37),
p_pds_information18 => lt_term_flex(38),
p_pds_information19 => lt_term_flex(39),
p_pds_information20 => lt_term_flex(40),
p_pds_information21 => lt_term_flex(41),
p_pds_information22 => lt_term_flex(42),
p_pds_information23 => lt_term_flex(43),
p_pds_information24 => lt_term_flex(44),
p_pds_information25 => lt_term_flex(45),
p_pds_information26 => lt_term_flex(46),
p_pds_information27 => lt_term_flex(47),
p_pds_information28 => lt_term_flex(48),
p_pds_information29 => lt_term_flex(49),
p_pds_information30 => lt_term_flex(50)
);
update_per_details(
p_validate => 0, -- false
p_effective_date => ld_actual_term_date,
p_period_of_service_id => ln_period_of_service_id,
p_actual_termination_date => ld_actual_term_date,
p_rehire_recommendation => lv_rehire_recommendation,
p_rehire_reason => lv_rehire_reason
);
SELECT ppf.list_name FULL_NAME,
ppf.last_name LAST_NAME,
ppf.first_name FIRST_NAME,
nvl(ppf.order_name, ppf.list_name) ORDER_NAME,
ppf.person_id PERSON_ID,
ppf.business_group_id BUSINESS_GROUP_ID,
ppf.effective_start_date EFFECTIVE_START_DATE,
ppf.effective_end_date EFFECTIVE_END_DATE,
ppf.employee_number EMPLOYEE_NUMBER,
ppf.email_address EMAIL_ADDRESS,
paf.effective_start_date ASN_EFFECTIVE_START_DATE,
paf.effective_end_date ASN_EFFECTIVE_END_DATE,
jbs.name JOB_NAME,
pos.name POSITION_NAME,
pg.name GRADE_NAME,
org.name ORGANIZATION,
loc.location_code LOCATION_NAME,
bus.name BUSINESS_GROUP,
hrl.meaning PRIMARY_FLAG,
paf.assignment_number ASSIGNMENT_NUMBER,
paf.assignment_id,
paf.object_version_number,
ppf.local_name LOCAL_NAME,
ppf.global_name GLOBAL_NAME,
ppf.npw_number
from per_people_f ppf, -- from secured view for assign new report(bug1855111)
per_Assignments_f2 paf,
per_jobs_vl jbs,
hr_all_organization_units_tl org,
hr_all_organization_units_tl bus,
hr_all_positions_f_vl pos,
per_grades_vl pg,
hr_lookups hrl,
hr_locations_all loc
WHERE (paf.assignment_type = 'E' and
ppf.current_employee_flag = 'Y' or
paf.assignment_type = 'C' and
ppf.current_npw_flag = 'Y')
AND ppf.person_id = paf.person_id
AND paf.primary_flag in (cp_param_1 , cp_param_2)
AND paf.business_group_id = bus.organization_id
AND bus.language = userenv('LANG')
AND paf.job_id = jbs.job_id (+)
AND paf.grade_id = pg.grade_id (+)
AND paf.organization_id = org.organization_id (+)
AND paf.location_id = loc.location_id(+)
AND org.language = userenv('LANG')
AND hrl.lookup_type(+) = 'YES_NO'
AND paf.primary_flag = hrl.lookup_code (+)
AND to_date(cp_param_3, 'YYYY-MM-DD') between paf.effective_start_date and paf.effective_end_date
AND to_date(cp_param_4, 'YYYY-MM-DD') between ppf.effective_start_date and ppf.effective_end_date
AND paf.position_id = pos.position_id (+)
AND to_date(cp_param_5, 'YYYY-MM-DD') between pos.effective_start_date(+) and pos.effective_end_date(+)
AND (cp_param_6 = 'Y' or ppf.business_group_id = cp_param_7)
AND NOT EXISTS
-- Replaced NOT IN clause with NOT EXISTS, Bug 2416631
-- TO FILTER ALL DIRECT REPORTS TO THE PERSON
(
select paf2.person_id
from per_assignments_f2 paf2
where paf2.supervisor_id = cp_param_8
and paf2.primary_flag in (cp_param_9 , cp_param_10)
and paf2.assignment_type in ('E','C')
and to_date(cp_param_11, 'YYYY-MM-DD') between paf2.effective_start_date and paf2.effective_end_date
and paf2.person_id = ppf.person_id
and ((hr_general2.supervisor_assignments_in_use <> 'TRUE') or
paf2.supervisor_assignment_id = cp_param_12)
and ((hr_general2.supervisor_assignments_in_use <> 'TRUE') or
paf2.assignment_id = paf.assignment_id)
)
-- TO FILTER THE MANAGER HIMSELF
-- Bug 2212956 Fix
--and ppf.person_id not in (cp_param_13 , cp_param_14, cp_param_15 )
and ppf.person_id = cp_param_13;
SELECT DECODE(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',ppf.global_name,ppf.local_name) full_name,
paf.object_version_number,
paf.assignment_id,
paf.supervisor_id SupervisorLov_Id,
DECODE(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',ppf2.global_name,ppf2.local_name) Supervisor_Name,
Paf.Person_Id,
(to_date( p_term_date, 'YYYY-MM-DD' ) + 1) Effective_Date,
jbs.name JOB_NAME,
pos.name POSITION_NAME,
pg.name GRADE_NAME,
org.name ORGANIZATION,
loc.location_code LOCATION_NAME,
bus.name BUSINESS_GROUP,
bus.organization_id business_group_id,
hrl.meaning PRIMARY_FLAG,
paf.assignment_number ASSIGNMENT_NUMBER,
paf.supervisor_id old_supervisor_id,
NVL(paf.supervisor_assignment_id,-1) SupervisorLov_assignment_id ,
NVL(paf.supervisor_assignment_id,-1) old_supervisor_assignment_id,
paf2.assignment_number SUPERVISOR_ASSIGNMENT_NUMBER,
jbs2.name SUPERVISOR_JOB_NAME,
pos2.name SUPERVISOR_POSITION_NAME,
org2.name SUPERVISOR_ORGANIZATION
FROM per_all_people_f ppf, -- Changed to use base table, see bug 2130066
per_all_people_f ppf2 ,
per_all_assignments_f paf ,
per_all_assignments_f paf2 ,
per_jobs_vl jbs ,
hr_all_positions_f_tl pos ,
per_grades_vl pg ,
hr_all_organization_units_tl org ,
hr_all_organization_units_tl bus ,
hr_lookups hrl ,
hr_locations_all loc ,
per_jobs_vl jbs2 ,
hr_all_positions_f_tl pos2 ,
Hr_All_Organization_Units_Tl Org2
Where Paf.Supervisor_Id = p_old_sup_person_id
AND ((p_flow_identifier = 'TERMINATION_FLOW')
Or ((Hr_General2.Supervisor_Assignments_In_Use = 'TRUE'
Or p_flow_identifier = 'TERM_SEC_ASGN_FLOW')
AND paf.supervisor_assignment_id = p_old_sup_asg_id)
Or ((Hr_General2.Supervisor_Assignments_In_Use <> 'TRUE')
AND p_flow_identifier = 'ASSIGNMENT_FLOW'))
AND paf.person_id = ppf.person_id
AND ((ppf.current_employee_flag = 'Y'
AND paf.assignment_type = 'E')
OR (ppf.current_npw_flag = 'Y'
And Paf.Assignment_Type = 'C'))
AND paf.primary_flag IN ('Y')
AND paf.business_group_id = bus.organization_id
AND bus.language = userenv('LANG')
AND paf.job_id = jbs.job_id (+)
AND paf.position_id = pos.position_id (+)
AND pos.language(+) = userenv('LANG')
AND paf.grade_id = pg.grade_id (+)
AND paf.organization_id = org.organization_id (+)
AND paf.location_id = loc.location_id(+)
AND org.language(+) = userenv('LANG')
AND hrl.lookup_type(+) = 'YES_NO'
And Paf.Primary_Flag = Hrl.Lookup_Code (+)
And to_date(p_term_date, 'YYYY-MM-DD') Between Paf.Effective_Start_Date And Paf.Effective_End_Date
And to_date(p_term_date, 'YYYY-MM-DD') Between Ppf.Effective_Start_Date And Ppf.Effective_End_Date-1
AND to_date(p_term_date, 'YYYY-MM-DD') BETWEEN ppf2.effective_start_date AND ppf2.effective_end_date
AND ppf2.person_id = paf.supervisor_id
AND paf.supervisor_id = paf2.person_id(+)
AND ( ((NVL(fnd_profile.value('HR_SUPERVISOR_HIERARCHY_USAGE'), 'P') <> 'A')
AND (paf.supervisor_id IS NULL
OR ((paf2.primary_flag = 'Y')
AND (paf2.assignment_type IN ('E','C','A'))) ) )
OR ( (NVL(fnd_profile.value('HR_SUPERVISOR_HIERARCHY_USAGE'), 'P') = 'A')
AND (paf.supervisor_id IS NULL
Or Paf.Supervisor_Assignment_Id = Paf2.Assignment_Id) ) )
AND ( to_date(p_term_date, 'YYYY-MM-DD') BETWEEN paf2.effective_start_date(+) AND paf2.effective_end_date(+))
AND paf2.job_id = jbs2.job_id (+)
AND paf2.position_id = pos2.position_id (+)
AND pos2.language(+) = userenv('LANG')
AND Paf2.Organization_Id = Org2.Organization_Id (+)
AND org2.language(+) = userenv('LANG');
SELECT object_version_number,
assignment_type
FROM per_all_assignments_f
WHERE assignment_id = cp_assignment_id
AND (cp_effective_date BETWEEN
NVL ( effective_start_date , cp_effective_date)
AND NVL ( effective_end_date , cp_effective_date )) ;
hr_assignment_att.update_asg(
p_validate=> l_validate ,
p_effective_date => cur_get_direct_reports.effective_date ,
p_attribute_update_mode=> 'ATTRIBUTE_UPDATE' ,
p_assignment_id=> cur_get_direct_reports.assignment_id ,
p_assignment_type => ln_assignment_type,
p_object_version_number => ln_object_version_no ,
p_supervisor_id => p_new_sup_person_id ,
p_supervisor_assignment_id => l_new_sup_asg_id ,
p_comment_id => ln_comment_id ,
p_effective_start_date=> ld_effective_start_date,
p_effective_end_date=> ld_effective_end_date,
p_no_managers_warning=> lb_no_managers_warning,
p_other_manager_warning=> lb_other_manager_warning ) ;