[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;
p_sqlstr := 'select distinct to_number(fai1.value)
from ff_archive_items fai1,
ff_database_items fdi1,
ff_archive_items fai2,
ff_database_items fdi2,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payroll_actions ppa1
where ppa1.payroll_action_id = :payroll_action_id
and ppa.business_group_id+0 = ppa1.business_group_id
and ppa.effective_date = ppa1.effective_date
and ppa.report_type = ''T4''
and ppa.payroll_action_id = paa.payroll_action_id
and fdi2.user_name = ''CAEOY_TAXATION_YEAR''
and fai2.user_entity_id = fdi2.user_entity_id
and fai2.value = pay_ca_t4_mag.get_parameter(''REPORTING_YEAR'',ppa1.legislative_parameters)
and paa.payroll_action_id= fai2.context1
and paa.action_status = ''C''
and paa.assignment_action_id = fai1.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = ''CAEOY_PERSON_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
WHERE ppa1.payroll_action_id = p_pactid
AND ppa.report_type = 'T4'
AND ppa.business_group_id+0 = 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 exists ( /* Query to select all GRE 's under a transmitter GRE */
select 'X'
from
hr_organization_information hoi1,
hr_organization_information hoi
where hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context = 'Canada Employer Identification'
and to_number(hoi.org_information11) = pay_ca_t4_mag.get_parameter('TRANSMITTER_GRE',ppa1.legislative_parameters)
and hoi1.org_information_context = 'Fed Magnetic Reporting'
and hoi.org_information5 = 'T4/RL1'
and hoi1.organization_id = to_number(hoi.org_information11)
)
AND paf.assignment_id = paa.assignment_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND paf.assignment_type = 'E'
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 )
ORDER BY paf.person_id;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT fai1.value
FROM FF_ARCHIVE_ITEMS FAI1,
ff_database_items fdi1,
ff_archive_item_contexts faic,
ff_contexts fc
WHERE FAI1.USER_ENTITY_ID = fdi1.user_entity_id
and fdi1.user_name = cp_dbitem
and fai1.archive_item_id = faic.archive_item_id
and fc.context_id = faic.context_id
and fc.context_name = 'JURISDICTION_CODE'
and faic.context = cp_jurisdiction
AND FAI1.CONTEXT1 = p_asg_act_id;
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;
Select ppa.payroll_action_id, ppa.business_group_id
from hr_organization_information hoi,
pay_payroll_actions PPA,
pay_ca_legislation_info pcli,
pay_ca_legislation_info pcli1
where hoi.organization_id = to_number(c_trans_id)
and hoi.org_information_context='Fed Magnetic Reporting'
and ppa.report_type = 'T4' -- 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'
and pcli.information_type = 'MAX_CPP_EARNINGS'
and ppa.effective_date between pcli.start_date and pcli.end_date
and pcli1.information_type = 'MAX_EI_EARNINGS'
and ppa.effective_date between pcli1.start_date and pcli1.end_date;
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 = 'T4'
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);