[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(fai4.value)
from ff_archive_items fai1,
ff_archive_items fai2,
ff_archive_items fai3,
ff_archive_items fai4,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = :payroll_action_id
and fai1.user_entity_id = ' || l_uid_caeoy_tax_year ||
' and fai1.value =
nvl(pay_ca_t4_reg.get_parameter(''TAX_YEAR'',
ppa.legislative_parameters),fai1.value)
and fai1.context1 = paa.payroll_action_id
and fai2.user_entity_id = ' || l_uid_caeoy_tax_unit_id ||
' and fai2.value =
nvl(pay_ca_t4_reg.get_parameter(''GRE_ID'',
ppa.legislative_parameters),fai2.value)
and fai2.context1 = paa.payroll_action_id
and fai3.user_entity_id = ' || l_uid_caeoy_prov_of_emp ||
' and fai3.value =
nvl(pay_ca_t4_reg.get_parameter(''PROV_CD'',
ppa.legislative_parameters),fai3.value)
and fai3.context1 = paa.assignment_action_id
and fai4.user_entity_id = ' || l_uid_caeoy_person_id ||
' and fai4.context1 = paa.assignment_action_id
and fai4.value = nvl(pay_ca_t4_reg.get_parameter(''PER_ID'',
ppa.legislative_parameters),fai4.value)
order by to_number(fai4.value)';
SELECT
pay_ca_t4_reg.get_parameter('TRANSFER_GRE', ppa.legislative_parameters),
ppa.payroll_action_id,
ppa.effective_date
FROM
pay_payroll_actions ppa
WHERE
ppa.report_type = 'T4' AND
ppa.report_category = 'CAEOY' and
ppa.report_qualifier = 'CAEOY' and
ppa.business_group_id = l_bus_group_id and
ppa.effective_date = l_year_end and
ppa.action_status = 'C';
SELECT
ppa.payroll_action_id,
ppa.effective_date
FROM
pay_payroll_actions ppa
WHERE
ppa.report_type = 'T4' AND
ppa.report_category = 'CAEOY' and
ppa.report_qualifier = 'CAEOY' and
ppa.business_group_id = l_bus_group_id and
pay_ca_t4_reg.get_parameter('TRANSFER_GRE', ppa.legislative_parameters)
= to_char(p_tax_unit_id) and
ppa.effective_date = l_year_end and
ppa.action_status = 'C';
select paf.assignment_id assignment_id,
faic.context prov_cd,
paa.assignment_action_id,
paa.payroll_action_id
from per_assignments_f paf,
pay_assignment_actions paa,
ff_archive_items fai,
ff_contexts fc,
ff_archive_item_contexts faic
where paf.person_id between stperson
and endperson
and paf.primary_flag = 'Y'
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) --l_year_end
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.assignment_action_id = fai.context1
and fai.user_entity_id = l_uid_caeoy_gross_earning
and fai.archive_item_id = faic.archive_item_id
and faic.context = nvl(rtrim(p_prov), faic.context)
and faic.context_id = fc.context_id
and fc.context_name = 'JURISDICTION_CODE';
select paf.assignment_id assignment_id,
faic.context prov_cd,
paa.assignment_action_id,
paa.payroll_action_id
from per_assignments_f paf,
pay_assignment_actions paa,
ff_archive_items fai,
ff_contexts fc,
ff_archive_item_contexts faic
where paf.person_id between stperson
and endperson
and paf.primary_flag = 'Y'
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) --l_year_end
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 paa.assignment_action_id = fai.context1
and fai.user_entity_id = l_uid_caeoy_gross_earning
and fai.archive_item_id = faic.archive_item_id
and faic.context = nvl(rtrim(p_prov), faic.context)
and faic.context_id = fc.context_id
and fc.context_name = 'JURISDICTION_CODE';
select paf.assignment_id assignment_id,
faic.context prov_cd,
paa.assignment_action_id,
paa.payroll_action_id
from per_assignments_f paf,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_item_contexts faic,
ff_contexts fc
where paf.person_id >= stperson
and paf.person_id <= endperson
and paf.primary_flag = 'Y'
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) --l_year_end
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.assignment_action_id = fai.context1
and fai.user_entity_id = l_uid_caeoy_gross_earning
and fai.archive_item_id = faic.archive_item_id
and faic.context = nvl(rtrim(p_prov), faic.context)
and faic.context_id = fc.context_id
and fc.context_name = 'JURISDICTION_CODE'
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 effective_date,
report_type,
business_group_id,
legislative_parameters
into l_effective_date,
l_report_type,
l_bus_group_id,
l_legislative_parameters
from pay_payroll_actions
where payroll_action_id = pactid;
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 hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = p_lookup_type AND
hl.lookup_code = p_lookup_code;