[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
*/
/************************************************************
** 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_dbi.delete;
sv_col.delete;
sv_msg.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(
fdi.user_name,'CAEOY'),'RL1_'),'PER_YTD'),'PER_JD_YTD'),'EMPLOYEE_'),
'_',' ')))) req_col
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%';
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 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;
/* 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%';
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 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 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';