[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_selected_asg_set_range
it will be called in place of
c_selected_asg_set CURSOR, if
RANGE_PERSON_ID is enabled.
****************************************************************************/
gv_package VARCHAR2(100) := 'pay_ca_eoy_rl2_amend_arch';
select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'SSN',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'ASG_SET',ppa.legislative_parameters)),
to_char(effective_date,'YYYY') ,
effective_date,
start_date,
business_group_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'SSN',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
'ASG_SET',ppa.legislative_parameters)),
to_char(effective_date,'YYYY') ,
effective_date,
start_date,
business_group_id,
-- Added for bug 10399514
report_type,
report_qualifier,
report_category
-- Added for bug 10399514
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
Purpose : This returns the select statement that is
used to create the range rows for the
Provincial YE Amendment Pre-Process.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE eoy_range_cursor(
p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ld_end_date DATE;
'select distinct asg.person_id person_id
from per_all_assignments_f asg
where person_id = ' || to_char(ln_person_id) ||
' and :p_payroll_action_id > 0';
'select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf
where assignment_set_id = ' || to_char(ln_asg_set) || '
and asgset.assignment_id = paf.assignment_id
and asgset.include_or_exclude = ''I''
and :payroll_action_id > 0
order by paf.person_id';
'select distinct asg.person_id person_id
from per_all_assignments_f asg
where person_id = 0
and :p_payroll_action_id > 0
order by asg.person_id ';
hr_utility.trace('No person is selected as ln_person_id and ln_asg_set are null');
CURSOR c_selected_asg_set(cp_start_person in number
,cp_end_person in number
,cp_asg_set in number) is
select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where
(pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) = to_char(ln_pre_org_id)
OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
ppa.legislative_parameters) = to_char(ln_pre_org_id))
AND asgset.assignment_set_id = cp_asg_set
and asgset.include_or_exclude = 'I'
and paf.assignment_id = asgset.assignment_id
and paf.person_id between cp_start_person
and cp_end_person
and ppa.report_type IN ('RL2', 'CAEOY_RL2_AMEND_PP')
and to_char(ppa.effective_date,'YYYY') = lv_year
and ppa.business_group_id+0 = ln_business_group_id
-- and pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
-- ppa.legislative_parameters) = to_char(ln_pre_org_id)
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paf.person_id = to_number(paa.serial_number);
CURSOR c_selected_asg_set_range(cp_start_person in number
,cp_end_person in number
,cp_asg_set in number) is
select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
where
(pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) = to_char(ln_pre_org_id)
OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
ppa.legislative_parameters) = to_char(ln_pre_org_id))
AND asgset.assignment_set_id = cp_asg_set
and asgset.include_or_exclude = 'I'
and paf.assignment_id = asgset.assignment_id
-- and paf.person_id between cp_start_person and cp_end_person
AND ppr.payroll_action_id = p_payroll_action_id
AND ppr.chunk_number = p_chunk
AND ppr.person_id = paf.person_id
and ppa.report_type IN ('RL2', 'CAEOY_RL2_AMEND_PP')
and to_char(ppa.effective_date,'YYYY') = lv_year
and ppa.business_group_id+0 = ln_business_group_id
-- and pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
-- ppa.legislative_parameters) = to_char(ln_pre_org_id)
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paf.person_id = to_number(paa.serial_number);
select ppa.report_type,
paa.assignment_id,
-- Added for bug 10399514
report_qualifier,
report_category,
-- Added for bug 10399514
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where (pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) = to_char(ln_pre_org_id)
OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
ppa.legislative_parameters) = to_char(ln_pre_org_id))
AND to_number(paa.serial_number) = cp_person_id
-- and pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
-- ppa.legislative_parameters) = to_char(cp_pre_org_id)
and paa.action_status = 'C'
and ppa.business_group_id+0 = ln_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type IN ('RL2', 'CAEOY_RL2_AMEND_PP')
order by paa.assignment_action_id desc;
select substr(full_name,1,31), substr(national_identifier,1,11)
from per_all_people_f
where person_id = cp_person_id
ORDER BY effective_end_date desc;
select pay_assignment_actions_s.nextval
into ln_rl2amend_asg_action
from dual;
/* Insert into pay_assignment_actions. */
-- hr_utility.trace('creating asg. action');
/* Update the serial number column with the person id
so that the RL2 Amendment report will not have
to do an additional checking against the assignment
table */
-- hr_utility.trace('updating asg. action');
update pay_assignment_actions aa
set aa.serial_number = to_char(p_person_id)
where aa.assignment_action_id = ln_rl2amend_asg_action;
select report_format
into lv_report_format
from pay_report_format_mappings_f
where report_type = lv_report_type
and report_qualifier = lv_state
and report_category = lv_report_cat ;
hr_utility.trace('opening c_selected_asg_set_range CURSOR');
open c_selected_asg_set_range (p_start_person_id
,p_end_person_id
,ln_asg_set);
hr_utility.trace('opening c_selected_asg_set');
open c_selected_asg_set (p_start_person_id
,p_end_person_id
,ln_asg_set);
hr_utility.trace('fetching from c_selected_asg_set_range CURSOR');
fetch c_selected_asg_set_range into ln_person_id_sel ;
if c_selected_asg_set_range%notfound then
hr_utility.trace('No Person found for reporting in this chunk');
hr_utility.trace('fetching from c_selected_asg_set CURSOR');
fetch c_selected_asg_set into ln_person_id_sel ;
if c_selected_asg_set%notfound then
hr_utility.trace('No Person found for reporting in this chunk');
hr_utility.trace('ln_person_id after c_selected_asg_set = '||to_char(ln_person_id_sel));
hr_utility.trace('closing close c_selected_asg_set_range CURSOR');
close c_selected_asg_set_range;
hr_utility.trace('closing c_selected_asg_set');
close c_selected_asg_set;