[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4AEOY_ARCHIVE
Source
1 package body pay_ca_t4aeoy_archive as
2 /*$Header: pycayt4a.pkb 120.8.12000000.2 2007/08/23 08:36:29 ssmukher noship $ */
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 115.0 Created
14 ** 18-AUG-2000 M.Mukherjee 115.3 Added footnote generation and
15 ** registration number
16 ** 28-AUG-2000 M.Mukherjee 115.4 Added box38 footnote code archiving
17 ** 28-AUG-2000 M.Mukherjee 115.7 Changed the footnote archiving so
18 ** that it does not try to archive
19 ** null user_entities
20 ** 21-SEP-2000 M.Mukherjee 115.9 Corrected archiving of registration
21 ** number.
22 ** 25-SEP-2000 M.Mukherjee 115.10 Corrected archiving of footnots
23 ** 27-SEP-2000 M.Mukherjee 115.11 Corrected archiving of registration
24 ** number,technical_contact info and
25 ** accounting contact_info
26 ** 30-OCT-2000 M.Mukherjee 115.13 balance_feeds are being checked for
27 ** that business group id.Bug1482190
28 ** 31-OCT-2000 SSattini 115.15 Corrected the box38 footnote code
29 ** to fix bug:1483943
30 ** 13-DEC-2000 MMukherjee 115.16 Stopped assignment action creation
31 ** if the employeehas not been paid
32 ** anything in that year, even though
33 ** there is payroll run.
34 ** 28-NOV-2001 SSattini 115.17 Corrected the 'Employee Initial'
35 ** to fix bug:1474421 and
36 ** modified employer_info cursor
37 ** to avoid the hr_ca_tax_units_v
38 ** view and improve performance.
39 ** Also added T4A_BOX27 balance
40 ** check to archive the
41 ** CAEOY_T4A_BOX27_14_PER_GRE_YTD
42 ** footnote code 14 based on BOX27.
43 ** 15-MAR-2002 rsirigir 115.18 Bug 2254026, included checkfile
44 ** in order to comply with GSCC
45 ** standards
46 ** 28-MAY-2002 SSattini 115.19 Fixed the bug#2175045 by adding
47 ** cur_non_box_mesg cursor and code
48 ** in procedure eoy_archive_data.
49 ** The T4A Nonbox footnotes are
50 ** archived into
51 ** pay_action_information table.
52 ** 25-JUL-2002 SSattini 115.21 changed the balance name from
53 ** 'T4A_BOX32' to 'T4A_BOX34'
54 ** to open c_reg_balance_feed_info
55 ** cursor for bugfix:2408456.
56 ** Added else part to fulfill reqt
57 ** if Pension Plan Registration
58 ** number is not there for balance
59 ** T4A_BOX34 then check in the
60 ** balance T4A_BOX32 and archive the
61 ** pension adjustment registration
62 ** number. Also added two new cursors
63 ** c_ele_processed_for_emp and
64 ** cur_info_element_amt to check
65 ** elements fed to balances are
66 ** processed for that employee.
67 ** 28-AUG-2002 SSattini 115.22 Fixed the bug#2426517, added
68 ** some validations to avoid un-
69 ** necessary footnote archiving
70 ** also corrected archiving of
71 ** CAEOY_T4A_FOOTNOTE_CODE used
72 ** for T4A_BOX38 in T4A reports.
73 ** Removed c_ele_processed_for_emp
74 ** and cur_info_element_amt cursors
75 ** and removed some validations
76 ** that refer to the cursors,
77 ** because used wrong setup to fix
78 ** bug#2408456 earlier and in this
79 ** version fixed bug#2408456 with
80 ** right test setup.
81 ** 09-SEP-2002 SSattini 115.23 Fixed the bug#2426517, added
82 ** one local variable for single
83 ** footnote archiving, also
84 ** corrected archiving of box38.
85 **
86 ** 13-SEP-2002 SSouresr 115.24 Fixed the bug#2561691, Added
87 ** conditions to the c_get_asg_id
88 ** cursor so that the primary
89 ** assignment is selected.
90 ** 05-NOV-2002 SSattini 115.25 Fixed the bug#2449037, archiving
91 ** CAEOY_T4A_FOOTNOTE_CODE after
92 ** checking t4a nonbox footnotes
93 ** for an employee, so that nonbox
94 ** footnote code is archived and
95 ** displayed in T4A Box38.
96 ** 07-NOV-2002 SSattini 115.26 Fixed the bug#2598802, archiving
97 ** GRE's 'Fed Magnetic Reporting'
98 ** using separate cursor
99 ** c_get_gre_acct_info in
100 ** eoy_archive_gre_data procedure.
101 ** Changed the cursor employer_info
102 ** in eoy_archive_gre_data procedure
103 ** removed the part that archives
104 ** GRE 'Fed Magnetic Reporting'.
105 ** 12-NOV-2002 SSattini 115.27 Removed unnecessary archiving
106 ** of db items with dimension
107 ** _GRE_YTD from eoy_archive_gre_data
108 ** procedure, those db items are
109 ** CAEOY_T4_BOX20_GRE_YTD
110 ** CAEOY_FED_WITHHELD_GRE_YTD
111 ** CAEOY_T4_BOX52_GRE_YTD
112 ** CAEOY_EI_EE_TAXABLE_GRE_YTD
113 ** CAEOY_CPP_ER_LIABILITY_GRE_YTD
114 ** CAEOY_EI_ER_LIABILITY_GRE_YTD.
115 **
116 ** 02-DEC-2002 SSattini 115.28 Added 'nocopy' for out and in out
117 ** parameters, GSCC compliance.
118 ** 04-DEC-2002 SSattini 115.29 Fixed the bug#2695047, changed
119 ** employee address portion.
120 ** If country is CA then the province
121 ** value should be archived from
122 ** region_1 and if US then from
123 ** region_2.
124 ** 06-DEC-2002 SSattini 115.30 Fixed the bug#2598777, archiving
125 ** PA amounts in dollars only.
126 **
127 ** 27-AUG-2003 SSouresr 115.33 If the new balance 'T4A No Gross
128 ** Earnings'
129 ** is non zero then archiving will
130 ** take place even if Gross Earnings is
131 ** zero.
132 ** 18-SEP-2003 mmukherj 115.34 Added proper error message if
133 ** transmitter GRE is not found.
134 **
135 ** 30-OCT-2003 SSattini 115.35 2696309 Added functionality to archive
136 ** Pension Plan Registration Numbers
137 ** in pay_action_information table
138 ** to be reported in T4A Summary
139 ** record (Employer Level).
140 ** 02-FEB-2004 SSattini 115.38 Tuned c_eoy_gre cursor in
141 ** action_creation procedure to fix
142 ** performance bug#3416511.
143 ** 02-JUL-2004 mmukherj 115.39 Tuned c_get_latest_asg cursor
144 ** bug#3358776.
145 ** 06-AUG-2004 SSattini 115.40 Modified cursor cur_non_box_mesg
146 ** to archive balance adjustments
147 ** for Non-box footnotes. Bug#3641353.
148 **
149 ** 10-AUG-2004 ssouresr 115.42 Added the negative balance flag bug#3311402
150 ** Also modified the non box footnote logic
151 ** so that the amounts for identical footnote
152 ** codes are summed up bug#3641308
153 ** 24-AUG-2004 mmukherj 115.43 Archiving two more dbis
154 ** CAEOY_TECHNICAL_CONTACT_EMAIL
155 ** CAEOY_TECHNICAL_CONTACT_EXTN
156 ** needed for T4A XML Magatpe.
157 **
158 ** 02-OCT-2004 ssouresr 115.45 Employee Address is now archived
159 ** for terminated employees
160 ** 02-OCT-2004 ssouresr 115.46 The negative balance flag will be
161 ** archived as Y if any box or nonbox
162 ** footnote is negative
163 ** 03-NOV-2004 rigarg 115.47 3922311, Modified the cursor employer_info
164 ** 3973040 to remove check for transmitter code 901.
165 **
166 ** 10-NOV-2004 ssouresr 115.48 Modified to use tables instead of views
167 ** to remove problems with security groups
168 ** 19-NOV-2004 mmukherj 115.49 bigfix 3913784
169 ** 24-NOV-2004 mmukherj 115.50 Changed the code so that if the
170 ** accounting contact info for GRE is
171 ** not there then it archives the
172 ** accounting contact info of Transmitter
173 ** 02-DEC-2004 ssouresr 115.51 Added error message for security group
174 ** 06-DEC-2004 mmukherj 115.52 Fix for not archiving the registration
175 ** no if archiver value is null.
176 ** 08-DEC-2004 mmukherj 115.53 Fix for PA registration no archiving.#3913784
177 ** 14-SEP-2004 ssouresr 115.54 Added T4A Archiver Amendment functionality
178 ** by creating function compare_archive_data
179 ** and using it to archive the T4A amendment
180 ** flag
181 ** 01-FEB-2005 mmukherj 115.55 Fix for single footnote #4107278
182 ** nonbox footnote #4118500 added.
183 ** 02-FEB-2005 ssouresr 115.56 Nonbox footnotes with a value of zero
184 ** will not be archived. In addition if the
185 ** same nonbox footnote is processed multiple
186 ** times this will be considered as only one
187 ** footnote count for the purposes of box38
188 ** 04-MAR-2005 ssouresr 115.57 The archiver uses a new NonBox Footnote Element
189 ** which has a Jurisdiction input value from the
190 ** beginning of 2006
191 ** 26-APR-2005 ssouresr 115.58 The archiver will now recognize amendments
192 ** made only to non box footnotes
193 ** 08-JUN-2005 ssouresr 115.59 Removed error message for security group
194 ** 15-JUL-2005 mmukherj 115.60 Bug fix #4026689. Added call to
195 ** eoy_archive_gre_data in eoy_archive_data.
196 ** So that when the Retry process calls
197 ** eoy_archive_data, it re-archives the employer
198 ** and transmitter data.
199 ** 05-AUG-2005 saurgupt 115.61 Bug 4517693: Added Address_line3 for
200 ** T4A archiver.
201 ** 26-AUG-2005 mmukherj 115.62 Commented out the use two cursors
202 ** c_eoy_all and eoy_all_range. Since GRE is
203 ** a mandatory parameter for Federal
204 ** Yearend Archiver Process these two cursors
205 ** will never be used.
206 ** 06-sep-2005 mmukherj 115.63 g_archive_flag is set to 'Y' after
207 ** archiving the GRE data. Otherwise it was
208 ** archiving Employer data multiple times
209 ** in some cases where there are more than one
210 ** chunks used in the process.
211 ** 27-SEP-2005 ssouresr 115.64 Corrected the footnote condition in the
212 ** function compare_archive_data
213 ** 06-OCT-2005 ssouresr 115.65 Modified the range cursor to avoid the use
214 ** of hr_soft_coding_keyflex.
215 ** 26-OCT-2005 ssouresr 115.66 Modified the range cursor to add order hint
216 ** 22-AUG-2007 ssmukher 115.67 Bug 4021563 Added code for Status
217 ** Indian employee in eoy_archive_data
218 ** procedure.
219 */
220
221 sqwl_range varchar2(4000);
222 eoy_gre_range varchar2(4000);
223 eoy_all_range varchar2(4000);
224
225 /* Returns the value of a legislative_parameter from pay_payroll_actions */
226
227 function get_parameter(name in varchar2,
228 parameter_list varchar2)
229 return varchar2
230 is
231 start_ptr number;
232 end_ptr number;
233 token_val pay_payroll_actions.legislative_parameters%type;
234 par_value pay_payroll_actions.legislative_parameters%type;
235 begin
236
237 token_val := name||'=';
238
239 start_ptr := instr(parameter_list, token_val) + length(token_val);
240 end_ptr := instr(parameter_list, ' ', start_ptr);
241
242 /* if there is no spaces use then length of the string */
243 if end_ptr = 0 then
244 end_ptr := length(parameter_list)+1;
245 end if;
246
247 /* Did we find the token */
248 if instr(parameter_list, token_val) = 0 then
249 par_value := NULL;
250 else
251 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
252 end if;
253
254 return par_value;
255
256 end get_parameter;
257
258 /*
259 ** Name : bal_db_item
260 ** Purpose : Given the name of a balance DB item as would be seen in
261 ** a fast formula it returns the defined_balance_id of the
262 ** balance it represents.
263 ** Arguments :
264 ** Notes : A defined balance_id is required by the PLSQL balance function.
265 */
266
267 function bal_db_item
268 (
269 p_db_item_name varchar2
270 ) return number is
271
272 /* Get the defined_balance_id for the specified balance DB item. */
273
274 cursor csr_defined_balance is
275 select to_number(UE.creator_id)
276 from ff_user_entities UE,
277 ff_database_items DI
278 where DI.user_name = p_db_item_name
279 and UE.user_entity_id = DI.user_entity_id
280 and Ue.creator_type = 'B'
281 and UE.legislation_code = 'CA';
282
283 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
284
285 begin
286
287 open csr_defined_balance;
288 fetch csr_defined_balance into l_defined_balance_id;
289 if csr_defined_balance%notfound then
290 close csr_defined_balance;
291 raise hr_utility.hr_error;
292 else
293 close csr_defined_balance;
294 end if;
295
296 return (l_defined_balance_id);
297
298 end bal_db_item;
299
300 /*
301 ** Name : get_dates
302 ** Purpose : The dates are dependent on the report being run
303 ** For T4 it is year end dates.
304 */
305
306 procedure get_dates
307 (
308 p_report_type in varchar2,
309 p_effective_date in date,
310 p_period_end in out nocopy date,
311 p_quarter_start in out nocopy date,
312 p_quarter_end in out nocopy date,
313 p_year_start in out nocopy date,
314 p_year_end in out nocopy date
315 ) is
316 begin
317
318 if p_report_type = 'T4A' then
319 /*
320 ** Year End Pre-process is a yearly process where the identifier
321 ** indicates the year eg. 1998. The expected values for the example
322 ** should be
323 ** p_period_end 31-DEC-1998
324 ** p_quarter_start 01-OCT-1998
325 ** p_quarter_end 31-DEC-1998
326 ** p_year_start 01-JAN-1998
327 ** p_year_end 31-DEC-1998
328 */
329
330 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
331 p_quarter_start := trunc(p_period_end, 'Q');
332 p_quarter_end := p_period_end;
333
334 /* For EOY */
335
336 end if;
337
338 p_year_start := trunc(p_effective_date, 'Y');
339 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
340
341 end get_dates;
342
343 /*
344 ** Name : get_selection_information
345 ** Purpose : Returns information used in the selection of people to
346 ** be reported on.
347 ** Arguments :
348 **
349 ** The following values are returned :-
350 **
351 ** p_period_start - The start of the period over which to select
352 ** the people.
353 ** p_period_end - The end of the period over which to select
354 ** the people.
355 ** p_defined_balance_id - The balance which must be non zero for each
356 ** person to be included in the report.
357 ** p_group_by_gre - should the people be grouped by GRE.
358 ** p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
359 ** the testing of the balance.
360 ** p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
361 ** for the testing of the balance.
362 **
363 ** Notes : This routine provides a way of coding explicit rules for
364 ** individual reports where they are different from the
365 ** standard selection criteria for the report type ie. in
366 ** NY state the selection of people in the 4th quarter is
367 ** different from the first 3.
368 */
369
370 procedure get_selection_information
371 (
372
373 /* Identifies the type of report, the authority for which it is being run,
374 and the period being reported. */
375
376 p_report_type varchar2,
377 p_quarter_start date,
378 p_quarter_end date,
379 p_year_start date,
380 p_year_end date,
381 /* Information returned is used to control the selection of people to
382 report on. */
383
384 p_period_start in out nocopy date,
385 p_period_end in out nocopy date,
386 p_defined_balance_id in out nocopy number,
387 p_group_by_gre in out nocopy boolean,
388 p_tax_unit_context in out nocopy boolean,
389 p_jurisdiction_context in out nocopy boolean
390 ) is
391
392 begin
393
394 /* Depending on the report being processed, derive all the information
395 required to be able to select the people to report on. */
396
397 if p_report_type = 'T4A' then
398
399 /* Default settings for Year End Pre-process. */
400
401 p_period_start := p_year_start;
402 p_period_end := p_year_end;
403 p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD');
404 p_group_by_gre := FALSE;
405 p_tax_unit_context := TRUE;
406 p_jurisdiction_context := FALSE;
407
408 /* For EOY - end */
409
410 /* An invalid report type has been passed so fail. */
411
412 else
413
414 raise hr_utility.hr_error;
415
416 end if;
417
418 end get_selection_information;
419
420
421
422 /*
423 ** Name : eoy_action_creation
424 ** Purpose : This creates the assignment actions for a specific chunk
425 ** of people to be archived by the year end pre-process.
426 ** Arguments :
427 ** Notes :
428 */
429
430 procedure eoy_action_creation(pactid in number,
431 stperson in number,
432 endperson in number,
433 chunk in number) is
434
435
436
437 /* Variables used to hold the select columns from the SQL statement. */
438
439 l_person_id number;
440 l_assignment_id number;
441 l_tax_unit_id number;
442 l_eoy_tax_unit_id number;
443 l_effective_end_date date;
444 l_archive_item_id number;
445 l_user_entity_name_tab pay_ca_t4aeoy_archive.char240_data_type_table;
446
447 /* Variables used to hold the values used as bind variables within the
448 SQL statement. */
449
450 l_bus_group_id number;
451 l_period_start date;
452 l_period_end date;
453
454 /* Variables used to hold the details of the payroll and assignment actions
455 that are created. */
456
457 l_payroll_action_created boolean := false;
458 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
459 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
460
461 /* Variable holding the balance to be tested. */
462
463 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
464
465 /* Indicator variables used to control how the people are grouped. */
466
467 l_group_by_gre boolean := FALSE;
468
469 /* Indicator variables used to control which contexts are set up for
470 balance. */
471
472 l_tax_unit_context boolean := FALSE;
473 l_jurisdiction_context boolean := FALSE;
474
475 /* Variables used to hold the current values returned within the loop for
476 checking against the new values returned from within the loop on the
477 next iteration. */
478
479 l_prev_person_id per_all_people_f.person_id%type;
480 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
481
482 /* Variable to hold the jurisdiction code used as a context for state
483 reporting. */
484
485 l_jurisdiction_code varchar2(30);
486
487 /* general process variables */
488
489 l_report_type pay_payroll_actions.report_type%type;
490 l_province pay_payroll_actions.report_qualifier%type;
491 l_value number;
492 old_l_value number;
493 l_registration_no varchar2(150);
494 l_balance_name varchar2(150);
495 l_effective_date date;
496 l_quarter_start date;
497 l_quarter_end date;
498 l_year_start date;
499 l_year_end date;
500 lockingactid number;
501 l_primary_asg pay_assignment_actions.assignment_id%type;
502 l_legislative_parameters varchar2(240);
503 l_max_aaid number;
504
505
506 /* For Year End Preprocess we have to archive the assignments
507 belonging to a GRE */
508 /*
509 CURSOR c_eoy_gre IS
510 SELECT ASG.person_id person_id,
511 ASG.assignment_id assignment_id,
512 paa.tax_unit_id tax_unit_id,
513 ASG.effective_end_date effective_end_date
514 FROM per_all_assignments_f ASG,
515 pay_assignment_actions paa,
516 pay_payroll_actions ppa
517 WHERE ppa.payroll_action_id >= 0
518 AND ppa.effective_date between l_period_start
519 and l_period_end
520 AND ppa.action_type in ('R','Q','V','B','I')
521 AND ppa.business_group_id + 0 = l_bus_group_id
522 AND ppa.payroll_action_id = paa.payroll_action_id
523 AND paa.tax_unit_id = l_eoy_tax_unit_id
524 AND paa.assignment_id = ASG.assignment_id
525 AND ppa.business_group_id = ASG.business_group_id +0
526 AND ASG.person_id + 0 between stperson and endperson
527 AND ASG.assignment_type = 'E'
528 AND ppa.effective_date between ASG.effective_start_date
529 AND ASG.effective_end_date
530 ORDER BY 1, 3, 4 DESC, 2;
531 */
532
533 /* Tuned c_eoy_gre for bug#3416511 */
534 CURSOR c_eoy_gre IS
535 SELECT ASG.person_id person_id,
536 ASG.assignment_id assignment_id,
537 paa.tax_unit_id tax_unit_id,
538 ASG.effective_end_date effective_end_date
539 FROM per_all_assignments_f ASG,
540 pay_assignment_actions paa,
541 pay_payroll_actions ppa,
542 per_all_people_f ppf
543 WHERE ppa.effective_date between l_period_start
544 and l_period_end
545 AND ppa.action_type in ('R','Q','V','B','I')
546 AND ppa.business_group_id +0 = l_bus_group_id
547 AND ppa.payroll_action_id = paa.payroll_action_id
548 AND paa.tax_unit_id = l_eoy_tax_unit_id
549 AND paa.assignment_id = ASG.assignment_id
550 AND ppa.business_group_id = ASG.business_group_id +0
551 AND ppf.person_id between stperson and endperson
552 AND ASG.person_id = ppf.person_id
553 AND ASG.assignment_type = 'E'
554 AND ppa.effective_date between ASG.effective_start_date
555 AND ASG.effective_end_date
556 AND ppa.effective_date between ppf.effective_start_date
557 AND ppf.effective_end_date
558 ORDER BY 1, 3, 4 DESC, 2;
559
560
561 /* Commented c_eoy_all, because Tax Unit id is a mandatory parameter
562 in archiver process, this cursor will never be used */
563 /*
564 CURSOR c_eoy_all IS
565 SELECT ASG.person_id person_id,
566 ASG.assignment_id assignment_id,
567 to_number(SCL.segment11) tax_unit_id,
568 ASG.effective_end_date effective_end_date
569 FROM per_all_assignments_f ASG,
570 hr_soft_coding_keyflex SCL,
571 pay_all_payrolls_f PPY
572 WHERE ASG.business_group_id + 0 = l_bus_group_id
573 AND ASG.person_id between stperson and endperson
574 AND ASG.assignment_type = 'E'
575 AND ASG.effective_start_date <= l_period_end
576 AND ASG.effective_end_date >= l_period_start
577 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
578 AND PPY.payroll_id = ASG.payroll_id
579 ORDER BY 1, 3, 4 DESC, 2;
580 */
581 /* Get the primary assignment for the given person_id */
582
583 CURSOR c_get_asg_id (p_person_id number) IS
584 SELECT assignment_id
585 from per_all_assignments_f paf
586 where person_id = p_person_id
587 and primary_flag = 'Y'
588 and assignment_type = 'E'
589 and paf.effective_start_date <= l_period_end
590 and paf.effective_end_date >= l_period_start
591 ORDER BY assignment_id desc;
592
593 begin
594
595 /* Get the report type, report qualifier, business group id and the
596 gre for which the archiving has to be done */
597
598 hr_utility.trace('getting report type ');
599
600 select effective_date,
601 report_type,
602 business_group_id,
603 legislative_parameters
604 into l_effective_date,
605 l_report_type,
606 l_bus_group_id,
607 l_legislative_parameters
608 from pay_payroll_actions
609 where payroll_action_id = pactid;
610
611 l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
612
613 hr_utility.trace('getting dates');
614
615 get_dates(l_report_type,
616 l_effective_date,
617 l_period_end,
618 l_quarter_start,
619 l_quarter_end,
620 l_year_start,
621 l_year_end);
622
623 hr_utility.trace('getting selection information');
624 hr_utility.trace('report type '|| l_report_type);
625 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
626 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
627 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
628 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
629
630 get_selection_information
631 (l_report_type,
632 l_quarter_start,
633 l_quarter_end,
634 l_year_start,
635 l_year_end,
636 l_period_start,
637 l_period_end,
638 l_defined_balance_id,
639 l_group_by_gre,
640 l_tax_unit_context,
641 l_jurisdiction_context);
642
643 if l_eoy_tax_unit_id <> 99999 then
644 open c_eoy_gre;
645 /* else
646 open c_eoy_all;
647 */
648 end if;
649
650 /* Loop for all rows returned for SQL statement. */
651
652 hr_utility.trace('Entering loop');
653
654 loop
655
656 if l_eoy_tax_unit_id <> 99999 then
657
658 hr_utility.trace('Fetching person id');
659
660 fetch c_eoy_gre into l_person_id,
661 l_assignment_id,
662 l_tax_unit_id,
663 l_effective_end_date;
664
665 exit when c_eoy_gre%NOTFOUND;
666 /*
667 else
668
669 fetch c_eoy_all into l_person_id,
670 l_assignment_id,
671 l_tax_unit_id,
672 l_effective_end_date;
673
674 exit when c_eoy_all%NOTFOUND;
675 */
676 end if;
677
678
679 /* If the new row is the same as the previous row according to the way
680 the rows are grouped then discard the row ie. grouping by GRE
681 requires a single row for each person / GRE combination. */
682
683 hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
684 hr_utility.trace('previous tax unit id is '||
685 to_char(l_prev_tax_unit_id));
686
687 if ( l_person_id = l_prev_person_id and
688 l_tax_unit_id = l_prev_tax_unit_id) then
689 null;
690
691 else
692
693 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
694 hr_utility.trace('person is '|| to_char(l_person_id));
695 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
696
697
698 /* Have a new unique row according to the way the rows are grouped.
699 ** The inclusion of the person is dependent on having a non zero
700 ** balance.
701 ** If the balance is non zero then an assignment action is created to
702 ** indicate their inclusion in the magnetic tape report. */
703
704 /* Set up the context of tax unit id */
705
706 hr_utility.trace('Setting context');
707
708 if l_tax_unit_context then
709 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
710 end if;
711
712 begin
713 select paa1.assignment_action_id
714 into l_max_aaid
715 from pay_assignment_actions paa1,
716 per_all_assignments_f paf2,
717 pay_payroll_actions ppa2,
718 pay_action_classifications pac2
719 where paf2.person_id = l_person_id
720 and paa1.assignment_id = paf2.assignment_id
721 and paa1.tax_unit_id = l_tax_unit_id
722 and paa1.payroll_action_id = ppa2.payroll_action_id
723 and ppa2.action_type = pac2.action_type
724 and pac2.classification_name = 'SEQUENCED'
725 and ppa2.effective_date between paf2.effective_start_date
726 and paf2.effective_end_date
727 and ppa2.effective_date between l_period_start and
728 l_period_end
729 and not exists (select ''
730 FROM pay_action_classifications pac,
731 pay_payroll_actions ppa,
732 pay_assignment_actions paa,
733 per_all_assignments_f paf1
734 WHERE paf1.person_id = l_person_id
735 AND paa.assignment_id = paf1.assignment_id
736 AND paa.tax_unit_id = l_tax_unit_id
737 AND ppa.payroll_action_id = paa.payroll_action_id
738 AND ppa.effective_date between l_period_start
739 and l_period_end
740 AND paa.action_sequence > paa1.action_sequence
741 AND pac.action_type = ppa.action_type
742 AND pac.classification_name = 'SEQUENCED')
743 and rownum < 2;
744 exception
745 when no_data_found then
746 l_max_aaid := -9999;
747 end;
748
749 /* Get the primary assignment */
750 open c_get_asg_id(l_person_id);
751 fetch c_get_asg_id into l_primary_asg;
752 if c_get_asg_id%NOTFOUND then
753 close c_get_asg_id;
754 raise hr_utility.hr_error;
755 else
756 close c_get_asg_id;
757 end if;
758
759 if l_max_aaid <> -9999 then /* Max Assignment action id */
760 if ( (pay_ca_balance_pkg.call_ca_balance_get_value
761 ( 'Gross Earnings',
762 'YTD' , l_max_aaid, l_primary_asg , NULL, 'PER' ,
763 l_tax_unit_id, l_bus_group_id, NULL)
764 <> 0) OR
765 (pay_ca_balance_pkg.call_ca_balance_get_value
766 ( 'T4A No Gross Earnings',
767 'YTD' , l_max_aaid, l_primary_asg , NULL, 'PER' ,
768 l_tax_unit_id, l_bus_group_id, NULL)
769 <> 0) ) then
770 /* Create the assignment action to represnt the person / tax unit
771 combination. */
772
773 select pay_assignment_actions_s.nextval
774 into lockingactid
775 from dual;
776
777 /* Insert into pay_assignment_actions. */
778
779 hr_utility.trace('creating assignment action');
780
781 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
782 pactid,chunk,l_tax_unit_id);
783
784 /* Update the serial number column with the person id
785 ** so that the mag routine and the W2 view will not have
786 ** to do an additional checking against the assignment
787 ** table
788 */
789
790 hr_utility.trace('updating assignment action');
791
792 update pay_assignment_actions aa
793 set aa.serial_number = to_char(l_person_id)
794 where aa.assignment_action_id = lockingactid;
795 end if; /* End of Gross Earning <> 0 */
796 end if ; /*l_max_aaid <> -9999 */
797 end if; /* End of l_person_id <> l_prev_person_id */
798
799 /* Record the current values for the next time around the loop. */
800
801 l_prev_person_id := l_person_id;
802 l_prev_tax_unit_id := l_tax_unit_id;
803
804 end loop;
805
806 if l_eoy_tax_unit_id <> 99999 then
807 close c_eoy_gre;
808 /* else
809 close c_eoy_all;
810 */
811 end if;
812
813
814 end eoy_action_creation;
815
816
817
818 /*
819 ** Name : get_user_entity_id
820 ** Purpose : This gets the user_entity_id for a specific database item name.
821 ** Arguments : p_dbi_name -> database item name.
822 ** Notes :
823 */
824
825 function get_user_entity_id (p_dbi_name in varchar2)
826 return number is
827 l_user_entity_id number;
828
829 begin
830
831 hr_utility.trace('getting the user_entity_id for '
832 || p_dbi_name);
833 select fdi.user_entity_id
834 into l_user_entity_id
835 from ff_database_items fdi,
836 ff_user_entities fui
837 where user_name = p_dbi_name
838 and fdi.user_entity_id = fui.user_entity_id
839 and fui.legislation_code = 'CA';
840
841 return l_user_entity_id;
842
843 exception
844 when others then
845 hr_utility.trace('Error while getting the user_entity_id for '
846 || p_dbi_name ||'**');
847 raise hr_utility.hr_error;
848
849 end get_user_entity_id;
850
851 /* Name : get_footnote_user_entity_id
852 ** Purpose : This gets the user_entity_id for a specific database item name.
853 ** and it does not raise error if the the user entity is not found
854 ** Arguments : p_dbi_name -> database item name.
855 ** Notes :
856 */
857
858 function get_footnote_user_entity_id (p_dbi_name in varchar2)
859 return number is
860 l_user_entity_id number;
861
862 begin
863
864 select user_entity_id
865 into l_user_entity_id
866 from ff_database_items
867 where user_name = p_dbi_name;
868
869 return l_user_entity_id;
870
871 exception
872 when others then
873 hr_utility.trace('Error while getting the user_entity_id'
874 || p_dbi_name ||'***');
875 return 0;
876
877 end get_footnote_user_entity_id;
878
879 /*
880 Name : compare_archive_data
881 Purpose : compares Federal YEPP data and Federal YE Amendment Data
882 Arguments : p_assignment_action_id -> Assignment_action_id
883 p_locked_action_id -> YEPP Assignment_action_id
884
885 Notes : Used specifically for Federal YE Amendment Pre-Process (YE-2004)
886 */
887
888 function compare_archive_data(p_assignment_action_id in number
889 ,p_locked_action_id in number
890 ) return varchar2 is
891 TYPE act_info_rec IS RECORD
892 (archive_context1 number(25)
893 ,archive_ue_id number(25)
894 ,archive_value varchar2(240));
895
896 TYPE footnote_rec IS RECORD
897 (message varchar2(240)
898 ,value varchar2(240));
899
900 TYPE action_info_table IS TABLE OF act_info_rec
901 INDEX BY BINARY_INTEGER;
902
903 TYPE footnote_table IS TABLE OF footnote_rec
904 INDEX BY BINARY_INTEGER;
905
906 -- Cursor to get archived values based on asg_act_id
907
908 cursor c_get_emp_t4a_data (cp_asg_act_id number) is
909 SELECT fai1.context1,
910 fdi1.user_entity_id,
911 fai1.value
912 FROM ff_archive_items fai1,
913 ff_database_items fdi1
914 WHERE fai1.user_entity_id = fdi1.user_entity_id
915 AND fai1.context1 = cp_asg_act_id
916 AND fdi1.user_name <> 'CAEOY_T4A_AMENDMENT_FLAG'
917 order by fdi1.user_name;
918
919 cursor c_get_nonbox_footnote(cp_asg_act_id number) is
920 select action_information4,
921 action_information5
922 from pay_action_information
923 where action_context_id = cp_asg_act_id
924 and action_information_category = 'CA FOOTNOTES'
925 and action_context_type = 'AAP'
926 and action_information6 = 'T4A'
927 order by action_information4;
928
929
930 i number;
931 lv_flag varchar2(2);
932 ltr_amend_arch_data action_info_table;
933 ltr_yepp_arch_data action_info_table;
934 ln_yepp_box_count number;
935 ln_amend_box_count number;
936
937 ltr_amend_footnote footnote_table;
938 ltr_yepp_footnote footnote_table;
939 ln_yepp_footnote_count number;
940 ln_amend_footnote_count number;
941
942 begin
943 -- hr_utility.trace_on('Y','TEST');
944
945 /* Initialization Process */
946
947 lv_flag := 'N';
948
949 if ltr_amend_arch_data.count > 0 then
950 ltr_amend_arch_data.delete;
951 end if;
952
953 if ltr_yepp_arch_data.count > 0 then
954 ltr_yepp_arch_data.delete;
955 end if;
956
957 if ltr_amend_footnote.count > 0 then
958 ltr_amend_footnote.delete;
959 end if;
960
961 if ltr_yepp_footnote.count > 0 then
962 ltr_yepp_footnote.delete;
963 end if;
964
965 /* Populate T4A Amendment Footnotes */
966 open c_get_nonbox_footnote(p_assignment_action_id);
967
968 hr_utility.trace('Populating T4A Amendment Footnote ');
969
970 ln_amend_footnote_count := 0;
971 loop
972 fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
973 exit when c_get_nonbox_footnote%NOTFOUND;
974
975 hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
976 hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
977
978 ln_amend_footnote_count := ln_amend_footnote_count + 1;
979 end loop;
980
981 close c_get_nonbox_footnote;
982
983 /* Populate T4A YEPP Footnotes */
984 open c_get_nonbox_footnote(p_locked_action_id);
985
986 ln_yepp_footnote_count := 0;
987 loop
988 fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
989 exit when c_get_nonbox_footnote%NOTFOUND;
990
991 hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
992 hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
993
994 ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
995 end loop;
996
997 close c_get_nonbox_footnote;
998
999
1000 /* Populate T4A Amendment Data for an assignment_action */
1001 open c_get_emp_t4a_data(p_assignment_action_id);
1002
1003 hr_utility.trace('Populating T4A Amendment Data ');
1004 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1005
1006 ln_amend_box_count := 0;
1007 loop
1008 fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1009 exit when c_get_emp_t4a_data%NOTFOUND;
1010
1011 hr_utility.trace('I :'||to_char(ln_amend_box_count));
1012 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1013 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1014 hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1015
1016 ln_amend_box_count := ln_amend_box_count + 1;
1017 end loop;
1018
1019 close c_get_emp_t4a_data;
1020
1021
1022 /* Populate T4A YEPP Data for an assignment_action */
1023 open c_get_emp_t4a_data(p_locked_action_id);
1024
1025 hr_utility.trace('Populating T4A YEPP Data ');
1026 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1027
1028 ln_yepp_box_count := 0;
1029 loop
1030 fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1031 exit when c_get_emp_t4a_data%NOTFOUND;
1032
1033 hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1034 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1035 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1036 hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1037
1038 ln_yepp_box_count := ln_yepp_box_count + 1;
1039 end loop;
1040
1041 close c_get_emp_t4a_data;
1042
1043 /* Compare T4A Amendment Data and T4A YEPP Data for an
1044 assignment_action */
1045
1046 hr_utility.trace('Comparing T4A Amend and T4A YEPP Data');
1047
1048 if ln_yepp_box_count <> ln_amend_box_count then
1049 lv_flag := 'Y';
1050 elsif ln_yepp_box_count = ln_amend_box_count then
1051 for i in ltr_yepp_arch_data.first..ltr_yepp_arch_data.last
1052 loop
1053 if (ltr_yepp_arch_data(i).archive_ue_id =
1054 ltr_amend_arch_data(i).archive_ue_id) then
1055
1056 if ((ltr_yepp_arch_data(i).archive_value <>
1057 ltr_amend_arch_data(i).archive_value) or
1058 (ltr_yepp_arch_data(i).archive_value is null and
1059 ltr_amend_arch_data(i).archive_value is not null) or
1060 (ltr_yepp_arch_data(i).archive_value is not null and
1061 ltr_amend_arch_data(i).archive_value is null)) then
1062
1063 lv_flag := 'Y';
1064 hr_utility.trace('Archive_UE_id with differnt value :'||to_char(ltr_yepp_arch_data(i).archive_ue_id));
1065 exit;
1066 end if;
1067 end if;
1068 end loop;
1069 end if;
1070
1071 /* Compare T4A Amendment Footnotes and T4A YEPP Footnotes for an
1072 assignment_action */
1073
1074 hr_utility.trace('Comparing T4A Amend and T4A YEPP Footnotes');
1075
1076 if lv_flag <> 'Y' then
1077 if ln_yepp_footnote_count <> ln_amend_footnote_count then
1078 lv_flag := 'Y';
1079 elsif ((ln_yepp_footnote_count = ln_amend_footnote_count) and
1080 (ln_yepp_footnote_count <> 0)) then
1081 for i in ltr_yepp_footnote.first..ltr_yepp_footnote.last
1082 loop
1083 if (ltr_yepp_footnote(i).message =
1084 ltr_amend_footnote(i).message) then
1085
1086 if ((ltr_yepp_footnote(i).value <>
1087 ltr_amend_footnote(i).value) or
1088 (ltr_yepp_footnote(i).value is null and
1089 ltr_amend_footnote(i).value is not null) or
1090 (ltr_yepp_footnote(i).value is not null and
1091 ltr_amend_footnote(i).value is null)) then
1092
1093 lv_flag := 'Y';
1094 hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
1095 exit;
1096 end if;
1097 end if;
1098 end loop;
1099 end if;
1100 end if;
1101
1102 /* If there is no value difference for Entire Employee data then set
1103 flag to 'N' */
1104
1105 if lv_flag <> 'Y' then
1106 lv_flag := 'N';
1107 hr_utility.trace('No value difference for an Employee Asg Action: '||
1108 to_char(p_assignment_action_id));
1109 end if;
1110
1111 hr_utility.trace('lv_flag :'||lv_flag);
1112
1113 return lv_flag;
1114
1115 -- hr_utility.trace_off;
1116
1117 end compare_archive_data;
1118
1119
1120 /* Name : eoy_archive_gre_data
1121 Purpose : This performs the CA specific employer data archiving.
1122 Arguments :
1123 Notes :
1124 */
1125
1126 procedure eoy_archive_gre_data(p_payroll_action_id in number,
1127 p_tax_unit_id in number,
1128 p_transmitter_gre_id in number)
1129 is
1130
1131 l_user_entity_id number;
1132 l_taxunit_context_id number;
1133 l_jursd_context_id number;
1134 l_value varchar2(240);
1135 l_sit_uid number;
1136 l_sui_uid number;
1137 l_fips_uid number;
1138 l_counter number;
1139 l_seq_tab pay_ca_t4aeoy_archive.number_data_type_table;
1140 l_context_id_tab pay_ca_t4aeoy_archive.number_data_type_table;
1141 l_context_val_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1142 l_user_entity_name_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1143 l_balance_type_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1144 l_user_entity_value_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1145 l_arch_gre_step number := 0;
1146 l_name varchar2(240);
1147 l_business_group_id number;
1148 l_seq number;
1149 l_context_id number;
1150 l_context_val varchar2(240);
1151 l_employer_ein varchar2(240);
1152 l_address_line_1 varchar2(240);
1153 l_address_line_2 varchar2(240);
1154 l_address_line_3 varchar2(240);
1155 l_town_or_city varchar2(240);
1156 l_province_code varchar2(240);
1157 l_postal_code varchar2(240);
1158 l_country_code varchar2(240);
1159 l_accounting_contact_name varchar2(240);
1160 l_accounting_contact_phone varchar2(240);
1161 l_accounting_contact_area_code varchar2(240);
1162 l_accounting_contact_extension varchar2(240);
1163
1164 l_trans_acct_contact_name varchar2(240);
1165 l_trans_acct_contact_area_code varchar2(240);
1166 l_trans_acct_contact_phone varchar2(240);
1167 l_trans_acct_contact_extn varchar2(240);
1168
1169 l_proprietor_sin_1 varchar2(240);
1170 l_proprietor_sin_2 varchar2(240);
1171 l_transmitter_name varchar2(240);
1172 l_transmitter_type_indicator varchar2(240);
1173 l_transmitter_number varchar2(240);
1174 l_transmitter_type_code varchar2(240);
1175 l_transmitter_data_type_code varchar2(240);
1176 l_transmitter_addr_line_1 varchar2(240);
1177 l_transmitter_addr_line_2 varchar2(240);
1178 l_transmitter_addr_line_3 varchar2(240);
1179 l_transmitter_city varchar2(240);
1180 l_transmitter_province varchar2(240);
1181 l_Federal_Youth_Hire_Ind varchar2(80);
1182 l_transmitter_postal_code varchar2(240);
1183 l_transmitter_country varchar2(240);
1184 l_transmitter_orgid number;
1185 l_technical_contact_name varchar2(240);
1186 l_technical_contact_phone varchar2(240);
1187 l_technical_contact_area_code varchar2(240);
1188 l_technical_contact_language varchar2(240);
1189 l_technical_contact_email varchar2(240);
1190 l_technical_contact_extn varchar2(240);
1191 l_object_version_number number;
1192 l_some_warning boolean;
1193 l_archive_item_id number;
1194 l_taxation_year varchar2(240);
1195 l_effective_date date;
1196 result number;
1197
1198 /* Alternate query to avoid hr_tax_units_v in t4a archiver */
1199
1200 cursor employer_info is
1201 select nvl(hoi6.ORG_INFORMATION9,ou2.name) GRE_stat_report_name,
1202 bg.business_group_id Business_group_id,
1203 hoi6.ORG_INFORMATION1 Employer_identification_number,
1204 hl2.ADDRESS_LINE_1 GRE_addrline1,
1205 hl2.ADDRESS_LINE_2 GRE_addrline2,
1206 hl2.ADDRESS_LINE_3 GRE_addrline3,
1207 hl2.TOWN_OR_CITY GRE_town_or_city,
1208 DECODE(hl2.STYLE , 'US' , hl2.REGION_2 ,
1209 'CA' , hl2.REGION_1 ,
1210 'CA_GLB',hl2.region_1, ' ') GRE_province,
1211 hl2.POSTAL_CODE GRE_postal_code,
1212 hl2.COUNTRY GRE_country,
1213 nvl(hoi3.ORG_INFORMATION9,ou1.name) trans_stat_report_name,
1214 hl1.ADDRESS_LINE_1 trans_addrline1,
1215 hl1.ADDRESS_LINE_2 trans_addrline2,
1216 hl1.ADDRESS_LINE_3 trans_addrline3,
1217 hl1.TOWN_OR_CITY trans_town_or_city,
1218 DECODE(hl1.STYLE , 'US' , hl1.REGION_2 ,
1219 'CA' , hl1.REGION_1 ,
1220 'CA_GLB',hl1.region_1, ' ') trans_province,
1221 hl1.POSTAL_CODE trans_postal_code,
1222 hl1.COUNTRY trans_country,
1223 hoi2.org_information5 trans_type_indicator,
1224 hoi2.ORG_INFORMATION4 trans_number,
1225 hoi2.ORG_INFORMATION2 trans_type_code,
1226 hoi2.ORG_INFORMATION3 trans_datatype_code,
1227 hoi2.ORG_INFORMATION6 trans_tech_contact_name,
1228 hoi2.ORG_INFORMATION8 trans_tech_contact_phone,
1229 hoi2.ORG_INFORMATION7 trans_tech_contact_areacode,
1230 hoi2.ORG_INFORMATION9 trans_tech_contact_lang,
1231 hoi2.ORG_INFORMATION17 trans_tech_contact_extn,
1232 hoi2.ORG_INFORMATION18 trans_tech_contact_email,
1233 hoi2.ORG_INFORMATION10 trans_acct_contact_name,
1234 hoi2.ORG_INFORMATION11 trans_acct_contact_area_code,
1235 hoi2.ORG_INFORMATION12 trans_acct_contact_phone,
1236 hoi2.ORG_INFORMATION13 trans_acct_contact_extn
1237 from hr_all_organization_units ou1, /* transmitter org */
1238 hr_organization_information hoi1, /* Transmitter GRE to check
1239 GRE/Legal Classification is enabled */
1240 hr_organization_information hoi2, /* Transmitter GRE to check
1241 'Fed Magnetic Reporting' */
1242 hr_organization_information hoi3, /* Transmitter GRE to check
1243 'Employer Identification' */
1244 hr_locations_all hl1, /* trans location */
1245 hr_all_organization_units ou2, /* GRE Org */
1246 hr_organization_information hoi4, /* GRE to check GRE/Legal
1247 Classification is enabled */
1248 hr_organization_information hoi6, /* GRE to check
1249 'Employer Identification'*/
1250 hr_locations_all hl2, /* GRE location */
1251 per_business_groups bg
1252 where bg.business_group_id = ou1.business_group_id
1253 and bg.legislation_code = 'CA'
1254 and ou1.organization_id = p_transmitter_gre_id
1255 and ou1.organization_id = hoi1.organization_id
1256 and hoi1.org_information_context = 'CLASS'
1257 and hoi1.org_information1 = 'HR_LEGAL'
1258 and hoi1.org_information2 = 'Y'
1259 and ou1.location_id = hl1.location_id
1260 and ou1.organization_id = hoi2.organization_id
1261 and hoi2.org_information_context = 'Fed Magnetic Reporting'
1262 and hoi2.org_information1 = 'Y'
1263 and ou1.organization_id = hoi3.organization_id
1264 and hoi3.org_information_context = 'Canada Employer Identification'
1265 and hoi3.org_information5 in ('T4A/RL1','T4A/RL2')
1266 and bg.business_group_id = ou2.business_group_id
1267 and ou2.organization_id = p_tax_unit_id
1268 and ou2.organization_id = hoi4.organization_id
1269 and hoi4.org_information_context = 'CLASS'
1270 and hoi4.org_information1 = 'HR_LEGAL'
1271 and hoi4.org_information2 = 'Y'
1272 and ou2.location_id = hl2.location_id
1273 and ou2.organization_id = hoi6.organization_id
1274 and hoi6.org_information_context = 'Canada Employer Identification'
1275 and hoi6.ORG_INFORMATION5 in ('T4A/RL1','T4A/RL2');
1276
1277 /* Created this cursor to fix bug#2598802 */
1278 CURSOR c_get_gre_acct_info(cp_gre_id number) IS
1279 select hoi.ORG_INFORMATION10 GRE_acct_contact_name,
1280 hoi.ORG_INFORMATION12 GRE_acct_contact_phone,
1281 hoi.ORG_INFORMATION11 GRE_acct_contact_area_code,
1282 hoi.ORG_INFORMATION13 GRE_acct_contact_extn,
1283 hoi.ORG_INFORMATION14 GRE_Proprietor_SIN#1,
1284 hoi.ORG_INFORMATION15 GRE_Proprietor_SIN#2,
1285 hoi.ORG_INFORMATION16 GRE_Fedyouth_hire_Prgind
1286 from hr_organization_information hoi
1287 where hoi.organization_id = cp_gre_id
1288 and hoi.org_information_context = 'Fed Magnetic Reporting';
1289
1290 begin
1291 /* payroll action level database items */
1292
1293 l_arch_gre_step := 30;
1294
1295 /* Archive the Employer level data */
1296
1297 begin
1298 hr_utility.trace('getting employer data ');
1299
1300 open employer_info;
1301 fetch employer_info
1302 into l_name, l_business_group_id,
1303 l_employer_ein, l_address_line_1,
1304 l_address_line_2, l_address_line_3,
1305 l_town_or_city, l_province_code,
1306 l_postal_code, l_country_code,
1307 l_transmitter_name,
1308 l_transmitter_addr_line_1,
1309 l_transmitter_addr_line_2, l_transmitter_addr_line_3,
1310 l_transmitter_city, l_transmitter_province,
1311 l_transmitter_postal_code, l_transmitter_country,
1312 l_Transmitter_Type_Indicator, l_Transmitter_number,
1313 l_Transmitter_Type_code, l_Transmitter_data_type_code,
1314 l_technical_contact_name, l_technical_contact_phone,
1315 l_technical_contact_area_code, l_technical_contact_language,
1316 l_technical_contact_extn, l_technical_contact_email,
1317 l_trans_acct_contact_name, l_trans_acct_contact_area_code,
1318 l_trans_acct_contact_phone, l_trans_acct_contact_extn;
1319
1320 /* Added this part to fix bug#2598802 */
1321 open c_get_gre_acct_info(p_tax_unit_id);
1322 fetch c_get_gre_acct_info into l_accounting_contact_name,
1323 l_accounting_contact_phone ,
1324 l_accounting_contact_area_code,
1325 l_accounting_contact_extension,
1326 l_proprietor_sin_1,
1327 l_proprietor_sin_2,
1328 l_federal_youth_hire_ind;
1329
1330
1331 if employer_info%FOUND then
1332 close employer_info;
1333 hr_utility.trace('got employer data ');
1334 else
1335 hr_utility.trace('cannot find employer data ');
1336 l_employer_ein := null;
1337 l_address_line_1 := null;
1338 l_address_line_2 := null;
1339 l_address_line_3 := null;
1340 l_town_or_city := null;
1341 l_province_code := null;
1342 l_postal_code := null;
1343 l_country_code := null;
1344 l_name := null;
1345 l_transmitter_name := null;
1346 l_transmitter_addr_line_1 := null;
1347 l_transmitter_addr_line_2 := null;
1348 l_transmitter_addr_line_3 := null;
1349 l_transmitter_city := null;
1350 l_transmitter_province := null;
1351 l_transmitter_postal_code := null;
1352 l_transmitter_country := null;
1353 l_technical_contact_name := null;
1354 l_technical_contact_phone := null;
1355 l_technical_contact_language := null;
1356
1357 close employer_info;
1358 hr_utility.raise_error;
1359 end if;
1360
1361 /* Added this part to fix bug#2598802 */
1362 if c_get_gre_acct_info%found then
1363 close c_get_gre_acct_info;
1364 else
1365 l_accounting_contact_name := null;
1366 l_accounting_contact_phone := null;
1367 l_accounting_contact_area_code := null;
1368 l_accounting_contact_extension := null;
1369 l_proprietor_sin_1 := null;
1370 l_proprietor_sin_2 := null;
1371 l_federal_youth_hire_ind := null;
1372 end if;
1373
1374 if l_accounting_contact_name is null then
1375 l_accounting_contact_name := l_trans_acct_contact_name;
1376 end if;
1377
1378 if l_accounting_contact_phone is null then
1379 l_accounting_contact_phone := l_trans_acct_contact_phone;
1380 end if;
1381
1382 if l_accounting_contact_area_code is null then
1383 l_accounting_contact_area_code := l_trans_acct_contact_area_code;
1384 end if;
1385
1386 if l_accounting_contact_extension is null then
1387 l_accounting_contact_extension := l_trans_acct_contact_extn;
1388 end if;
1389 end;
1390
1391
1392 begin
1393 select to_char(effective_date,'YYYY'),
1394 add_months(trunc(effective_date, 'Y'),12) - 1
1395 into l_taxation_year,
1396 l_effective_date
1397 from pay_payroll_actions
1398 where payroll_action_id = p_payroll_action_id;
1399
1400 exception when no_data_found then
1401 l_taxation_year := null;
1402 l_effective_date := null;
1403
1404 end;
1405
1406 select context_id
1407 into l_taxunit_context_id
1408 from ff_contexts
1409 where context_name = 'TAX_UNIT_ID';
1410
1411 l_counter := 0;
1412 l_arch_gre_step := 40;
1413
1414 l_counter := l_counter + 1;
1415 l_user_entity_name_tab(l_counter) := 'CAEOY_TAXATION_YEAR';
1416 l_user_entity_value_tab(l_counter) := l_taxation_year;
1417
1418 l_counter := l_counter + 1;
1419 l_user_entity_name_tab(l_counter) := 'CAEOY_TAX_UNIT_ID';
1420 l_user_entity_value_tab(l_counter) := p_tax_unit_id;
1421
1422 l_counter := l_counter + 1;
1423 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_COUNTRY';
1424 l_user_entity_value_tab(l_counter) := l_transmitter_country;
1425
1426 l_counter := l_counter + 1;
1427 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NAME';
1428 -- l_user_entity_value_tab(l_counter) := l_transmitter_country;
1429 l_user_entity_value_tab(l_counter) := l_transmitter_name;
1430
1431 l_counter := l_counter + 1;
1432 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE1';
1433 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_1;
1434
1435 l_counter := l_counter + 1;
1436 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE2';
1437 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_2;
1438
1439 -- Bug 4517693
1440 l_counter := l_counter + 1;
1441 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE3';
1442 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_3;
1443
1444 l_counter := l_counter + 1;
1445 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_CITY';
1446 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1447
1448 l_counter := l_counter + 1;
1449 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_PROVINCE';
1450 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1451
1452 l_counter := l_counter + 1;
1453 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_POSTAL_CODE';
1454 l_user_entity_value_tab(l_counter) := l_transmitter_postal_code;
1455
1456 l_counter := l_counter + 1;
1457 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_INDICATOR';
1458 l_user_entity_value_tab(l_counter) := l_transmitter_type_indicator;
1459
1460 l_counter := l_counter + 1;
1461 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NUMBER';
1462 l_user_entity_value_tab(l_counter) := l_transmitter_number;
1463
1464 l_counter := l_counter + 1;
1465 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_CODE';
1466 l_user_entity_value_tab(l_counter) := l_transmitter_type_code;
1467
1468 l_counter := l_counter + 1;
1469 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_DATA_TYPE_CODE';
1470 l_user_entity_value_tab(l_counter) := l_transmitter_data_type_code;
1471
1472 l_counter := l_counter + 1;
1473 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_NAME';
1474 l_user_entity_value_tab(l_counter) := l_technical_contact_name;
1475
1476 l_counter := l_counter + 1;
1477 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_PHONE';
1478 l_user_entity_value_tab(l_counter) := l_technical_contact_phone;
1479
1480 l_counter := l_counter + 1;
1481 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_AREA_CODE';
1482 l_user_entity_value_tab(l_counter) := l_technical_contact_area_code;
1483
1484 l_counter := l_counter + 1;
1485 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EXTN';
1486 l_user_entity_value_tab(l_counter) := l_technical_contact_extn;
1487
1488 l_counter := l_counter + 1;
1489 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EMAIL';
1490 l_user_entity_value_tab(l_counter) := l_technical_contact_email;
1491
1492 l_counter := l_counter + 1;
1493 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_LANGUAGE';
1494 l_user_entity_value_tab(l_counter) := l_technical_contact_language;
1495
1496 l_counter := l_counter + 1;
1497 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_NAME';
1498 l_user_entity_value_tab(l_counter) := l_accounting_contact_name;
1499
1500 l_counter := l_counter + 1;
1501 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_PHONE';
1502 l_user_entity_value_tab(l_counter) := l_accounting_contact_phone ;
1503
1504 l_counter := l_counter + 1;
1505 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE';
1506 l_user_entity_value_tab(l_counter) := l_accounting_contact_area_code ;
1507
1508 l_counter := l_counter + 1;
1509 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_EXTENSION';
1510 l_user_entity_value_tab(l_counter) := l_accounting_contact_extension ;
1511
1512
1513 l_counter := l_counter + 1;
1514 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_NAME';
1515 l_user_entity_value_tab(l_counter) := l_name;
1516
1517 l_counter := l_counter + 1;
1518 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER';
1519 l_user_entity_value_tab(l_counter) := l_employer_ein;
1520
1521 l_counter := l_counter + 1;
1522 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE1';
1523 l_user_entity_value_tab(l_counter) := l_address_line_1;
1524
1525 l_counter := l_counter + 1;
1526 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE2';
1527 l_user_entity_value_tab(l_counter) := l_address_line_2;
1528
1529 -- Added by Saurabh for bug 4517693
1530 l_counter := l_counter + 1;
1531 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE3';
1532 l_user_entity_value_tab(l_counter) := l_address_line_3;
1533
1534 l_counter := l_counter + 1;
1535 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_CITY';
1536 l_user_entity_value_tab(l_counter) := l_town_or_city;
1537
1538 l_counter := l_counter + 1;
1539 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_PROVINCE';
1540 l_user_entity_value_tab(l_counter) := l_province_code;
1541
1542 l_counter := l_counter + 1;
1543 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_COUNTRY';
1544 l_user_entity_value_tab(l_counter) := l_country_code;
1545
1546 l_counter := l_counter + 1;
1547 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_POSTAL_CODE';
1548 l_user_entity_value_tab(l_counter) := l_postal_code;
1549
1550
1551 l_counter := l_counter + 1;
1552 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN1';
1553 l_user_entity_value_tab(l_counter) := l_proprietor_sin_1;
1554
1555 l_counter := l_counter + 1;
1556 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN2';
1557 l_user_entity_value_tab(l_counter) := l_proprietor_sin_2;
1558
1559 l_counter := l_counter + 1;
1560 l_user_entity_name_tab(l_counter):='CAEOY_FEDERAL_YOUTH_HIRE_PROGRAM_INDICATOR';
1561 l_user_entity_value_tab(l_counter) := l_federal_youth_hire_ind;
1562
1563 for i in 1..l_counter loop
1564
1565 l_arch_gre_step := 42;
1566 hr_utility.trace('calling archive API ' || l_user_entity_name_tab(i));
1567 ff_archive_api.create_archive_item(
1568 -- p_validate => 'TRUE'
1569 p_archive_item_id => l_archive_item_id
1570 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1571 ,p_archive_value => l_user_entity_value_tab(i)
1572 ,p_archive_type => 'PA'
1573 ,p_action_id => p_payroll_action_id
1574 ,p_legislation_code => 'CA'
1575 ,p_object_version_number => l_object_version_number
1576 ,p_some_warning => l_some_warning
1577 );
1578 hr_utility.trace('Ended calling archive API');
1579 l_arch_gre_step := 47;
1580 end loop;
1581
1582 /* Removed the unnecessary archiving of db items with dimension _GRE_YTD */
1583
1584 --hr_utility.trace_off;
1585 g_archive_flag := 'Y';
1586 exception
1587 when others then
1588 g_archive_flag := 'N';
1589 if l_transmitter_name is null then
1590 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1591 hr_utility.set_message_token('ORGIND','GRE');
1592 /* push message into pay_message_lines */
1593 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1594 pay_core_utils.push_token('ORGIND','GRE');
1595 hr_utility.raise_error;
1596 else
1597 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1598 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1599 hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1600 hr_utility.raise_error;
1601 end if;
1602 raise hr_utility.hr_error;
1603
1604 end eoy_archive_gre_data;
1605
1606 /* Name : chk_gre_archive
1607 Purpose : Function to check if the employer level data has been archived
1608 or not.
1609 Arguments :
1610 Notes :
1611 */
1612
1613 function chk_gre_archive (p_payroll_action_id number) return boolean is
1614
1615 l_flag varchar2(1);
1616
1617 cursor c_chk_payroll_action is
1618 select 'Y'
1619 from dual
1620 where exists (select null
1621 from ff_archive_items fai
1622 where fai.context1 = p_payroll_action_id
1623 and archive_type = 'PA');
1624 begin
1625
1626 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1627
1628 if g_archive_flag = 'Y' then
1629 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1630 return (TRUE);
1631 else
1632
1633 hr_utility.trace('chk_gre_archive - opening cursor');
1634
1635 open c_chk_payroll_action;
1636 fetch c_chk_payroll_action into l_flag;
1637 if c_chk_payroll_action%FOUND then
1638 hr_utility.trace('chk_gre_archive - found in cursor');
1639 g_archive_flag := 'Y';
1640 else
1641 hr_utility.trace('chk_gre_archive - not found in cursor');
1642 g_archive_flag := 'N';
1643 end if;
1644
1645 hr_utility.trace('chk_gre_archive - closing cursor');
1646 close c_chk_payroll_action;
1647 if g_archive_flag = 'Y' then
1648 hr_utility.trace('chk_gre_archive - returning true');
1649 return (TRUE);
1650 else
1651 hr_utility.trace('chk_gre_archive - returning false');
1652 return(FALSE);
1653 end if;
1654 end if;
1655 end chk_gre_archive;
1656
1657 /* Name : eoy_archinit
1658 Purpose : This performs the context initialization for the year end
1659 pre-process.
1660 Arguments :
1661 Notes :
1662 */
1663
1664
1665 procedure eoy_archinit(p_payroll_action_id in number) is
1666 l_jurisdiction_code VARCHAR2(30);
1667 l_tax_unit_id NUMBER(15);
1668 l_archive boolean:= FALSE;
1669 l_step number := 0;
1670
1671 cursor c_get_min_chunk is
1672 select min(paa.chunk_number)
1673 from pay_assignment_actions paa
1674 where paa.payroll_action_id = p_payroll_action_id;
1675
1676 begin
1677 open c_get_min_chunk;
1678 fetch c_get_min_chunk into g_min_chunk;
1679 l_step := 1;
1680 if c_get_min_chunk%NOTFOUND then
1681 g_min_chunk := -1;
1682 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1683 end if;
1684 close c_get_min_chunk;
1685
1686 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1687 l_step := 2;
1688 l_archive := chk_gre_archive(p_payroll_action_id);
1689
1690 l_step := 3;
1691 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1692 exception
1693 when others then
1694 raise_application_error(-20001,'eoy_archinit at '
1695 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1696 end eoy_archinit;
1697
1698
1699 /* Name : eoy_archive_data
1700 Purpose : This performs the CA specific employee context setting for the
1701 Year End PreProcess.
1702 Arguments :
1703 Notes :
1704 */
1705
1706 procedure eoy_archive_data(p_assactid in number, p_effective_date in date) is
1707
1708 l_aaid pay_assignment_actions.assignment_action_id%type;
1709 l_aaseq pay_assignment_actions.action_sequence%type;
1710 l_asgid pay_assignment_actions.assignment_id%type;
1711 l_date_earned date;
1712 l_user_entity_name_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1713 l_balance_type_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1714 l_user_entity_value_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1715 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1716 l_business_group_id pay_assignment_actions.tax_unit_id%type;
1717 l_year_start date;
1718 l_year_end date;
1719 l_context_no number := 60;
1720 l_count number := 0;
1721 l_jurisdiction varchar2(11);
1722 l_province_uei ff_user_entities.user_entity_id%type;
1723 l_county_uei ff_user_entities.user_entity_id%type;
1724 l_city_uei ff_user_entities.user_entity_id%type;
1725 l_county_sd_uei ff_user_entities.user_entity_id%type;
1726 l_city_sd_uei ff_user_entities.user_entity_id%type;
1727 l_province_abbrev pay_us_states.state_abbrev%type;
1728 l_county_name pay_us_counties.county_name%type;
1729 l_city_name pay_us_city_names.city_name%type;
1730 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
1731 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
1732 l_step number := 0;
1733 l_county_code varchar2(3);
1734 l_city_code varchar2(4);
1735 l_jursd_context_id ff_contexts.context_id%type;
1736 l_taxunit_context_id ff_contexts.context_id%type;
1737 l_seq_tab pay_ca_t4aeoy_archive.number_data_type_table;
1738 l_context_id_tab pay_ca_t4aeoy_archive.number_data_type_table;
1739 l_context_val_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1740 l_chunk number;
1741 l_payroll_action_id number;
1742 l_defined_balance_id number;
1743 l_result number;
1744 l_person_id number;
1745 l_organization_id number;
1746 l_location_id number;
1747 l_first_name varchar2(240);
1748 l_last_name varchar2(240);
1749 l_employee_number varchar2(240);
1750 l_national_identifier varchar2(240);
1751 l_pre_name_adjunct varchar2(240);
1752 l_middle_names varchar2(240);
1753 l_employee_phone_no varchar2(240);
1754 l_work_telephone varchar2(240);
1755 l_address_line1 varchar2(240);
1756 l_address_line2 varchar2(240);
1757 l_address_line3 varchar2(240);
1758 l_address_line4 varchar2(240);
1759 l_town_or_city varchar2(80);
1760 l_province_code varchar2(80);
1761 l_postal_code varchar2(80);
1762 l_telephone_number varchar2(80);
1763 l_country_code varchar2(80);
1764 l_counter number;
1765 l_archive_item_id number;
1766 result number := 0;
1767 earning_exists number := 0;
1768 l_object_version_number number;
1769 l_context_id number;
1770 l_context_val varchar2(80);
1771 l_some_warning boolean;
1772 l_cpp_exempt_flag varchar2(80);
1773 l_ei_exempt_flag varchar2(80);
1774 l_footnote_code varchar2(10);
1775 l_box38_footnote_code varchar2(10) := NULL;
1776 l_footnote_balance varchar2(80);
1777 l_footnote_amount number;
1778 old_l_footnote_code varchar2(80) := null;
1779 l_footnote_code_ue varchar2(80);
1780 l_box38_footnote_code_ue varchar2(80);
1781 l_footnote_amount_ue varchar2(80);
1782 l_no_of_fn_codes number := 0;
1783 l_box38_count number := 0;
1784 l_value number := 0;
1785 old_l_value number := 0;
1786 old_l_value1 number := 0;
1787 old_l_value2 number := 0;
1788 arch_l_value number := 0;
1789 l_registration_no varchar2(150);
1790 old_l_registration_no varchar2(150);
1791 old_l_registration_no1 varchar2(150);
1792 old_l_registration_no2 varchar2(150);
1793 arch_l_registration_no varchar2(150);
1794 l_balance_name varchar2(150);
1795 l_single_footnote_code varchar2(10);
1796 lv_serial_number varchar2(30);
1797 l_negative_balance_exists varchar2(5);
1798
1799 /* new variables added for Federal YE Amendment PP */
1800 ld_fapp_effective_date date;
1801 lv_fapp_report_type varchar2(20);
1802 ln_fapp_locked_action_id number;
1803 lv_fapp_flag varchar2(2);
1804 lv_fapp_locked_actid_reptype varchar2(20);
1805
1806 /* T4A Nonbox footnote variables */
1807 l_messages VARCHAR2(240);
1808 l_prev_messages VARCHAR2(240);
1809 l_mesg_amt NUMBER(16,2);
1810 l_total_mesg_amt NUMBER(16,2);
1811 ln_tax_unit_id NUMBER;
1812 ln_prev_tax_unit_id NUMBER;
1813 ld_eff_date DATE;
1814 ld_prev_eff_date DATE;
1815 ln_assignment_action_id NUMBER;
1816 l_context_value VARCHAR2(50);
1817 l_action_information_id_1 NUMBER ;
1818 l_object_version_number_1 NUMBER ;
1819
1820 /* T4A_Registration_no variables part of bug fix 2408456 */
1821 l_check_flag varchar2(2);
1822 l_element_type_id number(20);
1823 l_run_result_id number(20);
1824 l_ele_proc_eff_date date;
1825 l_info_ele_amt varchar2(20);
1826 l_ele_classification_id number(20);
1827 l_ele_classification_name varchar2(50);
1828
1829 lv_emplr_regno varchar2(20);
1830 lv_emplr_regno1 varchar2(20);
1831 lv_emplr_regno2 varchar2(20);
1832 ln_emplr_regamt number(30);
1833 ln_emplr_regamt1 number(30);
1834 ln_emplr_regamt2 number(30);
1835
1836 lv_footnote_element varchar2(50);
1837 l_transmitter_gre_id number;
1838
1839 l_status_indian varchar2(1);
1840 /* cursor used to archive the footnote code values */
1841 cursor c_balance_feed_info (p_balance_name varchar2) is
1842 select distinct pet.element_information18,
1843 pbt1.balance_name
1844 from pay_balance_feeds_f pbf,
1845 pay_balance_types pbt,
1846 pay_balance_types pbt1,
1847 pay_input_values_f piv,
1848 pay_element_types_f pet,
1849 fnd_lookup_values flv
1850 where pbt.balance_name = p_balance_name
1851 and pbf.balance_type_id = pbt.balance_type_id
1852 and pbf.input_value_id = piv.input_value_id
1853 and piv.element_type_id = pet.element_type_id
1854 and pet.business_group_id = l_business_group_id
1855 and pbt1.balance_type_id = pet.element_information10
1856 and pet.element_information18 = flv.lookup_code
1857 and flv.lookup_type = 'PAY_CA_T4A_FOOTNOTES'
1858 and flv.language = userenv('LANG')
1859 order by pet.element_information18;
1860
1861 /* cursor used to archive the Pension Adjustment Registration Number */
1862 cursor c_reg_balance_feed_info (p_balance_name varchar2) is
1863 select nvl(pet.element_information20,'NOT FOUND'),
1864 pbt1.balance_name,pet.element_type_id,
1865 pet.classification_id
1866 from pay_balance_feeds_f pbf,
1867 pay_balance_types pbt,
1868 pay_balance_types pbt1,
1869 pay_input_values_f piv,
1870 pay_element_types_f pet
1871 where pbt.balance_name = p_balance_name
1872 and pbf.balance_type_id = pbt.balance_type_id
1873 and pbf.input_value_id = piv.input_value_id
1874 and piv.element_type_id = pet.element_type_id
1875 and pet.business_group_id = l_business_group_id
1876 and pbt1.balance_type_id = pet.element_information10
1877 -- and pet.element_information_category = 'CA_EARNINGS'
1878 and pet.element_information20 is not null;
1879
1880 /* Cursor for T4A Nonbox Footnote archive to fix bug#2175045 */
1881 /* Modified the cur_non_box_mesg cursor to fix bug#3641353.
1882 Kept the Jurisdiction context validation because of performance
1883 for T4A Reporting and added action_type 'B' Balance Adj's */
1884 CURSOR cur_non_box_mesg( cp_asgact_id in number,
1885 cp_eff_date in date) is
1886 select distinct prrv1.result_value,
1887 prrv2.result_value,
1888 hoi.organization_id,
1889 run_ppa.effective_date,
1890 run_paa.assignment_action_id
1891 from pay_run_result_values prrv1
1892 , pay_run_result_values prrv2
1893 , pay_run_results prr
1894 , pay_element_types_f pet
1895 , pay_input_values_f piv1
1896 , pay_input_values_f piv2
1897 , pay_assignment_actions run_paa
1898 , pay_payroll_actions run_ppa
1899 , pay_assignment_actions arch_paa
1900 , pay_payroll_actions arch_ppa
1901 , per_all_assignments_f arch_paf
1902 , per_all_assignments_f all_paf
1903 , hr_all_organization_units hou
1904 , hr_organization_information hoi
1905 where arch_paa.assignment_action_id = cp_asgact_id
1906 and arch_ppa.payroll_action_id = arch_paa.payroll_action_id
1907 and hou.business_group_id = arch_ppa.business_group_id
1908 and hou.organization_id = hoi.organization_id
1909 and hoi.organization_id =
1910 to_number(pycadar_pkg.get_parameter('TRANSFER_GRE',arch_ppa.legislative_parameters))
1911 and hoi.org_information_context = 'Canada Employer Identification'
1912 and hoi.org_information5 IN ('T4A/RL1','T4A/RL2')
1913 and run_paa.tax_unit_id = hou.organization_id
1914 and run_ppa.payroll_action_id = run_paa.payroll_action_id
1915 and run_ppa.action_type in ( 'R', 'Q', 'B' )
1916 and to_char(run_ppa.effective_date,'YYYY' ) =
1917 to_char(cp_eff_date,'YYYY')
1918 and run_paa.action_status = 'C'
1919 and pet.element_name = lv_footnote_element --'T4A NonBox Footnotes'
1920 and prr.assignment_action_id = run_paa.assignment_action_id
1921 and prr.element_type_id = pet.element_type_id
1922 and piv1.element_type_id = pet.element_type_id
1923 and piv1.name = 'Message'
1924 and prrv1.run_result_id = prr.run_result_id
1925 and prrv1.input_value_id = piv1.input_value_id
1926 and piv2.element_type_id = pet.element_type_id
1927 and piv2.name = 'Amount'
1928 and prrv2.run_result_id = prrv1.run_result_id
1929 and prrv2.input_value_id = piv2.input_value_id
1930 and arch_paf.assignment_id = arch_paa.assignment_id
1931 and to_char(cp_eff_date,'YYYY')
1932 between to_char(arch_paf.effective_start_date,'YYYY')
1933 and to_char(arch_paf.effective_end_date,'YYYY')
1934 and all_paf.person_id = arch_paf.person_id
1935 and to_char(cp_eff_date,'YYYY')
1936 between to_char(all_paf.effective_start_date,'YYYY')
1937 and to_char(all_paf.effective_end_date,'YYYY')
1938 and run_paa.assignment_id = all_paf.assignment_id
1939 and exists (select 1
1940 from pay_action_contexts pac,ff_contexts ffc
1941 where ffc.context_name = 'JURISDICTION_CODE'
1942 and pac.context_id = ffc.context_id
1943 and pac.assignment_id = run_paa.assignment_id);
1944
1945
1946 /* Cursor to check the Employer Level PP Registration Number
1947 Bug fix#2696309 */
1948 CURSOR c_get_emplr_reg_no(cp_tax_unit_id varchar2
1949 ,cp_payroll_action_id number
1950 ,cp_reg_no varchar2
1951 ,cp_eff_date date) IS
1952 select action_information4,to_number(action_information5)
1953 from pay_action_information
1954 where action_context_id = cp_payroll_action_id
1955 and effective_date = cp_eff_date
1956 AND tax_unit_id = cp_tax_unit_id
1957 and action_information_category = 'CAEOY PENSION PLAN INFO'
1958 AND ACTION_INFORMATION4 = cp_reg_no;
1959
1960 CURSOR c_get_latest_asg(p_person_id number ) IS
1961 select paa.assignment_action_id
1962 from pay_assignment_actions paa,
1963 per_all_assignments_f paf,
1964 pay_payroll_actions ppa,
1965 pay_action_classifications pac
1966 where paf.person_id = p_person_id
1967 and paa.assignment_id = paf.assignment_id
1968 and paa.tax_unit_id = l_tax_unit_id
1969 and paa.payroll_action_id = ppa.payroll_action_id
1970 and ppa.action_type = pac.action_type
1971 and pac.classification_name = 'SEQUENCED'
1972 and ppa.effective_date +0 between paf.effective_start_date
1973 and paf.effective_end_date
1974 and ppa.effective_date +0 between l_year_start and
1975 l_year_end
1976 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
1977 and paa.source_action_id is null)
1978 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
1979 and paa.source_action_id is not null )
1980 or (ppa.action_type = 'V' and ppa.run_type_id is null
1981 and paa.run_type_id is not null
1982 and paa.source_action_id is null))
1983 order by paa.action_sequence desc;
1984
1985
1986 /* New cursors added for Federal YE Amendment Pre-Process Validation */
1987
1988 CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
1989 select ppa.report_type
1990 from pay_payroll_actions ppa,pay_assignment_actions paa
1991 where paa.assignment_action_id = cp_locked_actid
1992 and ppa.payroll_action_id = paa.payroll_action_id;
1993
1994 CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
1995 select locked_action_id
1996 from pay_action_interlocks
1997 where locking_action_id = cp_locking_act_id;
1998
1999 /* New cursor for checking for the employee been a Status Indian */
2000 CURSOR c_get_status_indian(cp_assign number,
2001 cp_effec_date date) IS
2002 select ca_tax_information1
2003 from pay_ca_emp_fed_tax_info_f pca
2004 where pca.assignment_id = cp_assign
2005 and cp_effec_date between pca.effective_start_date and
2006 pca.effective_end_date;
2007
2008 begin
2009
2010 -- hr_utility.trace_on('Y','ORACLEMM');
2011
2012 l_count := 0;
2013 l_box38_footnote_code := '00';
2014 l_negative_balance_exists := 'N';
2015
2016 hr_utility.set_location ('archive_data',1);
2017 hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
2018
2019
2020 SELECT aa.assignment_id,
2021 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2022 aa.tax_unit_id,
2023 aa.chunk_number,
2024 aa.payroll_action_id,
2025 aa.serial_number
2026 into l_asgid,
2027 l_date_earned,
2028 l_tax_unit_id,
2029 l_chunk,
2030 l_payroll_action_id,
2031 lv_serial_number
2032 FROM pay_assignment_actions aa
2033 WHERE aa.assignment_action_id = p_assactid;
2034
2035 /*Bug 4021563 Fetching the Status Indian flag */
2036 open c_get_status_indian(l_asgid,p_effective_date);
2037 fetch c_get_status_indian
2038 into l_status_indian;
2039 close c_get_status_indian;
2040
2041 /* Call the archive_gre_data procedure */
2042 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2043 select org_information11
2044 into l_transmitter_gre_id
2045 from hr_organization_information
2046 where organization_id = l_tax_unit_id
2047 and org_information_context = 'Canada Employer Identification';
2048
2049 l_step := 3;
2050 hr_utility.trace('eoy_archive_data archiving employer data');
2051 eoy_archive_gre_data(l_payroll_action_id,
2052 l_tax_unit_id,
2053 l_transmitter_gre_id);
2054
2055 l_step := 4;
2056 hr_utility.trace('eoy_archive_data archived employer data');
2057 else
2058 g_archive_flag := 'Y';
2059 end if;
2060
2061
2062 l_year_start := trunc(p_effective_date, 'Y');
2063 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2064
2065 if to_number(to_char(l_year_end,'YYYY')) > 2005 then
2066 lv_footnote_element := 'T4A Non Box Footnotes';
2067 else
2068 lv_footnote_element := 'T4A NonBox Footnotes';
2069 end if;
2070
2071 hr_utility.trace('l_date_earned : '|| to_char(l_date_earned));
2072
2073 /* YE-2001 change to avoid hr_ca_tax_units_v view */
2074 select business_group_id
2075 into l_business_group_id
2076 from hr_all_organization_units
2077 where organization_id = l_tax_unit_id;
2078
2079 l_step := 1;
2080
2081 /*
2082 select paa1.assignment_action_id
2083 into l_aaid
2084 from pay_assignment_actions paa1,
2085 per_all_assignments_f paf2
2086 where paa1.assignment_id = paf2.assignment_id
2087 and paa1.tax_unit_id = l_tax_unit_id
2088 and (paa1.action_sequence , paf2.person_id) =
2089 (SELECT MAX(paa.action_sequence), paf.person_id
2090 FROM pay_action_classifications pac,
2091 pay_payroll_actions ppa,
2092 pay_assignment_actions paa,
2093 per_all_assignments_f paf1,
2094 per_all_assignments_f paf
2095 WHERE paf.assignment_id = l_asgid
2096 AND paf1.person_id = paf.person_id
2097 AND paa.tax_unit_id = l_tax_unit_id
2098 AND paa.assignment_id = paf1.assignment_id
2099 AND paa.payroll_action_id = ppa.payroll_action_id
2100 AND ppa.action_type = pac.action_type
2101 AND pac.classification_name = 'SEQUENCED'
2102 AND ppa.effective_date <= p_effective_date
2103 group by paf.person_id)
2104 and rownum < 2;
2105 */
2106 begin
2107 open c_get_latest_asg(lv_serial_number );
2108 fetch c_get_latest_asg into l_aaid;
2109 close c_get_latest_asg;
2110 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2111
2112 exception
2113 when no_data_found then
2114 l_aaid := -9999;
2115 raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2116 ||to_char(l_person_id));
2117 end;
2118 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2119 hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2120 hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2121
2122
2123
2124 /* Assign values to the PL/SQL tables */
2125
2126 l_step := 16;
2127
2128
2129 l_seq_tab(2) := 2;
2130 l_context_id_tab(2) := l_taxunit_context_id;
2131 l_context_val_tab(2) := l_tax_unit_id;
2132
2133 /*
2134 l_count := l_count + 1;
2135 l_user_entity_name_tab(l_count) := 'CAEOY_GROSS_EARNINGS_PER_GRE_YTD';
2136 l_balance_type_tab(l_count) := 'Gross Earnings';
2137 */
2138
2139 l_count := l_count + 1;
2140 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX16_PER_GRE_YTD';
2141 l_balance_type_tab(l_count) := 'T4A_BOX16';
2142
2143 l_count := l_count + 1;
2144 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX18_PER_GRE_YTD';
2145 l_balance_type_tab(l_count) := 'T4A_BOX18';
2146
2147 l_count := l_count + 1;
2148 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX20_PER_GRE_YTD';
2149 l_balance_type_tab(l_count) := 'T4A_BOX20';
2150
2151 l_count := l_count + 1;
2152 l_user_entity_name_tab(l_count) := 'CAEOY_FED_WITHHELD_PER_GRE_YTD';
2153 l_balance_type_tab(l_count) := 'FED Withheld';
2154
2155 l_count := l_count + 1;
2156 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX24_PER_GRE_YTD';
2157 l_balance_type_tab(l_count) := 'T4A_BOX24';
2158
2159 l_count := l_count + 1;
2160 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX26_PER_GRE_YTD';
2161 l_balance_type_tab(l_count) := 'T4A_BOX26';
2162
2163 l_count := l_count + 1;
2164 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX27_PER_GRE_YTD';
2165 l_balance_type_tab(l_count) := 'T4A_BOX27';
2166
2167 l_count := l_count + 1;
2168 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX28_PER_GRE_YTD';
2169 l_balance_type_tab(l_count) := 'T4A_BOX28';
2170
2171 l_count := l_count + 1;
2172 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX30_PER_GRE_YTD';
2173 l_balance_type_tab(l_count) := 'T4A_BOX30';
2174
2175 l_count := l_count + 1;
2176 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX32_PER_GRE_YTD';
2177 l_balance_type_tab(l_count) := 'T4A_BOX32';
2178
2179 l_count := l_count + 1;
2180 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX34_PER_GRE_YTD';
2181 l_balance_type_tab(l_count) := 'T4A_BOX34';
2182 /*
2183 l_count := l_count + 1;
2184 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX36_PER_GRE_YTD';
2185 l_balance_type_tab(l_count) := 'T4A_BOX36';
2186 */
2187 l_count := l_count + 1;
2188 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX40_PER_GRE_YTD';
2189 l_balance_type_tab(l_count) := 'T4A_BOX40';
2190
2191 l_count := l_count + 1;
2192 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX42_PER_GRE_YTD';
2193 l_balance_type_tab(l_count) := 'T4A_BOX42';
2194
2195 l_count := l_count + 1;
2196 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX46_PER_GRE_YTD';
2197 l_balance_type_tab(l_count) := 'T4A_BOX46';
2198
2199 /* Initializing variables as part of bug fix#2426517 */
2200 l_box38_footnote_code := '00';
2201 l_box38_count := 0;
2202
2203 if ( (pay_ca_balance_pkg.call_ca_balance_get_value
2204 ( 'Gross Earnings',
2205 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
2206 l_tax_unit_id, l_business_group_id, NULL)
2207 <> 0) OR
2208 (pay_ca_balance_pkg.call_ca_balance_get_value
2209 ( 'T4A No Gross Earnings',
2210 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
2211 l_tax_unit_id, l_business_group_id, NULL)
2212 <> 0) ) then
2213
2214 earning_exists := 1;
2215
2216 hr_utility.trace('starting loop for balances');
2217
2218 for i in 1 .. l_count
2219 loop
2220 result := 0;
2221 /* Now, set up the jurisdiction context for the db items that
2222 need the jurisdiction as a context.Here we are archiving all the
2223 jurisdictions we got from pay_action_contexts for all
2224 assignment_actions. So even though a particular assignment_action
2225 is for aparticular jurisdiction the archiver table has data for
2226 all the jurisdictions, but values of irrelevant jurisdictions will
2227 be 0 */
2228
2229 /* To get balances you must use the highest assignment action . Since
2230 T4A does not have Jurisdiction specific balances first we have to
2231 sum up balances for all jurisdictions. */
2232
2233 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2234 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2235
2236 hr_utility.trace('i is ' ||to_char(i));
2237 hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2238 hr_utility.trace('AAID is ' || to_char(l_aaid));
2239 hr_utility.trace('ASGID is ' || to_char(l_asgid));
2240 hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2241 hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2242
2243 result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2244 ( l_balance_type_tab(i),
2245 'YTD' ,
2246 l_aaid,
2247 l_asgid,
2248 NULL,
2249 'PER' ,
2250 l_tax_unit_id,
2251 l_business_group_id,
2252 NULL
2253 );
2254
2255 /* start footnote archiving */
2256
2257 l_footnote_code := NULL;
2258 l_footnote_balance := NULL;
2259 l_footnote_amount := 0;
2260 old_l_footnote_code := NULL;
2261 l_footnote_code_ue := NULL;
2262 l_box38_footnote_code_ue:= NULL;
2263 l_footnote_amount_ue := NULL;
2264 l_no_of_fn_codes := 0;
2265 l_value := 0;
2266 old_l_value := 0;
2267 old_l_value1 := 0;
2268 old_l_value2 := 0;
2269 l_count := 0;
2270 l_single_footnote_code := NULL;
2271
2272 if result <> 0 then
2273
2274 /* hr_utility.trace_on('Y','T4AARCH'); */
2275 hr_utility.trace('Result is ' || to_char(result));
2276 /* Check the footnote amounts and codes and archive them */
2277 /* Check which elements has fed the balance and what are their
2278 footnotes and if the primary balance for that element is 0 or not */
2279
2280 if l_balance_type_tab(i) in ( 'T4A_BOX16',
2281 'T4A_BOX18',
2282 'T4A_BOX24',
2283 'T4A_BOX26',
2284 'T4A_BOX27',
2285 'T4A_BOX28',
2286 'T4A_BOX32',
2287 'T4A_BOX40') then
2288 begin
2289 hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
2290 hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
2291 hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292 hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293 hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294 hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2295 hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
2296 hr_utility.trace('l_box38_count :'||l_box38_count);
2297
2298 open c_balance_feed_info(l_balance_type_tab(i));
2299
2300 hr_utility.trace('balance_type '||l_balance_type_tab(i));
2301
2302 loop
2303
2304 hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
2305 hr_utility.trace('666 l_count '|| l_count);
2306 hr_utility.trace('666 p_assactid '|| p_assactid);
2307 fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
2308 if l_balance_type_tab(i) = 'T4A_BOX24'
2309 and l_footnote_code = '10(BOX24)' then
2310 l_footnote_code := '10A';
2311 end if;
2312 exit when c_balance_feed_info%NOTFOUND;
2313 l_count := l_count + 1;
2314
2315 hr_utility.trace('l_footnote_balance '||l_footnote_balance);
2316 hr_utility.trace('l_footnotecode '||l_footnote_code);
2317 hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
2318 if l_footnote_code <> old_l_footnote_code then /* footnote
2319 changed */
2320 if old_l_footnote_code is not null then /* not the first
2321 record */
2322
2323 hr_utility.trace('archive ft_amount_ue'
2324 ||l_footnote_amount_ue);
2325 hr_utility.trace('archive ft_amount'
2326 ||to_char(l_footnote_amount));
2327 if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
2328 and l_footnote_amount <> 0 then
2329
2330 l_footnote_amount_ue := 'CAEOY_' || l_balance_type_tab(i) ||'_'||old_l_footnote_code||'_AMT_PER_GRE_YTD';
2331
2332 ff_archive_api.create_archive_item(
2333 p_archive_item_id => l_archive_item_id
2334 ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_amount_ue)
2335 ,p_archive_value => l_footnote_amount
2336 ,p_archive_type => 'AAP'
2337 ,p_action_id => p_assactid
2338 ,p_legislation_code => 'CA'
2339 ,p_object_version_number => l_object_version_number
2340 ,p_context_name1 => 'TAX_UNIT_ID'
2341 ,p_context1 => l_tax_unit_id
2342 ,p_some_warning => l_some_warning
2343 );
2344
2345 l_no_of_fn_codes := l_no_of_fn_codes + 1;
2346 l_box38_count := l_box38_count + 1;
2347 l_single_footnote_code := old_l_footnote_code;
2348
2349 if l_footnote_amount < 0 then
2350 l_negative_balance_exists := 'Y';
2351 end if;
2352
2353 end if;
2354 l_footnote_amount := 0;
2355 old_l_footnote_code := l_footnote_code ;
2356 end if;
2357 end if; /* end of if l_footnote_code <> old_l_footnote_code */
2358
2359 old_l_footnote_code := l_footnote_code ;
2360 l_footnote_amount_ue := 'CAEOY_' || l_balance_type_tab(i) ||'_'||old_l_footnote_code||'_AMT_PER_GRE_YTD';
2361
2362
2363 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2364 ( l_footnote_balance,
2365 'YTD' ,
2366 l_aaid,
2367 l_asgid,
2368 NULL,
2369 'PER' ,
2370 l_tax_unit_id,
2371 l_business_group_id,
2372 NULL );
2373
2374 hr_utility.trace('666 l_footnote_balance '|| l_footnote_balance);
2375 hr_utility.trace('666 l_value '|| l_value);
2376 l_footnote_amount := l_footnote_amount + l_value ;
2377
2378 /* to fix bug#2426517 added one more validation to if stmt */
2379 /* if (l_value <> 0 and
2380 get_footnote_user_entity_id(l_footnote_amount_ue) <> 0 ) then
2381
2382 l_no_of_fn_codes := l_no_of_fn_codes + 1;
2383 l_box38_count := l_box38_count + 1;
2384 l_single_footnote_code := l_footnote_code;
2385
2386 hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
2387 hr_utility.trace('chk l_box38_count '|| l_box38_count);
2388 hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
2389 end if;
2390 */
2391 hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
2392 end loop;
2393 close c_balance_feed_info;
2394
2395 if l_footnote_code is not null and
2396 l_footnote_amount_ue is not null and
2397 l_footnote_amount <> 0 and
2398 get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
2399 then
2400 hr_utility.trace('666archive footnote amount '|| l_footnote_amount);
2401 hr_utility.trace('666archive footnote amount ue'|| l_footnote_amount_ue);
2402
2403 ff_archive_api.create_archive_item(
2404 p_archive_item_id => l_archive_item_id
2405 ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_amount_ue)
2406 ,p_archive_value => l_footnote_amount
2407 ,p_archive_type => 'AAP'
2408 ,p_action_id => p_assactid
2409 ,p_legislation_code => 'CA'
2410 ,p_object_version_number => l_object_version_number
2411 ,p_context_name1 => 'TAX_UNIT_ID'
2412 ,p_context1 => l_tax_unit_id
2413 ,p_some_warning => l_some_warning
2414 );
2415
2416 l_no_of_fn_codes := l_no_of_fn_codes + 1;
2417 l_box38_count := l_box38_count + 1;
2418 l_single_footnote_code := l_footnote_code;
2419
2420 if l_footnote_amount < 0 then
2421 l_negative_balance_exists := 'Y';
2422 end if;
2423
2424 end if;
2425
2426 hr_utility.trace('666archive l_no_of_fn_codes '|| l_no_of_fn_codes);
2427 if l_no_of_fn_codes > 1 then
2428 l_footnote_code := '13';
2429 hr_utility.trace('666archive footnote code '|| l_footnote_code);
2430 /* changed here as part of bugfix#2426517 */
2431 elsif l_no_of_fn_codes = 1 then
2432 l_footnote_code := l_single_footnote_code;
2433 hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
2434 elsif l_no_of_fn_codes = 0 then
2435 l_footnote_code := '00';
2436 hr_utility.trace('666archive footnote code '|| l_footnote_code);
2437 end if;
2438
2439 l_footnote_code_ue := 'CAEOY_' || l_balance_type_tab(i) || '_FOOTNOTE_CODE';
2440
2441 hr_utility.trace('before archiving l_footnote_code_ue is '|| l_footnote_code_ue);
2442 /* Part of fix for bug#2426517, to avoid unnecessary archiving
2443 of footnote code added one more condiftion to if stmt before
2444 archiving the footnote code for the corresponding BOX balance */
2445
2446 if l_footnote_code is not null and l_no_of_fn_codes > 0 and
2447 get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
2448
2449 hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
2450 hr_utility.trace('l_footnote_code:'|| l_footnote_code);
2451 hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
2452 ff_archive_api.create_archive_item(
2453 -- p_validate => 'TRUE'
2454 p_archive_item_id => l_archive_item_id
2455 ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_code_ue)
2456 ,p_archive_value => l_footnote_code
2457 ,p_archive_type => 'AAP'
2458 ,p_action_id => p_assactid
2459 ,p_legislation_code => 'CA'
2460 ,p_object_version_number => l_object_version_number
2461 ,p_context_name1 => 'TAX_UNIT_ID'
2462 ,p_context1 => l_tax_unit_id
2463 ,p_some_warning => l_some_warning
2464 );
2465 end if;
2466
2467 /* assigning value to box38_footnote_code */
2468
2469 hr_utility.trace('999 l_box38_count '|| l_box38_count);
2470 /* initialised l_box38_footnote_code before checking
2471 gross earnings to this assignment action fix#2426517 */
2472
2473 if l_box38_count > 1 then
2474 l_box38_footnote_code := '13';
2475 hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
2476 /* Added one more condition to archive correct footnote code
2477 value for box38 as part of bug fix#2426517 and assigned
2478 l_single_footnote_code to l_box38_footnote_code variable */
2479 elsif l_box38_count = 1 and l_no_of_fn_codes > 0 then
2480 l_box38_footnote_code := l_single_footnote_code;
2481 hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
2482 end if;
2483
2484 end;
2485 end if;
2486 end if;
2487 /** End of Footnote archiving **/
2488
2489 --hr_utility.trace_off;
2490
2491 hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2492 hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2493 hr_utility.trace('Result is ' || to_char(result));
2494
2495 /* Added this condition to fix bug#2598777 */
2496 if l_user_entity_name_tab(i) = 'CAEOY_T4A_BOX34_PER_GRE_YTD' then
2497 result := round(result);
2498 end if;
2499 /* Bug 4021563 Added code for Status Indian type employee */
2500 if (l_balance_type_tab(i) in ( 'T4A_BOX16', 'T4A_BOX18',
2501 'T4A_BOX26',
2502 'T4A_BOX27',
2503 'T4A_BOX28') and l_status_indian = 'Y') then
2504 result := 0;
2505
2506 end if;
2507 ff_archive_api.create_archive_item(
2508 p_archive_item_id => l_archive_item_id
2509 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
2510 ,p_archive_value => result
2511 ,p_archive_type => 'AAP'
2512 ,p_action_id => p_assactid
2513 ,p_legislation_code => 'CA'
2514 ,p_object_version_number => l_object_version_number
2515 ,p_context_name1 => 'TAX_UNIT_ID'
2516 ,p_context1 => l_tax_unit_id
2517 ,p_some_warning => l_some_warning
2518 );
2519
2520 if result < 0 then
2521 l_negative_balance_exists := 'Y';
2522 end if;
2523
2524 end loop; /* for archiving all T4A Balances */
2525
2526 /** Start box38 footnote archiving **/
2527 hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2528 hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
2529 hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
2530
2531 /** box38 footnote archive has been moved after nonbox footnote archive **/
2532
2533 /* start registration number archiving */
2534
2535 l_registration_no := NULL;
2536 old_l_registration_no := NULL;
2537 arch_l_registration_no := NULL;
2538 old_l_value := 0;
2539 old_l_registration_no1 := NULL;
2540 old_l_value1 := 0;
2541 old_l_registration_no2 := NULL;
2542 old_l_value2 := 0;
2543 arch_l_value := 0;
2544 l_value := 0;
2545
2546 begin
2547
2548 open c_reg_balance_feed_info('T4A_BOX34');
2549
2550 loop
2551
2552 fetch c_reg_balance_feed_info into l_registration_no,l_balance_name,
2553 l_element_type_id,l_ele_classification_id;
2554 exit when c_reg_balance_feed_info%NOTFOUND;
2555
2556 hr_utility.trace('checking for T4A_BOX34');
2557 hr_utility.trace('p_assactid:'||to_char(p_assactid));
2558 hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559 hr_utility.trace('l_registration_no:'||l_registration_no);
2560 hr_utility.trace('l_balance_name:'||l_balance_name);
2561 hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2562 hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
2563 hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
2564
2565 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2566 ( l_balance_name,
2567 'YTD' ,
2568 l_aaid,
2569 l_asgid,
2570 NULL,
2571 'PER' ,
2572 l_tax_unit_id,
2573 l_business_group_id,
2574 NULL );
2575
2576 hr_utility.trace('before check null l_value:'||to_char(l_value));
2577 if l_value is null then
2578 l_value := 0;
2579 end if;
2580
2581 hr_utility.trace('after check null l_value:'||to_char(l_value));
2582
2583
2584 /* Condition to check the amounts and determine the registration
2585 number to archive Bug fix 2408456 */
2586 if old_l_value = 0 then
2587 hr_utility.trace('in reg1');
2588 old_l_value := l_value;
2589 old_l_registration_no := l_registration_no;
2590 elsif old_l_value1 = 0 then
2591 hr_utility.trace('in reg2');
2592 old_l_value1 := l_value;
2593 old_l_registration_no1 := l_registration_no;
2594 elsif old_l_value2 = 0 then
2595 hr_utility.trace('in reg3');
2596 old_l_value2 := l_value;
2597 old_l_registration_no2 := l_registration_no;
2598 else
2599 if l_value > nvl(old_l_value,0) then
2600 hr_utility.trace('old_l1');
2601 old_l_value := l_value;
2602 old_l_registration_no := l_registration_no;
2603 elsif l_value > nvl(old_l_value1,0) then
2604 hr_utility.trace('old_2');
2605 old_l_value1 := l_value;
2606 old_l_registration_no1 := l_registration_no;
2607 elsif l_value > nvl(old_l_value2,0) then
2608 old_l_value2 := l_value;
2609 old_l_registration_no2 := l_registration_no;
2610 end if;
2611 end if;
2612 /* End of Condition to check amounts Bug fix 2408456 */
2613
2614 end loop;
2615 close c_reg_balance_feed_info;
2616 if old_l_value > old_l_value1 then
2617 hr_utility.trace('in reg4');
2618 if old_l_value> old_l_value2 then
2619 arch_l_registration_no := old_l_registration_no;
2620 arch_l_value := old_l_value;
2621 else
2622 arch_l_registration_no := old_l_registration_no2;
2623 arch_l_value := old_l_value2;
2624 end if;
2625 else
2626 if old_l_value1>old_l_value2 then
2627 arch_l_registration_no := old_l_registration_no1;
2628 arch_l_value := old_l_value1;
2629 else
2630 arch_l_registration_no := old_l_registration_no2;
2631 arch_l_value := old_l_value2;
2632 end if;
2633 end if;
2634
2635 /* archive registration number derived from T4A_BOX34 */
2636 if arch_l_registration_no is not null and arch_l_value > 0 then
2637
2638 ff_archive_api.create_archive_item(
2639 -- p_validate => 'TRUE'
2640 p_archive_item_id => l_archive_item_id
2641 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_EMPLOYEE_REGISTRATION_NO')
2642 ,p_archive_value => arch_l_registration_no
2643 ,p_archive_type => 'AAP'
2644 ,p_action_id => p_assactid
2645 ,p_legislation_code => 'CA'
2646 ,p_object_version_number => l_object_version_number
2647 ,p_some_warning => l_some_warning
2648 );
2649 end if;
2650
2651 /* Bug fix#2696309, Employer level Pension Plan Register Number */
2652 hr_utility.trace('Start of Employer Level PP Reg no ');
2653
2654 if old_l_registration_no is not null and old_l_value >0 then
2655 hr_utility.trace('in reg1 pay_action_information');
2656 hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2657 hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2658
2659 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2660 l_payroll_action_id
2661 ,old_l_registration_no
2662 ,p_effective_date);
2663 fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
2664 if c_get_emplr_reg_no%FOUND then
2665 hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
2666
2667 ln_emplr_regamt := ln_emplr_regamt + old_l_value;
2668
2669 update pay_action_information
2670 set action_information5 = to_char(ln_emplr_regamt)
2671 where action_context_id = l_payroll_action_id
2672 and tax_unit_id = l_tax_unit_id
2673 and effective_date = p_effective_date
2674 and action_information_category = 'CAEOY PENSION PLAN INFO'
2675 AND ACTION_INFORMATION4 = old_l_registration_no;
2676
2677
2678 else
2679
2680 hr_utility.trace('in reg1 insert pay_action_information');
2681 -- insert a new record into pay_action_information
2682
2683 pay_action_information_api.create_action_information(
2684 p_action_information_id => l_action_information_id_1,
2685 p_object_version_number => l_object_version_number_1,
2686 p_action_information_category => 'CAEOY PENSION PLAN INFO',
2687 p_action_context_id => l_payroll_action_id,
2688 p_action_context_type => 'PA',
2689 p_jurisdiction_code => NULL,
2690 p_tax_unit_id => l_tax_unit_id,
2691 p_effective_date => p_effective_date,
2692 p_action_information1 => NULL,
2693 p_action_information2 => NULL,
2694 p_action_information3 => NULL,
2695 p_action_information4 => old_l_registration_no,
2696 p_action_information5 => to_char(old_l_value),
2697 p_action_information6 => NULL,
2698 p_action_information7 => NULL,
2699 p_action_information8 => NULL,
2700 p_action_information9 => NULL,
2701 p_action_information10 => NULL,
2702 p_action_information11 => NULL,
2703 p_action_information12 => NULL,
2704 p_action_information13 => NULL,
2705 p_action_information14 => NULL,
2706 p_action_information15 => NULL,
2707 p_action_information16 => NULL,
2708 p_action_information17 => NULL,
2709 p_action_information18 => NULL,
2710 p_action_information19 => NULL,
2711 p_action_information20 => NULL,
2712 p_action_information21 => NULL,
2713 p_action_information22 => NULL,
2714 p_action_information23 => NULL,
2715 p_action_information24 => NULL,
2716 p_action_information25 => NULL,
2717 p_action_information26 => NULL,
2718 p_action_information27 => NULL,
2719 p_action_information28 => NULL,
2720 p_action_information29 => NULL,
2721 p_action_information30 => NULL
2722 );
2723
2724 end if; -- c_get_emplr_reg_no%FOUND
2725 close c_get_emplr_reg_no;
2726 end if;
2727
2728 if old_l_registration_no1 is not null and old_l_value1 >0 then
2729 hr_utility.trace('in reg2 pay_action_information');
2730
2731 hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2732 hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2733
2734 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2735 l_payroll_action_id
2736 , old_l_registration_no1
2737 ,p_effective_date);
2738 fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
2739 if c_get_emplr_reg_no%FOUND then
2740
2741 hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
2742
2743 ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
2744
2745 update pay_action_information
2746 set action_information5 = to_char(ln_emplr_regamt1)
2747 where action_context_id = l_payroll_action_id
2748 and tax_unit_id = l_tax_unit_id
2749 and effective_date = p_effective_date
2750 and action_information_category = 'CAEOY PENSION PLAN INFO'
2751 AND ACTION_INFORMATION4 = old_l_registration_no1;
2752
2753 else
2754 hr_utility.trace('in reg2 insert pay_action_information');
2755
2756 -- insert a new record into pay_action_information
2757
2758 pay_action_information_api.create_action_information(
2759 p_action_information_id => l_action_information_id_1,
2760 p_object_version_number => l_object_version_number_1,
2761 p_action_information_category => 'CAEOY PENSION PLAN INFO',
2762 p_action_context_id => l_payroll_action_id,
2763 p_action_context_type => 'PA',
2764 p_jurisdiction_code => NULL,
2765 p_tax_unit_id => l_tax_unit_id,
2766 p_effective_date => p_effective_date,
2767 p_action_information1 => NULL,
2768 p_action_information2 => NULL,
2769 p_action_information3 => NULL,
2770 p_action_information4 => old_l_registration_no1,
2771 p_action_information5 => to_char(old_l_value1),
2772 p_action_information6 => NULL,
2773 p_action_information7 => NULL,
2774 p_action_information8 => NULL,
2775 p_action_information9 => NULL,
2776 p_action_information10 => NULL,
2777 p_action_information11 => NULL,
2778 p_action_information12 => NULL,
2779 p_action_information13 => NULL,
2780 p_action_information14 => NULL,
2781 p_action_information15 => NULL,
2782 p_action_information16 => NULL,
2783 p_action_information17 => NULL,
2784 p_action_information18 => NULL,
2785 p_action_information19 => NULL,
2786 p_action_information20 => NULL,
2787 p_action_information21 => NULL,
2788 p_action_information22 => NULL,
2789 p_action_information23 => NULL,
2790 p_action_information24 => NULL,
2791 p_action_information25 => NULL,
2792 p_action_information26 => NULL,
2793 p_action_information27 => NULL,
2794 p_action_information28 => NULL,
2795 p_action_information29 => NULL,
2796 p_action_information30 => NULL
2797 );
2798
2799 end if; -- c_get_emplr_reg_no%FOUND
2800 close c_get_emplr_reg_no;
2801 end if;
2802
2803 if old_l_registration_no2 is not null and old_l_value2 > 0 then
2804 hr_utility.trace('in reg3 pay_action_information');
2805 hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2806 hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2807 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2808 l_payroll_action_id
2809 , old_l_registration_no2
2810 ,p_effective_date);
2811 fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
2812 if c_get_emplr_reg_no%FOUND then
2813
2814 hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
2815
2816 ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
2817
2818 update pay_action_information
2819 set action_information5 = to_char(ln_emplr_regamt2)
2820 where action_context_id = l_payroll_action_id
2821 and tax_unit_id = l_tax_unit_id
2822 and effective_date = p_effective_date
2823 and action_information_category = 'CAEOY PENSION PLAN INFO'
2824 AND ACTION_INFORMATION4 = old_l_registration_no2;
2825
2826 hr_utility.trace('Updated pay_action_information');
2827
2828 else
2829 hr_utility.trace('in reg3 insert pay_action_information');
2830
2831 -- insert a new record into pay_action_information
2832
2833 pay_action_information_api.create_action_information(
2834 p_action_information_id => l_action_information_id_1,
2835 p_object_version_number => l_object_version_number_1,
2836 p_action_information_category => 'CAEOY PENSION PLAN INFO',
2837 p_action_context_id => l_payroll_action_id,
2838 p_action_context_type => 'PA',
2839 p_jurisdiction_code => NULL,
2840 p_tax_unit_id => l_tax_unit_id,
2841 p_effective_date => p_effective_date,
2842 p_action_information1 => NULL,
2843 p_action_information2 => NULL,
2844 p_action_information3 => NULL,
2845 p_action_information4 => old_l_registration_no2,
2846 p_action_information5 => to_char(old_l_value2),
2847 p_action_information6 => NULL,
2848 p_action_information7 => NULL,
2849 p_action_information8 => NULL,
2850 p_action_information9 => NULL,
2851 p_action_information10 => NULL,
2852 p_action_information11 => NULL,
2853 p_action_information12 => NULL,
2854 p_action_information13 => NULL,
2855 p_action_information14 => NULL,
2856 p_action_information15 => NULL,
2857 p_action_information16 => NULL,
2858 p_action_information17 => NULL,
2859 p_action_information18 => NULL,
2860 p_action_information19 => NULL,
2861 p_action_information20 => NULL,
2862 p_action_information21 => NULL,
2863 p_action_information22 => NULL,
2864 p_action_information23 => NULL,
2865 p_action_information24 => NULL,
2866 p_action_information25 => NULL,
2867 p_action_information26 => NULL,
2868 p_action_information27 => NULL,
2869 p_action_information28 => NULL,
2870 p_action_information29 => NULL,
2871 p_action_information30 => NULL
2872 );
2873
2874 end if; -- c_get_emplr_reg_no%FOUND
2875 close c_get_emplr_reg_no;
2876 end if;
2877
2878
2879 /* Added else part to fix bug#2408456
2880 if the registration number doesn't exist for the elements
2881 that are fed to balance T4A_BOX34 then check the elements
2882 that are fed to balance T4A_BOX32 and archive it */
2883
2884 l_registration_no := NULL;
2885 old_l_registration_no := NULL;
2886 arch_l_registration_no := NULL;
2887 old_l_value := 0;
2888 old_l_registration_no1 := NULL;
2889 old_l_value1 := 0;
2890 old_l_registration_no2 := NULL;
2891 old_l_value2 := 0;
2892 arch_l_value := 0;
2893 l_value := 0;
2894
2895 if old_l_registration_no is null or
2896 old_l_registration_no1 is null or
2897 old_l_registration_no2 is null then
2898
2899 l_registration_no := NULL;
2900 old_l_registration_no := NULL; old_l_value := 0;
2901 old_l_registration_no1 := NULL;
2902 old_l_value1 := 0;
2903 old_l_registration_no2 := NULL;
2904 old_l_value2 := 0;
2905 l_value := 0;
2906 arch_l_registration_no := NULL;
2907 arch_l_value := 0;
2908 begin
2909
2910 open c_reg_balance_feed_info('T4A_BOX32');
2911
2912 loop
2913 fetch c_reg_balance_feed_info into l_registration_no,
2914 l_balance_name,l_element_type_id,
2915 l_ele_classification_id;
2916 exit when c_reg_balance_feed_info%NOTFOUND;
2917
2918 hr_utility.trace('checking for T4A_BOX32 ');
2919 hr_utility.trace('p_assactid:'||to_char(p_assactid));
2920 hr_utility.trace('l_asgid:'||to_char(l_asgid));
2921 hr_utility.trace('l_registration_no:'||l_registration_no);
2922 hr_utility.trace('l_balance_name:'||l_balance_name);
2923 hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2924
2925 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2926 ( l_balance_name,
2927 'YTD' ,
2928 l_aaid,
2929 l_asgid,
2930 NULL,
2931 'PER' ,
2932 l_tax_unit_id,
2933 l_business_group_id,
2934 NULL );
2935
2936
2937 hr_utility.trace('l_value:'||to_char(l_value));
2938 if l_value is null then
2939 l_value := 0;
2940 end if;
2941
2942 hr_utility.trace('before checking the new validation ');
2943 hr_utility.trace('l_value :'||to_char(l_value));
2944 hr_utility.trace('l_registration_no:'||l_registration_no);
2945 hr_utility.trace('old_l_value :'||to_char(old_l_value));
2946 hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
2947
2948 /* Condition to check the amounts and determine the
2949 registration number to archive Bug fix 2408456 */
2950
2951 if l_value > nvl(old_l_value,0) then
2952
2953 old_l_value := l_value;
2954 old_l_registration_no := l_registration_no;
2955 elsif l_value > nvl(old_l_value1,0) then
2956 old_l_value1 := l_value;
2957 old_l_registration_no1 := l_registration_no;
2958 elsif l_value > nvl(old_l_value2,0) then
2959 old_l_value2 := l_value;
2960 old_l_registration_no2 := l_registration_no;
2961
2962 end if;
2963 /* End of Condition to check amounts Bug fix 2408456 */
2964
2965 end loop;
2966 close c_reg_balance_feed_info;
2967
2968 if old_l_value > old_l_value1 then
2969 if old_l_value> old_l_value2 then
2970 arch_l_registration_no := old_l_registration_no;
2971 arch_l_value := old_l_value;
2972 else
2973 arch_l_registration_no := old_l_registration_no2;
2974 arch_l_value := old_l_value2;
2975 end if;
2976 else
2977 if old_l_value1>old_l_value2 then
2978 arch_l_registration_no := old_l_registration_no1;
2979 arch_l_value := old_l_value1;
2980 else
2981 arch_l_registration_no := old_l_registration_no2;
2982 arch_l_value := old_l_value2;
2983 end if;
2984 end if;
2985 if arch_l_registration_no is not null and arch_l_value > 0 then
2986
2987 ff_archive_api.create_archive_item(
2988 -- p_validate => 'TRUE'
2989 p_archive_item_id => l_archive_item_id
2990 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_EMPLOYEE_REGISTRATION_NO')
2991 ,p_archive_value => arch_l_registration_no
2992 ,p_archive_type => 'AAP'
2993 ,p_action_id => p_assactid
2994 ,p_legislation_code => 'CA'
2995 ,p_object_version_number => l_object_version_number
2996 ,p_some_warning => l_some_warning
2997 );
2998 end if;
2999 if old_l_registration_no is not null and old_l_value > 0 then
3000
3001
3002 /* Bug fix#2696309, Employer level Pension Plan Register Number */
3003
3004 hr_utility.trace('Start of Employer Level PP Reg no ');
3005 hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
3006 hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
3007 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3008 l_payroll_action_id
3009 ,old_l_registration_no
3010 ,p_effective_date);
3011 fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
3012 if c_get_emplr_reg_no%FOUND then
3013 hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
3014 ln_emplr_regamt := ln_emplr_regamt + old_l_value;
3015
3016 update pay_action_information
3017 set action_information5 = to_char(ln_emplr_regamt)
3018 where action_context_id = l_payroll_action_id
3019 and tax_unit_id = l_tax_unit_id
3020 and effective_date = p_effective_date
3021 and action_information_category = 'CAEOY PENSION PLAN INFO'
3022 AND ACTION_INFORMATION4 = old_l_registration_no;
3023
3024 else
3025 -- insert a new record into pay_action_information
3026
3027 pay_action_information_api.create_action_information(
3028 p_action_information_id => l_action_information_id_1,
3029 p_object_version_number => l_object_version_number_1,
3030 p_action_information_category => 'CAEOY PENSION PLAN INFO',
3031 p_action_context_id => l_payroll_action_id,
3032 p_action_context_type => 'PA',
3033 p_jurisdiction_code => NULL ,
3034 p_tax_unit_id => l_tax_unit_id,
3035 p_effective_date => p_effective_date,
3036 p_assignment_id => l_asgid,
3037 p_action_information1 => NULL,
3038 p_action_information2 => NULL,
3039 p_action_information3 => NULL,
3040 p_action_information4 => old_l_registration_no,
3041 p_action_information5 => to_char(old_l_value),
3042 p_action_information6 => NULL,
3043 p_action_information7 => NULL,
3044 p_action_information8 => NULL,
3045 p_action_information9 => NULL,
3046 p_action_information10 => NULL,
3047 p_action_information11 => NULL,
3048 p_action_information12 => NULL,
3049 p_action_information13 => NULL,
3050 p_action_information14 => NULL,
3051 p_action_information15 => NULL,
3052 p_action_information16 => NULL,
3053 p_action_information17 => NULL,
3054 p_action_information18 => NULL,
3055 p_action_information19 => NULL,
3056 p_action_information20 => NULL,
3057 p_action_information21 => NULL,
3058 p_action_information22 => NULL,
3059 p_action_information23 => NULL,
3060 p_action_information24 => NULL,
3061 p_action_information25 => NULL,
3062 p_action_information26 => NULL,
3063 p_action_information27 => NULL,
3064 p_action_information28 => NULL,
3065 p_action_information29 => NULL,
3066 p_action_information30 => NULL
3067 );
3068
3069 end if; -- c_get_emplr_reg_no%FOUND
3070 close c_get_emplr_reg_no;
3071 end if;
3072 if old_l_registration_no1 is not null and old_l_value1 > 0 then
3073
3074
3075 /* Bug fix#2696309, Employer level Pension Plan Register Number */
3076
3077 hr_utility.trace('Start of Employer Level PP Reg no ');
3078 hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3079 hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3080
3081 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3082 l_payroll_action_id
3083 ,old_l_registration_no1
3084 ,p_effective_date);
3085 fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
3086 if c_get_emplr_reg_no%FOUND then
3087 hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
3088 ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
3089
3090 update pay_action_information
3091 set action_information5 = to_char(ln_emplr_regamt1)
3092 where action_context_id = l_payroll_action_id
3093 and tax_unit_id = l_tax_unit_id
3094 and effective_date = p_effective_date
3095 and action_information_category = 'CAEOY PENSION PLAN INFO'
3096 AND ACTION_INFORMATION4 = old_l_registration_no1;
3097
3098 else
3099 -- insert a new record into pay_action_information
3100
3101 pay_action_information_api.create_action_information(
3102 p_action_information_id => l_action_information_id_1,
3103 p_object_version_number => l_object_version_number_1,
3104 p_action_information_category => 'CAEOY PENSION PLAN INFO',
3105 p_action_context_id => l_payroll_action_id,
3106 p_action_context_type => 'PA',
3107 p_jurisdiction_code => NULL ,
3108 p_tax_unit_id => l_tax_unit_id,
3109 p_effective_date => p_effective_date,
3110 p_assignment_id => l_asgid,
3111 p_action_information1 => NULL,
3112 p_action_information2 => NULL,
3113 p_action_information3 => NULL,
3114 p_action_information4 => old_l_registration_no1,
3115 p_action_information5 => to_char(old_l_value1),
3116 p_action_information6 => NULL,
3117 p_action_information7 => NULL,
3118 p_action_information8 => NULL,
3119 p_action_information9 => NULL,
3120 p_action_information10 => NULL,
3121 p_action_information11 => NULL,
3122 p_action_information12 => NULL,
3123 p_action_information13 => NULL,
3124 p_action_information14 => NULL,
3125 p_action_information15 => NULL,
3126 p_action_information16 => NULL,
3127 p_action_information17 => NULL,
3128 p_action_information18 => NULL,
3129 p_action_information19 => NULL,
3130 p_action_information20 => NULL,
3131 p_action_information21 => NULL,
3132 p_action_information22 => NULL,
3133 p_action_information23 => NULL,
3134 p_action_information24 => NULL,
3135 p_action_information25 => NULL,
3136 p_action_information26 => NULL,
3137 p_action_information27 => NULL,
3138 p_action_information28 => NULL,
3139 p_action_information29 => NULL,
3140 p_action_information30 => NULL
3141 );
3142
3143 end if; -- c_get_emplr_reg_no%FOUND
3144 close c_get_emplr_reg_no;
3145
3146 end if; /* for old_l_registration_no1 is not null derived
3147 from T4A_BOX32 */
3148
3149 if old_l_registration_no2 is not null and old_l_value2 > 0 then
3150
3151 /* Bug fix#2696309, Employer level Pension Plan Register Number */
3152
3153 hr_utility.trace('Start of Employer Level PP Reg no ');
3154 hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3155 hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3156
3157 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3158 l_payroll_action_id
3159 ,old_l_registration_no2
3160 ,p_effective_date);
3161 fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
3162 hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
3163 if c_get_emplr_reg_no%FOUND then
3164 ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
3165
3166 update pay_action_information
3167 set action_information5 = to_char(ln_emplr_regamt2)
3168 where action_context_id = l_payroll_action_id
3169 and tax_unit_id = l_tax_unit_id
3170 and effective_date = p_effective_date
3171 and action_information_category = 'CAEOY PENSION PLAN INFO'
3172 AND ACTION_INFORMATION4 = old_l_registration_no2;
3173
3174 else
3175 -- insert a new record into pay_action_information
3176
3177 pay_action_information_api.create_action_information(
3178 p_action_information_id => l_action_information_id_1,
3179 p_object_version_number => l_object_version_number_1,
3180 p_action_information_category => 'CAEOY PENSION PLAN INFO',
3181 p_action_context_id => l_payroll_action_id,
3182 p_action_context_type => 'PA',
3183 p_jurisdiction_code => NULL ,
3184 p_tax_unit_id => l_tax_unit_id,
3185 p_effective_date => p_effective_date,
3186 p_assignment_id => l_asgid,
3187 p_action_information1 => NULL,
3188 p_action_information2 => NULL,
3189 p_action_information3 => NULL,
3190 p_action_information4 => old_l_registration_no2,
3191 p_action_information5 => to_char(old_l_value2),
3192 p_action_information6 => NULL,
3193 p_action_information7 => NULL,
3194 p_action_information8 => NULL,
3195 p_action_information9 => NULL,
3196 p_action_information10 => NULL,
3197 p_action_information11 => NULL,
3198 p_action_information12 => NULL,
3199 p_action_information13 => NULL,
3200 p_action_information14 => NULL,
3201 p_action_information15 => NULL,
3202 p_action_information16 => NULL,
3203 p_action_information17 => NULL,
3204 p_action_information18 => NULL,
3205 p_action_information19 => NULL,
3206 p_action_information20 => NULL,
3207 p_action_information21 => NULL,
3208 p_action_information22 => NULL,
3209 p_action_information23 => NULL,
3210 p_action_information24 => NULL,
3211 p_action_information25 => NULL,
3212 p_action_information26 => NULL,
3213 p_action_information27 => NULL,
3214 p_action_information28 => NULL,
3215 p_action_information29 => NULL,
3216 p_action_information30 => NULL
3217 );
3218
3219 end if; -- c_get_emplr_reg_no%FOUND
3220 close c_get_emplr_reg_no;
3221
3222 end if; /* for old_l_registration_no2 is not null derived
3223 from T4A_BOX32 */
3224
3225
3226 end;
3227
3228 /* End of bug fix for bug #2408456 */
3229 end if; /* for old_l_registration_no is not null derived
3230 from T4A_BOX34 */
3231 end;
3232 /* end registration number archiving */
3233 else
3234 hr_utility.trace('result is 0');
3235
3236 end if; /* end if for result <> 0 condition */
3237
3238 /* Need to add the T4A Nonbox Footnote archiving code to fix bug#2175045 */
3239 begin
3240
3241 l_total_mesg_amt := 0;
3242 l_mesg_amt := 0;
3243
3244 open cur_non_box_mesg(p_assactid, p_effective_date);
3245 loop
3246 fetch cur_non_box_mesg into l_messages,
3247 l_mesg_amt,
3248 ln_tax_unit_id,
3249 ld_eff_date,
3250 ln_assignment_action_id;
3251
3252 if cur_non_box_mesg%notfound then
3253 exit;
3254 end if;
3255
3256 hr_utility.trace('l_messages - '||l_messages);
3257 hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
3258
3259 /* If the same Non Box footnote is processed more than
3260 once during the year, then the sum of the associated
3261 amounts is archived */
3262
3263 if ((l_messages <> l_prev_messages) and
3264 (l_prev_messages is not null)) then
3265
3266 hr_utility.trace('l_prev_messages - '||l_prev_messages);
3267
3268 if l_total_mesg_amt <> 0 then
3269
3270 pay_action_information_api.create_action_information(
3271 p_action_information_id => l_action_information_id_1,
3272 p_object_version_number => l_object_version_number_1,
3273 p_action_information_category => 'CA FOOTNOTES',
3274 p_action_context_id => p_assactid,
3275 p_action_context_type => 'AAP',
3276 p_jurisdiction_code => NULL,
3277 p_tax_unit_id => ln_prev_tax_unit_id,
3278 p_effective_date => ld_prev_eff_date,
3279 p_assignment_id => l_asgid,
3280 p_action_information1 => NULL,
3281 p_action_information2 => NULL,
3282 p_action_information3 => NULL,
3283 p_action_information4 => l_prev_messages,
3284 p_action_information5 => l_total_mesg_amt,
3285 p_action_information6 => 'T4A',
3286 p_action_information7 => NULL,
3287 p_action_information8 => NULL,
3288 p_action_information9 => NULL,
3289 p_action_information10 => NULL,
3290 p_action_information11 => NULL,
3291 p_action_information12 => NULL,
3292 p_action_information13 => NULL,
3293 p_action_information14 => NULL,
3294 p_action_information15 => NULL,
3295 p_action_information16 => NULL,
3296 p_action_information17 => NULL,
3297 p_action_information18 => NULL,
3298 p_action_information19 => NULL,
3299 p_action_information20 => NULL,
3300 p_action_information21 => NULL,
3301 p_action_information22 => NULL,
3302 p_action_information23 => NULL,
3303 p_action_information24 => NULL,
3304 p_action_information25 => NULL,
3305 p_action_information26 => NULL,
3306 p_action_information27 => NULL,
3307 p_action_information28 => NULL,
3308 p_action_information29 => NULL,
3309 p_action_information30 => NULL
3310 );
3311
3312 if l_box38_footnote_code = '00' then
3313 l_box38_footnote_code := l_prev_messages;
3314 else
3315 if l_box38_footnote_code <> '13' then
3316 l_box38_footnote_code := '13';
3317 end if;
3318 end if;
3319
3320 if l_total_mesg_amt < 0 then
3321 l_negative_balance_exists := 'Y';
3322 end if;
3323
3324 end if;
3325
3326 l_total_mesg_amt := l_mesg_amt;
3327 else
3328 l_total_mesg_amt := l_total_mesg_amt + l_mesg_amt;
3329 end if;
3330
3331 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3332
3333 l_prev_messages := l_messages;
3334 ln_prev_tax_unit_id := ln_tax_unit_id;
3335 ld_prev_eff_date := ld_eff_date;
3336
3337 end loop;
3338
3339 close cur_non_box_mesg;
3340
3341 if (l_prev_messages is not null) then
3342
3343 hr_utility.trace('l_prev_messages - '||l_prev_messages);
3344 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3345
3346 if l_total_mesg_amt <> 0 then
3347
3348 pay_action_information_api.create_action_information(
3349 p_action_information_id => l_action_information_id_1,
3350 p_object_version_number => l_object_version_number_1,
3351 p_action_information_category => 'CA FOOTNOTES',
3352 p_action_context_id => p_assactid,
3353 p_action_context_type => 'AAP',
3354 p_jurisdiction_code => NULL,
3355 p_tax_unit_id => ln_prev_tax_unit_id,
3356 p_effective_date => ld_prev_eff_date,
3357 p_assignment_id => l_asgid,
3358 p_action_information1 => NULL,
3359 p_action_information2 => NULL,
3360 p_action_information3 => NULL,
3361 p_action_information4 => l_prev_messages,
3362 p_action_information5 => l_total_mesg_amt,
3363 p_action_information6 => 'T4A',
3364 p_action_information7 => NULL,
3365 p_action_information8 => NULL,
3366 p_action_information9 => NULL,
3367 p_action_information10 => NULL,
3368 p_action_information11 => NULL,
3369 p_action_information12 => NULL,
3370 p_action_information13 => NULL,
3371 p_action_information14 => NULL,
3372 p_action_information15 => NULL,
3373 p_action_information16 => NULL,
3374 p_action_information17 => NULL,
3375 p_action_information18 => NULL,
3376 p_action_information19 => NULL,
3377 p_action_information20 => NULL,
3378 p_action_information21 => NULL,
3379 p_action_information22 => NULL,
3380 p_action_information23 => NULL,
3381 p_action_information24 => NULL,
3382 p_action_information25 => NULL,
3383 p_action_information26 => NULL,
3384 p_action_information27 => NULL,
3385 p_action_information28 => NULL,
3386 p_action_information29 => NULL,
3387 p_action_information30 => NULL
3388 );
3389
3390 if l_box38_footnote_code = '00' then
3391 l_box38_footnote_code := l_prev_messages;
3392 else
3393 if l_box38_footnote_code <> '13' then
3394 l_box38_footnote_code := '13';
3395 end if;
3396 end if;
3397
3398 if l_total_mesg_amt < 0 then
3399 l_negative_balance_exists := 'Y';
3400 end if;
3401
3402 end if;
3403
3404 end if;
3405
3406 end; /* End of T4A Nonbox Footnote Archive end of bugfix#2175045 */
3407
3408 /* Archive the negative balance flag */
3409 ff_archive_api.create_archive_item(
3410 p_archive_item_id => l_archive_item_id
3411 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_NEGATIVE_BALANCE_EXISTS')
3412 ,p_archive_value => l_negative_balance_exists
3413 ,p_archive_type => 'AAP'
3414 ,p_action_id => p_assactid
3415 ,p_legislation_code => 'CA'
3416 ,p_object_version_number => l_object_version_number
3417 ,p_context_name1 => 'TAX_UNIT_ID'
3418 ,p_context1 => l_tax_unit_id
3419 ,p_some_warning => l_some_warning
3420 );
3421
3422 /* T4A Box 38 Footnote code archiving */
3423 ff_archive_api.create_archive_item(
3424 p_archive_item_id => l_archive_item_id
3425 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_FOOTNOTE_CODE')
3426 ,p_archive_value => l_box38_footnote_code
3427 ,p_archive_type => 'AAP'
3428 ,p_action_id => p_assactid
3429 ,p_legislation_code => 'CA'
3430 ,p_object_version_number => l_object_version_number
3431 ,p_context_name1 => 'TAX_UNIT_ID'
3432 ,p_context1 => l_tax_unit_id
3433 ,p_some_warning => l_some_warning
3434 );
3435 /* End of t4a box 38 archive */
3436
3437 begin
3438 l_counter := 0;
3439 hr_utility.trace('selecting people');
3440
3441 select PEOPLE.person_id,
3442 PEOPLE.first_name,
3443 PEOPLE.last_name,
3444 PEOPLE.employee_number,
3445 PEOPLE.WORK_TELEPHONE,
3446 replace(PEOPLE.national_identifier,' '),
3447 PEOPLE.middle_names, /* Bug:1474421 Changed pre_name_adjunct to middle_names */
3448 ASSIGN.organization_id,
3449 ASSIGN.location_id
3450 into l_person_id,
3451 l_first_name,
3452 l_last_name,
3453 l_employee_number,
3454 l_work_telephone,
3455 l_national_identifier,
3456 l_middle_names, /* changed variable l_pre_name_adjunct to l_middle_names */
3457 l_organization_id,
3458 l_location_id
3459 from
3460 per_all_assignments_f ASSIGN
3461 , per_all_people_f PEOPLE
3462 , per_person_types PTYPE
3463 , fnd_sessions SES
3464 where l_date_earned BETWEEN ASSIGN.effective_start_date
3465 AND ASSIGN.effective_end_date
3466 and ASSIGN.assignment_id = l_asgid
3467 and PEOPLE.person_id = ASSIGN.person_id
3468 and l_date_earned BETWEEN PEOPLE.effective_start_date
3469 AND PEOPLE.effective_end_date
3470 and PTYPE.person_type_id = PEOPLE.person_type_id
3471 and SES.session_id = USERENV('SESSIONID') ;
3472 exception
3473 when no_data_found then
3474 l_first_name := null;
3475 l_last_name := null;
3476 l_employee_number := null;
3477 l_work_telephone := null;
3478 l_national_identifier := null;
3479 l_middle_names := null; /* changed variable l_pre_name_adjunct
3480 to l_middle_names */
3481 hr_utility.raise_error;
3482 end;
3483
3484 begin
3485 select PHONE.phone_number
3486 into l_employee_phone_no
3487 from per_phones PHONE ,
3488 fnd_sessions SES
3489 where PHONE.parent_id (+) = l_person_id
3490 and PHONE.parent_table (+)= 'PER_ALL_PEOPLE_F'
3491 and PHONE.phone_type (+)= 'W1'
3492 and l_date_earned BETWEEN NVL(PHONE.date_from,SES.effective_date)
3493 AND NVL(PHONE.date_to,SES.effective_date)
3494 and SES.session_id = USERENV('SESSIONID') ;
3495 exception
3496 when no_data_found then
3497 l_employee_phone_no := l_work_telephone;
3498 end;
3499
3500 hr_utility.trace('selected people');
3501 /* Initialise l_count */
3502 l_count := 0;
3503
3504 /* hr_utility.trace_on('Y','ORACLE'); */
3505
3506 l_counter := l_counter + 1;
3507 l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
3508 l_user_entity_value_tab(l_counter) := l_person_id;
3509
3510 l_counter := l_counter + 1;
3511 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
3512 l_user_entity_value_tab(l_counter) := l_first_name;
3513
3514 l_counter := l_counter + 1;
3515 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
3516 l_user_entity_value_tab(l_counter) := l_last_name;
3517
3518 l_counter := l_counter + 1;
3519 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
3520 l_user_entity_value_tab(l_counter) := l_middle_names; /* changed variable
3521 l_pre_name_adjunct to l_middle_names */
3522
3523 l_counter := l_counter + 1;
3524 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
3525 l_user_entity_value_tab(l_counter) := l_national_identifier;
3526
3527 l_counter := l_counter + 1;
3528 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
3529 l_user_entity_value_tab(l_counter) := l_employee_number;
3530
3531 l_counter := l_counter + 1;
3532 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_ORGANIZATION_ID';
3533 l_user_entity_value_tab(l_counter) := l_organization_id;
3534
3535 l_counter := l_counter + 1;
3536 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_LOCATION_ID';
3537 l_user_entity_value_tab(l_counter) := l_location_id;
3538
3539 if earning_exists = 1 then
3540 for i in 1..l_counter loop
3541
3542 l_context_id := l_taxunit_context_id;
3543 l_context_val := l_tax_unit_id;
3544
3545 ff_archive_api.create_archive_item(
3546 -- p_validate => 'TRUE'
3547 p_archive_item_id => l_archive_item_id
3548 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
3549 ,p_archive_value => l_user_entity_value_tab(i)
3550 ,p_archive_type => 'AAP'
3551 ,p_action_id => p_assactid
3552 ,p_legislation_code => 'CA'
3553 ,p_object_version_number => l_object_version_number
3554 ,p_some_warning => l_some_warning
3555 );
3556 end loop;
3557 end if;
3558
3559 begin
3560 hr_utility.trace('selecting address');
3561
3562 select addr.address_line1,
3563 addr.address_line2,
3564 addr.address_line3,
3565 addr.town_or_city,
3566 decode(addr.country,'CA',addr.region_1,'US',addr.region_2,' '),
3567 replace(addr.postal_code,' '),
3568 addr.telephone_number_1,
3569 country.territory_code
3570 into l_address_line1,
3571 l_address_line2,
3572 l_address_line3,
3573 l_town_or_city,
3574 l_province_code,
3575 l_postal_code,
3576 l_telephone_number,
3577 l_country_code
3578 from per_addresses addr,
3579 fnd_territories_vl country
3580 where addr.person_id = l_person_id
3581 and addr.primary_flag = 'Y'
3582 and p_effective_date
3583 between nvl(addr.date_from,p_effective_date)
3584 and nvl(addr.date_to, p_effective_date)
3585 and country.territory_code = addr.country;
3586 exception
3587 when no_data_found then
3588 l_address_line1 := null;
3589 l_address_line2 := null;
3590 l_address_line3 := null;
3591 l_address_line4 := null;
3592 l_town_or_city := null;
3593 l_province_code := null;
3594 l_postal_code := null;
3595 l_telephone_number := null;
3596 l_country_code := null;
3597 end;
3598
3599 hr_utility.trace('selected address');
3600
3601 l_counter := 0;
3602 l_counter := l_counter + 1;
3603 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
3604 l_user_entity_value_tab(l_counter) := l_address_line1;
3605
3606 l_counter := l_counter + 1;
3607 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE2';
3608 l_user_entity_value_tab(l_counter) := l_address_line2;
3609
3610 l_counter := l_counter + 1;
3611 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE3';
3612 l_user_entity_value_tab(l_counter) := l_address_line3;
3613
3614 l_counter := l_counter + 1;
3615 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE4';
3616 l_user_entity_value_tab(l_counter) := l_address_line4;
3617
3618 l_counter := l_counter + 1;
3619 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_CITY';
3620 l_user_entity_value_tab(l_counter) := l_town_or_city;
3621
3622 l_counter := l_counter + 1;
3623 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_PROVINCE';
3624 l_user_entity_value_tab(l_counter) := l_province_code;
3625
3626 l_counter := l_counter + 1;
3627 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_COUNTRY';
3628 l_user_entity_value_tab(l_counter) := l_country_code;
3629
3630 l_counter := l_counter + 1;
3631 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_POSTAL_CODE';
3632 l_user_entity_value_tab(l_counter) := l_postal_code;
3633
3634 /*
3635 l_counter := l_counter + 1;
3636 l_user_entity_value_tab(l_counter) := 'CAEOY_EMPLOYEE_BUSINESS_NUMBER';
3637 l_user_entity_name_tab(l_counter) := 'To be decided';
3638 */
3639 if earning_exists = 1 then
3640 for i in 1..l_counter loop
3641
3642 l_context_id := l_taxunit_context_id;
3643 l_context_val := l_tax_unit_id;
3644
3645 hr_utility.trace('archiving address');
3646 ff_archive_api.create_archive_item(
3647 p_archive_item_id => l_archive_item_id
3648 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
3649 ,p_archive_value => l_user_entity_value_tab(i)
3650 ,p_archive_type => 'AAP'
3651 ,p_action_id => p_assactid
3652 ,p_legislation_code => 'CA'
3653 ,p_object_version_number => l_object_version_number
3654 ,p_some_warning => l_some_warning
3655 );
3656 hr_utility.trace('archived address');
3657 end loop;
3658 end if;
3659 hr_utility.trace('end of eoy_archive_data');
3660 l_step := 37;
3661
3662 -- Federal YE Amendment Pre-Process Validation (T4A Amendment Archiver code)
3663
3664 Begin
3665
3666 hr_utility.trace('Started Federal YE Amendment PP Validation ');
3667
3668 select effective_date,
3669 report_type
3670 into ld_fapp_effective_date,
3671 lv_fapp_report_type
3672 from pay_payroll_actions
3673 where payroll_action_id = l_payroll_action_id;
3674
3675 hr_utility.trace('Fed Amend Pre-Process Pactid :'||
3676 to_char(l_payroll_action_id));
3677 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3678
3679 if lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP' then
3680
3681 begin
3682
3683 open c_get_fapp_locked_action_id(p_assactid);
3684 fetch c_get_fapp_locked_action_id
3685 into ln_fapp_locked_action_id;
3686
3687 close c_get_fapp_locked_action_id;
3688
3689 hr_utility.trace('T4A Amend PP Action ID : '||to_char(p_assactid));
3690 hr_utility.trace('ln_fapp_locked_action_id :'||
3691 to_char(ln_fapp_locked_action_id));
3692
3693 open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
3694 fetch c_get_fapp_lkd_actid_rtype
3695 into lv_fapp_locked_actid_reptype;
3696
3697 close c_get_fapp_lkd_actid_rtype;
3698
3699 hr_utility.trace('lv_fapp_locked_actid_reptype :'||
3700 lv_fapp_locked_actid_reptype);
3701
3702 lv_fapp_flag := compare_archive_data(p_assactid,
3703 ln_fapp_locked_action_id);
3704
3705 if lv_fapp_flag = 'Y' then
3706
3707 hr_utility.trace('Archiving T4A Amendment Flag is : ' || lv_fapp_flag);
3708
3709 ff_archive_api.create_archive_item(
3710 p_archive_item_id => l_archive_item_id
3711 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_AMENDMENT_FLAG')
3712 ,p_archive_value => lv_fapp_flag
3713 ,p_archive_type => 'AAP'
3714 ,p_action_id => p_assactid
3715 ,p_legislation_code => 'CA'
3716 ,p_object_version_number => l_object_version_number
3717 ,p_context_name1 => 'TAX_UNIT_ID'
3718 ,p_context1 => l_tax_unit_id
3719 ,p_some_warning => l_some_warning
3720 );
3721
3722 end if;
3723
3724 end; -- report_type validation
3725
3726 end if; -- report type validation for FAPP
3727 hr_utility.trace('End of Federal YE Amendment PP Validation');
3728
3729 exception when no_data_found then
3730 hr_utility.trace('Report type not found for given Payroll_action ');
3731 null;
3732 end;
3733
3734 -- End of Federal YE Amendment Pre-Process Validation
3735
3736 end eoy_archive_data;
3737
3738
3739 /* Name : eoy_range_cursor
3740 Purpose : This returns the select statement that is used to created the
3741 range rows for the Year End Pre-Process.
3742 Arguments :
3743 Notes :
3744 */
3745
3746 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
3747
3748 l_legislative_parameters varchar2(240);
3749 l_eoy_tax_unit_id number;
3750 l_transmitter_gre_id number;
3751 l_archive boolean:= FALSE;
3752 l_business_group number;
3753 l_year_start date;
3754 l_year_end date;
3755
3756 begin
3757
3758 select legislative_parameters,
3759 trunc(effective_date,'Y'),
3760 effective_date,
3761 business_group_id
3762 into l_legislative_parameters,
3763 l_year_start,
3764 l_year_end,
3765 l_business_group
3766 from pay_payroll_actions
3767 where payroll_action_id = pactid;
3768
3769 hr_utility.trace('legislative prameter is '|| l_legislative_parameters);
3770
3771 l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
3772
3773 select org_information11
3774 into l_transmitter_gre_id
3775 from hr_organization_information
3776 where organization_id = l_eoy_tax_unit_id
3777 and org_information_context = 'Canada Employer Identification'
3778 and org_information5 in ('T4A/RL1','T4A/RL2');
3779
3780 hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
3781 hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
3782
3783 if l_eoy_tax_unit_id <> -99999 then
3784
3785 sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
3786 PPA PAY_PAYROLL_ACTIONS_N51,
3787 PAA PAY_ASSIGNMENT_ACTIONS_N50,
3788 ASG PER_ASSIGNMENTS_F_PK,
3789 PPA1 PAY_PAYROLL_ACTIONS_PK)
3790 USE_NL(PPY, PPA, PAA, ASG, PPA1) */
3791 distinct asg.person_id
3792 from pay_all_payrolls_f ppy,
3793 pay_payroll_actions ppa,
3794 pay_assignment_actions paa,
3795 per_all_assignments_f asg,
3796 pay_payroll_actions ppa1
3797 where ppa1.payroll_action_id = :payroll_action_id
3798 and ppa.effective_date between
3799 fnd_date.canonical_to_date('''||
3800 fnd_date.date_to_canonical(l_year_start)||''') and
3801 fnd_date.canonical_to_date('''||
3802 fnd_date.date_to_canonical(l_year_end)||''')
3803 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
3804 and ppa.action_status = ''C''
3805 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
3806 and ppa.payroll_action_id = paa.payroll_action_id
3807 and paa.tax_unit_id = '|| to_char(l_eoy_tax_unit_id)||'
3808 and paa.action_status = ''C''
3809 and paa.assignment_id = asg.assignment_id
3810 and ppa.business_group_id = asg.business_group_id + 0
3811 and ppa.effective_date between asg.effective_start_date
3812 and asg.effective_end_date
3813 and asg.assignment_type = ''E''
3814 and ppa.payroll_id = ppy.payroll_id
3815 and ppy.business_group_id = '||to_char(l_business_group)||'
3816 order by asg.person_id';
3817
3818 l_archive := chk_gre_archive(pactid);
3819
3820 if g_archive_flag = 'N' then
3821 hr_utility.trace('eoy_range_cursor archiving employer data');
3822
3823 -- now the archiver has provision for archiving payroll_action_level data .
3824 -- So make use of that
3825
3826 hr_utility.trace('eoy_range_cursor archiving employer data');
3827
3828 eoy_archive_gre_data(pactid,
3829 l_eoy_tax_unit_id,
3830 l_transmitter_gre_id);
3831 end if;
3832
3833 end if;
3834
3835 end eoy_range_cursor;
3836
3837 end pay_ca_t4aeoy_archive;