[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select user_entity_id
from ff_database_items
where user_name = p_user_name;
select
legislative_parameters
into
leg_param
from
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
sqlstr := 'select distinct to_number(fai3.value)
from ff_archive_items fai1,
ff_archive_items fai2,
ff_archive_items fai3,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = :payroll_action_id
and fai1.user_entity_id = ' || l_uid_tax_year ||
' and fai1.value =
nvl(pay_ca_t4a_reg.get_parameter(''TAX_YEAR'',ppa.legislative_parameters),fai1.value)
and fai2.user_entity_id = ' || l_uid_tax_unit_id ||
' and fai2.value =
nvl(pay_ca_t4a_reg.get_parameter(''GRE_ID'',ppa.legislative_parameters),fai2.value)
and fai1.context1 = fai2.context1
and paa.payroll_action_id= fai2.context1
and paa.assignment_action_id=fai3.context1
and fai3.user_entity_id = ' || l_uid_person_id ||
' and fai3.value =
nvl(pay_ca_t4a_reg.get_parameter(''PER_ID'',ppa.legislative_parameters),fai3.value)
order by to_number(fai3.value)';
select distinct to_number(fai2.value) tax_unit_id,
payroll_action_id arch_pactid,
ppa.effective_date
from pay_payroll_actions ppa,
ff_archive_items fai1,
ff_archive_items fai2
where fai1.user_entity_id = l_uid_caeoy_tax_year
and fai1.value = l_year
and fai2.context1 = fai1.context1
and fai2.user_entity_id = l_uid_caeoy_tax_unit_id
and ppa.payroll_action_id = fai1.context1
and ppa.report_type = 'T4A'
and ppa.report_qualifier = 'CAEOY'
and ppa.report_category = 'CAEOY'
and ppa.action_type = 'X'
and ppa.business_group_id+0 = l_bus_group_id;
select payroll_action_id arch_pactid,
ppa.effective_date
from pay_payroll_actions ppa,
ff_archive_items fai1,
ff_archive_items fai2
where fai1.user_entity_id = l_uid_caeoy_tax_year
and fai1.value = l_year
and ppa.payroll_action_id = fai1.context1
and ppa.report_type = 'T4A'
and ppa.report_qualifier = 'CAEOY'
and ppa.report_category = 'CAEOY'
and ppa.action_type = 'X'
and ppa.business_group_id + 0 = l_bus_group_id
and fai1.context1 = fai2.context1
and fai2.user_entity_id = l_uid_caeoy_tax_unit_id
and fai2.value = to_char(l_t4areg_tax_unit_id);
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa
where
paf.person_id >= stperson and
paf.person_id <= endperson and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid;
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa,
pay_population_ranges ppr
where
-- paf.person_id >= stperson and
-- paf.person_id <= endperson and
ppr.payroll_action_id = pactid and
ppr.chunk_number = chunk and
ppr.person_id = paf.person_id and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid;
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa,
per_periods_of_service PDS
where
paf.person_id >= stperson and
paf.person_id <= endperson and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid and
pds.actual_termination_date is not null and
pds.period_of_service_id = paf.period_of_service_id;
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa,
pay_population_ranges ppr,
per_periods_of_service PDS
where
-- paf.person_id >= stperson and
-- paf.person_id <= endperson and
ppr.payroll_action_id = pactid and
ppr.chunk_number = chunk and
ppr.person_id = paf.person_id and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid and
pds.actual_termination_date is not null and
pds.period_of_service_id = paf.period_of_service_id;
select paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from per_assignments_f paf,
pay_assignment_actions paa
where paf.person_id between stperson
and endperson
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and paf.business_group_id = l_bus_group_id
and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date)
and paf.effective_end_date >= l_year_start
and paa.payroll_action_id = p_arch_pactid
and paa.assignment_id = paf.assignment_id
and paa.serial_number = p_per_id;
select paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from per_assignments_f paf,
pay_assignment_actions paa,
per_periods_of_service pds
where paf.person_id between stperson
and endperson
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and paf.business_group_id = l_bus_group_id
and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date)
and paf.effective_end_date >= l_year_start
and paa.payroll_action_id = p_arch_pactid
and paa.assignment_id = paf.assignment_id
and paa.serial_number = p_per_id
and pds.actual_termination_date is not null
and pds.period_of_service_id = paf.period_of_service_id;
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa
where
paf.person_id >= stperson and
paf.person_id <= endperson and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid and
exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = l_asg_set_id
and hasa.assignment_id = paf.assignment_id
and upper(hasa.include_or_exclude) = 'I');
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa,
pay_population_ranges ppr
where
-- paf.person_id >= stperson and
-- paf.person_id <= endperson and
ppr.payroll_action_id = pactid and
ppr.chunk_number = chunk and
ppr.person_id = paf.person_id and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid and
exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = l_asg_set_id
and hasa.assignment_id = paf.assignment_id
and upper(hasa.include_or_exclude) = 'I');
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa,
per_periods_of_service pds
where
paf.person_id >= stperson and
paf.person_id <= endperson and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid and
exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = l_asg_set_id
and hasa.assignment_id = paf.assignment_id
and upper(hasa.include_or_exclude) = 'I')
and pds.actual_termination_date is not null
and pds.period_of_service_id = paf.period_of_service_id;
select
paf.assignment_id assignment_id,
paa.assignment_action_id,
paa.payroll_action_id,
paf.effective_end_date
from
per_assignments_f paf,
pay_assignment_actions paa,
pay_population_ranges ppr,
per_periods_of_service pds
where
-- paf.person_id >= stperson and
-- paf.person_id <= endperson and
ppr.payroll_action_id = pactid and
ppr.chunk_number = chunk and
ppr.person_id = paf.person_id and
paf.primary_flag = 'Y' and
paf.assignment_type = 'E' and
paf.business_group_id = l_bus_group_id and
paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.primary_flag = 'Y'
and paf2.effective_start_date
<= l_session_date) and --l_year_end
paf.effective_end_date >= l_year_start and
paf.assignment_id = paa.assignment_id and
paa.payroll_action_id = p_arch_pactid and
exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = l_asg_set_id
and hasa.assignment_id = paf.assignment_id
and upper(hasa.include_or_exclude) = 'I')
and pds.actual_termination_date is not null
and pds.period_of_service_id = paf.period_of_service_id;
select
effective_date,
report_type,
-- Added for bug 10399514
report_qualifier,
report_category,
-- Added for bug 10399514
business_group_id,
legislative_parameters
into
l_effective_date,
l_report_type,
-- Added for bug 10399514
l_state,
l_report_cat,
-- Added for bug 10399514
l_bus_group_id,
l_legislative_parameters
from
pay_payroll_actions
where
payroll_action_id = pactid;
select report_format
into l_report_format
from pay_report_format_mappings_f
where report_type = l_report_type
and report_qualifier = l_state
and report_category = l_report_cat ;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions paa
set paa.serial_number = lv_serial_number
where paa.assignment_action_id = lockingactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions paa
set paa.serial_number = lv_serial_number
where paa.assignment_action_id = lockingactid;
sqlstr := 'select paa1.rowid
from hr_all_organization_units hou,
hr_all_organization_units hou1,
hr_locations_all loc,
per_all_people_f ppf,
per_all_assignments_f paf,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where ppa1.payroll_action_id = :pactid
and paa1.payroll_action_id = ppa1.payroll_action_id
and paa1.assignment_id = paf.assignment_id
and paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.assignment_id= paf.assignment_id
and paf2.effective_start_date
<= ppa1.effective_date)
and paf.effective_end_date >= ppa1.start_date
and paf.assignment_type = ''E''
and hou1.organization_id = paa1.tax_unit_id
and hou.organization_id = paf.organization_id
and loc.location_id = paf.location_id
and ppf.person_id = paf.person_id
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_all_people_f ppf2
where ppf2.person_id= paf.person_id
and ppf2.effective_start_date
<= ppa1.effective_date)
and ppf.effective_end_date >= ppa1.start_date
order by
decode(pay_ca_t4_reg.get_parameter
(''P_S1'',ppa1.legislative_parameters),
''GRE'',hou1.name,
''ORGANIZATION'',hou.name,
''LOCATION'',loc.location_code,null),
decode(pay_ca_t4_reg.get_parameter(''P_S2'',
ppa1.legislative_parameters),
''GRE'',hou1.name,
''ORGANIZATION'',hou.name,
''LOCATION'',loc.location_code,null),
decode(pay_ca_t4_reg.get_parameter(''P_S3'',
ppa1.legislative_parameters),
''GRE'',hou1.name,
''ORGANIZATION'',hou.name,
''LOCATION'',loc.location_code,null),
ppf.last_name,first_name
for update of paa1.assignment_action_id';
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select 1 ord, meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and ( ( p_person_language is null and language = 'US' ) or
( p_person_language is not null and language = p_person_language ) )
union all
select 2 ord, meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and ( language = 'US' and p_person_language is not null
and language <> p_person_language )
order by 1;