[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_YEER_PKG
Source
1 PACKAGE BODY pay_ca_yeer_pkg AS
2 /* $Header: pycayeer.pkb 120.24.12020000.23 2012/12/03 12:52:22 sgotlasw ship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_ca_yeer_pkg
22
23 Description : Package for the Elements Reports. The package
24 generated the output file in the specified user
25 format. The current formats supported are
26 - HTML
27 - CSV
28
29 Change List
30 -----------
31 Date Name Vers Bug No Description
32 ---- ---- ------ ------- -----------
33 28-NOV-2000 vpandya 115.0 Created.
34 06-MAR-2001 vpandya 115.1 CPP/EI Reason.
35 26-MAR-2001 vpandya 115.2 Omit QPP from federal, print
36 balance name for Neg.Bal.
37 27-MAR-2001 vpandya 115.3 Replaced CPP/QPP Exempt with
38 CPP/QPP Eligible Pay Periods.
39 30-MAR-2001 vpandya 115.4 Added Multiple Jurisdiction Reason
40 for Provincial and aligned code.
41 02-APR-2001 vpandya 115.5 Added CPP/QPP Basic Exemption.
42 04-APR-2001 vpandya 115.6 Removed CPP/QPP Basic Exemption,
43 should print from the archiver.
44 09-NOV-2001 vpandya 115.7 Added CPP/QPP Basic Exemption,
45 now we use DBI for that.
46 09-NOV-2001 vpandya 115.8 Changed cursor rl_tax_unit_id.
47 10-NOV-2001 vpandya 115.9 Added set veify off at top.
48 12-NOV-2001 vpandya 115.10 Added dbdrv line.
49 12-DEC-2001 mmukherj 115.12 Taken out to_number from employee
50 number, because employee number can
51 be alphanumeric
52 10-AUG-2002 vpandya 115.16 Modified cursor cur_lkup, added
53 condition language=userenv('LANG')
54 10-AUG-2002 vpandya 115.17 Added OSERROR command at top
55 15-DEC-2002 vpandya 115.18 Added nocopy with out parameter and
56 bug 2718862, pick employee for CPP,
57 QPP or EI deficiency only if it is
58 negative and more than a dollar.
59 20-DEC-2002 vpandya 115.19 Bug 2718862, new requirement.
60 Print exceptions Employee Hired in
61 this year, terminated in this year,
62 turned 18 and 70 in this year only
63 when there is a CPP/QPP/EI defici..
64 29-AUG-2003 irgonzal 115.20 2406070 Changed format of employee name:
65 <last>, <first>.
66 13-NOV-2003 ssouresr 115.21 Passing PRE Organization Id to pier_yeer
67 instead of quebec identification number
68 19-DEC-2003 ssouresr 115.22 Exception Report first looks at RL1
69 Amendment before it looks at the RL1
70 30-DEC-2003 ssouresr 115.23 Exception Report first looks at T4
71 Amendment before it looks at the T4
72 31-DEC-2003 ssouresr 115.25 The PIER report should not display employees
73 that have had a CPP/QPP or EI block in the
74 year but do not have a deficiency in their
75 CPP or EI deductions.
76 01-JUL-2004 schauhan 115.26 3352591 Added Employee number in print_employee when report
77 type is T4A.
78 19-NOV-2004 ssouresr 115.27 Overpayment for CPP/QPP/EI will not be reported
79 anymore. Deficiency will be set to 0 in these cases
80 Also Deficiencies will not be reported as negative
81 anymore
82 20-NOV-2004 ssouresr 115.28 QPP Exempt is now reported if it has been set and
83 the provincial parameter has been selected
84 22-NOV-2004 ssouresr 115.29 Added exists clauses to main cursors returning
85 assignments to report
86 28-NOV-2004 ssouresr 115.30 Changed 'Quebec Bn' to 'Quebec Identification Number'
87 29-APR-2005 ssouresr 115.31 The Year End Exception Report now picks up T4A
88 Amendment data too. Also made changes so that box
89 names with negative balances are correctly displayed
90 15-JUN-2005 ssouresr 115.32 Replaced hr_organization_units with hr_all_organization_units
91 this allows correct output to be produced when a
92 a secure user runs the report
93 30-AUG-2005 ssattini 115.33 2689672 Modified prov_employer_validation,provincial_process
94 prov_employee_validation and print_employee to print YEER
95 report for RL2 PRE.
96 31-AUG-2005 ssattini 115.34 3977930 Modified provincial_process,federal_process to add sort
97 by last_name,first_name,middle_names.
98 04-OCT-2005 ssouresr 115.35 Modified archive data cursors to reduce their cost
99 08-NOV-2005 ssouresr 115.36 Commented out Youth Hire Program Indicator
100 check
101 09-NOV-2005 ssouresr 115.37 Added checks for fields that are mandatory
102 for year end magnetic media
103 22-DEC-2005 ssouresr 115.38 The exception report will now also detect negative
104 T4A and RL1 non box footnotes.
105 31-JUL-2006 ydevi 115.39 all monetary values are converted into number by using
106 fnd_number.number_to_canonical function instead of to_number
107 function
108 The masking of the monetory values has been done using
109 pay_us_employee_payslip_web.get_format_value instead of
110 to_char.
111 01-Aug-2006 ssmukher 115.40 Implementation of PPIP tax in the package.Also the
112 use of diff EI rates (For Quebec and Non Quebec Employees).
113 Modified the following procedures
114 1) fed_employee_validation, 2) prov_employee_validation,
115 3) print_employee.
116 04-Sep-2006 ssmukher 115.41 Removed the reference of PPIP earnings from Federal
117 processes.Modifiwed the print_employee procedure to remove
118 all references of PPIP for Federal option.Also added a cursor
119 get_jurisdiction_code in federal_process to fetch the jurisdiction
120 for the employee based on which the EI_Rate will be applicable.
121 15-Sep-2006 ssmukher 115.42 5531874 Modified the cursor get_jurisdiction_code to use
122 CAEOY_PROVINCE_OF_EMPLOYMENT instead of CAEOY_EMPLOYMENT_PROVINCE.
123 Also modified the l_info_value variable size to NUMBER(12,3) in
124 legi_info function.Also modified the sv_ppip_rate and sv_ei_ppip_rate
125 variable size to NUMBER(12,3).
126 21-Sep-2006 ssmukher 115.43 5531874 Modified the print_employee.
127 29-NOV-2006 meshah 115.44 5552744 Modified initialize_static_var,
128 print_employee and
129 fed_employee_validation to distinguish
130 between EI for Fed and QC.
131 30-NOV-2006 meshah 115.45 5552744 missed backslash for nbsp.
132 08-DEC-2006 meshah 115.46 5703506 modified the procedure federal_process.
133 Added DISTINCT to cursor cur_asg_act.
134 03-Jan-2007 ssmukher 115.47 5723058 Overloaded the function legi_info.
135 Also modified the procedure
136 pier_yeer to fetch the value for
137 EI_RATE using the new overloaded
138 legi_info function.
139 24-Sep-2007 amigarg 115.48 6443068 Increased the variable size of sv_employee_name to 300
140 28-Sep-2007 amigarg 115.49 6443068 put the substr in sv_Employee_name
141
142 09-sep-2010 rgottipa 115.51 10085168 modified T4 as _T4 in cur_dbi cusor in
143 federal_process procedure
144 09-Nov-2010 rgottipa 115.52 10261964 alignment of negative balance is changed from
145 right to left.
146
147 18-Nov-2010 rgottipa 115.54 10261964 l_emp_last_name and l_emp_first_name
148 substr each one to 30 characters.
149
150 02-Dec-2011 pracagra 115.55 13421306 Added new variables sv_qpp_max_exempt,
151 sv_qpp_rate and sv_qpp_ded_required.
152 Added formula for sv_qpp_ded_required and
153 sv_qpp_max_exempt.
154 20-Jan-2012 pracagra 115.56 13612413 Modified the variable definition of
155 'sv_cpp_rate', 'sv_qpp_rate',
156 'sv_ei_rate','sv_ppip_rate' and
157 'sv_ppip_ei_rate' to number(12,4)
158 13-Sep-2012 sgotlasw 115.57 13542043 Added code display employee details on the
159 report output who have Cpp Election Date or
160 Cpp Revocation Date.
161 16-SEP-2012 sgotlasw 115.8 6773615 Added 'validateSIN' function to validate
162 SIN of the employee. Now proper validation is
163 performed on employee SIN and employee having
164 invalid SIN will get displayed on the report
165 output.
166 18-SEP-2012 sgotlasw 115.59 5942058 Code is modified to display complete employer
167 address including address line 3 on the reoprt
168 output.
169 18-SEP-2012 sgotlasw 115.60 4142751 Validation is performed on the length of the employee
170 address lines 1,2 and 3. Employee having length
171 of address line 1 more than 30 characters or length
172 of address lines 2 and 3 together more than 30
173 characters is displayed on the report output.
174 20-SEP-2012 sgotlasw 115.61 10163858 Fixed code to avoid displaying multiple headers
175 in the output.
176 20-SEP-2012 sgotlasw 115.62 10316246 Modified cursor 'cur_asg_act' to avoid picking same
177 employee twice due to the multiple records in
178 per_all_people_f table.
179 20-SEP-2012 sgotlasw 115.63 Replaced '!=' with '<>' to resolve GSCC errors.
180 24-SEP-2012 sbachu 115.64 6773615 The message "Invalid Social Insurance Number" should
181 not be shown if employee has not been assigned any SIN.
182 13542043 Modified cursor cur_cpp_date_block to avoid multiple
183 CPP Election Date or CPP Revocation Date messages
184 24-SEP-2012 sbachu 115.65 10316246 Modified cursor 'cur_rl_asg_act' to avoid picking same
185 employee twice due to the multiple records in
186 per_all_people_f table.
187 26-SEP-2012 sbachu 115.66 13542043 Modifed cursor cur_cpp_date_block so that it does
188 does not fetch the future year dates.
189 27-SEP-2012 sgotlasw 115.67 4142751 Added Address line length validation when report
190 is run for RL2 PRE.
191 02-NOV-2012 sgotlasw 115.69 13542043 Included sv_print :=1
192 4393047 Headings for negative balances on report output
193 have been corrected.
194 06-NOV-2012 sgotlasw 115.70 Corrected compilation error due to missing
195 'END CASE'
196 06-NOV-2012 sgotlasw 115.71 4344652 Added Parms for pier_yeer.
197 29-NOV-2012 sgotlasw 115.72 15908332 Added code to display Further Information Codes
198 holding negative values.
199 29-NOV-2012 sgotlasw 115.73 15908452 Fixed formatting issue while displaying
200 negative balances.
201 29-NOV-2012 sgotlasw 115.74 15908390 Display format of the headings of negative box
202 balances is fixed.
203 30-NOV-2012 sgotlasw 115.75 6837510 Added validation for the employee location. If
204 assignment do not have employee location, then
205 employee will get displayed in Year End Exception
206 Report.
207 */
208
209 /************************************************************
210 ** Local Package Variables ( Static Variables )
211 ************************************************************/
212 gv_title VARCHAR2(100) := ' Year End Exception report ';
213 gc_csv_delimiter VARCHAR2(1) := ',';
214 gc_csv_data_delimiter VARCHAR2(1) := '"';
215
216 gv_html_start_data VARCHAR2(5) := '<td>' ;
217 gv_html_end_data VARCHAR2(5) := '</td>' ;
218
219 gv_package_name VARCHAR2(50) := 'pay_ca_yeer_pkg';
220
221 sv_date varchar2(20) := ' ';
222 sv_page number(4) := 0;
223
224 sv_reporting_year varchar2(4) := ' '; /* Reporting Year */
225 sv_p_y varchar2(1) := ' '; /* PIER or Exception Flag */
226 sv_pier_yeer varchar2(80) := ' '; /* PIER or Exception Title */
227 sv_f_p varchar2(1) := ' '; /* Fed. or Prov. Flag */
228 sv_fed_prov varchar2(240) := ' '; /* Fed. or Prov. Full name */
229 sv_gre_name varchar2(255) := ' '; /* GRE Name */
230 sv_pre_name varchar2(255) := ' '; /* PRE Name */
231 sv_qin varchar2(16) := ' '; /* Quebec Id. Number */
232 sv_gre number(20) := 0; /* GRE - Tax Unit Id */
233 sv_pre number(20) := 0; /* PRE - Organization Id */
234 sv_b_g_id number(20) := 0; /* Business Group Id */
235 sv_print_line number(2) := 31; /* Lines per page */
236 sv_line number(4) := 0; /* Counter for lines */
237 sv_busi_no varchar2(80) := ' '; /* Business Number */
238 sv_trans_y_n char(1); /* Trans. GRE or not flag */
239 sv_report_type varchar2(30) := ' '; /* Archiver Report Type*/
240 sv_context_id number(9) := 0; /* Jurisdiction Context Id*/
241 sv_asg_id number(10) := 0; /* Assignment Id */
242
243 sv_lkup tab_dbi; /* PL/SQL Lookup Table for Reasons and Title */
244 sv_neg_bal tab_dbi; /* Database Items for Employee */
245 sv_dbi tab_dbi; /* Database Items for Employee */
246 sv_msg tab_mesg; /* Messages */
247 sv_col tab_col_name;/* Required Columns */
248 sv_m number(2); /* Message Counter */
249 sv_c number(2); /* Column Counter */
250 sv_nb number(2); /* Negative Balance Counter */
251
252 /* CPP/QPP and EI Variables */
253 /*For 13612413- modified the variable definition of 'sv_cpp_rate', 'sv_qpp_rate', 'sv_ei_rate' to number(12,4)*/
254 sv_cpp_max_earn number(12,2);
255 sv_ei_max_earn number(12,2);
256 sv_cpp_max_exempt number(12,2);
257 sv_qpp_max_exempt number(12,2); /* For Bug no. 13421306 */
258 sv_ei_max_exempt number(12,2);
259 sv_cpp_exempt number(12,2);
260 sv_cpp_rate number(12,4);
261 sv_qpp_rate number(12,4); /* For Bug no. 13421306 */
262 sv_ei_rate number(12,4);
263
264 /* Added by ssmukher for PPIP tax */
265 /*For 13612413- modified the variable definition of 'sv_ppip_rate', 'sv_ppip_ei_rate' to number(12,4)*/
266 sv_ppip_rate number(12,4);
267 sv_ppip_ei_rate number(12,4);
268 sv_ppip_max_earn number(12,2);
269 sv_ppip_max_exempt number(12,2);
270 sv_jurisdiction varchar2(5);
271
272 /* BUG 6837510 */
273 sv_location_id number(12);
274
275 /* Employer Static Variables */
276
277 sv_employer_name varchar2(240);
278 sv_employer_address_line1 varchar2(240);
279 sv_employer_address_line2 varchar2(240);
280 sv_employer_address_line3 varchar2(240); --5942058
281 sv_employer_city varchar2(240);
282 sv_employer_province varchar2(240);
283 sv_employer_postal_code varchar2(240);
284
285 /* Employee Static Variables */
286
287 sv_person_id varchar2(240);
288 sv_no_of_cpp_periods number(10);
289 sv_date_of_birth date;
290 sv_hire_date date;
291 sv_terminate_date date;
292 sv_total_earnings NUMBER;
293 sv_pensionable_earnings NUMBER;
294 sv_ded_reported_16 NUMBER;
295 sv_rl1_slip_no varchar2(240);
296 sv_insurable_earnings NUMBER;
297 sv_ded_reported_18 NUMBER;
298
299 /* bug 5552744 */
300 sv_qc_insurable_earnings NUMBER;
301 sv_qc_ded_reported_18 NUMBER;
302 sv_qc_ei_ded_required number(12,2);
303 sv_qc_ei_max_exempt number(12,2);
304 sv_qc_ei_deficiency NUMBER;
305
306
307 /* Added by ssmukher for PPIP tax */
308 sv_ppip_insurable_earnings NUMBER;
309 sv_ded_reported_ppip NUMBER;
310 sv_ppip_ded_required NUMBER;
311 sv_ppip_deficiency NUMBER;
312 sv_ppip_block varchar2(1);
313
314 sv_cpp_qpp_deficiency NUMBER;
315 sv_ei_deficiency NUMBER;
316 sv_employee_name varchar2(240);
317 sv_employee_sin varchar2(240);
318 sv_employee_no varchar2(240);
319 sv_cpp_block varchar2(1);
320 sv_ei_block varchar2(1);
321 sv_cpp_ded_required number(12,2);
322 sv_qpp_ded_required number(12,2); /*For Bug no. 13421306*/
323 sv_ei_ded_required number(12,2);
324 sv_print number(1);
325 sv_emp_jurisdiction varchar2(30);
326 sv_cpp_exempt_bal number(12,2) := 0.00;
327 sv_cpp_basic_exemption number(12,2) := 0.00;
328
329 /* Initialize static variables from different level
330 lv_type = E Employee Level, lv_type = R Employer level */
331
332 /* RL2 Employer and Employee records */
333 lr_rl2_transrec PAY_CA_EOY_RL2_TRANS_INFO_V%ROWTYPE;
334 lr_rl2_emprec PAY_CA_EOY_RL2_EMPLOYEE_INFO_V%ROWTYPE;
335
336 /* Bug 15908332 */
337 further_info_count number;
338 TYPE FURTHER_CODES IS VARRAY(80) OF VARCHAR2(50);
339 TYPE FURTHER_AMOUNTS IS VARRAY(80) OF VARCHAR2(50);
340 TYPE FURTHER_SHORT_NAMES IS VARRAY(80) OF VARCHAR2(50);
341 l_further_code FURTHER_CODES:=FURTHER_CODES('','','','','','','','','','','','','',
342 '','','','','','','');
343 l_further_amount FURTHER_AMOUNTS:=FURTHER_AMOUNTS('','','','','','','','','','','','','',
344 '','','','','','','');
345 l_further_short_name FURTHER_SHORT_NAMES:=FURTHER_SHORT_NAMES('','','','','','','','','','','','','',
346 '','','','','','','');
347 /* Bug 15908332 */
348 /* Bug 13542043 - Variable to hold Cpp Election Date and Cpp Revocation Date. */
349 sv_cpp_date tab_cpp_date;
350
351 procedure initialize_static_var ( lv_type in varchar2 ) is
352 begin
353 if lv_type = 'E' then
354 sv_dbi.delete;
355 sv_nb := 0;
356 sv_col.delete;
357 sv_c := 0;
358 sv_msg.delete;
359 sv_m := 0;
360 sv_neg_bal.delete;
361 sv_person_id := null;
362 -- sv_no_of_cpp_periods := null;
363 sv_date_of_birth := null;
364 sv_hire_date := null;
365 sv_terminate_date := null;
366 sv_total_earnings := 0;
367 sv_pensionable_earnings := 0;
368 sv_ded_reported_16 := 0;
369 sv_rl1_slip_no := null;
370 sv_insurable_earnings := 0;
371 sv_ded_reported_18 := 0;
372 sv_ded_reported_ppip := 0;
373 sv_ppip_insurable_earnings := 0;
374 sv_cpp_qpp_deficiency := 0;
375 sv_ei_deficiency := 0;
376 sv_ppip_deficiency := 0;
377 sv_employee_name := null;
378 sv_employee_sin := null;
379 sv_cpp_block := null;
380 sv_ei_block := null;
381 sv_ppip_block := null;
382 sv_cpp_ded_required := 0;
383 sv_qpp_ded_required := 0; /*For Bug no. 13421306*/
384 sv_ei_ded_required := 0;
385 sv_ppip_ded_required := 0;
386 sv_print := 0;
387 sv_cpp_exempt_bal := 0;
388 sv_cpp_basic_exemption := 0;
389
390 /* bug 5552744 */
391 sv_qc_insurable_earnings := 0;
392 sv_qc_ded_reported_18 := 0;
393 sv_qc_ei_deficiency := 0;
394 sv_qc_ei_ded_required := 0;
395 /* bug 5552744 */
396 /* bug 13702180 */
397 sv_cpp_date.delete;
398 /* BUG 6837510 */
399 sv_location_id := null;
400
401 elsif lv_type = 'R' then
402 sv_c := 0;
403 sv_m := 0;
404 sv_dbi.delete;
405 sv_col.delete;
406 sv_msg.delete;
407 sv_employer_name := null;
408 sv_employer_address_line1 := null;
409 sv_employer_address_line2 := null;
410 sv_employer_address_line3 := null; --5942058
411 sv_employer_city := null;
412 sv_employer_province := null;
413 sv_employer_postal_code := null;
414 /* bug 13702180 */
415 sv_cpp_date.delete;
416 end if;
417 end initialize_static_var;
418
419 /* The cursor Cur_multi_juris is used to verify whether an employee worked
420 in miltiple jurisdiction during the ewporting year */
421
422 function get_multi_jd ( p_person_id in number )
423 return number is
424 l_multi_jd number := 0;
425 begin
426
427 select count( distinct lkp.meaning )
428 into l_multi_jd
429 from PER_ALL_ASSIGNMENTS_F paf,
430 HR_LOCATIONS_ALL hrl,
431 HR_LOOKUPS lkp
432 where paf.person_id = p_person_id
433 and sv_reporting_year between
434 to_char(paf.effective_start_date,'YYYY') and
435 to_char(paf.effective_end_date,'YYYY')
436 and paf.location_id = hrl.location_id
437 and lkp.lookup_code = hrl.region_1
438 and lkp.lookup_type = 'CA_PROVINCE';
439
440 return(l_multi_jd);
441
442 end;
443
444 /*
445 The function get_bal_name is used to print balance name (for
446 boxes with negative balance).
447 */
448
449 function get_bal_name ( p_bal_name in varchar2 )
450 return varchar2 is
451 l_bal_name varchar2(240) := ' ';
452 cp_bal_name varchar2(240) := ' ';
453 begin
454 if instr(upper(p_bal_name),'BOX') > 0 then
455 cp_bal_name := sv_report_type || '_' || p_bal_name;
456 else
457 cp_bal_name := p_bal_name;
458 end if;
459
460 select replace(replace(replace(replace(tl.balance_name,'T4A'),
461 'T4'), 'RL1' ), '_' )
462 into l_bal_name
463 from pay_balance_types bal, pay_balance_types_tl tl
464 where upper(bal.balance_name) = upper(cp_bal_name)
465 and tl.balance_type_id = bal.balance_type_id
466 and tl.language = userenv('LANG');
467
468 return(l_bal_name);
469
470 exception
471 when others then
472 return(l_bal_name);
473 end get_bal_name;
474
475 /*
476 This legi_info function is returning the information value based on
477 information type (p_info_type) for the reporting year.
478 Type, CPP_MAXIMUM, CPP_RATE, CPP_EXEMPT, EI_MAXIMUM and EI_RATE
479 */
480
481 function legi_info ( p_info_type in varchar2 )
482 return number is
483 l_info_value number(12,3) := 0;
484 begin
485 select information_value
486 into l_info_value
487 from pay_ca_legislation_info
488 where information_type = p_info_type
489 and jurisdiction_code is NULL
490 and sv_reporting_year between to_char(start_date,'YYYY')
491 and to_char(end_date,'YYYY');
492
493 return(l_info_value);
494
495 exception
496 when others then
497 return(0.00);
498 end legi_info;
499
500 /*
501 This legi_info function is returning the information value based on
502 information type (p_info_type) and jurisdiction code for the
503 reporting year. Type EI_RATE
504 */
505 function legi_info ( p_info_type in varchar2,
506 p_jurisdiction in varchar2)
507 return number is
508 l_info_value number(12,3) := 0;
509 begin
510 select information_value
511 into l_info_value
512 from pay_ca_legislation_info
513 where information_type = p_info_type
514 and jurisdiction_code = p_jurisdiction
515 and sv_reporting_year between to_char(start_date,'YYYY')
516 and to_char(end_date,'YYYY');
517
518 return(l_info_value);
519
520 exception
521 when others then
522 return(0.00);
523 end legi_info;
524 /* The procedure format_data writes the value in file */
525
526 procedure format_data ( lv_format in varchar2 ) is
527 begin
528 --sv_line := sv_line + 1;
529 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_format);
530 end format_data;
531
532 /* The function get_lookup_meaning returns values of exceptions
533 and labels, this is used for translation purpose. */
534
535 FUNCTION get_lookup_meaning( fp_lookup_code in varchar2 )
536 RETURN VARCHAR2
537 IS
538 lv_meaning varchar2(240);
539 begin
540 for i in sv_lkup.first..sv_lkup.last
541 loop
542 if sv_lkup(i).dbi_name = fp_lookup_code then
543 lv_meaning := sv_lkup(i).dbi_value;
544 exit;
545 end if;
546 end loop;
547 hr_utility.set_location(fp_lookup_code||' '||lv_meaning, 1111 );
548 return(lv_meaning);
549 end get_lookup_meaning;
550
551 /* The function print_spaces returns spaces to print spaces in the file */
552
553 FUNCTION print_spaces( p_no_of_spaces in number )
554 RETURN VARCHAR2
555 IS
556 l_space varchar2(25) := ' ';
557 l_no_of_spaces varchar2(32000);
558 begin
559 for i in 1..p_no_of_spaces
560 loop
561 l_no_of_spaces := l_no_of_spaces || l_space;
562 end loop;
563 return(l_no_of_spaces);
564 end print_spaces;
565
566 /* The function new_page is used to print blank lines if the pagesize is
567 fixed with no. of lines e.g 31 lines per page.
568 set two variables 1. sv_print_line ( total no. of line per page )
569 2. sv_line ( add one to it when a line print to file )
570 */
571
572 FUNCTION new_page
573 RETURN VARCHAR2
574 IS
575 l_add_row varchar2(25) := '<tr><td> </td></tr>';
576 l_blank_rows varchar2(2000);
577 begin
578 for i in 1..sv_print_line - sv_line
579 loop
580 l_blank_rows := l_blank_rows || l_add_row;
581 end loop;
582 return(l_blank_rows);
583 end;
584
585 /* The function print_line forms a line with entered character
586 and width of line */
587
588 FUNCTION print_line
589 (p_print in varchar2
590 ,p_number in number
591 )
592 RETURN VARCHAR2
593 IS
594 l_line varchar2(200);
595 begin
596 for i in 1..p_number
597 loop
598 l_line := l_line || p_print;
599 end loop;
600 return(l_line);
601 end;
602
603 /* Bug 6773615 - This function validates SIN of the employee. */
604 FUNCTION validateSIN( national_identifier in varchar2 )
605 RETURN BOOLEAN AS
606 check_digit VARCHAR2(40);
607 num1 VARCHAR2(40);
608 num2 VARCHAR2(40);
609 num3 VARCHAR2(40);
610 num4 VARCHAR2(40);
611 num2_add VARCHAR2(40);
612 check_format VARCHAR2(40);
613 return_value BOOLEAN := true;
614
615 begin
616 -- hr_utility.trace_on(null,'VALIDATE_SIN');
617 check_format := hr_ni_chk_pkg.chk_nat_id_format(national_identifier,'DDD DDD DDD');
618 hr_utility.trace('check_format ----'||check_format);
619 if ( check_format <>'0' ) then
620 /* check digit is the last digit of SIN */
621 check_digit := substr(check_format,-1,1);
622 hr_utility.trace('check_digit ----'||check_digit);
623
624 /* Make a number from the second and each alternate number */
625 num1 := substr(check_format,2,1) || substr(check_format,5,1) ||
626 substr(check_format,7,1) || substr(check_format,10,1) ;
627 hr_utility.trace('num1 ----'||num1);
628
629 /* Multiply the number by 2 /*
630 /* NOTE : '0000' is appended to ensure that substr will not fail in the next step */
631 num2 := num1 * 2 ||'0000';
632 hr_utility.trace('num2 ----'||num2);
633
634 /* Cross add the digits in the sum */
635 num2_add := substr(num2,1,1) + substr(num2,2,1) + substr(num2,3,1)
636 + substr(num2,4,1) + substr(num2,5,1);
637 hr_utility.trace('num2_add ----'||num2_add);
638
639 /* Cross add intervening digits of SIN i.e digit1 + digit3 + ... */
640 num3 := substr(check_format,1,1) + substr(check_format,3,1) +
641 substr(check_format,6,1) + substr(check_format,9,1);
642 hr_utility.trace('num3 ----'||num3);
643
644 /* Total the above two numbers */
645 num4 := num2_add + num3;
646 hr_utility.trace('num4 ----'||num4);
647
648 /* If the resulting total is a multiple of 10, the check digit is 0;
649 otherwise check digit will be the difference of the total calculated and the
650 next highest number ending in zero */
651 if ( substr(num4,-1,1) = '0' ) then
652 if ( check_digit <> '0' ) then
653 return_value := false;
654 end if;
655 else
656 if ( check_digit <> (10 - substr(to_char(num4),-1,1))) then
657 return_value := false;
658 end if;
659 end if;
660 else
661 return_value := false;
662 end if;
663 -- hr_utility.trace_off();
664 return return_value;
665 end validateSIN;
666
667 /* The procedure employee_header prints the header of Employee Exceptions */
668
669 procedure employee_header is
670 lv_format varchar2(32000);
671 begin
672 lv_format := '<table border=0><tr><td></td><td><HEAD> <CENTER> <B>' ||
673 sv_pier_yeer || '</B></CENTER></HEAD></td>';
674 lv_format := lv_format || '<td align="left">' ||
675 get_lookup_meaning('L_DATE') || sv_date || '</td></tr>';
676 format_data(lv_format);
677
678 sv_page := sv_page + 1;
679 lv_format := '<tr><td></td>';
680 if ( sv_p_y = 'E' ) then
681 lv_format := lv_format || '<td align="center"><B>'||
682 get_lookup_meaning('L_EMPL_EXCEPTION')||
683 '</B></td></tr><tr></tr>';
684 else
685 lv_format := lv_format ||
686 '<td align="center"> </B></td></tr><tr></tr>';
687 end if;
688 format_data(lv_format);
689
690 lv_format := '<tr><td align="left"><B>';
691 if ( sv_f_p = 'F' ) then
692 lv_format := lv_format || get_lookup_meaning('L_GRE_NAME')||
693 sv_gre_name|| '</B></td><td align="left"><B>'||
694 get_lookup_meaning('L_BUSI_NO')||sv_busi_no;
695 else
696 lv_format := lv_format || get_lookup_meaning('L_PRE_NAME')||
697 sv_pre_name || '</B></td><td align="left"><B>'||
698 get_lookup_meaning('L_QCIDNO')||sv_qin;
699 end if;
700
701 lv_format := lv_format || '</B></td><td align="left"><B>' ||
702 get_lookup_meaning('L_REPORTING_YEAR')||sv_reporting_year||
703 '</B></td></tr>';
704 format_data(lv_format);
705
706 lv_format := '<tr><td>'|| print_line('-',50)|| '</td><td>'||
707 print_line('-',52)||
708 '</td><td>'|| print_line('-',50)|| '</td></tr>';
709 format_data(lv_format);
710
711 lv_format := '</table>';
712 format_data(lv_format);
713
714 lv_format := '<table border=0>';
715 format_data(lv_format);
716
717 sv_line := 8;
718
719 end employee_header;
720
721 /* The procedure employer_header prints the header of Employer Exceptions */
722
723 procedure employer_header is
724 lv_format varchar2(32000);
725 begin
726 lv_format := '<table border=0><tr><td></td><td><HEAD> <CENTER> <B>' ||
727 sv_pier_yeer || '</B></CENTER></HEAD></td>'||
728 '<td align="left">' || get_lookup_meaning('L_DATE')||
729 sv_date || '</td></tr>';
730 format_data(lv_format);
731
732 lv_format := '<tr><td></td><td align="center"><B>'||
733 get_lookup_meaning('L_EMPR_EXCEPTION')||'</B></td></tr>';
734 format_data(lv_format);
735
736 lv_format := '<tr><td>' || print_line('-',50)|| '</td><td>'||
737 print_line('-',52)|| '</td><td>'|| print_line('-',50)||
738 '</tr>'|| '</table>';
739 format_data(lv_format);
740
741 lv_format := '<table border=0><tr>'||
742 '<td align="left"><B>'||get_lookup_meaning('L_EMPR_NAME')||
743 '</B></td>';
744 format_data(lv_format);
745
746 if ( sv_f_p = 'F' ) then
747 lv_format := '<td align="left" colspan=2><B>' ||
748 get_lookup_meaning('L_BUSINESS_NO') || '</B></td>';
749 else
750 lv_format := '<td align="left" colspan=2><B>' ||
751 get_lookup_meaning('L_QCID_NUMBER') || '</B></td>';
752 end if;
753 format_data(lv_format);
754
755 lv_format := '<td align="left"><B>' ||
756 get_lookup_meaning('L_REPORTING_YR') || '</B></td>';
757 format_data(lv_format);
758
759 if sv_f_p = 'F' then
760 lv_format := '<td align="left"><B>' ||
761 get_lookup_meaning('L_TR_GRE') || '</B></td>';
762 else
763 lv_format := '<td align="left"><B>' || get_lookup_meaning('L_TR_PRE')
764 || '</B></td>';
765 end if;
766 lv_format := lv_format || '</tr>';
767 format_data(lv_format);
768
769 lv_format := '<tr><td>' || print_line('-',80)|| '</td><td>'||
770 print_line('-',20)|| '</td><td>'|| print_line('-',20)||
771 '</td><td>'|| print_line('-',10)||
772 '</td><td>'|| print_line('-',20)|| '</td></tr>';
773 format_data(lv_format);
774
775 sv_line := 5;
776
777 end employer_header;
778
779 /* The procedure print_employee is used to print Employee Exception data
780 for T4, T4A and RL1. */
781
782 procedure print_employee is
783 lv_format varchar2(32000);
784 lv_req_flds varchar2(32000);
785 l_sort_neg tab_dbi;
786 l_juris_cd varchar2(240);
787 l_juris_cd1 varchar2(240);
788 i number(3);
789 l number(3);
790 begin
791
792 if ((sv_report_type = 'T4A') or
793 (sv_report_type = 'CAEOY_T4A_AMEND_PP')) then
794
795 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_EMPL_NAME') -- Bug 3352591-Added Employee
796 ||'</B></td>'|| '<td align="left" colspan=3>'||sv_employee_name -- number when report type = T4A.
797 ||'</td><td align="right"><B>'||get_lookup_meaning('L_EMP_NO')
798 ||'</B></td><td align="right">'||sv_employee_no||'</td></tr>';
799 format_data(lv_format);
800
801 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_SIN')||
802 '</B></td>'|| '<td align="left">'||sv_employee_sin||'</td>'||
803 '<td align="right"><B>'||get_lookup_meaning('L_DOB')||
804 '</B></td>'|| '<td align="left">'||sv_date_of_birth||
805 '</td></tr>';
806 format_data(lv_format);
807
808 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_DATE_HIRE')
809 ||'</B></td>'|| '<td align="left">'||sv_hire_date||'</td>'||
810 '<td align="right"><B>'||
811 get_lookup_meaning('L_DATE_TERMINATION')||'</B></td>'||
812 '<td align="left">'||sv_terminate_date||'</td></tr>';
813 format_data(lv_format);
814
815 /*
816 elsif ( sv_m = 0 and
817 to_number(sv_cpp_qpp_deficiency,'999,999,990.00') = 0.00 and
818 to_number(sv_ei_deficiency,'999,999,990.00') = 0.00 ) then
819
820 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_EMPL_NAME')
821 ||'</B></td>'|| '<td align="left" colspan=3>'||sv_employee_name
822 ||'</td>' || '<td align="right"><B>'||
823 get_lookup_meaning('L_SIN')||'</B></td>'||
824 '<td align="left">'||sv_employee_sin||'</td></tr>';
825 format_data(lv_format);
826 */
827
828 -- RL2 Employee Print
829 elsif (sv_report_type = 'RL2') then
830
831 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_EMPL_NAME') -- Bug 3352591-Added Employee
832 ||'</B></td>'|| '<td align="left" colspan=3>'||sv_employee_name -- number when report type = T4A.
833 ||'</td><td align="right"><B>'||get_lookup_meaning('L_EMP_NO')
834 ||'</B></td><td align="right">'||sv_employee_no||'</td></tr>';
835 format_data(lv_format);
836
837 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_SIN')||
838 '</B></td>'|| '<td align="left">'||sv_employee_sin||'</td>'||
839 '<td align="right"><B>'||get_lookup_meaning('L_DOB')||
840 '</B></td>'|| '<td align="left">'||sv_date_of_birth||
841 '</td></tr>';
842 format_data(lv_format);
843
844 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_DATE_HIRE')
845 ||'</B></td>'|| '<td align="left">'||sv_hire_date||'</td>'||
846 '<td align="right"><B>'||
847 get_lookup_meaning('L_DATE_TERMINATION')||'</B></td>'||
848 '<td align="left">'||sv_terminate_date||'</td></tr>';
849 format_data(lv_format);
850
851 lv_format := '<tr><td align="right"><B>'||
852 get_lookup_meaning('L_RL2_SLIP_NO')||'</B></td>'||
853 '<td align="left">'||
854 sv_rl1_slip_no ||'</td>'||
855 '<td> </td>'|| '<td> </td></tr>';
856
857 format_data(lv_format);
858
859 else
860 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_EMPL_NAME')
861 ||'</B></td>'|| '<td align="left" colspan=3>'||sv_employee_name
862 ||'</td><td align="right"><B>'||get_lookup_meaning('L_EMP_NO')
863 ||'</B></td><td align="right">'||sv_employee_no||'</td></tr>';
864 format_data(lv_format);
865
866 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_SIN')||
867 '</B></td>'|| '<td align="left">'||sv_employee_sin||'</td>'||
868 '<td align="right"><B>'||get_lookup_meaning('L_TOT_EARN')||
869 '</B></td>'|| '<td align="right">'
870 ||pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_total_earnings)||'</td>'
871 || '<td align="right"> </td>'|| '<td> </td></tr>';
872 format_data(lv_format);
873
874 lv_format := '<tr><td align="right"><B>'||get_lookup_meaning('L_DOB')||
875 '</B></td>'|| '<td align="left">'||sv_date_of_birth||'</td>';
876 if sv_f_p = 'F' then
877 lv_format := lv_format ||
878 '<td align="right"><B>'||get_lookup_meaning('L_CPP_PENS_EARN')
879 ||'</B></td>'|| '<td align="right">'
880 ||pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_pensionable_earnings)
881 ||'</td>';
882
883 else
884 lv_format := lv_format ||
885 '<td align="right"><B>'||get_lookup_meaning('L_QPP_PENS_EARN')
886 ||'</B></td>'|| '<td align="right">'
887 ||pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_pensionable_earnings)||
888 '</td>';
889 end if;
890
891 if ( sv_f_p = 'F' ) then
892 lv_format := lv_format || '<td align="right"><B>'||
893 get_lookup_meaning('L_INS_EARN')||'</B></td>'||
894 '<td align="right">'||
895 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_insurable_earnings)||'</td></tr>';
896 else
897 lv_format := lv_format || '<td align="right"><B>'||
898 get_lookup_meaning('L_PPIP_INSEARN')||'</B></td>'||
899 '<td align="right">'||
900 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ppip_insurable_earnings)||'</td></tr>';
901 end if;
902 format_data(lv_format);
903
904 lv_format := '<tr><td align="right"><B>'||
905 get_lookup_meaning('L_DATE_HIRE')||'</B></td>'||
906 '<td align="left">'||sv_hire_date||'</td>';
907
908 if sv_f_p = 'F' then
909 lv_format := lv_format || '<td align="right"><B>'||
910 get_lookup_meaning('L_CPP_REPORTED')||'</B></td>'||
911 '<td align="right">'
912 ||pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ded_reported_16)||'</td>'||
913 '<td align="right"><B>'||get_lookup_meaning('L_EI_REPORTED')
914 ||'</B></td>'|| '<td align="right">'
915 ||pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ded_reported_18)||'</td></tr>';
916 else
917 lv_format := lv_format || '<td align="right"><B>'||
918 get_lookup_meaning('L_QPP_REPORTED')||'</B></td>'||
919 '<td align="right">'||
920 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ded_reported_16)||'</td>'||
921 '<td align="right"><B>'||get_lookup_meaning('L_PPIP_REPORTED')
922 ||'</B></td>'|| '<td align="right" >'
923 ||pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ded_reported_ppip)||
924 '</td></tr>';
925 end if;
926 format_data(lv_format);
927
928 lv_format := '<tr><td align="right"><B>'||
929 get_lookup_meaning('L_DATE_TERMINATION')||'</B></td>'||
930 '<td align="left">'||sv_terminate_date||'</td>';
931
932 if sv_f_p = 'F' then
933 lv_format := lv_format || '<td align="right"><B>'||
934 get_lookup_meaning('L_CPP_REQUIRED')||'</B></td>'||
935 '<td align="right">'||
936 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_ded_required)||'</td>'||
937 '<td align="right"><B>'||get_lookup_meaning('L_EI_REQUIRED')
938 ||'</B></td>'|| '<td align="right">'||
939 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ei_ded_required)||'</td></tr>';
940 else
941 lv_format := lv_format || '<td align="right"><B>'||
942 get_lookup_meaning('L_QPP_REQUIRED')||'</B></td>'||
943 '<td align="right">'||
944 /*For Bug no. 13421306- Changed sv_cpp_ded_required to sv_qpp_ded_required*/
945 /*pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_ded_required)||'</td>'||*/
946 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_qpp_ded_required)||'</td>'||
947 '<td align="right"><B>'||get_lookup_meaning('L_PPIP_REQUIRED')
948 ||'</B></td>'|| '<td align="right" >'||
949 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ppip_ded_required)||
950 '</td></tr>';
951 end if;
952 format_data(lv_format);
953
954 if sv_f_p = 'F' then
955 lv_format := '<tr><td align="right"><B>'||
956 ' ' ||'</B></td>'||
957 '<td align="right">'||
958 ' '||'</td>'||
959 '<td align="right"><B>'||
960 get_lookup_meaning('L_CPP_DEFICIENCY')||'</B></td>'||
961 '<td align="right">'||
962 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_qpp_deficiency)||'</td>'||
963 '<td align="right"><B>'||
964 get_lookup_meaning('L_EI_DEFICIENCY')||'</B></td>'||
965 '<td align="right">'||
966 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ei_deficiency)||'</td></tr>';
967 --'<td align="left">'||sv_no_of_cpp_periods||'</td>'||
968 else
969 lv_format := '<tr><td align="right"><B>'||
970 get_lookup_meaning('L_RL_SLIP_NO')||'</B></td>'||
971 '<td align="left">'||
972 sv_rl1_slip_no ||'</td>'||
973 '<td align="right"><B>'||
974 get_lookup_meaning('L_QPP_DEFICIENCY')||'</B></td>'||
975 '<td align="right">'||
976 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_qpp_deficiency)||'</td>'||
977 '<td align="right"><B>'||
978 get_lookup_meaning('L_PPIP_DEFICIENCY')||'</B></td>'||
979 '<td align="right">'||
980 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_ppip_deficiency)||
981 '</td></tr>';
982 --'<td align="left">'||sv_no_of_cpp_periods||'</td>'||
983 end if;
984 format_data(lv_format);
985
986 if sv_f_p = 'F' then
987 lv_format := '<tr><td align="right"><B>'||
988 get_lookup_meaning('L_CPP_BASIC_EXEMPT')||'</B></td>'||
989 '<td align="right">'||
990 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_basic_exemption)||'</td>'||
991 '<td align="right"><B>'||
992 get_lookup_meaning('L_CPP_EXEMPT')||'</B></td>'||
993 '<td align="right">'||
994 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_exempt_bal)||'</td>'||
995 /* bug 5552744 */
996 /* QC Insurable Earning */
997 '<td align="right"><B>'||
998 get_lookup_meaning('L_INS_EARN')||' (QC)'||'</B></td>'||
999 '<td align="right">'||
1000 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_qc_insurable_earnings)||'</td></tr>'||
1001 /* QC EI Reported */
1002 '<tr><td align="right"><B>'||
1003 ' ' ||'</B></td>'||
1004 '<td align="right">'||
1005 ' '||'</td>'||
1006 '<td align="right"><B>'||
1007 ' ' ||'</B></td>'||
1008 '<td align="right">'||
1009 ' '||'</td>'||
1010 '<td align="right"><B>'||
1011 get_lookup_meaning('L_EI_REPORTED')||' (QC)'||'</B></td>'||
1012 '<td align="right">'||
1013 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_qc_ded_reported_18)||'</td></tr>'||
1014 /* QC EI Required */
1015 '<tr><td align="right"><B>'||
1016 ' ' ||'</B></td>'||
1017 '<td align="right">'||
1018 ' '||'</td>'||
1019 '<td align="right"><B>'||
1020 ' ' ||'</B></td>'||
1021 '<td align="right">'||
1022 ' '||'</td>'||
1023 '<td align="right"><B>'||
1024 get_lookup_meaning('L_EI_REQUIRED')||' (QC)'||'</B></td>'||
1025 '<td align="right">'||
1026 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_qc_ei_ded_required)||'</td></tr>'||
1027 /* QC EI Deficiency */
1028 '<tr><td align="right"><B>'||
1029 ' ' ||'</B></td>'||
1030 '<td align="right">'||
1031 ' '||'</td>'||
1032 '<td align="right"><B>'||
1033 ' ' ||'</B></td>'||
1034 '<td align="right">'||
1035 ' '||'</td>'||
1036 '<td align="right"><B>'||
1037 get_lookup_meaning('L_EI_DEFICIENCY')||' (QC)'||'</B></td>'||
1038 '<td align="right">'||
1039 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_qc_ei_deficiency)||'</td></tr>';
1040
1041 else
1042 lv_format := '<tr><td align="right"><B>'||
1043 get_lookup_meaning('L_QPP_BASIC_EXEMPT')||'</B></td>'||
1044 '<td align="right">'||
1045 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_basic_exemption)||'</td>'||
1046 '<td align="right"><B>'||
1047 get_lookup_meaning('L_QPP_EXEMPT')||'</B></td>'||
1048 '<td align="right">'||
1049 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,sv_cpp_exempt_bal)||'</td></tr>';
1050 end if;
1051 format_data(lv_format);
1052
1053 end if; -- End of sv_report_type = 'T4A'
1054
1055 if sv_nb > 0 then
1056 lv_format := '<tr><td align=right> </td></tr>';
1057 format_data(lv_format);
1058 lv_format := null;
1059 lv_req_flds := null;
1060
1061 /* The below logic introduce to sort negative balance jurisdictionwise
1062 and print only for T4. For T4A and RL1 control will go to else part. */
1063
1064 if ((sv_report_type = 'T4') or
1065 (sv_report_type = 'CAEOY_T4_AMEND_PP')) then
1066
1067 lv_format := '<tr><td colspan=6 align=left><B>'||--'<tr><td align=right><B>'|| rgottipa to fix 10261964
1068 get_lookup_meaning('R_NEG_BOX')||'</B></td></tr>';
1069 format_data(lv_format);
1070
1071 while sv_neg_bal.count > 0
1072 loop
1073
1074 hr_utility.set_location(to_char(sv_neg_bal.count),888);
1075 i := 0;
1076 l := 0;
1077 l_juris_cd := sv_neg_bal(1).dbi_name;
1078
1079 for k in sv_neg_bal.first..sv_neg_bal.last
1080 loop
1081
1082 l_juris_cd1 := sv_neg_bal(k).dbi_name;
1083 hr_utility.set_location('K = '||to_char(k),888);
1084 hr_utility.set_location('JURI = *'||sv_neg_bal(k).dbi_name||'*',888);
1085
1086 if l_juris_cd1 = l_juris_cd then
1087 i := i + 1;
1088
1089 hr_utility.set_location('I = '||to_char(i),888);
1090
1091 if mod(i,5) = 1 then
1092 if i = 1 then
1093 lv_format := '<tr><td align=right><B>'||
1094 l_juris_cd ||'</B></td>';
1095 else
1096 lv_format := '<tr><td align=right> </td>';
1097 end if;
1098 lv_req_flds := '<tr><td align=right> </td>';
1099 lv_format := lv_format || '<td align="right"><B>'||
1100 --get_bal_name(sv_neg_bal(k).dbi_short_name)||'</B></td>';
1101 sv_neg_bal(k).dbi_short_name||'</B></td>';
1102 lv_req_flds := lv_req_flds || '<td align="right">'||
1103 sv_neg_bal(k).dbi_value||'</td>';
1104 elsif mod(i,5) = 0 then
1105 lv_format := lv_format || '<td align="right"><B>'||
1106 --get_bal_name(sv_neg_bal(k).dbi_short_name)||
1107 sv_neg_bal(k).dbi_short_name||
1108 '</B></td></tr>';
1109 lv_req_flds := lv_req_flds || '<td align="right">'||
1110 sv_neg_bal(k).dbi_value||'</td></tr>';
1111 format_data(lv_format);
1112 format_data(lv_req_flds);
1113 lv_format := null;
1114 lv_req_flds := null;
1115 else
1116 lv_format := lv_format || '<td align="right"><B>'||
1117 --get_bal_name(sv_neg_bal(k).dbi_short_name)||'</B></td>';
1118 sv_neg_bal(k).dbi_short_name||'</B></td>';
1119 lv_req_flds := lv_req_flds || '<td align="right">'||
1120 sv_neg_bal(k).dbi_value||'</td>';
1121 end if;
1122 else
1123 l := l + 1;
1124 hr_utility.set_location('L = '||to_char(l),888);
1125 l_sort_neg(l).dbi_name := sv_neg_bal(k).dbi_name;
1126 l_sort_neg(l).dbi_value := sv_neg_bal(k).dbi_value;
1127 l_sort_neg(l).dbi_short_name := sv_neg_bal(k).dbi_short_name;
1128 end if;
1129 end loop;
1130
1131 lv_format := rtrim(ltrim(lv_format));
1132 lv_req_flds := rtrim(ltrim(lv_req_flds));
1133
1134 if mod(i,5) <> 0 then
1135 lv_format := lv_format ||'</tr>';
1136 lv_req_flds := lv_req_flds ||'</tr>';
1137 format_data(lv_format);
1138 format_data(lv_req_flds);
1139 lv_format := null;
1140 lv_req_flds := null;
1141 end if;
1142
1143 sv_neg_bal.delete;
1144
1145 if l_sort_neg.first is not null then
1146
1147 for k in l_sort_neg.first..l_sort_neg.last
1148 loop
1149 sv_neg_bal(k).dbi_name := l_sort_neg(k).dbi_name;
1150 sv_neg_bal(k).dbi_value := l_sort_neg(k).dbi_value;
1151 sv_neg_bal(k).dbi_short_name := l_sort_neg(k).dbi_short_name;
1152 end loop;
1153
1154 l_sort_neg.delete;
1155
1156 end if;
1157
1158
1159 end loop;
1160 else
1161
1162 for i in 1..sv_nb loop
1163
1164 hr_utility.set_location(to_char(mod(i,5)),888);
1165 if mod(i,5) = 1 then
1166 if i = 1 then
1167 lv_format := '<tr><td colspan=6 align=left><B>'||
1168 get_lookup_meaning('R_NEG_BOX')||'</B></td></tr>'
1169 || '<tr><td align=right> </td>' ; -- Bug 15908452 - Corrected formatting issue
1170 else
1171 lv_format := '<tr><td align=right> </td>';
1172 end if;
1173 lv_req_flds := '<tr><td align=right> </td>';
1174 lv_format := lv_format || '<td align="right"><B>'||
1175 -- get_bal_name(sv_neg_bal(i).dbi_short_name)||'</B></td>';
1176 sv_neg_bal(i).dbi_short_name||'</B></td>';
1177 lv_req_flds := lv_req_flds || '<td align="right">'||
1178 sv_neg_bal(i).dbi_value||'</td>';
1179 elsif mod(i,5) = 0 then
1180 lv_format := lv_format || '<td align="right"><B>'||
1181 -- get_bal_name(sv_neg_bal(i).dbi_short_name)||
1182 sv_neg_bal(i).dbi_short_name||
1183 '</B></td></tr>';
1184 lv_req_flds := lv_req_flds || '<td align="right">'||
1185 sv_neg_bal(i).dbi_value||'</td></tr>';
1186 format_data(lv_format);
1187 format_data(lv_req_flds);
1188 lv_format := null;
1189 lv_req_flds := null;
1190 else
1191 lv_format := lv_format || '<td align="right"><B>'||
1192 --get_bal_name(sv_neg_bal(i).dbi_short_name)||'</B></td>';
1193 sv_neg_bal(i).dbi_short_name||'</B></td>';
1194 lv_req_flds := lv_req_flds || '<td align="right">'||
1195 sv_neg_bal(i).dbi_value||'</td>';
1196 end if;
1197 lv_format := rtrim(ltrim(lv_format));
1198 lv_req_flds := rtrim(ltrim(lv_req_flds));
1199
1200 end loop;
1201 if mod(sv_nb,5) <> 0 then
1202 lv_format := lv_format ||'</tr>';
1203 lv_req_flds := lv_req_flds ||'</tr>';
1204 format_data(lv_format);
1205 format_data(lv_req_flds);
1206 lv_format := null;
1207 lv_req_flds := null;
1208 end if;
1209
1210 end if;
1211
1212 end if;
1213
1214 if ( ( sv_c + sv_m ) > 0 ) then
1215
1216 hr_utility.set_location('5',888);
1217 lv_format := '<tr><td> </td></tr>';
1218 format_data(lv_format);
1219
1220 hr_utility.set_location('6',888);
1221 lv_format := '<tr><td><B>'||get_lookup_meaning('R_REASON')||'</B></td></tr>';
1222 format_data(lv_format);
1223
1224 if ( sv_m > 0 ) then
1225 for i in 1..sv_m
1226 loop
1227 hr_utility.set_location('7',888);
1228 lv_format := '<tr><td colspan=6>'||print_spaces(30);
1229 lv_format := lv_format||' '||to_char(i)||'. '||sv_msg(i)||'</td></tr>';
1230 format_data(lv_format);
1231 end loop;
1232 end if;
1233 if sv_c > 0 then
1234 hr_utility.set_location('8',888);
1235 lv_format := '<tr><td colspan=6>'||print_spaces(30);
1236 lv_format := lv_format||' '||to_char(sv_m+1)||'. '||
1237 get_lookup_meaning('R_REQ_FIELDS')||'</td></tr>';
1238 format_data(lv_format);
1239 lv_req_flds := null;
1240 for i in 1..sv_c
1241 loop
1242 if i <> 1 then
1243 lv_req_flds := lv_req_flds || ', ';
1244 end if;
1245 lv_req_flds := lv_req_flds || sv_col(i);
1246 end loop;
1247 if ( length(lv_req_flds) < 135 ) then
1248 lv_req_flds := lv_req_flds ||
1249 print_spaces( 135 - length(lv_req_flds) );
1250 end if;
1251 hr_utility.set_location('9',888);
1252 lv_format := '<tr><td colspan=5>'||print_spaces(30);
1253 lv_format := lv_format||' '||lv_req_flds||'</td></tr>';
1254 format_data(lv_format);
1255 end if;
1256 end if;
1257 hr_utility.set_location('10',888);
1258 lv_format := '<tr><td colspan=6>'||print_line('-',156)||'</td></tr>';
1259 format_data(lv_format);
1260 end print_employee;
1261
1262 /* The procedure print_employer prints Employer Data for T4, T4A and RL1. */
1263
1264 procedure print_employer is
1265 lv_format varchar2(32000);
1266 lv_req_flds varchar2(32000);
1267 begin
1268 lv_format := '<tr><td>'|| sv_employer_name || '</td><td colspan=2>';
1269 if sv_f_p = 'P' then
1270 lv_format := lv_format || sv_qin;
1271 else
1272 lv_format := lv_format || sv_busi_no;
1273 end if;
1274 lv_format := lv_format || '</td><td>' || sv_reporting_year ||
1275 '</td><td align="center">' || sv_trans_y_n || '</td></tr>';
1276 format_data(lv_format);
1277
1278 lv_format := '<tr><td>' || sv_employer_address_line1 || '</td></tr>';
1279 format_data(lv_format);
1280
1281 if ( sv_employer_address_line2 is not null ) then
1282 lv_format := '<tr><td>' || sv_employer_address_line2 || '</td></tr>';
1283 format_data(lv_format);
1284 end if;
1285
1286 /* 5942058 */
1287 if ( sv_employer_address_line3 is not null ) then
1288 lv_format := '<tr><td>' || sv_employer_address_line3 || '</td></tr>';
1289 format_data(lv_format);
1290 end if;
1291
1292 lv_format := '<tr><td>' || sv_employer_city||','||sv_employer_province||
1293 ' '|| sv_employer_postal_code || '</td></tr>';
1294 format_data(lv_format);
1295
1296 if ( ( sv_c + sv_m ) > 0 ) then
1297
1298 lv_format := '</table>' ||
1299 '<table border=0><tr><td> </td><td></td></tr>';
1300 format_data(lv_format);
1301
1302 lv_format := '<tr><td colspan=2><B>' ||
1303 get_lookup_meaning('R_REASON') ||
1304 '</B></td></tr>';
1305 format_data(lv_format);
1306
1307 if ( sv_m > 0 ) then
1308 for i in 1..sv_m
1309 loop
1310 lv_format := '<tr><td>'||print_spaces(30)||'</td><td>' ||
1311 to_char(i)||'. '||sv_msg(i)||
1312 print_spaces(135-length(sv_msg(i)))||
1313 '</td></tr>';
1314 format_data(lv_format);
1315 end loop;
1316 end if;
1317 if sv_c > 0 then
1318 lv_format := '<tr><td>'||print_spaces(30)||'</td><td>' ||
1319 to_char(sv_m+1)||'. '||
1320 get_lookup_meaning('R_REQ_FIELDS') ||
1321 '</td></tr><tr></tr>';
1322 format_data(lv_format);
1323
1324 for i in 1..sv_c
1325 loop
1326 if i <> 1 then
1327 lv_req_flds := lv_req_flds || ', ';
1328 end if;
1329 lv_req_flds := lv_req_flds || sv_col(i);
1330 end loop;
1331 if ( length(lv_req_flds) < 135 ) then
1332 lv_req_flds := lv_req_flds ||
1333 print_spaces( 135 - length(lv_req_flds) );
1334 end if;
1335 lv_format := '<tr><td></td><td>'||lv_req_flds||'</td></tr>';
1336 format_data(lv_format);
1337 end if;
1338 lv_format := '<tr><td colspan=2>' || print_line('-',156) ||
1339 '</td></tr>';
1340 format_data(lv_format);
1341 else
1342 lv_format := '<tr><td colspan=5>' || print_line('-',156) ||
1343 '</td></tr>';
1344 format_data(lv_format);
1345 end if;
1346 lv_format := '</table>';
1347 format_data(lv_format);
1348 end print_employer;
1349
1350 /* The procedure static_header prints the input parameters */
1351
1352 procedure static_header is
1353 lv_format varchar2(32000);
1354 begin
1355
1356 lv_format := '<table border=0><tr><td></td><td><HEAD> <CENTER> <B>' ||
1357 sv_pier_yeer || '</B></CENTER></HEAD></td>'||
1358 '<td align="left">' || get_lookup_meaning('L_DATE')||
1359 sv_date || '</td></tr>';
1360 format_data(lv_format);
1361
1362 lv_format := '<tr><td> </td></tr>';
1363 format_data(lv_format);
1364
1365 lv_format := '<tr><td>'|| print_line('-',50) || '</td><td>'||
1366 print_line('-',50) || '</td><td>' || print_line('-',50) ||
1367 '</td></tr>';
1368 format_data(lv_format);
1369
1370 lv_format := '<tr><td align="left"><B>' ||
1371 get_lookup_meaning('L_REPORT_PARAMETERS') ||
1372 '</B></td></tr>';
1373 format_data(lv_format);
1374
1375 lv_format := '<tr><td align="right"><B>' ||
1376 get_lookup_meaning('L_REPORTING_YEAR') || '</B></td>' ||
1377 '<td align="left">' || sv_reporting_year || '</td></tr>';
1378 format_data(lv_format);
1379
1380 lv_format := '<tr><td align="right"><B>' ||
1381 get_lookup_meaning('L_REPORT_NAME') || '</B></td>' ||
1382 '<td align="left">' || sv_pier_yeer || '</td></tr>';
1383 format_data(lv_format);
1384
1385 lv_format := '<tr><td align="right"><B>' ||
1386 get_lookup_meaning('L_FED_PROV') || '</B></td>' ||
1387 '<td align="left">' || sv_fed_prov || '</td></tr>';
1388 format_data(lv_format);
1389
1390 lv_format := '<tr><td align="right"><B>' ||
1391 get_lookup_meaning('L_BUSINESS_NO') || '</B></td>'||
1392 '<td align="left">' || sv_busi_no || '</td></tr>';
1393 format_data(lv_format);
1394
1395 lv_format := '<tr><td align="right"><B>' ||
1396 get_lookup_meaning('L_ARCHIVED_GRE') || '</B></td>' ||
1397 '<td align="left">' || sv_gre_name || '</td></tr>';
1398 format_data(lv_format);
1399
1400 lv_format := '<tr><td align="right"><B>' ||
1401 get_lookup_meaning('L_QC_ID_NO') || '</B></td>' ||
1402 '<td align="left">' || sv_qin || '</td></tr>';
1403 format_data(lv_format);
1404
1405 lv_format := '<tr><td align="right"><B>' ||
1406 get_lookup_meaning('L_ARCHIVED_PRE') || '</B></td>'||
1407 '<td align="left">' || sv_pre_name || '</td></tr>';
1408 format_data(lv_format);
1409
1410 lv_format := '<tr><td> </td></tr>';
1411 format_data(lv_format);
1412
1413 lv_format := '<tr><td colspan=3>'||print_line('-',156)||'</td></tr>';
1414 format_data(lv_format);
1415
1416 sv_line := 11;
1417 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, new_page);
1418
1419 lv_format := '</table>';
1420 format_data(lv_format);
1421
1422 end static_header;
1423
1424 /* The procedure prov_employee_validation validates the value of
1425 RL1 and RL2 Employee */
1426
1427 PROCEDURE prov_employee_validation is
1428 l_emp_first_name varchar2(240);
1429 l_emp_last_name varchar2(240);
1430 lv_overlimit number(1) := 0;
1431 lv_missing_adr number(1) := 0;
1432 lv_person_id number(10) := 0;
1433 lv_asg_act_id number(10) := 0;
1434 /* 4142751 */
1435 addr_line_2_3 number(10) := 0;
1436 /* 15908332 */
1437 l_i number(10);
1438
1439 begin
1440 sv_ei_deficiency := 0;
1441
1442 /* bug 6837510 */
1443 if sv_location_id is null then
1444 sv_m := sv_m + 1;
1445 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_LOC');
1446 sv_print := 1;
1447 end if;
1448
1449 if sv_report_type in ('RL1','CAEOY_RL1_AMEND_PP') then
1450
1451 for i in sv_dbi.first..sv_dbi.last
1452 loop
1453 hr_utility.set_location(to_char(i)||'. '||sv_dbi(i).dbi_name||' '||
1454 sv_dbi(i).dbi_value||' '||sv_dbi(i).dbi_short_name, 999 );
1455
1456 if sv_dbi(i).dbi_name = 'CAEOY_PERSON_ID' then
1457 lv_person_id := to_number(sv_dbi(i).dbi_value);
1458 end if;
1459
1460 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_NUMBER' then
1461 sv_employee_no := sv_dbi(i).dbi_value;
1462 end if;
1463
1464 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_FIRST_NAME' then
1465 l_emp_first_name := sv_dbi(i).dbi_value;
1466 if l_emp_first_name is null then
1467 sv_c := sv_c + 1;
1468 sv_col(sv_c) := upper(sv_dbi(i).dbi_short_name);
1469 sv_print := 1;
1470 end if;
1471 end if;
1472
1473 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_LAST_NAME' then
1474 l_emp_last_name := sv_dbi(i).dbi_value;
1475 if l_emp_last_name is null then
1476 sv_c := sv_c + 1;
1477 sv_col(sv_c) := upper(sv_dbi(i).dbi_short_name);
1478 sv_print := 1;
1479 end if;
1480 end if;
1481
1482 if sv_dbi(i).dbi_name = 'CAEOY_RL1_SLIP_NUMBER' then
1483 sv_rl1_slip_no := sv_dbi(i).dbi_value;
1484 if sv_rl1_slip_no is null then
1485 sv_c := sv_c + 1;
1486 sv_col(sv_c) := upper(sv_dbi(i).dbi_short_name);
1487 sv_print := 1;
1488 end if;
1489 end if;
1490
1491 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE1' and
1492 sv_dbi(i).dbi_value is null
1493 then
1494 if lv_missing_adr = 0 then
1495 sv_m := sv_m + 1;
1496 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1497 sv_print := 1;
1498 lv_missing_adr := 1;
1499 end if;
1500 end if;
1501
1502 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_CITY' and
1503 sv_dbi(i).dbi_value is null
1504 then
1505 if lv_missing_adr = 0 then
1506 sv_m := sv_m + 1;
1507 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1508 sv_print := 1;
1509 lv_missing_adr := 1;
1510 end if;
1511 end if;
1512
1513 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_PROVINCE' and
1514 sv_dbi(i).dbi_value is null
1515 then
1516 if lv_missing_adr = 0 then
1517 sv_m := sv_m + 1;
1518 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1519 sv_print := 1;
1520 lv_missing_adr := 1;
1521 end if;
1522 end if;
1523
1524 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_POSTAL_CODE' and
1525 sv_dbi(i).dbi_value is null
1526 then
1527 if lv_missing_adr = 0 then
1528 sv_m := sv_m + 1;
1529 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1530 sv_print := 1;
1531 lv_missing_adr := 1;
1532 end if;
1533 end if;
1534
1535 /* Changes start - Bug 4142751 */
1536
1537 if ( sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE1' and
1538 sv_dbi(i).dbi_value is not null )
1539 then
1540 if ( length(sv_dbi(i).dbi_value) > 30 ) then
1541 sv_m := sv_m + 1;
1542 sv_msg(sv_m) := get_lookup_meaning('R_EXCEEDING_ADDR_LINE_1');
1543 sv_print := 1;
1544 end if;
1545 end if;
1546
1547 if ( sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE2' and
1548 sv_dbi(i).dbi_value is not null )
1549 then
1550 addr_line_2_3 := addr_line_2_3 + length(sv_dbi(i).dbi_value) ;
1551 end if;
1552
1553 if ( sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE3' ) then
1554 if ( sv_dbi(i).dbi_value is not null )
1555 then
1556 addr_line_2_3 := addr_line_2_3 + length(sv_dbi(i).dbi_value) ;
1557 end if;
1558 if ( addr_line_2_3 > 30 ) then
1559 sv_m := sv_m + 1;
1560 sv_msg(sv_m) := get_lookup_meaning('R_EXCEEDING_ADDR_LINE_2_3');
1561 sv_print := 1;
1562 end if;
1563 end if;
1564
1565 /* Changes End - Bug 4142751 */
1566
1567 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_SIN' then
1568 sv_employee_sin := substr(sv_dbi(i).dbi_value,1,3) ||' '||
1569 substr(sv_dbi(i).dbi_value,4,3) ||' '||
1570 substr(sv_dbi(i).dbi_value,7,3) ;
1571
1572 if sv_dbi(i).dbi_value is null then
1573 sv_c := sv_c + 1;
1574 sv_col(sv_c) := upper(sv_dbi(i).dbi_short_name);
1575 sv_print := 1;
1576 end if;
1577
1578 if ( not pay_ca_yeer_pkg.validateSIN(sv_dbi(i).dbi_value)) and sv_dbi(i).dbi_value is not null then /* Bug 6773615 sbachu*/
1579 sv_m := sv_m + 1;
1580 sv_msg(sv_m) := get_lookup_meaning('R_SIN_INVALID');
1581 sv_print := 1;
1582 end if;
1583
1584 end if;
1585
1586 if sv_dbi(i).dbi_name = 'CAEOY_GROSS_EARNINGS_PER_JD_YTD' then
1587 sv_total_earnings := nvl(sv_total_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1588 hr_utility.trace('sv_total_earnings ='|| sv_total_earnings);
1589 end if;
1590
1591 if sv_dbi(i).dbi_name = 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD' then
1592 sv_pensionable_earnings := nvl(sv_pensionable_earnings,0) +fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1593 end if;
1594
1595 if sv_dbi(i).dbi_name = 'CAEOY_QPP_EE_WITHHELD_PER_JD_YTD' then
1596 sv_ded_reported_16 := nvl(sv_ded_reported_16,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1597 end if;
1598
1599 if sv_dbi(i).dbi_name = 'CAEOY_QPP_BASIC_EXEMPTION_PER_JD_YTD' then
1600 sv_cpp_basic_exemption :=
1601 nvl(sv_cpp_basic_exemption,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1602 end if;
1603
1604 if sv_dbi(i).dbi_name = 'CAEOY_QPP_EXEMPT_PER_JD_YTD' then
1605 sv_cpp_exempt_bal :=
1606 nvl(sv_cpp_exempt_bal,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1607 end if;
1608
1609 /* Added by ssmukher for PPIP tax */
1610 if sv_dbi(i).dbi_name = 'CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD' then
1611 sv_ppip_insurable_earnings := nvl(sv_ppip_insurable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1612 end if;
1613 if sv_dbi(i).dbi_name = 'CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD' then
1614 sv_ded_reported_ppip :=nvl(sv_ded_reported_ppip,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
1615 end if;
1616
1617 sv_ppip_ded_required := ((sv_ppip_insurable_earnings * sv_ppip_rate )/ 100 );
1618
1619 if sv_ppip_ded_required > sv_ppip_max_exempt then
1620 sv_ppip_ded_required := sv_ppip_max_exempt;
1621 lv_overlimit := 1;
1622 end if;
1623
1624 if sv_ppip_ded_required < 0 then
1625 sv_ppip_deficiency := sv_ded_reported_ppip ;
1626 end if;
1627
1628 sv_ppip_deficiency := (sv_ppip_ded_required - sv_ded_reported_ppip);
1629
1630 if sv_ppip_deficiency < 0 then
1631 sv_ppip_deficiency := 0;
1632 end if;
1633
1634 if sv_p_y = 'E' then
1635
1636 if instr(sv_dbi(i).dbi_name, 'YTD') > 0 and
1637 instr(sv_dbi(i).dbi_name, 'CODE') = 0 and
1638 fnd_number.canonical_to_number(nvl(sv_dbi(i).dbi_value,'0')) < 0 then
1639
1640 sv_nb := sv_nb + 1;
1641 sv_neg_bal(sv_nb).dbi_name := sv_dbi(i).dbi_name;
1642 sv_neg_bal(sv_nb).dbi_value :=
1643 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,fnd_number.canonical_to_number(sv_dbi(i).dbi_value));
1644 sv_neg_bal(sv_nb).dbi_short_name := sv_dbi(i).dbi_short_name;
1645 sv_print := 1;
1646
1647 end if;
1648
1649 if (sv_dbi(i).dbi_name = 'CAEOY_RL1_NONBOX_FOOTNOTE') and
1650 (fnd_number.canonical_to_number(nvl(sv_dbi(i).dbi_value,'0')) < 0) then
1651
1652 sv_nb := sv_nb + 1;
1653 sv_neg_bal(sv_nb).dbi_name := sv_dbi(i).dbi_name;
1654 sv_neg_bal(sv_nb).dbi_value :=
1655 pay_us_employee_payslip_web.get_format_value(sv_b_g_id,fnd_number.canonical_to_number(sv_dbi(i).dbi_value));
1656 sv_neg_bal(sv_nb).dbi_short_name := sv_dbi(i).dbi_short_name;
1657 sv_print := 1;
1658
1659 end if;
1660
1661 end if;
1662
1663 end loop;
1664
1665 -- commented by rgottipa
1666 --sv_employee_name := substr(l_emp_last_name,1,120)||', '||substr(l_emp_first_name,1,118);-- #2406070
1667 sv_employee_name := substr(l_emp_last_name,1,30)||', '||substr(l_emp_first_name,1,30); --#10261964
1668
1669 /* Commented for Bug no. 13421306
1670 sv_cpp_ded_required := ((sv_pensionable_earnings - sv_cpp_exempt ) * sv_cpp_rate / 100 );
1671
1672 if sv_cpp_ded_required > sv_cpp_max_exempt then
1673 sv_cpp_ded_required := sv_cpp_max_exempt;
1674 lv_overlimit := 1;
1675 end if;
1676
1677 if sv_cpp_ded_required < 0 then
1678 sv_cpp_ded_required := 0.00;
1679 end if;*/
1680 /* For Bug no. 13421306 -start*/
1681 sv_qpp_ded_required := ((sv_pensionable_earnings - sv_cpp_exempt ) * sv_qpp_rate / 100 );
1682
1683 if sv_qpp_ded_required > sv_qpp_max_exempt then
1684 sv_qpp_ded_required := sv_qpp_max_exempt;
1685 lv_overlimit := 1;
1686 end if;
1687
1688 if sv_qpp_ded_required < 0 then
1689 sv_qpp_ded_required := 0.00;
1690 end if;
1691 /* For Bug no. 13421306 - end*/
1692
1693 /*sv_cpp_qpp_deficiency := to_char((fnd_number.canonical_to_number(sv_ded_reported_16,'999,999,990.00')
1694 - sv_cpp_ded_required ),'999,990.00'); */
1695
1696 /*For Bug no. 13421306- Changed sv_cpp_ded_required to sv_qpp_ded_required*/
1697 --sv_cpp_qpp_deficiency := sv_cpp_ded_required - sv_ded_reported_16;
1698 sv_cpp_qpp_deficiency := sv_qpp_ded_required - sv_ded_reported_16;
1699
1700
1701
1702 /* The deficiency field should not display over-payments */
1703 if sv_cpp_qpp_deficiency < 0 then ----till here
1704 sv_cpp_qpp_deficiency := 0;
1705 end if;
1706
1707 if sv_nb > 0 then
1708 sv_m := sv_m + 1;
1709 sv_msg(sv_m) := get_lookup_meaning('R_NEG_BAL');
1710 sv_print := 1;
1711 end if;
1712
1713 if lv_overlimit > 0 then
1714 sv_m := sv_m + 1;
1715 sv_msg(sv_m) := get_lookup_meaning('R_OVERLIMIT_BAL');
1716 sv_print := 1;
1717 end if;
1718
1719 if sv_p_y = 'P' then
1720 /* When option is PIER Report, the following messages should print
1721 if they fullfill their conditions except Negative Balance. */
1722 sv_print := 0;
1723 sv_nb := 0;
1724 sv_m := 0;
1725 sv_c := 0;
1726 sv_msg.delete;
1727 sv_col.delete;
1728 sv_neg_bal.delete;
1729 end if;
1730
1731 /* if ( ( fnd_number.canonical_to_number( sv_cpp_qpp_deficiency, '999,990.00') > 0 and
1732 abs(fnd_number.canonical_to_number( sv_cpp_qpp_deficiency, '999,990.00')) > 1 ) or
1733 ( fnd_number.canonical_to_number( sv_ei_deficiency, '999,990.00') > 0 and
1734 abs(fnd_number.canonical_to_number( sv_ei_deficiency, '999,990.00')) > 1 ) ) then */
1735
1736 if (sv_cpp_qpp_deficiency > 1) or (sv_ppip_deficiency > 1) then
1737
1738 sv_print := 1;
1739
1740 if to_number(sv_reporting_year) -
1741 to_number(to_char(sv_date_of_birth,'YYYY') ) = 18 then
1742 sv_m := sv_m + 1;
1743 sv_msg(sv_m) := get_lookup_meaning('R_EMP_TURNED_18');
1744 end if;
1745
1746 if sv_reporting_year = to_char(sv_hire_date,'YYYY') then
1747 sv_m := sv_m + 1;
1748 sv_msg(sv_m) := get_lookup_meaning('R_EMP_HIRED');
1749 end if;
1750
1751 if sv_reporting_year = to_char(sv_terminate_date,'YYYY') then
1752 sv_m := sv_m + 1;
1753 sv_msg(sv_m) := get_lookup_meaning('R_EMP_TERMINATED');
1754 end if;
1755
1756 if sv_p_y = 'P' then
1757
1758 if (sv_cpp_block = 'Y') then
1759 sv_m := sv_m + 1;
1760 sv_msg(sv_m) := get_lookup_meaning('R_QPP_BLOCK');
1761 end if;
1762
1763 if (sv_ppip_block = 'Y') then
1764 sv_m := sv_m + 1;
1765 sv_msg(sv_m) := get_lookup_meaning('R_PPIP_BLOCK');
1766 end if;
1767
1768 end if;
1769
1770 end if;
1771
1772 if sv_p_y = 'E' then
1773
1774 if (sv_cpp_block = 'Y') then
1775 sv_m := sv_m + 1;
1776 sv_msg(sv_m) := get_lookup_meaning('R_QPP_BLOCK');
1777 sv_print := 1;
1778 end if;
1779
1780 if (sv_ppip_block = 'Y') then
1781 sv_m := sv_m + 1;
1782 sv_msg(sv_m) := get_lookup_meaning('R_PPIP_BLOCK');
1783 end if;
1784
1785 if get_multi_jd(lv_person_id) > 1 then
1786 sv_m := sv_m + 1;
1787 sv_msg(sv_m) := get_lookup_meaning('R_EMP_MULTI_JD');
1788 sv_print := 1;
1789 end if;
1790
1791 end if;
1792
1793 end if; /* End of sv_report_type in ('RL1','CAEOY_RL1_AMEND_PP') */
1794
1795 /* RL2 Employee Validation */
1796
1797 if sv_report_type = 'RL2' then
1798
1799 lv_person_id := to_number(lr_rl2_emprec.PERSON_ID);
1800 sv_employee_no := lr_rl2_emprec.EMPLOYEE_NUMBER;
1801
1802 l_emp_first_name := lr_rl2_emprec.EMPLOYEE_FIRST_NAME;
1803 if l_emp_first_name is null then
1804 sv_c := sv_c + 1;
1805 sv_col(sv_c) := upper('First Name');
1806 sv_print := 1;
1807 end if;
1808
1809 l_emp_last_name := lr_rl2_emprec.EMPLOYEE_LAST_NAME;
1810 if l_emp_last_name is null then
1811 sv_c := sv_c + 1;
1812 sv_col(sv_c) := upper('Last Name');
1813 sv_print := 1;
1814 end if;
1815
1816 -- commented by rgottipa
1817 --sv_employee_name := substr(l_emp_last_name,1,120)||', '||substr(l_emp_first_name,1,118);-- #2406070
1818 sv_employee_name := substr(l_emp_last_name,1,30)||', '||substr(l_emp_first_name,1,30); --#10261964
1819 sv_rl1_slip_no := lr_rl2_emprec.RL2_SLIP_NUMBER;
1820 if sv_rl1_slip_no is null then
1821 sv_c := sv_c + 1;
1822 sv_col(sv_c) := upper('Slip Number');
1823 sv_print := 1;
1824 end if;
1825
1826
1827 if lr_rl2_emprec.EMPLOYEE_ADDRESS_LINE1 is null then
1828 if lv_missing_adr = 0 then
1829 sv_m := sv_m + 1;
1830 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1831 sv_print := 1;
1832 lv_missing_adr := 1;
1833 end if;
1834 end if;
1835
1836 if lr_rl2_emprec.EMPLOYEE_CITY is null then
1837 if lv_missing_adr = 0 then
1838 sv_m := sv_m + 1;
1839 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1840 sv_print := 1;
1841 lv_missing_adr := 1;
1842 end if;
1843 end if;
1844
1845 if lr_rl2_emprec.EMPLOYEE_PROVINCE is null then
1846 if lv_missing_adr = 0 then
1847 sv_m := sv_m + 1;
1848 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1849 sv_print := 1;
1850 lv_missing_adr := 1;
1851 end if;
1852 end if;
1853
1854 if lr_rl2_emprec.EMPLOYEE_POSTAL_CODE is null then
1855 if lv_missing_adr = 0 then
1856 sv_m := sv_m + 1;
1857 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
1858 sv_print := 1;
1859 lv_missing_adr := 1;
1860 end if;
1861 end if;
1862
1863 /* Changes start - Bug 4142751 */
1864
1865 if ( lr_rl2_emprec.EMPLOYEE_ADDRESS_LINE1 is not null )
1866 then
1867 if ( length(lr_rl2_emprec.EMPLOYEE_ADDRESS_LINE1) > 30 ) then
1868 sv_m := sv_m + 1;
1869 sv_msg(sv_m) := get_lookup_meaning('R_EXCEEDING_ADDR_LINE_1');
1870 sv_print := 1;
1871 end if;
1872 end if;
1873
1874 if ( lr_rl2_emprec.EMPLOYEE_ADDRESS_LINE2 is not null ) then
1875 addr_line_2_3 := addr_line_2_3 + length(lr_rl2_emprec.EMPLOYEE_ADDRESS_LINE2) ;
1876 end if;
1877
1878 if ( lr_rl2_emprec.EMPLOYEE_ADDRESS_LINE3 is not null ) then
1879 addr_line_2_3 := addr_line_2_3 + length(lr_rl2_emprec.EMPLOYEE_ADDRESS_LINE3) ;
1880 end if;
1881
1882 if ( addr_line_2_3 > 30 ) then
1883 sv_m := sv_m + 1;
1884 sv_msg(sv_m) := get_lookup_meaning('R_EXCEEDING_ADDR_LINE_2_3');
1885 sv_print := 1;
1886 end if;
1887
1888 /* Changes End - Bug 4142751 */
1889
1890 sv_employee_sin := substr(lr_rl2_emprec.EMPLOYEE_SIN,1,3) ||' '||
1891 substr(lr_rl2_emprec.EMPLOYEE_SIN,4,3) ||' '||
1892 substr(lr_rl2_emprec.EMPLOYEE_SIN,7,3) ;
1893
1894 if lr_rl2_emprec.EMPLOYEE_SIN is null then
1895 sv_c := sv_c + 1;
1896 sv_col(sv_c) := upper('Sin');
1897 sv_print := 1;
1898 end if;
1899
1900 if ( not pay_ca_yeer_pkg.validateSIN(lr_rl2_emprec.EMPLOYEE_SIN)) and lr_rl2_emprec.EMPLOYEE_SIN is not null then /* Bug 6773615 sbachu*/
1901 sv_m := sv_m + 1;
1902 sv_msg(sv_m) := get_lookup_meaning('R_SIN_INVALID');
1903 sv_print := 1;
1904 end if;
1905
1906 if lr_rl2_emprec.RL2_SOURCE_OF_INCOME is null then
1907 sv_c := sv_c + 1;
1908 sv_col(sv_c) := upper('Source of Income');
1909 sv_print := 1;
1910 end if;
1911
1912
1913 if sv_p_y = 'E' then
1914 /* Checking for Negative Balance values for RL2 */
1915 if lr_rl2_emprec.NEGATIVE_BALANCE_FLAG = 'Y' then
1916
1917 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_A,'0')) < 0 then
1918
1919 sv_nb := sv_nb + 1;
1920 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_A';
1921 sv_neg_bal(sv_nb).dbi_value :=
1922 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
1923 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_A));
1924
1925 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box A';
1926 sv_print := 1;
1927 end if;
1928
1929 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_B,'0')) < 0 then
1930
1931 sv_nb := sv_nb + 1;
1932 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_B';
1933 sv_neg_bal(sv_nb).dbi_value :=
1934 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
1935 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_B));
1936
1937 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box B';
1938 sv_print := 1;
1939 end if;
1940
1941 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_C,'0')) < 0 then
1942
1943 sv_nb := sv_nb + 1;
1944 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_C';
1945 sv_neg_bal(sv_nb).dbi_value :=
1946 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
1947 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_C));
1948
1949 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box C';
1950 sv_print := 1;
1951 end if;
1952
1953 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_D,'0')) < 0 then
1954
1955 sv_nb := sv_nb + 1;
1956 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_D';
1957 sv_neg_bal(sv_nb).dbi_value :=
1958 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
1959 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_D));
1960
1961 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box D';
1962 sv_print := 1;
1963 end if;
1964
1965 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_E,'0')) < 0 then
1966
1967 sv_nb := sv_nb + 1;
1968 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_E';
1969 sv_neg_bal(sv_nb).dbi_value :=
1970 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
1971 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_E));
1972
1973 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box E';
1974 sv_print := 1;
1975 end if;
1976
1977 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_F,'0')) < 0 then
1978
1979 sv_nb := sv_nb + 1;
1980 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_F';
1981 sv_neg_bal(sv_nb).dbi_value :=
1982 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
1983 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_F));
1984
1985 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box F';
1986 sv_print := 1;
1987 end if;
1988
1989 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_G,'0')) < 0 then
1990
1991 sv_nb := sv_nb + 1;
1992 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_G';
1993 sv_neg_bal(sv_nb).dbi_value :=
1994 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
1995 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_G));
1996
1997 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box G';
1998 sv_print := 1;
1999 end if;
2000
2001 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_H,'0')) < 0 then
2002
2003 sv_nb := sv_nb + 1;
2004 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_H';
2005 sv_neg_bal(sv_nb).dbi_value :=
2006 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2007 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_H));
2008
2009 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box H';
2010 sv_print := 1;
2011 end if;
2012
2013 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_I,'0')) < 0 then
2014
2015 sv_nb := sv_nb + 1;
2016 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_I';
2017 sv_neg_bal(sv_nb).dbi_value :=
2018 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2019 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_I));
2020
2021 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box I';
2022 sv_print := 1;
2023 end if;
2024
2025 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_J,'0')) < 0 then
2026
2027 sv_nb := sv_nb + 1;
2028 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_J';
2029 sv_neg_bal(sv_nb).dbi_value :=
2030 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2031 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_J));
2032
2033 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box J';
2034 sv_print := 1;
2035 end if;
2036
2037 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_K,'0')) < 0 then
2038
2039 sv_nb := sv_nb + 1;
2040 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_K';
2041 sv_neg_bal(sv_nb).dbi_value :=
2042 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2043 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_K));
2044
2045 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box K';
2046 sv_print := 1;
2047 end if;
2048
2049 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_L,'0')) < 0 then
2050
2051 sv_nb := sv_nb + 1;
2052 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_L';
2053 sv_neg_bal(sv_nb).dbi_value :=
2054 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2055 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_L));
2056
2057 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box L';
2058 sv_print := 1;
2059 end if;
2060
2061 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_M,'0')) < 0 then
2062
2063 sv_nb := sv_nb + 1;
2064 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_M';
2065 sv_neg_bal(sv_nb).dbi_value :=
2066 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2067 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_M));
2068
2069 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box M';
2070 sv_print := 1;
2071 end if;
2072
2073 if fnd_number.canonical_to_number(nvl(lr_rl2_emprec.RL2_BOX_O,'0')) < 0 then
2074
2075 sv_nb := sv_nb + 1;
2076 sv_neg_bal(sv_nb).dbi_name := 'RL2_BOX_O';
2077 sv_neg_bal(sv_nb).dbi_value :=
2078 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2079 ,fnd_number.canonical_to_number(lr_rl2_emprec.RL2_BOX_O));
2080
2081 sv_neg_bal(sv_nb).dbi_short_name := 'RL2 Box O';
2082 sv_print := 1;
2083 end if;
2084
2085 /* Bug 15908332 */
2086 for l_i in 1..further_info_count loop
2087 if l_further_amount(l_i) is not null then
2088 if(l_further_code(l_i) <> 'RL2_FURTHER_INFO_AMOUNT_201') then
2089 if (fnd_number.canonical_to_number(nvl(l_further_amount(l_i),'0')) < 0) then
2090 sv_nb := sv_nb + 1;
2091 sv_neg_bal(sv_nb).dbi_name := l_further_code(l_i);
2092 sv_neg_bal(sv_nb).dbi_value :=
2093 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2094 ,l_further_amount(l_i));
2095 sv_neg_bal(sv_nb).dbi_short_name := l_further_short_name(l_i);
2096 sv_print := 1;
2097 end if;
2098 end if;
2099 end if;
2100 end loop;
2101 /* Bug 15908332 */
2102
2103 end if; /* End of NEGATIVE_BALANCE_FLAG = 'Y' */
2104
2105
2106 if sv_nb > 0 then
2107 sv_m := sv_m + 1;
2108 sv_msg(sv_m) := get_lookup_meaning('R_NEG_BAL');
2109 sv_print := 1;
2110 end if;
2111
2112 end if; /* End of sv_p_y = 'E' */
2113
2114 end if; /* End of sv_report_type = 'RL2' */
2115
2116 /* End of RL2 Employee Validation */
2117
2118 end prov_employee_validation;
2119
2120 /* The procedure prov_employer_validation validates the value
2121 of RL1 Employer */
2122
2123 PROCEDURE prov_employer_validation is
2124 begin
2125 if sv_report_type in ('RL1','CAEOY_RL1_AMEND_PP') then
2126 for i in sv_dbi.first..sv_dbi.last
2127 loop
2128 hr_utility.set_location(sv_dbi(i).dbi_short_name, 601);
2129 if sv_trans_y_n = 'Y' then
2130 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_NUMBER' then
2131 if sv_dbi(i).dbi_value is null then
2132 sv_c := sv_c + 1;
2133 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2134 hr_utility.set_location('Required column '||sv_col(sv_c), 610);
2135 else
2136 if ( ( substr(sv_dbi(i).dbi_value,1,2) <> 'NP' ) or
2137 ( length(sv_dbi(i).dbi_value) <> 8 ) or
2138 ( not ( substr(sv_dbi(i).dbi_value,3) >= '000000' ) and
2139 ( substr(sv_dbi(i).dbi_value,3) <= '999999' ) ) )
2140 then
2141 sv_m := sv_m + 1;
2142 sv_msg(sv_m) := get_lookup_meaning('R_INVALID_TRANS_NO');
2143 hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 611);
2144 end if;
2145 end if;
2146 end if;
2147 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_NAME' and
2148 sv_dbi(i).dbi_value is null then
2149 sv_c := sv_c + 1;
2150 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2151 hr_utility.set_location('Required column '||sv_col(sv_c), 612);
2152 end if;
2153 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_CITY' and
2154 sv_dbi(i).dbi_value is null then
2155 sv_c := sv_c + 1;
2156 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2157 hr_utility.set_location('Required column '||sv_col(sv_c), 613);
2158 end if;
2159 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_PROVINCE' and
2160 sv_dbi(i).dbi_value is null then
2161 sv_c := sv_c + 1;
2162 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2163 hr_utility.set_location('Required column '||sv_col(sv_c), 614);
2164 end if;
2165 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE' and
2166 sv_dbi(i).dbi_value is null then
2167 sv_c := sv_c + 1;
2168 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2169 hr_utility.set_location('Required column '||sv_col(sv_c), 615);
2170 end if;
2171 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_NAME' and
2172 sv_dbi(i).dbi_value is null then
2173 sv_c := sv_c + 1;
2174 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2175 hr_utility.set_location('Required column '||sv_col(sv_c), 616);
2176 end if;
2177 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE' and
2178 sv_dbi(i).dbi_value is null then
2179 sv_c := sv_c + 1;
2180 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2181 hr_utility.set_location('Required column '||sv_col(sv_c), 617);
2182 end if;
2183 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE' and
2184 sv_dbi(i).dbi_value is null then
2185 sv_c := sv_c + 1;
2186 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2187 hr_utility.set_location('Required column '||sv_col(sv_c), 618);
2188 end if;
2189 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE' and
2190 sv_dbi(i).dbi_value is null then
2191 sv_c := sv_c + 1;
2192 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2193 hr_utility.set_location('Required column '||sv_col(sv_c), 619);
2194 end if;
2195 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1' and
2196 sv_dbi(i).dbi_value is null then
2197 sv_c := sv_c + 1;
2198 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2199 hr_utility.set_location('Required column '||sv_col(sv_c), 620);
2200 end if;
2201 if sv_dbi(i).dbi_name = 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE' and
2202 sv_dbi(i).dbi_value is null then
2203 sv_c := sv_c + 1;
2204 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2205 hr_utility.set_location('Required column '||sv_col(sv_c), 621);
2206 end if;
2207 if sv_dbi(i).dbi_name = 'CAEOY_RL1_SOURCE_OF_SLIPS' and
2208 sv_dbi(i).dbi_value is null then
2209 sv_c := sv_c + 1;
2210 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2211 hr_utility.set_location('Required column '||sv_col(sv_c), 622);
2212 end if;
2213 end if;
2214
2215 if sv_dbi(i).dbi_name = 'CAEOY_RL1_QUEBEC_BN' then
2216 sv_qin := sv_dbi(i).dbi_value;
2217 if sv_dbi(i).dbi_value is null then
2218 sv_c := sv_c + 1;
2219 --sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2220 sv_col(sv_c) := get_lookup_meaning('L_QCID_NUMBER');
2221 hr_utility.set_location('Required column '||sv_col(sv_c), 650);
2222 else
2223 if length(sv_dbi(i).dbi_value) <> 16 then
2224 sv_m := sv_m + 1;
2225 sv_msg(sv_m) := get_lookup_meaning('R_QIN_LENGTH');
2226 hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 603);
2227 else
2228 for j in 1..length(sv_dbi(i).dbi_value)
2229 loop
2230 if ( j not in ( 11, 12 ) ) then
2231 if instr('1234567890',substr(sv_dbi(i).dbi_value,j,1)) = 0 then
2232 sv_m := sv_m + 1;
2233 sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
2234 hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 651);
2235 exit;
2236 end if;
2237 else
2238 if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
2239 substr(sv_dbi(i).dbi_value,j,1)) = 0 then
2240 sv_m := sv_m + 1;
2241 sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
2242 hr_utility.set_location(sv_msg(sv_m)|| ' '||sv_dbi(i).dbi_value, 652);
2243 exit;
2244 end if;
2245 end if;
2246 end loop;
2247 end if;
2248 end if;
2249 end if;
2250
2251 if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_NAME' then
2252 sv_employer_name := sv_dbi(i).dbi_value;
2253 if sv_dbi(i).dbi_value is null then
2254 sv_c := sv_c + 1;
2255 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2256 hr_utility.set_location('Required column '||sv_col(sv_c), 653);
2257 end if;
2258 end if;
2259
2260 if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_CITY' then
2261 sv_employer_city := sv_dbi(i).dbi_value;
2262 if sv_dbi(i).dbi_value is null then
2263 sv_c := sv_c + 1;
2264 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2265 hr_utility.set_location('Required column '||sv_col(sv_c), 654);
2266 end if;
2267 end if;
2268
2269 if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_PROVINCE' then
2270 sv_employer_province := sv_dbi(i).dbi_value;
2271 if sv_dbi(i).dbi_value is null then
2272 sv_c := sv_c + 1;
2273 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2274 hr_utility.set_location('Required column '||sv_col(sv_c), 655);
2275 end if;
2276 end if;
2277
2278 if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_POSTAL_CODE' then
2279 sv_employer_postal_code := sv_dbi(i).dbi_value;
2280 if sv_dbi(i).dbi_value is null then
2281 sv_c := sv_c + 1;
2282 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2283 hr_utility.set_location('Required column '||sv_col(sv_c), 656);
2284 end if;
2285 end if;
2286
2287 if sv_dbi(i).dbi_name = 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME' and
2288 sv_dbi(i).dbi_value is null then
2289 sv_c := sv_c + 1;
2290 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2291 hr_utility.set_location('Required column '||sv_col(sv_c), 657);
2292 end if;
2293
2294 if sv_dbi(i).dbi_name = 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE' and
2295 sv_dbi(i).dbi_value is null then
2296 sv_c := sv_c + 1;
2297 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2298 hr_utility.set_location('Required column '||sv_col(sv_c), 658);
2299 end if;
2300
2301 if sv_dbi(i).dbi_name = 'CAEOY_TAXATION_YEAR' and
2302 sv_dbi(i).dbi_value is null then
2303 sv_c := sv_c + 1;
2304 sv_col(sv_c) := sv_dbi(i).dbi_short_name;
2305 hr_utility.set_location('Required column '||sv_col(sv_c), 659);
2306 end if;
2307
2308 if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1' then
2309 sv_employer_address_line1 := sv_dbi(i).dbi_value;
2310 if sv_dbi(i).dbi_value is null then
2311 sv_m := sv_m + 1;
2312 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
2313 end if;
2314 end if;
2315
2316 if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2' then
2317 sv_employer_address_line2 := sv_dbi(i).dbi_value;
2318 end if;
2319
2320 /* 5942058 */
2321 if sv_dbi(i).dbi_name = 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3' then
2322 sv_employer_address_line3 := sv_dbi(i).dbi_value;
2323 end if;
2324
2325 end loop;
2326
2327 end if; -- End if for sv_report_type in ('RL1','CAEOY_RL1_AMEND_PP')
2328
2329 /* Start of RL2 Employer Validation */
2330 hr_utility.set_location('RL2 Employer validation', 699);
2331 if sv_report_type = 'RL2' then
2332
2333 if sv_trans_y_n = 'Y' then
2334
2335 if lr_rl2_transrec.TRANSMITTER_NUMBER is null then
2336 sv_c := sv_c + 1;
2337 sv_col(sv_c) := 'Transmitter Number';
2338 hr_utility.set_location('Required column '||sv_col(sv_c), 710);
2339 else
2340 if ( ( substr(lr_rl2_transrec.TRANSMITTER_NUMBER,1,2) <> 'NP' ) or
2341 ( length(lr_rl2_transrec.TRANSMITTER_NUMBER) <> 8 ) or
2342 ( not ( substr(lr_rl2_transrec.TRANSMITTER_NUMBER,3) >= '000000' ) and
2343 ( substr(lr_rl2_transrec.TRANSMITTER_NUMBER,3) <= '999999' ) ) )
2344 then
2345 sv_m := sv_m + 1;
2346 sv_msg(sv_m) := get_lookup_meaning('R_INVALID_TRANS_NO');
2347 hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.TRANSMITTER_NUMBER, 711);
2348 end if;
2349 end if;
2350
2351 if lr_rl2_transrec.TRANSMITTER_NAME is null then
2352 sv_c := sv_c + 1;
2353 sv_col(sv_c) := 'Transmitter Name';
2354 hr_utility.set_location('Required column '||sv_col(sv_c), 712);
2355 end if;
2356
2357 if lr_rl2_transrec.TRANSMITTER_CITY is null then
2358 sv_c := sv_c + 1;
2359 sv_col(sv_c) := 'Transmitter City';
2360 hr_utility.set_location('Required column '||sv_col(sv_c), 713);
2361 end if;
2362
2363 if lr_rl2_transrec.TRANSMITTER_PROVINCE is null then
2364 sv_c := sv_c + 1;
2365 sv_col(sv_c) := 'Transmitter Province';
2366 hr_utility.set_location('Required column '||sv_col(sv_c), 714);
2367 end if;
2368
2369 if lr_rl2_transrec.TRANSMITTER_POSTAL_CODE is null then
2370 sv_c := sv_c + 1;
2371 sv_col(sv_c) := 'Transmitter Postal Code';
2372 hr_utility.set_location('Required column '||sv_col(sv_c), 715);
2373 end if;
2374
2375 if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_NAME is null then
2376 sv_c := sv_c + 1;
2377 sv_col(sv_c) := 'Technical Contact Name';
2378 hr_utility.set_location('Required column '||sv_col(sv_c), 716);
2379 end if;
2380
2381 if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_PHONE is null then
2382 sv_c := sv_c + 1;
2383 sv_col(sv_c) := 'Technical Contact Phone';
2384 hr_utility.set_location('Required column '||sv_col(sv_c), 717);
2385 end if;
2386
2387 if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_CODE is null then
2388 sv_c := sv_c + 1;
2389 sv_col(sv_c) := 'Technical Contact Area Code';
2390 hr_utility.set_location('Required column '||sv_col(sv_c), 718);
2391 end if;
2392
2393 if lr_rl2_transrec.TRANSMITTER_TECH_CONTACT_LANG is null then
2394 sv_c := sv_c + 1;
2395 sv_col(sv_c) := 'Technical Contact Language';
2396 hr_utility.set_location('Required column '||sv_col(sv_c), 719);
2397 end if;
2398
2399 if lr_rl2_transrec.TRANSMITTER_PACKAGE_TYPE is null then
2400 sv_c := sv_c + 1;
2401 sv_col(sv_c) := 'Transmitter Package Type';
2402 hr_utility.set_location('Required column '||sv_col(sv_c), 720);
2403 end if;
2404
2405 if lr_rl2_transrec.SOURCE_OF_SLIPS is null then
2406 sv_c := sv_c + 1;
2407 sv_col(sv_c) := 'Source of Slips';
2408 hr_utility.set_location('Required column '||sv_col(sv_c), 721);
2409 end if;
2410
2411 if lr_rl2_transrec.TRANSMITTER_ADDRESS_LINE1 is null then
2412 sv_c := sv_c + 1;
2413 sv_col(sv_c) := 'Transmitter Address Line 1';
2414 hr_utility.set_location('Required column '||sv_col(sv_c), 722);
2415 end if;
2416
2417 end if; -- end of sv_trans_y_n = 'Y'
2418
2419 -- Quebec Business Number validation
2420 sv_qin := lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER;
2421 if lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER is null then
2422 sv_c := sv_c + 1;
2423 sv_col(sv_c) := get_lookup_meaning('L_QCID_NUMBER');
2424 hr_utility.set_location('Required column '||sv_col(sv_c), 750);
2425 else
2426 if length(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER) <> 16 then
2427 sv_m := sv_m + 1;
2428 sv_msg(sv_m) := get_lookup_meaning('R_QIN_LENGTH');
2429 hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER, 703);
2430 else
2431 for j in 1..length(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER)
2432 loop
2433 if ( j not in ( 11, 12 ) ) then
2434 if instr('1234567890',substr(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER,j,1)) = 0 then
2435 sv_m := sv_m + 1;
2436 sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
2437 hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER, 751);
2438 exit;
2439 end if;
2440 else
2441 if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
2442 substr(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER,j,1)) = 0 then
2443 sv_m := sv_m + 1;
2444 sv_msg(sv_m) := get_lookup_meaning('R_QIN_INVALID');
2445 hr_utility.set_location(sv_msg(sv_m)|| ' '||lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER, 752);
2446 exit;
2447 end if;
2448 end if;
2449 end loop;
2450 end if;-- End if for length(lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER)
2451 end if; -- End if for lr_rl2_transrec.QUEBEC_BUSINESS_NUMBER is null
2452
2453
2454 sv_employer_name := lr_rl2_transrec.EMPLOYER_NAME;
2455 if lr_rl2_transrec.EMPLOYER_NAME is null then
2456 sv_c := sv_c + 1;
2457 sv_col(sv_c) := 'Employer Name';
2458 hr_utility.set_location('Required column '||sv_col(sv_c), 753);
2459 end if;
2460
2461 sv_employer_city := lr_rl2_transrec.EMPLOYER_CITY;
2462 if lr_rl2_transrec.EMPLOYER_CITY is null then
2463 sv_c := sv_c + 1;
2464 sv_col(sv_c) := 'Employer City';
2465 hr_utility.set_location('Required column '||sv_col(sv_c), 754);
2466 end if;
2467
2468 sv_employer_province := lr_rl2_transrec.EMPLOYER_PROVINCE;
2469 if lr_rl2_transrec.EMPLOYER_PROVINCE is null then
2470 sv_c := sv_c + 1;
2471 sv_col(sv_c) := 'Employer Province';
2472 hr_utility.set_location('Required column '||sv_col(sv_c), 755);
2473 end if;
2474
2475 sv_employer_postal_code := lr_rl2_transrec.EMPLOYER_POSTAL_CODE;
2476 if lr_rl2_transrec.EMPLOYER_POSTAL_CODE is null then
2477 sv_c := sv_c + 1;
2478 sv_col(sv_c) := 'Employer Postal Code';
2479 hr_utility.set_location('Required column '||sv_col(sv_c), 756);
2480 end if;
2481
2482 if lr_rl2_transrec.TRANSMITTER_ACCT_CONTACT_NAME is null then
2483 sv_c := sv_c + 1;
2484 sv_col(sv_c) := 'Accounting Contact Name';
2485 hr_utility.set_location('Required column '||sv_col(sv_c), 757);
2486 end if;
2487
2488 if lr_rl2_transrec.TRANSMITTER_ACCT_CONTACT_PHONE is null then
2489 sv_c := sv_c + 1;
2490 sv_col(sv_c) := 'Accounting Contact Phone';
2491 hr_utility.set_location('Required column '||sv_col(sv_c), 758);
2492 end if;
2493
2494 if lr_rl2_transrec.REPORTING_YEAR is null then
2495 sv_c := sv_c + 1;
2496 sv_col(sv_c) := 'Taxation Year';
2497 hr_utility.set_location('Required column '||sv_col(sv_c), 759);
2498 end if;
2499
2500 sv_employer_address_line1 := lr_rl2_transrec.EMPLOYER_ADD_LINE1;
2501 if lr_rl2_transrec.EMPLOYER_ADD_LINE1 is null then
2502 sv_m := sv_m + 1;
2503 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
2504 end if;
2505
2506 sv_employer_address_line2 := lr_rl2_transrec.EMPLOYER_ADD_LINE2;
2507
2508 /* 5942058 */
2509 sv_employer_address_line3 := lr_rl2_transrec.EMPLOYER_ADD_LINE3;
2510
2511 end if; -- end if for sv_report_type = 'RL2' then
2512 /* End of RL2 Employer Validation */
2513
2514 end prov_employer_validation;
2515
2516 /* The procedure fed_employee_validation validates the value of
2517 T4/T4A Employee */
2518
2519 PROCEDURE fed_employee_validation is
2520 l_emp_first_name varchar2(240);
2521 l_emp_last_name varchar2(240);
2522 lv_overlimit number(1) := 0;
2523 lv_missing_adr number(1) := 0;
2524 lv_multi_jurisdiction number(2) := 0;
2525 lv_person_id number(10) := 0;
2526 lv_asg_act_id number(10) := 0;
2527 /* 4142751 */
2528 addr_line_2_3 number(10) := 0;
2529 begin
2530 for i in sv_dbi.first..sv_dbi.last
2531 loop
2532 hr_utility.set_location(to_char(i)||'. '||sv_dbi(i).dbi_name||' '||
2533 sv_dbi(i).dbi_value||' '||sv_dbi(i).dbi_short_name, 999 );
2534 if sv_dbi(i).dbi_name = 'CAEOY_PERSON_ID' then
2535 lv_person_id := to_number(sv_dbi(i).dbi_value);
2536 end if;
2537
2538 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_NUMBER' then
2539 sv_employee_no := sv_dbi(i).dbi_value;
2540 end if;
2541
2542 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_FIRST_NAME' then
2543 l_emp_first_name := sv_dbi(i).dbi_value;
2544 if l_emp_first_name is null then
2545 sv_c := sv_c + 1;
2546 sv_col(sv_c) := upper(sv_dbi(i).dbi_short_name);
2547 sv_print := 1;
2548 end if;
2549 end if;
2550 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_LAST_NAME' then
2551 l_emp_last_name := sv_dbi(i).dbi_value;
2552 if l_emp_last_name is null then
2553 sv_c := sv_c + 1;
2554 sv_col(sv_c) := upper(sv_dbi(i).dbi_short_name);
2555 sv_print := 1;
2556 end if;
2557 end if;
2558 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE1' and
2559 sv_dbi(i).dbi_value is null then
2560 if lv_missing_adr = 0 then
2561 sv_m := sv_m + 1;
2562 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
2563 sv_print := 1;
2564 lv_missing_adr := 1;
2565 end if;
2566 end if;
2567 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_CITY' and
2568 sv_dbi(i).dbi_value is null then
2569 if lv_missing_adr = 0 then
2570 sv_m := sv_m + 1;
2571 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
2572 sv_print := 1;
2573 lv_missing_adr := 1;
2574 end if;
2575 end if;
2576 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_PROVINCE' and
2577 sv_dbi(i).dbi_value is null then
2578 if lv_missing_adr = 0 then
2579 sv_m := sv_m + 1;
2580 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
2581 sv_print := 1;
2582 lv_missing_adr := 1;
2583 end if;
2584 end if;
2585 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_POSTAL_CODE' and
2586 sv_dbi(i).dbi_value is null then
2587 if lv_missing_adr = 0 then
2588 sv_m := sv_m + 1;
2589 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
2590 sv_print := 1;
2591 lv_missing_adr := 1;
2592 end if;
2593 end if;
2594
2595 /* Changes start - Bug 4142751 */
2596
2597 if ( sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE1' and
2598 sv_dbi(i).dbi_value is not null )
2599 then
2600 if ( length(sv_dbi(i).dbi_value) > 30 ) then
2601 sv_m := sv_m + 1;
2602 sv_msg(sv_m) := get_lookup_meaning('R_EXCEEDING_ADDR_LINE_1');
2603 sv_print := 1;
2604 end if;
2605 end if;
2606
2607 if ( sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE2' and
2608 sv_dbi(i).dbi_value is not null )
2609 then
2610 addr_line_2_3 := addr_line_2_3 + length(sv_dbi(i).dbi_value) ;
2611 end if;
2612
2613 if ( sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_ADDRESS_LINE3' ) then
2614 if ( sv_dbi(i).dbi_value is not null )
2615 then
2616 addr_line_2_3 := addr_line_2_3 + length(sv_dbi(i).dbi_value) ;
2617 end if;
2618 if ( addr_line_2_3 > 30 ) then
2619 sv_m := sv_m + 1;
2620 sv_msg(sv_m) := get_lookup_meaning('R_EXCEEDING_ADDR_LINE_2_3');
2621 sv_print := 1;
2622 end if;
2623 end if;
2624
2625 /* Bug 6837510 */
2626 if sv_dbi(i).dbi_name = 'CAEOY_T4_LOCATION_ID' and sv_dbi(i).dbi_value is null then
2627 sv_m := sv_m + 1;
2628 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_LOC');
2629 sv_print := 1;
2630 end if;
2631
2632 /* Changes End - Bug 4142751 */
2633 if sv_dbi(i).dbi_name = 'CAEOY_EMPLOYEE_SIN' then
2634 sv_employee_sin := substr(sv_dbi(i).dbi_value,1,3) ||' '||
2635 substr(sv_dbi(i).dbi_value,4,3) ||' '||
2636 substr(sv_dbi(i).dbi_value,7,3) ;
2637 if sv_dbi(i).dbi_value is null then
2638 sv_c := sv_c + 1;
2639 sv_col(sv_c) := upper(sv_dbi(i).dbi_short_name);
2640 sv_print := 1;
2641 end if;
2642 if ( not pay_ca_yeer_pkg.validateSIN(sv_dbi(i).dbi_value)) and sv_dbi(i).dbi_value is not null then /* Bug 6773615 sbachu*/
2643 sv_m := sv_m + 1;
2644 sv_msg(sv_m) := get_lookup_meaning('R_SIN_INVALID');
2645 sv_print := 1;
2646 end if;
2647 end if;
2648 if sv_dbi(i).dbi_name = 'CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD' then
2649 sv_total_earnings :=nvl(sv_total_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2650 --lv_multi_jurisdiction := lv_multi_jurisdiction + 1;
2651 hr_utility.set_location('Multi Jurisdiction : '||
2652 to_char(lv_multi_jurisdiction), 999 );
2653 end if;
2654 if sv_dbi(i).dbi_name = 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD' then
2655 sv_pensionable_earnings :=nvl(sv_pensionable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2656 end if;
2657 /* Commented because bug# 1701287
2658 --if sv_dbi(i).dbi_name = 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD' then
2659 --sv_pensionable_earnings :=
2660 -- to_char(fnd_number.canonical_to_number(nvl(sv_pensionable_earnings,'0'),'999,999,990.00') +
2661 --fnd_number.canonical_to_number(sv_dbi(i).dbi_value),'999,999,990.00');
2662 --end if;
2663 */
2664 if sv_dbi(i).dbi_name = 'CAEOY_CPP_EE_WITHHELD_PER_JD_GRE_YTD' then
2665 sv_ded_reported_16 :=nvl(sv_ded_reported_16,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2666 end if;
2667 /*
2668 if sv_dbi(i).dbi_name = 'CAEOY_QPP_EE_WITHHELD_PER_JD_GRE_YTD' then
2669 sv_ded_reported_16 :=
2670 to_char(fnd_number.canonical_to_number(nvl(sv_ded_reported_16,'0'),'999,999,990.00') +
2671 fnd_number.canonical_to_number(sv_dbi(i).dbi_value),'999,999,990.00');
2672 end if;
2673 */
2674
2675 /* bug 5552744 */
2676
2677 if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' OR
2678 sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2679
2680 select context into sv_emp_jurisdiction
2681 from ff_archive_item_contexts
2682 where archive_item_id = sv_dbi(i).archive_item_id
2683 and context_id = sv_context_id;
2684
2685 hr_utility.trace('Emp JD is '||sv_emp_jurisdiction);
2686
2687 if sv_emp_jurisdiction = 'QC' then
2688
2689 if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
2690 sv_qc_insurable_earnings := nvl(sv_qc_insurable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2691 hr_utility.trace('QC Insurable Earning : '|| sv_qc_insurable_earnings);
2692 end if;
2693
2694 if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2695 sv_qc_ded_reported_18 := nvl(sv_qc_ded_reported_18,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2696 hr_utility.trace('QC Withheld : '|| sv_qc_ded_reported_18);
2697 end if;
2698
2699 else
2700
2701 if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
2702 sv_insurable_earnings := nvl(sv_insurable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2703 hr_utility.trace('FED Insurable Earning : '|| sv_insurable_earnings);
2704 end if;
2705
2706 if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2707 sv_ded_reported_18 := nvl(sv_ded_reported_18,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2708 hr_utility.trace('FED Withheld : '|| sv_ded_reported_18);
2709 end if;
2710
2711 end if; /* 'QC' */
2712
2713 end if;
2714
2715
2716 /* bug 5552744 */
2717 /*
2718 if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
2719 sv_insurable_earnings := nvl(sv_insurable_earnings,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2720 end if;
2721 if sv_dbi(i).dbi_name = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2722 sv_ded_reported_18 :=nvl(sv_ded_reported_18,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2723 end if;
2724 */
2725
2726 if sv_dbi(i).dbi_name = 'CAEOY_CPP_BASIC_EXEMPTION_PER_JD_GRE_YTD' then
2727 sv_cpp_basic_exemption :=
2728 nvl(sv_cpp_basic_exemption,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2729 end if;
2730 if sv_dbi(i).dbi_name = 'CAEOY_CPP_EXEMPT_PER_JD_GRE_YTD' then
2731 sv_cpp_exempt_bal :=
2732 nvl(sv_cpp_exempt_bal,0) + fnd_number.canonical_to_number(sv_dbi(i).dbi_value);
2733 end if;
2734 if sv_p_y = 'E' then
2735 if instr(sv_dbi(i).dbi_name, 'GRE_YTD') > 0 and
2736 fnd_number.canonical_to_number(nvl(sv_dbi(i).dbi_value,'0')) < 0
2737 then
2738 if ((sv_report_type = 'T4') or
2739 (sv_report_type = 'CAEOY_T4_AMEND_PP')) then
2740 begin
2741 select context
2742 into sv_emp_jurisdiction
2743 from ff_archive_item_contexts
2744 where archive_item_id = sv_dbi(i).archive_item_id
2745 and context_id = sv_context_id;
2746
2747 exception
2748 when others then
2749 null;
2750 end;
2751 end if;
2752 sv_nb := sv_nb + 1;
2753 if ((sv_report_type = 'T4') or
2754 (sv_report_type = 'CAEOY_T4_AMEND_PP')) then
2755 sv_neg_bal(sv_nb).dbi_name := sv_emp_jurisdiction;
2756 else
2757 sv_neg_bal(sv_nb).dbi_name := sv_dbi(i).dbi_name;
2758 end if;
2759 sv_neg_bal(sv_nb).dbi_value :=
2760 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2761 ,fnd_number.canonical_to_number(sv_dbi(i).dbi_value));
2762 sv_neg_bal(sv_nb).dbi_short_name := sv_dbi(i).dbi_short_name;
2763 sv_print := 1;
2764 end if;
2765
2766 if (sv_dbi(i).dbi_name = 'CAEOY_T4A_NONBOX_FOOTNOTE') and
2767 (fnd_number.canonical_to_number(nvl(sv_dbi(i).dbi_value,'0')) < 0) then
2768
2769 sv_nb := sv_nb + 1;
2770 sv_neg_bal(sv_nb).dbi_name := sv_dbi(i).dbi_name;
2771 sv_neg_bal(sv_nb).dbi_value :=
2772 pay_us_employee_payslip_web.get_format_value(sv_b_g_id
2773 ,fnd_number.canonical_to_number(sv_dbi(i).dbi_value));
2774 sv_neg_bal(sv_nb).dbi_short_name := sv_dbi(i).dbi_short_name;
2775 sv_print := 1;
2776
2777 end if;
2778
2779 end if;
2780 end loop;
2781
2782 -- commented by rgottipa
2783 --sv_employee_name := substr(l_emp_last_name,1,120)||', '||substr(l_emp_first_name,1,118);-- #2406070
2784 sv_employee_name := substr(l_emp_last_name,1,30)||', '||substr(l_emp_first_name,1,30); --#10261964
2785 sv_cpp_ded_required := ((sv_pensionable_earnings - sv_cpp_exempt ) * sv_cpp_rate / 100 );
2786
2787 if sv_cpp_ded_required > sv_cpp_max_exempt then
2788 sv_cpp_ded_required := sv_cpp_max_exempt;
2789 lv_overlimit := 1;
2790 end if;
2791
2792 if sv_cpp_ded_required < 0 then
2793 sv_cpp_ded_required := 0.00;
2794 end if;
2795
2796 /* bug 5552744 */
2797
2798 sv_qc_ei_ded_required := (( sv_qc_insurable_earnings * sv_ppip_ei_rate )/ 100 );
2799 sv_qc_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ppip_ei_rate / 100 );
2800
2801 sv_ei_ded_required := (( sv_insurable_earnings * sv_ei_rate )/ 100 );
2802 sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ei_rate / 100 );
2803
2804 hr_utility.trace('QC EI Dedn Req. ' || sv_qc_ei_ded_required);
2805 hr_utility.trace('QC EI Max Exempt ' || sv_qc_ei_max_exempt);
2806 hr_utility.trace('QC EI Withheld ' || sv_qc_ded_reported_18);
2807
2808 hr_utility.trace('FED EI Dedn Req. ' || sv_ei_ded_required);
2809 hr_utility.trace('FED EI Max Exempt ' || sv_ei_max_exempt);
2810 hr_utility.trace('FED EI Withheld ' || sv_ded_reported_18);
2811
2812 /* overlimit condition */
2813 if sv_ei_ded_required > sv_ei_max_exempt then
2814 sv_ei_ded_required := sv_ei_max_exempt;
2815 lv_overlimit := 1;
2816 end if;
2817
2818 if sv_qc_ei_ded_required > sv_qc_ei_max_exempt then
2819 sv_qc_ei_ded_required := sv_qc_ei_max_exempt;
2820 lv_overlimit := 1;
2821 end if;
2822
2823 /* under withheld */
2824 if sv_ei_ded_required < 0 then
2825 sv_ei_deficiency := sv_ded_reported_18 ;
2826 end if;
2827
2828 if sv_qc_ei_ded_required < 0 then
2829 sv_qc_ei_deficiency := sv_qc_ded_reported_18 ;
2830 end if;
2831
2832 /* difference */
2833
2834 sv_qc_insurable_earnings := sv_qc_insurable_earnings;
2835
2836 hr_utility.trace('sv_ei_ded_required : '|| sv_ei_ded_required);
2837 hr_utility.trace('sv_ded_reported_18 : '|| sv_ded_reported_18);
2838 hr_utility.trace('sv_insurable_earnings : '|| sv_insurable_earnings);
2839
2840 hr_utility.trace('sv_qc_ei_ded_required : '|| sv_qc_ei_ded_required);
2841 hr_utility.trace('sv_qc_ded_reported_18 : '|| sv_qc_ded_reported_18);
2842 hr_utility.trace('sv_qc_insurable_earnings : '|| sv_qc_insurable_earnings);
2843
2844 sv_ei_deficiency := sv_ei_ded_required - sv_ded_reported_18 ;
2845 sv_qc_ei_deficiency := sv_qc_ei_ded_required - sv_qc_ded_reported_18 ;
2846
2847 hr_utility.trace('sv_ei_deficiency : '|| sv_ei_deficiency);
2848 hr_utility.trace('sv_qc_ei_deficiency : '|| sv_qc_ei_deficiency);
2849
2850 if sv_ei_deficiency < 0 then
2851 sv_ei_deficiency := 0;
2852 end if;
2853
2854 if sv_qc_ei_deficiency < 0 then
2855 sv_qc_ei_deficiency := 0;
2856 end if;
2857
2858 /* bug 5552744 */
2859
2860 /* Added by ssmukher for incorporating diff EI rate for employees working in Quebec
2861 but belonging to Non Quebec province*/
2862 /*
2863 if sv_jurisdiction = 'QC' then
2864 sv_ei_ded_required := (( sv_insurable_earnings * sv_ppip_ei_rate )/ 100 );
2865 else
2866 sv_ei_ded_required := (( sv_insurable_earnings * sv_ei_rate )/ 100 );
2867 end if;
2868
2869 if sv_jurisdiction = 'QC' then
2870 sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ppip_ei_rate / 100 );
2871 else
2872 sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ei_rate / 100 );
2873 end if;
2874
2875 if sv_ei_ded_required > sv_ei_max_exempt then
2876 sv_ei_ded_required := sv_ei_max_exempt;
2877 lv_overlimit := 1;
2878 end if;
2879
2880 if sv_ei_ded_required < 0 then
2881 sv_ei_deficiency := sv_ded_reported_18 ;
2882 end if;
2883 */
2884
2885 /* sv_cpp_qpp_deficiency :=
2886 to_char( (fnd_number.canonical_to_number(sv_ded_reported_16,'999,999,990.00')
2887 - sv_cpp_ded_required ),'999,990.00');
2888
2889 sv_ei_deficiency :=
2890 to_char( (fnd_number.canonical_to_number(sv_ded_reported_18,'999,999,990.00')
2891 - sv_ei_ded_required ),'999,990.00');
2892 */
2893
2894 sv_cpp_qpp_deficiency := (sv_cpp_ded_required - sv_ded_reported_16);
2895
2896 /* The deficiency fields should not display over-payments */
2897
2898 if sv_cpp_qpp_deficiency < 0 then
2899 sv_cpp_qpp_deficiency := 0;
2900 end if;
2901
2902 if sv_nb > 0 then
2903 sv_m := sv_m + 1;
2904 sv_msg(sv_m) := get_lookup_meaning('R_NEG_BAL');
2905 sv_print := 1;
2906 end if;
2907 if lv_overlimit > 0 then
2908 sv_m := sv_m + 1;
2909 sv_msg(sv_m) := get_lookup_meaning('R_OVERLIMIT_BAL');
2910 sv_print := 1;
2911 end if;
2912 --if lv_multi_jurisdiction > 1 then
2913 if get_multi_jd(lv_person_id) > 1 and sv_p_y = 'E' then
2914 sv_m := sv_m + 1;
2915 sv_msg(sv_m) := get_lookup_meaning('R_EMP_MULTI_JD');
2916 sv_print := 1;
2917 end if;
2918 if sv_p_y = 'P' then
2919 /* When option is PIER Report, the following messages should print
2920 if they fullfill their conditions except Negative Balance. */
2921 sv_nb := 0;
2922 sv_m := 0;
2923 sv_c := 0;
2924 sv_print := 0;
2925 sv_msg.delete;
2926 sv_col.delete;
2927 sv_neg_bal.delete;
2928 end if;
2929
2930 /* if ( ( fnd_number.canonical_to_number( sv_cpp_qpp_deficiency, '999,990.00') < 0 and
2931 abs(fnd_number.canonical_to_number( sv_cpp_qpp_deficiency, '999,990.00')) > 1 ) or
2932 ( fnd_number.canonical_to_number( sv_ei_deficiency, '999,990.00') < 0 and
2933 abs(fnd_number.canonical_to_number( sv_ei_deficiency, '999,990.00')) > 1 ) ) then
2934 */
2935
2936 if ( (sv_cpp_qpp_deficiency > 1) or (sv_ei_deficiency > 1) or (sv_qc_ei_deficiency > 1)) then
2937
2938 sv_print := 1;
2939
2940 if to_number(sv_reporting_year) -
2941 to_number(to_char(sv_date_of_birth,'YYYY') ) = 18 then
2942 sv_m := sv_m + 1;
2943 sv_msg(sv_m) := get_lookup_meaning('R_EMP_TURNED_18');
2944 end if;
2945 if to_number(sv_reporting_year) -
2946 to_number(to_char(sv_date_of_birth,'YYYY') ) = 70 then
2947 sv_m := sv_m + 1;
2948 sv_msg(sv_m) := get_lookup_meaning('R_EMP_TURNED_70');
2949 end if;
2950 if sv_reporting_year = to_char(sv_hire_date,'YYYY') then
2951 sv_m := sv_m + 1;
2952 sv_msg(sv_m) := get_lookup_meaning('R_EMP_HIRED');
2953 end if;
2954 if sv_reporting_year = to_char(sv_terminate_date,'YYYY') then
2955 sv_m := sv_m + 1;
2956 sv_msg(sv_m) := get_lookup_meaning('R_EMP_TERMINATED');
2957 end if;
2958
2959 if sv_p_y = 'P' then
2960
2961 if ( sv_cpp_block = 'Y' ) then
2962 sv_m := sv_m + 1;
2963 sv_msg(sv_m) := get_lookup_meaning('R_CPP_BLOCK');
2964 end if;
2965
2966 if ( sv_ei_block = 'Y' ) then
2967 sv_m := sv_m + 1;
2968 sv_msg(sv_m) := get_lookup_meaning('R_EI_BLOCK');
2969 end if;
2970
2971 /* Bug 13542043 - Added code to display Cpp Election, Revocation dates */
2972 if (sv_cpp_date.first is not null) then
2973 for i in sv_cpp_date.first..sv_cpp_date.last
2974 loop
2975 if (sv_cpp_date(i).cpp_election_date is NOT NULL) then
2976 sv_m := sv_m + 1;
2977 sv_msg(sv_m) := get_lookup_meaning('R_ELECTION_DATE_BLOCK') ||' ''' || to_char(sv_cpp_date(i).cpp_election_date,'DD-MON-YYYY') ||'''';
2978 end if;
2979
2980 if (sv_cpp_date(i).cpp_revocation_date is NOT NULL) then
2981 sv_m := sv_m + 1;
2982 sv_msg(sv_m) := get_lookup_meaning('R_REVOCATION_DATE_BLOCK') ||' ''' || to_char(sv_cpp_date(i).cpp_revocation_date,'DD-MON-YYYY') ||'''';
2983 end if;
2984 end loop;
2985 end if;
2986
2987 end if;
2988
2989 end if;
2990
2991 if sv_p_y = 'E' then
2992
2993 if ( sv_cpp_block = 'Y' ) then
2994 sv_m := sv_m + 1;
2995 sv_msg(sv_m) := get_lookup_meaning('R_CPP_BLOCK');
2996 sv_print := 1;
2997 end if;
2998
2999 if ( sv_ei_block = 'Y' ) then
3000 sv_m := sv_m + 1;
3001 sv_msg(sv_m) := get_lookup_meaning('R_EI_BLOCK');
3002 sv_print := 1;
3003 end if;
3004
3005 /* Bug 13542043 - Added code to display Cpp Election, Revocation dates */
3006 if (sv_cpp_date.first is not null) then
3007 for i in sv_cpp_date.first..sv_cpp_date.last
3008 loop
3009 if (sv_cpp_date(i).cpp_election_date is NOT NULL) then
3010 sv_m := sv_m + 1;
3011 sv_msg(sv_m) := get_lookup_meaning('R_ELECTION_DATE_BLOCK') ||' ''' || to_char(sv_cpp_date(i).cpp_election_date,'DD-MON-YYYY') ||'''';
3012 sv_print := 1;
3013 end if;
3014
3015 if (sv_cpp_date(i).cpp_revocation_date is NOT NULL) then
3016 sv_m := sv_m + 1;
3017 sv_msg(sv_m) := get_lookup_meaning('R_REVOCATION_DATE_BLOCK') ||' ''' || to_char(sv_cpp_date(i).cpp_revocation_date,'DD-MON-YYYY') ||'''';
3018 sv_print := 1;
3019 end if;
3020 end loop;
3021 end if;
3022
3023 end if;
3024
3025 end fed_employee_validation;
3026
3027 /* The procedure fed_employer_validation validates the value of
3028 T4/T4A Employer */
3029
3030 PROCEDURE fed_employer_validation ( p_dbi_name in varchar2,
3031 p_dbi_value in varchar2,
3032 p_dbi_short_name in varchar2) is
3033 begin
3034 --hr_utility.set_location(p_dbi_short_name, 201);
3035 if sv_trans_y_n = 'Y' then
3036 if p_dbi_name = 'CAEOY_TRANSMITTER_NUMBER' then
3037 if p_dbi_value is null then
3038 sv_c := sv_c + 1;
3039 sv_col(sv_c) := p_dbi_short_name;
3040 hr_utility.set_location('Required column '||sv_col(sv_c), 210);
3041 else
3042 if ( ( substr(p_dbi_value,1,2) <> 'MM' ) or
3043 ( length(p_dbi_value) <> 8 ) or
3044 ( not ( substr(p_dbi_value,3) >= '000000' ) and
3045 ( substr(p_dbi_value,3) <= '999999' ) ) )
3046 then
3047 sv_m := sv_m + 1;
3048 sv_msg(sv_m) := get_lookup_meaning('R_INVALID_TRANS_NO');
3049 hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 211);
3050 end if;
3051 end if;
3052 end if;
3053 if p_dbi_name = 'CAEOY_TRANSMITTER_NAME' and
3054 p_dbi_value is null then
3055 sv_c := sv_c + 1;
3056 sv_col(sv_c) := p_dbi_short_name;
3057 hr_utility.set_location('Required column '||sv_col(sv_c), 212);
3058 end if;
3059 if p_dbi_name = 'CAEOY_TRANSMITTER_CITY' and
3060 p_dbi_value is null then
3061 sv_c := sv_c + 1;
3062 sv_col(sv_c) := p_dbi_short_name;
3063 hr_utility.set_location('Required column '||sv_col(sv_c), 213);
3064 end if;
3065 if p_dbi_name = 'CAEOY_TRANSMITTER_PROVINCE' and
3066 p_dbi_value is null then
3067 sv_c := sv_c + 1;
3068 sv_col(sv_c) := p_dbi_short_name;
3069 hr_utility.set_location('Required column '||sv_col(sv_c), 214);
3070 end if;
3071 if p_dbi_name = 'CAEOY_TRANSMITTER_POSTAL_CODE' and
3072 p_dbi_value is null then
3073 sv_c := sv_c + 1;
3074 sv_col(sv_c) := p_dbi_short_name;
3075 hr_utility.set_location('Required column '||sv_col(sv_c), 215);
3076 end if;
3077 if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_NAME' and
3078 p_dbi_value is null then
3079 sv_c := sv_c + 1;
3080 sv_col(sv_c) := p_dbi_short_name;
3081 hr_utility.set_location('Required column '||sv_col(sv_c), 216);
3082 end if;
3083 if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_PHONE' and
3084 p_dbi_value is null then
3085 sv_c := sv_c + 1;
3086 sv_col(sv_c) := p_dbi_short_name;
3087 hr_utility.set_location('Required column '||sv_col(sv_c), 217);
3088 end if;
3089 if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_AREA_CODE' and
3090 p_dbi_value is null then
3091 sv_c := sv_c + 1;
3092 sv_col(sv_c) := p_dbi_short_name;
3093 hr_utility.set_location('Required column '||sv_col(sv_c), 218);
3094 end if;
3095 if p_dbi_name = 'CAEOY_TECHNICAL_CONTACT_LANGUAGE' and
3096 p_dbi_value is null then
3097 sv_c := sv_c + 1;
3098 sv_col(sv_c) := p_dbi_short_name;
3099 hr_utility.set_location('Required column '||sv_col(sv_c), 219);
3100 end if;
3101 if p_dbi_name = 'CAEOY_TRANSMITTER_TYPE_INDICATOR' and
3102 p_dbi_value is null then
3103 sv_c := sv_c + 1;
3104 sv_col(sv_c) := p_dbi_short_name;
3105 hr_utility.set_location('Required column '||sv_col(sv_c), 220);
3106 end if;
3107 end if;
3108 if p_dbi_name = 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER' then
3109 sv_busi_no := p_dbi_value;
3110 if p_dbi_value is null then
3111 sv_c := sv_c + 1;
3112 sv_col(sv_c) := p_dbi_short_name;
3113 hr_utility.set_location('Required column '||sv_col(sv_c), 250);
3114 else
3115 if length(p_dbi_value) <> 15 then
3116 sv_m := sv_m + 1;
3117 sv_msg(sv_m) := get_lookup_meaning('R_BN_LENGTH');
3118 hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 203);
3119 else
3120 for i in 1..length(p_dbi_value)
3121 loop
3122 if ( i not in ( 10, 11 ) ) then
3123 if instr('1234567890',substr(p_dbi_value,i,1)) = 0 then
3124 sv_m := sv_m + 1;
3125 sv_msg(sv_m) := get_lookup_meaning('R_BN_INVALID');
3126 hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 251);
3127 exit;
3128 end if;
3129 else
3130 if instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',substr(p_dbi_value,i,1)) =
3131 0 then
3132 sv_m := sv_m + 1;
3133 sv_msg(sv_m) := get_lookup_meaning('R_BN_INVALID');
3134 hr_utility.set_location(sv_msg(sv_m)|| ' '||p_dbi_value, 252);
3135 exit;
3136 end if;
3137 end if;
3138
3139 end loop;
3140 end if;
3141 end if;
3142 end if;
3143 if p_dbi_name = 'CAEOY_EMPLOYER_NAME' then
3144 sv_employer_name := p_dbi_value;
3145 if p_dbi_value is null then
3146 sv_c := sv_c + 1;
3147 sv_col(sv_c) := p_dbi_short_name;
3148 hr_utility.set_location('Required column '||sv_col(sv_c), 253);
3149 end if;
3150 end if;
3151 if p_dbi_name = 'CAEOY_EMPLOYER_CITY' then
3152 sv_employer_city := p_dbi_value;
3153 if p_dbi_value is null then
3154 sv_c := sv_c + 1;
3155 sv_col(sv_c) := p_dbi_short_name;
3156 hr_utility.set_location('Required column '||sv_col(sv_c), 254);
3157 end if;
3158 end if;
3159 if p_dbi_name = 'CAEOY_EMPLOYER_PROVINCE' then
3160 sv_employer_province := p_dbi_value;
3161 if p_dbi_value is null then
3162 sv_c := sv_c + 1;
3163 sv_col(sv_c) := p_dbi_short_name;
3164 hr_utility.set_location('Required column '||sv_col(sv_c), 255);
3165 end if;
3166 end if;
3167 if p_dbi_name = 'CAEOY_EMPLOYER_POSTAL_CODE' then
3168 sv_employer_postal_code := p_dbi_value;
3169 if p_dbi_value is null then
3170 sv_c := sv_c + 1;
3171 sv_col(sv_c) := p_dbi_short_name;
3172 hr_utility.set_location('Required column '||sv_col(sv_c), 256);
3173 end if;
3174 end if;
3175 if p_dbi_name = 'CAEOY_ACCOUNTING_CONTACT_NAME' and
3176 p_dbi_value is null then
3177 sv_c := sv_c + 1;
3178 sv_col(sv_c) := p_dbi_short_name;
3179 hr_utility.set_location('Required column '||sv_col(sv_c), 257);
3180 end if;
3181 if p_dbi_name = 'CAEOY_ACCOUNTING_CONTACT_PHONE' and
3182 p_dbi_value is null then
3183 sv_c := sv_c + 1;
3184 sv_col(sv_c) := p_dbi_short_name;
3185 hr_utility.set_location('Required column '||sv_col(sv_c), 258);
3186 end if;
3187 if p_dbi_name = 'CAEOY_TAXATION_YEAR' and
3188 p_dbi_value is null then
3189 sv_c := sv_c + 1;
3190 sv_col(sv_c) := p_dbi_short_name;
3191 hr_utility.set_location('Required column '||sv_col(sv_c), 259);
3192 end if;
3193 /*
3194 if p_dbi_name = 'CAEOY_FEDERAL_YOUTH_HIRE_PROGRAM_INDICATOR' and
3195 p_dbi_value is null then
3196 sv_c := sv_c + 1;
3197 sv_col(sv_c) := p_dbi_short_name;
3198 hr_utility.set_location('Required column '||sv_col(sv_c), 260);
3199
3200 end if;
3201 */
3202 if p_dbi_name = 'CAEOY_EMPLOYER_ADDRESS_LINE1' then
3203 sv_employer_address_line1 := p_dbi_value;
3204 if p_dbi_value is null then
3205 sv_m := sv_m + 1;
3206 sv_msg(sv_m) := get_lookup_meaning('R_MISSING_ADR');
3207 end if;
3208 end if;
3209 if p_dbi_name = 'CAEOY_EMPLOYER_ADDRESS_LINE2' then
3210 sv_employer_address_line2 := p_dbi_value;
3211 end if;
3212
3213 /* 5942058 */
3214 if p_dbi_name = 'CAEOY_EMPLOYER_ADDRESS_LINE3' then
3215 sv_employer_address_line3 := p_dbi_value;
3216 end if;
3217 end fed_employer_validation;
3218
3219 /* The procedure provincial_process is executed when user has selected option
3220 Provincial. This procedure is called from the main procedure pier_yeer.*/
3221
3222 PROCEDURE provincial_process ( fp_pre in number, fp_b_g_id in number) is
3223
3224 /* The cursor cur_rl_pay_act retrieves archived payroll_action_id(PACTID).
3225 If Prov Reporting Establishment(PRE) is selected, this cursor selects
3226 PACTID for that PRE otherwise it selects all PACTID for all archived
3227 PRE */
3228
3229 -- Need to modify the cursor cur_rl_pay_act to enable RL2 PRE (Modified)
3230 cursor cur_rl_pay_act is
3231 select ppa.payroll_action_id ,
3232 hoi.org_information1 business_number,
3233 hou.organization_id,
3234 hou.name,
3235 ppa.payroll_id,
3236 ppa.effective_date,
3237 ppa.report_type,
3238 hoi.org_information2
3239 from hr_organization_information hoi,
3240 hr_all_organization_units hou,
3241 pay_payroll_actions ppa
3242 where hou.business_group_id = fp_b_g_id
3243 and hoi.organization_id = hou.organization_id
3244 and hoi.org_information_context = 'Prov Reporting Est'
3245 and ppa.business_group_id = fp_b_g_id
3246 and hoi.organization_id =
3247 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
3248 ppa.legislative_parameters)
3249 and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
3250 ppa.legislative_parameters) =
3251 nvl(to_char(fp_pre),pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
3252 ppa.legislative_parameters))
3253 and ppa.report_type in ('RL1', 'CAEOY_RL1_AMEND_PP','RL2')
3254 and ppa.action_status = 'C'
3255 and to_char(ppa.effective_date,'YYYY') = sv_reporting_year
3256 and to_char(ppa.effective_date,'DD-MM') = '31-12'
3257 order by hou.organization_id, ppa.payroll_action_id;
3258
3259 /* The cursor cur_rl_trans_y_n is used to verify whether retrieved GRE is
3260 the type of transmitter or not */
3261
3262 cursor cur_rl_trans_y_n ( cp_org_id in number,
3263 cp_qin in varchar2 ) is
3264 select 'Y'
3265 from hr_organization_information
3266 where organization_id = cp_org_id
3267 and org_information2 = cp_qin
3268 and org_information3 = 'Y'
3269 and org_information_context = 'Prov Reporting Est';
3270
3271 /* The cursor cur_rl_dbi retrieves archive items and its value depending on
3272 context ( PACTID or ASGACTID) */
3273
3274 cursor cur_rl_dbi ( cp_context in number ) is
3275 select distinct rtrim(ltrim(fdi.user_name)),
3276 rtrim(ltrim(fai.value)),
3277 initcap(rtrim(ltrim(replace(replace(replace(replace(replace(replace(replace(
3278 fdi.user_name,'CAEOY'),'RL1_'),'PER_YTD'),'PER_JD_YTD'),'AMOUNT'),'EMPLOYEE_'),
3279 '_',' ')))) req_col /* bug 4393047 - Added AMOUNT */
3280 from ff_database_items fdi
3281 ,ff_archive_items fai
3282 where fai.user_entity_id = fdi.user_entity_id
3283 and fai.context1 = to_char(cp_context)
3284 and fdi.user_name like 'CAEOY%'
3285 order by req_col; -- Bug 15908452
3286
3287 /* The cursor cur_rl_cpp_periods retrives the QPP periods for an Employee. */
3288
3289 cursor cur_rl_cpp_periods ( cp_payroll_id in number ) is
3290 select count(regular_payment_date)
3291 from per_time_periods target
3292 where payroll_id = cp_payroll_id
3293 and to_char( target.regular_payment_date,'YYYY' ) = sv_reporting_year;
3294
3295 /* The cursor cur_rl_asg_act retrieves all assignment action ids for input
3296 PACTID that have not been amended. Added sort option to fix bug#3977930 */
3297
3298 cursor cur_rl_asg_act (cp_pactid in number) is
3299 select paa.assignment_action_id,
3300 paa.assignment_id,
3301 paa.serial_number person_id,
3302 paa.action_status
3303 from pay_assignment_actions paa,
3304 pay_payroll_actions ppa,
3305 per_all_people_f ppf
3306 where paa.payroll_action_id = cp_pactid
3307 and ppa.payroll_action_id = paa.payroll_action_id
3308 and ppa.business_group_id = fp_b_g_id
3309 and ppa.effective_date between ppf.effective_start_date /*10316246 sbachu*/
3310 and ppf.effective_end_date
3311 and not exists
3312 (select 1
3313 from pay_assignment_actions paa_amend,
3314 pay_payroll_actions ppa_amend
3315 where paa_amend.payroll_action_id > cp_pactid
3316 and paa.serial_number = paa_amend.serial_number
3317 and ppa_amend.payroll_action_id = paa_amend.payroll_action_id
3318 and ppa_amend.report_type = 'CAEOY_RL1_AMEND_PP'
3319 and ppa_amend.business_group_id = fp_b_g_id
3320 and ppa_amend.action_status = 'C'
3321 and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters) =
3322 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa_amend.legislative_parameters)
3323 and to_char(ppa_amend.effective_date,'YYYY') = sv_reporting_year
3324 and to_char(ppa_amend.effective_date,'YYYY') = to_char(ppa.effective_date,'YYYY')
3325 and to_char(ppa_amend.effective_date,'DD-MM') = '31-12'
3326 and to_char(ppa_amend.effective_date,'DD-MM') = to_char(ppa.effective_date,'DD-MM'))
3327 and exists
3328 (select 1
3329 from per_assignments_f paf
3330 where paf.assignment_id = paa.assignment_id
3331 and paf.effective_start_date <= ppa.effective_date
3332 and paf.effective_end_date >= trunc(ppa.effective_date,'Y')
3333 )
3334 and ppf.person_id = paa.serial_number
3335 and ppf.effective_start_date <= ppa.effective_date
3336 and ppf.effective_end_date >= trunc(ppa.effective_date,'Y')
3337 order by ppf.last_name,ppf.first_name,ppf.middle_names;
3338
3339
3340 /* The cursor cur_rl_dob retrieves the Birth Date and Hired Date
3341 for an employee. */
3342
3343 cursor cur_rl_dob ( cp_person_id in number,
3344 cp_effective_date in date ) is
3345 select ppf.date_of_birth,
3346 ppf.original_date_of_hire
3347 from per_all_people_f ppf
3348 where ppf.person_id = cp_person_id
3349 and cp_effective_date between ppf.effective_start_date
3350 and ppf.effective_end_date;
3351
3352 /* The cursor cur_rl_dob retrieves the termination Date if any.*/
3353
3354 cursor cur_rl_terminate ( cp_person_id in number ) is
3355 select actual_termination_date
3356 from per_periods_of_service
3357 where person_id = cp_person_id
3358 and actual_termination_date is not null;
3359
3360 /* The cursor cur_rl_qpp_block is used to verify whether an employee has
3361 QPP Block or not. */
3362
3363 cursor cur_rl_qpp_block (cp_bg_id in number,
3364 cp_person_id in number,
3365 cp_effective_date in date ) is
3366 select qpp_exempt_flag
3367 from per_all_assignments_f paaf,
3368 pay_ca_emp_prov_tax_info_f pcefti
3369 where paaf.person_id = cp_person_id
3370 and to_char(cp_effective_date,'YYYY') between
3371 to_char(paaf.effective_start_date,'YYYY') and
3372 to_char(paaf.effective_end_date, 'YYYY' )
3373 and pcefti.assignment_id = paaf.assignment_id
3374 and pcefti.business_group_id+0 = cp_bg_id
3375 and to_char(cp_effective_date,'YYYY') between
3376 to_char(pcefti.effective_start_date,'YYYY') and
3377 to_char(pcefti.effective_end_date,'YYYY')
3378 and pcefti.qpp_exempt_flag = 'Y';
3379
3380 /* The cursor cur_rl1_qpip_block is used to verify whether an employee has
3381 PPIP Block or not. */
3382
3383 cursor cur_rl_qpip_block (cp_bg_id in number,
3384 cp_person_id in number,
3385 cp_effective_date in date ) is
3386 select ppip_exempt_flag
3387 from per_all_assignments_f paaf,
3388 pay_ca_emp_prov_tax_info_f pcefti
3389 where paaf.person_id = cp_person_id
3390 and to_char(cp_effective_date,'YYYY') between
3391 to_char(paaf.effective_start_date,'YYYY') and
3392 to_char(paaf.effective_end_date, 'YYYY' )
3393 and pcefti.assignment_id = paaf.assignment_id
3394 and pcefti.business_group_id+0 = cp_bg_id
3395 and to_char(cp_effective_date,'YYYY') between
3396 to_char(pcefti.effective_start_date,'YYYY') and
3397 to_char(pcefti.effective_end_date,'YYYY')
3398 and pcefti.ppip_exempt_flag = 'Y';
3399
3400 cursor cur_rl_tax_unit_id( cp_asg_id in number ) is
3401 select nvl(hsck.segment1, hsck.segment11)
3402 from per_all_assignments_f paf,
3403 hr_soft_coding_keyflex hsck
3404 where paf.assignment_id = cp_asg_id
3405 and add_months(trunc(to_date(sv_reporting_year,'YYYY'),'Y'),12)-1 between
3406 paf.effective_start_date and paf.effective_end_date
3407 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
3408
3409 /* Cursor to get the RL2 Transmitter and Employer Info */
3410 CURSOR cur_rl2_transmitter(cp_bg_id number,
3411 cp_pact_id number) IS
3412 select * from PAY_CA_EOY_RL2_TRANS_INFO_V
3413 where business_group_id = cp_bg_id
3414 and payroll_action_id = cp_pact_id;
3415
3416 /* Cursor to get the RL2 Employee Info */
3417 CURSOR cur_rl2_employee(cp_bg_id number,
3418 cp_asgact_id number) IS
3419 select * from PAY_CA_EOY_RL2_EMPLOYEE_INFO_V
3420 where business_group_id = cp_bg_id
3421 and assignment_action_id = cp_asgact_id;
3422
3423 cursor cur_rl_nonbox_footnote(cp_asgact_id number) is
3424 select pai.action_information5,
3425 flv.meaning,
3426 'CAEOY_RL1_NONBOX_FOOTNOTE'
3427 from pay_action_information pai,
3428 fnd_lookup_types flt,
3429 fnd_lookup_values flv
3430 where pai.action_context_id = cp_asgact_id
3431 and pai.action_context_type = 'AAP'
3432 and pai.jurisdiction_code = 'QC'
3433 and pai.action_information_category = 'CA FOOTNOTES'
3434 and pai.action_information6 = 'RL1'
3435 and flt.lookup_type = 'PAY_CA_RL1_NONBOX_FOOTNOTES'
3436 and flv.lookup_type = flt.lookup_type
3437 and flv.language = userenv('LANG')
3438 and flv.enabled_flag = 'Y'
3439 and flv.lookup_code = pai.action_information4;
3440
3441 /* Bug 6837510 */
3442 cursor cur_get_location(cp_asgact_id number) is
3443 select paf.LOCATION_ID
3444 from pay_assignment_actions paa,
3445 per_assignments_f paf
3446 where paa.assignment_action_id = cp_asgact_id
3447 and paf.assignment_id = paa.assignment_id;
3448
3449 /* Bug 15908332 */
3450 cursor c_further_codes(cp_asgact_id number) is
3451 select pai_emp.ACTION_INFORMATION4 code, pai_emp.ACTION_INFORMATION5 value,
3452 initcap(rtrim(ltrim(replace(replace(replace(pai_emp.ACTION_INFORMATION4,'RL2_'),'AMOUNT_'),'_',' ')))) short_name
3453 FROM pay_action_information pai_emp
3454 ,pay_assignment_actions paa_arch
3455 ,pay_payroll_actions ppa_arch
3456 where pai_emp.action_context_id=cp_asgact_id
3457 AND pai_emp.action_context_id = paa_arch.assignment_action_id
3458 AND pai_emp.action_information_category = 'CAEOY RL2 FURTHER INFO'
3459 AND pai_emp.assignment_id = paa_arch.assignment_id
3460 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
3461 AND paa_arch.action_status = 'C'
3462 AND ppa_arch.report_type IN ('RL2','CAEOY_RL2_AMEND_PP')
3463 AND ppa_arch.report_qualifier IN ('CAEOYRL2','CAEOY_RL2_AMEND_PPQ')
3464 AND ppa_arch.report_category = 'ARCHIVE'
3465 AND ppa_arch.action_type = 'X'
3466 AND ppa_arch.action_status = 'C'
3467 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id;
3468 /* Bug 15908332 */
3469
3470 l_print_y_n number(1) := 0;
3471 l_transmitter_y_n char(1);
3472
3473 l_payroll_action_id number(20);
3474 l_first_employee number(20);
3475 l_business_number varchar2(180);
3476 l_org_id number(20);
3477 l_pre_name varchar2(180);
3478 l_payroll_id number(9);
3479 l_effective_date date;
3480
3481 l_dbi_name varchar2(240);
3482 l_dbi_value varchar2(240);
3483 l_dbi_short_name varchar2(240);
3484
3485 l_assignment_action_id number(15);
3486 l_assignment_id number(10);
3487 l_person_id varchar2(30);
3488 l_action_status varchar2(1);
3489 l_tax_unit_id varchar2(60);
3490 i number(3);
3491
3492 begin
3493
3494 open cur_rl_pay_act;
3495 loop
3496 fetch cur_rl_pay_act into
3497 l_payroll_action_id,
3498 l_business_number,
3499 l_org_id,
3500 l_pre_name,
3501 l_payroll_id,
3502 l_effective_date,
3503 sv_report_type,
3504 sv_qin;
3505
3506 exit when cur_rl_pay_act%notfound;
3507
3508 hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 510);
3509 hr_utility.set_location('ORG ID ' || to_char(l_org_id), 520);
3510 hr_utility.set_location('BUSI NO. ' || l_business_number, 530);
3511 hr_utility.set_location('PRE NAME ' || l_pre_name, 540);
3512 hr_utility.set_location('QIN ' || sv_qin, 550);
3513
3514 sv_busi_no := l_business_number;
3515 sv_pre_name := l_pre_name;
3516
3517 l_transmitter_y_n := 'N';
3518
3519 open cur_rl_trans_y_n(l_org_id, sv_qin);
3520 fetch cur_rl_trans_y_n into l_transmitter_y_n;
3521 close cur_rl_trans_y_n;
3522
3523 sv_trans_y_n := l_transmitter_y_n;
3524 hr_utility.set_location('Transmitter ? ' || sv_trans_y_n, 550);
3525
3526 if (sv_report_type <> 'CAEOY_RL1_AMEND_PP') then
3527
3528 if sv_p_y = 'E' then
3529
3530 initialize_static_var('R');
3531
3532 /* Added for RL2 Exception Report */
3533 if sv_report_type = 'RL2' then
3534
3535 open cur_rl2_transmitter(fp_b_g_id,l_payroll_action_id);
3536 i := 0;
3537 fetch cur_rl2_transmitter into lr_rl2_transrec;
3538 if cur_rl2_transmitter%FOUND then
3539 i := 1;
3540 end if;
3541 close cur_rl2_transmitter;
3542
3543 else
3544 open cur_rl_dbi(l_payroll_action_id);
3545
3546 hr_utility.set_location(' Cursor DBI Before Validation ', 560);
3547 i := 0;
3548 loop
3549 fetch cur_rl_dbi into l_dbi_name,
3550 l_dbi_value,
3551 l_dbi_short_name;
3552 exit when cur_rl_dbi%notfound;
3553
3554 i := i + 1;
3555 sv_dbi(i).dbi_name := l_dbi_name;
3556 sv_dbi(i).dbi_value := l_dbi_value;
3557 sv_dbi(i).dbi_short_name := l_dbi_short_name;
3558 end loop;
3559
3560 hr_utility.set_location(' Cursor DBI After Validation ', 570);
3561 close cur_rl_dbi;
3562
3563 end if; /* End of RL2 report type validation */
3564
3565 if i <> 0 then
3566 prov_employer_validation;
3567 employer_header;
3568 print_employer;
3569 end if;
3570
3571 end if; /* end of validation sv_p_y = 'E' */
3572
3573 end if; /* end of sv_report_type validation */
3574
3575 open cur_rl_cpp_periods(l_payroll_id);
3576 fetch cur_rl_cpp_periods into sv_no_of_cpp_periods;
3577 close cur_rl_cpp_periods;
3578
3579 /* 10163858 - Code changes begin */
3580 -- l_first_employee := 0;
3581
3582 if (sv_report_type <> 'CAEOY_RL1_AMEND_PP') then
3583 l_first_employee := 0;
3584 end if;
3585
3586 if (sv_report_type = 'CAEOY_RL1_AMEND_PP') then
3587 format_data('<table border=0>');
3588 end if;
3589 /* 10163858 - Code changes end */
3590
3591 open cur_rl_asg_act(l_payroll_action_id);
3592 loop
3593 fetch cur_rl_asg_act into l_assignment_action_id,
3594 l_assignment_id,
3595 l_person_id,
3596 l_action_status;
3597 exit when cur_rl_asg_act%notfound;
3598
3599 if l_first_employee = 0 then
3600 employee_header;
3601 l_first_employee := 1;
3602 end if;
3603
3604 sv_asg_id := l_assignment_id;
3605
3606 initialize_static_var('E');
3607
3608 /* Bug 6837510 */
3609 open cur_get_location (l_assignment_action_id);
3610 fetch cur_get_location into sv_location_id;
3611 close cur_get_location;
3612
3613 hr_utility.set_location(' CUR_TAX_UNIT_ID', 587 );
3614
3615 open cur_rl_tax_unit_id( l_assignment_id );
3616 fetch cur_rl_tax_unit_id into l_tax_unit_id;
3617 close cur_rl_tax_unit_id;
3618
3619 sv_gre := l_tax_unit_id;
3620
3621 hr_utility.set_location(' CUR_DOB', 588 );
3622
3623 open cur_rl_dob( l_person_id, l_effective_date);
3624 fetch cur_rl_dob into sv_date_of_birth, sv_hire_date;
3625 close cur_rl_dob;
3626
3627 hr_utility.set_location('CUR_TERMINATE',577);
3628
3629 open cur_rl_terminate( l_person_id);
3630 fetch cur_rl_terminate into sv_terminate_date;
3631 close cur_rl_terminate;
3632
3633 hr_utility.set_location('CUR_QPP_BLOCK',566);
3634
3635 open cur_rl_qpp_block(fp_b_g_id, l_person_id, l_effective_date);
3636 fetch cur_rl_qpp_block into sv_cpp_block;
3637 close cur_rl_qpp_block;
3638
3639 hr_utility.set_location('CUR_PPIP_BLOCK',566);
3640
3641 open cur_rl_qpip_block(fp_b_g_id, l_person_id, l_effective_date);
3642 fetch cur_rl_qpip_block into sv_ppip_block;
3643 close cur_rl_qpip_block;
3644
3645 hr_utility.set_location('CUR_DBI',555);
3646
3647 if sv_report_type = 'RL2' then
3648
3649 open cur_rl2_employee(fp_b_g_id,
3650 l_assignment_action_id);
3651 fetch cur_rl2_employee into lr_rl2_emprec;
3652
3653 if cur_rl2_employee%FOUND then
3654 i := 1;
3655 end if;
3656
3657 close cur_rl2_employee;
3658 /* Bug 15908332 */
3659 further_info_count := 0;
3660 for rec in c_further_codes(l_assignment_action_id)
3661 loop
3662 if rec.value is not null then
3663 further_info_count := further_info_count+1;
3664 l_further_code(further_info_count):=rec.code;
3665 l_further_amount(further_info_count):=rec.value;
3666 l_further_short_name(further_info_count):=rec.short_name;
3667 end if;
3668 end loop;
3669 /* Bug 15908332 */
3670
3671 else
3672
3673 open cur_rl_dbi(l_assignment_action_id);
3674 hr_utility.set_location(' Cursor Assignment actions '||to_char(l_assignment_action_id), 199);
3675 i := 0;
3676 loop
3677 fetch cur_rl_dbi into l_dbi_name,
3678 l_dbi_value,
3679 l_dbi_short_name;
3680 exit when cur_rl_dbi%notfound;
3681
3682 i := i + 1;
3683 sv_dbi(i).dbi_name := l_dbi_name;
3684 sv_dbi(i).dbi_value := l_dbi_value;
3685 /* 4393047 - Added code to correct headings for negative balances on report output */
3686 CASE
3687
3688 WHEN ( instr(upper(l_dbi_short_name),'BOX') > 0 ) THEN
3689 l_dbi_short_name := substr(l_dbi_short_name,1,3)||upper(substr(l_dbi_short_name,4));
3690
3691 WHEN ( instr(upper(l_dbi_short_name),'QPP') > 0 ) THEN
3692 if ( instr(upper(l_dbi_short_name),'QPP EE') > 0 ) THEN
3693 l_dbi_short_name := upper(substr(l_dbi_short_name,1,6)) ||initcap(substr(l_dbi_short_name,7)) ;
3694 else
3695 l_dbi_short_name := upper(substr(l_dbi_short_name,1,3)) ||initcap(substr(l_dbi_short_name,4));
3696 end if;
3697
3698 WHEN ( instr(upper(l_dbi_short_name),'CPP') > 0 ) THEN
3699 if ( instr(upper(l_dbi_short_name),'CPP EE') > 0 ) THEN
3700 l_dbi_short_name := upper(substr(l_dbi_short_name,1,6)) ||initcap(substr(l_dbi_short_name,7)) ;
3701 else
3702 l_dbi_short_name := upper(substr(l_dbi_short_name,1,3)) ||initcap(substr(l_dbi_short_name,4));
3703 end if;
3704
3705 WHEN ( instr(upper(l_dbi_short_name),'PPIP') > 0 ) THEN
3706 if ( instr(upper(l_dbi_short_name),'PPIP EE') > 0 ) THEN
3707 l_dbi_short_name := upper(substr(l_dbi_short_name,1,7)) ||initcap(substr(l_dbi_short_name,8)) ;
3708 else
3709 l_dbi_short_name := upper(substr(l_dbi_short_name,1,4)) ||initcap(substr(l_dbi_short_name,5));
3710 end if;
3711
3712 WHEN ( instr(upper(l_dbi_short_name),'EI') > 0 ) THEN
3713 if ( instr(upper(l_dbi_short_name),'EI EE') > 0 ) THEN
3714 l_dbi_short_name := upper(substr(l_dbi_short_name,1,5)) ||initcap(substr(l_dbi_short_name,6)) ;
3715 else
3716 l_dbi_short_name := upper(substr(l_dbi_short_name,1,2)) ||initcap(substr(l_dbi_short_name,3));
3717 end if;
3718 ELSE null;
3719 END CASE;
3720 sv_dbi(i).dbi_short_name := l_dbi_short_name;
3721
3722 end loop;
3723 close cur_rl_dbi;
3724
3725 open cur_rl_nonbox_footnote(l_assignment_action_id);
3726 loop
3727 fetch cur_rl_nonbox_footnote into l_dbi_value,
3728 l_dbi_short_name,
3729 l_dbi_name;
3730 exit when cur_rl_nonbox_footnote%notfound;
3731
3732 i := i + 1;
3733 sv_dbi(i).dbi_name := l_dbi_name;
3734 sv_dbi(i).dbi_value := l_dbi_value;
3735 sv_dbi(i).dbi_short_name := l_dbi_short_name;
3736
3737 end loop;
3738 close cur_rl_nonbox_footnote;
3739
3740 end if; /* End of sv_report_type = 'RL2' */
3741
3742
3743 if i <> 0 then
3744 prov_employee_validation;
3745 if sv_print = 1 then
3746 print_employee;
3747 end if;
3748 end if;
3749
3750 end loop;
3751 close cur_rl_asg_act;
3752
3753 format_data('</table>');
3754
3755 end loop;
3756 close cur_rl_pay_act;
3757
3758 end provincial_process;
3759
3760 /* The procedure federal_process is executed when user has selected option
3761 Federal. This procedure is called from the main procedure pier_yeer.*/
3762
3763 PROCEDURE federal_process ( fp_gre in number, fp_b_g_id in number) is
3764
3765 /* The cursor cur_pay_act retrieves archived payroll_action_id(PACTID).
3766 If GRE is selected, this cursor selects PACTID for that GRE otherwise
3767 it selects all PACTID for all archived GRE */
3768
3769 cursor cur_pay_act is
3770 select ppa.payroll_action_id ,
3771 hoi.org_information1 business_number,
3772 hou.organization_id,
3773 hou.name,
3774 ppa.payroll_id,
3775 ppa.effective_date,
3776 ppa.report_type
3777 from hr_organization_information hoi,
3778 hr_all_organization_units hou,
3779 pay_payroll_actions ppa
3780 where hou.business_group_id = fp_b_g_id
3781 and hoi.organization_id = hou.organization_id
3782 and hoi.org_information_context = 'Canada Employer Identification'
3783 and ppa.business_group_id = fp_b_g_id
3784 and hoi.organization_id = pycadar_pkg.get_parameter('TRANSFER_GRE',
3785 ppa.legislative_parameters )
3786 and ( ( hoi.organization_id = fp_gre ) OR
3787 ( fp_gre is null and hoi.organization_id = hoi.organization_id ))
3788 and ( ( ppa.report_type in ('T4', 'CAEOY_T4_AMEND_PP', 'T4A', 'CAEOY_T4A_AMEND_PP' ) and sv_p_y = 'E' ) or
3789 ( ppa.report_type in ('T4', 'CAEOY_T4_AMEND_PP') and sv_p_y = 'P' ) )
3790 and ppa.action_status = 'C'
3791 and to_char(ppa.effective_date,'YYYY') = sv_reporting_year
3792 and to_char(ppa.effective_date,'DD-MM') = '31-12'
3793 order by hou.organization_id, ppa.payroll_action_id;
3794
3795 /* The cursor cur_trans_y_n is used to verify whether retrieved GRE is
3796 the type of transmitter or not */
3797
3798 cursor cur_trans_y_n ( cp_org_id in number ) is
3799 select 'Y'
3800 from hr_organization_information
3801 where organization_id = cp_org_id
3802 and org_information1 = 'Y'
3803 and org_information_context = 'Fed Magnetic Reporting';
3804
3805 /* The cursor cur_dbi retrieves archive items and its value depending on
3806 context ( PACTID or ASGACTID) for T4 and T4A. */
3807
3808 -- T4 is changed as _T4 by rgottipa for 10085168
3809
3810 cursor cur_dbi ( cp_context in number ) is
3811 select distinct rtrim(ltrim(fdi.user_name)),
3812 rtrim(ltrim(fai.value)),
3813 initcap(rtrim(ltrim(replace(replace(replace(replace(replace(
3814 replace(replace( fdi.user_name,'CAEOY'),'T4A'),'_T4'),'PER_GRE_YTD')
3815 ,'PER_JD_GRE_YTD'),'EMPLOYEE_'),'_',' ')))) req_col,
3816 fai.archive_item_id
3817 from ff_database_items fdi
3818 ,ff_archive_items fai
3819 where fai.user_entity_id = fdi.user_entity_id
3820 and fai.context1 = to_char(cp_context)
3821 and fdi.user_name like 'CAEOY%'
3822 order by req_col; -- order by added by rgottipa by 10085168
3823
3824 /* Cursor to find the Employment Jurisdiction code for the employee */
3825 cursor get_jurisdiction_code( cp_context in number ) is
3826 select rtrim(ltrim(fai.value))
3827 from ff_database_items fdi
3828 ,ff_archive_items fai
3829 where fai.user_entity_id = fdi.user_entity_id
3830 and fai.context1 = to_char(cp_context)
3831 and fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
3832
3833 /* The cursor cur_cpp_periods retrives the CPP periods for an Employee. */
3834
3835 cursor cur_cpp_periods ( cp_payroll_id in number ) is
3836 select count(regular_payment_date)
3837 from per_time_periods target
3838 where payroll_id = cp_payroll_id
3839 and to_char( target.regular_payment_date,'YYYY' ) = sv_reporting_year;
3840
3841 /* The cursor cur_asg_act retrieves all assignment action ids for input
3842 PACTID that have not been amended. Added sort option to fix bug#3977930 */
3843
3844 /* For bug 5703506, added DISTINCT to the query. Also had to added the
3845 person_name in the select and other columns in select in the order by.
3846
3847 The is because of the date join on the table per_people_f. If there
3848 are date track records in that table there will be multiple records */
3849
3850 cursor cur_asg_act (cp_pactid in number) is
3851 select DISTINCT
3852 paa.assignment_action_id,
3853 paa.assignment_id,
3854 paa.serial_number person_id,
3855 paa.action_status,
3856 ppf.last_name,ppf.first_name,ppf.middle_names
3857 from pay_assignment_actions paa,
3858 pay_payroll_actions ppa,
3859 per_all_people_f ppf
3860 where paa.payroll_action_id = cp_pactid
3861 and ppa.payroll_action_id = paa.payroll_action_id
3862 and ppa.business_group_id = fp_b_g_id
3863 and ppa.effective_date between ppf.effective_start_date
3864 and ppf.effective_end_date
3865 and not exists
3866 (select 1
3867 from pay_assignment_actions paa_amend,
3868 pay_payroll_actions ppa_amend
3869 where paa_amend.payroll_action_id > cp_pactid
3870 and paa.serial_number = paa_amend.serial_number
3871 and ppa_amend.payroll_action_id = paa_amend.payroll_action_id
3872 and ppa_amend.report_type in ('CAEOY_T4_AMEND_PP','CAEOY_T4A_AMEND_PP')
3873 and ppa_amend.business_group_id = fp_b_g_id
3874 and ppa_amend.action_status = 'C'
3875 and pycadar_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters) =
3876 pycadar_pkg.get_parameter('TRANSFER_GRE',ppa_amend.legislative_parameters)
3877 and to_char(ppa_amend.effective_date,'YYYY') = sv_reporting_year
3878 and to_char(ppa_amend.effective_date,'YYYY') = to_char(ppa.effective_date,'YYYY')
3879 and to_char(ppa_amend.effective_date,'DD-MM') = '31-12'
3880 and to_char(ppa_amend.effective_date,'DD-MM') = to_char(ppa.effective_date,'DD-MM'))
3881 and exists
3882 (select 1
3883 from per_assignments_f paf
3884 where paf.assignment_id = paa.assignment_id
3885 and paf.effective_start_date <= ppa.effective_date
3886 and paf.effective_end_date >= trunc(ppa.effective_date,'Y')
3887 )
3888 and ppf.person_id = paa.serial_number
3889 and ppf.effective_start_date <= ppa.effective_date
3890 and ppf.effective_end_date >= trunc(ppa.effective_date,'Y')
3891 order by ppf.last_name,ppf.first_name,ppf.middle_names,
3892 paa.assignment_action_id,
3893 paa.assignment_id,
3894 paa.serial_number,
3895 paa.action_status;
3896
3897 /* The cursor cur_rl_dob retrieves the Birth Date and Hired Date
3898 for an employee. */
3899
3900 cursor cur_dob ( cp_person_id in number,
3901 cp_effective_date in date ) is
3902 select ppf.date_of_birth,
3903 ppf.original_date_of_hire
3904 from per_all_people_f ppf
3905 where ppf.person_id = cp_person_id
3906 and cp_effective_date between ppf.effective_start_date
3907 and ppf.effective_end_date;
3908
3909 /* The cursor cur_rl_dob retrieves the termination Date if any.*/
3910
3911 cursor cur_terminate ( cp_person_id in number ) is
3912 select actual_termination_date
3913 from per_periods_of_service
3914 where person_id = cp_person_id
3915 and actual_termination_date is not null;
3916
3917 /* The cursor cur_cpp_block is used to verify whether an employee has
3918 CPP Block or not. */
3919
3920 cursor cur_cpp_block ( cp_bg_id in number,
3921 cp_person_id in number,
3922 cp_effective_date in date ) is
3923 select cpp_qpp_exempt_flag
3924 from per_all_assignments_f paaf, pay_ca_emp_fed_tax_info_f pcefti
3925 where paaf.person_id = cp_person_id
3926 and to_char(cp_effective_date,'YYYY') between
3927 to_char(paaf.effective_start_date,'YYYY') and
3928 to_char(paaf.effective_end_date, 'YYYY' )
3929 and pcefti.assignment_id = paaf.assignment_id
3930 and pcefti.business_group_id+0 = cp_bg_id
3931 and to_char(cp_effective_date,'YYYY') between
3932 to_char(pcefti.effective_start_date,'YYYY') and
3933 to_char(pcefti.effective_end_date,'YYYY')
3934 and pcefti.cpp_qpp_exempt_flag = 'Y';
3935
3936 /* Bug 13542043 - Cursor to fetch Cpp Eelection, Revocation Dates of the employee. */
3937
3938 cursor cur_cpp_date_block ( cp_bg_id in number,
3939 cp_person_id in number,
3940 cp_effective_date in date ) is
3941 select distinct pcefti.CPP_ELECTION_DATE, /*Bug 13542043 sbachu*/
3942 pcefti.CPP_REVOCATION_DATE
3943 from per_all_assignments_f paaf, pay_ca_emp_fed_tax_info_f pcefti
3944 where paaf.person_id = cp_person_id
3945 and to_char(cp_effective_date,'YYYY') between
3946 to_char(paaf.effective_start_date,'YYYY') and
3947 to_char(paaf.effective_end_date, 'YYYY' )
3948 and pcefti.assignment_id = paaf.assignment_id
3949 and pcefti.business_group_id+0 = cp_bg_id
3950 and to_char(cp_effective_date,'YYYY') between
3951 to_char(pcefti.effective_start_date,'YYYY') and
3952 to_char(pcefti.effective_end_date,'YYYY')
3953 and ( ( /*Bug 13542043 sbachu*/
3954 pcefti.CPP_ELECTION_DATE is not null
3955 and to_number(to_char(pcefti.CPP_ELECTION_DATE,'YYYY')) <= to_number(to_char(cp_effective_date,'YYYY'))
3956 ) or
3957 (
3958 pcefti.CPP_REVOCATION_DATE is not null
3959 and to_number(to_char(pcefti.CPP_REVOCATION_DATE,'YYYY')) <= to_number(to_char(cp_effective_date,'YYYY'))
3960 )
3961 );
3962
3963 /* The cursor cur_ei_block is used to verify whether an employee has
3964 EI Block or not. */
3965
3966 cursor cur_ei_block ( cp_bg_id in number,
3967 cp_person_id in number,
3968 cp_effective_date in date ) is
3969 select ei_exempt_flag
3970 from per_all_assignments_f paaf, pay_ca_emp_fed_tax_info_f pcefti
3971 where paaf.person_id = cp_person_id
3972 and to_char(cp_effective_date,'YYYY') between
3973 to_char(paaf.effective_start_date,'YYYY') and
3974 to_char(paaf.effective_end_date, 'YYYY' )
3975 and pcefti.assignment_id = paaf.assignment_id
3976 and pcefti.business_group_id+0 = cp_bg_id
3977 and to_char(cp_effective_date,'YYYY') between
3978 to_char(pcefti.effective_start_date,'YYYY') and
3979 to_char(pcefti.effective_end_date,'YYYY')
3980 and pcefti.ei_exempt_flag = 'Y';
3981
3982 cursor cur_t4a_nonbox_footnote(cp_asgact_id number) is
3983 select pai.action_information5,
3984 flv.meaning,
3985 'CAEOY_T4A_NONBOX_FOOTNOTE'
3986 from pay_action_information pai,
3987 fnd_lookup_types flt,
3988 fnd_lookup_values flv
3989 where pai.action_context_id = cp_asgact_id
3990 and pai.action_context_type = 'AAP'
3991 and pai.action_information_category = 'CA FOOTNOTES'
3992 and pai.action_information6 = 'T4A'
3993 and flt.lookup_type = 'PAY_CA_T4A_NONBOX_FOOTNOTES'
3994 and flv.lookup_type = flt.lookup_type
3995 and flv.language = userenv('LANG')
3996 and flv.enabled_flag = 'Y'
3997 and flv.lookup_code = pai.action_information4;
3998
3999 l_print_y_n number(1) := 0;
4000 l_transmitter_y_n char(1);
4001
4002 l_payroll_action_id number(20);
4003 l_business_number varchar2(180);
4004 l_first_employee number(20);
4005 l_org_id number(20);
4006 l_gre_name varchar2(180);
4007 l_payroll_id number(9);
4008 l_effective_date date;
4009
4010 l_dbi_name varchar2(240);
4011 l_dbi_value varchar2(240);
4012 l_dbi_short_name varchar2(240);
4013 l_arc_item_id number(15);
4014
4015 l_assignment_action_id number(15);
4016 l_assignment_id number(10);
4017 l_person_id varchar2(30);
4018 l_action_status varchar2(1);
4019 i number(3);
4020
4021 l_last_name per_people_f.last_name%TYPE;
4022 l_first_name per_people_f.first_name%TYPE;
4023 l_middle_names per_people_f.middle_names%TYPE;
4024 /* Bug 13542043 */
4025 l_cpp_election_date date;
4026 l_cpp_revocation_date date;
4027
4028 begin
4029 open cur_pay_act;
4030 loop
4031 fetch cur_pay_act into l_payroll_action_id,
4032 l_business_number,
4033 l_org_id,
4034 l_gre_name,
4035 l_payroll_id,
4036 l_effective_date,
4037 sv_report_type;
4038
4039 exit when cur_pay_act%notfound;
4040
4041 hr_utility.set_location('PACTID ' || to_char(l_payroll_action_id), 110);
4042 hr_utility.set_location('ORG ID ' || to_char(l_org_id), 120);
4043 hr_utility.set_location('BUSI NO. ' || l_business_number, 130);
4044 hr_utility.set_location('GRE NAME ' || l_gre_name, 140);
4045
4046 sv_busi_no := l_business_number;
4047 sv_gre_name := l_gre_name;
4048 sv_gre := l_org_id;
4049
4050 l_transmitter_y_n := 'N';
4051
4052 open cur_trans_y_n(l_org_id);
4053 fetch cur_trans_y_n into l_transmitter_y_n;
4054 close cur_trans_y_n;
4055
4056 sv_trans_y_n := l_transmitter_y_n;
4057 hr_utility.set_location('Transmitter ? ' || sv_trans_y_n, 150);
4058
4059 if ((sv_report_type <> 'CAEOY_T4_AMEND_PP') and
4060 (sv_report_type <> 'CAEOY_T4A_AMEND_PP')) then
4061
4062 if sv_p_y = 'E' then
4063
4064 initialize_static_var('R');
4065 open cur_dbi(l_payroll_action_id);
4066
4067 hr_utility.set_location(' Cursor DBI Before Validation ', 160);
4068 loop
4069
4070 fetch cur_dbi into l_dbi_name,
4071 l_dbi_value,
4072 l_dbi_short_name,
4073 l_arc_item_id;
4074 exit when cur_dbi%notfound;
4075
4076 fed_employer_validation(l_dbi_name,l_dbi_value,l_dbi_short_name);
4077
4078 end loop;
4079
4080 hr_utility.set_location(' Cursor DBI After Validation ', 170);
4081 close cur_dbi;
4082 employer_header;
4083 print_employer;
4084 end if;
4085
4086 end if;
4087
4088 open cur_cpp_periods(l_payroll_id);
4089 fetch cur_cpp_periods into sv_no_of_cpp_periods;
4090 close cur_cpp_periods;
4091
4092 /* 10163858 - Code changes begin */
4093 if ((sv_report_type <> 'CAEOY_T4_AMEND_PP') and
4094 (sv_report_type <> 'CAEOY_T4A_AMEND_PP')) then
4095 l_first_employee := 0;
4096 end if;
4097
4098 if ((sv_report_type = 'CAEOY_T4_AMEND_PP') or
4099 (sv_report_type = 'CAEOY_T4A_AMEND_PP')) then
4100 format_data('<table border=0>');
4101 end if;
4102 /* 10163858 - Code changes end */
4103
4104 open cur_asg_act(l_payroll_action_id);
4105 loop
4106 fetch cur_asg_act into l_assignment_action_id,
4107 l_assignment_id,
4108 l_person_id,
4109 l_action_status,
4110 l_last_name,
4111 l_first_name,
4112 l_middle_names;
4113
4114 exit when cur_asg_act%notfound;
4115
4116 if l_first_employee = 0 then
4117 employee_header;
4118 l_first_employee := 1;
4119 end if;
4120
4121 sv_asg_id := l_assignment_id;
4122
4123 initialize_static_var('E');
4124
4125 hr_utility.set_location(' CUR_DOB', 188 );
4126
4127 open cur_dob( l_person_id, l_effective_date);
4128 fetch cur_dob into sv_date_of_birth, sv_hire_date;
4129 close cur_dob;
4130
4131 hr_utility.set_location('CUR_TERMINATE',177);
4132
4133 open cur_terminate( l_person_id);
4134 fetch cur_terminate into sv_terminate_date;
4135 close cur_terminate;
4136
4137 hr_utility.set_location('CUR_CPP_BLOCK',166);
4138
4139 open cur_cpp_block( fp_b_g_id, l_person_id, l_effective_date);
4140 fetch cur_cpp_block into sv_cpp_block;
4141 close cur_cpp_block;
4142
4143 hr_utility.set_location('CUR_CPP_DATE_BLOCK',169);
4144
4145 open cur_cpp_date_block( fp_b_g_id, l_person_id, l_effective_date);
4146 i := 0;
4147 loop
4148 fetch cur_cpp_date_block into l_cpp_election_date,
4149 l_cpp_revocation_date;
4150 exit when cur_cpp_date_block%notfound;
4151 i := i + 1;
4152 sv_cpp_date(i).cpp_election_date := l_cpp_election_date;
4153 sv_cpp_date(i).cpp_revocation_date := l_cpp_revocation_date;
4154 end loop;
4155 close cur_cpp_date_block;
4156
4157 hr_utility.set_location('CUR_EI_BLOCK',156);
4158
4159 open cur_ei_block( fp_b_g_id, l_person_id, l_effective_date);
4160 fetch cur_ei_block into sv_ei_block;
4161 close cur_ei_block;
4162
4163 hr_utility.set_location('JURISDICTION_CODE',157);
4164
4165 open get_jurisdiction_code(l_assignment_action_id);
4166 fetch get_jurisdiction_code into sv_jurisdiction;
4167 close get_jurisdiction_code;
4168
4169 hr_utility.set_location('CUR_DBI',159);
4170
4171 open cur_dbi(l_assignment_action_id);
4172 hr_utility.set_location(' Cursor Assignment actions '||
4173 to_char(l_assignment_action_id), 199);
4174 i := 0;
4175 loop
4176 fetch cur_dbi into l_dbi_name,
4177 l_dbi_value,
4178 l_dbi_short_name,
4179 l_arc_item_id;
4180 exit when cur_dbi%notfound;
4181
4182 i := i + 1;
4183 sv_dbi(i).dbi_name := l_dbi_name;
4184 sv_dbi(i).dbi_value := l_dbi_value;
4185 /* Bug 15908390 - Following code snippet is added ot correct the format of
4186 the heading displayed for negative balances on YEER output */
4187 CASE
4188 WHEN ( instr(upper(l_dbi_short_name),'BOX') > 0 ) THEN
4189 l_dbi_short_name := substr(l_dbi_short_name,1,3)||upper(substr(l_dbi_short_name,4));
4190
4191 WHEN ( instr(upper(l_dbi_short_name),'QPP') > 0 ) THEN
4192 if ( instr(upper(l_dbi_short_name),'QPP EE') > 0 ) THEN
4193 l_dbi_short_name := upper(substr(l_dbi_short_name,1,6)) ||initcap(substr(l_dbi_short_name,7)) ;
4194 else
4195 l_dbi_short_name := upper(substr(l_dbi_short_name,1,3)) ||initcap(substr(l_dbi_short_name,4));
4196 end if;
4197
4198 WHEN ( instr(upper(l_dbi_short_name),'CPP') > 0 ) THEN
4199 if ( instr(upper(l_dbi_short_name),'CPP EE') > 0 ) THEN
4200 l_dbi_short_name := upper(substr(l_dbi_short_name,1,6)) ||initcap(substr(l_dbi_short_name,7)) ;
4201 else
4202 l_dbi_short_name := upper(substr(l_dbi_short_name,1,3)) ||initcap(substr(l_dbi_short_name,4));
4203 end if;
4204
4205 WHEN ( instr(upper(l_dbi_short_name),'PPIP') > 0 ) THEN
4206 if ( instr(upper(l_dbi_short_name),'PPIP EE') > 0 ) THEN
4207 l_dbi_short_name := upper(substr(l_dbi_short_name,1,7)) ||initcap(substr(l_dbi_short_name,8)) ;
4208 else
4209 l_dbi_short_name := upper(substr(l_dbi_short_name,1,4)) ||initcap(substr(l_dbi_short_name,5));
4210 end if;
4211
4212 WHEN ( instr(upper(l_dbi_short_name),'EI') > 0 ) THEN
4213 if ( instr(upper(l_dbi_short_name),'EI EE') > 0 ) THEN
4214 l_dbi_short_name := upper(substr(l_dbi_short_name,1,5)) ||initcap(substr(l_dbi_short_name,6)) ;
4215 else
4216 l_dbi_short_name := upper(substr(l_dbi_short_name,1,2)) ||initcap(substr(l_dbi_short_name,3));
4217 end if;
4218 ELSE null;
4219 END CASE;
4220 sv_dbi(i).dbi_short_name := l_dbi_short_name;
4221 sv_dbi(i).archive_item_id := l_arc_item_id;
4222 end loop;
4223 close cur_dbi;
4224
4225 open cur_t4a_nonbox_footnote(l_assignment_action_id);
4226 loop
4227 fetch cur_t4a_nonbox_footnote into l_dbi_value,
4228 l_dbi_short_name,
4229 l_dbi_name;
4230 exit when cur_t4a_nonbox_footnote%notfound;
4231
4232 i := i + 1;
4233 sv_dbi(i).dbi_name := l_dbi_name;
4234 sv_dbi(i).dbi_value := l_dbi_value;
4235 sv_dbi(i).dbi_short_name := l_dbi_short_name;
4236
4237 end loop;
4238 close cur_t4a_nonbox_footnote;
4239
4240 if i <> 0 then
4241 fed_employee_validation;
4242 if sv_print = 1 then
4243 print_employee;
4244 end if;
4245 end if;
4246
4247 end loop;
4248 close cur_asg_act;
4249 format_data('</table>');
4250
4251 end loop;
4252 close cur_pay_act;
4253 end federal_process;
4254
4255 /*****************************************************************
4256 ** This is the main procedure which is called from the Concurrent
4257 ** Request. All the paramaters are passed based on which it will
4258 ** print an HTML format file.
4259 *****************************************************************/
4260
4261 PROCEDURE pier_yeer
4262 (errbuf out nocopy varchar2
4263 ,retcode out nocopy number
4264 ,p_reporting_year in varchar2
4265 ,p_pier_yeer in varchar2
4266 ,p_fed_prov in varchar2
4267 ,p_fed_prov_dummy in varchar2
4268 ,p_is_gre in number
4269 ,p_gre in number
4270 ,p_is_pre in number
4271 ,p_pre in number
4272 ,p_b_g_id in number
4273 )
4274 IS
4275
4276 /* The cursor cur_lkup is used to store all reasons and labels in
4277 PL/SQL table so we no need to retrieve this table many times. */
4278
4279 cursor cur_lkup is
4280 select flv.lookup_code,
4281 flv.meaning,
4282 flv.description
4283 from fnd_lookup_types flt,
4284 fnd_lookup_values flv
4285 where flt.lookup_type = 'PAY_CA_EOY_EXCEPTIONS'
4286 and flv.lookup_type = flt.lookup_type
4287 and flv.language = userenv('LANG');
4288
4289 p_output_file_type varchar2(10) := ' ';
4290 lv_lookup_code varchar2(30);
4291 lv_meaning varchar2(80);
4292 lv_description varchar2(240);
4293 i number := 0;
4294 BEGIN
4295 -- hr_utility.trace_on(null,'VRP');
4296 hr_utility.set_location(gv_package_name || '.pier_yeer', 10);
4297 hr_utility.set_location('Reporting Year ' || p_reporting_year, 20);
4298 hr_utility.set_location('Report Name ' || p_pier_yeer, 30);
4299 hr_utility.set_location('Fed/Prov ' || p_fed_prov, 40);
4300 hr_utility.set_location('GRE ' || to_char(p_gre), 50);
4301 hr_utility.set_location('PRE ' || to_char(p_pre), 60);
4302 hr_utility.set_location('BGID ' || to_char(p_b_g_id), 70);
4303
4304 open cur_lkup;
4305 loop
4306 fetch cur_lkup into lv_lookup_code,
4307 lv_meaning,
4308 lv_description;
4309 exit when cur_lkup%notfound;
4310 i := i + 1;
4311 sv_lkup(i).dbi_name := lv_lookup_code;
4312 sv_lkup(i).dbi_value := lv_meaning;
4313 sv_lkup(i).dbi_short_name := lv_description;
4314 end loop;
4315 close cur_lkup;
4316
4317 select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') into sv_date from dual;
4318 hr_utility.set_location('DATE ' || sv_date, 80);
4319
4320 /* Report Parameters print first using static_header procedure */
4321
4322 sv_gre := p_gre;
4323 sv_pre := p_pre;
4324 sv_reporting_year := substr(p_reporting_year,1,4);
4325 sv_p_y := p_pier_yeer;
4326 sv_f_p := p_fed_prov;
4327 sv_b_g_id := p_b_g_id;
4328
4329 /* Select all CPP and EI information */
4330
4331 sv_cpp_max_earn := legi_info('MAX_CPP_EARNINGS');
4332 sv_cpp_exempt := legi_info('CPP_EXEMPTION');
4333 sv_cpp_rate := legi_info('CPP_RATE');
4334 sv_qpp_rate := legi_info('QPP_RATE'); /*For Bug no. 13421306*/
4335 sv_ei_max_earn := legi_info('MAX_EI_EARNINGS');
4336 sv_ei_rate := legi_info('EI_RATE');
4337 /* Added by ssmukher for PPIP tax implementation */
4338 sv_ppip_max_earn := legi_info('MAX_PPIP_EARNINGS');
4339 sv_ppip_rate := legi_info('PPIP_RATE');
4340 sv_ppip_ei_rate := legi_info('EI_RATE','QC');
4341
4342 sv_ppip_max_exempt := ( ( sv_ppip_max_earn ) * sv_ppip_rate / 100 );
4343
4344 sv_cpp_max_exempt := ( ( sv_cpp_max_earn - sv_cpp_exempt ) *
4345 sv_cpp_rate / 100 );
4346 /*For Bug no. 13421306 - Start*/
4347 sv_qpp_max_exempt := ( ( sv_cpp_max_earn - sv_cpp_exempt ) *
4348 sv_qpp_rate / 100 );
4349 /*For Bug no. 13421306 - end*/
4350 /*
4351 if sv_ppip_insurable_earnings > 0 then
4352 sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ppip_ei_rate / 100 );
4353 else
4354 sv_ei_max_exempt := ( ( sv_ei_max_earn ) * sv_ei_rate / 100 );
4355 end if;
4356 */
4357 hr_utility.set_location('Report: ' || p_pier_yeer, 85);
4358
4359 if ( p_pier_yeer = 'P' ) then
4360 if ( p_fed_prov = 'F' ) then
4361 sv_pier_yeer := get_lookup_meaning('L_PIER');
4362 else
4363 sv_pier_yeer := get_lookup_meaning('L_QPP_RPT');
4364 end if;
4365 else
4366 sv_pier_yeer := get_lookup_meaning('L_YEER');
4367 end if;
4368
4369 hr_utility.set_location('Report: ' || sv_pier_yeer, 90);
4370
4371
4372 if p_fed_prov = 'P' then
4373 sv_fed_prov := get_lookup_meaning('L_PROV');
4374 else
4375 sv_fed_prov := get_lookup_meaning('L_FED');
4376 end if;
4377 hr_utility.set_location('FEd/Prov: ' || sv_fed_prov, 100);
4378
4379 if ( p_gre is not null ) then
4380 begin
4381 /* Used to print GRE name as report parameter. */
4382 select name, org_information1
4383 into sv_gre_name, sv_busi_no
4384 from hr_organization_information hoi,
4385 hr_all_organization_units hou
4386 where hoi.organization_id = hou.organization_id
4387 and hoi.organization_id = p_gre
4388 and hoi.org_information_context = 'Canada Employer Identification'
4389 and hou.business_group_id = p_b_g_id;
4390
4391 exception
4392 when others then
4393 null;
4394 end;
4395 hr_utility.set_location('GRE ' || sv_gre_name, 110);
4396 end if;
4397
4398 if ( p_pre is not null ) then
4399 begin
4400 /* Used to print PRE name as report parameter. */
4401 select hou.name,
4402 hoi.org_information2
4403 into sv_pre_name,
4404 sv_qin
4405 from hr_organization_information hoi,
4406 hr_all_organization_units hou
4407 where hoi.organization_id = hou.organization_id
4408 and hoi.organization_id = p_pre
4409 and hoi.org_information1 = 'QC'
4410 and hoi.org_information_context = 'Prov Reporting Est'
4411 and hou.business_group_id = p_b_g_id;
4412
4413 exception
4414 when others then
4415 null;
4416 end;
4417 hr_utility.set_location('PRE ' || sv_pre_name, 120);
4418 hr_utility.set_location('QIN ' || sv_qin, 120);
4419 end if;
4420
4421 begin
4422
4423 /* Select context id for Jurisdiction and is used for T4 Neg. Bal. */
4424 select context_id
4425 into sv_context_id
4426 from ff_contexts
4427 where context_name = 'JURISDICTION_CODE';
4428
4429 exception
4430 when others then
4431 null;
4432 end;
4433
4434 format_data('<html><body>');
4435 static_header;
4436 if p_fed_prov = 'F' then
4437 federal_process(p_gre, p_b_g_id);
4438 else
4439 provincial_process(p_pre, p_b_g_id);
4440 end if;
4441 format_data('</body></html>');
4442 END pier_yeer;
4443
4444 end pay_ca_yeer_pkg;