[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.start_date,
ppa.effective_date,
ppa.business_group_id,
ppa.report_type,
ppa.legislative_parameters
INTO p_year_start,
p_year_end,
p_business_group_id,
p_report_type,
p_legislative_parameters
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_pactid;
select user_entity_id
from ff_database_items
where user_name = p_user_name;
p_sqlstr := 'select distinct to_number(fai1.value)
from ff_archive_items fai1,
ff_archive_items fai2,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payroll_actions ppa1
where ppa1.payroll_action_id = :p_pactid
and ppa.report_type = ''T4A''
and ppa.report_qualifier = ''CAEOY''
and ppa.report_category = ''CAEOY''
and ppa.action_type = ''X''
and ppa.action_status = ''C''
and ppa.business_group_id = ppa1.business_group_id
and ppa.effective_date = ppa1.effective_date
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = ''C''
and fai2.user_entity_id = '|| l_tax_year_ue_id ||
' and fai2.context1 = paa.payroll_action_id
and fai2.value = '|| l_tax_year ||
' and fai1.context1 = paa.assignment_action_id
and fai1.user_entity_id = '||l_person_id_ue_id||
' order by to_number(fai1.value)';
SELECT paf.person_id,
paf.assignment_id,
Paa.tax_unit_id,
paf.effective_end_date,
paa.assignment_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa1,
hr_organization_information hoi1
WHERE ppa1.payroll_action_id = p_pactid
AND ppa.report_type = 'T4A'
and ppa.report_qualifier = 'CAEOY'
and ppa.report_category = 'CAEOY'
and ppa.action_type = 'X'
and ppa.action_status = 'C'
AND ppa.business_group_id = ppa1.business_group_id
AND ppa.effective_date = ppa1.effective_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
and hoi1.org_information_context= 'Canada Employer Identification'
and hoi1.org_information11 = l_trans_gre
and paa.tax_unit_id = hoi1.organization_id
AND paf.assignment_id = paa.assignment_id
AND paf.person_id BETWEEN p_stperson and p_endperson
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
AND paf.effective_end_date = (SELECT MAX(paf2.effective_end_date)
FROM per_all_assignments_f paf2
WHERE paf2.assignment_id = paf.assignment_id
AND paf2.effective_start_date <= ppa.effective_date);
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
select to_number(pai.action_information5) ppreg_amt,
pai.action_information4 ppreg_no
from pay_action_information pai,pay_payroll_actions ppa
where pai.action_context_id = cp_pact_id
and pai.tax_unit_id = cp_tax_unit_id
and ppa.payroll_action_id = pai.action_context_id
and pai.effective_date = ppa.effective_date
and pai.action_information_category = 'CAEOY PENSION PLAN INFO'
order by 1 desc;
select pai.action_information5
from pay_action_information pai
where pai.action_context_id = cp_assignment_action_id
and pai.action_information_category = 'CA FOOTNOTES'
and pai.action_information4 = cp_footnote_code
order by 1 desc;
Select ppa.payroll_action_id,ppa.business_group_id
from hr_organization_information hoi,
pay_payroll_actions PPA
where hoi.organization_id = to_number(c_trans_id)
and hoi.org_information_context='Fed Magnetic Reporting'
and ppa.report_type = 'T4A' -- T4 Archiver Report Type
and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='))
and to_char(ppa.effective_date,'YYYY')= c_year
and to_char(ppa.effective_date,'DD-MM')= '31-12';
Select distinct ppa.payroll_action_id, hoi.organization_id, hou.name
From pay_payroll_actions ppa,
hr_organization_information hoi,
hr_all_organization_units hou
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information11 = p_trans
and hou.business_group_id = p_bg_id
and hou.organization_id = hoi.organization_id
and ppa.report_type = 'T4A'
and ppa.effective_date = to_date('31-12'||p_year,'DD-MM-YYYY')
and ppa.business_group_id = p_bg_id
and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='));
select hou.name
from hr_all_organization_units hou
where hou.organization_id = to_number(b_org_id);
select fai.value
from ff_database_items fdi,
ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_asg_act_id
and fdi.user_name = cp_dbitem;
select to_char(to_number(p_data), p_format)
into l_data from dual;
select to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
into l_data from dual;
select fai.value
from ff_archive_items fai,
ff_database_items fdi
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = b_context_id
and fdi.user_name = b_user_name;