[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_type pay_datetracked_events.update_type%TYPE ,
surrogate_key pay_process_events.surrogate_key%type ,
column_name pay_event_updates.column_name%TYPE ,
effective_date date,
creation_date date,
old_value varchar2(2000),
new_value varchar2(2000),
change_values varchar2(2000),
proration_type varchar2(10),
change_mode pay_process_events.change_type%type,--'DATE_PROCESSED' etc
element_entry_id pay_element_entries_f.element_entry_id%type,
next_ee number ,
assignment_id per_all_Assignments_f.assignment_id%type
);
select pay_no_eerr_status_control.get_parameter (
legislative_parameters,
'LEGAL_EMPLOYER'
),
pay_no_eerr_status_control.get_parameter (
legislative_parameters,
'ARCHIVE'
),
start_date, --14507939
effective_date, business_group_id
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select org.organization_id legal_employer_id,
org.name
legal_employer_name, org.location_id,
hoi1.org_information1
legal_employer_org_no
from hr_all_organization_units org,
hr_organization_information hoi1
where org.organization_id = p_legal_employer_id
and hoi1.organization_id(+) = org.organization_id
and hoi1.org_information_context(+) = 'NO_LEGAL_EMPLOYER_DETAILS';
select hoi_le.org_information1 local_unit_id,
hou_lu.name
local_unit_name,
hoi_lu.org_information1
local_unit_org_no, hou_lu.location_id
from hr_all_organization_units hou_le,
hr_organization_information hoi_le,
hr_all_organization_units hou_lu,
hr_organization_information hoi_lu
where hoi_le.organization_id = hou_le.organization_id
and hou_le.organization_id = csr_v_legal_employer_id
and hoi_le.org_information_context = 'NO_LOCAL_UNITS'
and hou_lu.organization_id = hoi_le.org_information1
and hou_lu.organization_id = hoi_lu.organization_id
and hoi_lu.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
select nvl(fnd_number.canonical_to_number (global_value),0)
from ff_globals_f
where legislation_code = 'NO' and global_name = p_global_name
and p_effective_date between effective_start_date and effective_end_date ;
select hoi_le.org_information1 local_unit_id,
hou_lu.name
local_unit_name,
hoi_lu.org_information1
local_unit_org_no, hou_lu.location_id
from hr_all_organization_units hou_le,
hr_organization_information hoi_le,
hr_all_organization_units hou_lu,
hr_organization_information hoi_lu
where hoi_le.organization_id = hou_le.organization_id
and hou_le.organization_id = csr_v_legal_employer_id
and hoi_le.org_information_context = 'NO_LOCAL_UNITS'
and hou_lu.organization_id = hoi_le.org_information1
and hou_lu.organization_id = hoi_lu.organization_id
and hoi_lu.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
select papf.person_id person_id, paaf.assignment_id,
papf.effective_start_date, null effective_end_date,
national_identifier, full_name, employee_number, normal_hours,
hourly_salaried_code, hsc.segment3 position_code, frequency
from per_all_people_f papf,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsc,
per_assignment_status_types past
where papf.person_id between p_start_person and p_end_person
and paaf.person_id = papf.person_id
and paaf.business_group_id = papf.business_group_id
-- and paaf.primary_flag = 'Y'
and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and hsc.segment2 = to_char (p_local_unit)
and paaf.assignment_status_type_id =
past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paaf.assignment_id = (select min (assignment_id)
from per_all_assignments_f asg,
hr_soft_coding_keyflex hsck
where person_id = papf.person_id
and hsck.soft_coding_keyflex_id =
asg.soft_coding_keyflex_id
and hsck.segment2 =
to_char(p_local_unit))
and p_end_date between paaf.effective_start_date
and paaf.effective_end_date
and p_end_date between papf.effective_start_date
and papf.effective_end_date
and not exists (select actual_termination_date
from per_periods_of_service
where actual_termination_date =
paaf.effective_end_date
and person_id = papf.person_id
and actual_termination_date = nvl(final_process_date,actual_termination_date )
and p_end_date >= actual_termination_date
)
--14507939 Start1
AND NOT EXISTS
(
SELECT 1
FROM per_person_type_usages_f pptuf
WHERE pptuf.person_id = papf.person_id
AND p_end_date BETWEEN pptuf.effective_start_date
AND pptuf.effective_end_date
AND pptuf.person_type_id IN
(
SELECT pptt.person_type_id
FROM pay_user_tables put
,pay_user_tables_tl putt
,pay_user_columns puc
,pay_user_columns_tl puct
,pay_user_rows_f purf
,pay_user_column_instances_f pucif
,per_person_types_tl pptt
WHERE put.business_group_id = papf.business_group_id
AND put.user_table_id = putt.user_table_id
AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
AND puc.user_column_id = puct.user_column_id
AND puc.business_group_id = papf.business_group_id
AND lower (trim (puct.user_column_name)) = lower (trim ('Person Type'))
AND puc.user_table_id = put.user_table_id
AND purf.user_table_id = put.user_table_id
AND purf.business_group_id = papf.business_group_id
AND p_end_date BETWEEN purf.effective_start_date
AND purf.effective_end_date
AND pucif.user_row_id = purf.user_row_id
AND pucif.user_column_id = puc.user_column_id
AND pucif.business_group_id = papf.business_group_id
AND p_end_date BETWEEN pucif.effective_start_date
AND pucif.effective_end_date
AND lower (trim (pptt.user_person_type)) = lower (trim (pucif.value))
)
)
--14507939 End 1
union
select papf.person_id person_id, paaf.assignment_id,
papf.effective_start_date, paaf.effective_end_date,
national_identifier, full_name, employee_number, normal_hours,
hourly_salaried_code, hsc.segment3 position_code, frequency
from per_all_people_f papf,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsc,
per_assignment_status_types past
where paaf.person_id = papf.person_id
and papf.person_id between p_start_person and p_end_person
and paaf.business_group_id = papf.business_group_id
--and paaf.primary_flag = 'Y'
and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and hsc.segment2 = to_char (p_local_unit)
and paaf.assignment_status_type_id =
past.assignment_status_type_id
and paaf.assignment_id = (select min (assignment_id)
from per_all_assignments_f asg,
hr_soft_coding_keyflex hsck
where person_id = papf.person_id
and hsck.soft_coding_keyflex_id =
asg.soft_coding_keyflex_id
and hsck.segment2 =
to_char (p_local_unit))
--and past.PER_SYSTEM_STATUS = 'TERM_ASSIGN'
and ( ( papf.effective_end_date between p_start_date
and p_end_date
--and paaf.effective_end_date between p_start_date and p_end_date
and exists (select actual_termination_date
from per_periods_of_service
where actual_termination_date =
paaf.effective_end_date
and person_id = papf.person_id
and actual_termination_date = nvl(final_process_date,actual_termination_date ))
)
or ( paaf.effective_start_date between p_start_date
and p_end_date
and past.per_system_status = 'TERM_ASSIGN'
and papf.effective_end_date between p_start_date
and p_end_date
)
)
--14507939 Start2
AND NOT EXISTS
(
SELECT 1
FROM per_person_type_usages_f pptuf
WHERE pptuf.person_id = papf.person_id
AND p_end_date BETWEEN pptuf.effective_start_date
AND pptuf.effective_end_date
AND pptuf.person_type_id IN
(
SELECT pptt.person_type_id
FROM pay_user_tables put
,pay_user_tables_tl putt
,pay_user_columns puc
,pay_user_columns_tl puct
,pay_user_rows_f purf
,pay_user_column_instances_f pucif
,per_person_types_tl pptt
WHERE put.business_group_id = papf.business_group_id
AND put.user_table_id = putt.user_table_id
AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
AND puc.user_column_id = puct.user_column_id
AND puc.business_group_id = papf.business_group_id
AND lower (trim (puct.user_column_name)) = lower (trim ('Person Type'))
AND puc.user_table_id = put.user_table_id
AND purf.user_table_id = put.user_table_id
AND purf.business_group_id = papf.business_group_id
AND p_end_date BETWEEN purf.effective_start_date
AND purf.effective_end_date
AND pucif.user_row_id = purf.user_row_id
AND pucif.user_column_id = puc.user_column_id
AND pucif.business_group_id = papf.business_group_id
AND p_end_date BETWEEN pucif.effective_start_date
AND pucif.effective_end_date
AND lower (trim (pptt.user_person_type)) = lower (trim (pucif.value))
)
)
--14507939 End 2
;
select min (effective_start_date)
from per_all_assignments_f paaf, per_assignment_status_types past
where assignment_id = p_assignment_id
and paaf.assignment_status_type_id =
past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
select paa.date_start, paa.date_end
from per_absence_attendances paa, per_absence_attendance_types paat
where paat.absence_attendance_type_id =
paa.absence_attendance_type_id
and paa.person_id = p_person_id
and paa.date_end - paa.date_start > g_absence_termination_limit
and paa.date_start between g_effective_start_date
and g_effective_date
and paat.absence_category not in
('S', 'PTM', 'PTS', 'PTP', 'PTA', 'VAC', 'MRE', 'CMS');*/
select paa.date_start, paa.date_end
from per_absence_attendances paa, per_absence_attendance_types paat
where paat.absence_attendance_type_id =
paa.absence_attendance_type_id
and paa.person_id = p_person_id
and paa.date_start between g_effective_start_date and g_effective_date
/* and paat.absence_category not in
('S', 'PTM', 'PTS', 'PTP', 'PTA', 'VAC', 'MRE'); -- 5520062 */
SELECT paatt.absence_attendance_type_id
FROM pay_user_tables put
,pay_user_tables_tl putt
,pay_user_columns puc
,pay_user_columns_tl puct
,pay_user_rows_f purf
,pay_user_column_instances_f pucif
,per_absence_attendance_types paat
,per_abs_attendance_types_tl paatt
WHERE put.business_group_id = paa.business_group_id
AND put.user_table_id = putt.user_table_id
AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
AND puc.user_column_id = puct.user_column_id
AND puc.business_group_id = paa.business_group_id
AND lower (trim (puct.user_column_name)) = lower (trim ('Absence Type'))
AND puc.user_table_id = put.user_table_id
AND purf.user_table_id = put.user_table_id
AND purf.business_group_id = paa.business_group_id
AND g_effective_date BETWEEN purf.effective_start_date
AND purf.effective_end_date
AND pucif.user_row_id = purf.user_row_id
AND pucif.user_column_id = puc.user_column_id
AND pucif.business_group_id = paa.business_group_id
AND g_effective_date BETWEEN pucif.effective_start_date
AND pucif.effective_end_date
AND paat.business_group_id = paa.business_group_id
AND paatt.absence_attendance_type_id = paat.absence_attendance_type_id
AND lower (trim (paatt.name)) = lower (trim (pucif.value))
);
select paa.date_start, paa.date_end
from per_absence_attendances paa, per_absence_attendance_types paat
where paat.absence_attendance_type_id =
paa.absence_attendance_type_id
and paa.person_id = p_person_id
and paa.date_end between p_prev_last_date and g_effective_date
/* and paa.date_end - paa.date_start >= g_absence_termination_limit
and paat.absence_category not in
('S', 'PTM', 'PTS', 'PTP', 'PTA', 'VAC', 'MRE');
SELECT paatt.absence_attendance_type_id
FROM pay_user_tables put
,pay_user_tables_tl putt
,pay_user_columns puc
,pay_user_columns_tl puct
,pay_user_rows_f purf
,pay_user_column_instances_f pucif
,per_absence_attendance_types paat
,per_abs_attendance_types_tl paatt
WHERE put.business_group_id = paa.business_group_id
AND put.user_table_id = putt.user_table_id
AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
AND puc.user_column_id = puct.user_column_id
AND puc.business_group_id = paa.business_group_id
AND lower (trim (puct.user_column_name)) = lower (trim ('Absence Type'))
AND puc.user_table_id = put.user_table_id
AND purf.user_table_id = put.user_table_id
AND purf.business_group_id = paa.business_group_id
AND g_effective_date BETWEEN purf.effective_start_date
AND purf.effective_end_date
AND pucif.user_row_id = purf.user_row_id
AND pucif.user_column_id = puc.user_column_id
AND pucif.business_group_id = paa.business_group_id
AND g_effective_date BETWEEN pucif.effective_start_date
AND pucif.effective_end_date
AND paat.business_group_id = paa.business_group_id
AND paatt.absence_attendance_type_id = paat.absence_attendance_type_id
AND lower (trim (paatt.name)) = lower (trim (pucif.value))
);
select event_group_id
from pay_event_groups
where event_group_name = p_event_group_name
and nvl (business_group_id, p_business_group_id) =
p_business_group_id;
select org_information1
from hr_organization_information hoi, hr_soft_coding_keyflex hsc
where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
and hsc.segment2 = organization_id
and soft_coding_keyflex_id = p_soft_coding_keyflex_id;
select segment3
from hr_soft_coding_keyflex hsc, per_all_assignments_f paaf
where paaf.job_id = p_job_id
and assignment_id = p_assignment_id
and p_effective_date between paaf.effective_start_date
and paaf.effective_end_date
and paaf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id;
select per_system_status
from per_assignment_status_types
where assignment_status_type_id = p_assignment_status_type_id;
select peef.element_entry_id
from pay_element_entries_f peef, pay_element_types_f pet
where pet.element_name = c_element_name
and pet.legislation_code = 'NO'
and peef.assignment_id = c_assignment_id
and peef.element_type_id = pet.element_type_id
and c_eff_date between peef.effective_start_date
and peef.effective_end_date
and c_eff_date between pet.effective_start_date
and pet.effective_end_date;
select nvl (segment2, '0')
from hr_soft_coding_keyflex
where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
select nvl (segment3, 0)
from hr_soft_coding_keyflex
where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
select peef.element_entry_id
from pay_element_entries_f peef, pay_element_types_f pet
where pet.element_name = c_element_name
and pet.legislation_code = 'NO'
and peef.assignment_id = c_assignment_id
and peef.element_type_id = pet.element_type_id
and c_eff_date between peef.effective_start_date
and peef.effective_end_date
and c_eff_date between pet.effective_start_date
and pet.effective_end_date;
select peef.element_entry_id
from pay_element_entries_f peef, pay_element_types_f pet
where pet.element_name = p_element_name
and pet.legislation_code = 'NO'
and peef.assignment_id = p_assignment_id
and peef.element_type_id = pet.element_type_id
and peef.effective_start_date between p_start_date
and p_end_date ;
select fnd_date.canonical_to_date (peev.screen_entry_value)
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_entry_values_f peev
where pet.element_name = c_element_name
and pet.element_type_id = piv.element_type_id
and piv.name = c_input_val_name
and pet.legislation_code = 'NO'
and piv.legislation_code = 'NO'
and peef.assignment_id = c_assignment_id
and peef.element_entry_id = peev.element_entry_id
and peef.element_type_id = pet.element_type_id
and peev.input_value_id = piv.input_value_id
and c_eff_date between piv.effective_start_date
and piv.effective_end_date
and c_eff_date between pet.effective_start_date
and pet.effective_end_date
and c_eff_date between peev.effective_start_date
and peev.effective_end_date
and c_eff_date between peef.effective_start_date
and peef.effective_end_date;
select dated_table_id
from pay_dated_tables
where table_name = c_table_name;
select effective_start_date,
fnd_number.canonical_to_number (screen_entry_value) entry_value
from pay_element_entry_values_f peev
where element_entry_id = c_element_entry_id
and effective_start_date between c_eff_start_date and c_eff_end_date
and screen_entry_value is not null
and effective_start_date =
(select max (effective_start_date)
from pay_element_entry_values_f peevf
where element_entry_id = c_element_entry_id
and effective_start_date between c_eff_start_date
and c_eff_end_date
-- and peevf.effective_start_date = peev.effective_start_date
and to_char (peev.effective_start_date, 'MM') =
to_char (peevf.effective_start_date, 'MM'));
select fnd_number.canonical_to_number (screen_entry_value) entry_value
from pay_element_entry_values_f
where element_entry_id = c_element_entry_id
and c_effective_date between effective_start_date
and effective_end_date
and screen_entry_value is not null;
select assignment_id
from per_all_assignments_f paaf ,hr_soft_coding_keyflex hsck
where person_id = p_person_id
and assignment_id <> p_assignment_id
and hsck.segment2 = to_char (p_local_unit)
and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
select normal_hours,
hourly_salaried_code, hsc.segment3 position_code, frequency
from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
where paaf.assignment_id = p_assignment_id
and hsc.segment2 = to_char (p_local_unit)
and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and p_effective_date between paaf.effective_start_date and paaf.effective_End_date;
select normal_hours , effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date < p_effective_date
order by effective_start_date desc ;
l_last_update_date date;
p_copy_to (p_to).update_type := p_copy_from (p_from).update_type;
l_detail_tab.delete;
l_detail_tab.delete;
l_detailed_output1.delete;
l_detailed_output1.delete;
l_detailed_output(merge_cnt).update_type := l_detailed_output1(i).update_type ;
l_detailed_output3.delete;
l_detailed_output3.delete;
if l_detailed_output3(i).column_name = 'NORMAL_HOURS' OR l_detailed_output3(i).column_name = 'ASSIGNMENT_STATUS_TYPE_ID' OR (l_detailed_output3(i).dated_table_id = l_table3 and l_detailed_output3(i).update_type = 'I' )then
l_detailed_output(merge_cnt).effective_date := l_detailed_output3(i).effective_date;
l_detailed_output(merge_cnt).update_type := l_detailed_output3(i).update_type ;
if l_detailed_output(cnt).dated_table_id = l_table3 and l_detailed_output (cnt).update_type = 'I' then
rl_assignment_details.normal_hours := 0;
select pay_assignment_actions_s.nextval
into l_assact_id
from dual;
p_copy_to (p_to).update_type := p_copy_from (p_from).update_type;
select action_information1, action_information2 business_group_id,
action_information3
legal_employer_id,
action_information4
legal_employer_name,
action_information5
legal_employer_org_no,
action_information6 local_unit_id,
action_information7
local_unit_name,
action_information8 local_unit_org_no, effective_date
from pay_action_information pai
where action_context_type = 'PA'
and action_context_id = p_payroll_action_id
and action_information_category = 'EMEA REPORT INFORMATION'
and action_information1 = 'PYNOEERSTC';
select action_information2, action_information3, action_information4,
action_information5, action_information6, action_information7,
action_information8, action_information9, action_information10,
fnd_date.canonical_to_date (action_information11)
action_information11,
action_information12,
fnd_date.canonical_to_date (action_information13)
action_information13,
fnd_date.canonical_to_date (action_information14)
action_information14,
action_information15,
fnd_date.canonical_to_date (action_information16)
action_information16,
action_information17,
fnd_date.canonical_to_date (action_information18)
action_information18,
fnd_date.canonical_to_date (action_information19)
action_information19,
fnd_date.canonical_to_date (action_information20)
action_information20
from pay_payroll_actions paa,
pay_assignment_actions assg,
pay_action_information pai
where paa.payroll_action_id = p_payroll_action_id
and assg.payroll_action_id = paa.payroll_action_id
and pai.action_context_id = assg.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'EMEA REPORT INFORMATION'
and pai.action_information1 = 'PYNOEERSTC'
and pai.action_information3 = p_legal_employer
and pai.action_information5 = p_local_unit_id;
select count (*)
from pay_payroll_actions paa,
pay_assignment_actions assg,
pay_action_information pai
where paa.payroll_action_id = p_payroll_action_id
and assg.payroll_action_id = paa.payroll_action_id
and pai.action_context_id = assg.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'EMEA REPORT INFORMATION'
and pai.action_information1 = 'PYNOEERSTC'
and pai.action_information3 = p_legal_employer
and pai.action_information5 = p_local_unit_id;
select payroll_action_id
into l_payroll_action_id
from pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
where fcrs.request_id = fnd_global.conc_request_id
and fcrs.priority_request_id = fcrs1.priority_request_id
and ppa.request_id between fcrs1.request_id and fcrs.request_id
and ppa.request_id = fcrs1.request_id;
select frequency
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date
and effective_end_date;
select normal_hours, frequency
from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
where paaf.person_id = p_person_id
and paaf.assignment_id <> p_assignment_id
and paaf.normal_hours is not null
and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and hsc.segment2 = to_char (p_local_unit)
and hourly_salaried_code = 'S'
and p_effective_date between paaf.effective_start_date
and paaf.effective_end_date;