[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_ARCHIVE
Source
1 package body pay_ca_eoy_archive as
2 /* $Header: pycayear.pkb 120.37.12020000.10 2013/02/28 06:15:34 rgottipa ship $ */
3
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6
7 Description : Package and procedure to build sql for payroll processes.
8
9 Change List
10 -----------
11 Date Name Vers Bug No Description
12 ----------- ---------- ----- ------- -----------------------------------
13 03-JAN-2000 M.Mukherjee 110.0 Created
14 04-JAN-2000 M.Mukherjee 110.1 Changed the name to hr_ca_tax_units_v
15 from hr_ca_tax_units_v_temp
16 12-JAN-2000 M.Mukherjee 110.2 Changed the name of report type
17 31-JAN-2000 M.Mukherjee 110.4 ADDED archiving of QPP balances
18 03-FEB-2000 M.Mukherjee 110.6 Corrected exempt_flags archiving
19 04-FEB-2000 M.Mukherjee 110.7 Corrected exempt_flags archiving
20 07-FEB-2000 M.Mukherjee 110.8 Corrected exempt_flags archiving
21 put it inside if condition
22 07-FEB-2000 M.Mukherjee 115.0,115.1,115.2 Upported in 115, changed the name
23 of database items and tested the
24 exempt flag query
25 14-APR-2000 S.Sattineni 115.3 Changed the pay_ca_emp_all_fedtax_info
26 to pay_ca_emp_all_fedtax_info_v
27 16-MAY-2000 M.Mukherjee 115.4 Changed the report_type to T4
28 30-JUN-2000 P.Ganguly 115.5 Subtracted QC only Taxable Benefits
29 from Gross Earnings in case of
30 Quebec jurisdiction -
31 eoy_archive_data - Procedure.
32 18-AUG-2000 M. Mukherjee 115.7 Changes for Magtapes
33 added registration number archiving
34 05-SEP-2000 M. Mukherjee 115.8 Added error message for no
35 transmitter GRE.
36 14-SEP-2000 P.Ganguly 115.12 Added the Null value check
37 for result and qc_result.
38 15-SEP-2000 P.Ganguly 115.13 Added a check if CPP and QPP is
39 0 and the employee is < 17, > 70
40 CAEOY_CPP_QPP_EXEMPT should archive
41 'X'
42 20-SEP-2000 M. Mukherjee 115.15 Changed the name of QC only Taxable
43 benefit
44 to 'Taxable Benefits for Quebec'
45 21-SEP-2000 M. Mukherjee 115.16 Corrected archiving of registration
46 no
47 03-OCT-2000 M. Mukherjee 115.17-115.20Corrected archiving of registration
48 no
49 11-OCT-2000 SSattineni 115.21 Corrected archiving of
50 Accounting_Contact_Name for Employer
51 Info
52 13-NOV-2001 P.Ganguly 115.22 Changed the cursor get_person_info so
53 that it picks up middle_names rather
54 than pre_adjunct_name. Also added
55 CAEOY_CPP_BASIC_EXEMPTION_PER_JD_GRE
56 _YTD and CAEOY_CPP_EXEMPT_PER_JD_GRE
57 _YTD for Year End Exception Report.
58 13-NOV-2001 P.Ganguly 115.23 Added set verify off
59 14-NOV-2001 P.Ganguly 115.24 Added dbdrv command.
60 11-DEC-2001 P.Ganguly 115.25 Changed the cursor employer_info into
61 two cursors cur_employer_info and
62 cur_transmitter_info because of
63 performance issues.
64 18-DEC-2001 P.Ganguly 115.26 Changed the cursor employer_info into
65 two - employer_info and cur_employer_
66 info.
67 18-DEC-2001 P.Ganguly 115.27 Added \ at the end of first dbdrv.
68 20-DEC-2001 P.Ganguly 115.28 Changed the cursor cur_employer_info-
69 removed the check org_information2=
70 '904'
71 04-JAN-2002 SSattini 115.29 Changed the dbdrv line
72 24-JAN-2002 P.Ganguly 115.30 Added a check in the cursor
73 c_get_asg_id so that it picks up
74 assignments of type 'E'
75 14-NOV-2002 P.Ganguly 115.31 Fixed bug# 2667016. Removed the
76 Group level balance calls.
77 25-NOV-2002 P.Ganguly 115.32 Fixed Bug# 2598777. While archiving
78 CAEOY_T4_BOX52_PER_JD_GRE_YTD added
79 a round() to round the amount for
80 Box 52.
81 02-DEC-2002 P.Ganguly 115.36 Added nocopy in the out parameter.
82 03-DEC-2002 P.Ganguly 115.37 Fixed Bug# 2690890. Called a function
83 pay_ca_rl1_reg.get_primary_address
84 to get the employee's primary address
85 .
86 09-DEC-2002 P.Ganguly 115.38 Added a new dbi CAEOY_GRE_EI_RATE
87 to archive the EI_RATE of each GRE.
88 18-DEC-2002 P.Ganguly 115.39 Fixed Bug# 2707038. Changed the
89 procedure eoy_archive_data. The amt
90 against other information code
91 31/53/77 is subtracted from Box 14.
92 Code 53/77 has been started
93 archiving as per Bug# 2707038.
94 Fixed Bug# 2599468. While archiving
95 Registration Number for box 50 a
96 check is introduced to check the
97 value of Box 52. If Box 52 is > than
98 0 then the highest reg no against
99 Box 52 is archived. If Box 52 <= 0
100 then Reg No against box 20 is
101 archived.
102 27-AUG-2003 SSOURESR 115.41 If new balance 'T4 No Gross Earnings'
103 is not zero then archiving will
104 proceed Also the new balance 'T4 Non
105 Taxable Earnings' will be deducted
106 from Gross Earnings. Bugs 2594600 and
107 2954727
108 27-AUG-2003 mmukherj 115.42 Bugfix for #2953960. If transmiiter
109 GRE is not properly setup it will give
110 an error message.
111 05-SEP-2003 SSattineni 115.43 Added T4 Amendment Archiving logic
112 in eoy_archive_data procedure. Also
113 added new local function
114 compare_archive_data used for T4
115 Amendment Archiver.
116 06-NOV-2003 SSattineni 115.45 Added code to archive T4 Employment
117 code in eoy_archive_data procedure.
118 Fix for bug#2141132.
119 07-NOV-2003 SSouresr 115.46 Employees that only have non taxable
120 earnings should not be archived. Added
121 check for this #3137707.
122 19-NOV-2003 SSouresr 115.47 The function compare_archive_data was
123 changed so that correct comparisons
124 are made for archived null values.
125 03-DEC-2003 SSattineni 115.48 Added code to archive
126 CAEOY_T4_NEGATIVE_BALANCE_EXISTS flag
127 to avoid negative balances employees
128 in T4 Magnetic Media and Paper.
129 03-DEC-2003 SSattineni 115.49 Fixed the bug#3284220. Modified
130 compare_archive_data function to
131 consider if there are any new
132 db_items archived by amendment and
133 not archived by YEPP then return
134 amendment flag 'Y'.
135 04-DEC-2003 PGanguly 115.50 Fixed the bug# 3298050. Changed the
136 cursor c_eoy_gre so that it checks
137 for data in the pay_assignment_actions
138 and pay_payroll_actions via EXIST
139 clause rather than direct join. Also
140 removed the tax_unit_id from the
141 select clause as this cursor selects
142 the data for a particular GRE.
143 05-DEC-2003 SSattineni 115.51 The Negative Balance Exists flag
144 was archiving incorrect for some
145 employees, so initialised the
146 flag value with 'N'.
147 05-FEB-2004 SSattineni 115.52 Fixed the bug#3422384, added
148 additional logic to archive the
149 CPP/QPP Exempt flag and EI Exempt
150 flag correctly for an employee.
151 06-FEB-2004 mmukherj 115.53 Added cursor c_get_latest_actid to
152 improve performance of getting latest
153 assignment action id.
154 19-FEB-2004 SSattineni 115.55 Modified c_get_date_of_birth cursor
155 to address the terminated employees
156 issue for Box 28 validation. Part of
157 fix#3422384.
158 02-JUL-2004 mmukherj 115.56 Modified c_eoy_gre further to make
159 it more performant.
160 09-AUG-2004 SSattineni 115.58 Modified eoy_action_creation procedure
161 to check 'T4 Non Taxable Earnings',
162 'Gross Earnings' and 'T4 No Gross
163 Earnings' balance values before
164 creating the assignment action for T4.
165 Fix for bug#3267520.
166 20-AUG-2004 rigarg 115.59 Fix for bug#3564076
167 Added archiver for DBI's for Technical
168 Contact Extension and EMail.
169 24-AUG-2004 ssmukher 115.60 Fix for bug# 3447439.Modified the
170 cursor c_get_latest_asg to fetch the
171 earn date and assignment action id.
172 This earn date will be used to fetch
173 the CPP/QPP and EE exempt flag for
174 an employee in a particular province.
175 02-NOV-2004 rigarg 115.61 Fix for bug# 3973040. Removed
176 Transmitter Code 904 check.
177 10-NOV-2004 ssouresr 115.63 Modified to use tables instead of
178 views to remove problems with
179 security groups
180 12-NOV-2004 ssouresr 115.64 Added a date range to the cursor
181 c_get_latest_asg to make sure records
182 are only picked up in the year
183 22-NOV-2004 mmukherj 115.65 bugfix #4025926
184 01-DEC-2004 mmukherj 115.66 Archiving QPP Reduced Subject. Because
185 this amunt has to be printed in BOX26
186 for QC employee. Bugfix 4031227.
187 02-DEC-2004 ssouresr 115.67 Added error message for security group
188 07-DEC-2004 ssouresr 115.68 Removed the changes made for 3447439
189 in 115.60
190 as this was impacting performance
191 08-JUN-2005 ssouresr 115.69 Removed error message for security
192 group
193 13-JUN-2005 mmukherj 115.70 Bug fix #4026689. Added call to
194 eoy_archive_gre_data in
195 eoy_archive_data. So that when the
196 Retry process calls eoy_archive_data,
197 it re-archives the employer and
198 transmitter data.
199 29-JUL-2005 ssmukher 115.71 Bug Fix #4034155 Added code to remove
200 the other information amounts from
201 the Box 14
202 03-AUG-2005 ssmukher 115.72 Bug Fix #4034155 Added code for
203 checking the other information
204 amt total not to exceed the gross
205 earnings total displayed in Box 14.
206 Also modified the check condition for
207 flag l_negative_balance_exists
208 in eoy_archive_data procedure.
209 05-AUG-2005 saurgupt 115.73 Bug 4517693: Added Address_line3 for
210 T4 archiver.
211 11-Aug-2005 ssmukher 115.74 Bug 4547415 Substracted the amount
212 associated with code 31,53 and 78
213 from the Grosss Earnings(box 14)
214 26-AUG-2005 mmukherj 115.75 Commented out the use of two cursors
215 c_eoy_all and eoy_all_range. Since
216 GRE is a mandatory parameter for
217 Federal Yearend Archiver Process
218 these two cursors will never be used.
219 14-Sep-2005 ssmukher 115.76 Bug Fix 4028693 .Archive 0 value for
220 'Gross Earnings' when the Employment
221 code is either 11,12,13 and 17
222 26-OCT-2005 ssouresr 115.77 range_cursor has been modified to
223 avoid using hr_soft_coding_keyflex
224 04-NOV-2005 ssouresr 115.78 Removed archiving of the Federal Youth
225 Hire indicator flag
226 4-NOV-2005 pganguly 115.79 Fixed bug# 4033041. Commented out
227 archiver code for T4_BOX50.
228 3-MAR-2006 ssmukher 115.80 Fixed Bug #5041252 .Removed the
229 per_all_assignments_f table check
230 from the select statement in the
231 procedure eoy_archive_data to fetch
232 the CPP/QPP exempt flag
233 from pay_ca_emp_prov_tax_info_f.
234 25-Jul-2006 ssmukher 115.81 Made modification in the
235 eoy_archive_data procedure to
236 incorporate the PPIP tax.
237 28-AUG-2006 pganguly 115.82 Fixed bug# 4025900. Changed the code
238 for Box 14 so that it subtracts OTHER
239 _INFORMATION71 before archiving.
240 30-Aug-2007 ssmukher 115.83 Bug 5706114 fix.T4 Box44 and T4 Box20
241 should not be reported for Status
242 indian employee.Modified the proc
243 eoy_archive_data.
244 4-SEPT-2007 ssmukher 115.84 Fix for bug# 3447439.Modified the
245 cursor c_get_latest_asg in
246 eoy_archive_data to fetch the
247 earn date and assignment action id.
248 This earn date will be used to fetch
249 the CPP/QPP and EI exempt flag for
250 an employee in a particular province.
251 6-SEPT-2007 amigarg 115.85 Fix for bug# 5698016.Added the
252 T4_other_info_amount for code 81-85.
253 19-SEP-2007 amigarg 115.87 Fix for bug# 6399498.archived the
254 registration number for status_indian
255 11-DEC-2007 tclewis 115.88 In the package eoy_action_creation modified
256 The cursor c_eoy_gre removed the subquery
257 Modified the cursor c_get_latest_asg added
258 Hints.
259
260 19-SEP-2008 sneelapa 115.89 Fix for bug# 6399498.
261 During QA testing bug 6399498 was reopened.
262
263 Modified CURSOR LOOP of c_balance_feed_info
264 and IF condition before CURSOR LOOP
265 so that c_balance_feed_info CURSOR will
266 get the "registration number" value
267 of T4_BOX52 Element incase of Status Indian Employee
268 and for non status indian employee get
269 the reg number of T4_BOX52 if value
270 for T4_BOX52 exists else get reg number
271 of T4_BOX20.
272
273 23-SEP-2008 sneelapa 115.91 Fix for bug# 6399498.
274
275 25-SEP-2008 sneelapa 115.93 Fix for bug# 6399498.
276 Modified CURSOR Query of c_balance_feed_info
277 previous version of package date was hardcoded
278 as '31-DEC-4712', which is against coding standards.
279
280 26-SEP-2008 sneelapa 115.95 Fix for bug# 6399498.
281 Modified CURSOR Query of c_balance_feed_info
282 WHERE Condition pee.effective_start_date >= l_year_start
283 is modified as
284 pee.effective_start_date <= l_year_end
285 IF an Employee is having two PA elements
286 One attached in 2006 and second one in 2008
287 and Archiver is run for 2008, 2006 Element was
288 not picked up.
289 30-OCT-2009 aneghosh 115.96 Fix for Bug 8576897.
290 Removed the deduction of Box 53 from
291 Box14.
292
293 09-DEC-2009 sneelapa 115.97 Fix for Bug 9135405.
294 Modified eoy_archive_data procedure to
295 archive data for T4 Other Info new codes.
296
297 31-DEC-2009 sneelapa 115.98 Fix for Bug 9135405.
298 Modified eoy_archive_data procedure to
299 not to include Other Info Codes 66 to 69
300 in Gross Income (Box 14).
301 09-NOV-2010 sneelapa 115.99 Fix for Bug 10097860.
302 Modified eoy_archive_data procedure to
303 to archive Other Info Code 86
304 30-DEC-2010 sneelapa 115.100 Fix for Bug 10388148.
305 Modified eoy_archive_gre_data procedure to
306 to archive Accounting contact details
307 and Proprietor SIN numbers of
308 Transmitter GRE for Non Transmitter GRE
309 if Accounting contact details and
310 Proprietor SIN numbers are not feeded
311 for Non Transmitter GRE.
312 01-Aug-2011 rgottipa 115.101 Modified eoy_archive_data procedure to
313 archive correct registration number
314 for Box50.
315 29-Aug-2011 sneelapa 115.102 10399514 Introduced new CURSOR c_eoy_gre_range
316 it will be called in place of
317 c_eoy_gre CURSOR, if RANGE_PERSON_ID
318 is enabled.
319 30-Oct-2011 rgottipa 115.103 10244185 Introduced new CURSOR c_get_t4code_limits
320 to get max limit for code 87.
321 Not archiving code 53 from year 2011.
322 14-Nov-2011 sneelapa 115.104 7611439 Modified logic for archiving
323 CAEOY_T4_EMPLOYEE_REGISTRATION_NO to archive
324 Jurisdiction Code and Tax Unit ID contexts.
325 23-Nov-2011 sneelapa 115.105 7611439 Modified logic to decide based on which
326 balance c_balance_feed_info to be opened
327 for archiving CAEOY_T4_EMPLOYEE_REGISTRATION_NO.
328 02-Feb-2012 pracagra 115.106 13615110 Modified the hint to the cursor 'c_eoy_gre_range'
329 to improve the performance of the 'CA Year End
330 Preprocess'.
331 20-Jul-2012 rgottipa 115.107 13797428 Modified the cursor 'c_balance_feed_info' by
332 passing assignment_id as one of the parameter.
333 This is because to get the l_registration_no
334 for terminated assignments.
335 29-Aug-2012 rgottipa 115.108 13505953 added 'if' condition while archiving data for
336 'CAEOY_EMPLOYMENT_CODE'(Box29) to avoid
337 archiving of Box29 value for other prov
338 if employment code specified for specific
339 province.
340 04-Sep-2012 rgottipa 115.109 13501503 Trying to fetch l_cpp_exempt_flag and
341 l_ei_exempt_flag flags at each
342 assignment level with consideration of
343 jurisdiction. Modified the CURSOR
344 c_diff_assignments.
345 07-Sep-2012 sgotlasw 115.110 11655053 Added user entity for archiving 'EI ER Liability'.
346 18-Sep-2012 rgottipa 115.111 13505953 Added logic to set l_box14_flag when
347 employment_code (Box 29) exists.
348
349 22-Nov-2012 sneelapa 115.112 13087530 Added logic to add "Taxable Benefits without Remuneration"
350 balance value to CPP_EE_TAXABLE or CPP_EE_TAXABLE
351 depending on Employee Jurisdiction.
352 28-Feb-2012 rgottipa 115.114 16173065 Re written the CURSOR c_balance_feed_info
353 so that it will bring REGISTRATION NUMBER from the
354 elements which are processed for the employee
355 irrespective of how they have fed (directly or indirctly).
356
357 */
358
359
360 sqwl_range varchar2(4000);
361 eoy_gre_range varchar2(4000);
362 eoy_all_range varchar2(4000);
363
364 /* Returns the value of a legislative_parameter from pay_payroll_actions */
365
366 function get_parameter(name in varchar2,
367 parameter_list varchar2)
368 return varchar2
369 is
370 start_ptr number;
371 end_ptr number;
372 token_val pay_payroll_actions.legislative_parameters%type;
373 par_value pay_payroll_actions.legislative_parameters%type;
374 begin
375 --
376 token_val := name||'=';
377 --
378 start_ptr := instr(parameter_list, token_val) + length(token_val);
379 end_ptr := instr(parameter_list, ' ', start_ptr);
380 --
381 /* if there is no spaces use then length of the string */
382 if end_ptr = 0 then
383 end_ptr := length(parameter_list)+1;
384 end if;
385 --
386 /* Did we find the token */
387 if instr(parameter_list, token_val) = 0 then
388 par_value := NULL;
389 else
390 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
391 end if;
392 --
393 return par_value;
394 --
395 end get_parameter;
396
397 /* Name : bal_db_item
398 Purpose : Given the name of a balance DB item as would be seen in a fast formula
399 it returns the defined_balance_id of the balance it represents.
400 Arguments :
401 Notes : A defined balance_id is required by the PLSQL balance function.
402 */
403
404 function bal_db_item
405 (
406 p_db_item_name varchar2
407 ) return number is
408
409 /* Get the defined_balance_id for the specified balance DB item. */
410
411 cursor csr_defined_balance is
412 select to_number(UE.creator_id)
413 from ff_user_entities UE,
414 ff_database_items DI
415 where DI.user_name = p_db_item_name
416 and UE.user_entity_id = DI.user_entity_id
417 and Ue.creator_type = 'B'
418 and UE.legislation_code = 'CA';
419
420 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
421
422 begin
423
424 open csr_defined_balance;
425 fetch csr_defined_balance into l_defined_balance_id;
426 if csr_defined_balance%notfound then
427 close csr_defined_balance;
428 hr_utility.raise_error;
429 else
430 close csr_defined_balance;
431 end if;
432
433 return (l_defined_balance_id);
434
435 end bal_db_item;
436
437
438 /* Name : get_dates
439 Purpose : The dates are dependent on the report being run
440 For T4 it is year end dates.
441
442 */
443
444 procedure get_dates
445 (
446 p_report_type in varchar2,
447 p_effective_date in date,
448 p_period_end in out nocopy date,
449 p_quarter_start in out nocopy date,
450 p_quarter_end in out nocopy date,
451 p_year_start in out nocopy date,
452 p_year_end in out nocopy date
453 ) is
454 begin
455
456 if p_report_type = 'T4' then
457
458 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
459 p_quarter_start := trunc(p_period_end, 'Q');
460 p_quarter_end := p_period_end;
461
462 end if;
463
464 p_year_start := trunc(p_effective_date, 'Y');
465 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
466
467 end get_dates;
468
469
470 /* Name : get_selection_information
471 Purpose : Returns information used in the selection of people to be reported on.
472 Arguments :
473
474 The following values are returned :-
475
476 p_period_start - The start of the period over which to select
477 the people.
478 p_period_end - The end of the period over which to select
479 the people.
480 p_defined_balance_id - The balance which must be non zero for each
481 person to be included in the report.
482 p_group_by_gre - should the people be grouped by GRE.
483 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
484 the testing of the balance.
485 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
486 for the testing of the balance.
487
488 Notes : This routine provides a way of coding explicit rules for
489 individual reports where they are different from the
490 standard selection criteria for the report type ie. in
491 NY state the selection of people in the 4th quarter is
492 different from the first 3.
493 */
494
495 procedure get_selection_information
496 (
497
498 /* Identifies the type of report, the authority for which it is being run,
499 and the period being reported. */
500 p_report_type varchar2,
501 p_quarter_start date,
502 p_quarter_end date,
503 p_year_start date,
504 p_year_end date,
505 /* Information returned is used to control the selection of people to
506 report on. */
507 p_period_start in out nocopy date,
508 p_period_end in out nocopy date,
509 p_defined_balance_id in out nocopy number,
510 p_group_by_gre in out nocopy boolean,
511 p_tax_unit_context in out nocopy boolean,
512 p_jurisdiction_context in out nocopy boolean
513 ) is
514
515 begin
516
517 /* Depending on the report being processed, derive all the information
518 required to be able to select the people to report on. */
519
520 if p_report_type = 'T4' then
521
522 /* Default settings for Year End Pre-process. */
523
524 p_period_start := p_year_start;
525 p_period_end := p_year_end;
526 p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD');
527 p_group_by_gre := FALSE;
528 p_tax_unit_context := TRUE;
529 p_jurisdiction_context := FALSE;
530
531 /* For EOY - end */
532
533 /* An invalid report type has been passed so fail. */
534
535 else
536
537 hr_utility.raise_error;
538
539 end if;
540
541 end get_selection_information;
542
543
544
545
546 /* Name : eoy_action_creation
547 Purpose : This creates the assignment actions for a specific chunk
548 of people to be archived by the year end pre-process.
549 Arguments :
550 Notes :
551 */
552
553 procedure eoy_action_creation(pactid in number,
554 stperson in number,
555 endperson in number,
556 chunk in number) is
557
558
559
560 /* Variables used to hold the select columns from the SQL statement.*/
561
562 l_person_id number;
563 l_assignment_id number;
564 l_tax_unit_id number;
565 l_eoy_tax_unit_id number;
566 l_effective_end_date date;
567 l_archive_item_id number;
568 l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
569
570 /* Variables used to hold the values used as bind variables within the
571 SQL statement. */
572
573 l_bus_group_id number;
574 l_period_start date;
575 l_period_end date;
576
577 /* Variables used to hold the details of the payroll and assignment actions
578 that are created. */
579
580 l_payroll_action_created boolean := false;
581 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
582 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
583
584 /* Variable holding the balance to be tested. */
585
586 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
587
588 /* Indicator variables used to control how the people are grouped. */
589
590 l_group_by_gre boolean := FALSE;
591
592 /* Indicator variables used to control which contexts are set up for
593 balance. */
594
595 l_tax_unit_context boolean := FALSE;
596 l_jurisdiction_context boolean := FALSE;
597
598 /* Variables used to hold the current values returned within the loop for
599 checking against the new values returned from within the loop on the
600 next iteration. */
601
602 l_prev_person_id per_all_people_f.person_id%type;
603 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
604
605 /* Variable to hold the jurisdiction code used as a context for state
606 reporting. */
607
608 l_jurisdiction_code varchar2(30);
609
610 /* general process variables */
611
612 l_report_type pay_payroll_actions.report_type%type;
613
614 -- Variables declared for bug 10399514
615 l_person_on boolean ;
616 l_report_cat pay_payroll_actions.report_category%type;
617 l_state pay_payroll_actions.report_qualifier%type;
618 l_report_format pay_report_format_mappings_f.report_format%type;
619 -- Variables declared for bug 10399514
620
621 l_province pay_payroll_actions.report_qualifier%type;
622 l_value number;
623 l_effective_date date;
624 l_quarter_start date;
625 l_quarter_end date;
626 l_year_start date;
627 l_year_end date;
628 lockingactid number;
629 l_primary_asg pay_assignment_actions.assignment_id%type;
630 l_legislative_parameters varchar2(240);
631
632
633 /* For Year End Preprocess we have to archive the assignments
634 belonging to a GRE */
635 /*
636 CURSOR c_eoy_gre IS
637 SELECT ASG.person_id person_id,
638 ASG.assignment_id assignment_id,
639 ASG.effective_end_date effective_end_date
640 FROM
641 per_all_assignments_f ASG
642 WHERE
643 ASG.business_group_id = l_bus_group_id AND
644 asg.assignment_type = 'E' AND
645 ASG.person_id between stperson and endperson AND
646 EXISTS
647 (SELECT 1
648 FROM pay_payroll_actions ppa,
649 pay_assignment_actions paa
650 WHERE
651 ppa.business_group_id = l_bus_group_id AND
652 ppa.payroll_action_id = paa.payroll_action_id AND
653 ppa.action_type in ('R','Q','V','B','I') AND
654 ppa.effective_date BETWEEN ASG.effective_start_date AND
655 ASG.effective_end_date AND
656 ppa.effective_date between l_period_start AND
657 l_period_end AND
658 paa.assignment_id = ASG.assignment_id AND
659 paa.tax_unit_id = l_eoy_tax_unit_id)
660 ORDER BY 1, 3 DESC, 2;
661 */
662
663 CURSOR c_eoy_gre IS
664 SELECT /*+ Ordered
665 INDEX (asg PER_ASSIGNMENTS_F_N12)
666 INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
667 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
668 DISTINCT ASG.person_id person_id
669 FROM
670 per_all_assignments_f ASG,
671 pay_assignment_actions paa,
672 pay_payroll_actions ppa
673
674 WHERE ppa.effective_date between l_period_start
675 and l_period_end
676 and ppa.action_type in ('R','Q','V','B','I')
677 and ppa.action_status = 'C'
678 and ppa.business_group_id + 0 = l_bus_group_id
679 and ppa.payroll_action_id = paa.payroll_action_id
680 and paa.tax_unit_id = l_eoy_tax_unit_id
681 and paa.action_status = 'C'
682 and paa.assignment_id = ASG.assignment_id
683 and ppa.business_group_id = ASG.business_group_id +0
684 and ppa.effective_date between ASG.effective_start_date
685 and ASG.effective_end_date
686 AND ASG.person_id between stperson and endperson
687 AND ASG.assignment_type = 'E';
688
689
690 -- Added for Bug# 10399514
691 -- Used when RANGE_PERSON_ID functionality is available
692
693 CURSOR c_eoy_gre_range IS
694 SELECT /*+ leading(ppr,asg,paa) */ --Modified for bug 13615110
695 DISTINCT ASG.person_id person_id
696 FROM
697 per_all_assignments_f ASG,
698 pay_assignment_actions paa,
699 pay_payroll_actions ppa,
700 pay_population_ranges ppr
701 WHERE ppa.effective_date between l_period_start
702 and l_period_end
703 and ppa.action_type in ('R','Q','V','B','I')
704 and ppa.action_status = 'C'
705 and ppa.business_group_id + 0 = l_bus_group_id
706 and ppa.payroll_action_id = paa.payroll_action_id
707 and paa.tax_unit_id = l_eoy_tax_unit_id
708 and paa.action_status = 'C'
709 and paa.assignment_id = ASG.assignment_id
710 and ppa.business_group_id = ASG.business_group_id +0
711 and ppa.effective_date between ASG.effective_start_date
712 and ASG.effective_end_date
713 -- AND ASG.person_id between stperson and endperson
714 AND ppr.payroll_action_id = pactid
715 AND ppr.chunk_number = chunk
716 AND ppr.person_id = ASG.person_id
717 AND ASG.assignment_type = 'E';
718
719 --Original query:
720 /* SELECT DISTINCT
721 ASG.person_id person_id
722 FROM
723 per_all_assignments_f ASG,
724 pay_all_payrolls_f PPY
725 WHERE exists
726 (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
727 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
728 /* 'x'
729 from pay_payroll_actions ppa,
730 pay_assignment_actions paa
731 where ppa.effective_date between l_period_start
732 and l_period_end
733 and ppa.action_type in ('R','Q','V','B','I')
734 and ppa.action_status = 'C'
735 and ppa.business_group_id + 0 = l_bus_group_id
736 and ppa.payroll_action_id = paa.payroll_action_id
737 and paa.tax_unit_id = l_eoy_tax_unit_id
738 and paa.action_status = 'C'
739 and paa.assignment_id = ASG.assignment_id
740 and ppa.business_group_id = ASG.business_group_id +0
741 and ppa.effective_date between ASG.effective_start_date
742 and ASG.effective_end_date)
743 AND ASG.person_id between stperson and endperson
744 AND ASG.assignment_type = 'E'
745 AND PPY.payroll_id = ASG.payroll_id;
746 */
747
748 /* Commented c_eoy_all, because Tax Unit id is a mandatory parameter
749 in archiver process, this cursor will never be used */
750 /*
751 CURSOR c_eoy_all IS
752 SELECT ASG.person_id person_id,
753 ASG.assignment_id assignment_id,
754 to_number(SCL.segment1) tax_unit_id,
755 ASG.effective_end_date effective_end_date
756 FROM per_all_assignments_f ASG,
757 hr_soft_coding_keyflex SCL,
758 pay_all_payrolls_f PPY
759 WHERE ASG.business_group_id + 0 = l_bus_group_id
760 AND ASG.person_id between stperson and endperson
761 AND ASG.assignment_type = 'E'
762 AND ASG.effective_start_date <= l_period_end
763 AND ASG.effective_end_date >= l_period_start
764 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
765 AND PPY.payroll_id = ASG.payroll_id
766 ORDER BY 1, 3, 4 DESC, 2;
767 */
768 /* Get the primary assignment for the given person_id */
769
770 CURSOR c_get_asg_id (p_person_id number) IS
771 SELECT assignment_id
772 from per_all_assignments_f paf
773 where person_id = p_person_id
774 and primary_flag = 'Y'
775 and paf.effective_start_date <= l_period_end
776 and paf.effective_end_date >= l_period_start
777 and paf.assignment_type = 'E'
778 ORDER BY assignment_id desc;
779
780 /* Cursor to get the latest assignment_action_id based
781 on person_id. Bug#3267520 */
782 CURSOR c_get_latest_asg(p_person_id number ) IS
783 select /*+ Ordered
784 INDEX (asg PER_ASSIGNMENTS_F_N12)
785 INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
786 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
787 paa.assignment_action_id
788 from per_all_assignments_f paf,
789 pay_assignment_actions paa,
790 pay_payroll_actions ppa,
791 pay_action_classifications pac
792 where paf.person_id = p_person_id
793 and paa.assignment_id = paf.assignment_id
794 and paa.tax_unit_id = l_tax_unit_id
795 and paa.payroll_action_id = ppa.payroll_action_id
796 and ppa.action_type = pac.action_type
797 and pac.classification_name = 'SEQUENCED'
798 and ppa.effective_date +0 between paf.effective_start_date
799 and paf.effective_end_date
800 and ppa.effective_date +0 between l_year_start and
801 l_year_end
802 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
803 and paa.source_action_id is null)
804 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
805 and paa.source_action_id is not null )
806 or (ppa.action_type = 'V' and ppa.run_type_id is null
807 and paa.run_type_id is not null
808 and paa.source_action_id is null))
809 order by paa.action_sequence desc;
810
811
812 /* Original Query:
813 select paa.assignment_action_id
814 from pay_assignment_actions paa,
815 per_all_assignments_f paf,
816 pay_payroll_actions ppa,
817 pay_action_classifications pac
818 where paf.person_id = p_person_id
819 and paa.assignment_id = paf.assignment_id
820 and paa.tax_unit_id = l_tax_unit_id
821 and paa.payroll_action_id = ppa.payroll_action_id
822 and ppa.action_type = pac.action_type
823 and pac.classification_name = 'SEQUENCED'
824 and ppa.effective_date +0 between paf.effective_start_date
825 and paf.effective_end_date
826 and ppa.effective_date +0 between l_year_start and
827 l_year_end
828 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
829 and paa.source_action_id is null)
830 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
831 and paa.source_action_id is not null )
832 or (ppa.action_type = 'V' and ppa.run_type_id is null
833 and paa.run_type_id is not null
834 and paa.source_action_id is null))
835 order by paa.action_sequence desc;
836 */
837
838 /* local variables Bug#3267520 */
839 ln_non_taxable_earnings number(30);
840 ln_gross_earnings number(30);
841 ln_no_gross_earnings number(30);
842 ln_max_aa_id number;
843
844 begin
845
846 /* Get the report type, report qualifier, business group id and the
847 gre for which the archiving has to be done */
848
849 hr_utility.trace('getting report type ');
850
851 select effective_date,
852 report_type,
853 -- Added for bug 10399514
854 report_qualifier,
855 report_category,
856 -- Added for bug 10399514
857 business_group_id,
858 legislative_parameters
859 into l_effective_date,
860 l_report_type,
861 -- Added for bug 10399514
862 l_state,
863 l_report_cat,
864 -- Added for bug 10399514
865 l_bus_group_id,
866 l_legislative_parameters
867 from pay_payroll_actions
868 where payroll_action_id = pactid;
869
870 l_eoy_tax_unit_id := get_parameter('TRANSFER_GRE',l_legislative_parameters);
871
872 hr_utility.trace('getting dates');
873
874 get_dates(l_report_type,
875 l_effective_date,
876 l_period_end,
877 l_quarter_start,
878 l_quarter_end,
879 l_year_start,
880 l_year_end);
881
882 hr_utility.trace('getting selection information');
883 hr_utility.trace('report type '|| l_report_type);
884 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
885 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
886 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
887 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
888
889 get_selection_information
890 (l_report_type,
891 l_quarter_start,
892 l_quarter_end,
893 l_year_start,
894 l_year_end,
895 l_period_start,
896 l_period_end,
897 l_defined_balance_id,
898 l_group_by_gre,
899 l_tax_unit_context,
900 l_jurisdiction_context);
901
902 /*
903 if l_eoy_tax_unit_id <> 99999 then
904 open c_eoy_gre;
905 end if;
906 else
907 open c_eoy_all;
908 */
909
910
911
912 -- Code modification for bug 10399514 starts here
913 /* Initializing variable */
914 l_person_on := FALSE ;
915
916 Begin
917 select report_format
918 into l_report_format
919 from pay_report_format_mappings_f
920 where report_type = l_report_type
921 and report_qualifier = l_state
922 and report_category = l_report_cat ;
923 Exception
924 When Others Then
925 l_report_format := Null ;
926 End ;
927
928 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
929 p_report_format => l_report_format,
930 p_report_qualifier => l_state,
931 p_report_category => l_report_cat) ;
932
933 -- open c_eoy_qbin;
934
935 if l_person_on then
936 hr_utility.trace('opening c_eoy_gre_range CURSOR');
937 OPEN c_eoy_gre_range ;
938 else
939 hr_utility.trace('opening c_eoy_gre CURSOR');
940 OPEN c_eoy_gre;
941 end if ;
942
943 -- Code modification for bug 10399514 ends here
944
945 /* Loop for all rows returned for SQL statement. */
946
947 hr_utility.trace('Entering loop');
948
949 loop
950
951 if l_eoy_tax_unit_id <> 99999 then
952
953 hr_utility.trace('Fetching person id');
954
955 -- Code modification for bug 10399514 starts here
956 if l_person_on then
957 hr_utility.trace('fetching from c_eoy_gre_range CURSOR');
958 fetch c_eoy_gre_range
959 into l_person_id;
960 exit when c_eoy_gre_range%NOTFOUND;
961 else
962 hr_utility.trace('fetching from c_eoy_gre CURSOR');
963 fetch c_eoy_gre
964 into l_person_id;
965 exit when c_eoy_gre%NOTFOUND;
966 end if ;
967
968 l_tax_unit_id := l_eoy_tax_unit_id;
969
970 -- Code modification for bug 10399514 ends here
971 /*
972 else
973
974 fetch c_eoy_all into l_person_id,
975 l_assignment_id,
976 l_tax_unit_id,
977 l_effective_end_date;
978
979 exit when c_eoy_all%NOTFOUND;
980 */
981 end if;
982
983
984 /* If the new row is the same as the previous row according to the way
985 the rows are grouped then discard the row ie. grouping by GRE
986 requires a single row for each person / GRE combination. */
987
988 hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
989 hr_utility.trace('previous tax unit id is '||
990 to_char(l_prev_tax_unit_id));
991
992 if ( l_person_id = l_prev_person_id and
993 l_tax_unit_id = l_prev_tax_unit_id) then
994
995 hr_utility.trace('Not creating Asg_action, duplicate');
996 null;
997
998 else
999
1000 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1001 hr_utility.trace('person is '|| to_char(l_person_id));
1002 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1003
1004
1005 /* Have a new unique row according to the way the rows are grouped.
1006 The inclusion of the person is dependent on having a non zero
1007 balance. If the balance is non zero then an assignment action
1008 is created to indicate their inclusion in the T4 Magnetic Media
1009 and T4 Paper Reports. */
1010
1011 /* Get the primary assignment */
1012 open c_get_asg_id(l_person_id);
1013 fetch c_get_asg_id into l_primary_asg;
1014 if c_get_asg_id%NOTFOUND then
1015 close c_get_asg_id;
1016 hr_utility.trace('Primary Asg Not found');
1017 hr_utility.raise_error;
1018 else
1019 close c_get_asg_id;
1020 end if;
1021
1022
1023 /* Bug#3267520, checking if any earnings exists or not */
1024 ln_max_aa_id := null;
1025 ln_non_taxable_earnings := 0;
1026 ln_gross_earnings := 0;
1027 ln_no_gross_earnings := 0;
1028
1029 begin
1030 open c_get_latest_asg(l_person_id );
1031 fetch c_get_latest_asg into ln_max_aa_id;
1032 close c_get_latest_asg;
1033 hr_utility.trace('Action creation Max assignment_action_id : ' ||
1034 to_char(ln_max_aa_id));
1035
1036 exception
1037 when no_data_found then
1038 ln_max_aa_id := -9999;
1039 raise_application_error(-20001,
1040 'Balance Assignment Action does not exist for : '
1041 ||to_char(l_person_id));
1042 end;
1043
1044 hr_utility.trace('Setting context');
1045 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1046 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',ln_max_aa_id);
1047
1048 ln_non_taxable_earnings :=
1049 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1050 ('T4 Non Taxable Earnings',
1051 'YTD',ln_max_aa_id,l_primary_asg,NULL,'PER',
1052 l_tax_unit_id,l_bus_group_id,NULL),0);
1053 hr_utility.trace('T4 Non Taxable Earnings :'||
1054 to_char(ln_non_taxable_earnings));
1055
1056 ln_gross_earnings :=
1057 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1058 ('Gross Earnings',
1059 'YTD',ln_max_aa_id,l_primary_asg,NULL,'PER' ,
1060
1061 l_tax_unit_id, l_bus_group_id, NULL),0);
1062 hr_utility.trace('Gross Earnings :'||
1063 to_char(ln_gross_earnings));
1064
1065 ln_no_gross_earnings :=
1066 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1067 ('T4 No Gross Earnings',
1068 'YTD',ln_max_aa_id, l_primary_asg,NULL,'PER' ,
1069 l_tax_unit_id, l_bus_group_id, NULL),0);
1070 hr_utility.trace('T4 No Gross Earnings :'||
1071 to_char(ln_no_gross_earnings));
1072
1073 /* End of adding code for bug#3267520, below If condition
1074 is also part of this bug fix */
1075
1076 If (((ln_gross_earnings <> 0) and
1077 (ln_non_taxable_earnings <> ln_gross_earnings)) or
1078 (ln_no_gross_earnings <> 0)) then
1079
1080 /* Create the assignment action to archive T4 details */
1081
1082 select pay_assignment_actions_s.nextval
1083 into lockingactid
1084 from dual;
1085
1086 /* Insert into pay_assignment_actions. */
1087
1088 hr_utility.trace('creating assignment action');
1089
1090 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1091 pactid,chunk,l_tax_unit_id);
1092
1093 /* Update the serial number column with the person id
1094 so that we can use in the Magnetic Media process
1095 to do an additional check against the assignment table */
1096
1097 hr_utility.trace('updating assignment action');
1098
1099 update pay_assignment_actions aa
1100 set aa.serial_number = to_char(l_person_id)
1101 where aa.assignment_action_id = lockingactid;
1102
1103 hr_utility.trace('Created Assignment action'||
1104 to_char(lockingactid));
1105
1106 End if; --Checking Gross Earnings, No Gross Earnings, NonTaxable Earn
1107
1108 end if; -- validation l_person_id = l_prev_person_id
1109
1110 /* Record the current values for the next time around the loop. */
1111
1112 l_prev_person_id := l_person_id;
1113 l_prev_tax_unit_id := l_tax_unit_id;
1114
1115 end loop;
1116
1117 if l_eoy_tax_unit_id <> 99999 then
1118
1119 -- Code modification for bug 10399514 starts here
1120 if l_person_on then
1121 hr_utility.trace('closing c_eoy_gre_range CURSOR');
1122 close c_eoy_gre_range;
1123 else
1124 hr_utility.trace('closing c_eoy_gre CURSOR');
1125 close c_eoy_gre;
1126 end if ;
1127 -- Code modification for bug 10399514 ends here
1128 /*
1129 else
1130 close c_eoy_all;
1131 */
1132 end if;
1133
1134
1135 end eoy_action_creation;
1136
1137
1138
1139 /*
1140 Name : get_user_entity_id
1141 Purpose : This gets the user_entity_id for a specific database item name.
1142 Arguments : p_dbi_name -> database item name.
1143 Notes :
1144 */
1145
1146 function get_user_entity_id (p_dbi_name in varchar2)
1147 return number is
1148 l_user_entity_id number;
1149
1150 begin
1151
1152 select fdi.user_entity_id
1153 into l_user_entity_id
1154 from ff_database_items fdi,
1155 ff_user_entities fui
1156 where user_name = p_dbi_name
1157 and fdi.user_entity_id = fui.user_entity_id
1158 and fui.legislation_code = 'CA';
1159
1160 return l_user_entity_id;
1161
1162 exception
1163 when others then
1164 hr_utility.trace('Error while getting the user_entity_id for '
1165 || p_dbi_name);
1166 hr_utility.raise_error;
1167
1168 end get_user_entity_id;
1169
1170
1171
1172 /*
1173 Name : compare_archive_data
1174 Purpose : compares Federal YEPP data and Federal YE Amendment Data
1175 Arguments : p_assignment_action_id -> Assignment_action_id
1176 p_locked_action_id -> YEPP Assignment_action_id
1177 p_jurisdiction -> Jurisdiction_code
1178
1179 Notes : Used specifically for Federal YE Amendment Pre-Process (YE-2003)
1180 */
1181
1182 Function compare_archive_data(p_assignment_action_id in number
1183 ,p_locked_action_id in number
1184 ,p_jurisdiction in varchar2
1185 ) return varchar2 is
1186 TYPE act_info_rec IS RECORD
1187 ( archive_context1 number(25)
1188 ,archive_ue_id number(25)
1189 ,archive_value varchar2(240)
1190 );
1191
1192 TYPE number_data_type_table IS TABLE OF NUMBER
1193 INDEX BY BINARY_INTEGER;
1194
1195 TYPE action_info_table IS TABLE OF act_info_rec
1196 INDEX BY BINARY_INTEGER;
1197
1198 ltr_amend_arch_data action_info_table;
1199 ltr_yepp_arch_data action_info_table;
1200 ltr_amend_emp_data action_info_table;
1201 ltr_yepp_emp_data action_info_table;
1202 ltr_emp_ue_id number_data_type_table;
1203
1204 -- Cursor to get archived values based on Asg_act_id,jurisdiction
1205 cursor c_get_emp_t4box_data(cp_asg_act_id number,
1206 cp_jurisdiction varchar2) is
1207 SELECT fai1.context1,fdi1.user_entity_id,fai1.value
1208 FROM FF_ARCHIVE_ITEMS FAI1,
1209 ff_database_items fdi1,
1210 ff_archive_item_contexts faic,
1211 ff_contexts fc
1212 WHERE FAI1.USER_ENTITY_ID = fdi1.user_entity_id
1213 and fai1.archive_item_id = faic.archive_item_id
1214 and fc.context_id = faic.context_id
1215 and fc.context_name = 'JURISDICTION_CODE'
1216 and faic.context = cp_jurisdiction
1217 AND FAI1.CONTEXT1 = cp_asg_act_id
1218 AND fdi1.user_name <> 'CAEOY_T4_AMENDMENT_FLAG'
1219 order by fdi1.user_name;
1220
1221 -- Cursor to get archived values based on Asg_act_id
1222 cursor c_get_employee_data(cp_asg_act_id number,
1223 cp_dbi_ue_id number) is
1224 select fai.context1,fai.user_entity_id,fai.value
1225 from ff_archive_items fai
1226 where fai.user_entity_id = cp_dbi_ue_id
1227 and fai.context1 = cp_asg_act_id;
1228
1229 i number;
1230 j number;
1231 lv_flag varchar2(2);
1232 ln_yepp_box_count number;
1233 ln_amend_box_count number;
1234
1235
1236 begin
1237 -- hr_utility.trace_on('Y','TEST');
1238 /* Initialization Process */
1239 lv_flag := 'N';
1240 if ltr_amend_arch_data.count > 0 then
1241 ltr_amend_arch_data.delete;
1242 end if;
1243
1244 if ltr_yepp_arch_data.count > 0 then
1245 ltr_yepp_arch_data.delete;
1246 end if;
1247
1248 if ltr_amend_emp_data.count > 0 then
1249 ltr_amend_emp_data.delete;
1250 end if;
1251
1252 if ltr_yepp_emp_data.count > 0 then
1253 ltr_yepp_emp_data.delete;
1254 end if;
1255
1256 if ltr_emp_ue_id.count > 0 then
1257 ltr_emp_ue_id.delete;
1258 end if;
1259
1260
1261 j := 0;
1262 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_INITIAL');
1263
1264 j := j+1;
1265 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_FIRST_NAME');
1266
1267 j := j+1;
1268 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_LAST_NAME');
1269
1270 j := j+1;
1271 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_SIN');
1272
1273 j := j+1;
1274 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_NUMBER');
1275
1276 j := j+1;
1277 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE1');
1278
1279 j := j+1;
1280 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE2');
1281
1282 j := j+1;
1283 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE3');
1284
1285 j := j+1;
1286 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE4');
1287
1288 j := j+1;
1289 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_CITY');
1290
1291 j := j+1;
1292 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_PROVINCE');
1293
1294 j := j+1;
1295 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_COUNTRY');
1296
1297 j := j+1;
1298 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_POSTAL_CODE');
1299
1300
1301 /* Populate T4 Amendment Box Data for an assignment_action */
1302 open c_get_emp_t4box_data(p_assignment_action_id,p_jurisdiction);
1303 hr_utility.trace('Populating T4 Amendment Box Data ');
1304 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1305 ln_amend_box_count := 0;
1306 loop
1307 fetch c_get_emp_t4box_data into ltr_amend_arch_data(ln_amend_box_count);
1308 exit when c_get_emp_t4box_data%NOTFOUND;
1309
1310 hr_utility.trace('I :'||to_char(ln_amend_box_count));
1311 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1312 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1313 hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1314 ln_amend_box_count := ln_amend_box_count + 1;
1315 end loop;
1316
1317 close c_get_emp_t4box_data;
1318
1319 /* Populate T4 Amendment Employee Data for an assignment_action */
1320 hr_utility.trace('Populating Amendment Employee Data ');
1321 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1322 for i in 0 .. j
1323 loop
1324 open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
1325 fetch c_get_employee_data into ltr_amend_emp_data(i);
1326
1327 hr_utility.trace('I :'||to_char(i));
1328 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
1329 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
1330 hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
1331
1332 close c_get_employee_data;
1333 end loop;
1334
1335
1336 /* Populate T4 YEPP Box Data for an assignment_action */
1337 open c_get_emp_t4box_data(p_locked_action_id,p_jurisdiction);
1338 hr_utility.trace('Populating T4 YEPP Box Data ');
1339 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1340 ln_yepp_box_count := 0;
1341 loop
1342 fetch c_get_emp_t4box_data into ltr_yepp_arch_data(ln_yepp_box_count);
1343 exit when c_get_emp_t4box_data%NOTFOUND;
1344
1345 hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1346 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1347 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1348 hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1349 ln_yepp_box_count := ln_yepp_box_count + 1;
1350 end loop;
1351
1352 close c_get_emp_t4box_data;
1353
1354 /* Populate T4 YEPP Employee Data for an assignment_action */
1355 hr_utility.trace('Populating YEPP Employee Data ');
1356 hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
1357 for i in 0 .. j
1358 loop
1359 open c_get_employee_data(P_locked_action_id,ltr_emp_ue_id(i));
1360 fetch c_get_employee_data into ltr_yepp_emp_data(i);
1361 exit when c_get_employee_data%NOTFOUND;
1362
1363 hr_utility.trace('I :'||to_char(i));
1364 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
1365 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
1366 hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
1367
1368 close c_get_employee_data;
1369 end loop;
1370
1371 /* Compare T4 Amendment Box Data and T4 YEPP Box Data for an
1372 assignment_action */
1373
1374 hr_utility.trace('Comparing T4 Amend and T4 YEPP Box Data ');
1375
1376 if ln_yepp_box_count <> ln_amend_box_count then
1377 lv_flag := 'Y';
1378 elsif ln_yepp_box_count = ln_amend_box_count then
1379 for i in ltr_yepp_arch_data.first..ltr_yepp_arch_data.last
1380 loop
1381 if (ltr_yepp_arch_data(i).archive_ue_id =
1382 ltr_amend_arch_data(i).archive_ue_id) then
1383
1384 if ((ltr_yepp_arch_data(i).archive_value <>
1385 ltr_amend_arch_data(i).archive_value) or
1386 (ltr_yepp_arch_data(i).archive_value is null and
1387 ltr_amend_arch_data(i).archive_value is not null) or
1388 (ltr_yepp_arch_data(i).archive_value is not null and
1389 ltr_amend_arch_data(i).archive_value is null)) then
1390
1391 lv_flag := 'Y';
1392 hr_utility.trace('Archive_UE_id with differnt value :'||to_char(ltr_yepp_arch_data(i).archive_ue_id));
1393 exit;
1394 end if;
1395 end if;
1396 end loop;
1397 end if;
1398
1399 /* Compare T4 Employee Data and T4 YEPP Employee Data for an
1400 assignment_action */
1401 If lv_flag <> 'Y' then
1402
1403 hr_utility.trace('Comparing T4 Amend and T4 YEPP Employee Data ');
1404 for i in ltr_yepp_emp_data.first..ltr_yepp_emp_data.last
1405 loop
1406 if (ltr_yepp_emp_data(i).archive_ue_id =
1407 ltr_amend_emp_data(i).archive_ue_id) then
1408
1409 if ((ltr_yepp_emp_data(i).archive_value <>
1410 ltr_amend_emp_data(i).archive_value) or
1411 (ltr_yepp_emp_data(i).archive_value is null and
1412 ltr_amend_emp_data(i).archive_value is not null) or
1413 (ltr_yepp_emp_data(i).archive_value is not null and
1414 ltr_amend_emp_data(i).archive_value is null)) then
1415
1416 lv_flag := 'Y';
1417 hr_utility.trace('Archive_UE_id with different value :'||
1418 to_char(ltr_yepp_arch_data(i).archive_ue_id));
1419 exit;
1420 end if;
1421 end if;
1422 end loop;
1423
1424 End if; -- p_flag <> 'Y'
1425
1426 /* If there is no value difference for Entire Employee data then set
1427 flag to 'N' */
1428
1429 if lv_flag <> 'Y' then
1430 lv_flag := 'N';
1431 hr_utility.trace('No value difference for an Employee Asg Action: '||
1432 to_char(p_assignment_action_id));
1433 end if;
1434
1435 hr_utility.trace('lv_flag :'||lv_flag);
1436 return lv_flag;
1437 -- hr_utility.trace_off;
1438 end compare_archive_data;
1439
1440
1441
1442 /* Name : eoy_archive_gre_data
1443 Purpose : This performs the CA specific employer data archiving.
1444 Arguments :
1445 Notes :
1446 */
1447
1448 procedure eoy_archive_gre_data(p_payroll_action_id in number,
1449 p_tax_unit_id in number,
1450 p_transmitter_gre_id in number)
1451 is
1452
1453 l_user_entity_id number;
1454 l_taxunit_context_id number;
1455 l_jursd_context_id number;
1456 l_value varchar2(240);
1457 l_sit_uid number;
1458 l_sui_uid number;
1459 l_fips_uid number;
1460 l_counter number;
1461 l_seq_tab pay_ca_eoy_archive.number_data_type_table;
1462 l_context_id_tab pay_ca_eoy_archive.number_data_type_table;
1463 l_context_val_tab pay_ca_eoy_archive.char240_data_type_table;
1464 l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1465 l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
1466 l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
1467 l_arch_gre_step number := 0;
1468 l_name varchar2(240);
1469 l_business_group_id number;
1470 l_seq number;
1471 l_context_id number;
1472 l_context_val varchar2(240);
1473 l_employer_ein varchar2(240);
1474 l_address_line_1 varchar2(240);
1475 l_address_line_2 varchar2(240);
1476 l_address_line_3 varchar2(240);
1477 l_town_or_city varchar2(240);
1478 l_province_code varchar2(240);
1479 l_postal_code varchar2(240);
1480 l_country_code varchar2(240);
1481 l_accounting_contact_name varchar2(240);
1482 l_accounting_contact_phone varchar2(240);
1483 l_accounting_contact_area_code varchar2(240);
1484 l_technical_contact_area_code varchar2(240);
1485 l_accounting_contact_extension varchar2(240);
1486 l_proprietor_sin_1 varchar2(240);
1487 l_proprietor_sin_2 varchar2(240);
1488 l_transmitter_name varchar2(240);
1489 l_transmitter_type_indicator varchar2(240);
1490 l_transmitter_type_code varchar2(240);
1491 l_transmitter_data_type_code varchar2(240);
1492 l_transmitter_number varchar2(240);
1493 l_transmitter_addr_line_1 varchar2(240);
1494 l_transmitter_addr_line_2 varchar2(240);
1495 l_transmitter_addr_line_3 varchar2(240);
1496 l_transmitter_city varchar2(240);
1497 l_transmitter_province varchar2(240);
1498 /*l_Federal_Youth_Hire_Ind varchar2(80); */
1499 l_transmitter_postal_code varchar2(240);
1500 l_transmitter_country varchar2(240);
1501 l_transmitter_orgid number;
1502 l_technical_contact_name varchar2(240);
1503 l_technical_contact_phone varchar2(240);
1504 l_technical_contact_extn varchar2(240);
1505 l_technical_contact_email varchar2(240);
1506 l_technical_contact_language varchar2(240);
1507
1508 -- Added for bug 10388148
1509 l_trans_acct_contact_name varchar2(240);
1510 l_trans_acct_contact_area_code varchar2(240);
1511 l_trans_acct_contact_phone varchar2(240);
1512 l_trans_acct_contact_extn varchar2(240);
1513 l_trans_proprietor_sin_1 varchar2(240);
1514 l_trans_proprietor_sin_2 varchar2(240);
1515 -- Added for bug 10388148
1516
1517 l_object_version_number number;
1518 l_some_warning boolean;
1519 l_archive_item_id number;
1520 l_taxation_year varchar2(240);
1521 l_effective_date date;
1522 result number;
1523 employer_info_found varchar2(1);
1524 l_ei_rate number;
1525
1526 cursor cur_bg(p_tax_unit_id1 number) is
1527 select
1528 business_group_id
1529 from
1530 hr_all_organization_units
1531 where
1532 organization_id = p_tax_unit_id1;
1533
1534 cursor employer_info is
1535 select
1536 nvl(hoi6.ORG_INFORMATION9,hou.name) GRE_stat_report_name,
1537 hoi6.ORG_INFORMATION1 Employer_identification_number,
1538 hl.ADDRESS_LINE_1 GRE_addrline1,
1539 hl.ADDRESS_LINE_2 GRE_addrline2,
1540 hl.ADDRESS_LINE_3 GRE_addrline3,
1541 hl.TOWN_OR_CITY GRE_town_or_city,
1542 DECODE(hl.STYLE , 'US' , hl.REGION_2 ,
1543 'CA' , hl.REGION_1 ,
1544 'CA_GLB',hl.region_1, ' ') GRE_province,
1545 hl.POSTAL_CODE GRE_postal_code,
1546 hl.COUNTRY GRE_country,
1547 hoi6.org_information3 ei_rate
1548 from
1549 hr_all_organization_units hou,
1550 hr_organization_information hoi6,
1551 hr_locations_all hl
1552 where
1553 hou.organization_id = p_tax_unit_id
1554 and hou.organization_id = hoi6.organization_id
1555 and hoi6.org_information_context = 'Canada Employer Identification'
1556 and hoi6.org_information5 in ('T4/RL1','T4/RL2')
1557 and hou.location_id = hl.location_id;
1558
1559 cursor cur_employer_info is
1560 select
1561 hoi5.ORG_INFORMATION10 GRE_acct_contact_name,
1562 hoi5.ORG_INFORMATION12 GRE_acct_contact_phone,
1563 hoi5.ORG_INFORMATION11 GRE_acct_contact_area_code,
1564 hoi5.ORG_INFORMATION13 GRE_acct_contact_extn,
1565 hoi5.ORG_INFORMATION14 GRE_Proprietor_SIN#1,
1566 hoi5.ORG_INFORMATION15 GRE_Proprietor_SIN#2/*,
1567 hoi5.ORG_INFORMATION16 GRE_Fedyouth_hire_Prgind*/
1568 from
1569 hr_organization_information hoi5
1570 where
1571 hoi5.organization_id = p_tax_unit_id
1572 and hoi5.org_information_context = 'Fed Magnetic Reporting';
1573
1574 cursor cur_transmitter_info is
1575 select
1576 nvl(hoi3.ORG_INFORMATION9,hou.name) trans_stat_report_name,
1577 hl.ADDRESS_LINE_1 trans_addrline1,
1578 hl.ADDRESS_LINE_2 trans_addrline2,
1579 hl.ADDRESS_LINE_3 trans_addrline3,
1580 hl.TOWN_OR_CITY trans_town_or_city,
1581 DECODE(hl.STYLE , 'US', hl.REGION_2,
1582 'CA', hl.REGION_1,
1583 'CA_GLB',hl.region_1, ' ') trans_province,
1584 hl.POSTAL_CODE trans_postal_code,
1585 hl.COUNTRY trans_country,
1586 hoi2.org_information5 trans_type_indicator,
1587 hoi2.ORG_INFORMATION4 trans_number,
1588 hoi2.ORG_INFORMATION2 trans_type_code,
1589 hoi2.ORG_INFORMATION3 trans_datatype_code,
1590 hoi2.ORG_INFORMATION6 trans_tech_contact_name,
1591 hoi2.ORG_INFORMATION8 trans_tech_contact_phone,
1592 hoi2.ORG_INFORMATION7 trans_tech_contact_areacode,
1593 hoi2.ORG_INFORMATION9 trans_tech_contact_lang,
1594 -- Added for bug 10388148
1595 hoi2.ORG_INFORMATION10 trans_acct_contact_name,
1596 hoi2.ORG_INFORMATION11 trans_acct_contact_area_code,
1597 hoi2.ORG_INFORMATION12 trans_acct_contact_phone,
1598 hoi2.ORG_INFORMATION13 trans_acct_contact_extn,
1599 hoi2.ORG_INFORMATION14 trans_proprietor_SIN#1,
1600 hoi2.ORG_INFORMATION15 trans_proprietor_SIN#2,
1601 -- Added for bug 10388148
1602 hoi2.ORG_INFORMATION17 trans_tech_contact_extn,
1603 hoi2.ORG_INFORMATION18 trans_tech_contact_email
1604 from
1605 hr_all_organization_units hou,
1606 hr_organization_information hoi2,
1607 hr_organization_information hoi3,
1608 hr_locations_all hl
1609 where
1610 hou.organization_id = p_transmitter_gre_id
1611 and hou.organization_id = hoi2.organization_id
1612 and hoi2.org_information_context = 'Fed Magnetic Reporting'
1613 and hoi2.org_information1 = 'Y'
1614 -- and hoi2.org_information2 = '904' --comented for bug 3973040
1615 and hou.organization_id = hoi3.organization_id
1616 and hoi3.org_information_context = 'Canada Employer Identification'
1617 and hou.location_id = hl.location_id;
1618
1619 begin
1620 /* payroll action level database items */
1621
1622 l_arch_gre_step := 30;
1623
1624 /* Archive the Employer level data */
1625
1626 --hr_utility.trace_on('Y','CAEOY');
1627 hr_utility.trace('getting employer data ');
1628
1629 open cur_bg(p_tax_unit_id);
1630 fetch
1631 cur_bg
1632 into
1633 l_business_group_id;
1634 close cur_bg;
1635
1636 open employer_info;
1637 fetch employer_info
1638 into
1639 l_name,
1640 l_employer_ein,
1641 l_address_line_1,
1642 l_address_line_2,
1643 l_address_line_3,
1644 l_town_or_city,
1645 l_province_code,
1646 l_postal_code,
1647 l_country_code,
1648 l_ei_rate;
1649
1650 if employer_info%NOTFOUND then
1651
1652 hr_utility.trace('cannot find employer data ');
1653 employer_info_found := 'N';
1654
1655 l_employer_ein := null;
1656 l_address_line_1 := null;
1657 l_address_line_2 := null;
1658 l_address_line_3 := null;
1659 l_town_or_city := null;
1660 l_province_code := null;
1661 l_postal_code := null;
1662 l_country_code := null;
1663 l_name := null;
1664
1665 close employer_info;
1666
1667 else
1668
1669 close employer_info;
1670 hr_utility.trace('Employer data found !!!! ');
1671 employer_info_found := 'Y';
1672
1673 end if;
1674
1675 open cur_employer_info;
1676 fetch cur_employer_info
1677 into
1678 l_accounting_contact_name,
1679 l_accounting_contact_phone ,
1680 l_accounting_contact_area_code,
1681 l_accounting_contact_extension,
1682 l_proprietor_sin_1,
1683 l_proprietor_sin_2;/*,
1684 l_federal_youth_hire_ind*/
1685
1686 if cur_employer_info%NOTFOUND then
1687
1688 hr_utility.trace('cannot find employer data 2 ');
1689 employer_info_found := 'N';
1690
1691 l_proprietor_sin_1 := null;
1692 l_proprietor_sin_2 := null;
1693 /* l_federal_youth_hire_ind := null; */
1694 l_accounting_contact_name := null;
1695 l_accounting_contact_phone := null;
1696 l_accounting_contact_area_code := null;
1697 l_accounting_contact_extension := null;
1698 l_accounting_contact_area_code := null;
1699 l_accounting_contact_extension := null;
1700
1701 close cur_employer_info;
1702
1703 else
1704
1705 close cur_employer_info;
1706 hr_utility.trace('Employer data found 2 !!!! ');
1707 employer_info_found := 'Y';
1708
1709 end if;
1710
1711 open cur_transmitter_info;
1712 fetch cur_transmitter_info
1713 into
1714 l_transmitter_name,
1715 l_transmitter_addr_line_1,
1716 l_transmitter_addr_line_2,
1717 l_transmitter_addr_line_3,
1718 l_transmitter_city,
1719 l_transmitter_province,
1720 l_transmitter_postal_code,
1721 l_transmitter_country,
1722 l_Transmitter_Type_Indicator,
1723 l_Transmitter_number,
1724 l_Transmitter_Type_code,
1725 l_Transmitter_data_type_code,
1726 l_technical_contact_name,
1727 l_technical_contact_phone,
1728 l_technical_contact_area_code,
1729 l_technical_contact_language,
1730 -- Added for bug 10388148
1731 l_trans_acct_contact_name,
1732 l_trans_acct_contact_area_code,
1733 l_trans_acct_contact_phone,
1734 l_trans_acct_contact_extn,
1735 l_trans_proprietor_sin_1,
1736 l_trans_proprietor_sin_2,
1737 -- Added for bug 10388148
1738 l_technical_contact_extn,
1739 l_technical_contact_email;
1740
1741 if cur_transmitter_info%NOTFOUND then
1742
1743 close cur_transmitter_info;
1744 hr_utility.trace('Transmitter information not found');
1745
1746 l_transmitter_name := null;
1747 l_transmitter_addr_line_1 := null;
1748 l_transmitter_addr_line_2 := null;
1749 l_transmitter_addr_line_3 := null;
1750 l_transmitter_city := null;
1751 l_transmitter_province := null;
1752 l_transmitter_postal_code := null;
1753 l_transmitter_country := null;
1754 l_Transmitter_Type_Indicator := null;
1755 l_Transmitter_number := null;
1756 l_Transmitter_Type_code := null;
1757 l_Transmitter_data_type_code := null;
1758 l_technical_contact_name := null;
1759 l_technical_contact_phone := null;
1760 l_technical_contact_area_code := null;
1761 l_technical_contact_language := null;
1762
1763 employer_info_found := 'N';
1764
1765 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1766 hr_utility.set_message_token('ORGIND','GRE');
1767 /* push message into pay_message_lines */
1768 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1769 pay_core_utils.push_token('ORGIND','GRE');
1770 hr_utility.raise_error;
1771
1772 else
1773
1774 close cur_transmitter_info;
1775 employer_info_found := 'Y';
1776
1777 end if;
1778
1779 -- Added for bug 10388148 from here
1780 if l_accounting_contact_name is null then
1781 l_accounting_contact_name := l_trans_acct_contact_name;
1782 end if;
1783
1784 if l_accounting_contact_phone is null then
1785 l_accounting_contact_phone := l_trans_acct_contact_phone;
1786 end if;
1787
1788 if l_accounting_contact_area_code is null then
1789 l_accounting_contact_area_code := l_trans_acct_contact_area_code;
1790 end if;
1791
1792 if l_accounting_contact_extension is null then
1793 l_accounting_contact_extension := l_trans_acct_contact_extn;
1794 end if;
1795
1796 if l_proprietor_sin_1 is null then
1797 l_proprietor_sin_1 := l_trans_proprietor_sin_1;
1798 end if;
1799
1800 if l_proprietor_sin_2 is null then
1801 l_proprietor_sin_2 := l_trans_proprietor_sin_2;
1802 end if;
1803 -- Added for bug 10388148 till here
1804 begin
1805
1806 select to_char(effective_date,'YYYY'),
1807 add_months(trunc(effective_date, 'Y'),12) - 1
1808 into l_taxation_year,
1809 l_effective_date
1810 from pay_payroll_actions
1811 where payroll_action_id = p_payroll_action_id;
1812
1813 exception when no_data_found then
1814 l_taxation_year := null;
1815 l_effective_date := null;
1816
1817 end;
1818
1819 select context_id
1820 into l_taxunit_context_id
1821 from ff_contexts
1822 where context_name = 'TAX_UNIT_ID';
1823
1824 l_counter := 0;
1825 l_arch_gre_step := 40;
1826
1827 l_counter := l_counter + 1;
1828 l_user_entity_name_tab(l_counter) := 'CAEOY_TAXATION_YEAR';
1829 l_user_entity_value_tab(l_counter) := l_taxation_year;
1830
1831 l_counter := l_counter + 1;
1832 l_user_entity_name_tab(l_counter) := 'CAEOY_TAX_UNIT_ID';
1833 l_user_entity_value_tab(l_counter) := p_tax_unit_id;
1834
1835 l_counter := l_counter + 1;
1836 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_COUNTRY';
1837 l_user_entity_value_tab(l_counter) := l_transmitter_country;
1838
1839 l_counter := l_counter + 1;
1840 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NAME';
1841 l_user_entity_value_tab(l_counter) := l_transmitter_name;
1842
1843 l_counter := l_counter + 1;
1844 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE1';
1845 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_1;
1846
1847 l_counter := l_counter + 1;
1848 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE2';
1849 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_2;
1850
1851 -- Bug 4517693
1852 l_counter := l_counter + 1;
1853 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE3';
1854 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_3;
1855
1856 l_counter := l_counter + 1;
1857 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_CITY';
1858 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1859
1860 l_counter := l_counter + 1;
1861 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_PROVINCE';
1862 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1863
1864 l_counter := l_counter + 1;
1865 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_POSTAL_CODE';
1866 l_user_entity_value_tab(l_counter) := l_transmitter_postal_code;
1867
1868 l_counter := l_counter + 1;
1869 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_INDICATOR';
1870 l_user_entity_value_tab(l_counter) := l_transmitter_type_indicator;
1871
1872 l_counter := l_counter + 1;
1873 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NUMBER';
1874 l_user_entity_value_tab(l_counter) := l_transmitter_number;
1875
1876 l_counter := l_counter + 1;
1877 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_CODE';
1878 l_user_entity_value_tab(l_counter) := l_transmitter_type_code;
1879
1880 l_counter := l_counter + 1;
1881 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_DATA_TYPE_CODE';
1882 l_user_entity_value_tab(l_counter) := l_transmitter_data_type_code;
1883
1884 l_counter := l_counter + 1;
1885 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_NAME';
1886 l_user_entity_value_tab(l_counter) := l_technical_contact_name;
1887
1888 l_counter := l_counter + 1;
1889 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_PHONE';
1890 l_user_entity_value_tab(l_counter) := l_technical_contact_phone;
1891
1892 l_counter := l_counter + 1;
1893 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_AREA_CODE';
1894 l_user_entity_value_tab(l_counter) := l_technical_contact_area_code;
1895
1896 l_counter := l_counter + 1;
1897 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EXTN';
1898 l_user_entity_value_tab(l_counter) := l_technical_contact_extn;
1899
1900 l_counter := l_counter + 1;
1901 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EMAIL';
1902 l_user_entity_value_tab(l_counter) := l_technical_contact_email;
1903
1904 l_counter := l_counter + 1;
1905 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_LANGUAGE';
1906 l_user_entity_value_tab(l_counter) := l_technical_contact_language;
1907
1908 l_counter := l_counter + 1;
1909 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_NAME';
1910 l_user_entity_value_tab(l_counter) := l_accounting_contact_name;
1911
1912 l_counter := l_counter + 1;
1913 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_PHONE';
1914 l_user_entity_value_tab(l_counter) := l_accounting_contact_phone ;
1915
1916 l_counter := l_counter + 1;
1917 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE';
1918 l_user_entity_value_tab(l_counter) := l_accounting_contact_area_code ;
1919
1920 l_counter := l_counter + 1;
1921 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_EXTENSION';
1922 l_user_entity_value_tab(l_counter) := l_accounting_contact_extension ;
1923
1924
1925 l_counter := l_counter + 1;
1926 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_NAME';
1927 l_user_entity_value_tab(l_counter) := l_name;
1928
1929 l_counter := l_counter + 1;
1930 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER';
1931 l_user_entity_value_tab(l_counter) := l_employer_ein;
1932
1933 l_counter := l_counter + 1;
1934 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE1';
1935 l_user_entity_value_tab(l_counter) := l_address_line_1;
1936
1937 l_counter := l_counter + 1;
1938 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE2';
1939 l_user_entity_value_tab(l_counter) := l_address_line_2;
1940
1941 -- Bug 4517693
1942 l_counter := l_counter + 1;
1943 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE3';
1944 l_user_entity_value_tab(l_counter) := l_address_line_3;
1945
1946 l_counter := l_counter + 1;
1947 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_CITY';
1948 l_user_entity_value_tab(l_counter) := l_town_or_city;
1949
1950 l_counter := l_counter + 1;
1951 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_PROVINCE';
1952 l_user_entity_value_tab(l_counter) := l_province_code;
1953
1954 l_counter := l_counter + 1;
1955 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_COUNTRY';
1956 l_user_entity_value_tab(l_counter) := l_country_code;
1957
1958 l_counter := l_counter + 1;
1959 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_POSTAL_CODE';
1960 l_user_entity_value_tab(l_counter) := l_postal_code;
1961
1962
1963 l_counter := l_counter + 1;
1964 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN1';
1965 l_user_entity_value_tab(l_counter) := l_proprietor_sin_1;
1966
1967 l_counter := l_counter + 1;
1968 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN2';
1969 l_user_entity_value_tab(l_counter) := l_proprietor_sin_2;
1970
1971 /* l_counter := l_counter + 1;
1972 l_user_entity_name_tab(l_counter):='CAEOY_FEDERAL_YOUTH_HIRE_PROGRAM_INDICATOR';
1973 l_user_entity_value_tab(l_counter) := l_federal_youth_hire_ind; */
1974
1975 l_counter := l_counter + 1;
1976 l_user_entity_name_tab(l_counter) := 'CAEOY_GRE_EI_RATE';
1977 l_user_entity_value_tab(l_counter) := l_ei_rate;
1978
1979 for i in 1..l_counter loop
1980
1981 l_arch_gre_step := 42;
1982 hr_utility.trace('calling archive API ' || l_user_entity_name_tab(i));
1983 ff_archive_api.create_archive_item(
1984 p_archive_item_id => l_archive_item_id
1985 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1986 ,p_archive_value => l_user_entity_value_tab(i)
1987 ,p_archive_type => 'PA'
1988 ,p_action_id => p_payroll_action_id
1989 ,p_legislation_code => 'CA'
1990 ,p_object_version_number => l_object_version_number
1991 ,p_some_warning => l_some_warning
1992 );
1993 hr_utility.trace('Ended calling archive API');
1994 l_arch_gre_step := 47;
1995
1996 end loop;
1997
1998 g_archive_flag := 'Y';
1999 exception
2000 when others then
2001 g_archive_flag := 'N';
2002 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2003 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2004 if l_arch_gre_step = 30 and l_transmitter_name is null then
2005 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2006 hr_utility.set_message_token('ORGIND','GRE');
2007 end if;
2008
2009 hr_utility.raise_error;
2010
2011 end eoy_archive_gre_data;
2012
2013 /* Name : chk_gre_archive
2014 Purpose : Function to check if the employer level data has been archived
2015 or not.
2016 Arguments :
2017 Notes :
2018 */
2019
2020 function chk_gre_archive (p_payroll_action_id number) return boolean is
2021
2022 l_flag varchar2(1);
2023
2024 cursor c_chk_payroll_action is
2025 select 'Y'
2026 from dual
2027 where exists (select null
2028 from ff_archive_items fai
2029 where fai.context1 = p_payroll_action_id
2030 and archive_type = 'PA');
2031 begin
2032
2033 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2034
2035 if g_archive_flag = 'Y' then
2036 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2037 return (TRUE);
2038 else
2039
2040 hr_utility.trace('chk_gre_archive - opening cursor');
2041
2042 open c_chk_payroll_action;
2043 fetch c_chk_payroll_action into l_flag;
2044 if c_chk_payroll_action%FOUND then
2045 hr_utility.trace('chk_gre_archive - found in cursor');
2046 g_archive_flag := 'Y';
2047 else
2048 hr_utility.trace('chk_gre_archive - not found in cursor');
2049 g_archive_flag := 'N';
2050 end if;
2051
2052 hr_utility.trace('chk_gre_archive - closing cursor');
2053 close c_chk_payroll_action;
2054 if g_archive_flag = 'Y' then
2055 hr_utility.trace('chk_gre_archive - returning true');
2056 return (TRUE);
2057 else
2058 hr_utility.trace('chk_gre_archive - returning false');
2059 return(FALSE);
2060 end if;
2061 end if;
2062 end chk_gre_archive;
2063
2064 /* Name : eoy_archinit
2065 Purpose : This performs the context initialization for the year end
2066 pre-process.
2067 Arguments :
2068 Notes :
2069 */
2070
2071
2072 procedure eoy_archinit(p_payroll_action_id in number) is
2073 l_jurisdiction_code VARCHAR2(30);
2074 l_tax_unit_id NUMBER(15);
2075 l_archive boolean:= FALSE;
2076 l_step number := 0;
2077
2078 cursor c_get_min_chunk is
2079 select min(paa.chunk_number)
2080 from pay_assignment_actions paa
2081 where paa.payroll_action_id = p_payroll_action_id;
2082 begin
2083 open c_get_min_chunk;
2084 fetch c_get_min_chunk into g_min_chunk;
2085 l_step := 1;
2086 if c_get_min_chunk%NOTFOUND then
2087 g_min_chunk := -1;
2088 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
2089 end if;
2090 close c_get_min_chunk;
2091
2092 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
2093 l_step := 2;
2094 l_archive := chk_gre_archive(p_payroll_action_id);
2095
2096 l_step := 3;
2097 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
2098 exception
2099 when others then
2100 raise_application_error(-20001,'eoy_archinit at '
2101 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2102 end eoy_archinit;
2103
2104
2105 /* Name : eoy_archive_data
2106 Purpose : This performs the CA specific employee context setting for the
2107 Year End PreProcess.
2108 Arguments :
2109 Notes :
2110 */
2111
2112 PROCEDURE eoy_archive_data(p_assactid in number,
2113 p_effective_date in date) IS
2114
2115 l_aaid pay_assignment_actions.assignment_action_id%type;
2116 l_aaseq pay_assignment_actions.action_sequence%type;
2117 l_asgid pay_assignment_actions.assignment_id%type;
2118 l_date_earned date;
2119 l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
2120 l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
2121 l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
2122 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2123 l_business_group_id pay_assignment_actions.tax_unit_id%type;
2124 l_year_start date;
2125 l_year_end date;
2126 l_context_no number := 60;
2127 l_count number := 0;
2128 l_jurisdiction varchar2(11);
2129 l_province_uei ff_user_entities.user_entity_id%type;
2130 l_county_uei ff_user_entities.user_entity_id%type;
2131 l_city_uei ff_user_entities.user_entity_id%type;
2132 l_county_sd_uei ff_user_entities.user_entity_id%type;
2133 l_city_sd_uei ff_user_entities.user_entity_id%type;
2134 l_province_abbrev pay_us_states.state_abbrev%type;
2135 l_county_name pay_us_counties.county_name%type;
2136 l_city_name pay_us_city_names.city_name%type;
2137 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
2138 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
2139 l_step number := 0;
2140 l_county_code varchar2(3);
2141 l_city_code varchar2(4);
2142 l_jursd_context_id ff_contexts.context_id%type;
2143 l_taxunit_context_id ff_contexts.context_id%type;
2144 l_seq_tab pay_ca_eoy_archive.number_data_type_table;
2145 l_context_id_tab pay_ca_eoy_archive.number_data_type_table;
2146 l_context_val_tab pay_ca_eoy_archive.char240_data_type_table;
2147 l_chunk number;
2148 l_payroll_action_id number;
2149 l_defined_balance_id number;
2150 l_result number;
2151 l_person_id number;
2152 l_organization_id number;
2153 l_location_id number;
2154 l_first_name varchar2(240);
2155 l_last_name varchar2(240);
2156 l_employee_number varchar2(240);
2157 l_national_identifier varchar2(240);
2158 l_middle_names per_all_people_f.middle_names%TYPE;
2159 l_employee_phone_no varchar2(240);
2160 l_address_line1 varchar2(240);
2161 l_address_line2 varchar2(240);
2162 l_address_line3 varchar2(240);
2163 l_address_line4 varchar2(240);
2164 l_town_or_city varchar2(80);
2165 l_province_code varchar2(80);
2166 l_postal_code varchar2(80);
2167 l_telephone_number varchar2(80);
2168 l_country_code varchar2(80);
2169 l_counter number;
2170 l_archive_item_id number;
2171 result number;
2172 earning_exists number := 0;
2173 ln_non_taxable_earnings number := 0;
2174 ln_no_gross_earnings number := 0;
2175 ln_gross_earnings number := 0;
2176 l_object_version_number number;
2177 l_context_id number;
2178 l_context_val varchar2(80);
2179 l_some_warning boolean;
2180 l_cpp_exempt_flag varchar2(80);
2181 l_ei_exempt_flag varchar2(80);
2182 /* Added by ssmukher for PPIP tax implementation */
2183 l_ppip_exempt_flag varchar2(80);
2184 qc_result number;
2185 l_inputs ff_exec.inputs_t;
2186 l_outputs ff_exec.outputs_t;
2187 l_return_value varchar2(240);
2188 l_invalid_mesg varchar2(240);
2189 l_invalid_sin varchar2(240);
2190 l_formula_id number;
2191 l_effective_start_date date;
2192 l_value number;
2193 old_l_value number;
2194 l_legislative_parameters varchar2(240);
2195 l_footnote_code varchar2(10);
2196 l_footnote_balance varchar2(80);
2197 l_registration_no varchar2(150);
2198 old_l_registration_no varchar2(150);
2199 l_balance_name varchar2(150);
2200 l_negative_balance_exists varchar2(5) ;
2201 l_person_arch_step number;
2202 l_cpp_ee_withheld_pjgy number;
2203 l_qpp_ee_withheld_pjgy number;
2204 l_ei_ee_withheld_pjgy number;
2205 addr pay_ca_rl1_reg.primaryaddress;
2206 l_user_entity_id ff_user_entities.user_entity_id%TYPE;
2207
2208 other_info_amount31 number;
2209 other_info_amount53 number;
2210 other_info_amount78 number;
2211 other_info_amount71 number;
2212
2213 -- ln_taxben_without_remuneration added for bug 13087530
2214 ln_taxben_without_remuneration number := 0;
2215
2216 -- code start for Bug 5698016
2217 other_info_amount81 number;
2218 other_info_amount82 number;
2219 other_info_amount83 number;
2220 other_info_amount84 number;
2221 other_info_amount85 number;
2222 -- code ended for Bug 5698016
2223
2224 -- code start for Bug 9135405
2225 other_info_amount66 number;
2226 other_info_amount67 number;
2227 other_info_amount68 number;
2228 other_info_amount69 number;
2229 -- code ended for Bug 9135405
2230
2231 box_52_exists varchar2(1) ;
2232 -- box_20_exists added for bug 7611439
2233 box_20_exists varchar2(1) ;
2234
2235 l_balance_name1 pay_balance_types.balance_name%TYPE;
2236 l_balance_name2 pay_balance_types.balance_name%TYPE; --rgottipa
2237 lv_empcode_prov varchar2(20);
2238 lv_employment_code varchar2(20);
2239 lv_serial_number varchar2(30);
2240 ld_date_of_birth date;
2241 lv_under18_flag varchar2(2);
2242 lv_over70_flag varchar2(2);
2243 ln_cpp_ee_taxable_pjgy number;
2244 ln_qpp_ee_taxable_pjgy number ;
2245 ln_ei_ee_taxable_pjgy number ;
2246 lv_cpp_archive_exempt_flag varchar2(20);
2247 lv_ei_archive_exempt_flag varchar2(20);
2248 /* Added by ssmukher for PPIP tax implementation */
2249 lv_ppip_archive_exempt_flag varchar2(20);
2250 l_ppip_ee_withheld_pjgy number;
2251 ln_ppip_ee_taxable_pjgy number;
2252
2253 lv_qpp_exempt_flag varchar2(20) ;
2254
2255 /* Added for Bug 4028693 */
2256 l_box14_flag char(1);
2257
2258 /* new variables added for Federal YE Amendment PP */
2259 ld_fapp_effective_date date;
2260 lv_fapp_report_type varchar2(20);
2261 ln_fapp_locked_action_id number;
2262 lv_fapp_prov varchar2(5);
2263 lv_fapp_flag varchar2(2);
2264 lv_fapp_locked_actid_reptype varchar2(20);
2265 ln_fapp_prev_amend_actid number;
2266
2267 l_transmitter_gre_id number;
2268
2269 l_status_indian varchar2(1);
2270 /* Added new variable for Bug 3447439 by ssmukher*/
2271 lv_actual_date date;
2272
2273 -- l_screen_entry_value added by sneelapa for bug 6399498
2274 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%type;
2275
2276 l_each_person_id per_all_people_f.person_id%type;
2277 l_each_asgid per_assignments_f.assignment_id%type;
2278
2279 -- added for bug 10244185
2280 lv_code87_Maxlimit varchar2(30);
2281 lv_code87_excess_amt number := 0;
2282 other_info_amount87 number;
2283 -- end bug 10244185
2284
2285 CURSOR get_person_info(p_asgid number) IS
2286 SELECT
2287 PEOPLE.person_id,
2288 PEOPLE.first_name,
2289 PEOPLE.last_name,
2290 PEOPLE.employee_number,
2291 replace(PEOPLE.national_identifier,' '),
2292 PEOPLE.middle_names,
2293 ASSIGN.organization_id,
2294 ASSIGN.location_id
2295 FROM
2296 per_all_assignments_f ASSIGN,
2297 per_all_people_f PEOPLE
2298 WHERE ASSIGN.assignment_id = p_asgid
2299 and l_date_earned BETWEEN ASSIGN.effective_start_date
2300 AND ASSIGN.effective_end_date
2301 AND PEOPLE.person_id = ASSIGN.person_id
2302 AND PEOPLE.effective_end_date = (select max(effective_end_date) from
2303 per_all_people_f PEOPLE1
2304 where PEOPLE1.person_id = PEOPLE.person_id);
2305
2306 /* Get the jurisdiction code of all the cities
2307 for the person_id corresponding to the
2308 assignment_id . Take it from pay_action_context table. */
2309
2310 cursor c_get_province(p_asgid number) is
2311 select distinct context_value
2312 from pay_action_contexts pac
2313 where pac.assignment_id = p_asgid;
2314
2315 /* for testing , since there is no data in pay_action_contexts table */
2316 cursor c_get_test_province is
2317 select province_abbrev
2318 from pay_ca_provinces_v pac;
2319
2320 -- l_business_group_id condition added by sneelapa, for bug 6399498
2321 /*
2322 cursor c_balance_feed_info (p_balance_name varchar2) is
2323 select nvl(pet.element_information20,'NOT FOUND'),
2324 pbt1.balance_name
2325 from pay_balance_feeds_f pbf,
2326 pay_balance_types pbt,
2327 pay_balance_types pbt1,
2328 pay_input_values_f piv,
2329 pay_element_types_f pet
2330 where pbt.balance_name = p_balance_name
2331 and pbf.balance_type_id = pbt.balance_type_id
2332 and pbf.input_value_id = piv.input_value_id
2333 and piv.element_type_id = pet.element_type_id
2334 and pbt1.balance_type_id = pet.element_information10
2335 -- and pet.element_information_category = 'CA_EARNINGS'
2336 and pet.business_group_id = l_business_group_id
2337 and pet.element_information20 is not null;
2338 */
2339
2340 --CURSOR c_balance_feed_info is modified by sneelapa for bug 6399498
2341 -- For issue reported by QA during testing of above bug.
2342 /* cursor c_balance_feed_info (p_balance_name varchar2,p_each_asgid number) is
2343 select nvl(pet.element_information20,'NOT FOUND'),
2344 pbt1.balance_name,
2345 pev.screen_entry_value
2346 from pay_balance_feeds_f pbf,
2347 pay_balance_types pbt,
2348 pay_balance_types pbt1,
2349 pay_input_values_f piv,
2350 pay_element_types_f pet,
2351 pay_element_entries_f pee,
2352 pay_element_entry_values_f pev
2353 where pbt.balance_name = p_balance_name
2354 and pee.assignment_id = p_each_asgid
2355 and pbf.balance_type_id = pbt.balance_type_id
2356 and pbf.input_value_id = piv.input_value_id
2357 and piv.element_type_id = pet.element_type_id
2358 and pbt1.balance_type_id = pet.element_information10
2359 and pet.business_group_id = l_business_group_id
2360 and pet.element_information20 is not null
2361 and pet.element_type_id = pee.element_type_id
2362 -- and trunc(p_effective_date) between pee.effective_start_date and pee.effective_end_date
2363 and ((pee.effective_start_date <= l_year_end
2364 and pee.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
2365 or
2366 (pee.effective_end_date between l_year_start and l_year_end))
2367 -- and trunc(p_effective_date) between pev.effective_start_date and pev.effective_end_date
2368 and ((pev.effective_start_date <= l_year_end
2369 and pev.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
2370 or
2371 (pev.effective_end_date between l_year_start and l_year_end))
2372 and pee.element_entry_id = pev.element_entry_id
2373 -- and fnd_number.canonical_to_number(pev.screen_entry_value) >= 0
2374 -- and pet.element_information_category = 'CA_EARNINGS'
2375 -- and pev.input_value_id = piv.input_value_id
2376 ; */
2377
2378 /* This CURSOR will bring REGISTRATION NUMBER from the elements
2379 which are processed for the employee irrespective of how they have
2380 fed (directly or indirctly) */
2381
2382 cursor c_balance_feed_info (p_balance_name varchar2,p_each_asgid number) is
2383 select nvl(pet.element_information20,'NOT FOUND'),pbtl.balance_name,rv.result_value
2384 from pay_assignment_actions pa,
2385 pay_payroll_actions ppa,
2386 per_all_assignments_f paf,
2387 pay_element_types_f pet,
2388 pay_run_results r,
2389 pay_run_result_values rv,
2390 pay_input_values_f piv,
2391 pay_balance_feeds_f pbf,
2392 pay_balance_types pbt,
2393 pay_balance_types pbtl
2394 where ppa.payroll_action_id = pa.payroll_action_id
2395 and r.run_result_id=rv.run_result_id
2396 and pet.element_type_id=r.element_type_id
2397 and r.assignment_action_id=pa.assignment_action_id
2398 and piv.input_value_id=rv.input_value_id
2399 and paf.assignment_id = pa.assignment_id
2400 and paf.assignment_id = p_each_asgid
2401 and paf.business_group_id = l_business_group_id
2402 and pbt.balance_name = p_balance_name
2403 and pbf.balance_type_id = pbt.balance_type_id
2404 and pbf.input_value_id = piv.input_value_id
2405 and pet.element_information20 is not null
2406 and pbtl.balance_type_id = pet.element_information10;
2407
2408 -- CURSOR c_diff_assignments is used to retrive all assignments for
2409 -- perticular person in the same GRE
2410 cursor c_diff_assignments(p_person_id number) is
2411 select distinct(paf.assignment_id)
2412 from per_all_assignments_f paf
2413 ,per_all_people_f ppf
2414 ,hr_soft_coding_keyflex_kfv hsc
2415 ,hr_locations_all hl
2416 where ppf.person_id = p_person_id
2417 and ppf.person_id = paf.person_id
2418 and hsc.segment1 = l_tax_unit_id
2419 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
2420 and DECODE(hl.STYLE , 'US' , hl.REGION_2 ,
2421 'CA' , hl.REGION_1 ,
2422 'CA_GLB',hl.region_1, ' ') = l_jurisdiction
2423 and hl.location_id = paf.location_id
2424 and paf.effective_start_date <= l_year_end
2425 and paf.effective_end_date >= l_year_start;
2426
2427 cursor cur_bg(p_tax_unit_id1 number) is
2428 select business_group_id
2429 from hr_all_organization_units
2430 where organization_id = p_tax_unit_id1;
2431
2432 /* New cursors added for Federal YE Amendment Pre-Process Validation */
2433 CURSOR c_get_fapp_prov_emp(cp_assignment_action_id number) IS
2434 select fai.value
2435 from ff_archive_items fai,
2436 ff_database_items fdi
2437 where fdi.user_entity_id = fai.user_entity_id
2438 and fai.context1 = cp_assignment_action_id
2439 and fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
2440
2441 CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
2442 select ppa.report_type
2443 from pay_payroll_actions ppa,pay_assignment_actions paa
2444 where paa.assignment_action_id = cp_locked_actid
2445 and ppa.payroll_action_id = paa.payroll_action_id;
2446
2447 CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
2448 select locked_action_id
2449 from pay_action_interlocks
2450 where locking_action_id = cp_locking_act_id;
2451
2452
2453 /* cursor to get the T4 Employment Code, Bug#2141132 */
2454 cursor c_get_employment_code(cp_gre varchar2,
2455 cp_person_id number) IS
2456 select pei_information2,
2457 pei_information3
2458 from per_people_extra_info
2459 where person_id = cp_person_id
2460 and pei_information_category = 'ADDITIONAL_T4_INFORMATION'
2461 and pei_information1 = cp_gre;
2462
2463 /* Modified the cursor for bug fix 3447439 */
2464 CURSOR c_get_latest_asg(p_person_id number,
2465 p_jurisdiction varchar2) IS
2466 select /*+ Ordered */
2467 paa.assignment_action_id,
2468 ppa.date_earned
2469 from per_all_assignments_f paf,
2470 pay_assignment_actions paa,
2471 pay_payroll_actions ppa,
2472 pay_action_classifications pac,
2473 pay_action_contexts pac1,
2474 ff_contexts fc
2475 where paf.person_id = p_person_id
2476 and paa.assignment_id = paf.assignment_id
2477 and paa.tax_unit_id = l_tax_unit_id
2478 and paa.payroll_action_id = ppa.payroll_action_id
2479 and ppa.action_type = pac.action_type
2480 and pac.classification_name = 'SEQUENCED'
2481 and ppa.effective_date +0 between paf.effective_start_date
2482 and paf.effective_end_date
2483 and ppa.effective_date +0 between l_year_start
2484 and l_year_end
2485 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
2486 and paa.source_action_id is null)
2487 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
2488 and paa.source_action_id is not null )
2489 or (ppa.action_type = 'V' and ppa.run_type_id is null
2490 and paa.run_type_id is not null
2491 and paa.source_action_id is null))
2492 and pac1.assignment_action_id = paa.assignment_action_id
2493 and pac1.context_id = fc.context_id
2494 and fc.context_name = 'JURISDICTION_CODE'
2495 and pac1.context_value = p_jurisdiction
2496 order by paa.action_sequence desc;
2497
2498 /* Modified cursor c_get_latest_asg by ssmukher for Bug 3447439 */
2499 /*
2500 cursor c_get_latest_asg(cp_person_id number,
2501 cp_tax_unit_id number,
2502 cp_jurisdiction varchar2) is
2503 select paa.assignment_action_id,
2504 ppa.date_earned
2505 from pay_assignment_actions paa,
2506 per_all_assignments_f paf,
2507 per_all_people_f ppf,
2508 pay_payroll_actions ppa,
2509 pay_action_classifications pac,
2510 pay_action_contexts pac1,
2511 ff_contexts fc
2512 where ppf.person_id = cp_person_id
2513 and paf.person_id = ppf.person_id
2514 and paf.assignment_id = paa.assignment_id
2515 and paa.tax_unit_id = cp_tax_unit_id
2516 and ppa.payroll_action_id = paa.payroll_action_id
2517 and ppa.effective_date+0 between l_year_start
2518 and l_year_end
2519 and ppa.effective_date between ppf.effective_start_date
2520 and ppf.effective_end_date
2521 and ppa.effective_date between paf.effective_start_date
2522 and paf.effective_end_date
2523 and ppa.action_type = pac.action_type
2524 and pac.classification_name = 'SEQUENCED'
2525 and pac1.assignment_action_id = paa.assignment_action_id
2526 and pac1.context_id = fc.context_id
2527 and fc.context_name = 'JURISDICTION_CODE'
2528 and pac1.context_value = cp_jurisdiction
2529 order by paa.action_sequence desc;
2530 */
2531
2532 /* cursor to get date_of_birth for an employee to check EI and CPP Exempt */
2533 cursor c_get_date_of_birth(ln_person_id number
2534 ,ld_eff_date date) is
2535 select ppf.date_of_birth
2536 from per_all_people_f ppf
2537 where ppf.person_id = ln_person_id
2538 and ppf.effective_end_date = (select max(ppf2.effective_end_date)
2539 from per_all_people_f ppf2
2540 where ppf2.person_id= ln_person_id
2541 and ppf2.effective_start_date
2542 <= ld_eff_date);
2543
2544 /* This cursor fetches the Status Indian flag for a assignment */
2545 CURSOR c_get_status_indian(cp_assign number,
2546 cp_effec_date date) IS
2547 select ca_tax_information1
2548 from pay_ca_emp_fed_tax_info_f pca
2549 where pca.assignment_id = cp_assign
2550 and cp_effec_date between pca.effective_start_date and
2551 pca.effective_end_date;
2552
2553 /* Bug#10244185, Cursor to get T4 Code87 Max Limit for validation */
2554 CURSOR c_get_t4code_limits(cp_lookup_code varchar2,
2555 cp_eff_date date) IS
2556 select information_value
2557 from pay_ca_legislation_info
2558 where lookup_type = 'T4ARCHIVE'
2559 and lookup_code = cp_lookup_code
2560 and cp_eff_date between start_date and end_date;
2561
2562 begin
2563
2564 -- hr_utility.trace_on(1,'ORACLE');
2565
2566 l_negative_balance_exists := 'N';
2567 box_52_exists := 'N';
2568 -- box_20_exists added for bug 7611439
2569 box_20_exists := 'N';
2570
2571 hr_utility.trace('box_52_exists 1 '||box_52_exists);
2572 hr_utility.trace('box_20_exists 1 '||box_20_exists);
2573 hr_utility.trace('p_effective_date '||to_char(p_effective_date));
2574
2575 lv_under18_flag := 'N';
2576 lv_over70_flag := 'N';
2577 ln_cpp_ee_taxable_pjgy := 0;
2578 ln_qpp_ee_taxable_pjgy := 0;
2579 ln_ei_ee_taxable_pjgy := 0;
2580 lv_cpp_archive_exempt_flag := Null;
2581 lv_ei_archive_exempt_flag := Null;
2582 lv_ppip_archive_exempt_flag := Null;
2583 lv_qpp_exempt_flag := Null;
2584 lv_fapp_flag := 'N';
2585 l_count := 0;
2586 hr_utility.trace('p_assactida value '||p_assactid);
2587 hr_utility.trace('getting assignment');
2588
2589
2590 SELECT
2591 aa.assignment_id,
2592 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2593 aa.tax_unit_id,
2594 aa.chunk_number,
2595 aa.payroll_action_id,
2596 aa.serial_number
2597 INTO
2598 l_asgid,
2599 l_date_earned,
2600 l_tax_unit_id,
2601 l_chunk,
2602 l_payroll_action_id,
2603 lv_serial_number
2604 FROM
2605 pay_assignment_actions aa
2606 WHERE
2607 aa.assignment_action_id = p_assactid;
2608
2609 l_year_start := trunc(p_effective_date, 'Y');
2610 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2611
2612 OPEN cur_bg(l_tax_unit_id);
2613 FETCH
2614 cur_bg
2615 INTO
2616 l_business_group_id;
2617 CLOSE cur_bg;
2618
2619 l_step := 1;
2620
2621 /*Bug 5706114 Fetching the Status Indian flag */
2622
2623 open c_get_status_indian(l_asgid,p_effective_date);
2624 fetch c_get_status_indian
2625 into l_status_indian;
2626 close c_get_status_indian;
2627
2628 /* Call the archive_gre_data procedure */
2629 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2630 select org_information11
2631 into l_transmitter_gre_id
2632 from hr_organization_information
2633 where organization_id = l_tax_unit_id
2634 and org_information_context = 'Canada Employer Identification';
2635
2636 l_step := 3;
2637 hr_utility.trace('eoy_archive_data archiving employer data');
2638 eoy_archive_gre_data(l_payroll_action_id,
2639 l_tax_unit_id,
2640 l_transmitter_gre_id);
2641
2642 l_step := 4;
2643 hr_utility.trace('eoy_archive_data archived employer data');
2644 end if;
2645
2646 /* Now, set up the jurisdiction context for the db items that
2647 need the jurisdiction as a context.Here we are archiving all the
2648 jurisdictions we got from pay_action_contexts for all assignment_actions.
2649 So even though a particular assignment_action is for aparticular jurisdiction
2650 the archiver table has data for all the jurisdictions, but values of
2651 irrelevant jurisdictions will be 0 */
2652
2653 /* Change it to c_get_province later on */
2654 OPEN c_get_test_province;
2655 LOOP
2656
2657 /* initializing local variables used for T4 Box 28 for each
2658 jurisdiction part of fix for bug#3422384 */
2659 lv_over70_flag := 'N';
2660 lv_under18_flag := 'N';
2661 l_cpp_exempt_flag := NULL;
2662 l_ei_exempt_flag := NULL;
2663 l_ppip_exempt_flag := NULL;
2664 l_cpp_ee_withheld_pjgy := 0;
2665 ln_cpp_ee_taxable_pjgy := 0;
2666 lv_cpp_archive_exempt_flag := NULL;
2667 lv_qpp_exempt_flag := NULL;
2668 l_qpp_ee_withheld_pjgy := 0;
2669 ln_qpp_ee_taxable_pjgy := 0;
2670 l_ei_exempt_flag := NULL;
2671 l_ei_ee_withheld_pjgy := 0;
2672 ln_ei_ee_taxable_pjgy := 0;
2673 l_ppip_ee_withheld_pjgy := 0;
2674 ln_ppip_ee_taxable_pjgy := 0;
2675 lv_ei_archive_exempt_flag := NULL;
2676 lv_ppip_archive_exempt_flag := Null;
2677 ld_date_of_birth := NULL;
2678
2679
2680 /* Initialise l_count */
2681 l_count := 0;
2682 l_step := 11;
2683
2684 FETCH c_get_test_province
2685 INTO l_jurisdiction;
2686
2687 hr_utility.trace('In jurisdiction loop ' || l_jurisdiction);
2688 EXIT WHEN c_get_test_province%NOTFOUND;
2689
2690 /*
2691 SELECT
2692 paa1.assignment_action_id
2693 INTO
2694 l_aaid
2695 FROM
2696 pay_assignment_actions paa1,
2697 per_all_assignments_f paf2
2698 WHERE
2699 paa1.assignment_id = paf2.assignment_id
2700 and paa1.tax_unit_id = l_tax_unit_id
2701 and (paa1.action_sequence , paf2.person_id) =
2702 (SELECT MAX(paa.action_sequence), paf.person_id
2703 FROM pay_action_classifications pac,
2704 pay_payroll_actions ppa,
2705 pay_assignment_actions paa,
2706 per_all_assignments_f paf1,
2707 per_all_assignments_f paf
2708 WHERE paf.assignment_id = l_asgid
2709 AND paf1.person_id = paf.person_id
2710 AND paa.tax_unit_id = l_tax_unit_id
2711 AND paa.assignment_id = paf1.assignment_id
2712 AND paa.payroll_action_id = ppa.payroll_action_id
2713 AND ppa.action_type = pac.action_type
2714 AND pac.classification_name = 'SEQUENCED'
2715 AND ppa.effective_date <= p_effective_date
2716 group by paf.person_id)
2717 and rownum < 2;
2718 */
2719 begin
2720
2721 open c_get_latest_asg(lv_serial_number,l_jurisdiction);
2722 fetch c_get_latest_asg into l_aaid,lv_actual_date;
2723 close c_get_latest_asg;
2724
2725 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2726
2727 exception
2728 when no_data_found then
2729 l_aaid := -9999;
2730 raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2731 ||to_char(l_person_id));
2732 end;
2733
2734 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2735 pay_ca_balance_view_pkg.set_context( 'JURISDICTION_CODE', l_jurisdiction);
2736
2737 hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2738
2739 /* Assign values to the PL/SQL tables */
2740
2741 l_step := 16;
2742
2743 l_seq_tab(1) := 1;
2744 l_context_id_tab(1) := l_jursd_context_id;
2745 l_context_val_tab(1) := l_jurisdiction;
2746
2747 l_seq_tab(2) := 2;
2748 l_context_id_tab(2) := l_taxunit_context_id;
2749 l_context_val_tab(2) := l_tax_unit_id;
2750
2751 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2752 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2753 pay_ca_balance_view_pkg.set_context( 'JURISDICTION_CODE', l_jurisdiction);
2754
2755 l_count := l_count + 1;
2756 l_user_entity_name_tab(l_count) := 'CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD';
2757 l_balance_type_tab(l_count) := 'Gross Earnings';
2758
2759 l_count := l_count + 1;
2760 l_user_entity_name_tab(l_count) := 'CAEOY_CPP_EE_WITHHELD_PER_JD_GRE_YTD';
2761 l_balance_type_tab(l_count) := 'CPP EE Withheld';
2762
2763 l_count := l_count + 1;
2764 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_WITHHELD_PER_JD_GRE_YTD';
2765 l_balance_type_tab(l_count) := 'QPP EE Withheld';
2766
2767 l_count := l_count + 1;
2768 l_user_entity_name_tab(l_count) := 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD';
2769 l_balance_type_tab(l_count) := 'EI EE Withheld';
2770
2771 /* Added following user entity for the Bug 11655053 */
2772 l_count := l_count + 1;
2773 l_user_entity_name_tab(l_count) := 'CAEOY_EI_ER_LIABILITY_PER_JD_GRE_YTD';
2774 l_balance_type_tab(l_count) := 'EI ER Liability';
2775
2776 l_count := l_count + 1;
2777 l_user_entity_name_tab(l_count) := 'CAEOY_FED_WITHHELD_PER_JD_GRE_YTD';
2778 l_balance_type_tab(l_count) := 'FED Withheld';
2779
2780 l_count := l_count + 1;
2781 l_user_entity_name_tab(l_count) := 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD';
2782 l_balance_type_tab(l_count) := 'EI EE Taxable';
2783
2784 IF l_jurisdiction ='QC' THEN
2785
2786 l_count := l_count + 1;
2787 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_WITHHELD_PER_JD_GRE_YTD';
2788 l_balance_type_tab(l_count) := 'PPIP EE Withheld';
2789
2790
2791 l_count := l_count + 1;
2792 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_TAXABLE_PER_JD_GRE_YTD';
2793 l_balance_type_tab(l_count) := 'PPIP EE Taxable';
2794
2795 END IF;
2796
2797 l_count := l_count + 1;
2798 l_user_entity_name_tab(l_count) := 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD';
2799 l_balance_type_tab(l_count) := 'CPP EE Taxable';
2800
2801 l_count := l_count + 1;
2802 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD';
2803 l_balance_type_tab(l_count) := 'QPP EE Taxable';
2804
2805 l_count := l_count + 1;
2806 l_user_entity_name_tab(l_count) := 'CAEOY_CPP_EE_RSUBJECT_PER_JD_GRE_YTD';
2807 l_balance_type_tab(l_count) := 'CPP Reduced Subject';
2808
2809 l_count := l_count + 1;
2810 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_RSUBJECT_PER_JD_GRE_YTD';
2811 l_balance_type_tab(l_count) := 'QPP Reduced Subject';
2812
2813 l_count := l_count + 1;
2814 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX20_PER_JD_GRE_YTD';
2815 l_balance_type_tab(l_count) := 'T4_BOX20';
2816
2817 l_count := l_count + 1;
2818 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX44_PER_JD_GRE_YTD';
2819 l_balance_type_tab(l_count) := 'T4_BOX44';
2820
2821 l_count := l_count + 1;
2822 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX46_PER_JD_GRE_YTD';
2823 l_balance_type_tab(l_count) := 'T4_BOX46';
2824
2825 /* l_count := l_count + 1
2826 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX50_PER_JD_GRE_YTD';
2827 l_balance_type_tab(l_count) := 'T4_BOX50'; */
2828
2829 l_count := l_count + 1;
2830 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX52_PER_JD_GRE_YTD';
2831 l_balance_type_tab(l_count) := 'T4_BOX52';
2832
2833 l_count := l_count + 1;
2834 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT30_PER_JD_GRE_YTD';
2835 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT30';
2836
2837 l_count := l_count + 1;
2838 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT32_PER_JD_GRE_YTD';
2839 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT32';
2840
2841 l_count := l_count + 1;
2842 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT33_PER_JD_GRE_YTD';
2843 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT33';
2844
2845 l_count := l_count + 1;
2846 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT34_PER_JD_GRE_YTD';
2847 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT34';
2848
2849 l_count := l_count + 1;
2850 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT35_PER_JD_GRE_YTD';
2851 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT35';
2852
2853 l_count := l_count + 1;
2854 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT36_PER_JD_GRE_YTD';
2855 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT36';
2856
2857 l_count := l_count + 1;
2858 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT37_PER_JD_GRE_YTD';
2859 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT37';
2860
2861 l_count := l_count + 1;
2862 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT38_PER_JD_GRE_YTD';
2863 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT38';
2864
2865 l_count := l_count + 1;
2866 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT39_PER_JD_GRE_YTD';
2867 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT39';
2868
2869 l_count := l_count + 1;
2870 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT40_PER_JD_GRE_YTD';
2871 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT40';
2872
2873 l_count := l_count + 1;
2874 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT41_PER_JD_GRE_YTD';
2875 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT41';
2876
2877 l_count := l_count + 1;
2878 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT42_PER_JD_GRE_YTD';
2879 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT42';
2880
2881 l_count := l_count + 1;
2882 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT43_PER_JD_GRE_YTD';
2883 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT43';
2884
2885 /* Modification for bug 9135405 starts here. */
2886
2887 hr_utility.trace('l_year_end is '|| to_char(l_year_end,'yyyy'));
2888
2889 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2010) then
2890 hr_utility.trace('inside if condition l_year_end '|| to_char(l_year_end,'yyyy'));
2891
2892 l_count := l_count + 1;
2893 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT66_PER_JD_GRE_YTD';
2894 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT66';
2895
2896 l_count := l_count + 1;
2897 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT67_PER_JD_GRE_YTD';
2898 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT67';
2899
2900 l_count := l_count + 1;
2901 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT68_PER_JD_GRE_YTD';
2902 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT68';
2903
2904 l_count := l_count + 1;
2905 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT69_PER_JD_GRE_YTD';
2906 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT69';
2907
2908 END IF;
2909
2910 /* Modification for bug 9135405 ends here. */
2911
2912 l_count := l_count + 1;
2913 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT70_PER_JD_GRE_YTD';
2914 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT70';
2915
2916 l_count := l_count + 1;
2917 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT71_PER_JD_GRE_YTD';
2918 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT71';
2919
2920 l_count := l_count + 1;
2921 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT72_PER_JD_GRE_YTD';
2922 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT72';
2923
2924 l_count := l_count + 1;
2925 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT73_PER_JD_GRE_YTD';
2926 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT73';
2927
2928 l_count := l_count + 1;
2929 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT74_PER_JD_GRE_YTD';
2930 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT74';
2931
2932 l_count := l_count + 1;
2933 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT75_PER_JD_GRE_YTD';
2934 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT75';
2935
2936 l_count := l_count + 1;
2937 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT76_PER_JD_GRE_YTD';
2938 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT76';
2939
2940 l_count := l_count + 1;
2941 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT77_PER_JD_GRE_YTD';
2942 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT77';
2943
2944 l_count := l_count + 1;
2945 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT79_PER_JD_GRE_YTD';
2946 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT79';
2947
2948 l_count := l_count + 1;
2949 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT80_PER_JD_GRE_YTD';
2950 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT80';
2951
2952 -- change started for Bug 5698016
2953
2954 l_count := l_count + 1;
2955 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT81_PER_JD_GRE_YTD';
2956 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT81';
2957
2958 l_count := l_count + 1;
2959 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT82_PER_JD_GRE_YTD';
2960 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT82';
2961
2962 l_count := l_count + 1;
2963 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT83_PER_JD_GRE_YTD';
2964 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT83';
2965
2966 l_count := l_count + 1;
2967 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT84_PER_JD_GRE_YTD';
2968 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT84';
2969
2970 l_count := l_count + 1;
2971 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT85_PER_JD_GRE_YTD';
2972 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT85';
2973
2974 --- change ended for Bug 5698016
2975
2976 /* Modification for bug 10097860 starts here. */
2977
2978 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2010) then
2979 l_count := l_count + 1;
2980 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT86_PER_JD_GRE_YTD';
2981 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT86';
2982
2983 END IF;
2984 /* Modification for bug 10097860 ends here. */
2985
2986 /* Modification for bug 10244185 starts here. */
2987 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2011) then
2988 l_count := l_count + 1;
2989 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT87_PER_JD_GRE_YTD';
2990 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT87';
2991
2992 END IF;
2993 /* Modification for bug 10244185 ends here. */
2994
2995 l_count := l_count + 1;
2996 l_user_entity_name_tab(l_count)
2997 := 'CAEOY_CPP_BASIC_EXEMPTION_PER_JD_GRE_YTD';
2998 l_balance_type_tab(l_count) := 'CPP EE Basic Exemption';
2999
3000 l_count := l_count + 1;
3001 l_user_entity_name_tab(l_count)
3002 := 'CAEOY_CPP_EXEMPT_PER_JD_GRE_YTD';
3003 l_balance_type_tab(l_count) := 'CPP Exempt';
3004
3005
3006 ln_non_taxable_earnings :=
3007 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3008 ('T4 Non Taxable Earnings',
3009 'YTD',l_aaid,l_asgid,NULL,'PER',
3010 l_tax_unit_id,l_business_group_id,l_jurisdiction),0);
3011
3012 ln_gross_earnings :=
3013 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3014 ('Gross Earnings',
3015 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
3016 l_tax_unit_id, l_business_group_id, l_jurisdiction),0);
3017
3018 ln_no_gross_earnings :=
3019 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3020 ('T4 No Gross Earnings',
3021 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
3022 l_tax_unit_id, l_business_group_id, l_jurisdiction),0);
3023
3024 /* Modification for bug 13087530 starts here. */
3025
3026 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2012) then
3027 ln_taxben_without_remuneration :=
3028 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3029 ('Taxable Benefit without Remuneration',
3030 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
3031 l_tax_unit_id, l_business_group_id, l_jurisdiction),0);
3032
3033 hr_utility.trace('13087530 ln_taxben_without_remuneration '||ln_taxben_without_remuneration);
3034 END IF;
3035
3036 /* Modification for bug 13087530 ends here. */
3037
3038 if (((ln_gross_earnings <> 0) and
3039 (ln_non_taxable_earnings <> ln_gross_earnings)) or
3040 (ln_no_gross_earnings <> 0)) then
3041
3042 hr_utility.trace('Jurisdiction is ** ' || l_jurisdiction);
3043 earning_exists := 1;
3044 ff_archive_api.create_archive_item(
3045 p_archive_item_id => l_archive_item_id
3046 ,p_user_entity_id => get_user_entity_id('CAEOY_PROVINCE_OF_EMPLOYMENT')
3047 ,p_archive_value => l_jurisdiction
3048 ,p_archive_type => 'AAP'
3049 ,p_action_id => p_assactid
3050 ,p_legislation_code => 'CA'
3051 ,p_object_version_number => l_object_version_number
3052 ,p_context_name1 => 'JURISDICTION_CODE'
3053 ,p_context1 => l_jurisdiction
3054 ,p_context_name2 => 'TAX_UNIT_ID'
3055 ,p_context2 => l_tax_unit_id
3056 ,p_some_warning => l_some_warning
3057 );
3058
3059 for i in 1 .. l_count
3060 loop
3061
3062 result := pay_ca_balance_pkg.call_ca_balance_get_value
3063 ( l_balance_type_tab(i),
3064 'YTD' ,
3065 l_aaid,
3066 l_asgid ,
3067 NULL,
3068 'PER' ,
3069 l_tax_unit_id,
3070 l_business_group_id,
3071 l_jurisdiction
3072 ) ;
3073
3074 if result is null then
3075 result := 0;
3076 end if;
3077
3078 /* if condition added for bug 10244185*/
3079 if l_user_entity_name_tab(i) = 'CAEOY_T4_OTHER_INFO_AMOUNT87_PER_JD_GRE_YTD' then
3080 open c_get_t4code_limits('CODE87_MAXLIMIT',p_effective_date);
3081 fetch c_get_t4code_limits into lv_code87_Maxlimit;
3082 close c_get_t4code_limits;
3083 if result > lv_code87_Maxlimit then
3084 result := lv_code87_Maxlimit;
3085 end if;
3086 end if;
3087 /* changes for bug 10244185 ends */
3088
3089 if l_user_entity_name_tab(i) = 'CAEOY_CPP_EE_WITHHELD_PER_JD_GRE_YTD' then
3090 l_cpp_ee_withheld_pjgy := result;
3091 elsif
3092 l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_WITHHELD_PER_JD_GRE_YTD' then
3093 l_qpp_ee_withheld_pjgy := result;
3094 elsif
3095 l_user_entity_name_tab(i) = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
3096 l_ei_ee_withheld_pjgy := result;
3097 elsif
3098 l_user_entity_name_tab(i) = 'CAEOY_T4_BOX52_PER_JD_GRE_YTD' then
3099 result := round(result);
3100 hr_utility.trace('box_52 Result = ' || to_char(result));
3101 if result > 0 then
3102 box_52_exists := 'Y';
3103 hr_utility.trace('box_52_exists');
3104 hr_utility.trace('box_52_exists 2 '||box_52_exists);
3105 end if;
3106 -- Modification for bug 7611439 starts here.
3107 elsif
3108 l_user_entity_name_tab(i) = 'CAEOY_T4_BOX20_PER_JD_GRE_YTD' then
3109 hr_utility.trace('box_20 Result = ' || to_char(result));
3110 if result > 0 then
3111 box_20_exists := 'Y';
3112 hr_utility.trace('box_20_exists');
3113 hr_utility.trace('box_20_exists 2 '||box_20_exists);
3114
3115 end if;
3116 -- Modification for bug 7611439 ends here.
3117 /* bug#3422384 Box26, Box24 */
3118 elsif l_user_entity_name_tab(i) = 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD' then
3119 ln_cpp_ee_taxable_pjgy := result;
3120 elsif l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD' then
3121 ln_qpp_ee_taxable_pjgy := result;
3122 elsif l_user_entity_name_tab(i) = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
3123 ln_ei_ee_taxable_pjgy := result;
3124 end if;
3125
3126 /* Added by ssmukher for PPIP Implementaton */
3127 if l_jurisdiction = 'QC' then
3128 if l_user_entity_name_tab(i) = 'CAEOY_PPIP_EE_TAXABLE_PER_JD_GRE_YTD' then
3129 ln_ppip_ee_taxable_pjgy := result;
3130 elsif
3131 l_user_entity_name_tab(i) = 'CAEOY_PPIP_EE_WITHHELD_PER_JD_GRE_YTD' then
3132 l_ppip_ee_withheld_pjgy := result;
3133 end if;
3134 end if;
3135
3136 if l_jurisdiction = 'QC' and
3137 l_balance_type_tab(i) = 'Gross Earnings' then
3138
3139 hr_utility.trace('Calculating QC only taxable benefit');
3140 hr_utility.trace('l_aaid ' || to_char(l_aaid));
3141 hr_utility.trace('l_asgid ' || to_char(l_asgid));
3142 hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
3143 hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
3144 hr_utility.trace('l_jurisdiction ' || l_jurisdiction);
3145
3146 qc_result := pay_ca_balance_pkg.call_ca_balance_get_value
3147 ( 'Taxable Benefits for Quebec',
3148 'YTD' ,
3149 l_aaid,
3150 l_asgid ,
3151 NULL,
3152 'PER' ,
3153 l_tax_unit_id,
3154 l_business_group_id,
3155 l_jurisdiction
3156 ) ;
3157
3158 hr_utility.trace('qc_result is' || to_char(qc_result));
3159
3160 if qc_result is null then
3161 qc_result := 0;
3162 end if;
3163
3164 result := result - qc_result;
3165
3166 end if;
3167
3168 if l_balance_type_tab(i) = 'Gross Earnings' then
3169
3170 other_info_amount31 := pay_ca_balance_pkg.call_ca_balance_get_value
3171 ( 'T4_OTHER_INFO_AMOUNT31',
3172 'YTD' ,
3173 l_aaid,
3174 l_asgid ,
3175 NULL,
3176 'PER' ,
3177 l_tax_unit_id,
3178 l_business_group_id,
3179 l_jurisdiction
3180 ) ;
3181
3182 /* Modification for bug 10244185 starts here. */
3183 IF ( to_number(to_char(l_year_end,'YYYY')) < 2011) then
3184 other_info_amount53 := pay_ca_balance_pkg.call_ca_balance_get_value
3185 ( 'T4_OTHER_INFO_AMOUNT53',
3186 'YTD' ,
3187 l_aaid,
3188 l_asgid ,
3189 NULL,
3190 'PER' ,
3191 l_tax_unit_id,
3192 l_business_group_id,
3193 l_jurisdiction
3194 ) ;
3195 ELSE other_info_amount53 := 0;
3196 END IF;
3197 /* Modification for bug 10244185 ends here. */
3198
3199 /* Added by ssmukher for Bug 4547415 */
3200
3201 other_info_amount78 := pay_ca_balance_pkg.call_ca_balance_get_value
3202 ( 'T4_OTHER_INFO_AMOUNT78',
3203 'YTD' ,
3204 l_aaid,
3205 l_asgid ,
3206 NULL,
3207 'PER' ,
3208 l_tax_unit_id,
3209 l_business_group_id,
3210 l_jurisdiction
3211 ) ;
3212
3213
3214 other_info_amount71 := pay_ca_balance_pkg.call_ca_balance_get_value
3215 ( 'T4_OTHER_INFO_AMOUNT71',
3216 'YTD' ,
3217 l_aaid,
3218 l_asgid ,
3219 NULL,
3220 'PER' ,
3221 l_tax_unit_id,
3222 l_business_group_id,
3223 l_jurisdiction
3224 ) ;
3225
3226 -- code started for bug 5698016
3227
3228
3229 other_info_amount81 := pay_ca_balance_pkg.call_ca_balance_get_value
3230 ( 'T4_OTHER_INFO_AMOUNT81',
3231 'YTD' ,
3232 l_aaid,
3233 l_asgid ,
3234 NULL,
3235 'PER' ,
3236 l_tax_unit_id,
3237 l_business_group_id,
3238 l_jurisdiction
3239 ) ;
3240
3241 other_info_amount82 := pay_ca_balance_pkg.call_ca_balance_get_value
3242 ( 'T4_OTHER_INFO_AMOUNT82',
3243 'YTD' ,
3244 l_aaid,
3245 l_asgid ,
3246 NULL,
3247 'PER' ,
3248 l_tax_unit_id,
3249 l_business_group_id,
3250 l_jurisdiction
3251 ) ;
3252
3253 other_info_amount83 := pay_ca_balance_pkg.call_ca_balance_get_value
3254 ( 'T4_OTHER_INFO_AMOUNT83',
3255 'YTD' ,
3256 l_aaid,
3257 l_asgid ,
3258 NULL,
3259 'PER' ,
3260 l_tax_unit_id,
3261 l_business_group_id,
3262 l_jurisdiction
3263 ) ;
3264
3265 /*
3266 other_info_amount84 := pay_ca_balance_pkg.call_ca_balance_get_value
3267 ( 'T4_OTHER_INFO_AMOUNT84',
3268 'YTD' ,
3269 l_aaid,
3270 l_asgid ,
3271 NULL,
3272 'PER' ,
3273 l_tax_unit_id,
3274 l_business_group_id,
3275 l_jurisdiction
3276 ) ;
3277
3278 other_info_amount85 := pay_ca_balance_pkg.call_ca_balance_get_value
3279 ( 'T4_OTHER_INFO_AMOUNT85',
3280 'YTD' ,
3281 l_aaid,
3282 l_asgid ,
3283 NULL,
3284 'PER' ,
3285 l_tax_unit_id,
3286 l_business_group_id,
3287 l_jurisdiction
3288 ) ;
3289 */
3290 -- code ended for bug 5698016
3291
3292 -- code ended for bug 9135405
3293 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2010) then
3294 other_info_amount66 := pay_ca_balance_pkg.call_ca_balance_get_value
3295 ( 'T4_OTHER_INFO_AMOUNT66',
3296 'YTD' ,
3297 l_aaid,
3298 l_asgid ,
3299 NULL,
3300 'PER' ,
3301 l_tax_unit_id,
3302 l_business_group_id,
3303 l_jurisdiction
3304 ) ;
3305
3306 other_info_amount67 := pay_ca_balance_pkg.call_ca_balance_get_value
3307 ( 'T4_OTHER_INFO_AMOUNT67',
3308 'YTD' ,
3309 l_aaid,
3310 l_asgid ,
3311 NULL,
3312 'PER' ,
3313 l_tax_unit_id,
3314 l_business_group_id,
3315 l_jurisdiction
3316 ) ;
3317
3318 other_info_amount68 := pay_ca_balance_pkg.call_ca_balance_get_value
3319 ( 'T4_OTHER_INFO_AMOUNT68',
3320 'YTD' ,
3321 l_aaid,
3322 l_asgid ,
3323 NULL,
3324 'PER' ,
3325 l_tax_unit_id,
3326 l_business_group_id,
3327 l_jurisdiction
3328 ) ;
3329
3330 other_info_amount69 := pay_ca_balance_pkg.call_ca_balance_get_value
3331 ( 'T4_OTHER_INFO_AMOUNT69',
3332 'YTD' ,
3333 l_aaid,
3334 l_asgid ,
3335 NULL,
3336 'PER' ,
3337 l_tax_unit_id,
3338 l_business_group_id,
3339 l_jurisdiction
3340 ) ;
3341 end if;
3342 -- code ended for bug 9135405
3343
3344 -- code starts for bug 10244185
3345 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2011) then
3346 other_info_amount87 := pay_ca_balance_pkg.call_ca_balance_get_value
3347 ( 'T4_OTHER_INFO_AMOUNT87',
3348 'YTD' ,
3349 l_aaid,
3350 l_asgid ,
3351 NULL,
3352 'PER' ,
3353 l_tax_unit_id,
3354 l_business_group_id,
3355 l_jurisdiction
3356 ) ;
3357 if other_info_amount87 is not null then
3358 open c_get_t4code_limits('CODE87_MAXLIMIT',p_effective_date);
3359 fetch c_get_t4code_limits into lv_code87_Maxlimit;
3360 close c_get_t4code_limits;
3361 if other_info_amount87 > lv_code87_Maxlimit then
3362 lv_code87_excess_amt := other_info_amount87 - lv_code87_Maxlimit;
3363 else
3364 lv_code87_excess_amt := 0;
3365 end if;
3366 end if;
3367 END IF;
3368 -- code ends here for bug 10244185
3369
3370 result := result - (
3371 NVL(other_info_amount31,0)
3372 -- + NVL(other_info_amount53,0) --Fix for Bug8576897
3373 + NVL(other_info_amount78,0)
3374 + NVL(other_info_amount71,0)
3375
3376 -- code started for bug 5698016
3377 + NVL(other_info_amount81,0)
3378 + NVL(other_info_amount82,0)
3379 + NVL(other_info_amount83,0)
3380 /*
3381 + NVL(other_info_amount84,0)
3382 + NVL(other_info_amount85,0)
3383 */
3384 -- code ended for bug 5698016
3385
3386 -- code started for bug 9135405
3387 + NVL(other_info_amount66,0)
3388 + NVL(other_info_amount67,0)
3389 + NVL(other_info_amount68,0)
3390 + NVL(other_info_amount69,0)
3391 -- code ended for bug 9135405
3392 + ln_non_taxable_earnings
3393 -- code started for bug 10244185
3394 + NVL(other_info_amount87,0))
3395 + lv_code87_excess_amt;
3396 -- code ends here for bug 10244185
3397
3398 hr_utility.trace(' Gross Earnings = ' || to_char(result));
3399
3400 /* Added for Bug 4028693 */
3401 open c_get_employment_code(to_char(l_tax_unit_id),
3402 to_number(lv_serial_number));
3403
3404 loop
3405 fetch c_get_employment_code
3406 into lv_empcode_prov,
3407 lv_employment_code;
3408 exit when c_get_employment_code%NOTFOUND;
3409
3410 if lv_employment_code is not null and
3411 lv_employment_code in ('11','12','13','17') then
3412 --bug 13505953
3413 if lv_empcode_prov is not null then
3414 if lv_empcode_prov = l_jurisdiction then
3415 l_box14_flag := 'Y';
3416 Exit;
3417 else l_box14_flag := 'N';
3418 end if;
3419 else
3420 l_box14_flag := 'Y';
3421 Exit;
3422 end if;
3423 -- end bug 13505953
3424 else
3425 l_box14_flag := 'N';
3426 end if;
3427
3428 end loop;
3429
3430 close c_get_employment_code;
3431
3432 lv_empcode_prov := null;
3433 lv_employment_code := null;
3434
3435 if l_box14_flag = 'Y' then
3436 result := 0;
3437 end if;
3438 /* end of changes for bug 4028693 */
3439
3440 end if;
3441
3442 /* Modification for bug 13087530 starts here. */
3443
3444 if ( to_number(to_char(l_year_end,'YYYY')) >= 2012) then
3445
3446 if l_user_entity_name_tab(i) = 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD'
3447 and l_jurisdiction <> 'QC'
3448 then
3449 hr_utility.trace('13087530 inside Non QC IF condition ');
3450
3451 if ln_taxben_without_remuneration > 0 then
3452
3453 hr_utility.trace('13087530 result before '||result);
3454 result := result + ln_taxben_without_remuneration;
3455 hr_utility.trace('13087530 result after '||result);
3456
3457 end if;
3458
3459 end if;
3460
3461 if l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD'
3462 and l_jurisdiction = 'QC'
3463 then
3464 hr_utility.trace('13087530 inside QC IF condition ');
3465
3466 if ln_taxben_without_remuneration > 0 then
3467
3468 hr_utility.trace('13087530 result before '||result);
3469 result := result + ln_taxben_without_remuneration;
3470 hr_utility.trace('13087530 result after '||result);
3471
3472 end if;
3473 end if;
3474
3475 end if; -- IF ( to_number(to_char(l_year_end,'YYYY')) >= 2012) then
3476
3477 /* Modification for bug 13087530 ends here. */
3478
3479 If (l_status_indian = 'Y' AND
3480 (l_balance_type_tab(i) IN
3481 ('T4_BOX20','T4_BOX44'))) then
3482 result := 0;
3483
3484 -- box_20_exists added for bug 7611439
3485 box_20_exists := 'N';
3486 hr_utility.trace('box_20_exists 3 '||box_20_exists);
3487 end if;
3488
3489 ff_archive_api.create_archive_item(
3490 p_archive_item_id => l_archive_item_id
3491 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
3492 ,p_archive_value => result
3493 ,p_archive_type => 'AAP'
3494 ,p_action_id => p_assactid
3495 ,p_legislation_code => 'CA'
3496 ,p_object_version_number => l_object_version_number
3497 ,p_context_name1 => 'JURISDICTION_CODE'
3498 ,p_context1 => l_jurisdiction
3499 ,p_context_name2 => 'TAX_UNIT_ID'
3500 ,p_context2 => l_tax_unit_id
3501 ,p_some_warning => l_some_warning
3502 );
3503
3504 /* Negative balance flag */
3505 /* Modified by ssmukher for Bug 4547415 */
3506 if result < 0 or other_info_amount31 < 0 or other_info_amount53 < 0
3507 or other_info_amount78 < 0 then
3508
3509 l_negative_balance_exists := 'Y';
3510
3511 end if;
3512
3513 end loop;
3514
3515 hr_utility.trace(' Archiver Asg Act Id = ' || to_char(p_assactid));
3516 hr_utility.trace(' Negative Balance Exists Flag = ' || l_negative_balance_exists);
3517
3518 /* Archiving the Negative Balance Exists Flag Bug#3289072 */
3519 if l_negative_balance_exists = 'Y' then
3520 l_user_entity_id :=
3521 get_user_entity_id('CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
3522
3523 ff_archive_api.create_archive_item(
3524 p_archive_item_id => l_archive_item_id
3525 ,p_user_entity_id => l_user_entity_id
3526 ,p_archive_value => l_negative_balance_exists
3527 ,p_archive_type => 'AAP'
3528 ,p_action_id => p_assactid
3529 ,p_legislation_code => 'CA'
3530 ,p_object_version_number => l_object_version_number
3531 ,p_context_name1 => 'JURISDICTION_CODE'
3532 ,p_context1 => l_jurisdiction
3533 ,p_context_name2 => 'TAX_UNIT_ID'
3534 ,p_context2 => l_tax_unit_id
3535 ,p_some_warning => l_some_warning);
3536
3537 else
3538 l_user_entity_id :=
3539 get_user_entity_id('CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
3540
3541 ff_archive_api.create_archive_item(
3542 p_archive_item_id => l_archive_item_id
3543 ,p_user_entity_id => l_user_entity_id
3544 ,p_archive_value => 'N'
3545 ,p_archive_type => 'AAP'
3546 ,p_action_id => p_assactid
3547 ,p_legislation_code => 'CA'
3548 ,p_object_version_number => l_object_version_number
3549 ,p_context_name1 => 'JURISDICTION_CODE'
3550 ,p_context1 => l_jurisdiction
3551 ,p_context_name2 => 'TAX_UNIT_ID'
3552 ,p_context2 => l_tax_unit_id
3553 ,p_some_warning => l_some_warning);
3554
3555 end if;
3556
3557 /* The following other_info_amounts are archived outside
3558 the main loop, otherwise the balance call would have been
3559 twice for each of them as they needed to be subtracted
3560 from the Gross Earnings */
3561 /* Modified by ssmukher for bug 4034155 */
3562 for i in 1..3 loop
3563
3564 if i = 1 then
3565 l_user_entity_id :=
3566 get_user_entity_id('CAEOY_T4_OTHER_INFO_AMOUNT31_PER_JD_GRE_YTD');
3567 result := other_info_amount31;
3568 elsif i = 2 then
3569 l_user_entity_id :=
3570 get_user_entity_id('CAEOY_T4_OTHER_INFO_AMOUNT53_PER_JD_GRE_YTD');
3571 result := other_info_amount53 ;
3572 /* Added by ssmukher for bug 4547415 */
3573 elsif i = 3 then
3574 l_user_entity_id :=
3575 get_user_entity_id('CAEOY_T4_OTHER_INFO_AMOUNT78_PER_JD_GRE_YTD');
3576 result := other_info_amount78 ;
3577 end if;
3578
3579 ff_archive_api.create_archive_item(
3580 p_archive_item_id => l_archive_item_id
3581 ,p_user_entity_id => l_user_entity_id
3582 ,p_archive_value => result
3583 ,p_archive_type => 'AAP'
3584 ,p_action_id => p_assactid
3585 ,p_legislation_code => 'CA'
3586 ,p_object_version_number => l_object_version_number
3587 ,p_context_name1 => 'JURISDICTION_CODE'
3588 ,p_context1 => l_jurisdiction
3589 ,p_context_name2 => 'TAX_UNIT_ID'
3590 ,p_context2 => l_tax_unit_id
3591 ,p_some_warning => l_some_warning);
3592
3593 end loop; -- End loop other information archived after Amount
3594 -- is subtracted from Gross Earnings.
3595
3596
3597 l_negative_balance_exists := 'N';
3598
3599 /* Archiving Exempt flags separately */
3600 hr_utility.trace('I am archiving CPP-QPP exempt flags for ' ||
3601 to_char(l_asgid));
3602 hr_utility.trace('effective date is ' || to_char(p_effective_date));
3603
3604 /* Bug#3422384, checking whether the employee age is under 18 or over 70 */
3605 open c_get_date_of_birth(to_number(lv_serial_number),p_effective_date);
3606 fetch c_get_date_of_birth into ld_date_of_birth;
3607 close c_get_date_of_birth;
3608
3609 if ld_date_of_birth is NULL then
3610 hr_utility.trace('Employee date of birth is NULL satisfied ');
3611 lv_over70_flag := 'N';
3612 lv_under18_flag := 'N';
3613 else
3614 hr_utility.trace('Employee date of birth found ');
3615
3616 /* Backing out the changes made in 115.60 */
3617 /* if (( add_months(trunc(ld_date_of_birth,'MONTH'),(18*12)+1)) <= lv_actual_date ) then
3618 if ( lv_actual_date >= ( add_months(trunc(ld_date_of_birth,'MONTH'),(70*12)+1))) then */
3619 if ((add_months(trunc(ld_date_of_birth,'MONTH'),(18*12)+1)) <= p_effective_date) then
3620 if (p_effective_date >= (add_months(trunc(ld_date_of_birth,'MONTH'),(70*12)+1))) then
3621 lv_over70_flag := 'Y';
3622 else
3623 lv_over70_flag := 'N';
3624 end if;
3625 else
3626 lv_under18_flag := 'Y';
3627
3628 end if;
3629 end if;
3630
3631 -- Bug 13501503
3632 select distinct(person_id) into l_each_person_id
3633 from per_all_assignments_f where assignment_id = l_asgid;
3634 hr_utility.trace('l_each_person_id ' || l_each_person_id);
3635
3636 open c_diff_assignments(l_each_person_id);
3637 fetch c_diff_assignments into l_each_asgid;
3638
3639 loop
3640 hr_utility.trace('start of c_diff_assignments CURSOR');
3641
3642 if c_diff_assignments%rowcount = 0 then
3643 hr_utility.trace('No rows fetched from c_diff_assignments');
3644 end if;
3645
3646 exit when c_diff_assignments%NOTFOUND;
3647 BEGIN
3648 hr_utility.trace('l_each_asgid ' || l_each_asgid);
3649 BEGIN
3650
3651 /* Removed the per_all_assignments_f join the select stmt for bug fix 5041252 */
3652 SELECT decode(target.CPP_QPP_EXEMPT_FLAG,'Y','X',NULL),
3653 decode(target.EI_EXEMPT_FLAG,'Y','X',NULL)
3654 INTO l_cpp_exempt_flag,
3655 l_ei_exempt_flag
3656 FROM pay_ca_emp_fed_tax_info_f target
3657 WHERE target.assignment_id = l_each_asgid
3658 and lv_actual_date/*p_effective_date*/ between target.effective_start_date
3659 and target.effective_end_date;
3660
3661 exception
3662 when no_data_found then
3663 l_cpp_exempt_flag := NULL;
3664 l_ei_exempt_flag := null;
3665 when others then
3666 l_cpp_exempt_flag := NULL;
3667 l_ei_exempt_flag := null;
3668 end;
3669
3670 /* Added extra validation to fix bug#3422384. For CPP
3671 1. If employee age is under 18 or over 70
3672 and Box16,Box26 = 0 then cpp_exempt_flag ='X'
3673 2. If cpp_exempt_flag in tax_information form is 'Y' and
3674 and Box16,Box26 = 0 then cpp_exempt_flag ='X'
3675 3. If employee age turned into 18 or over 70 mid year
3676 and Box16,Box26 > 0 and cpp_exempt_flag is 'Y' in
3677 tax form then cpp_exempt_flag = ?
3678 */
3679
3680 IF l_jurisdiction <> 'QC' THEN
3681
3682 IF (lv_under18_flag = 'Y' or lv_over70_flag = 'Y') and
3683 (l_cpp_ee_withheld_pjgy = 0) and (ln_cpp_ee_taxable_pjgy = 0) THEN
3684
3685 lv_cpp_archive_exempt_flag := 'X';
3686 Elsif (l_cpp_exempt_flag = 'X') and (l_cpp_ee_withheld_pjgy = 0)
3687 and (ln_cpp_ee_taxable_pjgy = 0) THEN
3688
3689 lv_cpp_archive_exempt_flag := 'X';
3690 END IF;
3691
3692 END IF;
3693
3694
3695 IF l_jurisdiction = 'QC' THEN
3696
3697 BEGIN
3698 SELECT decode(target.QPP_EXEMPT_FLAG,'Y','X',NULL),
3699 decode(target.PPIP_EXEMPT_FLAG,'Y','X',NULL)
3700 INTO lv_qpp_exempt_flag,
3701 l_ppip_exempt_flag
3702 FROM pay_ca_emp_prov_tax_info_f target
3703 WHERE target.assignment_id = l_each_asgid
3704 and target.province_code = 'QC'
3705 and lv_actual_date/*p_effective_date */ between target.effective_start_date
3706 and target.effective_end_date;
3707 EXCEPTION
3708 WHEN no_data_found THEN
3709 lv_qpp_exempt_flag := NULL;
3710 l_ppip_exempt_flag := NULL;
3711 END;
3712
3713 /* Added extra validation to fix bug#3422384. For QPP
3714 1. If employee is under 18
3715 and Box17,Box26 = 0 then qpp_exempt_flag= 'X'
3716 2. If qpp_exempt_flag in tax_information form is 'Y' and
3717 and Box17,Box26 = 0 then qpp_exempt_flag= 'X'
3718 3. If employee age turned into 18 mid year
3719 and Box17,Box26 > 0 and qpp_exempt_flag is 'Y' in
3720 tax form then qpp_exempt_flag = ?
3721 */
3722 IF (lv_under18_flag = 'Y') and (l_qpp_ee_withheld_pjgy = 0)
3723 and (ln_qpp_ee_taxable_pjgy = 0) THEN
3724 lv_cpp_archive_exempt_flag := 'X';
3725 Elsif (lv_qpp_exempt_flag = 'X') and (l_qpp_ee_withheld_pjgy = 0)
3726 and (ln_qpp_ee_taxable_pjgy = 0) THEN
3727 lv_cpp_archive_exempt_flag := 'X';
3728 END IF;
3729
3730 /* Added by ssmukher for PPIP Implementation */
3731 IF (l_ppip_exempt_flag = 'X' and l_ppip_ee_withheld_pjgy = 0
3732 and ln_ppip_ee_taxable_pjgy = 0) THEN
3733
3734 lv_ppip_archive_exempt_flag := 'X';
3735 END IF;
3736
3737 ff_archive_api.create_archive_item(
3738 p_archive_item_id => l_archive_item_id
3739 ,p_user_entity_id => get_user_entity_id('CAEOY_PPIP_EXEMPT')
3740 ,p_archive_value => lv_ppip_archive_exempt_flag
3741 ,p_archive_type => 'AAP'
3742 ,p_action_id => p_assactid
3743 ,p_legislation_code => 'CA'
3744 ,p_object_version_number => l_object_version_number
3745 ,p_context_name1 => 'JURISDICTION_CODE'
3746 ,p_context1 => l_jurisdiction
3747 ,p_context_name2 => 'TAX_UNIT_ID'
3748 ,p_context2 => l_tax_unit_id
3749 ,p_some_warning => l_some_warning
3750 );
3751
3752 END IF;
3753
3754
3755 /* changed to archive lv_cpp_archive_exempt_flag instead of l_cpp_exempt_flag
3756 to fix bug#3422384 */
3757 ff_archive_api.create_archive_item(
3758 p_archive_item_id => l_archive_item_id
3759 ,p_user_entity_id => get_user_entity_id('CAEOY_CPP_QPP_EXEMPT')
3760 ,p_archive_value => lv_cpp_archive_exempt_flag
3761 ,p_archive_type => 'AAP'
3762 ,p_action_id => p_assactid
3763 ,p_legislation_code => 'CA'
3764 ,p_object_version_number => l_object_version_number
3765 ,p_context_name1 => 'JURISDICTION_CODE'
3766 ,p_context1 => l_jurisdiction
3767 ,p_context_name2 => 'TAX_UNIT_ID'
3768 ,p_context2 => l_tax_unit_id
3769 ,p_some_warning => l_some_warning
3770 );
3771
3772 hr_utility.trace('I am archiving EI exempt flag');
3773
3774 /* Added extra validation to fix bug#3422384. For EI
3775 1. If ei_exempt_flag in tax_information form is 'Y' and
3776 and Box18,Box24 = 0 then ei_exempt_flag= 'X'
3777 */
3778
3779 IF (l_ei_exempt_flag = 'X' and l_ei_ee_withheld_pjgy = 0
3780 and ln_ei_ee_taxable_pjgy = 0) THEN
3781
3782 lv_ei_archive_exempt_flag := 'X';
3783
3784 END IF;
3785
3786 hr_utility.trace('assignment id ' || to_char(l_each_asgid) || '**');
3787 hr_utility.trace('cpp exempt flag is ' || lv_cpp_archive_exempt_flag || '**');
3788 hr_utility.trace('ei exempt flag is ' || lv_ei_archive_exempt_flag || '**');
3789
3790 ff_archive_api.create_archive_item(
3791 p_archive_item_id => l_archive_item_id
3792 ,p_user_entity_id => get_user_entity_id('CAEOY_EI_EXEMPT')
3793 ,p_archive_value => lv_ei_archive_exempt_flag
3794 ,p_archive_type => 'AAP'
3795 ,p_action_id => p_assactid
3796 ,p_legislation_code => 'CA'
3797 ,p_object_version_number => l_object_version_number
3798 ,p_context_name1 => 'JURISDICTION_CODE'
3799 ,p_context1 => l_jurisdiction
3800 ,p_context_name2 => 'TAX_UNIT_ID'
3801 ,p_context2 => l_tax_unit_id
3802 ,p_some_warning => l_some_warning
3803 );
3804
3805 exception
3806 when others then
3807 hr_utility.trace('Exception raised');
3808 null;
3809 end;
3810 hr_utility.trace('I have archived exempt flags for assignment id '||l_each_asgid);
3811 hr_utility.trace('Reached end');
3812 fetch c_diff_assignments into l_each_asgid;
3813 end loop;
3814 close c_diff_assignments;
3815
3816
3817 /* Archiving T4 Employment Code */
3818 open c_get_employment_code(to_char(l_tax_unit_id),
3819 to_number(lv_serial_number));
3820 loop -- c_get_emp_code
3821 fetch c_get_employment_code into lv_empcode_prov,
3822 lv_employment_code;
3823 exit when c_get_employment_code%NOTFOUND;
3824
3825 if lv_empcode_prov is not null and
3826 lv_empcode_prov = l_jurisdiction then
3827
3828 ff_archive_api.create_archive_item(
3829 p_archive_item_id => l_archive_item_id
3830 ,p_user_entity_id => get_user_entity_id('CAEOY_EMPLOYMENT_CODE')
3831 ,p_archive_value => lv_employment_code
3832 ,p_archive_type => 'AAP'
3833 ,p_action_id => p_assactid
3834 ,p_legislation_code => 'CA'
3835 ,p_object_version_number => l_object_version_number
3836 ,p_context_name1 => 'JURISDICTION_CODE'
3837 ,p_context1 => l_jurisdiction
3838 ,p_context_name2 => 'TAX_UNIT_ID'
3839 ,p_context2 => l_tax_unit_id
3840 ,p_some_warning => l_some_warning
3841 );
3842
3843 hr_utility.trace('Archived Employment code single prov');
3844
3845 /* to avoid archiving of Box29 value for other prov
3846 if employment code specified for particular
3847 province */
3848 elsif lv_empcode_prov is null then
3849
3850 ff_archive_api.create_archive_item(
3851 p_archive_item_id => l_archive_item_id
3852 ,p_user_entity_id => get_user_entity_id('CAEOY_EMPLOYMENT_CODE')
3853 ,p_archive_value => lv_employment_code
3854 ,p_archive_type => 'AAP'
3855 ,p_action_id => p_assactid
3856 ,p_legislation_code => 'CA'
3857 ,p_object_version_number => l_object_version_number
3858 ,p_context_name1 => 'JURISDICTION_CODE'
3859 ,p_context1 => l_jurisdiction
3860 ,p_context_name2 => 'TAX_UNIT_ID'
3861 ,p_context2 => l_tax_unit_id
3862 ,p_some_warning => l_some_warning
3863 );
3864
3865 hr_utility.trace('Archived employment code all prov');
3866
3867 end if;
3868
3869 end loop; -- c_get_employment_code
3870
3871 close c_get_employment_code;
3872 /* End of Employement Code archiving */
3873
3874 end if;
3875 -- Commented end loop; for bug 7611439, moved end loop after code for archiving t4 box 52.
3876 -- end loop;
3877
3878 /* start registration number archiving */
3879
3880 l_registration_no := NULL;
3881 old_l_registration_no := NULL;
3882 old_l_value := 0;
3883 l_value := 0;
3884
3885 --hr_utility.trace_on('Y','ORACLE');
3886
3887 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3888 hr_utility.trace('l_asgid is ' || to_char(l_asgid));
3889 hr_utility.trace('l_tax_unit_id is ' || to_char(l_tax_unit_id));
3890 hr_utility.trace('l_business group_id is ' || to_char(l_business_group_id));
3891
3892 begin
3893
3894
3895 if box_52_exists = 'Y' then
3896 l_balance_name1 := 'T4_BOX52';
3897 elsif box_20_exists = 'Y' then
3898 l_balance_name1 := 'T4_BOX20';
3899 end if;
3900
3901 -- l_balance_name1 := 'T4_BOX52'; -- commented by sneelapa for bug 7611439
3902
3903
3904 hr_utility.trace('BOX name is ' || l_balance_name1);
3905 hr_utility.trace('l_asgid ' || l_asgid);
3906 hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907 hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908 hr_utility.trace('box_52_exists ' || box_52_exists);
3909 hr_utility.trace('box_20_exists ' || box_20_exists);
3910 hr_utility.trace('l_jurisdiction '||l_jurisdiction);
3911
3912 -- adding this logic to resolving bug 13797428
3913 select distinct(person_id) into l_each_person_id
3914 from per_all_assignments_f where assignment_id = l_asgid;
3915 hr_utility.trace('l_each_person_id ' || l_each_person_id);
3916
3917 open c_diff_assignments(l_each_person_id);
3918 fetch c_diff_assignments into l_each_asgid;
3919
3920 loop
3921 hr_utility.trace('start of c_diff_assignments CURSOR');
3922
3923 if c_diff_assignments%rowcount = 0 then
3924 hr_utility.trace('No rows fetched from c_diff_assignments');
3925 end if;
3926
3927 exit when c_diff_assignments%NOTFOUND;
3928 begin
3929 hr_utility.trace('l_each_asgid ' || l_each_asgid);
3930
3931 open c_balance_feed_info(l_balance_name1,l_each_asgid);
3932 fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3933
3934 hr_utility.trace('CURSOR count ' || c_balance_feed_info%rowcount);
3935
3936 if c_balance_feed_info%rowcount = 0 and nvl(l_status_indian,'N') = 'N' then
3937 l_balance_name2 := 'T4_BOX20';
3938 if c_balance_feed_info%isopen then
3939 close c_balance_feed_info;
3940 end if;
3941 end if;
3942
3943 hr_utility.trace('BOX name is ' || l_balance_name2);
3944 if not c_balance_feed_info%isopen then
3945 open c_balance_feed_info(l_balance_name2,l_each_asgid);
3946 fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3947 hr_utility.trace('CURSOR count BOX20 is ' || c_balance_feed_info%rowcount);
3948 end if;
3949
3950 loop
3951 hr_utility.trace('start of c_balance_feed_info CURSOR');
3952 exit when c_balance_feed_info%NOTFOUND;
3953
3954 -- exception handling added by sneelapa for bug 6399498
3955 -- screen_entry_value will be NON NUMERIC data for certain Element Entry Values
3956 -- For example: Jurisdistiction.
3957
3958 begin
3959
3960 -- if condition added by sneelapa for bug 6399498
3961 if fnd_number.canonical_to_number(l_screen_entry_value) >= 0 then
3962
3963 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
3964 ( l_balance_name,
3965 'YTD' ,
3966 l_aaid,
3967 l_asgid,
3968 NULL,
3969 'PER' ,
3970 l_tax_unit_id,
3971 l_business_group_id,
3972 NULL );
3973
3974 if l_value is null then
3975 l_value := 0;
3976 end if;
3977 hr_utility.trace('l_value is ' || to_char(l_value));
3978 hr_utility.trace('old_l_value is ' || old_l_value);
3979 hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
3980 hr_utility.trace('l_registration_no is ' || l_registration_no);
3981
3982 if l_registration_no <> 'NOT FOUND' then
3983 if old_l_registration_no is null and l_value <> 0 then
3984 old_l_registration_no := l_registration_no;
3985 end if;
3986
3987 -- modified for bug 6399498
3988 if old_l_value >= l_value then -- revert back for bug 11886376
3989 -- if old_l_value > l_value then
3990 hr_utility.trace('if old_l_value >= l_value');
3991 if old_l_registration_no is not null then
3992 l_registration_no := old_l_registration_no;
3993 else old_l_registration_no := l_registration_no;
3994 end if;
3995 -- modified for bug 6399498
3996 elsif old_l_value < l_value then -- revert back for bug 11886376
3997 -- elsif old_l_value <= l_value then
3998 hr_utility.trace('elsif old_l_value < l_value');
3999 old_l_value := l_value;
4000 old_l_registration_no := l_registration_no;
4001 end if;
4002 end if;
4003 end if; -- if fnd_number.canonical_to_number(pev.screen_entry_value) >= 0 then
4004
4005 exception
4006 when others then
4007 null;
4008 end;
4009
4010 fetch c_balance_feed_info into l_registration_no,l_balance_name, l_screen_entry_value;
4011 end loop;
4012
4013 close c_balance_feed_info;
4014 exception
4015 when others then
4016 hr_utility.trace('Exception raised');
4017 null;
4018 end;
4019 hr_utility.trace('Reached end');
4020 fetch c_diff_assignments into l_each_asgid;
4021 end loop;
4022 close c_diff_assignments;
4023
4024 hr_utility.trace('loop ended');
4025 hr_utility.trace('old_l_value is ' || to_char(old_l_value));
4026 hr_utility.trace('l_registration no is ' || l_registration_no);
4027 hr_utility.trace('l_value is ' || to_char(l_value));
4028 hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
4029
4030 -- modified for bug 6399498, for QA reported issue in this bug.
4031 -- if l_registration_no is not null and old_l_value <> 0 then
4032
4033
4034 -- ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') added for bug 7611439
4035
4036 hr_utility.trace('box_52_exists before if condition '||box_52_exists);
4037 hr_utility.trace('box_20_exists before if condition '||box_20_exists);
4038 hr_utility.trace('box_52_exists before Archiving old_l_registration_no '||box_52_exists);
4039
4040 if old_l_registration_no is not null and
4041 ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') then
4042
4043 hr_utility.trace('l_registration no is ' || 'archiving');
4044 -- hr_utility.trace_off;
4045
4046 ff_archive_api.create_archive_item(
4047 p_archive_item_id => l_archive_item_id
4048 ,p_user_entity_id =>
4049 get_user_entity_id('CAEOY_T4_EMPLOYEE_REGISTRATION_NO')
4050 ,p_archive_value => old_l_registration_no
4051 ,p_archive_type => 'AAP'
4052 ,p_action_id => p_assactid
4053 ,p_legislation_code => 'CA'
4054 ,p_object_version_number => l_object_version_number
4055 -- JURISDICTION_CODE, TAX_UNIT_ID contexts added for bug 7611439
4056 ,p_context_name1 => 'JURISDICTION_CODE'
4057 ,p_context1 => l_jurisdiction
4058 ,p_context_name2 => 'TAX_UNIT_ID'
4059 ,p_context2 => l_tax_unit_id
4060 ,p_some_warning => l_some_warning
4061 );
4062 end if;
4063 end;
4064
4065 /* end registration number archiving */
4066
4067 -- moved end loop after code for archiving t4 box 52.
4068 box_52_exists := 'N';
4069 box_20_exists := 'N';
4070
4071 end loop;
4072
4073 begin
4074 l_counter := 0;
4075 hr_utility.trace('selecting people');
4076
4077 open get_person_info(l_asgid);
4078
4079 fetch get_person_info
4080 into
4081 l_person_id,
4082 l_first_name,
4083 l_last_name,
4084 l_employee_number,
4085 l_national_identifier,
4086 l_middle_names,
4087 l_organization_id,
4088 l_location_id;
4089
4090 l_person_arch_step := 1;
4091 /* Validations for magtape and exception report */
4092
4093 /* SIN validation */
4094 if l_national_identifier is not null then
4095 select formula_id,
4096 effective_start_date
4097 into l_formula_id,
4098 l_effective_start_date
4099 from ff_formulas_f
4100 where formula_name='NI_VALIDATION'
4101 and business_group_id is null
4102 and legislation_code='CA'
4103 and sysdate between effective_start_date and effective_end_date;
4104
4105 ff_exec.init_formula(l_formula_id,l_effective_start_date,l_inputs,l_outputs);
4106 for l_in_cnt in
4107 l_inputs.first..l_inputs.last
4108 loop
4109 if l_inputs(l_in_cnt).name='NATIONAL_IDENTIFIER' then
4110 l_inputs(l_in_cnt).value := l_national_identifier;
4111 end if;
4112 end loop;
4113 ff_exec.run_formula(l_inputs,l_outputs);
4114
4115 for l_out_cnt in
4116 l_outputs.first..l_outputs.last
4117 loop
4118 hr_utility.trace('inside loop for SIN validation');
4119 if l_outputs(l_out_cnt).name='RETURN_VALUE' then
4120 l_return_value := l_outputs(l_out_cnt).value;
4121 end if;
4122 if l_outputs(l_out_cnt).name='INVALID_MESG' then
4123 l_invalid_mesg := l_outputs(l_out_cnt).value;
4124 end if;
4125 end loop;
4126
4127 if l_return_value = 'INVALID_ID' then
4128 l_invalid_sin := 'Y';
4129 else
4130 l_invalid_sin := 'N';
4131 end if;
4132 else
4133 l_invalid_sin := 'A';
4134 end if;
4135
4136 l_person_arch_step := 2;
4137 hr_utility.trace('selected people');
4138 /* Initialise l_count */
4139 l_count := 0;
4140
4141 --hr_utility.trace_on('Y','ORACLE');
4142
4143 l_counter := l_counter + 1;
4144 l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
4145 l_user_entity_value_tab(l_counter) := l_person_id;
4146
4147 l_counter := l_counter + 1;
4148 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
4149 l_user_entity_value_tab(l_counter) := l_first_name;
4150
4151 l_counter := l_counter + 1;
4152 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
4153 l_user_entity_value_tab(l_counter) := l_last_name;
4154
4155 l_counter := l_counter + 1;
4156 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
4157 l_user_entity_value_tab(l_counter) := l_middle_names;
4158
4159 l_counter := l_counter + 1;
4160 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
4161 l_user_entity_value_tab(l_counter) := l_national_identifier;
4162
4163 l_counter := l_counter + 1;
4164 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN_INVALID';
4165 l_user_entity_value_tab(l_counter) := l_invalid_sin;
4166
4167 l_counter := l_counter + 1;
4168 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
4169 l_user_entity_value_tab(l_counter) := l_employee_number;
4170
4171 l_counter := l_counter + 1;
4172 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_ORGANIZATION_ID';
4173 l_user_entity_value_tab(l_counter) := l_organization_id;
4174
4175 l_counter := l_counter + 1;
4176 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_LOCATION_ID';
4177 l_user_entity_value_tab(l_counter) := l_location_id;
4178
4179 if earning_exists = 1 then
4180 for i in 1..l_counter loop
4181
4182 l_context_id := l_taxunit_context_id;
4183 l_context_val := l_tax_unit_id;
4184
4185 ff_archive_api.create_archive_item(
4186 -- p_validate => 'TRUE'
4187 p_archive_item_id => l_archive_item_id
4188 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
4189 ,p_archive_value => l_user_entity_value_tab(i)
4190 ,p_archive_type => 'AAP'
4191 ,p_action_id => p_assactid
4192 ,p_legislation_code => 'CA'
4193 ,p_object_version_number => l_object_version_number
4194 ,p_some_warning => l_some_warning
4195 );
4196 end loop;
4197 end if;
4198 exception when no_data_found then
4199 l_first_name := null;
4200 l_last_name := null;
4201 l_employee_number := null;
4202 l_national_identifier := null;
4203 l_middle_names := null;
4204 l_employee_phone_no := null;
4205 hr_utility.raise_error;
4206 when others then
4207 hr_utility.trace('Error in archiving person '||
4208 to_char(l_person_id) || 'at step :' ||
4209 to_char(l_person_arch_step) ||
4210 'sqlcode : ' || to_char(sqlcode));
4211 end;
4212
4213 addr := pay_ca_rl1_reg.get_primary_address(l_person_id,p_effective_date);
4214
4215 l_address_line1 := addr.addr_line_1;
4216 l_address_line2 := addr.addr_line_2;
4217 l_address_line3 := addr.addr_line_3;
4218 l_town_or_city := addr.city;
4219 l_province_code := addr.province;
4220 l_postal_code := replace(addr.postal_code,' ');
4221 l_country_code := addr.addr_line_5;
4222
4223 hr_utility.trace('selected address');
4224
4225 l_counter := 0;
4226 l_counter := l_counter + 1;
4227 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
4228 l_user_entity_value_tab(l_counter) := l_address_line1;
4229
4230 l_counter := l_counter + 1;
4231 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE2';
4232 l_user_entity_value_tab(l_counter) := l_address_line2;
4233
4234 l_counter := l_counter + 1;
4235 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE3';
4236 l_user_entity_value_tab(l_counter) := l_address_line3;
4237
4238 l_counter := l_counter + 1;
4239 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE4';
4240 l_user_entity_value_tab(l_counter) := l_address_line4;
4241
4242 l_counter := l_counter + 1;
4243 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_CITY';
4244 l_user_entity_value_tab(l_counter) := l_town_or_city;
4245
4246 l_counter := l_counter + 1;
4247 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_PROVINCE';
4248 l_user_entity_value_tab(l_counter) := l_province_code;
4249
4250 l_counter := l_counter + 1;
4251 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_COUNTRY';
4252 l_user_entity_value_tab(l_counter) := l_country_code;
4253
4254 l_counter := l_counter + 1;
4255 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_POSTAL_CODE';
4256 l_user_entity_value_tab(l_counter) := l_postal_code;
4257
4258 /*
4259 l_counter := l_counter + 1;
4260 l_user_entity_value_tab(l_counter) := 'CAEOY_EMPLOYEE_BUSINESS_NUMBER';
4261 l_user_entity_name_tab(l_counter) := 'To be decided';
4262 */
4263 if earning_exists = 1 then
4264 for i in 1..l_counter loop
4265
4266 l_context_id := l_taxunit_context_id;
4267 l_context_val := l_tax_unit_id;
4268
4269 hr_utility.trace('archiving address');
4270 ff_archive_api.create_archive_item(
4271 p_archive_item_id => l_archive_item_id
4272 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
4273 ,p_archive_value => l_user_entity_value_tab(i)
4274 ,p_archive_type => 'AAP'
4275 ,p_action_id => p_assactid
4276 ,p_legislation_code => 'CA'
4277 ,p_object_version_number => l_object_version_number
4278 ,p_some_warning => l_some_warning
4279 );
4280 hr_utility.trace('archived address');
4281 end loop;
4282 end if;
4283 hr_utility.trace('end of eoy_archive_data');
4284 l_step := 37;
4285
4286
4287 -- Federal YE Amendment Pre-Process Validation (T4 Amendmendment Archiver code)
4288
4289 Begin
4290
4291 hr_utility.trace('Started Federal YE Amendment PP Validation ');
4292 select effective_date,report_type
4293 into ld_fapp_effective_date,lv_fapp_report_type
4294 from pay_payroll_actions
4295 where payroll_action_id = l_payroll_action_id;
4296
4297 hr_utility.trace('Fed Amend Pre-Process Pactid :'||
4298 to_char(l_payroll_action_id));
4299 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
4300
4301 IF lv_fapp_report_type = 'CAEOY_T4_AMEND_PP' then
4302 begin
4303
4304 open c_get_fapp_locked_action_id(p_assactid);
4305 fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
4306 close c_get_fapp_locked_action_id;
4307
4308 hr_utility.trace('T4 Amend PP Action ID : '||to_char(p_assactid));
4309 hr_utility.trace('ln_fapp_locked_action_id :'||
4310 to_char(ln_fapp_locked_action_id));
4311 open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4312 fetch c_get_fapp_lkd_actid_rtype
4313 into lv_fapp_locked_actid_reptype;
4314 close c_get_fapp_lkd_actid_rtype;
4315 hr_utility.trace('lv_fapp_locked_actid_reptype :'||
4316 lv_fapp_locked_actid_reptype);
4317
4318 open c_get_fapp_prov_emp(p_assactid);
4319 loop
4320 fetch c_get_fapp_prov_emp into lv_fapp_prov;
4321 exit when c_get_fapp_prov_emp%NOTFOUND;
4322 hr_utility.trace('lv_fapp_prov : '||lv_fapp_prov);
4323 lv_fapp_flag := compare_archive_data(p_assactid,
4324 ln_fapp_locked_action_id,
4325 lv_fapp_prov);
4326
4327 if lv_fapp_flag = 'Y' then
4328
4329 hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
4330 hr_utility.trace('Archiving T4 Amendment Flag is : ' || lv_fapp_flag);
4331
4332 ff_archive_api.create_archive_item(
4333 p_archive_item_id => l_archive_item_id
4334 ,p_user_entity_id => get_user_entity_id('CAEOY_T4_AMENDMENT_FLAG'
4335 )
4336 ,p_archive_value => lv_fapp_flag
4337 ,p_archive_type => 'AAP'
4338 ,p_action_id => p_assactid
4339 ,p_legislation_code => 'CA'
4340 ,p_object_version_number => l_object_version_number
4341 ,p_context_name1 => 'JURISDICTION_CODE'
4342 ,p_context1 => lv_fapp_prov
4343 ,p_context_name2 => 'TAX_UNIT_ID'
4344 ,p_context2 => l_tax_unit_id
4345 ,p_some_warning => l_some_warning
4346 );
4347
4348 end if;
4349
4350 end loop;
4351 close c_get_fapp_prov_emp;
4352
4353 end; -- report_type validation
4354
4355 END IF; -- report type validation for FAPP
4356 hr_utility.trace('End of Federal YE Amendment PP Validation');
4357
4358 Exception when no_data_found then
4359 hr_utility.trace('Report type not found for given Payroll_action ');
4360 null;
4361 End;
4362 -- End of Federal YE Amendment Pre-Process Validation
4363
4364 end eoy_archive_data;
4365
4366
4367 /* Name : eoy_range_cursor
4368 Purpose : This returns the select statement that is used to created the
4369 range rows for the Year End Pre-Process.
4370 Arguments :
4371 Notes :
4372 */
4373
4374 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
4375
4376 l_legislative_parameters varchar2(240);
4377 l_eoy_tax_unit_id number;
4378 l_transmitter_gre_id number;
4379 l_archive boolean:= FALSE;
4380 l_business_group number;
4381 l_year_start date;
4382 l_year_end date;
4383
4384
4385 begin
4386
4387 --hr_utility.trace_on('Y','ORACLE');
4388
4389 select legislative_parameters,
4390 trunc(effective_date,'Y'),
4391 effective_date,
4392 business_group_id
4393 into l_legislative_parameters,
4394 l_year_start,
4395 l_year_end,
4396 l_business_group
4397 from pay_payroll_actions
4398 where payroll_action_id = pactid;
4399
4400 hr_utility.trace('legislative prameter is '|| l_legislative_parameters);
4401 l_eoy_tax_unit_id := get_parameter('TRANSFER_GRE',l_legislative_parameters);
4402
4403 select org_information11
4404 into l_transmitter_gre_id
4405 from hr_organization_information
4406 where organization_id = l_eoy_tax_unit_id
4407 and org_information_context = 'Canada Employer Identification';
4408
4409 hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
4410 hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
4411
4412 if l_eoy_tax_unit_id <> -99999 then
4413
4414 sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
4415 PPA PAY_PAYROLL_ACTIONS_N51,
4416 PAA PAY_ASSIGNMENT_ACTIONS_N50,
4417 ASG PER_ASSIGNMENTS_F_PK,
4418 PPA1 PAY_PAYROLL_ACTIONS_PK)
4419 USE_NL(PPY, PPA, PAA, ASG, PPA1) */
4420 distinct asg.person_id
4421 from pay_all_payrolls_f ppy,
4422 pay_payroll_actions ppa,
4423 pay_assignment_actions paa,
4424 per_all_assignments_f asg,
4425 pay_payroll_actions ppa1
4426 where ppa1.payroll_action_id = :payroll_action_id
4427 and ppa.effective_date between
4428 fnd_date.canonical_to_date('''||
4429 fnd_date.date_to_canonical(l_year_start)||''') and
4430 fnd_date.canonical_to_date('''||
4431 fnd_date.date_to_canonical(l_year_end)||''')
4432 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
4433 and ppa.action_status = ''C''
4434 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
4435 and ppa.payroll_action_id = paa.payroll_action_id
4436 and paa.tax_unit_id = '|| to_char(l_eoy_tax_unit_id)||'
4437 and paa.action_status = ''C''
4438 and paa.assignment_id = asg.assignment_id
4439 and ppa.business_group_id = asg.business_group_id + 0
4440 and ppa.effective_date between asg.effective_start_date
4441 and asg.effective_end_date
4442 and asg.assignment_type = ''E''
4443 and ppa.payroll_id = ppy.payroll_id
4444 and ppy.business_group_id = '||to_char(l_business_group)||'
4445 order by asg.person_id';
4446
4447 l_archive := chk_gre_archive(pactid);
4448
4449 if g_archive_flag = 'N' then
4450
4451 hr_utility.trace('eoy_range_cursor archiving employer data');
4452
4453 /* now the archiver has provision for archiving payroll_action_level
4454 data. So make use of that */
4455
4456 hr_utility.trace('eoy_range_cursor archiving employer data');
4457
4458 eoy_archive_gre_data(pactid,
4459 l_eoy_tax_unit_id,
4460 l_transmitter_gre_id);
4461 end if;
4462
4463 end if;
4464
4465 end eoy_range_cursor;
4466
4467 end pay_ca_eoy_archive;