[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_selected_asg_set_range and
c_all_asg_range. These will be
called if RANGE_PERSON_ID is enabled.
06-SEP-2011 rgottipa 115.25 10399514 Used ln_pre_id in all_pres logic
to call cursor c_all_asg_range
31-Dec-2012 rgottipa 115.26 15886428 Done changes to support print
terminate employees and Self Service
'paper' option.
*****************************************************************************/
gv_package VARCHAR2(100) := 'pay_ca_eoy_rl1_amend_reg';
select to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
'ASG_SET_ID',ppa.legislative_parameters)),
pay_ca_eoy_rl1_amend_reg.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_rl1_amend_reg.get_parameter(
'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
'ASG_SET_ID',ppa.legislative_parameters)),
pay_ca_eoy_rl1_amend_reg.get_parameter(
'MODE',ppa.legislative_parameters),
effective_date,
start_date,
business_group_id,
report_type,
report_qualifier,
report_category,
pay_ca_eoy_rl1_amend_reg.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
RL1 Amendment PAPER.
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_arch,
pay_assignment_actions paa_arch,
per_assignments_f paf,
pay_payroll_actions ppa
where paa_arch.assignment_id = paf.assignment_id
and ppa.payroll_action_id = :payroll_action_id
and ppa_arch.business_group_id = ppa.business_group_id
and ppa_arch.effective_date = ppa.effective_date
and ppa_arch.report_type = ''CAEOY_RL1_AMEND_PP''
and pycadar_pkg.get_parameter(''PRE_ORGANIZATION_ID'',
ppa_arch.legislative_parameters) = '|| ln_pre_org_id ||'
and paa_arch.action_status = ''C''
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
order by paf.person_id ';
'select distinct paf.person_id
from pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
per_assignments_f paf,
pay_payroll_actions ppa
where paa_arch.assignment_id = paf.assignment_id
and ppa.payroll_action_id = :payroll_action_id
and ppa_arch.business_group_id = ppa.business_group_id
and ppa_arch.effective_date = ppa.effective_date
and ppa_arch.report_type = ''CAEOY_RL1_AMEND_PP''
and paa_arch.action_status = ''C''
and ppa_arch.payroll_action_id = paa_arch.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_arch,
pay_assignment_actions paa_arch
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_arch.business_group_id = ln_business_group_id
and ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa_arch.legislative_parameters) =
nvl(ln_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa_arch.legislative_parameters))
and paa_arch.action_status = 'C'
and ppa_arch.effective_date = cp_effective_date
and paf.person_id = to_number(paa_arch.serial_number);
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_arch,
pay_assignment_actions paa_arch,
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 ppa_arch.business_group_id = ln_business_group_id
and ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa_arch.legislative_parameters) =
nvl(ln_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa_arch.legislative_parameters))
and paa_arch.action_status = 'C'
and ppa_arch.effective_date = cp_effective_date
and paf.person_id = to_number(paa_arch.serial_number)
and ppr.payroll_action_id = p_payroll_action_id
and ppr.chunk_number = p_chunk
and ppr.person_id = to_number(paa_arch.serial_number);
/* Cursor c_all_pres to select RL1 Amendment PRE based on Business Group
and effective date */
CURSOR c_all_pres(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 = 'Prov Reporting Est'
AND hoi.org_information4 = 'P01'
AND exists ( SELECT 1
FROM pay_payroll_actions ppa ,
pay_assignment_actions paa
WHERE ppa.report_type = 'CAEOY_RL1_AMEND_PP'
AND ppa.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
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 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) = to_char(hou.organization_id));
select distinct paa_arch.serial_number
from pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch
where ppa_arch.business_group_id = cp_bg_id
and ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
AND ppa_arch.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
and ppa_arch.effective_date = cp_eff_date
and ppa_arch.action_status = 'C'
and paa_arch.payroll_action_id = ppa_arch.payroll_action_id
and paa_arch.action_status = 'C'
and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa_arch.legislative_parameters) = to_char(cp_pre_org_id)
and to_number(paa_arch.serial_number) between
cp_start_person and cp_end_person;
select distinct paa_arch.serial_number
from pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_population_ranges ppr
where ppa_arch.business_group_id = cp_bg_id
and ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
AND ppa_arch.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
and ppa_arch.effective_date = cp_eff_date
and ppa_arch.action_status = 'C'
and paa_arch.payroll_action_id = ppa_arch.payroll_action_id
and paa_arch.action_status = 'C'
and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa_arch.legislative_parameters) = to_char(cp_pre_org_id)
--and to_number(paa_arch.serial_number) between
--cp_start_person and cp_end_person;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters),
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
and paa.assignment_id = paf.assignment_id
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) =
nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters))
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.business_group_id = cp_business_group_id
and (ppa.report_type = 'CAEOY_RL1_AMEND_PP'or ppa.report_type = 'PYRL1PRAMEND') /*Added for Bug#5046006*/
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 pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type in (decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND'),'CAEOY_RL1_AMEND_PP')/*Added for Bug#5046006*/
AND pact.payroll_action_id = passt.payroll_action_id
AND pact.effective_date = cp_effective_date /*Added for 10381064*/
AND passt.assignment_action_id = pail.locking_action_id
AND passt.assignment_id=paf.assignment_id
AND nvl(passt.tax_unit_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',pact.legislative_parameters))
= pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND (pail.locked_action_id = paa.assignment_action_id
OR paa.assignment_action_id < passt.assignment_action_id))
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters), --Bug 9133270
paf.effective_end_date
order by paa.assignment_action_id desc; /*Need to have this to ensure the latest archive assignment_action_id is considered*/
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters)
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.serial_number = to_char(cp_person_id)
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) =
nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters))
and paa.action_status = 'C'
and ppa.business_group_id = cp_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type = 'CAEOY_RL1_AMEND_PP'
and exists (select 1
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'))
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) --Bug 9133270
order by paa.assignment_action_id desc;/*Need to have this to ensure the latest archive assignment_action_id is considered*/
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters),
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
and paa.assignment_id = paf.assignment_id
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) =
nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters))
and paa.action_status = 'C'
and ppa.business_group_id = cp_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type = 'CAEOY_RL1_AMEND_PP'
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 pact,
pay_assignment_actions passt
WHERE pact.report_type = decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND')
AND pact.payroll_action_id = passt.payroll_action_id
AND pact.effective_date = cp_effective_date /*Added for 10381064*/
AND passt.assignment_action_id = pail.locking_action_id
AND passt.assignment_id = paa.assignment_id
AND nvl(passt.tax_unit_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',pact.legislative_parameters))
= pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND (pail.locked_action_id = paa.assignment_action_id
OR paa.assignment_action_id < passt.assignment_action_id))
and ((not exists
( SELECT 1
FROM pay_payroll_actions ppa2,
pay_assignment_actions paa2
WHERE ppa2.report_type = 'CAEOY_RL1_AMEND_PP'
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.effective_date = cp_effective_date /*Added for 10381064*/
AND paa2.assignment_action_id > paa.assignment_action_id
AND pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa2.legislative_parameters)
= pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND paa2.assignment_id=paa.assignment_id
) and lv_ppr_report_type = 'PAYCARL1AMPDF') or lv_ppr_report_type <> 'PAYCARL1AMPDF')
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters), --Bug 9133270
paf.effective_end_date
order by paa.assignment_action_id desc;/*Need to have this to ensure the latest archive assignment_action_id is considered*/
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters),
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
per_periods_of_service pds
where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
and paa.assignment_id = paf.assignment_id
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) =
nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters))
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.business_group_id = cp_business_group_id
and (ppa.report_type = 'CAEOY_RL1_AMEND_PP'or ppa.report_type = 'PYRL1PRAMEND') /*Added for Bug#5046006*/
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 pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type in (decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND'),'CAEOY_RL1_AMEND_PP')/*Added for Bug#5046006*/
AND pact.payroll_action_id = passt.payroll_action_id
AND pact.effective_date = cp_effective_date /*Added for 10381064*/
AND passt.assignment_action_id = pail.locking_action_id
AND passt.assignment_id=paf.assignment_id
AND nvl(passt.tax_unit_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',pact.legislative_parameters))
= pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND (pail.locked_action_id = paa.assignment_action_id
OR paa.assignment_action_id < passt.assignment_action_id))
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,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters), --Bug 9133270
paf.effective_end_date
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters),
paf.effective_end_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
per_periods_of_service pds
where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
and paa.assignment_id = paf.assignment_id
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) =
nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters))
and paa.action_status = 'C'
and ppa.business_group_id = cp_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type = 'CAEOY_RL1_AMEND_PP'
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 pact,
pay_assignment_actions passt
WHERE pact.report_type = decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND')
AND pact.payroll_action_id = passt.payroll_action_id
AND pact.effective_date = cp_effective_date /*Added for 10381064*/
AND passt.assignment_action_id = pail.locking_action_id
AND passt.assignment_id = paa.assignment_id
AND nvl(passt.tax_unit_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',pact.legislative_parameters))
= pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND (pail.locked_action_id = paa.assignment_action_id
OR paa.assignment_action_id < passt.assignment_action_id))
and ((not exists
( SELECT 1
FROM pay_payroll_actions ppa2,
pay_assignment_actions paa2
WHERE ppa2.report_type = 'CAEOY_RL1_AMEND_PP'
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.effective_date = cp_effective_date /*Added for 10381064*/
AND paa2.assignment_action_id > paa.assignment_action_id
AND pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa2.legislative_parameters)
= pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
AND paa2.assignment_id=paa.assignment_id
) and lv_ppr_report_type = 'PAYCARL1AMPDF') or lv_ppr_report_type <> 'PAYCARL1AMPDF')
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,
pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters), --Bug 9133270
paf.effective_end_date
order by paa.assignment_action_id desc;
select payroll_action_id
from pay_payroll_actions
where business_group_id = cp_bg_id
and report_type = 'RL1'
and report_qualifier = 'CAEOYRL1'
and action_type = 'X'
and action_status = 'C'
and effective_date = cp_year
and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters)
= to_char(cp_pre);
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 fai2.value,faic.context
from ff_archive_items fai2,
ff_archive_item_contexts faic,
ff_contexts fc
where fai2.context1 = cp_asg_act_id
AND fai2.user_entity_id = cp_uid_rl1amend_flag
AND fai2.archive_item_id = faic.archive_item_id
AND faic.context = 'QC'
AND faic.context_id = fc.context_id
AND fc.context_name = 'JURISDICTION_CODE';
select user_entity_id
from ff_database_items
where user_name = cp_user_name;
CURSOR c_paa_update_check (cp_locking_asg_act_id number) IS
select assignment_action_id from
pay_assignment_actions where
assignment_action_id = cp_locking_asg_act_id;
l_paa_update_check pay_assignment_actions.assignment_action_id%TYPE;
select pay_assignment_actions_s.nextval
into ln_rl1_amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_pre_org_id);
** Update the serial number column with Province_code QC,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports.
***********************************************************/
ln_serial_number := lv_prov_of_emp||
lpad(to_char(ln_asg_act_to_lock),14,0)||
lpad(to_char(ln_yepp_pact_id),14,0);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
open c_paa_update_check(ln_rl1_amend_reg_asg_action);
fetch c_paa_update_check into l_paa_update_check;
hr_utility.trace('l_update_check : '||l_paa_update_check);
if c_paa_update_check%FOUND then
if ln_pre_parameter is NULL then
ln_pre_org_id := '';
** Update the serial number column with Province_code QC,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports.
***********************************************************/
ln_serial_number := lv_prov_of_emp||
lpad(to_char(ln_asg_act_to_lock),14,0)||
lpad(to_char(ln_yepp_pact_id),14,0);
end if; -- c_paa_update_check%FOUND
close c_paa_update_check;
select pay_assignment_actions_s.nextval
into ln_rl1_amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_pre_org_id);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
select pay_assignment_actions_s.nextval
into ln_rl1_amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_pre_org_id);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
select pay_assignment_actions_s.nextval
into ln_rl1_amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_pre_org_id);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_rl1_amend_reg_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 ;
open c_selected_asg_set_range(p_start_person_id
,p_end_person_id
,ln_asg_set
,ld_end_date);
hr_utility.trace('Opened cusor c_selected_asg_set_range');
open c_selected_asg_set (p_start_person_id
,p_end_person_id
,ln_asg_set
,ld_end_date);
hr_utility.trace('Opened cusor c_selected_asg_set');
fetch c_selected_asg_set_range into ln_set_person_id;
if c_selected_asg_set_range%notfound then
hr_utility.trace('c_selected_asg_set_range not found ');
fetch c_selected_asg_set into ln_set_person_id;
if c_selected_asg_set%notfound then
hr_utility.trace('c_selected_asg_set not found ');
close c_selected_asg_set_range;
else close c_selected_asg_set;
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.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_rl1_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_rl1_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_rl1_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';