[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select paa.assignment_action_id, ppa.effective_date
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_run_results prr
where ppa.business_group_id = p_business_group_id
and ppa.action_type in ('Q','R')
and ppa.action_status in ('C','I')
and ppa.effective_date between trunc(p_date_earned,'MONTH')
and last_day(p_date_earned)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and paa.tax_unit_id = p_tax_unit_id
and paa.assignment_action_id = prr.assignment_action_id
and prr.source_id = p_orig_entry_id
and prr.source_type = 'E';
select prrv.result_value
from pay_run_results prr
, pay_element_types_f pet
, pay_input_values_f piv
, pay_run_result_values prrv
where prr.assignment_action_id = g_prior_asg_action_id
and prr.element_type_id = pet.element_type_id
and pet.ELEMENT_NAME = p_element_name
and pet.legislation_code = 'FR'
and pet.business_group_id is NULL
and g_prior_pay_action_date between pet.effective_start_date and pet.effective_end_date
and pet.element_type_id = piv.element_type_id
and g_prior_pay_action_date between piv.effective_start_date and piv.effective_end_date
and piv.name = p_input_value_name
and piv.legislation_code = 'FR'
and piv.business_group_id is NULL
and prrv.input_value_id = piv.input_value_id
and prrv.run_result_id = prr.run_result_id;
select group_code
into l_group_code
from pay_fr_contribution_usages
where contribution_usage_id = p_cu_id;
select *
from pay_fr_contribution_usages cu
where cu.process_type = p_process_type
and cu.contribution_usage_type = p_usage_type
and p_effective_date between cu.date_from and nvl(cu.date_to,to_date('31-12-4712','DD-MM-YYYY'))
and cu.element_name = p_element_name
and (cu.business_group_id is NULL or cu.business_group_id = p_business_group_id);
select ff.formula_id,
ff.effective_start_date
from ff_formulas_f ff
, ff_formula_types ft
where ft.formula_type_name = 'Oracle Payroll'
and ft.formula_type_id = ff.formula_type_id
and ff.formula_name = p_formula_name
and p_effective_date between ff.effective_start_date and ff.effective_end_date
and nvl(ff.business_group_id,-1) = p_business_group_id
and nvl(ff.legislation_code,'FR') = 'FR';
select nvl(nvl(act_parent.source_action_id, act_child.source_action_id),act_child.assignment_action_id)
from pay_assignment_actions act_child,
pay_assignment_actions act_parent
where act_child.assignment_action_id = p_asg_action_id
and act_parent.assignment_action_id (+) = act_child.source_action_id;
g_deduction_rates.delete;
/* Delete all existing values from the PL/SQL temp tables */
g_band_table.delete;
g_base_code_table.delete;
g_summary_deductions.delete;
select oi1.org_information4 order_number
from hr_organization_information oi1
where oi1.organization_id = p_establishment_id
and oi1.org_information_context = 'FR_ESTAB_PE_PRVS'
and oi1.org_information1 = p_emp_pension_provider_id;
select oi1.org_information4 order_number
from hr_organization_information oi1
, hr_organization_information oi2
where oi1.organization_id = p_establishment_id
and oi1.org_information_context = 'FR_ESTAB_PE_PRVS'
and oi1.org_information3 = 'Y'
and oi1.org_information1 = oi2.organization_id
and oi2.org_information2 = p_provider_type
and oi2.org_information_context = 'FR_PE_PRV_INFO';
select min(paa.date_start)
from pay_balance_types pbt
, pay_balance_feeds_f pbf
, pay_input_values_f piv
, pay_element_types_f pet
, pay_element_entries_f pee
, pay_element_links_f pel
, per_absence_attendances paa
where pbt.balance_name = 'FR_SICKNESS_ABSENCE_DAYS'
and pbt.business_group_id IS NULL
and pbt.legislation_code = 'FR'
and pbt.balance_type_id = pbf.balance_type_id
and pbf.input_value_id = piv.input_value_id
and pbf.business_group_id = p_business_group_id
and piv.element_type_id = pet.element_type_id
and pet.element_type_id = pel.element_type_id
and pet.business_group_id = p_business_group_id
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = p_assignment_id
and pee.creator_type = 'A'
and pee.creator_id = paa.absence_attendance_id
and paa.date_start > p_start_of_year
and pbf.effective_start_date <= pee.effective_start_date
and pbf.effective_end_date >= pee.effective_end_date;
select /*+ USE_NL(pbt pee pee2) */
sum(paa.absence_days)
from pay_balance_types pbt
, pay_balance_feeds_f pbf
, pay_input_values_f piv
, pay_element_types_f pet
, pay_element_entries_f pee
, pay_element_links_f pel
, per_absence_attendances paa
, pay_element_entries_f pee2
, pay_element_links_f pel2
, pay_element_types_f pet2
where pbt.balance_name = 'FR_SICKNESS_ABSENCE_DAYS'
and pbt.business_group_id IS NULL
and pbt.legislation_code = 'FR'
and pbt.balance_type_id = pbf.balance_type_id
and pbf.input_value_id = piv.input_value_id
and pbf.business_group_id = p_business_group_id
and piv.element_type_id = pet.element_type_id
and pet.element_type_id = pel.element_type_id
and pet.business_group_id = p_business_group_id
and pet.effective_start_date <= p_from_date
and pet.effective_end_date >= p_to_date
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = p_assignment_id
and pee.creator_type = 'A'
and pee.creator_id = paa.absence_attendance_id
and paa.date_start >= p_from_date
and paa.date_end <= p_to_date
and pbf.effective_start_date <= pee.effective_start_date
and pbf.effective_end_date >= pee.effective_end_date
/* Added to ensure that absences where employee is ARRCO are excluded */
and paa.date_start between pee2.effective_start_date and pee2.effective_end_date
and pee2.assignment_id = p_assignment_id
and pee2.entry_type = 'E'
and pee2.creator_type = 'F'
and pee2.element_link_id = pel2.element_link_id
and paa.date_start between pel2.effective_start_date and pel2.effective_end_date
and pel2.element_type_id = pet2.element_type_id
and paa.date_start between pet2.effective_start_date and pet2.effective_end_date
and pet2.element_name = 'FR_PENSION'
and pet2.legislation_code = 'FR'
and 'Y' = hruserdt.get_table_value(p_business_group_id
, 'FR_APEC_AGIRC', 'AGIRC'
, pee2.entry_information1, paa.date_start);
select ee.screen_entry_value
, b.pay_basis
-- commented as part of time analysis changes
--, a.normal_hours
--, a.frequency
, decode(pcf.ctr_information12, 'HOUR', fnd_number.canonical_to_number(pcf.ctr_information11), a.normal_hours) normal_hours
, decode(pcf.ctr_information12, 'HOUR', pcf.ctr_information13, a.frequency) frequency
from pay_element_entry_values_f ee
, pay_element_entries_f e
, per_all_assignments_f a
, per_pay_bases b
--
, per_contracts_f pcf
--
where a.assignment_id = p_assignment_id
and a.assignment_id = e.assignment_id
and e.element_entry_id = ee.element_entry_id
and ee.input_value_id = b.input_value_id
and a.pay_basis_id = b.pay_basis_id
and b.pay_basis in ('HOURLY','MONTHLY','ANNUAL')
--
and pcf.contract_id = a.contract_id
--
and p_effective_date between a.effective_start_date and a.effective_end_date
and p_effective_date between e.effective_start_date and e.effective_end_date
and p_effective_date between ee.effective_start_date and ee.effective_end_date
--
and p_effective_date between pcf.effective_start_date and pcf.effective_end_date;
select ptp2.start_date,
ptp2.end_date
from per_time_periods ptp,
pay_payroll_actions ppa,
per_time_periods ptp2
where ppa.date_earned BETWEEN ptp.START_DATE and ptp.END_DATE
and ppa.payroll_action_id = p_payroll_action_id
and ptp.payroll_id = ppa.payroll_id
and ptp2.end_date = ptp.start_date - 1
and ptp2.payroll_id = ppa.payroll_id;
SELECT first_name,last_name,per_information1,sex,marital_status
FROM per_all_people_f
WHERE person_id = p_employee_id
AND per_information_category ='FR';
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_balance_name
AND pbd.database_item_suffix = '_ASG_PTD'
AND pdb.legislation_code = 'FR';
if l_action <> 'U' then -- { insert row
hr_utility.set_location(l_proc, 60);
end if; -- } insert row
select CINST.value,
CINST.user_column_instance_id,
CINST.user_row_id
from pay_user_column_instances CINST
, pay_user_columns C
, pay_user_rows R
, pay_user_tables TAB
where TAB.user_table_name = p_table_name
and C.user_table_id = TAB.user_table_id
and nvl (C.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (C.legislation_code,
'FR') = 'FR'
and C.user_column_name = 'RATE'
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and nvl (R.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (R.legislation_code,
'FR') = 'FR'
and p_row_value = R.row_low_range_or_name
and CINST.user_row_id = R.user_row_id
and nvl (CINST.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (CINST.legislation_code,
'FR') = 'FR';