[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-SEP-2003 SSattini 115.1 Removed for update clause
from sort_action procedure.
17-SEP-2003 SSattini 115.2 Corrected the variables used
to substitute token values in
messages.
20-OCT-2003 SSattini 115.3 Added p_report_type parameter
to get_payroll_action_info
procedure, to check whether
report is Register or Paper.
01-SEP-2004 SSouresr 115.4 Made changes to the package to
support multi assignments
and also removed unnecessary
joins to per_assignments_f
27-SEP-2004 SSouresr 115.5 Corrected action_creation for
scenario with assignment set but
no GRE
22-NOV-2004 SSouresr 115.6 Replaced tables with views and also
added an exists clause to the main
cursors in action_creation
07-MAR-2005 SSouresr 115.7 Removed extra payroll_action_id from
the range cursor
29-MAY-2005 SSouresr 115.8 Updating the gre just before calling
action_creation when no gre parameter
has been given. This removes duplicates
15-JUN-2005 SSouresr 115.9 Replaced views with tables in sort_action
as this was causing Assertion failure
21-JUN-2005 SSouresr 115.10 Security Profile changes to c_all_gres
16-OCT-2006 meshah 115.11 5528944 Added order by to the range cursor
queries.
04-NOV-2010 emunisek 115.12 4676544 Made changes to Lock the Federal Yearend
Amendment Preprocess when T4 Amendment PDF
report was run.
15-NOV-2010 emunisek 115.13 4676544 Made changes such that an Employee with Negative
Balances gets opted out of T4 Amendment PDF and a
warning message is given in log file accordingly.
23-NOV-2010 emunisek 115.14 4676544 Modified cursor get_reprint_t4amendpdf_dtls such
that Reprint only happens when there is no latest
Amendment Archive Run latest to that.Also changed
the messages to Informative instead of Fatal for Negative
Balances, Absence of Archive Runs cases.
23-NOV-2010 emunisek 115.15 4676544 Modified the error message for Reprint Mode as the Core Package
has less size for message
25-NOV-2010 emunisek 115.16 4676544 Modified the cursors get_reprint_t4amendpdf_dtls and
get_latest_t4amendpdf_dtls such that Employees moved across
GREs will have outputs for each GRE.
02-DEC-2010 emunisek 115.17 4676544 Modified action_creation to remove lv_flag_count check in
LATEST and REPRINT modes of the report as the changes made to
get_reprint_t4amendpdf_dtls and get_latest_t4amendpdf_dtls
cursors are already taking care of the requirement and it
is preventing the expected outputs in some scenarios.
08-DEC-2010 emunisek 115.18 10381064 Modified cursors get_latest_t4amendpdf_dtls,
get_reprint_t4amendpdf_dtls to introduce the Date
Check on pay_payroll_actions table used in exists
clauses
07-SEP-2011 rgottipa 115.19 10399514 introduced new cursores
c_selected_asg_set_range and
c_all_asg_range. These will be
called if RANGE_PERSON_ID is enabled.
02-Jan-2013 rgottipa 115.20 15886428 Done changes to support print
terminate employees and online
'paper' option.
25-Jan-2013 rgottipa 115.22 16208287 'paper' option should not affect the
register report.
04-Feb-2013 rgottipa 115.23 16208287 Wrongly checking lv_report_type, hence
removed that check.
*****************************************************************************/
gv_package VARCHAR2(100) := 'pay_ca_eoy_t4_amend_reg';
select to_number(pay_ca_eoy_t4_amend_reg.get_parameter(
'GRE_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4_amend_reg.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4_amend_reg.get_parameter(
'ASG_SET_ID',ppa.legislative_parameters)),
pay_ca_eoy_t4_amend_reg.get_parameter(
'MODE',ppa.legislative_parameters),
effective_date,
start_date,
business_group_id,
pay_ca_eoy_t4_amend_reg.get_parameter(
'PROV_CD',ppa.legislative_parameters),
report_type
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
select to_number(pay_ca_eoy_t4_amend_reg.get_parameter(
'GRE_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4_amend_reg.get_parameter(
'PER_ID',ppa.legislative_parameters)),
to_number(pay_ca_eoy_t4_amend_reg.get_parameter(
'ASG_SET_ID',ppa.legislative_parameters)),
pay_ca_eoy_t4_amend_reg.get_parameter(
'MODE',ppa.legislative_parameters),
effective_date,
start_date,
business_group_id,
pay_ca_eoy_t4_amend_reg.get_parameter(
'PROV_CD',ppa.legislative_parameters),
report_type,
report_qualifier,
report_category,
pay_ca_eoy_t4_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
T4 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_T4_AMEND_PP''
and paa_arch.tax_unit_id = '|| ln_tax_unit_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_T4_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,
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 = 'CAEOY_T4_AMEND_PP'
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
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
,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 ppa.business_group_id = ln_business_group_id
and ppa.report_type = 'CAEOY_T4_AMEND_PP'
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
and paf.person_id = to_number(paa.serial_number)
and ppr.payroll_action_id = p_payroll_action_id
and ppr.chunk_number = p_chunk
and ppr.person_id = to_number(paa.serial_number);
/* Cursor c_all_gres to select T4 Amendment 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 = 'T4/RL1'
AND exists ( SELECT 1
FROM pay_payroll_actions ppa ,
pay_assignment_actions paa
WHERE ppa.report_type = 'CAEOY_T4_AMEND_PP'
AND ppa.report_qualifier = 'CAEOY_T4_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 paa.tax_unit_id = 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_T4_AMEND_PP'
AND ppa_arch.report_qualifier = 'CAEOY_T4_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 paa_arch.tax_unit_id = nvl(cp_gre,paa_arch.tax_unit_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_T4_AMEND_PP'
AND ppa_arch.report_qualifier = 'CAEOY_T4_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 paa_arch.tax_unit_id = nvl(cp_gre,paa_arch.tax_unit_id)
--and to_number(paa_arch.serial_number) 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 = to_number(paa_arch.serial_number);
/*For Bug 4676544 updated the cursor get_latest_t4amend_dtls to make sure T4 Amendment Paper Report
is not affected by the T4 Amendment PDF report*/
CURSOR get_latest_t4amend_dtls (cp_person_id in number
,cp_tax_unit_id in number
,cp_effective_date in date) is
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
paa.tax_unit_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.serial_number = to_char(cp_person_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 = 'CAEOY_T4_AMEND_PP'
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 paa1.payroll_action_id = ppa1.payroll_action_id
and ppa1.report_type = ppa.report_type)
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'))
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
paa.tax_unit_id,
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 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 = 'CAEOY_T4_AMEND_PP'
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 ('PAYCAT4AMPDF','CAEOY_T4_AMEND_PP')
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 passt.tax_unit_id = paa.tax_unit_id
AND (pail.locked_action_id = paa.assignment_action_id
OR paa.assignment_action_id < passt.assignment_action_id))
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'))
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,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
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.serial_number = to_char(cp_person_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 = 'CAEOY_T4_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'))
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_id,
paa.assignment_action_id,
paa.tax_unit_id,
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 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 = 'CAEOY_T4_AMEND_PP'
and exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type = 'PAYCAT4AMPDF'
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.tax_unit_id = paa.tax_unit_id
AND passt.assignment_id=paf.assignment_id
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_T4_AMEND_PP'
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND paa2.assignment_action_id > paa.assignment_action_id
AND paa2.tax_unit_id = paa.tax_unit_id
AND ppa2.effective_date = cp_effective_date /*Added for 10381064*/
AND paa2.assignment_id=paa.assignment_id
)
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'))
group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,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,
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 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 = 'CAEOY_T4_AMEND_PP'
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 ('PAYCAT4AMPDF','CAEOY_T4_AMEND_PP')
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 passt.tax_unit_id = paa.tax_unit_id
AND (pail.locked_action_id = paa.assignment_action_id
OR paa.assignment_action_id < passt.assignment_action_id))
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 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,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,
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 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 = 'CAEOY_T4_AMEND_PP'
and exists
( SELECT pail.locked_action_id
FROM pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
WHERE pact.report_type = 'PAYCAT4AMPDF'
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.tax_unit_id = paa.tax_unit_id
AND passt.assignment_id=paf.assignment_id
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_T4_AMEND_PP'
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND paa2.assignment_action_id > paa.assignment_action_id
AND paa2.tax_unit_id = paa.tax_unit_id
AND ppa2.effective_date = cp_effective_date /*Added for 10381064*/
AND paa2.assignment_id=paa.assignment_id
)
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 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,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 = 'T4'
and report_qualifier = 'CAEOY'
and action_type = 'X'
and action_status = 'C'
and effective_date = cp_year
and pay_ca_eoy_t4_amend_reg.get_parameter('TRANSFER_GRE',
legislative_parameters)
= to_char(cp_gre);
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_t4amend_flag
AND fai2.archive_item_id = faic.archive_item_id
AND faic.context = nvl(rtrim(cp_province), faic.context)
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;
select pay_assignment_actions_s.nextval
into ln_t4amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_t4amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with Province_code,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports. This
** logic works for both T4 Amendment Register and T4 Register
** 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_t4amend_reg_asg_action;
select pay_assignment_actions_s.nextval
into ln_t4amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_t4amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with Province_code,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports. This
** logic works for both T4 Amendment Register and T4 Register
** 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_t4amend_reg_asg_action;
select pay_assignment_actions_s.nextval
into ln_t4amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_t4amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with Province_code,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports. This
** logic works for both T4 Amendment Register and T4 Register
** 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_t4amend_reg_asg_action;
select pay_assignment_actions_s.nextval
into ln_t4amend_reg_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_t4amend_reg_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with Province_code,
** Archiver assignment_action and Archiver Payroll_action_id
** so that we need not refer back in the reports. This
** logic works for both T4 Amendment Register and T4 Register
** 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_t4amend_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');
hr_utility.trace('fetching from c_selected_asg_set_range CURSOR');
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 ');
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_t4_amend_reg.get_parameter('GRE_ID',ppa.legislative_parameters),
pay_ca_eoy_t4_amend_reg.get_parameter('PER_ID',ppa.legislative_parameters),
pay_ca_eoy_t4_amend_reg.get_parameter('ASG_SET_ID',ppa.legislative_parameters),
pay_ca_eoy_t4_amend_reg.get_parameter('P_S1',ppa.legislative_parameters),
pay_ca_eoy_t4_amend_reg.get_parameter('P_S2',ppa.legislative_parameters),
pay_ca_eoy_t4_amend_reg.get_parameter('P_S3',ppa.legislative_parameters),
pay_ca_eoy_t4_amend_reg.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),
pay_ca_eoy_t4_amend_reg.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';