[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_database_items fdi1,
ff_database_items fdi2,
ff_archive_items fai3,
ff_database_items fdi3,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = :payroll_action_id
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = ''CAEOY_TAXATION_YEAR''
and fai1.value =
nvl(pay_ca_rl1_reg.get_parameter(''TAX_YEAR'',
ppa.legislative_parameters),fai1.value)
and fai2.user_entity_id = fdi2.user_entity_id
and fdi2.user_name = ''CAEOY_RL1_PRE_ORG_ID''
and fai2.value =
nvl(pay_ca_rl1_reg.get_parameter(''PRE_ORGANIZATION_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 = fdi3.user_entity_id
and fdi3.user_name = ''CAEOY_PERSON_ID''
and fai3.value = nvl(pay_ca_rl1_reg.get_parameter(''PER_ID'',
ppa.legislative_parameters),fai3.value)
order by to_number(fai3.value)';
select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters )
from pay_payroll_actions ppa
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_bus_group_id
and ppa.action_status = 'C';
select paa.assignment_action_id,
ppa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_bus_group_id
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paa.serial_number = to_char(cp_person_id)
and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) = cp_pre_org_id
and paa.assignment_id = cp_assignment_id;
select distinct organization_id
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = p_pre_org_id;
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_business_group_id
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and to_number(paa.serial_number) between stperson and endperson
order by 1, 2;
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_population_ranges ppr
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_business_group_id
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
--and to_number(paa.serial_number) between stperson and endperson
and ppr.payroll_action_id = pactid
and ppr.chunk_number = chunk
and ppr.person_id = to_number(paa.serial_number)
order by 1, 2;
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_business_group_id
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and to_number(paa.serial_number) between stperson and endperson
and paa.serial_number = l_per_id;
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_population_ranges ppr
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_business_group_id
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
--and to_number(paa.serial_number) between stperson and endperson
and ppr.payroll_action_id = pactid
and ppr.chunk_number = chunk
and ppr.person_id = l_per_id
and paa.serial_number = l_per_id;
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_business_group_id
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and to_number(paa.serial_number) between stperson and endperson
and exists (select 1
from hr_assignment_set_amendments hasa,
per_assignments_f paf
where hasa.assignment_set_id = l_asg_set_id
and upper(hasa.include_or_exclude) = 'I'
and hasa.assignment_id = paf.assignment_id
and paf.person_id = to_number(paa.serial_number))
order by 1,2;
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_population_ranges ppr
where ppa.report_type = 'RL1'
and ppa.report_qualifier = 'CAEOYRL1'
and ppa.report_category = 'CAEOYRL1'
and ppa.effective_date = l_year_end
and ppa.start_date = l_year_start
and ppa.business_group_id = l_business_group_id
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
--and to_number(paa.serial_number) between stperson and endperson
and ppr.payroll_action_id = pactid
and ppr.chunk_number = chunk
and ppr.person_id = to_number(paa.serial_number)
and exists (select 1
from hr_assignment_set_amendments hasa,
per_assignments_f paf
where hasa.assignment_set_id = l_asg_set_id
and upper(hasa.include_or_exclude) = 'I'
and hasa.assignment_id = paf.assignment_id
and paf.person_id = to_number(paa.serial_number))
order by 1,2;
select paf.assignment_id,
paf.effective_end_date
from per_assignments_f paf
where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and paf.effective_start_date <= l_year_end
and paf.effective_end_date >= l_year_start
order by assignment_id desc,paf.effective_end_date desc;
select paf.assignment_id,
paf.effective_end_date
from per_assignments_f paf,
per_periods_of_service pds
where paf.person_id = p_person_id
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and paf.effective_start_date <= l_year_end
and paf.effective_end_date >= l_year_start
and pds.actual_termination_date is not null
and pds.period_of_service_id = paf.period_of_service_id
order by assignment_id desc,paf.effective_end_date desc;
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 /* we need the row id of the assignment actions that are created by PYUGEN */
from hr_all_organization_units hou1,
hr_all_organization_units hou,
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.assignment_type = ''E''
and paf.primary_flag = ''Y''
and paf.business_group_id = ppa1.business_group_id
and paf.effective_start_date <= ppa1.effective_date
and hou.organization_id = paa1.tax_unit_id
and loc.location_id = paf.location_id
and hou1.organization_id = paf.organization_id
and ppf.person_id = paf.person_id
and ppa1.effective_date between
ppf.effective_start_date and ppf.effective_end_date
and paf.effective_end_date =
(select max(paaf2.effective_end_date)
from per_all_assignments_f paaf2
where paaf2.assignment_id = paf.assignment_id
and paaf2.effective_start_date <= ppa1.effective_date)
order by
decode(pay_ca_rl1_reg.get_parameter(''P_S1'',ppa1.legislative_parameters),
''RL1_PRE'',hou.name,
''RL1_ORG'',hou1.name,
''RL1_LOC'',loc.location_code,null)
,decode(pay_ca_rl1_reg.get_parameter(''P_S2'',ppa1.legislative_parameters),
''RL1_PRE'',hou.name,
''RL1_ORG'',hou1.name,
''RL1_LOC'',loc.location_code,null)
,decode(pay_ca_rl1_reg.get_parameter(''P_S3'',ppa1.legislative_parameters),
''RL1_PRE'',hou.name,
''RL1_ORG'',hou1.name,
''RL1_LOC'',loc.location_code,null)
,ppf.last_name,ppf.first_name';
select addr.address_line1
,addr.address_line2
,addr.address_line3
,rtrim(substr(addr.town_or_city,1,23)) ||' '||
decode(addr.country, 'CA', addr.region_1, 'US', addr.region_2,
addr.region_1 )
||' '|| addr.country -- Bug 4134616
||' '|| addr.postal_code address_line4
,addr.country address_line5 -- Country Code
,country.territory_short_name address_line6 -- Country Name
,addr.town_or_city Town_or_City
,decode(addr.country, 'CA', addr.region_1,
'US', addr.region_2, addr.region_1 ) Province
,addr.postal_code Postal_Code
from per_addresses addr
,fnd_territories_vl country
where addr.person_id = p_person_id
and addr.primary_flag = 'Y'
/* Added the trunc function by ssmukher for Bug 4205724 */
and trunc(p_effective_date) between
addr.date_from and nvl(addr.date_to, trunc(p_effective_date))
and country.territory_code = addr.country;
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;