[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pay_fi_archive_psta.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER_ID'),
pay_fi_archive_psta.get_parameter (legislative_parameters, 'ARCHIVE'),
pay_fi_archive_psta.get_parameter (legislative_parameters, 'LOCAL_UNIT_ID'),
pay_fi_archive_psta.get_parameter (legislative_parameters, 'YEAR_RPT'),
pay_fi_archive_psta.get_parameter (legislative_parameters, 'PAYROLL_TYPE'),
pay_fi_archive_psta.get_parameter (legislative_parameters, 'PAYROLL_ID'), effective_date, business_group_id
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select o1.name legal_employer_name, hoi2.org_information1 legal_emp_y_num, hoi2.org_information11
from hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
where o1.business_group_id = g_business_group_id
and hoi1.organization_id = o1.organization_id
and hoi1.organization_id = csr_v_legal_employer_id
and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
and hoi1.org_information_context = 'CLASS'
and o1.organization_id = hoi2.organization_id
and hoi2.org_information_context = 'FI_LEGAL_EMPLOYER_DETAILS';
select o1.name local_unit_name, hoi2.org_information1 local_unit_sd_no, hoi2.org_information7
from hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
where o1.business_group_id = g_business_group_id
and hoi1.organization_id = o1.organization_id
and hoi1.organization_id = csr_v_local_unit_id
and hoi1.org_information1 = 'FI_LOCAL_UNIT'
and hoi1.org_information_context = 'CLASS'
and o1.organization_id = hoi2.organization_id
and hoi2.org_information_context = 'FI_LOCAL_UNIT_DETAILS';
select hoi_le.org_information1 local_unit_id, hou_lu.name local_unit_name, hoi_lu.org_information1 local_unit_sd_no,
hoi_lu.org_information7
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 = 'FI_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 = 'FI_LOCAL_UNIT_DETAILS';
select style, address_line_1, address_line_2, address_line_3, country, postal_code
from hr_organization_units hou, hr_locations hl
where hou.organization_id = p_organization_id and hou.location_id = hl.location_id;
'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 fnd_date.canonical_to_date (g_year || '12/31')
into g_year_last_date
from dual;
select payroll_name
into l_payroll_name
from pay_payrolls_f
where payroll_id = g_payroll_id and g_year_last_date between effective_start_date and effective_end_date;
select paaf.person_id, paaf.primary_flag, act.assignment_id assignment_id, act.assignment_action_id run_action_id,
act1.assignment_action_id
prepaid_action_id, appa.effective_date, appa.payroll_action_id,
appa2.payroll_action_id payactid, hsck.segment2 local_unit_id
from pay_payroll_actions appa,
pay_payroll_actions appa2,
pay_assignment_actions act,
pay_assignment_actions act1,
pay_action_interlocks pai,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck,
hr_organization_information hoi,
pay_payrolls_f ppa
where appa.action_type in ('R', 'Q')
and act.payroll_action_id = appa.payroll_action_id
and act.source_action_id is null -- Master Action
and act.action_status IN ('C','S') -- 10229501
and act.assignment_action_id = pai.locked_action_id
and act1.assignment_action_id = pai.locking_action_id
and act1.action_status IN ('C','S') -- 10229501
and act1.payroll_action_id = appa2.payroll_action_id
and appa2.action_type in ('P', 'U')
and paaf.assignment_id = act.assignment_id
-- and paaf.assignment_id = p_assignemtn_id
and appa.effective_date between paaf.effective_start_date and paaf.effective_end_date
and appa.effective_date between p_start_date and p_end_date
and paaf.primary_flag = 'Y'
and paaf.person_id between p_start_person and p_end_person
and ppa.payroll_id = paaf.payroll_id
and ppa.payroll_id = nvl (g_payroll_id, ppa.payroll_id)
and ppa.period_type = g_payroll_type
and g_year_last_date between ppa.effective_start_date and ppa.effective_end_date
and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and hsck.segment2 = nvl (to_char (p_local_unit_id), hsck.segment2)
and hoi.organization_id = p_legal_employer_id
and hoi.org_information_context = 'FI_LOCAL_UNITS'
and hoi.org_information1 = hsck.segment2
order by person_id, assignment_id, payroll_action_id, prepaid_action_id;
select papf.payroll_id, papf.payroll_name, ptp.start_date, ptp.end_date, ptp.period_name, ptp.period_type,
ptp.regular_payment_date, ptp.time_period_id
from pay_payroll_actions ppa, per_time_periods ptp, pay_all_payrolls_f papf
where ptp.time_period_id = ppa.time_period_id
and ppa.payroll_id = papf.payroll_id
and ppa.payroll_action_id = csr_v_payroll_action_id;
select pbt.balance_name
from pay_balance_types pbt, pay_balance_categories_f pbc
where pbc.legislation_code = 'FI'
and pbt.balance_category_id = pbc.balance_category_id
and pbt.business_group_id = g_business_group_id
and pbc.category_name = p_balance_category_name;
select iv1.name, eev1.effective_start_date, eev1.screen_entry_value screen_entry_value, iv1.lookup_type, uom
from per_all_assignments_f asg1,
per_all_assignments_f asg2,
per_all_people_f per,
pay_element_links_f el,
pay_element_types_f et,
pay_input_values_f iv1,
pay_element_entries_f ee,
pay_element_entry_values_f eev1
where asg1.assignment_id = p_assignment_id
and p_value_date between asg1.effective_start_date and asg1.effective_end_date
and p_value_date between asg2.effective_start_date and asg2.effective_end_date
and p_value_date between per.effective_start_date and per.effective_end_date
and per.person_id = asg1.person_id
and asg2.person_id = per.person_id
and asg2.primary_flag = 'Y'
and et.element_name = 'Car Benefit'
and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
and iv1.element_type_id = et.element_type_id
and iv1.name = nvl (p_input_name, iv1.name)
and el.business_group_id = per.business_group_id
and el.element_type_id = et.element_type_id
and ee.assignment_id = asg2.assignment_id
and ee.element_link_id = el.element_link_id
and eev1.element_entry_id = ee.element_entry_id
and eev1.input_value_id = iv1.input_value_id
and eev1.screen_entry_value is not null
and p_value_date between ee.effective_start_date and ee.effective_end_date
and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
select pay_assignment_actions_s.nextval
into l_actid
from dual;
select pay_assignment_actions_s.nextval
into l_actid
from dual;
select fnd_date.canonical_to_date (g_year || '12/31')
into g_year_last_date
from dual;
select pay_assignment_actions_s.nextval
into l_actid
from dual;
select paaf.effective_start_date - 1 term_dt
from per_all_assignments_f paaf, pay_assignment_actions paa
where paaf.assignment_id = paa.assignment_id
and paa.assignment_action_id = p_arch_assignment_action_id
and assignment_status_type_id = (select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'TERM_ASSIGN'
and active_flag = 'Y'
and legislation_code is null
and business_group_id is null)
and effective_end_date between g_year_start_date and g_year_last_date;*/
select max (effective_end_date) effective_date
from per_all_assignments_f paa
where assignment_id = p_asg_id
and paa.effective_start_date <= p_end_date
and paa.effective_end_date > = p_start_date
and assignment_status_type_id in (select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN'
and active_flag = 'Y'
and ( (legislation_code is null and business_group_id is null)
or (business_group_id = p_business_group_id)
));
select papf.person_id person_id, paaf.assignment_id, national_identifier, full_name, employee_number,
hourly_salaried_code, paaf.primary_flag, papf.date_of_birth, paaf.job_id, position_id
from per_all_people_f papf, per_all_assignments_f paaf, pay_assignment_actions pac
where pac.assignment_action_id = p_arch_assignment_action_id
and paaf.assignment_id = pac.assignment_id
and paaf.person_id = papf.person_id
and p_effective_date between paaf.effective_start_date and paaf.effective_end_date
and p_effective_date between papf.effective_start_date and papf.effective_end_date;
select eev1.screen_entry_value screen_entry_value
from per_all_assignments_f asg1,
per_all_assignments_f asg2,
per_all_people_f per,
pay_element_links_f el,
pay_element_types_f et,
pay_input_values_f iv1,
pay_element_entries_f ee,
pay_element_entry_values_f eev1
where asg1.assignment_id = p_assignment_id
and p_value_date between asg1.effective_start_date and asg1.effective_end_date
and p_value_date between asg2.effective_start_date and asg2.effective_end_date
and p_value_date between per.effective_start_date and per.effective_end_date
and per.person_id = asg1.person_id
and asg2.person_id = per.person_id
and asg2.primary_flag = 'Y'
and et.element_name = p_element_name --'Tax Card'
and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
and iv1.element_type_id = et.element_type_id
and iv1.name = p_input_value
and el.business_group_id = per.business_group_id
and el.element_type_id = et.element_type_id
and ee.assignment_id = asg2.assignment_id
and ee.element_link_id = el.element_link_id
and eev1.element_entry_id = ee.element_entry_id
and eev1.input_value_id = iv1.input_value_id
and p_value_date between ee.effective_start_date and ee.effective_end_date
and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
select iv1.name, eev1.effective_start_date, eev1.screen_entry_value screen_entry_value, iv1.lookup_type, uom
from per_all_assignments_f asg1,
per_all_assignments_f asg2,
per_all_people_f per,
pay_element_links_f el,
pay_element_types_f et,
pay_input_values_f iv1,
pay_element_entries_f ee,
pay_element_entry_values_f eev1
where asg1.assignment_id = p_assignment_id
and p_value_date between asg1.effective_start_date and asg1.effective_end_date
and p_value_date between asg2.effective_start_date and asg2.effective_end_date
and p_value_date between per.effective_start_date and per.effective_end_date
and per.person_id = asg1.person_id
and asg2.person_id = per.person_id
and asg2.primary_flag = 'Y'
and et.element_name = 'Car Benefit'
and (et.legislation_code = 'FI' or et.business_group_id = g_business_group_id)
and iv1.element_type_id = et.element_type_id
--and iv1.name = p_input_value
and el.business_group_id = per.business_group_id
and el.element_type_id = et.element_type_id
and ee.assignment_id = asg2.assignment_id
and ee.element_link_id = el.element_link_id
and eev1.element_entry_id = ee.element_entry_id
and eev1.input_value_id = iv1.input_value_id
and eev1.screen_entry_value is not null
and p_value_date between ee.effective_start_date and ee.effective_end_date
and p_value_date between eev1.effective_start_date and eev1.effective_end_date;
select pat.element_type_id, pat.element_name, pat.element_information1 benefit_type_code
from pay_element_classifications pec, pay_element_types_f pat, pay_element_entries_f pet
where pec.classification_name = 'Benefits in Kind'
and pec.legislation_code = 'FI'
and (pat.legislation_code = 'FI' or pat.business_group_id = g_business_group_id)
and pec.classification_id = pat.classification_id
and pat.element_type_id = pet.element_type_id
and pet.assignment_id = p_assignment_id
and g_pay_period_end_date between pat.effective_start_date and pat.effective_end_date
and g_pay_period_end_date between pet.effective_start_date and pet.effective_end_date
order by pat.element_type_id;
select 'Y'
from pay_action_information pai, pay_assignment_actions paa
where pai.action_context_id = paa.assignment_action_id
and paa.payroll_action_id = g_arch_payroll_action_id
and action_information1 = 'PYFIPSTA'
and action_information2 = 'PERSON DETAILS'
and action_information3 = to_char (p_person_id);
select 'Y'
from pay_action_information pai, pay_assignment_actions paa
where pai.action_context_id = paa.assignment_action_id
and paa.payroll_action_id = g_arch_payroll_action_id
and action_information1 = 'PYFIPSTA'
and action_information2 = 'Payroll Details'
and action_information3 = to_char (p_person_id)
and action_information4 = to_char (g_payroll_id)
and action_information5 = to_char (g_pay_period);
select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
pa.region_2
r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
pa.address_line3
al3, pa.postal_code postal_code
from per_addresses pa
where pa.primary_flag = 'Y'
and pa.person_id = p_person_id
and p_effective_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
pa.region_2
r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
pa.address_line3
al3, pa.postal_code postal_code
from per_addresses pa
where pa.address_type = 'FI_PR'
and pa.person_id = p_person_id
and p_effective_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
select pbt.balance_name
from pay_balance_types pbt, pay_balance_categories_f pbc
where pbc.legislation_code = 'FI'
and pbt.balance_category_id = pbc.balance_category_id
and pbt.business_group_id = g_business_group_id
and pbc.category_name = p_balance_category_name;
select to_number (nvl (global_value, 0))
from ff_globals_f
where legislation_code = 'FI' and global_name = p_global_name;
select name
from per_jobs
where job_id = p_job_id and p_effective_date between date_from and nvl (date_to, p_effective_date);
select name
from hr_positions_f
where position_id = p_position_id
and p_effective_date between effective_start_date and nvl (effective_end_date, p_effective_date);
select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
pa.region_2
r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
pa.address_line3
al3, pa.postal_code postal_code
from per_addresses pa
where pa.primary_flag = 'Y'
and pa.person_id = p_person_id
and g_year_last_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
select pa.person_id person_id, pa.style style, pa.address_type ad_type, pa.country country, pa.region_1 r1,
pa.region_2
r2, pa.region_3 r3, pa.town_or_city city, pa.address_line1 al1, pa.address_line2 al2,
pa.address_line3
al3, pa.postal_code postal_code
from per_addresses pa
where pa.address_type = 'FI_PR'
and pa.person_id = p_person_id
and g_year_last_date between pa.date_from and nvl (pa.date_to, to_date ('31-12-4712', 'DD-MM-YYYY'));
select territory_short_name
from fnd_territories_vl
where territory_code = p_territory_code;
select defined_balance_id
from pay_balance_types pbt, pay_balance_dimensions pbd, pay_defined_balances pdb
where pbt.balance_name = csr_v_balance_name
and nvl(pbt.business_group_id,g_business_group_id) = g_business_group_id
and pbt.balance_type_id = pdb.balance_type_id
and pbd.database_item_suffix = p_database_item_suffix --'_PER_YTD'
and pbd.legislation_code = 'FI'
and pbd.balance_dimension_id = pdb.balance_dimension_id;