[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'Selection Criterion'
parameters to the RL2 SRS Defn.
Removed references to hr_soft_coding_keyflex
13-NOV-2006 ssmukher 115.15 Added the orderby clause in c_all_asg cursor.
05-SEP-2011 rgottipa 115.17 Bug 10399514, introduced new cursores
c_all_asg_range, c_single_asg_range and
c_all_asg_in_asgset_range. These will be
called if RANGE_PERSON_ID is enabled.
14-SEP-2011 sneelapa 115.18 Bug 12965359, Modified order of the variables
in fetch clause for CURSORs in ACTION_CREATION
procedure.
31-Dec-2012 rgottipa 115.19 Bug 15886428, Done changes to support print
terminate employees and Self Service
'paper' option.
11-Jan-2013 rgottipa 115.20 If CURSOR c_get_asg_id_term is returning no
rows then exit from block instead of erroring.
25-Jan-2013 rgottipa 115.21 Bug 16208287, 'paper' option should not affect
the register report.
30-Apr-2013 rgottipa 115.22 Bug 16730209, using dummy for loop to exit
if no data found in CURSOR c_get_asg_id_term.
----------------------------------- range_cursor -----------------------------
*/
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_payroll_id number;
select legislative_parameters
into leg_param
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
sqlstr := 'select distinct to_number(paa_arch.serial_number)
from pay_action_information pai1,
pay_action_information pai2,
pay_payroll_actions ppa_reg,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch
where ppa_reg.payroll_action_id = :payroll_action_id
and pai1.action_context_type = ''PA''
and pai1.action_information1 = ''RL2''
and pai1.action_information_category = ''CAEOY TRANSMITTER INFO''
and pai1.action_information8 = nvl(' ||l_taxyear ||', pai1.action_information8)
and pai1.action_information27 = nvl(' ||l_pre_org_id || ',pai1.action_information27)
and pai2.action_context_type = ''AAP''
and pai2.action_information_category = ''CAEOY RL2 EMPLOYEE INFO''
and ppa_arch.payroll_action_id = pai1.action_context_id
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and paa_arch.assignment_action_id = pai2.action_context_id
and paa_arch.action_status = ''C''
and paa_arch.serial_number = nvl(pay_ca_rl2_reg.get_parameter(''PER_ID'',ppa_reg.legislative_parameters),
paa_arch.serial_number)
order by to_number(paa_arch.serial_number)';
select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters )
from pay_payroll_actions ppa
where ppa.report_type = 'RL2'
and ppa.report_qualifier = 'CAEOYRL2'
and ppa.report_category = 'ARCHIVE'
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 = 'RL2'
and ppa.report_qualifier = 'CAEOYRL2'
and ppa.report_category = 'ARCHIVE'
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 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 distinct hoi.organization_id
from hr_organization_information hoi,
hr_all_organization_units hou
where hou.business_group_id = l_bus_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = l_pre_org_id
and hoi.org_information5 = 'T4A/RL2';
select distinct paa.assignment_id,
to_number(paa.serial_number)
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'RL2'
and ppa.report_qualifier = 'CAEOYRL2'
and ppa.report_category = 'ARCHIVE'
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 to_number(paa.serial_number) between stperson and endperson
order by to_number(paa.serial_number);
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
*/
select distinct paa.assignment_id assignment_id,
to_number(paa.serial_number)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_population_ranges ppr
where ppa.report_type = 'RL2'
and ppa.report_qualifier = 'CAEOYRL2'
and ppa.report_category = 'ARCHIVE'
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 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 to_number(paa.serial_number);
select distinct paa.assignment_id,
to_number(paa.serial_number)
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'RL2'
and ppa.report_qualifier = 'CAEOYRL2'
and ppa.report_category = 'ARCHIVE'
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 to_number(paa.serial_number) between stperson and endperson
and paa.serial_number = l_per_id;
select distinct paa.assignment_id,
to_number(paa.serial_number)
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = 'RL2'
and ppa.report_qualifier = 'CAEOYRL2'
and ppa.report_category = 'ARCHIVE'
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 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));
select distinct to_number(paa.serial_number),
paa.assignment_id assignment_id
*/
select distinct paa.assignment_id assignment_id,
to_number(paa.serial_number)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_population_ranges ppr
where ppa.report_type = 'RL2'
and ppa.report_qualifier = 'CAEOYRL2'
and ppa.report_category = 'ARCHIVE'
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 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 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 ppa1.effective_date >= paf.effective_start_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_rl2_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_rl2_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_rl2_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 hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = p_lookup_type AND
hl.lookup_code = p_lookup_code;
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'
and p_effective_date between
addr.date_from and nvl(addr.date_to, p_effective_date)
and country.territory_code = addr.country;