[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select unique(paa1.assignment_id) assignment_id
from per_all_assignments_f paa
,per_all_assignments_f paa1
where paa.assignment_id=p_assignment_id
and paa.person_id=paa1.person_id
--12576775 start
and paa1.payroll_id IS NOT NULL
and paa1.effective_start_date < p_effective_date
and paa1.effective_end_date >= p_previous_year_start_date;
select payroll_id
from per_all_assignments_f
where assignment_id=p_assignment_id
and p_effective_date between effective_start_date
and effective_end_date;
select min(paa.effective_start_date)
,max(paa.effective_end_date)
from per_all_assignments_f paa
,per_assignment_status_types pas
where assignment_id=p_assignment_id
and paa.assignment_status_type_id=pas.assignment_status_type_id
and pas.PER_SYSTEM_STATUS='ACTIVE_ASSIGN'
and paa.PAYROLL_ID IS NOT NULL; --12576775
select START_DATE
,END_DATE
,PERIOD_NUM
from
per_time_periods
where payroll_id=l_payroll_id
and period_name like '%'||l_prev_year||'%';
select START_DATE
,END_DATE
from
per_time_periods
where payroll_id=l_payroll_id
and p_effective_date between START_DATE and END_DATE;
select ptp.start_date
,ptp.end_date
from pay_payroll_actions ppa
,per_time_periods ptp
where ppa.payroll_action_id = c_pay_act_id
AND ptp.time_period_id = ppa.time_period_id;
select paa.assignment_action_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
where paa.assignment_id = c_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status in ('C','P')
AND ppa.date_earned BETWEEN c_start_date AND c_end_date
AND paa.source_action_id IS NOT NULL
ORDER BY 1 desc;
SELECT pay_date_offset
FROM pay_payrolls_f
WHERE payroll_id = l_payroll_id;
SELECT pp.final_process_date
FROM per_periods_of_service pp,per_all_assignments_f assg
WHERE pp.person_id = assg.person_id
AND assg.assignment_id = c_assignment_id
AND pp.final_process_date > c_asg_end_dt
ORDER BY final_process_date DESC;
l_datetrack_update_mode varchar2(2000);
select asg.assignment_id
,asg.person_id
,asg.object_version_number
,asg.assignment_number
,asg.effective_start_date
,asg.effective_end_date
, scl.segment12 last_year_sal
-- , ast.user_status --12608988 Removed for performance optimization
from per_all_assignments_f asg
,hr_soft_coding_keyflex scl
,per_assignment_status_types ast
where organization_id in
(select pose.organization_id_child
from per_org_structure_elements pose,per_org_structure_versions posv
where
posv.org_structure_version_id = pose.org_structure_version_id
and posv.organization_structure_id=p_org_struct_id
and posv.business_group_id = pose.business_group_id
and posv.business_group_id=p_business_group_id
UNION ALL
select p_business_group_id FROM DUAL)
and nvl(p_hr_org_id,organization_id)=organization_id
--and fnd_date.canonical_to_date(p_date) between effective_start_date and effective_end_date --12322620
--12322620
AND asg.effective_end_date >= fnd_date.canonical_to_date(p_date)
--13604353
/*AND EXISTS (SELECT 1 from per_all_assignments_f paaf
where paaf.assignment_id = asg.assignment_id
and fnd_date.canonical_to_date(p_date) -1 BETWEEN paaf.effective_start_date and paaf.effective_end_date
)*/
AND EXISTS (SELECT 1 from per_periods_of_service ppos
where ppos.person_id = asg.person_id
AND ppos.DATE_START < fnd_date.canonical_to_date (p_date)
AND (ppos.ACTUAL_TERMINATION_DATE IS NULL --Not Terminated
OR nvl(ppos.FINAL_PROCESS_DATE,ppos.ACTUAL_TERMINATION_DATE)
>= fnd_date.canonical_to_date(p_date)) --Terminated in future
)
--13604353
--12322620
and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment12 is not null
and ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.business_group_id = p_business_group_id
--and ast.PER_SYSTEM_STATUS='ACTIVE_ASSIGN'; --12322620 QA Issue
SELECT max (asg.effective_end_date) max_end_date
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_asg_id;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
hr_nl_assignment_api.update_nl_emp_asg
(p_validate => FALSE
--,p_effective_date => fnd_date.canonical_to_date(p_date) --12322620
,p_effective_date => l_asg_st_date --12322620
,p_person_id => l_asg_details.person_id
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => l_asg_details.assignment_id
,p_object_version_number => l_object_version_number
,p_assignment_number => l_asg_details.assignment_number
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_last_year_salary => NULL
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_gsp_post_process_warning => l_gsp_post_process_warning
);
Procedure update_assignments (p_assignment_id IN NUMBER
,p_person_id IN NUMBER
,p_effective_date IN DATE
,p_override_value IN NUMBER
,p_dt_update_mode IN VARCHAR2) IS
CURSOR get_all_assignments (p_person_id NUMBER
,p_effective_date DATE) IS
select paa.assignment_id
,paa.effective_start_date
,paa.effective_end_date
,paa.object_version_number
,hsck.segment12
from per_all_assignments_f paa
,hr_soft_coding_keyflex hsck
,per_assignment_status_types pas
where person_id=p_person_id
and paa.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id (+)
and pas.assignment_status_type_id=paa.assignment_status_type_id
and pas.PER_SYSTEM_STATUS='ACTIVE_ASSIGN'
and pas.business_group_id IS NULL and pas.legislation_code IS NULL
and p_effective_date between effective_start_date and effective_end_date;
l_datetrack_update_mode varchar2(2000);
hr_utility.set_location('Inside update_assignments: p_person_id'||p_person_id,900);
hr_utility.set_location('Inside update_assignments: p_effective_date'||p_effective_date,900);
hr_utility.set_location('Inside update_assignments: p_override_value'||p_override_value,900);
hr_utility.set_location('Inside update_assignments: l_object_version_number'||l_object_version_number,1000);
hr_utility.set_location('Inside update_assignments: p_override_value'||p_override_value,1000);
/* l_datetrack_update_mode:='UPDATE';
l_datetrack_update_mode:='CORRECTION';
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
hr_utility.set_location('Inside l_datetrack_update_mode'||l_datetrack_update_mode,1200);
hr_utility.set_location('Inside update_assignments: p_override_value'||fnd_number.number_to_canonical(p_override_value),1200);
hr_nl_assignment_api.update_nl_emp_asg
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_datetrack_update_mode => p_dt_update_mode
,p_assignment_id => l_assignment.assignment_id
,p_object_version_number => l_assignment.object_version_number
,p_last_year_salary => fnd_number.number_to_canonical(p_override_value)
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_gsp_post_process_warning => l_gsp_post_process_warning);
hr_utility.set_location('In update_assignments SQLERRM'||SQLERRM||'SQLCODE'||SQLCODE,2000);
End update_assignments;