[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
07-Jan-2011 nkjaladi 115.3 10359119 Modified cursor c_selected_asg_set
in procedure action_creation
to remove the check for person_id
29-Aug-2011 sneelapa 115.4 10399514 Introduced new CURSOR
c_selected_asg_set_range it will
be called in place of
c_selected_asg_set CURSOR,
if RANGE_PERSON_ID is enabled.
31-Dec-2012 rgottipa 115.5 15886428 Done changes to support print
terminate employees and Self Service
'paper' option.
*****************************************************************************/
gv_package VARCHAR2(100) := 'pay_ca_eoy_t4a_cancel_pkg';
select to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'GRE_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'ASG_SET_ID',ppa.legislative_parameters)),
pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'MODE',ppa.legislative_parameters),
effective_date,
start_date,
business_group_id,
report_type
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
select to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'GRE_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'ASG_SET_ID',ppa.legislative_parameters)),
pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'MODE',ppa.legislative_parameters),
effective_date,
start_date,
business_group_id,
report_type,
report_qualifier,
report_category,
pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'PRINT_TERM',ppa.legislative_parameters)
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
Purpose : This returns the select statement that is
used to created the range rows for the
T4A Cancel PDF.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE 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_assignments_f asg
where person_id = ' || ln_person_id ||
' and :payroll_action_id > 0';
'select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_assignments_f paf
where assignment_set_id = ' || 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 paf.person_id
from pay_payroll_actions ppa_rep,
pay_assignment_actions paa_rep,
per_assignments_f paf,
pay_payroll_actions ppa
where paa_rep.assignment_id = paf.assignment_id
and ppa.payroll_action_id = :payroll_action_id
and ppa_rep.business_group_id = ppa.business_group_id
and ppa_rep.effective_date = ppa.effective_date
and ppa_rep.report_type in (''PAYCAT4APDF'',''PAYCAT4AAMPDF'')
and paa_rep.tax_unit_id = '|| ln_tax_unit_id ||'
and paa_rep.action_status = ''C''
and ppa_rep.payroll_action_id = paa_rep.payroll_action_id
order by paf.person_id ';
'select distinct paf.person_id
from pay_payroll_actions ppa_rep,
pay_assignment_actions paa_rep,
per_assignments_f paf,
pay_payroll_actions ppa
where paa_rep.assignment_id = paf.assignment_id
and ppa.payroll_action_id = :payroll_action_id
and ppa_rep.business_group_id = ppa.business_group_id
and ppa_rep.effective_date = ppa.effective_date
and ppa_rep.report_type in (''PAYCAT4APDF'',''PAYCAT4AAMPDF'')
and paa_rep.action_status = ''C''
and ppa_rep.payroll_action_id = paa_rep.payroll_action_id
order by paf.person_id ';
CURSOR c_selected_asg_set(cp_start_person in number
,cp_end_person in number
,cp_asg_set in number
,cp_effective_date in date) is
select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa
where 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.business_group_id = ln_business_group_id
and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = nvl(ln_tax_unit_id, paa.tax_unit_id)
and paa.action_status = 'C'
and ppa.effective_date = cp_effective_date;
CURSOR c_selected_asg_set_range(cp_start_person in number
,cp_end_person in number
,cp_asg_set in number
,cp_effective_date in date) is
select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_population_ranges ppr
where 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.business_group_id = ln_business_group_id
and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = nvl(ln_tax_unit_id, paa.tax_unit_id)
and paa.action_status = 'C'
and ppa.effective_date = cp_effective_date;
/* Cursor c_all_gres to select T4 Cancel GRE based on Business Group
and effective date */
CURSOR c_all_gres(cp_bg_id number,
cp_eff_date date) IS
select hou.organization_id
from hr_organization_information hoi,
hr_all_organization_units hou
where hou.business_group_id = cp_bg_id
AND hou.organization_id = hoi.organization_id
AND hou.date_from <= cp_eff_date
AND nvl(hou.date_to,fnd_date.canonical_to_date('4712/12/31 00:00:00'))
>= cp_eff_date
AND hoi.org_information_context = 'Canada Employer Identification'
AND hoi.org_information5 like 'T4A%'
AND exists ( SELECT 1
FROM pay_payroll_actions ppa ,
pay_assignment_actions paa
WHERE ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
AND ppa.report_qualifier = 'DEFAULT'
AND ppa.business_group_id = cp_bg_id
AND ppa.effective_date = cp_eff_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.tax_unit_id = hou.organization_id);
select distinct paf.person_id
from pay_payroll_actions ppa_rep,
pay_assignment_actions paa_rep,
per_assignments_f paf
where ppa_rep.business_group_id = cp_bg_id
and ppa_rep.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa_rep.report_qualifier = 'DEFAULT'
and ppa_rep.effective_date = cp_eff_date
and ppa_rep.action_status = 'C'
and paa_rep.payroll_action_id = ppa_rep.payroll_action_id
and paa_rep.action_status = 'C'
and paa_rep.assignment_id = paf.assignment_id
and paa_rep.tax_unit_id = nvl(cp_gre,paa_rep.tax_unit_id)
and to_number(paf.person_id) between
cp_start_person and cp_end_person;
select distinct paf.person_id
from pay_payroll_actions ppa_rep,
pay_assignment_actions paa_rep,
per_assignments_f paf,
pay_population_ranges ppr
where ppa_rep.business_group_id = cp_bg_id
and ppa_rep.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa_rep.report_qualifier = 'DEFAULT'
and ppa_rep.effective_date = cp_eff_date
and ppa_rep.action_status = 'C'
and paa_rep.payroll_action_id = ppa_rep.payroll_action_id
and paa_rep.action_status = 'C'
and paa_rep.assignment_id = paf.assignment_id
and paa_rep.tax_unit_id = nvl(cp_gre,paa_rep.tax_unit_id)
-- and to_number(paf.person_id) between
-- cp_start_person and cp_end_person;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
paa.tax_unit_id,
paa.serial_number,
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where paf.person_id = to_char(cp_person_id)
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
and ppa.business_group_id = ln_business_group_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and decode(ppa.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'MODE',ppa.legislative_parameters)) = 'LATEST'
and paf.effective_end_date = (select max(paf.effective_end_date)
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
and not exists (select 1
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa1.assignment_action_id
and ppa1.effective_date = ppa.effective_date
and paa1.tax_unit_id = paa.tax_unit_id
and paa1.payroll_action_id = ppa1.payroll_action_id
and ppa1.report_type = 'PAYCAT4ACLPDF')
and not exists (select 1
from pay_assignment_actions paa2,
pay_payroll_actions ppa2
where paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa2.effective_date = ppa.effective_date
and paa2.tax_unit_id = paa.tax_unit_id
and paa2.assignment_id = paa.assignment_id
and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
> substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
)
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
paa.tax_unit_id,
paa.serial_number,
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where paf.person_id = to_char(cp_person_id)
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
and ppa.business_group_id = ln_business_group_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and paf.effective_end_date = (select max(paf.effective_end_date)
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
and exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions ppa1,
pay_assignment_actions paa1
WHERE ppa1.report_type = 'PAYCAT4ACLPDF'
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.effective_date = cp_effective_date
AND paa1.assignment_action_id = pail.locking_action_id
AND paa1.tax_unit_id = paa.tax_unit_id
AND paa1.assignment_id=paf.assignment_id
AND pail.locked_action_id = paa.assignment_action_id)
AND not exists (select 1
from pay_assignment_actions paa2,
pay_payroll_actions ppa2
where paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa2.effective_date = ppa.effective_date
and paa2.tax_unit_id = paa.tax_unit_id
and paa2.assignment_id = paa.assignment_id
and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
> substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
)
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
paa.tax_unit_id,
paa.serial_number,
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
per_periods_of_service pds
where paf.person_id = to_char(cp_person_id)
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
and ppa.business_group_id = ln_business_group_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and decode(ppa.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
pay_ca_eoy_t4a_cancel_pkg.get_parameter(
'MODE',ppa.legislative_parameters)) = 'LATEST'
and paf.effective_end_date = (select max(paf.effective_end_date)
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
and not exists (select 1
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa1.assignment_action_id
and ppa1.effective_date = ppa.effective_date
and paa1.tax_unit_id = paa.tax_unit_id
and paa1.payroll_action_id = ppa1.payroll_action_id
and ppa1.report_type = 'PAYCAT4ACLPDF')
and not exists (select 1
from pay_assignment_actions paa2,
pay_payroll_actions ppa2
where paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa2.effective_date = ppa.effective_date
and paa2.tax_unit_id = paa.tax_unit_id
and paa2.assignment_id = paa.assignment_id
and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
> substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
)
and pds.actual_termination_date is not null
and pds.period_of_service_id = paf.period_of_service_id
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
paa.tax_unit_id,
paa.serial_number,
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
per_periods_of_service pds
where paf.person_id = to_char(cp_person_id)
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
and ppa.business_group_id = ln_business_group_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and paf.effective_end_date = (select max(paf.effective_end_date)
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
and exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions ppa1,
pay_assignment_actions paa1
WHERE ppa1.report_type = 'PAYCAT4ACLPDF'
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.effective_date = cp_effective_date
AND paa1.assignment_action_id = pail.locking_action_id
AND paa1.tax_unit_id = paa.tax_unit_id
AND paa1.assignment_id=paf.assignment_id
AND pail.locked_action_id = paa.assignment_action_id)
AND not exists (select 1
from pay_assignment_actions paa2,
pay_payroll_actions ppa2
where paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
and ppa2.effective_date = ppa.effective_date
and paa2.tax_unit_id = paa.tax_unit_id
and paa2.assignment_id = paa.assignment_id
and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
> substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
)
and pds.actual_termination_date is not null
and pds.period_of_service_id = paf.period_of_service_id
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
order by paa.assignment_action_id desc;
select substr(full_name,1,48), employee_number
from per_people_f
where person_id = cp_person_id
order by effective_end_date desc;
select 'Y'
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select pay_assignment_actions_s.nextval
into ln_t4acancel_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_t4acancel_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with Archiver assignment_action
** and Archiver Payroll_action_id so that we need not refer back
** in the reports.
******************************************************************/
ln_serial_number := lpad(to_char(ln_arch_act_to_lock),14,0)||
lpad(to_char(ln_rep_act_to_lock),14,0)||
lv_negative_bal_flag;
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_t4acancel_asg_action;
select pay_assignment_actions_s.nextval
into ln_t4acancel_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_t4acancel_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports.
***********************************************************/
ln_serial_number := lpad(to_char(ln_arch_act_to_lock),14,0)||
lpad(to_char(ln_rep_act_to_lock),14,0)||
lv_negative_bal_flag;
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_t4acancel_asg_action;
select report_format
into l_report_format
from pay_report_format_mappings_f
where report_type = lv_ppr_report_type
and report_qualifier = l_state
and report_category = l_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
,ld_end_date);
hr_utility.trace('opening c_selected_asg_set CURSOR');
OPEN c_selected_asg_set (p_start_person_id
,p_end_person_id
,ln_asg_set
,ld_end_date);
hr_utility.trace('fetching from c_selected_asg_set_range CURSOR');
fetch c_selected_asg_set_range
into ln_set_person_id;
exit when c_selected_asg_set_range%NOTFOUND;
hr_utility.trace('fetching from c_selected_asg_set CURSOR');
fetch c_selected_asg_set
into ln_set_person_id;
exit when c_selected_asg_set%NOTFOUND;
hr_utility.trace('closing c_selected_asg_set_range CURSOR');
close c_selected_asg_set_range;
hr_utility.trace('closing c_selected_asg_set CURSOR');
close c_selected_asg_set;
select pay_ca_eoy_t4a_cancel_pkg.get_parameter('GRE_ID',ppa.legislative_parameters),
pay_ca_eoy_t4a_cancel_pkg.get_parameter('PER_ID',ppa.legislative_parameters),
pay_ca_eoy_t4a_cancel_pkg.get_parameter('ASG_SET_ID',ppa.legislative_parameters),
pay_ca_eoy_t4a_cancel_pkg.get_parameter('P_S1',ppa.legislative_parameters),
pay_ca_eoy_t4a_cancel_pkg.get_parameter('P_S2',ppa.legislative_parameters),
pay_ca_eoy_t4a_cancel_pkg.get_parameter('P_S3',ppa.legislative_parameters),
pay_ca_eoy_t4a_cancel_pkg.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),
pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa.legislative_parameters),
ppa.effective_date,
ppa.start_date,
ppa.business_group_id
into l_gre_id,
l_per_id,
l_asg_set_id,
l_sort1,
l_sort2,
l_sort3,
l_dt, --session_date
l_print,
l_year_end,
l_year_start,
l_bg_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = payactid;
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 = :p_payroll_action_id
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';