[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_ARCHIVE
Source
1 package body pay_ca_eoy_archive as
2 /* $Header: pycayear.pkb 120.20.12010000.8 2008/09/26 14:12:00 sneelapa 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
290 */
291
292
293 sqwl_range varchar2(4000);
294 eoy_gre_range varchar2(4000);
295 eoy_all_range varchar2(4000);
296
297 /* Returns the value of a legislative_parameter from pay_payroll_actions */
298
299 function get_parameter(name in varchar2,
300 parameter_list varchar2)
301 return varchar2
302 is
303 start_ptr number;
304 end_ptr number;
305 token_val pay_payroll_actions.legislative_parameters%type;
306 par_value pay_payroll_actions.legislative_parameters%type;
307 begin
308 --
309 token_val := name||'=';
310 --
311 start_ptr := instr(parameter_list, token_val) + length(token_val);
312 end_ptr := instr(parameter_list, ' ', start_ptr);
313 --
314 /* if there is no spaces use then length of the string */
315 if end_ptr = 0 then
316 end_ptr := length(parameter_list)+1;
317 end if;
318 --
319 /* Did we find the token */
320 if instr(parameter_list, token_val) = 0 then
321 par_value := NULL;
322 else
323 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
324 end if;
325 --
326 return par_value;
327 --
328 end get_parameter;
329
330 /* Name : bal_db_item
331 Purpose : Given the name of a balance DB item as would be seen in a fast formula
332 it returns the defined_balance_id of the balance it represents.
333 Arguments :
334 Notes : A defined balance_id is required by the PLSQL balance function.
335 */
336
337 function bal_db_item
338 (
339 p_db_item_name varchar2
340 ) return number is
341
342 /* Get the defined_balance_id for the specified balance DB item. */
343
344 cursor csr_defined_balance is
345 select to_number(UE.creator_id)
346 from ff_user_entities UE,
347 ff_database_items DI
348 where DI.user_name = p_db_item_name
349 and UE.user_entity_id = DI.user_entity_id
350 and Ue.creator_type = 'B'
351 and UE.legislation_code = 'CA';
352
353 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
354
355 begin
356
357 open csr_defined_balance;
358 fetch csr_defined_balance into l_defined_balance_id;
359 if csr_defined_balance%notfound then
360 close csr_defined_balance;
361 hr_utility.raise_error;
362 else
363 close csr_defined_balance;
364 end if;
365
366 return (l_defined_balance_id);
367
368 end bal_db_item;
369
370
371 /* Name : get_dates
372 Purpose : The dates are dependent on the report being run
373 For T4 it is year end dates.
374
375 */
376
377 procedure get_dates
378 (
379 p_report_type in varchar2,
380 p_effective_date in date,
381 p_period_end in out nocopy date,
382 p_quarter_start in out nocopy date,
383 p_quarter_end in out nocopy date,
384 p_year_start in out nocopy date,
385 p_year_end in out nocopy date
386 ) is
387 begin
388
389 if p_report_type = 'T4' then
390
391 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
392 p_quarter_start := trunc(p_period_end, 'Q');
393 p_quarter_end := p_period_end;
394
395 end if;
396
397 p_year_start := trunc(p_effective_date, 'Y');
398 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
399
400 end get_dates;
401
402
403 /* Name : get_selection_information
404 Purpose : Returns information used in the selection of people to be reported on.
405 Arguments :
406
407 The following values are returned :-
408
409 p_period_start - The start of the period over which to select
410 the people.
411 p_period_end - The end of the period over which to select
412 the people.
413 p_defined_balance_id - The balance which must be non zero for each
414 person to be included in the report.
415 p_group_by_gre - should the people be grouped by GRE.
416 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
417 the testing of the balance.
418 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
419 for the testing of the balance.
420
421 Notes : This routine provides a way of coding explicit rules for
422 individual reports where they are different from the
423 standard selection criteria for the report type ie. in
424 NY state the selection of people in the 4th quarter is
425 different from the first 3.
426 */
427
428 procedure get_selection_information
429 (
430
431 /* Identifies the type of report, the authority for which it is being run,
432 and the period being reported. */
433 p_report_type varchar2,
434 p_quarter_start date,
435 p_quarter_end date,
436 p_year_start date,
437 p_year_end date,
438 /* Information returned is used to control the selection of people to
439 report on. */
440 p_period_start in out nocopy date,
441 p_period_end in out nocopy date,
442 p_defined_balance_id in out nocopy number,
443 p_group_by_gre in out nocopy boolean,
444 p_tax_unit_context in out nocopy boolean,
445 p_jurisdiction_context in out nocopy boolean
446 ) is
447
448 begin
449
450 /* Depending on the report being processed, derive all the information
451 required to be able to select the people to report on. */
452
453 if p_report_type = 'T4' then
454
455 /* Default settings for Year End Pre-process. */
456
457 p_period_start := p_year_start;
458 p_period_end := p_year_end;
459 p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD');
460 p_group_by_gre := FALSE;
461 p_tax_unit_context := TRUE;
462 p_jurisdiction_context := FALSE;
463
464 /* For EOY - end */
465
466 /* An invalid report type has been passed so fail. */
467
468 else
469
470 hr_utility.raise_error;
471
472 end if;
473
474 end get_selection_information;
475
476
477
478
479 /* Name : eoy_action_creation
480 Purpose : This creates the assignment actions for a specific chunk
481 of people to be archived by the year end pre-process.
482 Arguments :
483 Notes :
484 */
485
486 procedure eoy_action_creation(pactid in number,
487 stperson in number,
488 endperson in number,
489 chunk in number) is
490
491
492
493 /* Variables used to hold the select columns from the SQL statement.*/
494
495 l_person_id number;
496 l_assignment_id number;
497 l_tax_unit_id number;
498 l_eoy_tax_unit_id number;
499 l_effective_end_date date;
500 l_archive_item_id number;
501 l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
502
503 /* Variables used to hold the values used as bind variables within the
504 SQL statement. */
505
506 l_bus_group_id number;
507 l_period_start date;
508 l_period_end date;
509
510 /* Variables used to hold the details of the payroll and assignment actions
511 that are created. */
512
513 l_payroll_action_created boolean := false;
514 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
515 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
516
517 /* Variable holding the balance to be tested. */
518
519 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
520
521 /* Indicator variables used to control how the people are grouped. */
522
523 l_group_by_gre boolean := FALSE;
524
525 /* Indicator variables used to control which contexts are set up for
526 balance. */
527
528 l_tax_unit_context boolean := FALSE;
529 l_jurisdiction_context boolean := FALSE;
530
531 /* Variables used to hold the current values returned within the loop for
532 checking against the new values returned from within the loop on the
533 next iteration. */
534
535 l_prev_person_id per_all_people_f.person_id%type;
536 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
537
538 /* Variable to hold the jurisdiction code used as a context for state
539 reporting. */
540
541 l_jurisdiction_code varchar2(30);
542
543 /* general process variables */
544
545 l_report_type pay_payroll_actions.report_type%type;
546 l_province pay_payroll_actions.report_qualifier%type;
547 l_value number;
548 l_effective_date date;
549 l_quarter_start date;
550 l_quarter_end date;
551 l_year_start date;
552 l_year_end date;
553 lockingactid number;
554 l_primary_asg pay_assignment_actions.assignment_id%type;
555 l_legislative_parameters varchar2(240);
556
557
558 /* For Year End Preprocess we have to archive the assignments
559 belonging to a GRE */
560 /*
561 CURSOR c_eoy_gre IS
562 SELECT ASG.person_id person_id,
563 ASG.assignment_id assignment_id,
564 ASG.effective_end_date effective_end_date
565 FROM
566 per_all_assignments_f ASG
567 WHERE
568 ASG.business_group_id = l_bus_group_id AND
569 asg.assignment_type = 'E' AND
570 ASG.person_id between stperson and endperson AND
571 EXISTS
572 (SELECT 1
573 FROM pay_payroll_actions ppa,
574 pay_assignment_actions paa
575 WHERE
576 ppa.business_group_id = l_bus_group_id AND
577 ppa.payroll_action_id = paa.payroll_action_id AND
578 ppa.action_type in ('R','Q','V','B','I') AND
579 ppa.effective_date BETWEEN ASG.effective_start_date AND
580 ASG.effective_end_date AND
581 ppa.effective_date between l_period_start AND
582 l_period_end AND
583 paa.assignment_id = ASG.assignment_id AND
584 paa.tax_unit_id = l_eoy_tax_unit_id)
585 ORDER BY 1, 3 DESC, 2;
586 */
587
588 CURSOR c_eoy_gre IS
589 SELECT /*+ Ordered
590 INDEX (asg PER_ASSIGNMENTS_F_N12)
591 INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
592 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
593 DISTINCT ASG.person_id person_id
594 FROM
595 per_all_assignments_f ASG,
596 pay_assignment_actions paa,
597 pay_payroll_actions ppa
598
599 WHERE ppa.effective_date between l_period_start
600 and l_period_end
601 and ppa.action_type in ('R','Q','V','B','I')
602 and ppa.action_status = 'C'
603 and ppa.business_group_id + 0 = l_bus_group_id
604 and ppa.payroll_action_id = paa.payroll_action_id
605 and paa.tax_unit_id = l_eoy_tax_unit_id
606 and paa.action_status = 'C'
607 and paa.assignment_id = ASG.assignment_id
608 and ppa.business_group_id = ASG.business_group_id +0
609 and ppa.effective_date between ASG.effective_start_date
610 and ASG.effective_end_date
611 AND ASG.person_id between stperson and endperson
612 AND ASG.assignment_type = 'E';
613
614 --Original query:
615 /* SELECT DISTINCT
616 ASG.person_id person_id
617 FROM
618 per_all_assignments_f ASG,
619 pay_all_payrolls_f PPY
620 WHERE exists
621 (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
622 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
623 /* 'x'
624 from pay_payroll_actions ppa,
625 pay_assignment_actions paa
626 where ppa.effective_date between l_period_start
627 and l_period_end
628 and ppa.action_type in ('R','Q','V','B','I')
629 and ppa.action_status = 'C'
630 and ppa.business_group_id + 0 = l_bus_group_id
631 and ppa.payroll_action_id = paa.payroll_action_id
632 and paa.tax_unit_id = l_eoy_tax_unit_id
633 and paa.action_status = 'C'
634 and paa.assignment_id = ASG.assignment_id
635 and ppa.business_group_id = ASG.business_group_id +0
636 and ppa.effective_date between ASG.effective_start_date
637 and ASG.effective_end_date)
638 AND ASG.person_id between stperson and endperson
639 AND ASG.assignment_type = 'E'
640 AND PPY.payroll_id = ASG.payroll_id;
641 */
642
643 /* Commented c_eoy_all, because Tax Unit id is a mandatory parameter
644 in archiver process, this cursor will never be used */
645 /*
646 CURSOR c_eoy_all IS
647 SELECT ASG.person_id person_id,
648 ASG.assignment_id assignment_id,
649 to_number(SCL.segment1) tax_unit_id,
650 ASG.effective_end_date effective_end_date
651 FROM per_all_assignments_f ASG,
652 hr_soft_coding_keyflex SCL,
653 pay_all_payrolls_f PPY
654 WHERE ASG.business_group_id + 0 = l_bus_group_id
655 AND ASG.person_id between stperson and endperson
656 AND ASG.assignment_type = 'E'
657 AND ASG.effective_start_date <= l_period_end
658 AND ASG.effective_end_date >= l_period_start
659 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
660 AND PPY.payroll_id = ASG.payroll_id
661 ORDER BY 1, 3, 4 DESC, 2;
662 */
663 /* Get the primary assignment for the given person_id */
664
665 CURSOR c_get_asg_id (p_person_id number) IS
666 SELECT assignment_id
667 from per_all_assignments_f paf
668 where person_id = p_person_id
669 and primary_flag = 'Y'
670 and paf.effective_start_date <= l_period_end
671 and paf.effective_end_date >= l_period_start
672 and paf.assignment_type = 'E'
673 ORDER BY assignment_id desc;
674
675 /* Cursor to get the latest assignment_action_id based
676 on person_id. Bug#3267520 */
677 CURSOR c_get_latest_asg(p_person_id number ) IS
678 select /*+ Ordered
679 INDEX (asg PER_ASSIGNMENTS_F_N12)
680 INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
681 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
682 paa.assignment_action_id
683 from per_all_assignments_f paf,
684 pay_assignment_actions paa,
685 pay_payroll_actions ppa,
686 pay_action_classifications pac
687 where paf.person_id = p_person_id
688 and paa.assignment_id = paf.assignment_id
689 and paa.tax_unit_id = l_tax_unit_id
690 and paa.payroll_action_id = ppa.payroll_action_id
691 and ppa.action_type = pac.action_type
692 and pac.classification_name = 'SEQUENCED'
693 and ppa.effective_date +0 between paf.effective_start_date
694 and paf.effective_end_date
695 and ppa.effective_date +0 between l_year_start and
696 l_year_end
697 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
698 and paa.source_action_id is null)
699 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
700 and paa.source_action_id is not null )
701 or (ppa.action_type = 'V' and ppa.run_type_id is null
702 and paa.run_type_id is not null
703 and paa.source_action_id is null))
704 order by paa.action_sequence desc;
705
706
707 /* Original Query:
708 select paa.assignment_action_id
709 from pay_assignment_actions paa,
710 per_all_assignments_f paf,
711 pay_payroll_actions ppa,
712 pay_action_classifications pac
713 where paf.person_id = p_person_id
714 and paa.assignment_id = paf.assignment_id
715 and paa.tax_unit_id = l_tax_unit_id
716 and paa.payroll_action_id = ppa.payroll_action_id
717 and ppa.action_type = pac.action_type
718 and pac.classification_name = 'SEQUENCED'
719 and ppa.effective_date +0 between paf.effective_start_date
720 and paf.effective_end_date
721 and ppa.effective_date +0 between l_year_start and
722 l_year_end
723 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
724 and paa.source_action_id is null)
725 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
726 and paa.source_action_id is not null )
727 or (ppa.action_type = 'V' and ppa.run_type_id is null
728 and paa.run_type_id is not null
729 and paa.source_action_id is null))
730 order by paa.action_sequence desc;
731 */
732
733 /* local variables Bug#3267520 */
734 ln_non_taxable_earnings number(30);
735 ln_gross_earnings number(30);
736 ln_no_gross_earnings number(30);
737 ln_max_aa_id number;
738
739 begin
740
741 /* Get the report type, report qualifier, business group id and the
742 gre for which the archiving has to be done */
743
744 hr_utility.trace('getting report type ');
745
746 select effective_date,
747 report_type,
748 business_group_id,
749 legislative_parameters
750 into l_effective_date,
751 l_report_type,
752 l_bus_group_id,
753 l_legislative_parameters
754 from pay_payroll_actions
755 where payroll_action_id = pactid;
756
757 l_eoy_tax_unit_id := get_parameter('TRANSFER_GRE',l_legislative_parameters);
758
759 hr_utility.trace('getting dates');
760
761 get_dates(l_report_type,
762 l_effective_date,
763 l_period_end,
764 l_quarter_start,
765 l_quarter_end,
766 l_year_start,
767 l_year_end);
768
769 hr_utility.trace('getting selection information');
770 hr_utility.trace('report type '|| l_report_type);
771 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
772 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
773 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
774 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
775
776 get_selection_information
777 (l_report_type,
778 l_quarter_start,
779 l_quarter_end,
780 l_year_start,
781 l_year_end,
782 l_period_start,
783 l_period_end,
784 l_defined_balance_id,
785 l_group_by_gre,
786 l_tax_unit_context,
787 l_jurisdiction_context);
788
789 /*
790 if l_eoy_tax_unit_id <> 99999 then
791 open c_eoy_gre;
792 end if;
793 else
794 open c_eoy_all;
795 */
796 open c_eoy_gre;
797
798 /* Loop for all rows returned for SQL statement. */
799
800 hr_utility.trace('Entering loop');
801
802 loop
803
804 if l_eoy_tax_unit_id <> 99999 then
805
806 hr_utility.trace('Fetching person id');
807
808 fetch c_eoy_gre into l_person_id;
809
810 l_tax_unit_id := l_eoy_tax_unit_id;
811
812 exit when c_eoy_gre%NOTFOUND;
813 /*
814 else
815
816 fetch c_eoy_all into l_person_id,
817 l_assignment_id,
818 l_tax_unit_id,
819 l_effective_end_date;
820
821 exit when c_eoy_all%NOTFOUND;
822 */
823 end if;
824
825
826 /* If the new row is the same as the previous row according to the way
827 the rows are grouped then discard the row ie. grouping by GRE
828 requires a single row for each person / GRE combination. */
829
830 hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
831 hr_utility.trace('previous tax unit id is '||
832 to_char(l_prev_tax_unit_id));
833
834 if ( l_person_id = l_prev_person_id and
835 l_tax_unit_id = l_prev_tax_unit_id) then
836
837 hr_utility.trace('Not creating Asg_action, duplicate');
838 null;
839
840 else
841
842 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
843 hr_utility.trace('person is '|| to_char(l_person_id));
844 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
845
846
847 /* Have a new unique row according to the way the rows are grouped.
848 The inclusion of the person is dependent on having a non zero
849 balance. If the balance is non zero then an assignment action
850 is created to indicate their inclusion in the T4 Magnetic Media
851 and T4 Paper Reports. */
852
853 /* Get the primary assignment */
854 open c_get_asg_id(l_person_id);
855 fetch c_get_asg_id into l_primary_asg;
856 if c_get_asg_id%NOTFOUND then
857 close c_get_asg_id;
858 hr_utility.trace('Primary Asg Not found');
859 hr_utility.raise_error;
860 else
861 close c_get_asg_id;
862 end if;
863
864
865 /* Bug#3267520, checking if any earnings exists or not */
866 ln_max_aa_id := null;
867 ln_non_taxable_earnings := 0;
868 ln_gross_earnings := 0;
869 ln_no_gross_earnings := 0;
870
871 begin
872 open c_get_latest_asg(l_person_id );
873 fetch c_get_latest_asg into ln_max_aa_id;
874 close c_get_latest_asg;
875 hr_utility.trace('Action creation Max assignment_action_id : ' ||
876 to_char(ln_max_aa_id));
877
878 exception
879 when no_data_found then
880 ln_max_aa_id := -9999;
881 raise_application_error(-20001,
882 'Balance Assignment Action does not exist for : '
883 ||to_char(l_person_id));
884 end;
885
886 hr_utility.trace('Setting context');
887 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
888 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',ln_max_aa_id);
889
890 ln_non_taxable_earnings :=
891 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
892 ('T4 Non Taxable Earnings',
893 'YTD',ln_max_aa_id,l_primary_asg,NULL,'PER',
894 l_tax_unit_id,l_bus_group_id,NULL),0);
895 hr_utility.trace('T4 Non Taxable Earnings :'||
896 to_char(ln_non_taxable_earnings));
897
898 ln_gross_earnings :=
899 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
900 ('Gross Earnings',
901 'YTD',ln_max_aa_id,l_primary_asg,NULL,'PER' ,
902
903 l_tax_unit_id, l_bus_group_id, NULL),0);
904 hr_utility.trace('Gross Earnings :'||
905 to_char(ln_gross_earnings));
906
907 ln_no_gross_earnings :=
908 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
909 ('T4 No Gross Earnings',
910 'YTD',ln_max_aa_id, l_primary_asg,NULL,'PER' ,
911 l_tax_unit_id, l_bus_group_id, NULL),0);
912 hr_utility.trace('T4 No Gross Earnings :'||
913 to_char(ln_no_gross_earnings));
914
915 /* End of adding code for bug#3267520, below If condition
916 is also part of this bug fix */
917
918 If (((ln_gross_earnings <> 0) and
919 (ln_non_taxable_earnings <> ln_gross_earnings)) or
920 (ln_no_gross_earnings <> 0)) then
921
922 /* Create the assignment action to archive T4 details */
923
924 select pay_assignment_actions_s.nextval
925 into lockingactid
926 from dual;
927
928 /* Insert into pay_assignment_actions. */
929
930 hr_utility.trace('creating assignment action');
931
932 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
933 pactid,chunk,l_tax_unit_id);
934
935 /* Update the serial number column with the person id
936 so that we can use in the Magnetic Media process
937 to do an additional check against the assignment table */
938
939 hr_utility.trace('updating assignment action');
940
941 update pay_assignment_actions aa
942 set aa.serial_number = to_char(l_person_id)
943 where aa.assignment_action_id = lockingactid;
944
945 hr_utility.trace('Created Assignment action'||
946 to_char(lockingactid));
947
948 End if; --Checking Gross Earnings, No Gross Earnings, NonTaxable Earn
949
950 end if; -- validation l_person_id = l_prev_person_id
951
952 /* Record the current values for the next time around the loop. */
953
954 l_prev_person_id := l_person_id;
955 l_prev_tax_unit_id := l_tax_unit_id;
956
957 end loop;
958
959 if l_eoy_tax_unit_id <> 99999 then
960 close c_eoy_gre;
961 /*
962 else
963 close c_eoy_all;
964 */
965 end if;
966
967
968 end eoy_action_creation;
969
970
971
972 /*
973 Name : get_user_entity_id
974 Purpose : This gets the user_entity_id for a specific database item name.
975 Arguments : p_dbi_name -> database item name.
976 Notes :
977 */
978
979 function get_user_entity_id (p_dbi_name in varchar2)
980 return number is
981 l_user_entity_id number;
982
983 begin
984
985 select fdi.user_entity_id
986 into l_user_entity_id
987 from ff_database_items fdi,
988 ff_user_entities fui
989 where user_name = p_dbi_name
990 and fdi.user_entity_id = fui.user_entity_id
991 and fui.legislation_code = 'CA';
992
993 return l_user_entity_id;
994
995 exception
996 when others then
997 hr_utility.trace('Error while getting the user_entity_id for '
998 || p_dbi_name);
999 hr_utility.raise_error;
1000
1001 end get_user_entity_id;
1002
1003
1004
1005 /*
1006 Name : compare_archive_data
1007 Purpose : compares Federal YEPP data and Federal YE Amendment Data
1008 Arguments : p_assignment_action_id -> Assignment_action_id
1009 p_locked_action_id -> YEPP Assignment_action_id
1010 p_jurisdiction -> Jurisdiction_code
1011
1012 Notes : Used specifically for Federal YE Amendment Pre-Process (YE-2003)
1013 */
1014
1015 Function compare_archive_data(p_assignment_action_id in number
1016 ,p_locked_action_id in number
1017 ,p_jurisdiction in varchar2
1018 ) return varchar2 is
1019 TYPE act_info_rec IS RECORD
1020 ( archive_context1 number(25)
1021 ,archive_ue_id number(25)
1022 ,archive_value varchar2(240)
1023 );
1024
1025 TYPE number_data_type_table IS TABLE OF NUMBER
1026 INDEX BY BINARY_INTEGER;
1027
1028 TYPE action_info_table IS TABLE OF act_info_rec
1029 INDEX BY BINARY_INTEGER;
1030
1031 ltr_amend_arch_data action_info_table;
1032 ltr_yepp_arch_data action_info_table;
1033 ltr_amend_emp_data action_info_table;
1034 ltr_yepp_emp_data action_info_table;
1035 ltr_emp_ue_id number_data_type_table;
1036
1037 -- Cursor to get archived values based on Asg_act_id,jurisdiction
1038 cursor c_get_emp_t4box_data(cp_asg_act_id number,
1039 cp_jurisdiction varchar2) is
1040 SELECT fai1.context1,fdi1.user_entity_id,fai1.value
1041 FROM FF_ARCHIVE_ITEMS FAI1,
1042 ff_database_items fdi1,
1043 ff_archive_item_contexts faic,
1044 ff_contexts fc
1045 WHERE FAI1.USER_ENTITY_ID = fdi1.user_entity_id
1046 and fai1.archive_item_id = faic.archive_item_id
1047 and fc.context_id = faic.context_id
1048 and fc.context_name = 'JURISDICTION_CODE'
1049 and faic.context = cp_jurisdiction
1050 AND FAI1.CONTEXT1 = cp_asg_act_id
1051 AND fdi1.user_name <> 'CAEOY_T4_AMENDMENT_FLAG'
1052 order by fdi1.user_name;
1053
1054 -- Cursor to get archived values based on Asg_act_id
1055 cursor c_get_employee_data(cp_asg_act_id number,
1056 cp_dbi_ue_id number) is
1057 select fai.context1,fai.user_entity_id,fai.value
1058 from ff_archive_items fai
1059 where fai.user_entity_id = cp_dbi_ue_id
1060 and fai.context1 = cp_asg_act_id;
1061
1062 i number;
1063 j number;
1064 lv_flag varchar2(2);
1065 ln_yepp_box_count number;
1066 ln_amend_box_count number;
1067
1068
1069 begin
1070 -- hr_utility.trace_on('Y','TEST');
1071 /* Initialization Process */
1072 lv_flag := 'N';
1073 if ltr_amend_arch_data.count > 0 then
1074 ltr_amend_arch_data.delete;
1075 end if;
1076
1077 if ltr_yepp_arch_data.count > 0 then
1078 ltr_yepp_arch_data.delete;
1079 end if;
1080
1081 if ltr_amend_emp_data.count > 0 then
1082 ltr_amend_emp_data.delete;
1083 end if;
1084
1085 if ltr_yepp_emp_data.count > 0 then
1086 ltr_yepp_emp_data.delete;
1087 end if;
1088
1089 if ltr_emp_ue_id.count > 0 then
1090 ltr_emp_ue_id.delete;
1091 end if;
1092
1093
1094 j := 0;
1095 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_INITIAL');
1096
1097 j := j+1;
1098 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_FIRST_NAME');
1099
1100 j := j+1;
1101 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_LAST_NAME');
1102
1103 j := j+1;
1104 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_SIN');
1105
1106 j := j+1;
1107 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_NUMBER');
1108
1109 j := j+1;
1110 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE1');
1111
1112 j := j+1;
1113 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE2');
1114
1115 j := j+1;
1116 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE3');
1117
1118 j := j+1;
1119 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE4');
1120
1121 j := j+1;
1122 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_CITY');
1123
1124 j := j+1;
1125 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_PROVINCE');
1126
1127 j := j+1;
1128 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_COUNTRY');
1129
1130 j := j+1;
1131 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_POSTAL_CODE');
1132
1133
1134 /* Populate T4 Amendment Box Data for an assignment_action */
1135 open c_get_emp_t4box_data(p_assignment_action_id,p_jurisdiction);
1136 hr_utility.trace('Populating T4 Amendment Box Data ');
1137 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1138 ln_amend_box_count := 0;
1139 loop
1140 fetch c_get_emp_t4box_data into ltr_amend_arch_data(ln_amend_box_count);
1141 exit when c_get_emp_t4box_data%NOTFOUND;
1142
1143 hr_utility.trace('I :'||to_char(ln_amend_box_count));
1144 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1145 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1146 hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1147 ln_amend_box_count := ln_amend_box_count + 1;
1148 end loop;
1149
1150 close c_get_emp_t4box_data;
1151
1152 /* Populate T4 Amendment Employee Data for an assignment_action */
1153 hr_utility.trace('Populating Amendment Employee Data ');
1154 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1155 for i in 0 .. j
1156 loop
1157 open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
1158 fetch c_get_employee_data into ltr_amend_emp_data(i);
1159
1160 hr_utility.trace('I :'||to_char(i));
1161 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
1162 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
1163 hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
1164
1165 close c_get_employee_data;
1166 end loop;
1167
1168
1169 /* Populate T4 YEPP Box Data for an assignment_action */
1170 open c_get_emp_t4box_data(p_locked_action_id,p_jurisdiction);
1171 hr_utility.trace('Populating T4 YEPP Box Data ');
1172 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1173 ln_yepp_box_count := 0;
1174 loop
1175 fetch c_get_emp_t4box_data into ltr_yepp_arch_data(ln_yepp_box_count);
1176 exit when c_get_emp_t4box_data%NOTFOUND;
1177
1178 hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1179 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1180 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1181 hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1182 ln_yepp_box_count := ln_yepp_box_count + 1;
1183 end loop;
1184
1185 close c_get_emp_t4box_data;
1186
1187 /* Populate T4 YEPP Employee Data for an assignment_action */
1188 hr_utility.trace('Populating YEPP Employee Data ');
1189 hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
1190 for i in 0 .. j
1191 loop
1192 open c_get_employee_data(P_locked_action_id,ltr_emp_ue_id(i));
1193 fetch c_get_employee_data into ltr_yepp_emp_data(i);
1194 exit when c_get_employee_data%NOTFOUND;
1195
1196 hr_utility.trace('I :'||to_char(i));
1197 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
1198 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
1199 hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
1200
1201 close c_get_employee_data;
1202 end loop;
1203
1204 /* Compare T4 Amendment Box Data and T4 YEPP Box Data for an
1205 assignment_action */
1206
1207 hr_utility.trace('Comparing T4 Amend and T4 YEPP Box Data ');
1208
1209 if ln_yepp_box_count <> ln_amend_box_count then
1210 lv_flag := 'Y';
1211 elsif ln_yepp_box_count = ln_amend_box_count then
1212 for i in ltr_yepp_arch_data.first..ltr_yepp_arch_data.last
1213 loop
1214 if (ltr_yepp_arch_data(i).archive_ue_id =
1215 ltr_amend_arch_data(i).archive_ue_id) then
1216
1217 if ((ltr_yepp_arch_data(i).archive_value <>
1218 ltr_amend_arch_data(i).archive_value) or
1219 (ltr_yepp_arch_data(i).archive_value is null and
1220 ltr_amend_arch_data(i).archive_value is not null) or
1221 (ltr_yepp_arch_data(i).archive_value is not null and
1222 ltr_amend_arch_data(i).archive_value is null)) then
1223
1224 lv_flag := 'Y';
1225 hr_utility.trace('Archive_UE_id with differnt value :'||to_char(ltr_yepp_arch_data(i).archive_ue_id));
1226 exit;
1227 end if;
1228 end if;
1229 end loop;
1230 end if;
1231
1232 /* Compare T4 Employee Data and T4 YEPP Employee Data for an
1233 assignment_action */
1234 If lv_flag <> 'Y' then
1235
1236 hr_utility.trace('Comparing T4 Amend and T4 YEPP Employee Data ');
1237 for i in ltr_yepp_emp_data.first..ltr_yepp_emp_data.last
1238 loop
1239 if (ltr_yepp_emp_data(i).archive_ue_id =
1240 ltr_amend_emp_data(i).archive_ue_id) then
1241
1242 if ((ltr_yepp_emp_data(i).archive_value <>
1243 ltr_amend_emp_data(i).archive_value) or
1244 (ltr_yepp_emp_data(i).archive_value is null and
1245 ltr_amend_emp_data(i).archive_value is not null) or
1246 (ltr_yepp_emp_data(i).archive_value is not null and
1247 ltr_amend_emp_data(i).archive_value is null)) then
1248
1249 lv_flag := 'Y';
1250 hr_utility.trace('Archive_UE_id with different value :'||
1251 to_char(ltr_yepp_arch_data(i).archive_ue_id));
1252 exit;
1253 end if;
1254 end if;
1255 end loop;
1256
1257 End if; -- p_flag <> 'Y'
1258
1259 /* If there is no value difference for Entire Employee data then set
1260 flag to 'N' */
1261
1262 if lv_flag <> 'Y' then
1263 lv_flag := 'N';
1264 hr_utility.trace('No value difference for an Employee Asg Action: '||
1265 to_char(p_assignment_action_id));
1266 end if;
1267
1268 hr_utility.trace('lv_flag :'||lv_flag);
1269 return lv_flag;
1270 -- hr_utility.trace_off;
1271 end compare_archive_data;
1272
1273
1274
1275 /* Name : eoy_archive_gre_data
1276 Purpose : This performs the CA specific employer data archiving.
1277 Arguments :
1278 Notes :
1279 */
1280
1281 procedure eoy_archive_gre_data(p_payroll_action_id in number,
1282 p_tax_unit_id in number,
1283 p_transmitter_gre_id in number)
1284 is
1285
1286 l_user_entity_id number;
1287 l_taxunit_context_id number;
1288 l_jursd_context_id number;
1289 l_value varchar2(240);
1290 l_sit_uid number;
1291 l_sui_uid number;
1292 l_fips_uid number;
1293 l_counter number;
1294 l_seq_tab pay_ca_eoy_archive.number_data_type_table;
1295 l_context_id_tab pay_ca_eoy_archive.number_data_type_table;
1296 l_context_val_tab pay_ca_eoy_archive.char240_data_type_table;
1297 l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1298 l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
1299 l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
1300 l_arch_gre_step number := 0;
1301 l_name varchar2(240);
1302 l_business_group_id number;
1303 l_seq number;
1304 l_context_id number;
1305 l_context_val varchar2(240);
1306 l_employer_ein varchar2(240);
1307 l_address_line_1 varchar2(240);
1308 l_address_line_2 varchar2(240);
1309 l_address_line_3 varchar2(240);
1310 l_town_or_city varchar2(240);
1311 l_province_code varchar2(240);
1312 l_postal_code varchar2(240);
1313 l_country_code varchar2(240);
1314 l_accounting_contact_name varchar2(240);
1315 l_accounting_contact_phone varchar2(240);
1316 l_accounting_contact_area_code varchar2(240);
1317 l_technical_contact_area_code varchar2(240);
1318 l_accounting_contact_extension varchar2(240);
1319 l_proprietor_sin_1 varchar2(240);
1320 l_proprietor_sin_2 varchar2(240);
1321 l_transmitter_name varchar2(240);
1322 l_transmitter_type_indicator varchar2(240);
1323 l_transmitter_type_code varchar2(240);
1324 l_transmitter_data_type_code varchar2(240);
1325 l_transmitter_number varchar2(240);
1326 l_transmitter_addr_line_1 varchar2(240);
1327 l_transmitter_addr_line_2 varchar2(240);
1328 l_transmitter_addr_line_3 varchar2(240);
1329 l_transmitter_city varchar2(240);
1330 l_transmitter_province varchar2(240);
1331 /*l_Federal_Youth_Hire_Ind varchar2(80); */
1332 l_transmitter_postal_code varchar2(240);
1333 l_transmitter_country varchar2(240);
1334 l_transmitter_orgid number;
1335 l_technical_contact_name varchar2(240);
1336 l_technical_contact_phone varchar2(240);
1337 l_technical_contact_extn varchar2(240);
1338 l_technical_contact_email varchar2(240);
1339 l_technical_contact_language varchar2(240);
1340 l_object_version_number number;
1341 l_some_warning boolean;
1342 l_archive_item_id number;
1343 l_taxation_year varchar2(240);
1344 l_effective_date date;
1345 result number;
1346 employer_info_found varchar2(1);
1347 l_ei_rate number;
1348
1349 cursor cur_bg(p_tax_unit_id1 number) is
1350 select
1351 business_group_id
1352 from
1353 hr_all_organization_units
1354 where
1355 organization_id = p_tax_unit_id1;
1356
1357 cursor employer_info is
1358 select
1359 nvl(hoi6.ORG_INFORMATION9,hou.name) GRE_stat_report_name,
1360 hoi6.ORG_INFORMATION1 Employer_identification_number,
1361 hl.ADDRESS_LINE_1 GRE_addrline1,
1362 hl.ADDRESS_LINE_2 GRE_addrline2,
1363 hl.ADDRESS_LINE_3 GRE_addrline3,
1364 hl.TOWN_OR_CITY GRE_town_or_city,
1365 DECODE(hl.STYLE , 'US' , hl.REGION_2 ,
1366 'CA' , hl.REGION_1 ,
1367 'CA_GLB',hl.region_1, ' ') GRE_province,
1368 hl.POSTAL_CODE GRE_postal_code,
1369 hl.COUNTRY GRE_country,
1370 hoi6.org_information3 ei_rate
1371 from
1372 hr_all_organization_units hou,
1373 hr_organization_information hoi6,
1374 hr_locations_all hl
1375 where
1376 hou.organization_id = p_tax_unit_id
1377 and hou.organization_id = hoi6.organization_id
1378 and hoi6.org_information_context = 'Canada Employer Identification'
1379 and hoi6.org_information5 in ('T4/RL1','T4/RL2')
1380 and hou.location_id = hl.location_id;
1381
1382 cursor cur_employer_info is
1383 select
1384 hoi5.ORG_INFORMATION10 GRE_acct_contact_name,
1385 hoi5.ORG_INFORMATION12 GRE_acct_contact_phone,
1386 hoi5.ORG_INFORMATION11 GRE_acct_contact_area_code,
1387 hoi5.ORG_INFORMATION13 GRE_acct_contact_extn,
1388 hoi5.ORG_INFORMATION14 GRE_Proprietor_SIN#1,
1389 hoi5.ORG_INFORMATION15 GRE_Proprietor_SIN#2/*,
1390 hoi5.ORG_INFORMATION16 GRE_Fedyouth_hire_Prgind*/
1391 from
1392 hr_organization_information hoi5
1393 where
1394 hoi5.organization_id = p_tax_unit_id
1395 and hoi5.org_information_context = 'Fed Magnetic Reporting';
1396
1397 cursor cur_transmitter_info is
1398 select
1399 nvl(hoi3.ORG_INFORMATION9,hou.name) trans_stat_report_name,
1400 hl.ADDRESS_LINE_1 trans_addrline1,
1401 hl.ADDRESS_LINE_2 trans_addrline2,
1402 hl.ADDRESS_LINE_3 trans_addrline3,
1403 hl.TOWN_OR_CITY trans_town_or_city,
1404 DECODE(hl.STYLE , 'US', hl.REGION_2,
1405 'CA', hl.REGION_1,
1406 'CA_GLB',hl.region_1, ' ') trans_province,
1407 hl.POSTAL_CODE trans_postal_code,
1408 hl.COUNTRY trans_country,
1409 hoi2.org_information5 trans_type_indicator,
1410 hoi2.ORG_INFORMATION4 trans_number,
1411 hoi2.ORG_INFORMATION2 trans_type_code,
1412 hoi2.ORG_INFORMATION3 trans_datatype_code,
1413 hoi2.ORG_INFORMATION6 trans_tech_contact_name,
1414 hoi2.ORG_INFORMATION8 trans_tech_contact_phone,
1415 hoi2.ORG_INFORMATION7 trans_tech_contact_areacode,
1416 hoi2.ORG_INFORMATION9 trans_tech_contact_lang,
1417 hoi2.ORG_INFORMATION17 trans_tech_contact_extn,
1418 hoi2.ORG_INFORMATION18 trans_tech_contact_email
1419 from
1420 hr_all_organization_units hou,
1421 hr_organization_information hoi2,
1422 hr_organization_information hoi3,
1423 hr_locations_all hl
1424 where
1425 hou.organization_id = p_transmitter_gre_id
1426 and hou.organization_id = hoi2.organization_id
1427 and hoi2.org_information_context = 'Fed Magnetic Reporting'
1428 and hoi2.org_information1 = 'Y'
1429 -- and hoi2.org_information2 = '904' --comented for bug 3973040
1430 and hou.organization_id = hoi3.organization_id
1431 and hoi3.org_information_context = 'Canada Employer Identification'
1432 and hou.location_id = hl.location_id;
1433
1434 begin
1435 /* payroll action level database items */
1436
1437 l_arch_gre_step := 30;
1438
1439 /* Archive the Employer level data */
1440
1441 --hr_utility.trace_on('Y','CAEOY');
1442 hr_utility.trace('getting employer data ');
1443
1444 open cur_bg(p_tax_unit_id);
1445 fetch
1446 cur_bg
1447 into
1448 l_business_group_id;
1449 close cur_bg;
1450
1451 open employer_info;
1452 fetch employer_info
1453 into
1454 l_name,
1455 l_employer_ein,
1456 l_address_line_1,
1457 l_address_line_2,
1458 l_address_line_3,
1459 l_town_or_city,
1460 l_province_code,
1461 l_postal_code,
1462 l_country_code,
1463 l_ei_rate;
1464
1465 if employer_info%NOTFOUND then
1466
1467 hr_utility.trace('cannot find employer data ');
1468 employer_info_found := 'N';
1469
1470 l_employer_ein := null;
1471 l_address_line_1 := null;
1472 l_address_line_2 := null;
1473 l_address_line_3 := null;
1474 l_town_or_city := null;
1475 l_province_code := null;
1476 l_postal_code := null;
1477 l_country_code := null;
1478 l_name := null;
1479
1480 close employer_info;
1481
1482 else
1483
1484 close employer_info;
1485 hr_utility.trace('Employer data found !!!! ');
1486 employer_info_found := 'Y';
1487
1488 end if;
1489
1490 open cur_employer_info;
1491 fetch cur_employer_info
1492 into
1493 l_accounting_contact_name,
1494 l_accounting_contact_phone ,
1495 l_accounting_contact_area_code,
1496 l_accounting_contact_extension,
1497 l_proprietor_sin_1,
1498 l_proprietor_sin_2;/*,
1499 l_federal_youth_hire_ind*/
1500
1501 if cur_employer_info%NOTFOUND then
1502
1503 hr_utility.trace('cannot find employer data 2 ');
1504 employer_info_found := 'N';
1505
1506 l_proprietor_sin_1 := null;
1507 l_proprietor_sin_2 := null;
1508 /* l_federal_youth_hire_ind := null; */
1509 l_accounting_contact_name := null;
1510 l_accounting_contact_phone := null;
1511 l_accounting_contact_area_code := null;
1512 l_accounting_contact_extension := null;
1513 l_accounting_contact_area_code := null;
1514 l_accounting_contact_extension := null;
1515
1516 close cur_employer_info;
1517
1518 else
1519
1520 close cur_employer_info;
1521 hr_utility.trace('Employer data found 2 !!!! ');
1522 employer_info_found := 'Y';
1523
1524 end if;
1525
1526 open cur_transmitter_info;
1527 fetch cur_transmitter_info
1528 into
1529 l_transmitter_name,
1530 l_transmitter_addr_line_1,
1531 l_transmitter_addr_line_2,
1532 l_transmitter_addr_line_3,
1533 l_transmitter_city,
1534 l_transmitter_province,
1535 l_transmitter_postal_code,
1536 l_transmitter_country,
1537 l_Transmitter_Type_Indicator,
1538 l_Transmitter_number,
1539 l_Transmitter_Type_code,
1540 l_Transmitter_data_type_code,
1541 l_technical_contact_name,
1542 l_technical_contact_phone,
1543 l_technical_contact_area_code,
1544 l_technical_contact_language,
1545 l_technical_contact_extn,
1546 l_technical_contact_email;
1547
1548 if cur_transmitter_info%NOTFOUND then
1549
1550 close cur_transmitter_info;
1551 hr_utility.trace('Transmitter information not found');
1552
1553 l_transmitter_name := null;
1554 l_transmitter_addr_line_1 := null;
1555 l_transmitter_addr_line_2 := null;
1556 l_transmitter_addr_line_3 := null;
1557 l_transmitter_city := null;
1558 l_transmitter_province := null;
1559 l_transmitter_postal_code := null;
1560 l_transmitter_country := null;
1561 l_Transmitter_Type_Indicator := null;
1562 l_Transmitter_number := null;
1563 l_Transmitter_Type_code := null;
1564 l_Transmitter_data_type_code := null;
1565 l_technical_contact_name := null;
1566 l_technical_contact_phone := null;
1567 l_technical_contact_area_code := null;
1568 l_technical_contact_language := null;
1569
1570 employer_info_found := 'N';
1571
1572 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1573 hr_utility.set_message_token('ORGIND','GRE');
1574 /* push message into pay_message_lines */
1575 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1576 pay_core_utils.push_token('ORGIND','GRE');
1577 hr_utility.raise_error;
1578
1579 else
1580
1581 close cur_transmitter_info;
1582 employer_info_found := 'Y';
1583
1584 end if;
1585
1586 begin
1587
1588 select to_char(effective_date,'YYYY'),
1589 add_months(trunc(effective_date, 'Y'),12) - 1
1590 into l_taxation_year,
1591 l_effective_date
1592 from pay_payroll_actions
1593 where payroll_action_id = p_payroll_action_id;
1594
1595 exception when no_data_found then
1596 l_taxation_year := null;
1597 l_effective_date := null;
1598
1599 end;
1600
1601 select context_id
1602 into l_taxunit_context_id
1603 from ff_contexts
1604 where context_name = 'TAX_UNIT_ID';
1605
1606 l_counter := 0;
1607 l_arch_gre_step := 40;
1608
1609 l_counter := l_counter + 1;
1610 l_user_entity_name_tab(l_counter) := 'CAEOY_TAXATION_YEAR';
1611 l_user_entity_value_tab(l_counter) := l_taxation_year;
1612
1613 l_counter := l_counter + 1;
1614 l_user_entity_name_tab(l_counter) := 'CAEOY_TAX_UNIT_ID';
1615 l_user_entity_value_tab(l_counter) := p_tax_unit_id;
1616
1617 l_counter := l_counter + 1;
1618 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_COUNTRY';
1619 l_user_entity_value_tab(l_counter) := l_transmitter_country;
1620
1621 l_counter := l_counter + 1;
1622 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NAME';
1623 l_user_entity_value_tab(l_counter) := l_transmitter_name;
1624
1625 l_counter := l_counter + 1;
1626 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE1';
1627 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_1;
1628
1629 l_counter := l_counter + 1;
1630 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE2';
1631 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_2;
1632
1633 -- Bug 4517693
1634 l_counter := l_counter + 1;
1635 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE3';
1636 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_3;
1637
1638 l_counter := l_counter + 1;
1639 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_CITY';
1640 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1641
1642 l_counter := l_counter + 1;
1643 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_PROVINCE';
1644 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1645
1646 l_counter := l_counter + 1;
1647 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_POSTAL_CODE';
1648 l_user_entity_value_tab(l_counter) := l_transmitter_postal_code;
1649
1650 l_counter := l_counter + 1;
1651 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_INDICATOR';
1652 l_user_entity_value_tab(l_counter) := l_transmitter_type_indicator;
1653
1654 l_counter := l_counter + 1;
1655 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NUMBER';
1656 l_user_entity_value_tab(l_counter) := l_transmitter_number;
1657
1658 l_counter := l_counter + 1;
1659 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_CODE';
1660 l_user_entity_value_tab(l_counter) := l_transmitter_type_code;
1661
1662 l_counter := l_counter + 1;
1663 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_DATA_TYPE_CODE';
1664 l_user_entity_value_tab(l_counter) := l_transmitter_data_type_code;
1665
1666 l_counter := l_counter + 1;
1667 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_NAME';
1668 l_user_entity_value_tab(l_counter) := l_technical_contact_name;
1669
1670 l_counter := l_counter + 1;
1671 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_PHONE';
1672 l_user_entity_value_tab(l_counter) := l_technical_contact_phone;
1673
1674 l_counter := l_counter + 1;
1675 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_AREA_CODE';
1676 l_user_entity_value_tab(l_counter) := l_technical_contact_area_code;
1677
1678 l_counter := l_counter + 1;
1679 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EXTN';
1680 l_user_entity_value_tab(l_counter) := l_technical_contact_extn;
1681
1682 l_counter := l_counter + 1;
1683 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EMAIL';
1684 l_user_entity_value_tab(l_counter) := l_technical_contact_email;
1685
1686 l_counter := l_counter + 1;
1687 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_LANGUAGE';
1688 l_user_entity_value_tab(l_counter) := l_technical_contact_language;
1689
1690 l_counter := l_counter + 1;
1691 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_NAME';
1692 l_user_entity_value_tab(l_counter) := l_accounting_contact_name;
1693
1694 l_counter := l_counter + 1;
1695 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_PHONE';
1696 l_user_entity_value_tab(l_counter) := l_accounting_contact_phone ;
1697
1698 l_counter := l_counter + 1;
1699 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE';
1700 l_user_entity_value_tab(l_counter) := l_accounting_contact_area_code ;
1701
1702 l_counter := l_counter + 1;
1703 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_EXTENSION';
1704 l_user_entity_value_tab(l_counter) := l_accounting_contact_extension ;
1705
1706
1707 l_counter := l_counter + 1;
1708 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_NAME';
1709 l_user_entity_value_tab(l_counter) := l_name;
1710
1711 l_counter := l_counter + 1;
1712 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER';
1713 l_user_entity_value_tab(l_counter) := l_employer_ein;
1714
1715 l_counter := l_counter + 1;
1716 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE1';
1717 l_user_entity_value_tab(l_counter) := l_address_line_1;
1718
1719 l_counter := l_counter + 1;
1720 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE2';
1721 l_user_entity_value_tab(l_counter) := l_address_line_2;
1722
1723 -- Bug 4517693
1724 l_counter := l_counter + 1;
1725 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE3';
1726 l_user_entity_value_tab(l_counter) := l_address_line_3;
1727
1728 l_counter := l_counter + 1;
1729 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_CITY';
1730 l_user_entity_value_tab(l_counter) := l_town_or_city;
1731
1732 l_counter := l_counter + 1;
1733 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_PROVINCE';
1734 l_user_entity_value_tab(l_counter) := l_province_code;
1735
1736 l_counter := l_counter + 1;
1737 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_COUNTRY';
1738 l_user_entity_value_tab(l_counter) := l_country_code;
1739
1740 l_counter := l_counter + 1;
1741 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_POSTAL_CODE';
1742 l_user_entity_value_tab(l_counter) := l_postal_code;
1743
1744
1745 l_counter := l_counter + 1;
1746 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN1';
1747 l_user_entity_value_tab(l_counter) := l_proprietor_sin_1;
1748
1749 l_counter := l_counter + 1;
1750 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN2';
1751 l_user_entity_value_tab(l_counter) := l_proprietor_sin_2;
1752
1753 /* l_counter := l_counter + 1;
1754 l_user_entity_name_tab(l_counter):='CAEOY_FEDERAL_YOUTH_HIRE_PROGRAM_INDICATOR';
1755 l_user_entity_value_tab(l_counter) := l_federal_youth_hire_ind; */
1756
1757 l_counter := l_counter + 1;
1758 l_user_entity_name_tab(l_counter) := 'CAEOY_GRE_EI_RATE';
1759 l_user_entity_value_tab(l_counter) := l_ei_rate;
1760
1761 for i in 1..l_counter loop
1762
1763 l_arch_gre_step := 42;
1764 hr_utility.trace('calling archive API ' || l_user_entity_name_tab(i));
1765 ff_archive_api.create_archive_item(
1766 p_archive_item_id => l_archive_item_id
1767 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1768 ,p_archive_value => l_user_entity_value_tab(i)
1769 ,p_archive_type => 'PA'
1770 ,p_action_id => p_payroll_action_id
1771 ,p_legislation_code => 'CA'
1772 ,p_object_version_number => l_object_version_number
1773 ,p_some_warning => l_some_warning
1774 );
1775 hr_utility.trace('Ended calling archive API');
1776 l_arch_gre_step := 47;
1777
1778 end loop;
1779
1780 g_archive_flag := 'Y';
1781 exception
1782 when others then
1783 g_archive_flag := 'N';
1784 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1785 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1786 if l_arch_gre_step = 30 and l_transmitter_name is null then
1787 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1788 hr_utility.set_message_token('ORGIND','GRE');
1789 end if;
1790
1791 hr_utility.raise_error;
1792
1793 end eoy_archive_gre_data;
1794
1795 /* Name : chk_gre_archive
1796 Purpose : Function to check if the employer level data has been archived
1797 or not.
1798 Arguments :
1799 Notes :
1800 */
1801
1802 function chk_gre_archive (p_payroll_action_id number) return boolean is
1803
1804 l_flag varchar2(1);
1805
1806 cursor c_chk_payroll_action is
1807 select 'Y'
1808 from dual
1809 where exists (select null
1810 from ff_archive_items fai
1811 where fai.context1 = p_payroll_action_id
1812 and archive_type = 'PA');
1813 begin
1814
1815 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1816
1817 if g_archive_flag = 'Y' then
1818 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1819 return (TRUE);
1820 else
1821
1822 hr_utility.trace('chk_gre_archive - opening cursor');
1823
1824 open c_chk_payroll_action;
1825 fetch c_chk_payroll_action into l_flag;
1826 if c_chk_payroll_action%FOUND then
1827 hr_utility.trace('chk_gre_archive - found in cursor');
1828 g_archive_flag := 'Y';
1829 else
1830 hr_utility.trace('chk_gre_archive - not found in cursor');
1831 g_archive_flag := 'N';
1832 end if;
1833
1834 hr_utility.trace('chk_gre_archive - closing cursor');
1835 close c_chk_payroll_action;
1836 if g_archive_flag = 'Y' then
1837 hr_utility.trace('chk_gre_archive - returning true');
1838 return (TRUE);
1839 else
1840 hr_utility.trace('chk_gre_archive - returning false');
1841 return(FALSE);
1842 end if;
1843 end if;
1844 end chk_gre_archive;
1845
1846 /* Name : eoy_archinit
1847 Purpose : This performs the context initialization for the year end
1848 pre-process.
1849 Arguments :
1850 Notes :
1851 */
1852
1853
1854 procedure eoy_archinit(p_payroll_action_id in number) is
1855 l_jurisdiction_code VARCHAR2(30);
1856 l_tax_unit_id NUMBER(15);
1857 l_archive boolean:= FALSE;
1858 l_step number := 0;
1859
1860 cursor c_get_min_chunk is
1861 select min(paa.chunk_number)
1862 from pay_assignment_actions paa
1863 where paa.payroll_action_id = p_payroll_action_id;
1864 begin
1865 open c_get_min_chunk;
1866 fetch c_get_min_chunk into g_min_chunk;
1867 l_step := 1;
1868 if c_get_min_chunk%NOTFOUND then
1869 g_min_chunk := -1;
1870 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1871 end if;
1872 close c_get_min_chunk;
1873
1874 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1875 l_step := 2;
1876 l_archive := chk_gre_archive(p_payroll_action_id);
1877
1878 l_step := 3;
1879 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1880 exception
1881 when others then
1882 raise_application_error(-20001,'eoy_archinit at '
1883 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1884 end eoy_archinit;
1885
1886
1887 /* Name : eoy_archive_data
1888 Purpose : This performs the CA specific employee context setting for the
1889 Year End PreProcess.
1890 Arguments :
1891 Notes :
1892 */
1893
1894 PROCEDURE eoy_archive_data(p_assactid in number,
1895 p_effective_date in date) IS
1896
1897 l_aaid pay_assignment_actions.assignment_action_id%type;
1898 l_aaseq pay_assignment_actions.action_sequence%type;
1899 l_asgid pay_assignment_actions.assignment_id%type;
1900 l_date_earned date;
1901 l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1902 l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
1903 l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
1904 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1905 l_business_group_id pay_assignment_actions.tax_unit_id%type;
1906 l_year_start date;
1907 l_year_end date;
1908 l_context_no number := 60;
1909 l_count number := 0;
1910 l_jurisdiction varchar2(11);
1911 l_province_uei ff_user_entities.user_entity_id%type;
1912 l_county_uei ff_user_entities.user_entity_id%type;
1913 l_city_uei ff_user_entities.user_entity_id%type;
1914 l_county_sd_uei ff_user_entities.user_entity_id%type;
1915 l_city_sd_uei ff_user_entities.user_entity_id%type;
1916 l_province_abbrev pay_us_states.state_abbrev%type;
1917 l_county_name pay_us_counties.county_name%type;
1918 l_city_name pay_us_city_names.city_name%type;
1919 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
1920 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
1921 l_step number := 0;
1922 l_county_code varchar2(3);
1923 l_city_code varchar2(4);
1924 l_jursd_context_id ff_contexts.context_id%type;
1925 l_taxunit_context_id ff_contexts.context_id%type;
1926 l_seq_tab pay_ca_eoy_archive.number_data_type_table;
1927 l_context_id_tab pay_ca_eoy_archive.number_data_type_table;
1928 l_context_val_tab pay_ca_eoy_archive.char240_data_type_table;
1929 l_chunk number;
1930 l_payroll_action_id number;
1931 l_defined_balance_id number;
1932 l_result number;
1933 l_person_id number;
1934 l_organization_id number;
1935 l_location_id number;
1936 l_first_name varchar2(240);
1937 l_last_name varchar2(240);
1938 l_employee_number varchar2(240);
1939 l_national_identifier varchar2(240);
1940 l_middle_names per_all_people_f.middle_names%TYPE;
1941 l_employee_phone_no varchar2(240);
1942 l_address_line1 varchar2(240);
1943 l_address_line2 varchar2(240);
1944 l_address_line3 varchar2(240);
1945 l_address_line4 varchar2(240);
1946 l_town_or_city varchar2(80);
1947 l_province_code varchar2(80);
1948 l_postal_code varchar2(80);
1949 l_telephone_number varchar2(80);
1950 l_country_code varchar2(80);
1951 l_counter number;
1952 l_archive_item_id number;
1953 result number;
1954 earning_exists number := 0;
1955 ln_non_taxable_earnings number := 0;
1956 ln_no_gross_earnings number := 0;
1957 ln_gross_earnings number := 0;
1958 l_object_version_number number;
1959 l_context_id number;
1960 l_context_val varchar2(80);
1961 l_some_warning boolean;
1962 l_cpp_exempt_flag varchar2(80);
1963 l_ei_exempt_flag varchar2(80);
1964 /* Added by ssmukher for PPIP tax implementation */
1965 l_ppip_exempt_flag varchar2(80);
1966 qc_result number;
1967 l_inputs ff_exec.inputs_t;
1968 l_outputs ff_exec.outputs_t;
1969 l_return_value varchar2(240);
1970 l_invalid_mesg varchar2(240);
1971 l_invalid_sin varchar2(240);
1972 l_formula_id number;
1973 l_effective_start_date date;
1974 l_value number;
1975 old_l_value number;
1976 l_legislative_parameters varchar2(240);
1977 l_footnote_code varchar2(10);
1978 l_footnote_balance varchar2(80);
1979 l_registration_no varchar2(150);
1980 old_l_registration_no varchar2(150);
1981 l_balance_name varchar2(150);
1982 l_negative_balance_exists varchar2(5) ;
1983 l_person_arch_step number;
1984 l_cpp_ee_withheld_pjgy number;
1985 l_qpp_ee_withheld_pjgy number;
1986 l_ei_ee_withheld_pjgy number;
1987 addr pay_ca_rl1_reg.primaryaddress;
1988 l_user_entity_id ff_user_entities.user_entity_id%TYPE;
1989
1990 other_info_amount31 number;
1991 other_info_amount53 number;
1992 other_info_amount78 number;
1993 other_info_amount71 number;
1994
1995 -- code start for Bug 5698016
1996 other_info_amount81 number;
1997 other_info_amount82 number;
1998 other_info_amount83 number;
1999 other_info_amount84 number;
2000 other_info_amount85 number;
2001 -- code ended for Bug 5698016
2002
2003 box_52_exists varchar2(1) ;
2004 l_balance_name1 pay_balance_types.balance_name%TYPE;
2005 lv_empcode_prov varchar2(20);
2006 lv_employment_code varchar2(20);
2007 lv_serial_number varchar2(30);
2008 ld_date_of_birth date;
2009 lv_under18_flag varchar2(2);
2010 lv_over70_flag varchar2(2);
2011 ln_cpp_ee_taxable_pjgy number;
2012 ln_qpp_ee_taxable_pjgy number ;
2013 ln_ei_ee_taxable_pjgy number ;
2014 lv_cpp_archive_exempt_flag varchar2(20);
2015 lv_ei_archive_exempt_flag varchar2(20);
2016 /* Added by ssmukher for PPIP tax implementation */
2017 lv_ppip_archive_exempt_flag varchar2(20);
2018 l_ppip_ee_withheld_pjgy number;
2019 ln_ppip_ee_taxable_pjgy number;
2020
2021 lv_qpp_exempt_flag varchar2(20) ;
2022
2023 /* Added for Bug 4028693 */
2024 l_box14_flag char(1);
2025
2026 /* new variables added for Federal YE Amendment PP */
2027 ld_fapp_effective_date date;
2028 lv_fapp_report_type varchar2(20);
2029 ln_fapp_locked_action_id number;
2030 lv_fapp_prov varchar2(5);
2031 lv_fapp_flag varchar2(2);
2032 lv_fapp_locked_actid_reptype varchar2(20);
2033 ln_fapp_prev_amend_actid number;
2034
2035 l_transmitter_gre_id number;
2036
2037 l_status_indian varchar2(1);
2038 /* Added new variable for Bug 3447439 by ssmukher*/
2039 lv_actual_date date;
2040
2041 -- l_screen_entry_value added by sneelapa for bug 6399498
2042 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%type;
2043
2044 CURSOR get_person_info(p_asgid number) IS
2045 SELECT
2046 PEOPLE.person_id,
2047 PEOPLE.first_name,
2048 PEOPLE.last_name,
2049 PEOPLE.employee_number,
2050 replace(PEOPLE.national_identifier,' '),
2051 PEOPLE.middle_names,
2052 ASSIGN.organization_id,
2053 ASSIGN.location_id
2054 FROM
2055 per_all_assignments_f ASSIGN,
2056 per_all_people_f PEOPLE
2057 WHERE ASSIGN.assignment_id = p_asgid
2058 and l_date_earned BETWEEN ASSIGN.effective_start_date
2059 AND ASSIGN.effective_end_date
2060 AND PEOPLE.person_id = ASSIGN.person_id
2061 AND PEOPLE.effective_end_date = (select max(effective_end_date) from
2062 per_all_people_f PEOPLE1
2063 where PEOPLE1.person_id = PEOPLE.person_id);
2064
2065 /* Get the jurisdiction code of all the cities
2066 for the person_id corresponding to the
2067 assignment_id . Take it from pay_action_context table. */
2068
2069 cursor c_get_province(p_asgid number) is
2070 select distinct context_value
2071 from pay_action_contexts pac
2072 where pac.assignment_id = p_asgid;
2073
2074 /* for testing , since there is no data in pay_action_contexts table */
2075 cursor c_get_test_province is
2076 select province_abbrev
2077 from pay_ca_provinces_v pac;
2078
2079 -- l_business_group_id condition added by sneelapa, for bug 6399498
2080 /*
2081 cursor c_balance_feed_info (p_balance_name varchar2) is
2082 select nvl(pet.element_information20,'NOT FOUND'),
2083 pbt1.balance_name
2084 from pay_balance_feeds_f pbf,
2085 pay_balance_types pbt,
2086 pay_balance_types pbt1,
2087 pay_input_values_f piv,
2088 pay_element_types_f pet
2089 where pbt.balance_name = p_balance_name
2090 and pbf.balance_type_id = pbt.balance_type_id
2091 and pbf.input_value_id = piv.input_value_id
2092 and piv.element_type_id = pet.element_type_id
2093 and pbt1.balance_type_id = pet.element_information10
2094 -- and pet.element_information_category = 'CA_EARNINGS'
2095 and pet.business_group_id = l_business_group_id
2096 and pet.element_information20 is not null;
2097 */
2098
2099 --CURSOR c_balance_feed_info is modified by sneelapa for bug 6399498
2100 -- For issue reported by QA during testing of above bug.
2101 cursor c_balance_feed_info (p_balance_name varchar2) is
2102 select nvl(pet.element_information20,'NOT FOUND'),
2103 pbt1.balance_name,
2104 pev.screen_entry_value
2105 from pay_balance_feeds_f pbf,
2106 pay_balance_types pbt,
2107 pay_balance_types pbt1,
2108 pay_input_values_f piv,
2109 pay_element_types_f pet,
2110 pay_element_entries_f pee,
2111 pay_element_entry_values_f pev
2112 where pbt.balance_name = p_balance_name
2113 and pee.assignment_id = l_asgid
2114 and pbf.balance_type_id = pbt.balance_type_id
2115 and pbf.input_value_id = piv.input_value_id
2116 and piv.element_type_id = pet.element_type_id
2117 and pbt1.balance_type_id = pet.element_information10
2118 and pet.business_group_id = l_business_group_id
2119 and pet.element_information20 is not null
2120 and pet.element_type_id = pee.element_type_id
2121 -- and trunc(p_effective_date) between pee.effective_start_date and pee.effective_end_date
2122 and ((pee.effective_start_date <= l_year_end
2123 and pee.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
2124 or
2125 (pee.effective_end_date between l_year_start and l_year_end))
2126 -- and trunc(p_effective_date) between pev.effective_start_date and pev.effective_end_date
2127 and ((pev.effective_start_date <= l_year_end
2128 and pev.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
2129 or
2130 (pev.effective_end_date between l_year_start and l_year_end))
2131 and pee.element_entry_id = pev.element_entry_id
2132 -- and fnd_number.canonical_to_number(pev.screen_entry_value) >= 0
2133 -- and pet.element_information_category = 'CA_EARNINGS'
2134 -- and pev.input_value_id = piv.input_value_id
2135 ;
2136
2137
2138 cursor cur_bg(p_tax_unit_id1 number) is
2139 select business_group_id
2140 from hr_all_organization_units
2141 where organization_id = p_tax_unit_id1;
2142
2143 /* New cursors added for Federal YE Amendment Pre-Process Validation */
2144 CURSOR c_get_fapp_prov_emp(cp_assignment_action_id number) IS
2145 select fai.value
2146 from ff_archive_items fai,
2147 ff_database_items fdi
2148 where fdi.user_entity_id = fai.user_entity_id
2149 and fai.context1 = cp_assignment_action_id
2150 and fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
2151
2152 CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
2153 select ppa.report_type
2154 from pay_payroll_actions ppa,pay_assignment_actions paa
2155 where paa.assignment_action_id = cp_locked_actid
2156 and ppa.payroll_action_id = paa.payroll_action_id;
2157
2158 CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
2159 select locked_action_id
2160 from pay_action_interlocks
2161 where locking_action_id = cp_locking_act_id;
2162
2163
2164 /* cursor to get the T4 Employment Code, Bug#2141132 */
2165 cursor c_get_employment_code(cp_gre varchar2,
2166 cp_person_id number) IS
2167 select pei_information2,
2168 pei_information3
2169 from per_people_extra_info
2170 where person_id = cp_person_id
2171 and pei_information_category = 'ADDITIONAL_T4_INFORMATION'
2172 and pei_information1 = cp_gre;
2173
2174 /* Modified the cursor for bug fix 3447439 */
2175 CURSOR c_get_latest_asg(p_person_id number,
2176 p_jurisdiction varchar2) IS
2177 select /*+ Ordered */
2178 paa.assignment_action_id,
2179 ppa.date_earned
2180 from per_all_assignments_f paf,
2181 pay_assignment_actions paa,
2182 pay_payroll_actions ppa,
2183 pay_action_classifications pac,
2184 pay_action_contexts pac1,
2185 ff_contexts fc
2186 where paf.person_id = p_person_id
2187 and paa.assignment_id = paf.assignment_id
2188 and paa.tax_unit_id = l_tax_unit_id
2189 and paa.payroll_action_id = ppa.payroll_action_id
2190 and ppa.action_type = pac.action_type
2191 and pac.classification_name = 'SEQUENCED'
2192 and ppa.effective_date +0 between paf.effective_start_date
2193 and paf.effective_end_date
2194 and ppa.effective_date +0 between l_year_start
2195 and l_year_end
2196 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
2197 and paa.source_action_id is null)
2198 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
2199 and paa.source_action_id is not null )
2200 or (ppa.action_type = 'V' and ppa.run_type_id is null
2201 and paa.run_type_id is not null
2202 and paa.source_action_id is null))
2203 and pac1.assignment_action_id = paa.assignment_action_id
2204 and pac1.context_id = fc.context_id
2205 and fc.context_name = 'JURISDICTION_CODE'
2206 and pac1.context_value = p_jurisdiction
2207 order by paa.action_sequence desc;
2208
2209 /* Modified cursor c_get_latest_asg by ssmukher for Bug 3447439 */
2210 /*
2211 cursor c_get_latest_asg(cp_person_id number,
2212 cp_tax_unit_id number,
2213 cp_jurisdiction varchar2) is
2214 select paa.assignment_action_id,
2215 ppa.date_earned
2216 from pay_assignment_actions paa,
2217 per_all_assignments_f paf,
2218 per_all_people_f ppf,
2219 pay_payroll_actions ppa,
2220 pay_action_classifications pac,
2221 pay_action_contexts pac1,
2222 ff_contexts fc
2223 where ppf.person_id = cp_person_id
2224 and paf.person_id = ppf.person_id
2225 and paf.assignment_id = paa.assignment_id
2226 and paa.tax_unit_id = cp_tax_unit_id
2227 and ppa.payroll_action_id = paa.payroll_action_id
2228 and ppa.effective_date+0 between l_year_start
2229 and l_year_end
2230 and ppa.effective_date between ppf.effective_start_date
2231 and ppf.effective_end_date
2232 and ppa.effective_date between paf.effective_start_date
2233 and paf.effective_end_date
2234 and ppa.action_type = pac.action_type
2235 and pac.classification_name = 'SEQUENCED'
2236 and pac1.assignment_action_id = paa.assignment_action_id
2237 and pac1.context_id = fc.context_id
2238 and fc.context_name = 'JURISDICTION_CODE'
2239 and pac1.context_value = cp_jurisdiction
2240 order by paa.action_sequence desc;
2241 */
2242
2243 /* cursor to get date_of_birth for an employee to check EI and CPP Exempt */
2244 cursor c_get_date_of_birth(ln_person_id number
2245 ,ld_eff_date date) is
2246 select ppf.date_of_birth
2247 from per_all_people_f ppf
2248 where ppf.person_id = ln_person_id
2249 and ppf.effective_end_date = (select max(ppf2.effective_end_date)
2250 from per_all_people_f ppf2
2251 where ppf2.person_id= ln_person_id
2252 and ppf2.effective_start_date
2253 <= ld_eff_date);
2254
2255 /* This cursor fetches the Status Indian flag for a assignment */
2256 CURSOR c_get_status_indian(cp_assign number,
2257 cp_effec_date date) IS
2258 select ca_tax_information1
2259 from pay_ca_emp_fed_tax_info_f pca
2260 where pca.assignment_id = cp_assign
2261 and cp_effec_date between pca.effective_start_date and
2262 pca.effective_end_date;
2263 begin
2264
2265 -- hr_utility.trace_on(1,'ORACLE');
2266
2267 l_negative_balance_exists := 'N';
2268 box_52_exists := 'N';
2269 lv_under18_flag := 'N';
2270 lv_over70_flag := 'N';
2271 ln_cpp_ee_taxable_pjgy := 0;
2272 ln_qpp_ee_taxable_pjgy := 0;
2273 ln_ei_ee_taxable_pjgy := 0;
2274 lv_cpp_archive_exempt_flag := Null;
2275 lv_ei_archive_exempt_flag := Null;
2276 lv_ppip_archive_exempt_flag := Null;
2277 lv_qpp_exempt_flag := Null;
2278 lv_fapp_flag := 'N';
2279 l_count := 0;
2280 hr_utility.trace('p_assactida value '||p_assactid);
2281 hr_utility.trace('getting assignment');
2282
2283
2284 SELECT
2285 aa.assignment_id,
2286 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2287 aa.tax_unit_id,
2288 aa.chunk_number,
2289 aa.payroll_action_id,
2290 aa.serial_number
2291 INTO
2292 l_asgid,
2293 l_date_earned,
2294 l_tax_unit_id,
2295 l_chunk,
2296 l_payroll_action_id,
2297 lv_serial_number
2298 FROM
2299 pay_assignment_actions aa
2300 WHERE
2301 aa.assignment_action_id = p_assactid;
2302
2303 l_year_start := trunc(p_effective_date, 'Y');
2304 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2305
2306 OPEN cur_bg(l_tax_unit_id);
2307 FETCH
2308 cur_bg
2309 INTO
2310 l_business_group_id;
2311 CLOSE cur_bg;
2312
2313 l_step := 1;
2314
2315 /*Bug 5706114 Fetching the Status Indian flag */
2316
2317 open c_get_status_indian(l_asgid,p_effective_date);
2318 fetch c_get_status_indian
2319 into l_status_indian;
2320 close c_get_status_indian;
2321
2322 /* Call the archive_gre_data procedure */
2323 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2324 select org_information11
2325 into l_transmitter_gre_id
2326 from hr_organization_information
2327 where organization_id = l_tax_unit_id
2328 and org_information_context = 'Canada Employer Identification';
2329
2330 l_step := 3;
2331 hr_utility.trace('eoy_archive_data archiving employer data');
2332 eoy_archive_gre_data(l_payroll_action_id,
2333 l_tax_unit_id,
2334 l_transmitter_gre_id);
2335
2336 l_step := 4;
2337 hr_utility.trace('eoy_archive_data archived employer data');
2338 end if;
2339
2340 /* Now, set up the jurisdiction context for the db items that
2341 need the jurisdiction as a context.Here we are archiving all the
2342 jurisdictions we got from pay_action_contexts for all assignment_actions.
2343 So even though a particular assignment_action is for aparticular jurisdiction
2344 the archiver table has data for all the jurisdictions, but values of
2345 irrelevant jurisdictions will be 0 */
2346
2347 /* Change it to c_get_province later on */
2348 OPEN c_get_test_province;
2349 LOOP
2350
2351 /* initializing local variables used for T4 Box 28 for each
2352 jurisdiction part of fix for bug#3422384 */
2353 lv_over70_flag := 'N';
2354 lv_under18_flag := 'N';
2355 l_cpp_exempt_flag := NULL;
2356 l_ei_exempt_flag := NULL;
2357 l_ppip_exempt_flag := NULL;
2358 l_cpp_ee_withheld_pjgy := 0;
2359 ln_cpp_ee_taxable_pjgy := 0;
2360 lv_cpp_archive_exempt_flag := NULL;
2361 lv_qpp_exempt_flag := NULL;
2362 l_qpp_ee_withheld_pjgy := 0;
2363 ln_qpp_ee_taxable_pjgy := 0;
2364 l_ei_exempt_flag := NULL;
2365 l_ei_ee_withheld_pjgy := 0;
2366 ln_ei_ee_taxable_pjgy := 0;
2367 l_ppip_ee_withheld_pjgy := 0;
2368 ln_ppip_ee_taxable_pjgy := 0;
2369 lv_ei_archive_exempt_flag := NULL;
2370 lv_ppip_archive_exempt_flag := Null;
2371 ld_date_of_birth := NULL;
2372
2373
2374 /* Initialise l_count */
2375 l_count := 0;
2376 l_step := 11;
2377
2378 FETCH c_get_test_province
2379 INTO l_jurisdiction;
2380
2381 hr_utility.trace('In jurisdiction loop ' || l_jurisdiction);
2382 EXIT WHEN c_get_test_province%NOTFOUND;
2383
2384 /*
2385 SELECT
2386 paa1.assignment_action_id
2387 INTO
2388 l_aaid
2389 FROM
2390 pay_assignment_actions paa1,
2391 per_all_assignments_f paf2
2392 WHERE
2393 paa1.assignment_id = paf2.assignment_id
2394 and paa1.tax_unit_id = l_tax_unit_id
2395 and (paa1.action_sequence , paf2.person_id) =
2396 (SELECT MAX(paa.action_sequence), paf.person_id
2397 FROM pay_action_classifications pac,
2398 pay_payroll_actions ppa,
2399 pay_assignment_actions paa,
2400 per_all_assignments_f paf1,
2401 per_all_assignments_f paf
2402 WHERE paf.assignment_id = l_asgid
2403 AND paf1.person_id = paf.person_id
2404 AND paa.tax_unit_id = l_tax_unit_id
2405 AND paa.assignment_id = paf1.assignment_id
2406 AND paa.payroll_action_id = ppa.payroll_action_id
2407 AND ppa.action_type = pac.action_type
2408 AND pac.classification_name = 'SEQUENCED'
2409 AND ppa.effective_date <= p_effective_date
2410 group by paf.person_id)
2411 and rownum < 2;
2412 */
2413 begin
2414
2415 open c_get_latest_asg(lv_serial_number,l_jurisdiction);
2416 fetch c_get_latest_asg into l_aaid,lv_actual_date;
2417 close c_get_latest_asg;
2418
2419 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2420
2421 exception
2422 when no_data_found then
2423 l_aaid := -9999;
2424 raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2425 ||to_char(l_person_id));
2426 end;
2427
2428 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2429 pay_ca_balance_view_pkg.set_context( 'JURISDICTION_CODE', l_jurisdiction);
2430
2431 hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2432
2433 /* Assign values to the PL/SQL tables */
2434
2435 l_step := 16;
2436
2437 l_seq_tab(1) := 1;
2438 l_context_id_tab(1) := l_jursd_context_id;
2439 l_context_val_tab(1) := l_jurisdiction;
2440
2441 l_seq_tab(2) := 2;
2442 l_context_id_tab(2) := l_taxunit_context_id;
2443 l_context_val_tab(2) := l_tax_unit_id;
2444
2445 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2446 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2447 pay_ca_balance_view_pkg.set_context( 'JURISDICTION_CODE', l_jurisdiction);
2448
2449 l_count := l_count + 1;
2450 l_user_entity_name_tab(l_count) := 'CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD';
2451 l_balance_type_tab(l_count) := 'Gross Earnings';
2452
2453 l_count := l_count + 1;
2454 l_user_entity_name_tab(l_count) := 'CAEOY_CPP_EE_WITHHELD_PER_JD_GRE_YTD';
2455 l_balance_type_tab(l_count) := 'CPP EE Withheld';
2456
2457 l_count := l_count + 1;
2458 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_WITHHELD_PER_JD_GRE_YTD';
2459 l_balance_type_tab(l_count) := 'QPP EE Withheld';
2460
2461 l_count := l_count + 1;
2462 l_user_entity_name_tab(l_count) := 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD';
2463 l_balance_type_tab(l_count) := 'EI EE Withheld';
2464
2465 l_count := l_count + 1;
2466 l_user_entity_name_tab(l_count) := 'CAEOY_FED_WITHHELD_PER_JD_GRE_YTD';
2467 l_balance_type_tab(l_count) := 'FED Withheld';
2468
2469 l_count := l_count + 1;
2470 l_user_entity_name_tab(l_count) := 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD';
2471 l_balance_type_tab(l_count) := 'EI EE Taxable';
2472
2473 IF l_jurisdiction ='QC' THEN
2474
2475 l_count := l_count + 1;
2476 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_WITHHELD_PER_JD_GRE_YTD';
2477 l_balance_type_tab(l_count) := 'PPIP EE Withheld';
2478
2479
2480 l_count := l_count + 1;
2481 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_TAXABLE_PER_JD_GRE_YTD';
2482 l_balance_type_tab(l_count) := 'PPIP EE Taxable';
2483
2484 END IF;
2485
2486 l_count := l_count + 1;
2487 l_user_entity_name_tab(l_count) := 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD';
2488 l_balance_type_tab(l_count) := 'CPP EE Taxable';
2489
2490 l_count := l_count + 1;
2491 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD';
2492 l_balance_type_tab(l_count) := 'QPP EE Taxable';
2493
2494 l_count := l_count + 1;
2495 l_user_entity_name_tab(l_count) := 'CAEOY_CPP_EE_RSUBJECT_PER_JD_GRE_YTD';
2496 l_balance_type_tab(l_count) := 'CPP Reduced Subject';
2497
2498 l_count := l_count + 1;
2499 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_RSUBJECT_PER_JD_GRE_YTD';
2500 l_balance_type_tab(l_count) := 'QPP Reduced Subject';
2501
2502 l_count := l_count + 1;
2503 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX20_PER_JD_GRE_YTD';
2504 l_balance_type_tab(l_count) := 'T4_BOX20';
2505
2506 l_count := l_count + 1;
2507 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX44_PER_JD_GRE_YTD';
2508 l_balance_type_tab(l_count) := 'T4_BOX44';
2509
2510 l_count := l_count + 1;
2511 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX46_PER_JD_GRE_YTD';
2512 l_balance_type_tab(l_count) := 'T4_BOX46';
2513
2514 /* l_count := l_count + 1
2515 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX50_PER_JD_GRE_YTD';
2516 l_balance_type_tab(l_count) := 'T4_BOX50'; */
2517
2518 l_count := l_count + 1;
2519 l_user_entity_name_tab(l_count) := 'CAEOY_T4_BOX52_PER_JD_GRE_YTD';
2520 l_balance_type_tab(l_count) := 'T4_BOX52';
2521
2522 l_count := l_count + 1;
2523 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT30_PER_JD_GRE_YTD';
2524 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT30';
2525
2526 l_count := l_count + 1;
2527 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT32_PER_JD_GRE_YTD';
2528 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT32';
2529
2530 l_count := l_count + 1;
2531 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT33_PER_JD_GRE_YTD';
2532 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT33';
2533
2534 l_count := l_count + 1;
2535 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT34_PER_JD_GRE_YTD';
2536 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT34';
2537
2538 l_count := l_count + 1;
2539 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT35_PER_JD_GRE_YTD';
2540 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT35';
2541
2542 l_count := l_count + 1;
2543 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT36_PER_JD_GRE_YTD';
2544 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT36';
2545
2546 l_count := l_count + 1;
2547 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT37_PER_JD_GRE_YTD';
2548 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT37';
2549
2550 l_count := l_count + 1;
2551 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT38_PER_JD_GRE_YTD';
2552 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT38';
2553
2554 l_count := l_count + 1;
2555 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT39_PER_JD_GRE_YTD';
2556 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT39';
2557
2558 l_count := l_count + 1;
2559 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT40_PER_JD_GRE_YTD';
2560 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT40';
2561
2562 l_count := l_count + 1;
2563 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT41_PER_JD_GRE_YTD';
2564 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT41';
2565
2566 l_count := l_count + 1;
2567 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT42_PER_JD_GRE_YTD';
2568 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT42';
2569
2570 l_count := l_count + 1;
2571 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT43_PER_JD_GRE_YTD';
2572 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT43';
2573
2574 l_count := l_count + 1;
2575 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT70_PER_JD_GRE_YTD';
2576 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT70';
2577
2578 l_count := l_count + 1;
2579 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT71_PER_JD_GRE_YTD';
2580 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT71';
2581
2582 l_count := l_count + 1;
2583 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT72_PER_JD_GRE_YTD';
2584 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT72';
2585
2586 l_count := l_count + 1;
2587 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT73_PER_JD_GRE_YTD';
2588 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT73';
2589
2590 l_count := l_count + 1;
2591 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT74_PER_JD_GRE_YTD';
2592 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT74';
2593
2594 l_count := l_count + 1;
2595 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT75_PER_JD_GRE_YTD';
2596 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT75';
2597
2598 l_count := l_count + 1;
2599 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT76_PER_JD_GRE_YTD';
2600 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT76';
2601
2602 l_count := l_count + 1;
2603 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT77_PER_JD_GRE_YTD';
2604 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT77';
2605
2606 l_count := l_count + 1;
2607 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT79_PER_JD_GRE_YTD';
2608 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT79';
2609
2610 l_count := l_count + 1;
2611 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT80_PER_JD_GRE_YTD';
2612 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT80';
2613
2614 -- change started for Bug 5698016
2615
2616 l_count := l_count + 1;
2617 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT81_PER_JD_GRE_YTD';
2618 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT81';
2619
2620 l_count := l_count + 1;
2621 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT82_PER_JD_GRE_YTD';
2622 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT82';
2623
2624 l_count := l_count + 1;
2625 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT83_PER_JD_GRE_YTD';
2626 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT83';
2627
2628 l_count := l_count + 1;
2629 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT84_PER_JD_GRE_YTD';
2630 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT84';
2631
2632 l_count := l_count + 1;
2633 l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT85_PER_JD_GRE_YTD';
2634 l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT85';
2635
2636 --- change ended for Bug 5698016
2637
2638
2639 l_count := l_count + 1;
2640 l_user_entity_name_tab(l_count)
2641 := 'CAEOY_CPP_BASIC_EXEMPTION_PER_JD_GRE_YTD';
2642 l_balance_type_tab(l_count) := 'CPP EE Basic Exemption';
2643
2644 l_count := l_count + 1;
2645 l_user_entity_name_tab(l_count)
2646 := 'CAEOY_CPP_EXEMPT_PER_JD_GRE_YTD';
2647 l_balance_type_tab(l_count) := 'CPP Exempt';
2648
2649
2650 ln_non_taxable_earnings :=
2651 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2652 ('T4 Non Taxable Earnings',
2653 'YTD',l_aaid,l_asgid,NULL,'PER',
2654 l_tax_unit_id,l_business_group_id,l_jurisdiction),0);
2655
2656 ln_gross_earnings :=
2657 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2658 ('Gross Earnings',
2659 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
2660 l_tax_unit_id, l_business_group_id, l_jurisdiction),0);
2661
2662 ln_no_gross_earnings :=
2663 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2664 ('T4 No Gross Earnings',
2665 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
2666 l_tax_unit_id, l_business_group_id, l_jurisdiction),0);
2667
2668 if (((ln_gross_earnings <> 0) and
2669 (ln_non_taxable_earnings <> ln_gross_earnings)) or
2670 (ln_no_gross_earnings <> 0)) then
2671
2672 hr_utility.trace('Jurisdiction is ** ' || l_jurisdiction);
2673 earning_exists := 1;
2674 ff_archive_api.create_archive_item(
2675 p_archive_item_id => l_archive_item_id
2676 ,p_user_entity_id => get_user_entity_id('CAEOY_PROVINCE_OF_EMPLOYMENT')
2677 ,p_archive_value => l_jurisdiction
2678 ,p_archive_type => 'AAP'
2679 ,p_action_id => p_assactid
2680 ,p_legislation_code => 'CA'
2681 ,p_object_version_number => l_object_version_number
2682 ,p_context_name1 => 'JURISDICTION_CODE'
2683 ,p_context1 => l_jurisdiction
2684 ,p_context_name2 => 'TAX_UNIT_ID'
2685 ,p_context2 => l_tax_unit_id
2686 ,p_some_warning => l_some_warning
2687 );
2688
2689 for i in 1 .. l_count
2690 loop
2691 result := pay_ca_balance_pkg.call_ca_balance_get_value
2692 ( l_balance_type_tab(i),
2693 'YTD' ,
2694 l_aaid,
2695 l_asgid ,
2696 NULL,
2697 'PER' ,
2698 l_tax_unit_id,
2699 l_business_group_id,
2700 l_jurisdiction
2701 ) ;
2702
2703 if result is null then
2704 result := 0;
2705 end if;
2706
2707 if l_user_entity_name_tab(i) = 'CAEOY_CPP_EE_WITHHELD_PER_JD_GRE_YTD' then
2708 l_cpp_ee_withheld_pjgy := result;
2709 elsif
2710 l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_WITHHELD_PER_JD_GRE_YTD' then
2711 l_qpp_ee_withheld_pjgy := result;
2712 elsif
2713 l_user_entity_name_tab(i) = 'CAEOY_EI_EE_WITHHELD_PER_JD_GRE_YTD' then
2714 l_ei_ee_withheld_pjgy := result;
2715 elsif
2716 l_user_entity_name_tab(i) = 'CAEOY_T4_BOX52_PER_JD_GRE_YTD' then
2717 result := round(result);
2718 hr_utility.trace('box_52 Result = ' || to_char(result));
2719 if result > 0 then
2720 box_52_exists := 'Y';
2721 hr_utility.trace('box_52_exists');
2722 end if;
2723 /* bug#3422384 Box26, Box24 */
2724 elsif l_user_entity_name_tab(i) = 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD' then
2725 ln_cpp_ee_taxable_pjgy := result;
2726 elsif l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD' then
2727 ln_qpp_ee_taxable_pjgy := result;
2728 elsif l_user_entity_name_tab(i) = 'CAEOY_EI_EE_TAXABLE_PER_JD_GRE_YTD' then
2729 ln_ei_ee_taxable_pjgy := result;
2730 end if;
2731
2732 /* Added by ssmukher for PPIP Implementaton */
2733 if l_jurisdiction = 'QC' then
2734 if l_user_entity_name_tab(i) = 'CAEOY_PPIP_EE_TAXABLE_PER_JD_GRE_YTD' then
2735 ln_ppip_ee_taxable_pjgy := result;
2736 elsif
2737 l_user_entity_name_tab(i) = 'CAEOY_PPIP_EE_WITHHELD_PER_JD_GRE_YTD' then
2738 l_ppip_ee_withheld_pjgy := result;
2739 end if;
2740 end if;
2741
2742 if l_jurisdiction = 'QC' and
2743 l_balance_type_tab(i) = 'Gross Earnings' then
2744
2745 hr_utility.trace('Calculating QC only taxable benefit');
2746 hr_utility.trace('l_aaid ' || to_char(l_aaid));
2747 hr_utility.trace('l_asgid ' || to_char(l_asgid));
2748 hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
2749 hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
2750 hr_utility.trace('l_jurisdiction ' || l_jurisdiction);
2751
2752 qc_result := pay_ca_balance_pkg.call_ca_balance_get_value
2753 ( 'Taxable Benefits for Quebec',
2754 'YTD' ,
2755 l_aaid,
2756 l_asgid ,
2757 NULL,
2758 'PER' ,
2759 l_tax_unit_id,
2760 l_business_group_id,
2761 l_jurisdiction
2762 ) ;
2763
2764 hr_utility.trace('qc_result is' || to_char(qc_result));
2765
2766 if qc_result is null then
2767 qc_result := 0;
2768 end if;
2769
2770 result := result - qc_result;
2771
2772 end if;
2773
2774 if l_balance_type_tab(i) = 'Gross Earnings' then
2775
2776 other_info_amount31 := pay_ca_balance_pkg.call_ca_balance_get_value
2777 ( 'T4_OTHER_INFO_AMOUNT31',
2778 'YTD' ,
2779 l_aaid,
2780 l_asgid ,
2781 NULL,
2782 'PER' ,
2783 l_tax_unit_id,
2784 l_business_group_id,
2785 l_jurisdiction
2786 ) ;
2787
2788 other_info_amount53 := pay_ca_balance_pkg.call_ca_balance_get_value
2789 ( 'T4_OTHER_INFO_AMOUNT53',
2790 'YTD' ,
2791 l_aaid,
2792 l_asgid ,
2793 NULL,
2794 'PER' ,
2795 l_tax_unit_id,
2796 l_business_group_id,
2797 l_jurisdiction
2798 ) ;
2799
2800 /* Added by ssmukher for Bug 4547415 */
2801
2802 other_info_amount78 := pay_ca_balance_pkg.call_ca_balance_get_value
2803 ( 'T4_OTHER_INFO_AMOUNT78',
2804 'YTD' ,
2805 l_aaid,
2806 l_asgid ,
2807 NULL,
2808 'PER' ,
2809 l_tax_unit_id,
2810 l_business_group_id,
2811 l_jurisdiction
2812 ) ;
2813
2814
2815 other_info_amount71 := pay_ca_balance_pkg.call_ca_balance_get_value
2816 ( 'T4_OTHER_INFO_AMOUNT71',
2817 'YTD' ,
2818 l_aaid,
2819 l_asgid ,
2820 NULL,
2821 'PER' ,
2822 l_tax_unit_id,
2823 l_business_group_id,
2824 l_jurisdiction
2825 ) ;
2826
2827 -- code started for bug 5698016
2828
2829
2830 other_info_amount81 := pay_ca_balance_pkg.call_ca_balance_get_value
2831 ( 'T4_OTHER_INFO_AMOUNT81',
2832 'YTD' ,
2833 l_aaid,
2834 l_asgid ,
2835 NULL,
2836 'PER' ,
2837 l_tax_unit_id,
2838 l_business_group_id,
2839 l_jurisdiction
2840 ) ;
2841
2842 other_info_amount82 := pay_ca_balance_pkg.call_ca_balance_get_value
2843 ( 'T4_OTHER_INFO_AMOUNT82',
2844 'YTD' ,
2845 l_aaid,
2846 l_asgid ,
2847 NULL,
2848 'PER' ,
2849 l_tax_unit_id,
2850 l_business_group_id,
2851 l_jurisdiction
2852 ) ;
2853
2854 other_info_amount83 := pay_ca_balance_pkg.call_ca_balance_get_value
2855 ( 'T4_OTHER_INFO_AMOUNT83',
2856 'YTD' ,
2857 l_aaid,
2858 l_asgid ,
2859 NULL,
2860 'PER' ,
2861 l_tax_unit_id,
2862 l_business_group_id,
2863 l_jurisdiction
2864 ) ;
2865
2866 /*
2867 other_info_amount84 := pay_ca_balance_pkg.call_ca_balance_get_value
2868 ( 'T4_OTHER_INFO_AMOUNT84',
2869 'YTD' ,
2870 l_aaid,
2871 l_asgid ,
2872 NULL,
2873 'PER' ,
2874 l_tax_unit_id,
2875 l_business_group_id,
2876 l_jurisdiction
2877 ) ;
2878
2879 other_info_amount85 := pay_ca_balance_pkg.call_ca_balance_get_value
2880 ( 'T4_OTHER_INFO_AMOUNT85',
2881 'YTD' ,
2882 l_aaid,
2883 l_asgid ,
2884 NULL,
2885 'PER' ,
2886 l_tax_unit_id,
2887 l_business_group_id,
2888 l_jurisdiction
2889 ) ;
2890 */
2891 -- code ended for bug 5698016
2892
2893
2894
2895 result := result - (NVL(other_info_amount31,0)
2896 + NVL(other_info_amount53,0)
2897 + NVL(other_info_amount78,0)
2898 + NVL(other_info_amount71,0)
2899
2900 -- code started for bug 5698016
2901 + NVL(other_info_amount81,0)
2902 + NVL(other_info_amount82,0)
2903 + NVL(other_info_amount83,0)
2904 /*
2905 + NVL(other_info_amount84,0)
2906 + NVL(other_info_amount85,0)
2907 */
2908 -- code ended for bug 5698016
2909
2910 + ln_non_taxable_earnings);
2911
2912 hr_utility.trace(' Gross Earnings = ' || to_char(result));
2913
2914 /* Added for Bug 4028693 */
2915 open c_get_employment_code(to_char(l_tax_unit_id),
2916 to_number(lv_serial_number));
2917
2918 loop
2919 fetch c_get_employment_code
2920 into lv_empcode_prov,
2921 lv_employment_code;
2922 exit when c_get_employment_code%NOTFOUND;
2923
2924 if lv_employment_code is not null and
2925 lv_employment_code in ('11','12','13','17') then
2926
2927 l_box14_flag := 'Y';
2928 Exit;
2929 else
2930 l_box14_flag := 'N';
2931 end if;
2932
2933 end loop;
2934
2935 close c_get_employment_code;
2936
2937 lv_empcode_prov := null;
2938 lv_employment_code := null;
2939
2940 if l_box14_flag = 'Y' then
2941 result := 0;
2942 end if;
2943 /* end of changes for bug 4028693 */
2944
2945 end if;
2946
2947 If (l_status_indian = 'Y' AND
2948 (l_balance_type_tab(i) IN
2949 ('T4_BOX20','T4_BOX44'))) then
2950 result := 0;
2951 end if;
2952
2953 ff_archive_api.create_archive_item(
2954 p_archive_item_id => l_archive_item_id
2955 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
2956 ,p_archive_value => result
2957 ,p_archive_type => 'AAP'
2958 ,p_action_id => p_assactid
2959 ,p_legislation_code => 'CA'
2960 ,p_object_version_number => l_object_version_number
2961 ,p_context_name1 => 'JURISDICTION_CODE'
2962 ,p_context1 => l_jurisdiction
2963 ,p_context_name2 => 'TAX_UNIT_ID'
2964 ,p_context2 => l_tax_unit_id
2965 ,p_some_warning => l_some_warning
2966 );
2967
2968 /* Negative balance flag */
2969 /* Modified by ssmukher for Bug 4547415 */
2970 if result < 0 or other_info_amount31 < 0 or other_info_amount53 < 0
2971 or other_info_amount78 < 0 then
2972
2973 l_negative_balance_exists := 'Y';
2974
2975 end if;
2976
2977 end loop;
2978
2979 hr_utility.trace(' Archiver Asg Act Id = ' || to_char(p_assactid));
2980 hr_utility.trace(' Negative Balance Exists Flag = ' || l_negative_balance_exists);
2981
2982 /* Archiving the Negative Balance Exists Flag Bug#3289072 */
2983 if l_negative_balance_exists = 'Y' then
2984 l_user_entity_id :=
2985 get_user_entity_id('CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
2986
2987 ff_archive_api.create_archive_item(
2988 p_archive_item_id => l_archive_item_id
2989 ,p_user_entity_id => l_user_entity_id
2990 ,p_archive_value => l_negative_balance_exists
2991 ,p_archive_type => 'AAP'
2992 ,p_action_id => p_assactid
2993 ,p_legislation_code => 'CA'
2994 ,p_object_version_number => l_object_version_number
2995 ,p_context_name1 => 'JURISDICTION_CODE'
2996 ,p_context1 => l_jurisdiction
2997 ,p_context_name2 => 'TAX_UNIT_ID'
2998 ,p_context2 => l_tax_unit_id
2999 ,p_some_warning => l_some_warning);
3000
3001 else
3002 l_user_entity_id :=
3003 get_user_entity_id('CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
3004
3005 ff_archive_api.create_archive_item(
3006 p_archive_item_id => l_archive_item_id
3007 ,p_user_entity_id => l_user_entity_id
3008 ,p_archive_value => 'N'
3009 ,p_archive_type => 'AAP'
3010 ,p_action_id => p_assactid
3011 ,p_legislation_code => 'CA'
3012 ,p_object_version_number => l_object_version_number
3013 ,p_context_name1 => 'JURISDICTION_CODE'
3014 ,p_context1 => l_jurisdiction
3015 ,p_context_name2 => 'TAX_UNIT_ID'
3016 ,p_context2 => l_tax_unit_id
3017 ,p_some_warning => l_some_warning);
3018
3019 end if;
3020
3021 /* The following other_info_amounts are archived outside
3022 the main loop, otherwise the balance call would have been
3023 twice for each of them as they needed to be subtracted
3024 from the Gross Earnings */
3025 /* Modified by ssmukher for bug 4034155 */
3026 for i in 1..3 loop
3027
3028 if i = 1 then
3029 l_user_entity_id :=
3030 get_user_entity_id('CAEOY_T4_OTHER_INFO_AMOUNT31_PER_JD_GRE_YTD');
3031 result := other_info_amount31;
3032 elsif i = 2 then
3033 l_user_entity_id :=
3034 get_user_entity_id('CAEOY_T4_OTHER_INFO_AMOUNT53_PER_JD_GRE_YTD');
3035 result := other_info_amount53 ;
3036 /* Added by ssmukher for bug 4547415 */
3037 elsif i = 3 then
3038 l_user_entity_id :=
3039 get_user_entity_id('CAEOY_T4_OTHER_INFO_AMOUNT78_PER_JD_GRE_YTD');
3040 result := other_info_amount78 ;
3041 end if;
3042
3043 ff_archive_api.create_archive_item(
3044 p_archive_item_id => l_archive_item_id
3045 ,p_user_entity_id => l_user_entity_id
3046 ,p_archive_value => result
3047 ,p_archive_type => 'AAP'
3048 ,p_action_id => p_assactid
3049 ,p_legislation_code => 'CA'
3050 ,p_object_version_number => l_object_version_number
3051 ,p_context_name1 => 'JURISDICTION_CODE'
3052 ,p_context1 => l_jurisdiction
3053 ,p_context_name2 => 'TAX_UNIT_ID'
3054 ,p_context2 => l_tax_unit_id
3055 ,p_some_warning => l_some_warning);
3056
3057 end loop; -- End loop other information archived after Amount
3058 -- is subtracted from Gross Earnings.
3059
3060
3061 l_negative_balance_exists := 'N';
3062
3063 /* Archiving Exempt flags separately */
3064 hr_utility.trace('I am archiving CPP-QPP exempt flags for ' ||
3065 to_char(l_asgid));
3066 hr_utility.trace('effective date is ' || to_char(p_effective_date));
3067
3068 /* Bug#3422384, checking whether the employee age is under 18 or over 70 */
3069 open c_get_date_of_birth(to_number(lv_serial_number),p_effective_date);
3070 fetch c_get_date_of_birth into ld_date_of_birth;
3071 close c_get_date_of_birth;
3072
3073 if ld_date_of_birth is NULL then
3074 hr_utility.trace('Employee date of birth is NULL satisfied ');
3075 lv_over70_flag := 'N';
3076 lv_under18_flag := 'N';
3077 else
3078 hr_utility.trace('Employee date of birth found ');
3079
3080 /* Backing out the changes made in 115.60 */
3081 /* if (( add_months(trunc(ld_date_of_birth,'MONTH'),(18*12)+1)) <= lv_actual_date ) then
3082 if ( lv_actual_date >= ( add_months(trunc(ld_date_of_birth,'MONTH'),(70*12)+1))) then */
3083 if ((add_months(trunc(ld_date_of_birth,'MONTH'),(18*12)+1)) <= p_effective_date) then
3084 if (p_effective_date >= (add_months(trunc(ld_date_of_birth,'MONTH'),(70*12)+1))) then
3085 lv_over70_flag := 'Y';
3086 else
3087 lv_over70_flag := 'N';
3088 end if;
3089 else
3090 lv_under18_flag := 'Y';
3091
3092 end if;
3093 end if;
3094
3095 BEGIN
3096 /* Removed the per_all_assignments_f join the select stmt for bug fix 5041252 */
3097 SELECT decode(target.CPP_QPP_EXEMPT_FLAG,'Y','X',NULL),
3098 decode(target.EI_EXEMPT_FLAG,'Y','X',NULL)
3099 INTO l_cpp_exempt_flag,
3100 l_ei_exempt_flag
3101 FROM pay_ca_emp_fed_tax_info_f target
3102 WHERE target.assignment_id = l_asgid
3103 and lv_actual_date/*p_effective_date*/ between target.effective_start_date
3104 and target.effective_end_date;
3105
3106 exception when no_data_found then
3107 l_cpp_exempt_flag := NULL;
3108 l_ei_exempt_flag := null;
3109 end;
3110
3111 /* Added extra validation to fix bug#3422384. For CPP
3112 1. If employee age is under 18 or over 70
3113 and Box16,Box26 = 0 then cpp_exempt_flag ='X'
3114 2. If cpp_exempt_flag in tax_information form is 'Y' and
3115 and Box16,Box26 = 0 then cpp_exempt_flag ='X'
3116 3. If employee age turned into 18 or over 70 mid year
3117 and Box16,Box26 > 0 and cpp_exempt_flag is 'Y' in
3118 tax form then cpp_exempt_flag = ?
3119 */
3120
3121 IF l_jurisdiction <> 'QC' THEN
3122
3123 IF (lv_under18_flag = 'Y' or lv_over70_flag = 'Y') and
3124 (l_cpp_ee_withheld_pjgy = 0) and (ln_cpp_ee_taxable_pjgy = 0) THEN
3125
3126 lv_cpp_archive_exempt_flag := 'X';
3127 Elsif (l_cpp_exempt_flag = 'X') and (l_cpp_ee_withheld_pjgy = 0)
3128 and (ln_cpp_ee_taxable_pjgy = 0) THEN
3129
3130 lv_cpp_archive_exempt_flag := 'X';
3131 END IF;
3132
3133 END IF;
3134
3135
3136 IF l_jurisdiction = 'QC' THEN
3137
3138 BEGIN
3139 SELECT decode(target.QPP_EXEMPT_FLAG,'Y','X',NULL),
3140 decode(target.PPIP_EXEMPT_FLAG,'Y','X',NULL)
3141 INTO lv_qpp_exempt_flag,
3142 l_ppip_exempt_flag
3143 FROM pay_ca_emp_prov_tax_info_f target
3144 WHERE target.assignment_id = l_asgid
3145 and target.province_code = 'QC'
3146 and lv_actual_date/*p_effective_date */ between target.effective_start_date
3147 and target.effective_end_date;
3148 EXCEPTION
3149 WHEN no_data_found THEN
3150 lv_qpp_exempt_flag := NULL;
3151 l_ppip_exempt_flag := NULL;
3152 END;
3153
3154 /* Added extra validation to fix bug#3422384. For QPP
3155 1. If employee is under 18
3156 and Box17,Box26 = 0 then qpp_exempt_flag= 'X'
3157 2. If qpp_exempt_flag in tax_information form is 'Y' and
3158 and Box17,Box26 = 0 then qpp_exempt_flag= 'X'
3159 3. If employee age turned into 18 mid year
3160 and Box17,Box26 > 0 and qpp_exempt_flag is 'Y' in
3161 tax form then qpp_exempt_flag = ?
3162 */
3163 IF (lv_under18_flag = 'Y') and (l_qpp_ee_withheld_pjgy = 0)
3164 and (ln_qpp_ee_taxable_pjgy = 0) THEN
3165 lv_cpp_archive_exempt_flag := 'X';
3166 Elsif (lv_qpp_exempt_flag = 'X') and (l_qpp_ee_withheld_pjgy = 0)
3167 and (ln_qpp_ee_taxable_pjgy = 0) THEN
3168 lv_cpp_archive_exempt_flag := 'X';
3169 END IF;
3170
3171 /* Added by ssmukher for PPIP Implementation */
3172 IF (l_ppip_exempt_flag = 'X' and l_ppip_ee_withheld_pjgy = 0
3173 and ln_ppip_ee_taxable_pjgy = 0) THEN
3174
3175 lv_ppip_archive_exempt_flag := 'X';
3176 END IF;
3177
3178 ff_archive_api.create_archive_item(
3179 p_archive_item_id => l_archive_item_id
3180 ,p_user_entity_id => get_user_entity_id('CAEOY_PPIP_EXEMPT')
3181 ,p_archive_value => lv_ppip_archive_exempt_flag
3182 ,p_archive_type => 'AAP'
3183 ,p_action_id => p_assactid
3184 ,p_legislation_code => 'CA'
3185 ,p_object_version_number => l_object_version_number
3186 ,p_context_name1 => 'JURISDICTION_CODE'
3187 ,p_context1 => l_jurisdiction
3188 ,p_context_name2 => 'TAX_UNIT_ID'
3189 ,p_context2 => l_tax_unit_id
3190 ,p_some_warning => l_some_warning
3191 );
3192
3193 END IF;
3194
3195
3196 /* changed to archive lv_cpp_archive_exempt_flag instead of l_cpp_exempt_flag
3197 to fix bug#3422384 */
3198 ff_archive_api.create_archive_item(
3199 p_archive_item_id => l_archive_item_id
3200 ,p_user_entity_id => get_user_entity_id('CAEOY_CPP_QPP_EXEMPT')
3201 ,p_archive_value => lv_cpp_archive_exempt_flag
3202 ,p_archive_type => 'AAP'
3203 ,p_action_id => p_assactid
3204 ,p_legislation_code => 'CA'
3205 ,p_object_version_number => l_object_version_number
3206 ,p_context_name1 => 'JURISDICTION_CODE'
3207 ,p_context1 => l_jurisdiction
3208 ,p_context_name2 => 'TAX_UNIT_ID'
3209 ,p_context2 => l_tax_unit_id
3210 ,p_some_warning => l_some_warning
3211 );
3212
3213 hr_utility.trace('I am archiving EI exempt flag');
3214
3215 /* Added extra validation to fix bug#3422384. For EI
3216 1. If ei_exempt_flag in tax_information form is 'Y' and
3217 and Box18,Box24 = 0 then ei_exempt_flag= 'X'
3218 */
3219
3220 IF (l_ei_exempt_flag = 'X' and l_ei_ee_withheld_pjgy = 0
3221 and ln_ei_ee_taxable_pjgy = 0) THEN
3222
3223 lv_ei_archive_exempt_flag := 'X';
3224
3225 END IF;
3226
3227 hr_utility.trace('assignment id ' || to_char(l_asgid) || '**');
3228 hr_utility.trace('cpp exempt flag is ' || lv_cpp_archive_exempt_flag || '**');
3229 hr_utility.trace('ei exempt flag is ' || lv_ei_archive_exempt_flag || '**');
3230
3231 ff_archive_api.create_archive_item(
3232 p_archive_item_id => l_archive_item_id
3233 ,p_user_entity_id => get_user_entity_id('CAEOY_EI_EXEMPT')
3234 ,p_archive_value => lv_ei_archive_exempt_flag
3235 ,p_archive_type => 'AAP'
3236 ,p_action_id => p_assactid
3237 ,p_legislation_code => 'CA'
3238 ,p_object_version_number => l_object_version_number
3239 ,p_context_name1 => 'JURISDICTION_CODE'
3240 ,p_context1 => l_jurisdiction
3241 ,p_context_name2 => 'TAX_UNIT_ID'
3242 ,p_context2 => l_tax_unit_id
3243 ,p_some_warning => l_some_warning
3244 );
3245
3246 hr_utility.trace('I have archived exempt flags');
3247
3248
3249 /* Archiving T4 Employment Code */
3250 open c_get_employment_code(to_char(l_tax_unit_id),
3251 to_number(lv_serial_number));
3252 loop -- c_get_emp_code
3253 fetch c_get_employment_code into lv_empcode_prov,
3254 lv_employment_code;
3255 exit when c_get_employment_code%NOTFOUND;
3256
3257 if lv_empcode_prov is not null and
3258 lv_empcode_prov = l_jurisdiction then
3259
3260 ff_archive_api.create_archive_item(
3261 p_archive_item_id => l_archive_item_id
3262 ,p_user_entity_id => get_user_entity_id('CAEOY_EMPLOYMENT_CODE')
3263 ,p_archive_value => lv_employment_code
3264 ,p_archive_type => 'AAP'
3265 ,p_action_id => p_assactid
3266 ,p_legislation_code => 'CA'
3267 ,p_object_version_number => l_object_version_number
3268 ,p_context_name1 => 'JURISDICTION_CODE'
3269 ,p_context1 => l_jurisdiction
3270 ,p_context_name2 => 'TAX_UNIT_ID'
3271 ,p_context2 => l_tax_unit_id
3272 ,p_some_warning => l_some_warning
3273 );
3274
3275 hr_utility.trace('Archived Employment code single prov');
3276
3277 else
3278
3279 ff_archive_api.create_archive_item(
3280 p_archive_item_id => l_archive_item_id
3281 ,p_user_entity_id => get_user_entity_id('CAEOY_EMPLOYMENT_CODE')
3282 ,p_archive_value => lv_employment_code
3283 ,p_archive_type => 'AAP'
3284 ,p_action_id => p_assactid
3285 ,p_legislation_code => 'CA'
3286 ,p_object_version_number => l_object_version_number
3287 ,p_context_name1 => 'JURISDICTION_CODE'
3288 ,p_context1 => l_jurisdiction
3289 ,p_context_name2 => 'TAX_UNIT_ID'
3290 ,p_context2 => l_tax_unit_id
3291 ,p_some_warning => l_some_warning
3292 );
3293
3294 hr_utility.trace('Archived employment code all prov');
3295
3296 end if;
3297
3298 end loop; -- c_get_employment_code
3299
3300 close c_get_employment_code;
3301 /* End of Employement Code archiving */
3302
3303 end if;
3304
3305 end loop;
3306
3307 /* start registration number archiving */
3308
3309 l_registration_no := NULL;
3310 old_l_value := 0;
3311 l_value := 0;
3312
3313 --hr_utility.trace_on('Y','ORACLE');
3314
3315 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3316 hr_utility.trace('l_asgid is ' || to_char(l_asgid));
3317 hr_utility.trace('l_tax_unit_id is ' || to_char(l_tax_unit_id));
3318 hr_utility.trace('l_business group_id is ' || to_char(l_business_group_id));
3319
3320 begin
3321
3322
3323 /* if box_52_exists = 'Y' then
3324 l_balance_name1 := 'T4_BOX52';
3325 box_52_exists := 'N';
3326 --else -- commented by sneelapa for bug 6399498
3327 elsif nvl(l_status_indian,'N') = 'N' then
3328 l_balance_name1 := 'T4_BOX20';
3329 end if;
3330 */
3331 l_balance_name1 := 'T4_BOX52';
3332 box_52_exists := 'N';
3333
3334 hr_utility.trace('BOX name is ' || l_balance_name1);
3335 hr_utility.trace('l_asgid ' || l_asgid);
3336 hr_utility.trace('l_business_group_id ' || l_business_group_id);
3337 hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3338
3339 open c_balance_feed_info(l_balance_name1);
3340 fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3341
3342 hr_utility.trace('CURSOR count ' || c_balance_feed_info%rowcount);
3343
3344 if c_balance_feed_info%rowcount = 0 and nvl(l_status_indian,'N') = 'N' then
3345 l_balance_name1 := 'T4_BOX20';
3346 if c_balance_feed_info%isopen then
3347 close c_balance_feed_info;
3348 end if;
3349 end if;
3350
3351 hr_utility.trace('BOX name is ' || l_balance_name1);
3352 if not c_balance_feed_info%isopen then
3353 open c_balance_feed_info(l_balance_name1);
3354 fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3355 hr_utility.trace('CURSOR count BOX20 is ' || c_balance_feed_info%rowcount);
3356 end if;
3357
3358 loop
3359 hr_utility.trace('start of c_balance_feed_info CURSOR');
3360 exit when c_balance_feed_info%NOTFOUND;
3361
3362 -- exception handling added by sneelapa for bug 6399498
3363 -- screen_entry_value will be NON NUMERIC data for certain Element Entry Values
3364 -- For example: Jurisdistiction.
3365
3366 begin
3367
3368 -- if condition added by sneelapa for bug 6399498
3369 if fnd_number.canonical_to_number(l_screen_entry_value) >= 0 then
3370
3371 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
3372 ( l_balance_name,
3373 'YTD' ,
3374 l_aaid,
3375 l_asgid,
3376 NULL,
3377 'PER' ,
3378 l_tax_unit_id,
3379 l_business_group_id,
3380 NULL );
3381
3382 if l_value is null then
3383 l_value := 0;
3384 end if;
3385
3386 hr_utility.trace('l_value is ' || to_char(l_value));
3387 hr_utility.trace('old_l_value is ' || old_l_value);
3388 hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
3389 hr_utility.trace('l_registration_no is ' || l_registration_no);
3390
3391 if l_registration_no <> 'NOT FOUND' then
3392 if old_l_registration_no is null and l_value <> 0 then
3393 old_l_registration_no := l_registration_no;
3394 end if;
3395
3396 -- modified for bug 6399498
3397 -- if old_l_value >= l_value then
3398 if old_l_value > l_value then
3399 l_registration_no := old_l_registration_no;
3400 -- modified for bug 6399498
3401 -- elsif old_l_value < l_value then
3402 elsif old_l_value <= l_value then
3403 old_l_value := l_value;
3404 old_l_registration_no := l_registration_no;
3405 end if;
3406 end if;
3407
3408 end if; -- if fnd_number.canonical_to_number(pev.screen_entry_value) >= 0 then
3409
3410 exception
3411 when others then
3412 null;
3413 end;
3414
3415 fetch c_balance_feed_info into l_registration_no,l_balance_name, l_screen_entry_value;
3416 end loop;
3417
3418 close c_balance_feed_info;
3419
3420 hr_utility.trace('old_l_value is ' || to_char(old_l_value));
3421 hr_utility.trace('l_registration no is ' || l_registration_no);
3422
3423 -- modified for bug 6399498, for QA reported issue in this bug.
3424 -- if l_registration_no is not null and old_l_value <> 0 then
3425 if l_registration_no is not null then
3426
3427 hr_utility.trace('l_registration no is ' || 'archiving');
3428 -- hr_utility.trace_off;
3429
3430 ff_archive_api.create_archive_item(
3431 p_archive_item_id => l_archive_item_id
3432 ,p_user_entity_id =>
3433 get_user_entity_id('CAEOY_T4_EMPLOYEE_REGISTRATION_NO')
3434 ,p_archive_value => l_registration_no
3435 ,p_archive_type => 'AAP'
3436 ,p_action_id => p_assactid
3437 ,p_legislation_code => 'CA'
3438 ,p_object_version_number => l_object_version_number
3439 ,p_some_warning => l_some_warning
3440 );
3441 end if;
3442 end;
3443
3444 /* end registration number archiving */
3445
3446 begin
3447 l_counter := 0;
3448 hr_utility.trace('selecting people');
3449
3450 open get_person_info(l_asgid);
3451
3452 fetch get_person_info
3453 into
3454 l_person_id,
3455 l_first_name,
3456 l_last_name,
3457 l_employee_number,
3458 l_national_identifier,
3459 l_middle_names,
3460 l_organization_id,
3461 l_location_id;
3462
3463 l_person_arch_step := 1;
3464 /* Validations for magtape and exception report */
3465
3466 /* SIN validation */
3467 if l_national_identifier is not null then
3468 select formula_id,
3469 effective_start_date
3470 into l_formula_id,
3471 l_effective_start_date
3472 from ff_formulas_f
3473 where formula_name='NI_VALIDATION'
3474 and business_group_id is null
3475 and legislation_code='CA'
3476 and sysdate between effective_start_date and effective_end_date;
3477
3478 ff_exec.init_formula(l_formula_id,l_effective_start_date,l_inputs,l_outputs);
3479 for l_in_cnt in
3480 l_inputs.first..l_inputs.last
3481 loop
3482 if l_inputs(l_in_cnt).name='NATIONAL_IDENTIFIER' then
3483 l_inputs(l_in_cnt).value := l_national_identifier;
3484 end if;
3485 end loop;
3486 ff_exec.run_formula(l_inputs,l_outputs);
3487
3488 for l_out_cnt in
3489 l_outputs.first..l_outputs.last
3490 loop
3491 hr_utility.trace('inside loop for SIN validation');
3492 if l_outputs(l_out_cnt).name='RETURN_VALUE' then
3493 l_return_value := l_outputs(l_out_cnt).value;
3494 end if;
3495 if l_outputs(l_out_cnt).name='INVALID_MESG' then
3496 l_invalid_mesg := l_outputs(l_out_cnt).value;
3497 end if;
3498 end loop;
3499
3500 if l_return_value = 'INVALID_ID' then
3501 l_invalid_sin := 'Y';
3502 else
3503 l_invalid_sin := 'N';
3504 end if;
3505 else
3506 l_invalid_sin := 'A';
3507 end if;
3508
3509 l_person_arch_step := 2;
3510 hr_utility.trace('selected people');
3511 /* Initialise l_count */
3512 l_count := 0;
3513
3514 --hr_utility.trace_on('Y','ORACLE');
3515
3516 l_counter := l_counter + 1;
3517 l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
3518 l_user_entity_value_tab(l_counter) := l_person_id;
3519
3520 l_counter := l_counter + 1;
3521 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
3522 l_user_entity_value_tab(l_counter) := l_first_name;
3523
3524 l_counter := l_counter + 1;
3525 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
3526 l_user_entity_value_tab(l_counter) := l_last_name;
3527
3528 l_counter := l_counter + 1;
3529 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
3530 l_user_entity_value_tab(l_counter) := l_middle_names;
3531
3532 l_counter := l_counter + 1;
3533 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
3534 l_user_entity_value_tab(l_counter) := l_national_identifier;
3535
3536 l_counter := l_counter + 1;
3537 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN_INVALID';
3538 l_user_entity_value_tab(l_counter) := l_invalid_sin;
3539
3540 l_counter := l_counter + 1;
3541 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
3542 l_user_entity_value_tab(l_counter) := l_employee_number;
3543
3544 l_counter := l_counter + 1;
3545 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_ORGANIZATION_ID';
3546 l_user_entity_value_tab(l_counter) := l_organization_id;
3547
3548 l_counter := l_counter + 1;
3549 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_LOCATION_ID';
3550 l_user_entity_value_tab(l_counter) := l_location_id;
3551
3552 if earning_exists = 1 then
3553 for i in 1..l_counter loop
3554
3555 l_context_id := l_taxunit_context_id;
3556 l_context_val := l_tax_unit_id;
3557
3558 ff_archive_api.create_archive_item(
3559 -- p_validate => 'TRUE'
3560 p_archive_item_id => l_archive_item_id
3561 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
3562 ,p_archive_value => l_user_entity_value_tab(i)
3563 ,p_archive_type => 'AAP'
3564 ,p_action_id => p_assactid
3565 ,p_legislation_code => 'CA'
3566 ,p_object_version_number => l_object_version_number
3567 ,p_some_warning => l_some_warning
3568 );
3569 end loop;
3570 end if;
3571 exception when no_data_found then
3572 l_first_name := null;
3573 l_last_name := null;
3574 l_employee_number := null;
3575 l_national_identifier := null;
3576 l_middle_names := null;
3577 l_employee_phone_no := null;
3578 hr_utility.raise_error;
3579 when others then
3580 hr_utility.trace('Error in archiving person '||
3581 to_char(l_person_id) || 'at step :' ||
3582 to_char(l_person_arch_step) ||
3583 'sqlcode : ' || to_char(sqlcode));
3584 end;
3585
3586 addr := pay_ca_rl1_reg.get_primary_address(l_person_id,p_effective_date);
3587
3588 l_address_line1 := addr.addr_line_1;
3589 l_address_line2 := addr.addr_line_2;
3590 l_address_line3 := addr.addr_line_3;
3591 l_town_or_city := addr.city;
3592 l_province_code := addr.province;
3593 l_postal_code := replace(addr.postal_code,' ');
3594 l_country_code := addr.addr_line_5;
3595
3596 hr_utility.trace('selected address');
3597
3598 l_counter := 0;
3599 l_counter := l_counter + 1;
3600 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
3601 l_user_entity_value_tab(l_counter) := l_address_line1;
3602
3603 l_counter := l_counter + 1;
3604 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE2';
3605 l_user_entity_value_tab(l_counter) := l_address_line2;
3606
3607 l_counter := l_counter + 1;
3608 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE3';
3609 l_user_entity_value_tab(l_counter) := l_address_line3;
3610
3611 l_counter := l_counter + 1;
3612 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE4';
3613 l_user_entity_value_tab(l_counter) := l_address_line4;
3614
3615 l_counter := l_counter + 1;
3616 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_CITY';
3617 l_user_entity_value_tab(l_counter) := l_town_or_city;
3618
3619 l_counter := l_counter + 1;
3620 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_PROVINCE';
3621 l_user_entity_value_tab(l_counter) := l_province_code;
3622
3623 l_counter := l_counter + 1;
3624 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_COUNTRY';
3625 l_user_entity_value_tab(l_counter) := l_country_code;
3626
3627 l_counter := l_counter + 1;
3628 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_POSTAL_CODE';
3629 l_user_entity_value_tab(l_counter) := l_postal_code;
3630
3631 /*
3632 l_counter := l_counter + 1;
3633 l_user_entity_value_tab(l_counter) := 'CAEOY_EMPLOYEE_BUSINESS_NUMBER';
3634 l_user_entity_name_tab(l_counter) := 'To be decided';
3635 */
3636 if earning_exists = 1 then
3637 for i in 1..l_counter loop
3638
3639 l_context_id := l_taxunit_context_id;
3640 l_context_val := l_tax_unit_id;
3641
3642 hr_utility.trace('archiving address');
3643 ff_archive_api.create_archive_item(
3644 p_archive_item_id => l_archive_item_id
3645 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
3646 ,p_archive_value => l_user_entity_value_tab(i)
3647 ,p_archive_type => 'AAP'
3648 ,p_action_id => p_assactid
3649 ,p_legislation_code => 'CA'
3650 ,p_object_version_number => l_object_version_number
3651 ,p_some_warning => l_some_warning
3652 );
3653 hr_utility.trace('archived address');
3654 end loop;
3655 end if;
3656 hr_utility.trace('end of eoy_archive_data');
3657 l_step := 37;
3658
3659
3660 -- Federal YE Amendment Pre-Process Validation (T4 Amendmendment Archiver code)
3661
3662 Begin
3663
3664 hr_utility.trace('Started Federal YE Amendment PP Validation ');
3665 select effective_date,report_type
3666 into ld_fapp_effective_date,lv_fapp_report_type
3667 from pay_payroll_actions
3668 where payroll_action_id = l_payroll_action_id;
3669
3670 hr_utility.trace('Fed Amend Pre-Process Pactid :'||
3671 to_char(l_payroll_action_id));
3672 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3673
3674 IF lv_fapp_report_type = 'CAEOY_T4_AMEND_PP' then
3675 begin
3676
3677 open c_get_fapp_locked_action_id(p_assactid);
3678 fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
3679 close c_get_fapp_locked_action_id;
3680
3681 hr_utility.trace('T4 Amend PP Action ID : '||to_char(p_assactid));
3682 hr_utility.trace('ln_fapp_locked_action_id :'||
3683 to_char(ln_fapp_locked_action_id));
3684 open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
3685 fetch c_get_fapp_lkd_actid_rtype
3686 into lv_fapp_locked_actid_reptype;
3687 close c_get_fapp_lkd_actid_rtype;
3688 hr_utility.trace('lv_fapp_locked_actid_reptype :'||
3689 lv_fapp_locked_actid_reptype);
3690
3691 open c_get_fapp_prov_emp(p_assactid);
3692 loop
3693 fetch c_get_fapp_prov_emp into lv_fapp_prov;
3694 exit when c_get_fapp_prov_emp%NOTFOUND;
3695 hr_utility.trace('lv_fapp_prov : '||lv_fapp_prov);
3696 lv_fapp_flag := compare_archive_data(p_assactid,
3697 ln_fapp_locked_action_id,
3698 lv_fapp_prov);
3699
3700 if lv_fapp_flag = 'Y' then
3701
3702 hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
3703 hr_utility.trace('Archiving T4 Amendment Flag is : ' || lv_fapp_flag);
3704
3705 ff_archive_api.create_archive_item(
3706 p_archive_item_id => l_archive_item_id
3707 ,p_user_entity_id => get_user_entity_id('CAEOY_T4_AMENDMENT_FLAG'
3708 )
3709 ,p_archive_value => lv_fapp_flag
3710 ,p_archive_type => 'AAP'
3711 ,p_action_id => p_assactid
3712 ,p_legislation_code => 'CA'
3713 ,p_object_version_number => l_object_version_number
3714 ,p_context_name1 => 'JURISDICTION_CODE'
3715 ,p_context1 => lv_fapp_prov
3716 ,p_context_name2 => 'TAX_UNIT_ID'
3717 ,p_context2 => l_tax_unit_id
3718 ,p_some_warning => l_some_warning
3719 );
3720
3721 end if;
3722
3723 end loop;
3724 close c_get_fapp_prov_emp;
3725
3726 end; -- report_type validation
3727
3728 END IF; -- report type validation for FAPP
3729 hr_utility.trace('End of Federal YE Amendment PP Validation');
3730
3731 Exception when no_data_found then
3732 hr_utility.trace('Report type not found for given Payroll_action ');
3733 null;
3734 End;
3735 -- End of Federal YE Amendment Pre-Process Validation
3736
3737 end eoy_archive_data;
3738
3739
3740 /* Name : eoy_range_cursor
3741 Purpose : This returns the select statement that is used to created the
3742 range rows for the Year End Pre-Process.
3743 Arguments :
3744 Notes :
3745 */
3746
3747 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
3748
3749 l_legislative_parameters varchar2(240);
3750 l_eoy_tax_unit_id number;
3751 l_transmitter_gre_id number;
3752 l_archive boolean:= FALSE;
3753 l_business_group number;
3754 l_year_start date;
3755 l_year_end date;
3756
3757
3758 begin
3759
3760 --hr_utility.trace_on('Y','ORACLE');
3761
3762 select legislative_parameters,
3763 trunc(effective_date,'Y'),
3764 effective_date,
3765 business_group_id
3766 into l_legislative_parameters,
3767 l_year_start,
3768 l_year_end,
3769 l_business_group
3770 from pay_payroll_actions
3771 where payroll_action_id = pactid;
3772
3773 hr_utility.trace('legislative prameter is '|| l_legislative_parameters);
3774 l_eoy_tax_unit_id := get_parameter('TRANSFER_GRE',l_legislative_parameters);
3775
3776 select org_information11
3777 into l_transmitter_gre_id
3778 from hr_organization_information
3779 where organization_id = l_eoy_tax_unit_id
3780 and org_information_context = 'Canada Employer Identification';
3781
3782 hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
3783 hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
3784
3785 if l_eoy_tax_unit_id <> -99999 then
3786
3787 sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
3788 PPA PAY_PAYROLL_ACTIONS_N51,
3789 PAA PAY_ASSIGNMENT_ACTIONS_N50,
3790 ASG PER_ASSIGNMENTS_F_PK,
3791 PPA1 PAY_PAYROLL_ACTIONS_PK)
3792 USE_NL(PPY, PPA, PAA, ASG, PPA1) */
3793 distinct asg.person_id
3794 from pay_all_payrolls_f ppy,
3795 pay_payroll_actions ppa,
3796 pay_assignment_actions paa,
3797 per_all_assignments_f asg,
3798 pay_payroll_actions ppa1
3799 where ppa1.payroll_action_id = :payroll_action_id
3800 and ppa.effective_date between
3801 fnd_date.canonical_to_date('''||
3802 fnd_date.date_to_canonical(l_year_start)||''') and
3803 fnd_date.canonical_to_date('''||
3804 fnd_date.date_to_canonical(l_year_end)||''')
3805 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
3806 and ppa.action_status = ''C''
3807 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
3808 and ppa.payroll_action_id = paa.payroll_action_id
3809 and paa.tax_unit_id = '|| to_char(l_eoy_tax_unit_id)||'
3810 and paa.action_status = ''C''
3811 and paa.assignment_id = asg.assignment_id
3812 and ppa.business_group_id = asg.business_group_id + 0
3813 and ppa.effective_date between asg.effective_start_date
3814 and asg.effective_end_date
3815 and asg.assignment_type = ''E''
3816 and ppa.payroll_id = ppy.payroll_id
3817 and ppy.business_group_id = '||to_char(l_business_group)||'
3818 order by asg.person_id';
3819
3820 l_archive := chk_gre_archive(pactid);
3821
3822 if g_archive_flag = 'N' then
3823
3824 hr_utility.trace('eoy_range_cursor archiving employer data');
3825
3826 /* now the archiver has provision for archiving payroll_action_level
3827 data. So make use of that */
3828
3829 hr_utility.trace('eoy_range_cursor archiving employer data');
3830
3831 eoy_archive_gre_data(pactid,
3832 l_eoy_tax_unit_id,
3833 l_transmitter_gre_id);
3834 end if;
3835
3836 end if;
3837
3838 end eoy_range_cursor;
3839
3840 end pay_ca_eoy_archive;
3841