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