[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
the provincial parameter has been selected
22-NOV-2004 ssouresr 115.29 Added exists clauses to main cursors returning
assignments to report
28-NOV-2004 ssouresr 115.30 Changed 'Quebec Bn' to 'Quebec Identification Number'
29-APR-2005 ssouresr 115.31 The Year End Exception Report now picks up T4A
Amendment data too. Also made changes so that box
names with negative balances are correctly displayed
15-JUN-2005 ssouresr 115.32 Replaced hr_organization_units with hr_all_organization_units
this allows correct output to be produced when a
a secure user runs the report
30-AUG-2005 ssattini 115.33 2689672 Modified prov_employer_validation,provincial_process
prov_employee_validation and print_employee to print YEER
report for RL2 PRE.
31-AUG-2005 ssattini 115.34 3977930 Modified provincial_process,federal_process to add sort
by last_name,first_name,middle_names.
04-OCT-2005 ssouresr 115.35 Modified archive data cursors to reduce their cost
08-NOV-2005 ssouresr 115.36 Commented out Youth Hire Program Indicator
check
09-NOV-2005 ssouresr 115.37 Added checks for fields that are mandatory
for year end magnetic media
22-DEC-2005 ssouresr 115.38 The exception report will now also detect negative
T4A and RL1 non box footnotes.
31-JUL-2006 ydevi 115.39 all monetary values are converted into number by using
fnd_number.number_to_canonical function instead of to_number
function
The masking of the monetory values has been done using
pay_us_employee_payslip_web.get_format_value instead of
to_char.
01-Aug-2006 ssmukher 115.40 Implementation of PPIP tax in the package.Also the
use of diff EI rates (For Quebec and Non Quebec Employees).
Modified the following procedures
1) fed_employee_validation, 2) prov_employee_validation,
3) print_employee.
04-Sep-2006 ssmukher 115.41 Removed the reference of PPIP earnings from Federal
processes.Modifiwed the print_employee procedure to remove
all references of PPIP for Federal option.Also added a cursor
get_jurisdiction_code in federal_process to fetch the jurisdiction
for the employee based on which the EI_Rate will be applicable.
15-Sep-2006 ssmukher 115.42 5531874 Modified the cursor get_jurisdiction_code to use
CAEOY_PROVINCE_OF_EMPLOYMENT instead of CAEOY_EMPLOYMENT_PROVINCE.
Also modified the l_info_value variable size to NUMBER(12,3) in
legi_info function.Also modified the sv_ppip_rate and sv_ei_ppip_rate
variable size to NUMBER(12,3).
21-Sep-2006 ssmukher 115.43 5531874 Modified the print_employee.
29-NOV-2006 meshah 115.44 5552744 Modified initialize_static_var,
print_employee and
fed_employee_validation to distinguish
between EI for Fed and QC.
30-NOV-2006 meshah 115.45 5552744 missed backslash for nbsp.
08-DEC-2006 meshah 115.46 5703506 modified the procedure federal_process.
Added DISTINCT to cursor cur_asg_act.
03-Jan-2007 ssmukher 115.47 5723058 Overloaded the function legi_info.
Also modified the procedure
pier_yeer to fetch the value for
EI_RATE using the new overloaded
legi_info function.
24-Sep-2007 amigarg 115.48 6443068 Increased the variable size of sv_employee_name to 300
28-Sep-2007 amigarg 115.49 6443068 put the substr in sv_Employee_name
09-sep-2010 rgottipa 115.51 10085168 modified T4 as _T4 in cur_dbi cusor in
federal_process procedure
09-Nov-2010 rgottipa 115.52 10261964 alignment of negative balance is changed from
right to left.
18-Nov-2010 rgottipa 115.54 10261964 l_emp_last_name and l_emp_first_name
substr each one to 30 characters.
02-Dec-2011 pracagra 115.55 13421306 Added new variables sv_qpp_max_exempt,
sv_qpp_rate and sv_qpp_ded_required.
Added formula for sv_qpp_ded_required and
sv_qpp_max_exempt.
20-Jan-2012 pracagra 115.56 13612413 Modified the variable definition of
'sv_cpp_rate', 'sv_qpp_rate',
'sv_ei_rate','sv_ppip_rate' and
'sv_ppip_ei_rate' to number(12,4)
13-Sep-2012 sgotlasw 115.57 13542043 Added code display employee details on the
report output who have Cpp Election Date or
Cpp Revocation Date.
16-SEP-2012 sgotlasw 115.8 6773615 Added 'validateSIN' function to validate
SIN of the employee. Now proper validation is
performed on employee SIN and employee having
invalid SIN will get displayed on the report
output.
18-SEP-2012 sgotlasw 115.59 5942058 Code is modified to display complete employer
address including address line 3 on the reoprt
output.
18-SEP-2012 sgotlasw 115.60 4142751 Validation is performed on the length of the employee
address lines 1,2 and 3. Employee having length
of address line 1 more than 30 characters or length
of address lines 2 and 3 together more than 30
characters is displayed on the report output.
20-SEP-2012 sgotlasw 115.61 10163858 Fixed code to avoid displaying multiple headers
in the output.
20-SEP-2012 sgotlasw 115.62 10316246 Modified cursor 'cur_asg_act' to avoid picking same
employee twice due to the multiple records in
per_all_people_f table.
20-SEP-2012 sgotlasw 115.63 Replaced '!=' with '<>' to resolve GSCC errors.
24-SEP-2012 sbachu 115.64 6773615 The message "Invalid Social Insurance Number" should
not be shown if employee has not been assigned any SIN.
13542043 Modified cursor cur_cpp_date_block to avoid multiple
CPP Election Date or CPP Revocation Date messages
24-SEP-2012 sbachu 115.65 10316246 Modified cursor 'cur_rl_asg_act' to avoid picking same
employee twice due to the multiple records in
per_all_people_f table.
26-SEP-2012 sbachu 115.66 13542043 Modifed cursor cur_cpp_date_block so that it does
does not fetch the future year dates.
27-SEP-2012 sgotlasw 115.67 4142751 Added Address line length validation when report
is run for RL2 PRE.
02-NOV-2012 sgotlasw 115.69 13542043 Included sv_print :=1
4393047 Headings for negative balances on report output
have been corrected.
06-NOV-2012 sgotlasw 115.70 Corrected compilation error due to missing
'END CASE'
06-NOV-2012 sgotlasw 115.71 4344652 Added Parms for pier_yeer.
29-NOV-2012 sgotlasw 115.72 15908332 Added code to display Further Information Codes
holding negative values.
29-NOV-2012 sgotlasw 115.73 15908452 Fixed formatting issue while displaying
negative balances.
29-NOV-2012 sgotlasw 115.74 15908390 Display format of the headings of negative box
balances is fixed.
30-NOV-2012 sgotlasw 115.75 6837510 Added validation for the employee location. If
assignment do not have employee location, then
employee will get displayed in Year End Exception
Report.
*/
/************************************************************
** Local Package Variables ( Static Variables )
************************************************************/
gv_title VARCHAR2(100) := ' Year End Exception report ';
sv_dbi.delete;
sv_col.delete;
sv_msg.delete;
sv_neg_bal.delete;
sv_cpp_date.delete;
sv_dbi.delete;
sv_col.delete;
sv_msg.delete;
sv_cpp_date.delete;
select count( distinct lkp.meaning )
into l_multi_jd
from PER_ALL_ASSIGNMENTS_F paf,
HR_LOCATIONS_ALL hrl,
HR_LOOKUPS lkp
where paf.person_id = p_person_id
and sv_reporting_year between
to_char(paf.effective_start_date,'YYYY') and
to_char(paf.effective_end_date,'YYYY')
and paf.location_id = hrl.location_id
and lkp.lookup_code = hrl.region_1
and lkp.lookup_type = 'CA_PROVINCE';
select replace(replace(replace(replace(tl.balance_name,'T4A'),
'T4'), 'RL1' ), '_' )
into l_bal_name
from pay_balance_types bal, pay_balance_types_tl tl
where upper(bal.balance_name) = upper(cp_bal_name)
and tl.balance_type_id = bal.balance_type_id
and tl.language = userenv('LANG');
select information_value
into l_info_value
from pay_ca_legislation_info
where information_type = p_info_type
and jurisdiction_code is NULL
and sv_reporting_year between to_char(start_date,'YYYY')
and to_char(end_date,'YYYY');
select information_value
into l_info_value
from pay_ca_legislation_info
where information_type = p_info_type
and jurisdiction_code = p_jurisdiction
and sv_reporting_year between to_char(start_date,'YYYY')
and to_char(end_date,'YYYY');
sv_neg_bal.delete;
l_sort_neg.delete;
sv_msg.delete;
sv_col.delete;
sv_neg_bal.delete;
select context into sv_emp_jurisdiction
from ff_archive_item_contexts
where archive_item_id = sv_dbi(i).archive_item_id
and context_id = sv_context_id;
select context
into sv_emp_jurisdiction
from ff_archive_item_contexts
where archive_item_id = sv_dbi(i).archive_item_id
and context_id = sv_context_id;
sv_msg.delete;
sv_col.delete;
sv_neg_bal.delete;
/* The procedure provincial_process is executed when user has selected option
Provincial. This procedure is called from the main procedure pier_yeer.*/
PROCEDURE provincial_process ( fp_pre in number, fp_b_g_id in number) is
/* The cursor cur_rl_pay_act retrieves archived payroll_action_id(PACTID).
If Prov Reporting Establishment(PRE) is selected, this cursor selects
PACTID for that PRE otherwise it selects all PACTID for all archived
PRE */
-- Need to modify the cursor cur_rl_pay_act to enable RL2 PRE (Modified)
cursor cur_rl_pay_act is
select ppa.payroll_action_id ,
hoi.org_information1 business_number,
hou.organization_id,
hou.name,
ppa.payroll_id,
ppa.effective_date,
ppa.report_type,
hoi.org_information2
from hr_organization_information hoi,
hr_all_organization_units hou,
pay_payroll_actions ppa
where hou.business_group_id = fp_b_g_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'Prov Reporting Est'
and ppa.business_group_id = fp_b_g_id
and hoi.organization_id =
pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters)
and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters) =
nvl(to_char(fp_pre),pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters))
and ppa.report_type in ('RL1', 'CAEOY_RL1_AMEND_PP','RL2')
and ppa.action_status = 'C'
and to_char(ppa.effective_date,'YYYY') = sv_reporting_year
and to_char(ppa.effective_date,'DD-MM') = '31-12'
order by hou.organization_id, ppa.payroll_action_id;
select 'Y'
from hr_organization_information
where organization_id = cp_org_id
and org_information2 = cp_qin
and org_information3 = 'Y'
and org_information_context = 'Prov Reporting Est';
select distinct rtrim(ltrim(fdi.user_name)),
rtrim(ltrim(fai.value)),
initcap(rtrim(ltrim(replace(replace(replace(replace(replace(replace(replace(
fdi.user_name,'CAEOY'),'RL1_'),'PER_YTD'),'PER_JD_YTD'),'AMOUNT'),'EMPLOYEE_'),
'_',' ')))) req_col /* bug 4393047 - Added AMOUNT */
from ff_database_items fdi
,ff_archive_items fai
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = to_char(cp_context)
and fdi.user_name like 'CAEOY%'
order by req_col; -- Bug 15908452
select count(regular_payment_date)
from per_time_periods target
where payroll_id = cp_payroll_id
and to_char( target.regular_payment_date,'YYYY' ) = sv_reporting_year;
select paa.assignment_action_id,
paa.assignment_id,
paa.serial_number person_id,
paa.action_status
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_people_f ppf
where paa.payroll_action_id = cp_pactid
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = fp_b_g_id
and ppa.effective_date between ppf.effective_start_date /*10316246 sbachu*/
and ppf.effective_end_date
and not exists
(select 1
from pay_assignment_actions paa_amend,
pay_payroll_actions ppa_amend
where paa_amend.payroll_action_id > cp_pactid
and paa.serial_number = paa_amend.serial_number
and ppa_amend.payroll_action_id = paa_amend.payroll_action_id
and ppa_amend.report_type = 'CAEOY_RL1_AMEND_PP'
and ppa_amend.business_group_id = fp_b_g_id
and ppa_amend.action_status = 'C'
and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters) =
pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa_amend.legislative_parameters)
and to_char(ppa_amend.effective_date,'YYYY') = sv_reporting_year
and to_char(ppa_amend.effective_date,'YYYY') = to_char(ppa.effective_date,'YYYY')
and to_char(ppa_amend.effective_date,'DD-MM') = '31-12'
and to_char(ppa_amend.effective_date,'DD-MM') = to_char(ppa.effective_date,'DD-MM'))
and exists
(select 1
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= trunc(ppa.effective_date,'Y')
)
and ppf.person_id = paa.serial_number
and ppf.effective_start_date <= ppa.effective_date
and ppf.effective_end_date >= trunc(ppa.effective_date,'Y')
order by ppf.last_name,ppf.first_name,ppf.middle_names;
select ppf.date_of_birth,
ppf.original_date_of_hire
from per_all_people_f ppf
where ppf.person_id = cp_person_id
and cp_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select actual_termination_date
from per_periods_of_service
where person_id = cp_person_id
and actual_termination_date is not null;
select qpp_exempt_flag
from per_all_assignments_f paaf,
pay_ca_emp_prov_tax_info_f pcefti
where paaf.person_id = cp_person_id
and to_char(cp_effective_date,'YYYY') between
to_char(paaf.effective_start_date,'YYYY') and
to_char(paaf.effective_end_date, 'YYYY' )
and pcefti.assignment_id = paaf.assignment_id
and pcefti.business_group_id+0 = cp_bg_id
and to_char(cp_effective_date,'YYYY') between
to_char(pcefti.effective_start_date,'YYYY') and
to_char(pcefti.effective_end_date,'YYYY')
and pcefti.qpp_exempt_flag = 'Y';
select ppip_exempt_flag
from per_all_assignments_f paaf,
pay_ca_emp_prov_tax_info_f pcefti
where paaf.person_id = cp_person_id
and to_char(cp_effective_date,'YYYY') between
to_char(paaf.effective_start_date,'YYYY') and
to_char(paaf.effective_end_date, 'YYYY' )
and pcefti.assignment_id = paaf.assignment_id
and pcefti.business_group_id+0 = cp_bg_id
and to_char(cp_effective_date,'YYYY') between
to_char(pcefti.effective_start_date,'YYYY') and
to_char(pcefti.effective_end_date,'YYYY')
and pcefti.ppip_exempt_flag = 'Y';
select nvl(hsck.segment1, hsck.segment11)
from per_all_assignments_f paf,
hr_soft_coding_keyflex hsck
where paf.assignment_id = cp_asg_id
and add_months(trunc(to_date(sv_reporting_year,'YYYY'),'Y'),12)-1 between
paf.effective_start_date and paf.effective_end_date
and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
select * from PAY_CA_EOY_RL2_TRANS_INFO_V
where business_group_id = cp_bg_id
and payroll_action_id = cp_pact_id;
select * from PAY_CA_EOY_RL2_EMPLOYEE_INFO_V
where business_group_id = cp_bg_id
and assignment_action_id = cp_asgact_id;
select pai.action_information5,
flv.meaning,
'CAEOY_RL1_NONBOX_FOOTNOTE'
from pay_action_information pai,
fnd_lookup_types flt,
fnd_lookup_values flv
where pai.action_context_id = cp_asgact_id
and pai.action_context_type = 'AAP'
and pai.jurisdiction_code = 'QC'
and pai.action_information_category = 'CA FOOTNOTES'
and pai.action_information6 = 'RL1'
and flt.lookup_type = 'PAY_CA_RL1_NONBOX_FOOTNOTES'
and flv.lookup_type = flt.lookup_type
and flv.language = userenv('LANG')
and flv.enabled_flag = 'Y'
and flv.lookup_code = pai.action_information4;
select paf.LOCATION_ID
from pay_assignment_actions paa,
per_assignments_f paf
where paa.assignment_action_id = cp_asgact_id
and paf.assignment_id = paa.assignment_id;
select pai_emp.ACTION_INFORMATION4 code, pai_emp.ACTION_INFORMATION5 value,
initcap(rtrim(ltrim(replace(replace(replace(pai_emp.ACTION_INFORMATION4,'RL2_'),'AMOUNT_'),'_',' ')))) short_name
FROM pay_action_information pai_emp
,pay_assignment_actions paa_arch
,pay_payroll_actions ppa_arch
where pai_emp.action_context_id=cp_asgact_id
AND pai_emp.action_context_id = paa_arch.assignment_action_id
AND pai_emp.action_information_category = 'CAEOY RL2 FURTHER INFO'
AND pai_emp.assignment_id = paa_arch.assignment_id
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = 'C'
AND ppa_arch.report_type IN ('RL2','CAEOY_RL2_AMEND_PP')
AND ppa_arch.report_qualifier IN ('CAEOYRL2','CAEOY_RL2_AMEND_PPQ')
AND ppa_arch.report_category = 'ARCHIVE'
AND ppa_arch.action_type = 'X'
AND ppa_arch.action_status = 'C'
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id;
/* The procedure federal_process is executed when user has selected option
Federal. This procedure is called from the main procedure pier_yeer.*/
PROCEDURE federal_process ( fp_gre in number, fp_b_g_id in number) is
/* The cursor cur_pay_act retrieves archived payroll_action_id(PACTID).
If GRE is selected, this cursor selects PACTID for that GRE otherwise
it selects all PACTID for all archived GRE */
cursor cur_pay_act is
select ppa.payroll_action_id ,
hoi.org_information1 business_number,
hou.organization_id,
hou.name,
ppa.payroll_id,
ppa.effective_date,
ppa.report_type
from hr_organization_information hoi,
hr_all_organization_units hou,
pay_payroll_actions ppa
where hou.business_group_id = fp_b_g_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'Canada Employer Identification'
and ppa.business_group_id = fp_b_g_id
and hoi.organization_id = pycadar_pkg.get_parameter('TRANSFER_GRE',
ppa.legislative_parameters )
and ( ( hoi.organization_id = fp_gre ) OR
( fp_gre is null and hoi.organization_id = hoi.organization_id ))
and ( ( ppa.report_type in ('T4', 'CAEOY_T4_AMEND_PP', 'T4A', 'CAEOY_T4A_AMEND_PP' ) and sv_p_y = 'E' ) or
( ppa.report_type in ('T4', 'CAEOY_T4_AMEND_PP') and sv_p_y = 'P' ) )
and ppa.action_status = 'C'
and to_char(ppa.effective_date,'YYYY') = sv_reporting_year
and to_char(ppa.effective_date,'DD-MM') = '31-12'
order by hou.organization_id, ppa.payroll_action_id;
select 'Y'
from hr_organization_information
where organization_id = cp_org_id
and org_information1 = 'Y'
and org_information_context = 'Fed Magnetic Reporting';
select distinct rtrim(ltrim(fdi.user_name)),
rtrim(ltrim(fai.value)),
initcap(rtrim(ltrim(replace(replace(replace(replace(replace(
replace(replace( fdi.user_name,'CAEOY'),'T4A'),'_T4'),'PER_GRE_YTD')
,'PER_JD_GRE_YTD'),'EMPLOYEE_'),'_',' ')))) req_col,
fai.archive_item_id
from ff_database_items fdi
,ff_archive_items fai
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = to_char(cp_context)
and fdi.user_name like 'CAEOY%'
order by req_col; -- order by added by rgottipa by 10085168
select rtrim(ltrim(fai.value))
from ff_database_items fdi
,ff_archive_items fai
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = to_char(cp_context)
and fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
select count(regular_payment_date)
from per_time_periods target
where payroll_id = cp_payroll_id
and to_char( target.regular_payment_date,'YYYY' ) = sv_reporting_year;
person_name in the select and other columns in select in the order by.
The is because of the date join on the table per_people_f. If there
are date track records in that table there will be multiple records */
cursor cur_asg_act (cp_pactid in number) is
select DISTINCT
paa.assignment_action_id,
paa.assignment_id,
paa.serial_number person_id,
paa.action_status,
ppf.last_name,ppf.first_name,ppf.middle_names
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_people_f ppf
where paa.payroll_action_id = cp_pactid
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = fp_b_g_id
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and not exists
(select 1
from pay_assignment_actions paa_amend,
pay_payroll_actions ppa_amend
where paa_amend.payroll_action_id > cp_pactid
and paa.serial_number = paa_amend.serial_number
and ppa_amend.payroll_action_id = paa_amend.payroll_action_id
and ppa_amend.report_type in ('CAEOY_T4_AMEND_PP','CAEOY_T4A_AMEND_PP')
and ppa_amend.business_group_id = fp_b_g_id
and ppa_amend.action_status = 'C'
and pycadar_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters) =
pycadar_pkg.get_parameter('TRANSFER_GRE',ppa_amend.legislative_parameters)
and to_char(ppa_amend.effective_date,'YYYY') = sv_reporting_year
and to_char(ppa_amend.effective_date,'YYYY') = to_char(ppa.effective_date,'YYYY')
and to_char(ppa_amend.effective_date,'DD-MM') = '31-12'
and to_char(ppa_amend.effective_date,'DD-MM') = to_char(ppa.effective_date,'DD-MM'))
and exists
(select 1
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= trunc(ppa.effective_date,'Y')
)
and ppf.person_id = paa.serial_number
and ppf.effective_start_date <= ppa.effective_date
and ppf.effective_end_date >= trunc(ppa.effective_date,'Y')
order by ppf.last_name,ppf.first_name,ppf.middle_names,
paa.assignment_action_id,
paa.assignment_id,
paa.serial_number,
paa.action_status;
select ppf.date_of_birth,
ppf.original_date_of_hire
from per_all_people_f ppf
where ppf.person_id = cp_person_id
and cp_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select actual_termination_date
from per_periods_of_service
where person_id = cp_person_id
and actual_termination_date is not null;
select cpp_qpp_exempt_flag
from per_all_assignments_f paaf, pay_ca_emp_fed_tax_info_f pcefti
where paaf.person_id = cp_person_id
and to_char(cp_effective_date,'YYYY') between
to_char(paaf.effective_start_date,'YYYY') and
to_char(paaf.effective_end_date, 'YYYY' )
and pcefti.assignment_id = paaf.assignment_id
and pcefti.business_group_id+0 = cp_bg_id
and to_char(cp_effective_date,'YYYY') between
to_char(pcefti.effective_start_date,'YYYY') and
to_char(pcefti.effective_end_date,'YYYY')
and pcefti.cpp_qpp_exempt_flag = 'Y';
select distinct pcefti.CPP_ELECTION_DATE, /*Bug 13542043 sbachu*/
pcefti.CPP_REVOCATION_DATE
from per_all_assignments_f paaf, pay_ca_emp_fed_tax_info_f pcefti
where paaf.person_id = cp_person_id
and to_char(cp_effective_date,'YYYY') between
to_char(paaf.effective_start_date,'YYYY') and
to_char(paaf.effective_end_date, 'YYYY' )
and pcefti.assignment_id = paaf.assignment_id
and pcefti.business_group_id+0 = cp_bg_id
and to_char(cp_effective_date,'YYYY') between
to_char(pcefti.effective_start_date,'YYYY') and
to_char(pcefti.effective_end_date,'YYYY')
and ( ( /*Bug 13542043 sbachu*/
pcefti.CPP_ELECTION_DATE is not null
and to_number(to_char(pcefti.CPP_ELECTION_DATE,'YYYY')) <= to_number(to_char(cp_effective_date,'YYYY'))
) or
(
pcefti.CPP_REVOCATION_DATE is not null
and to_number(to_char(pcefti.CPP_REVOCATION_DATE,'YYYY')) <= to_number(to_char(cp_effective_date,'YYYY'))
)
);
select ei_exempt_flag
from per_all_assignments_f paaf, pay_ca_emp_fed_tax_info_f pcefti
where paaf.person_id = cp_person_id
and to_char(cp_effective_date,'YYYY') between
to_char(paaf.effective_start_date,'YYYY') and
to_char(paaf.effective_end_date, 'YYYY' )
and pcefti.assignment_id = paaf.assignment_id
and pcefti.business_group_id+0 = cp_bg_id
and to_char(cp_effective_date,'YYYY') between
to_char(pcefti.effective_start_date,'YYYY') and
to_char(pcefti.effective_end_date,'YYYY')
and pcefti.ei_exempt_flag = 'Y';
select pai.action_information5,
flv.meaning,
'CAEOY_T4A_NONBOX_FOOTNOTE'
from pay_action_information pai,
fnd_lookup_types flt,
fnd_lookup_values flv
where pai.action_context_id = cp_asgact_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'CA FOOTNOTES'
and pai.action_information6 = 'T4A'
and flt.lookup_type = 'PAY_CA_T4A_NONBOX_FOOTNOTES'
and flv.lookup_type = flt.lookup_type
and flv.language = userenv('LANG')
and flv.enabled_flag = 'Y'
and flv.lookup_code = pai.action_information4;
select flv.lookup_code,
flv.meaning,
flv.description
from fnd_lookup_types flt,
fnd_lookup_values flv
where flt.lookup_type = 'PAY_CA_EOY_EXCEPTIONS'
and flv.lookup_type = flt.lookup_type
and flv.language = userenv('LANG');
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') into sv_date from dual;
/* Select all CPP and EI information */
sv_cpp_max_earn := legi_info('MAX_CPP_EARNINGS');
select name, org_information1
into sv_gre_name, sv_busi_no
from hr_organization_information hoi,
hr_all_organization_units hou
where hoi.organization_id = hou.organization_id
and hoi.organization_id = p_gre
and hoi.org_information_context = 'Canada Employer Identification'
and hou.business_group_id = p_b_g_id;
select hou.name,
hoi.org_information2
into sv_pre_name,
sv_qin
from hr_organization_information hoi,
hr_all_organization_units hou
where hoi.organization_id = hou.organization_id
and hoi.organization_id = p_pre
and hoi.org_information1 = 'QC'
and hoi.org_information_context = 'Prov Reporting Est'
and hou.business_group_id = p_b_g_id;
/* Select context id for Jurisdiction and is used for T4 Neg. Bal. */
select context_id
into sv_context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE';