[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4AEOY_ARCHIVE
Source
1 package body pay_ca_t4aeoy_archive as
2 /*$Header: pycayt4a.pkb 120.29 2011/09/06 06:46:11 abellur ship $ */
3 /*
4 **
5 ** Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6 **
7 ** Description : Package and procedure to build sql for payroll processes.
8 **
9 ** Change List
10 ** -----------
11 ** Date Name Vers Bug No Description
12 ** ----------- ---------- ----- ------- -----------------------------------
13 ** 03-JAN-2000 M.Mukherjee 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 ** 12-NOV-2009 aneghosh 115.69 T4A changes for 2009. Bug 9091935.
220 ** 04-DEC-2009 aneghosh 115.70 Bug9160298. Back-tracked the changes
221 ** done for Bug9091935 as it proved to be
222 ** unnecessary and caused regression.
223 ** 16-AUG-2010 sneelapa 115.71 6456662. Modified code to archive
224 ** data for new T4A Other Info Amounts.
225 ** eoy_archive_data_new_format
226 ** procedure is introduced
227 ** 23-AUG-2010 rgottipa 115.74 9983489. Modified procedure
228 ** eoy_archive_data to archive .
229 ** footnotes till 2009 only.
230 ** 09-SEP-2010 sneelapa 115.77 9983489. Modified procedure
231 ** eoy_archive_data to call .
232 ** eoy_archive_data_new_format for
233 ** Regular Preprocess for year 2010 onwards
234 ** or for Amendment Preprocess.
235 ** 09-SEP-2010 sneelapa 115.78 10121216. Modified logic so that
236 ** 'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS' will be
237 ** archived by eoy_archive_data_new_format proc
238 ** incase eoy_archive_data_new_format is not called
239 ** DBI will be archived by eoy_archive_data proc.
240 ** Modified logic for archiving 0 ZERO for
241 ** OTHER Info Amount 026, 027, 142 and 143.
242 ** 21-SEP-2010 sneelapa 115.79 10115617. Modified eoy_archive_data
243 ** procedure. Added condition for opening
244 ** CURSOR c_balance_feed_info if request
245 ** is submitted for Fed Year End Preprocess
246 ** and Year is less than 2010.
247 ** 22-OCT-2010 sneelapa 115.80 Bug 10099479. Modified eoy_archive_data
248 ** procedure. Added condition for opening
249 ** CURSOR c_balance_feed_info if request
250 ** is submitted for Fed Year End Preprocess
251 ** and Year is less than 2010 for archiving
252 ** Registration number till 2009 only
253 ** from 2010 onwards Registration number
254 ** will be archived by eoy_archive_data_new_format
255 ** procedure.
256 ** 15-NOV-2010 sneelapa 115.82 Bug 10099479. Modified eoy_archive_data
257 ** procedure. Commented END IF after archiving
258 ** Registration number for T4A_BOX34 and added
259 ** ELSE for code which is archiving Registration
260 ** number for T4A_BOX32. Reason is Registration
261 ** number should be archived for T4A_BOX34 and IF
262 ** T4A_BOX34 is not having Registration number then
263 ** T4A_BOX32 Registration number should be archived.
264 ** 18-NOV-2010 sneelapa 115.83 Bug 10099479. Taken package version 115.81 as base
265 ** and added l_box34_regno_flag to check whether
266 ** Registration number for T4A_BOX34 is archived or not
267 ** Archive Registration number for T4A_BOX32.
268 ** if Registration number for T4A_BOX34
269 ** is not archived.
270 ** Added distinct clause for c_reg_balance_feed_info CURSOR.
271 ** 22-NOV-2010 sneelapa 115.84 Bug 10247374. Calling eoy_archive_data_new_format
272 ** procedure before compare_archive_data procedure.
273 ** compare_archive_data procedure archives CAEOY_T4A_AMENDMENT_FLAG
274 ** Regular archiver will have data for old DBIs and new DBIs
275 ** (other info amoutns), eoy_archive_data_new_format INSERTs
276 ** data for newly introduced Other Info Amount DBIs, this
277 ** should be called before compare_archive_data procedure as.
278 ** compare_archive_data compares COUNT for DBIs archived by
279 **
280 ** 28-DEC-2010 sneelapa 115.85 Bug 10420909. Modified eoy_archive_data_new_format
281 ** and compare_archive_data procedures.
282 ** Added if l_negative_balance_exists ='N' condition for
283 ** archiving Employer level Pension Plan Register Number
284 ** 30-DEC-2010 sneelapa 115.86 Fix for Bug 10388148.
285 ** Modified eoy_archive_gre_data procedure to
286 ** to archive Proprietor SIN numbers of
287 ** Transmitter GRE for Non Transmitter GRE
288 ** if Proprietor SIN numbers are not feeded
289 ** for Non Transmitter GRE.
290 ** 13-Jan-2011 sneelapa 115.87 Bug 11065493 Modified cursor cur_non_box_mesg to
291 ** consider Reversal of Quickpay/Payroll Runs.
292 ** 29-Aug-2011 sneelapa 115.113 10399514 Introduced new CURSOR c_eoy_gre_range
293 ** it will be called in place of
294 ** c_eoy_gre CURSOR, if RANGE_PERSON_ID
295 ** is enabled.
296 */
297
298
299 sqwl_range varchar2(4000);
300 eoy_gre_range varchar2(4000);
301 eoy_all_range varchar2(4000);
302
303 /* Returns the value of a legislative_parameter from pay_payroll_actions */
304
305 function get_parameter(name in varchar2,
306 parameter_list varchar2)
307 return varchar2
308 is
309 start_ptr number;
310 end_ptr number;
311 token_val pay_payroll_actions.legislative_parameters%type;
312 par_value pay_payroll_actions.legislative_parameters%type;
313 begin
314
315 token_val := name||'=';
316
317 start_ptr := instr(parameter_list, token_val) + length(token_val);
318 end_ptr := instr(parameter_list, ' ', start_ptr);
319
320 /* if there is no spaces use then length of the string */
321 if end_ptr = 0 then
322 end_ptr := length(parameter_list)+1;
323 end if;
324
325 /* Did we find the token */
326 if instr(parameter_list, token_val) = 0 then
327 par_value := NULL;
328 else
329 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
330 end if;
331
332 return par_value;
333
334 end get_parameter;
335
336 /*
337 ** Name : bal_db_item
338 ** Purpose : Given the name of a balance DB item as would be seen in
339 ** a fast formula it returns the defined_balance_id of the
340 ** balance it represents.
341 ** Arguments :
342 ** Notes : A defined balance_id is required by the PLSQL balance function.
343 */
344
345 function bal_db_item
346 (
347 p_db_item_name varchar2
348 ) return number is
349
350 /* Get the defined_balance_id for the specified balance DB item. */
351
352 cursor csr_defined_balance is
353 select to_number(UE.creator_id)
354 from ff_user_entities UE,
355 ff_database_items DI
356 where DI.user_name = p_db_item_name
357 and UE.user_entity_id = DI.user_entity_id
358 and Ue.creator_type = 'B'
359 and UE.legislation_code = 'CA';
360
361 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
362
363 begin
364
365 open csr_defined_balance;
366 fetch csr_defined_balance into l_defined_balance_id;
367 if csr_defined_balance%notfound then
368 close csr_defined_balance;
369 raise hr_utility.hr_error;
370 else
371 close csr_defined_balance;
372 end if;
373
374 return (l_defined_balance_id);
375
376 end bal_db_item;
377
378 /*
379 ** Name : get_dates
380 ** Purpose : The dates are dependent on the report being run
381 ** For T4 it is year end dates.
382 */
383
384 procedure get_dates
385 (
386 p_report_type in varchar2,
387 p_effective_date in date,
388 p_period_end in out nocopy date,
389 p_quarter_start in out nocopy date,
390 p_quarter_end in out nocopy date,
391 p_year_start in out nocopy date,
392 p_year_end in out nocopy date
393 ) is
394 begin
395
396 if p_report_type = 'T4A' then
397 /*
398 ** Year End Pre-process is a yearly process where the identifier
399 ** indicates the year eg. 1998. The expected values for the example
400 ** should be
401 ** p_period_end 31-DEC-1998
402 ** p_quarter_start 01-OCT-1998
403 ** p_quarter_end 31-DEC-1998
404 ** p_year_start 01-JAN-1998
405 ** p_year_end 31-DEC-1998
406 */
407
408 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
409 p_quarter_start := trunc(p_period_end, 'Q');
410 p_quarter_end := p_period_end;
411
412 /* For EOY */
413
414 end if;
415
416 p_year_start := trunc(p_effective_date, 'Y');
417 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
418
419 end get_dates;
420
421 /*
422 ** Name : get_selection_information
423 ** Purpose : Returns information used in the selection of people to
424 ** be reported on.
425 ** Arguments :
426 **
427 ** The following values are returned :-
428 **
429 ** p_period_start - The start of the period over which to select
430 ** the people.
431 ** p_period_end - The end of the period over which to select
432 ** the people.
433 ** p_defined_balance_id - The balance which must be non zero for each
434 ** person to be included in the report.
435 ** p_group_by_gre - should the people be grouped by GRE.
436 ** p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
437 ** the testing of the balance.
438 ** p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
439 ** for the testing of the balance.
440 **
441 ** Notes : This routine provides a way of coding explicit rules for
442 ** individual reports where they are different from the
443 ** standard selection criteria for the report type ie. in
444 ** NY state the selection of people in the 4th quarter is
445 ** different from the first 3.
446 */
447
448 procedure get_selection_information
449 (
450
451 /* Identifies the type of report, the authority for which it is being run,
452 and the period being reported. */
453
454 p_report_type varchar2,
455 p_quarter_start date,
456 p_quarter_end date,
457 p_year_start date,
458 p_year_end date,
459 /* Information returned is used to control the selection of people to
460 report on. */
461
462 p_period_start in out nocopy date,
463 p_period_end in out nocopy date,
464 p_defined_balance_id in out nocopy number,
465 p_group_by_gre in out nocopy boolean,
466 p_tax_unit_context in out nocopy boolean,
467 p_jurisdiction_context in out nocopy boolean
468 ) is
469
470 begin
471
472 /* Depending on the report being processed, derive all the information
473 required to be able to select the people to report on. */
474
475 if p_report_type = 'T4A' then
476
477 /* Default settings for Year End Pre-process. */
478
479 p_period_start := p_year_start;
480 p_period_end := p_year_end;
481 p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD');
482 p_group_by_gre := FALSE;
483 p_tax_unit_context := TRUE;
484 p_jurisdiction_context := FALSE;
485
486 /* For EOY - end */
487
488 /* An invalid report type has been passed so fail. */
489
490 else
491
492 raise hr_utility.hr_error;
493
494 end if;
495
496 end get_selection_information;
497
498
499
500 /*
501 ** Name : eoy_action_creation
502 ** Purpose : This creates the assignment actions for a specific chunk
503 ** of people to be archived by the year end pre-process.
504 ** Arguments :
505 ** Notes :
506 */
507
508 procedure eoy_action_creation(pactid in number,
509 stperson in number,
510 endperson in number,
511 chunk in number) is
512
513
514
515 /* Variables used to hold the select columns from the SQL statement. */
516
517 l_person_id number;
518 l_assignment_id number;
519 l_tax_unit_id number;
520 l_eoy_tax_unit_id number;
521 l_effective_end_date date;
522 l_archive_item_id number;
523 l_user_entity_name_tab pay_ca_t4aeoy_archive.char240_data_type_table;
524
525 /* Variables used to hold the values used as bind variables within the
526 SQL statement. */
527
528 l_bus_group_id number;
529 l_period_start date;
530 l_period_end date;
531
532 /* Variables used to hold the details of the payroll and assignment actions
533 that are created. */
534
535 l_payroll_action_created boolean := false;
536 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
537 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
538
539 /* Variable holding the balance to be tested. */
540
541 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
542
543 /* Indicator variables used to control how the people are grouped. */
544
545 l_group_by_gre boolean := FALSE;
546
547 /* Indicator variables used to control which contexts are set up for
548 balance. */
549
550 l_tax_unit_context boolean := FALSE;
551 l_jurisdiction_context boolean := FALSE;
552
553 /* Variables used to hold the current values returned within the loop for
554 checking against the new values returned from within the loop on the
555 next iteration. */
556
557 l_prev_person_id per_all_people_f.person_id%type;
558 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
559
560 /* Variable to hold the jurisdiction code used as a context for state
561 reporting. */
562
563 l_jurisdiction_code varchar2(30);
564
565 /* general process variables */
566
567 l_report_type pay_payroll_actions.report_type%type;
568 -- Variables declared for bug 10399514
569 l_person_on boolean ;
570 l_report_cat pay_payroll_actions.report_category%type;
571 l_state pay_payroll_actions.report_qualifier%type;
572 l_report_format pay_report_format_mappings_f.report_format%type;
573 -- Variables declared for bug 10399514
574 l_province pay_payroll_actions.report_qualifier%type;
575 l_value number;
576 old_l_value number;
577 l_registration_no varchar2(150);
578 l_balance_name varchar2(150);
579 l_effective_date date;
580 l_quarter_start date;
581 l_quarter_end date;
582 l_year_start date;
583 l_year_end date;
584 lockingactid number;
585 l_primary_asg pay_assignment_actions.assignment_id%type;
586 l_legislative_parameters varchar2(240);
587 l_max_aaid number;
588
589
590 /* For Year End Preprocess we have to archive the assignments
591 belonging to a GRE */
592 /*
593 CURSOR c_eoy_gre IS
594 SELECT ASG.person_id person_id,
595 ASG.assignment_id assignment_id,
596 paa.tax_unit_id tax_unit_id,
597 ASG.effective_end_date effective_end_date
598 FROM per_all_assignments_f ASG,
599 pay_assignment_actions paa,
600 pay_payroll_actions ppa
601 WHERE ppa.payroll_action_id >= 0
602 AND ppa.effective_date between l_period_start
603 and l_period_end
604 AND ppa.action_type in ('R','Q','V','B','I')
605 AND ppa.business_group_id + 0 = l_bus_group_id
606 AND ppa.payroll_action_id = paa.payroll_action_id
607 AND paa.tax_unit_id = l_eoy_tax_unit_id
608 AND paa.assignment_id = ASG.assignment_id
609 AND ppa.business_group_id = ASG.business_group_id +0
610 AND ASG.person_id + 0 between stperson and endperson
611 AND ASG.assignment_type = 'E'
612 AND ppa.effective_date between ASG.effective_start_date
613 AND ASG.effective_end_date
614 ORDER BY 1, 3, 4 DESC, 2;
615 */
616
617 /* Tuned c_eoy_gre for bug#3416511 */
618 CURSOR c_eoy_gre IS
619 SELECT ASG.person_id person_id,
620 ASG.assignment_id assignment_id,
621 paa.tax_unit_id tax_unit_id,
622 ASG.effective_end_date effective_end_date
623 FROM per_all_assignments_f ASG,
624 pay_assignment_actions paa,
625 pay_payroll_actions ppa,
626 per_all_people_f ppf
627 WHERE ppa.effective_date between l_period_start
628 and l_period_end
629 AND ppa.action_type in ('R','Q','V','B','I')
630 AND ppa.business_group_id +0 = l_bus_group_id
631 AND ppa.payroll_action_id = paa.payroll_action_id
632 AND paa.tax_unit_id = l_eoy_tax_unit_id
633 AND paa.assignment_id = ASG.assignment_id
634 AND ppa.business_group_id = ASG.business_group_id +0
635 AND ppf.person_id between stperson and endperson
636 AND ASG.person_id = ppf.person_id
637 AND ASG.assignment_type = 'E'
638 AND ppa.effective_date between ASG.effective_start_date
639 AND ASG.effective_end_date
640 AND ppa.effective_date between ppf.effective_start_date
641 AND ppf.effective_end_date
642 ORDER BY 1, 3, 4 DESC, 2;
643
644 -- Added for Bug# 10399514
645 -- Used when RANGE_PERSON_ID functionality is available
646
647 CURSOR c_eoy_gre_range IS
648 SELECT ASG.person_id person_id,
649 ASG.assignment_id assignment_id,
650 paa.tax_unit_id tax_unit_id,
651 ASG.effective_end_date effective_end_date
652 FROM per_all_assignments_f ASG,
653 pay_assignment_actions paa,
654 pay_payroll_actions ppa,
655 per_all_people_f ppf,
656 pay_population_ranges ppr
657 WHERE ppa.effective_date between l_period_start
658 and l_period_end
659 AND ppa.action_type in ('R','Q','V','B','I')
660 AND ppa.business_group_id +0 = l_bus_group_id
661 AND ppa.payroll_action_id = paa.payroll_action_id
662 AND paa.tax_unit_id = l_eoy_tax_unit_id
663 AND paa.assignment_id = ASG.assignment_id
664 AND ppa.business_group_id = ASG.business_group_id +0
665 -- AND ppf.person_id between stperson and endperson
666 AND ppr.payroll_action_id = pactid
667 AND ppr.chunk_number = chunk
668 AND ppr.person_id = ASG.person_id
669 AND ASG.person_id = ppf.person_id
670 AND ASG.assignment_type = 'E'
671 AND ppa.effective_date between ASG.effective_start_date
672 AND ASG.effective_end_date
673 AND ppa.effective_date between ppf.effective_start_date
674 AND ppf.effective_end_date
675 ORDER BY 1, 3, 4 DESC, 2;
676
677
678 /* Commented c_eoy_all, because Tax Unit id is a mandatory parameter
679 in archiver process, this cursor will never be used */
680 /*
681 CURSOR c_eoy_all IS
682 SELECT ASG.person_id person_id,
683 ASG.assignment_id assignment_id,
684 to_number(SCL.segment11) tax_unit_id,
685 ASG.effective_end_date effective_end_date
686 FROM per_all_assignments_f ASG,
687 hr_soft_coding_keyflex SCL,
688 pay_all_payrolls_f PPY
689 WHERE ASG.business_group_id + 0 = l_bus_group_id
690 AND ASG.person_id between stperson and endperson
691 AND ASG.assignment_type = 'E'
692 AND ASG.effective_start_date <= l_period_end
693 AND ASG.effective_end_date >= l_period_start
694 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
695 AND PPY.payroll_id = ASG.payroll_id
696 ORDER BY 1, 3, 4 DESC, 2;
697 */
698 /* Get the primary assignment for the given person_id */
699
700 CURSOR c_get_asg_id (p_person_id number) IS
701 SELECT assignment_id
702 from per_all_assignments_f paf
703 where person_id = p_person_id
704 and primary_flag = 'Y'
705 and assignment_type = 'E'
706 and paf.effective_start_date <= l_period_end
707 and paf.effective_end_date >= l_period_start
708 ORDER BY assignment_id desc;
709
710 begin
711
712 /* Get the report type, report qualifier, business group id and the
713 gre for which the archiving has to be done */
714
715 hr_utility.trace('getting report type ');
716
717 select effective_date,
718 report_type,
719 -- Added for bug 10399514
720 report_qualifier,
721 report_category,
722 -- Added for bug 10399514
723 business_group_id,
724 legislative_parameters
725 into l_effective_date,
726 l_report_type,
727 -- Added for bug 10399514
728 l_state,
729 l_report_cat,
730 -- Added for bug 10399514
731 l_bus_group_id,
732 l_legislative_parameters
733 from pay_payroll_actions
734 where payroll_action_id = pactid;
735
736 l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
737
738 hr_utility.trace('getting dates');
739
740 get_dates(l_report_type,
741 l_effective_date,
742 l_period_end,
743 l_quarter_start,
744 l_quarter_end,
745 l_year_start,
746 l_year_end);
747
748 hr_utility.trace('getting selection information');
749 hr_utility.trace('report type '|| l_report_type);
750 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
751 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
752 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
753 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
754
755 get_selection_information
756 (l_report_type,
757 l_quarter_start,
758 l_quarter_end,
759 l_year_start,
760 l_year_end,
761 l_period_start,
762 l_period_end,
763 l_defined_balance_id,
764 l_group_by_gre,
765 l_tax_unit_context,
766 l_jurisdiction_context);
767
768 if l_eoy_tax_unit_id <> 99999 then
769 /* else
770 open c_eoy_all;
771 */
772 -- Code modification for bug 10399514 starts here
773 /* Initializing variable */
774 l_person_on := FALSE ;
775
776 Begin
777 select report_format
778 into l_report_format
779 from pay_report_format_mappings_f
780 where report_type = l_report_type
781 and report_qualifier = l_state
782 and report_category = l_report_cat ;
783 Exception
784 When Others Then
785 l_report_format := Null ;
786 End ;
787
788 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
789 p_report_format => l_report_format,
790 p_report_qualifier => l_state,
791 p_report_category => l_report_cat) ;
792
793 -- open c_eoy_qbin;
794
795 if l_person_on then
796 hr_utility.trace('opening c_eoy_gre_range CURSOR');
797 OPEN c_eoy_gre_range ;
798 else
799 hr_utility.trace('opening c_eoy_gre CURSOR');
800 OPEN c_eoy_gre;
801 end if ;
802
803 -- Code modification for bug 10399514 ends here
804 end if;
805
806 /* Loop for all rows returned for SQL statement. */
807
808 hr_utility.trace('Entering loop');
809
810 loop
811
812 if l_eoy_tax_unit_id <> 99999 then
813
814 hr_utility.trace('Fetching person id');
815
816 -- Code modification for bug 10399514 starts here
817 if l_person_on then
818 hr_utility.trace('fetching from c_eoy_gre_range CURSOR');
819 fetch c_eoy_gre_range
820 into l_person_id,
821 l_assignment_id,
822 l_tax_unit_id,
823 l_effective_end_date;
824 exit when c_eoy_gre_range%NOTFOUND;
825 else
826 hr_utility.trace('fetching from c_eoy_gre CURSOR');
827 fetch c_eoy_gre
828 into l_person_id,
829 l_assignment_id,
830 l_tax_unit_id,
831 l_effective_end_date;
832 exit when c_eoy_gre%NOTFOUND;
833 end if ;
834 -- Code modification for bug 10399514 ends here
835 /*
836 else
837
838 fetch c_eoy_all into l_person_id,
839 l_assignment_id,
840 l_tax_unit_id,
841 l_effective_end_date;
842
843 exit when c_eoy_all%NOTFOUND;
844 */
845 end if;
846
847
848 /* If the new row is the same as the previous row according to the way
849 the rows are grouped then discard the row ie. grouping by GRE
850 requires a single row for each person / GRE combination. */
851
852 hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
853 hr_utility.trace('previous tax unit id is '||
854 to_char(l_prev_tax_unit_id));
855
856 if ( l_person_id = l_prev_person_id and
857 l_tax_unit_id = l_prev_tax_unit_id) then
858 null;
859
860 else
861
862 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
863 hr_utility.trace('person is '|| to_char(l_person_id));
864 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
865
866
867 /* Have a new unique row according to the way the rows are grouped.
868 ** The inclusion of the person is dependent on having a non zero
869 ** balance.
870 ** If the balance is non zero then an assignment action is created to
871 ** indicate their inclusion in the magnetic tape report. */
872
873 /* Set up the context of tax unit id */
874
875 hr_utility.trace('Setting context');
876
877 if l_tax_unit_context then
878 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
879 end if;
880
881 begin
882 select paa1.assignment_action_id
883 into l_max_aaid
884 from pay_assignment_actions paa1,
885 per_all_assignments_f paf2,
886 pay_payroll_actions ppa2,
887 pay_action_classifications pac2
888 where paf2.person_id = l_person_id
889 and paa1.assignment_id = paf2.assignment_id
890 and paa1.tax_unit_id = l_tax_unit_id
891 and paa1.payroll_action_id = ppa2.payroll_action_id
892 and ppa2.action_type = pac2.action_type
893 and pac2.classification_name = 'SEQUENCED'
894 and ppa2.effective_date between paf2.effective_start_date
895 and paf2.effective_end_date
896 and ppa2.effective_date between l_period_start and
897 l_period_end
898 and not exists (select ''
899 FROM pay_action_classifications pac,
900 pay_payroll_actions ppa,
901 pay_assignment_actions paa,
902 per_all_assignments_f paf1
903 WHERE paf1.person_id = l_person_id
904 AND paa.assignment_id = paf1.assignment_id
905 AND paa.tax_unit_id = l_tax_unit_id
906 AND ppa.payroll_action_id = paa.payroll_action_id
907 AND ppa.effective_date between l_period_start
908 and l_period_end
909 AND paa.action_sequence > paa1.action_sequence
910 AND pac.action_type = ppa.action_type
911 AND pac.classification_name = 'SEQUENCED')
912 and rownum < 2;
913 exception
914 when no_data_found then
915 l_max_aaid := -9999;
916 end;
917
918 /* Get the primary assignment */
919 open c_get_asg_id(l_person_id);
920 fetch c_get_asg_id into l_primary_asg;
921 if c_get_asg_id%NOTFOUND then
922 close c_get_asg_id;
923 raise hr_utility.hr_error;
924 else
925 close c_get_asg_id;
926 end if;
927
928 if l_max_aaid <> -9999 then /* Max Assignment action id */
929 if ( (pay_ca_balance_pkg.call_ca_balance_get_value
930 ( 'Gross Earnings',
931 'YTD' , l_max_aaid, l_primary_asg , NULL, 'PER' ,
932 l_tax_unit_id, l_bus_group_id, NULL)
933 <> 0) OR
934 (pay_ca_balance_pkg.call_ca_balance_get_value
935 ( 'T4A No Gross Earnings',
936 'YTD' , l_max_aaid, l_primary_asg , NULL, 'PER' ,
937 l_tax_unit_id, l_bus_group_id, NULL)
938 <> 0) ) then
939 /* Create the assignment action to represnt the person / tax unit
940 combination. */
941
942 select pay_assignment_actions_s.nextval
943 into lockingactid
944 from dual;
945
946 /* Insert into pay_assignment_actions. */
947
948 hr_utility.trace('creating assignment action');
949
950 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
951 pactid,chunk,l_tax_unit_id);
952
953 /* Update the serial number column with the person id
954 ** so that the mag routine and the W2 view will not have
955 ** to do an additional checking against the assignment
956 ** table
957 */
958
959 hr_utility.trace('updating assignment action');
960
961 update pay_assignment_actions aa
962 set aa.serial_number = to_char(l_person_id)
963 where aa.assignment_action_id = lockingactid;
964 end if; /* End of Gross Earning <> 0 */
965 end if ; /*l_max_aaid <> -9999 */
966 end if; /* End of l_person_id <> l_prev_person_id */
967
968 /* Record the current values for the next time around the loop. */
969
970 l_prev_person_id := l_person_id;
971 l_prev_tax_unit_id := l_tax_unit_id;
972
973 end loop;
974
975 if l_eoy_tax_unit_id <> 99999 then
976 -- Code modification for bug 10399514 starts here
977 if l_person_on then
978 hr_utility.trace('closing c_eoy_gre_range CURSOR');
979 close c_eoy_gre_range;
980 else
981 hr_utility.trace('closing c_eoy_gre CURSOR');
982 close c_eoy_gre;
983 end if ;
984 -- Code modification for bug 10399514 ends here
985 /* else
986 close c_eoy_all;
987 */
988 end if;
989
990
991 end eoy_action_creation;
992
993
994
995 /*
996 ** Name : get_user_entity_id
997 ** Purpose : This gets the user_entity_id for a specific database item name.
998 ** Arguments : p_dbi_name -> database item name.
999 ** Notes :
1000 */
1001
1002 function get_user_entity_id (p_dbi_name in varchar2)
1003 return number is
1004 l_user_entity_id number;
1005
1006 begin
1007
1008 hr_utility.trace('getting the user_entity_id for '
1009 || p_dbi_name);
1010 select fdi.user_entity_id
1011 into l_user_entity_id
1012 from ff_database_items fdi,
1013 ff_user_entities fui
1014 where user_name = p_dbi_name
1015 and fdi.user_entity_id = fui.user_entity_id
1016 and fui.legislation_code = 'CA';
1017
1018 return l_user_entity_id;
1019
1020 exception
1021 when others then
1022 hr_utility.trace('Error while getting the user_entity_id for '
1023 || p_dbi_name ||'**');
1024 raise hr_utility.hr_error;
1025
1026 end get_user_entity_id;
1027
1028 /* Name : get_footnote_user_entity_id
1029 ** Purpose : This gets the user_entity_id for a specific database item name.
1030 ** and it does not raise error if the the user entity is not found
1031 ** Arguments : p_dbi_name -> database item name.
1032 ** Notes :
1033 */
1034
1035 function get_footnote_user_entity_id (p_dbi_name in varchar2)
1036 return number is
1037 l_user_entity_id number;
1038
1039 begin
1040
1041 select user_entity_id
1042 into l_user_entity_id
1043 from ff_database_items
1044 where user_name = p_dbi_name;
1045
1046 return l_user_entity_id;
1047
1048 exception
1049 when others then
1050 hr_utility.trace('Error while getting the user_entity_id'
1051 || p_dbi_name ||'***');
1052 return 0;
1053
1054 end get_footnote_user_entity_id;
1055
1056 /*
1057 Name : compare_archive_data
1058 Purpose : compares Federal YEPP data and Federal YE Amendment Data
1059 Arguments : p_assignment_action_id -> Assignment_action_id
1060 p_locked_action_id -> YEPP Assignment_action_id
1061
1062 Notes : Used specifically for Federal YE Amendment Pre-Process (YE-2004)
1063 */
1064
1065 function compare_archive_data(p_assignment_action_id in number
1066 ,p_locked_action_id in number
1067 ) return varchar2 is
1068 TYPE act_info_rec IS RECORD
1069 (archive_context1 number(25)
1070 ,archive_ue_id number(25)
1071 ,archive_value varchar2(240));
1072
1073 TYPE footnote_rec IS RECORD
1074 (message varchar2(240)
1075 ,value varchar2(240));
1076
1077 TYPE action_info_table IS TABLE OF act_info_rec
1078 INDEX BY BINARY_INTEGER;
1079
1080 TYPE footnote_table IS TABLE OF footnote_rec
1081 INDEX BY BINARY_INTEGER;
1082
1083 -- Cursor to get archived values based on asg_act_id
1084
1085 cursor c_get_emp_t4a_data (cp_asg_act_id number) is
1086 SELECT fai1.context1,
1087 fdi1.user_entity_id,
1088 fai1.value
1089 FROM ff_archive_items fai1,
1090 ff_database_items fdi1
1091 WHERE fai1.user_entity_id = fdi1.user_entity_id
1092 AND fai1.context1 = cp_asg_act_id
1093 AND fdi1.user_name <> 'CAEOY_T4A_AMENDMENT_FLAG'
1094 order by fdi1.user_name;
1095
1096 cursor c_get_nonbox_footnote(cp_asg_act_id number) is
1097 select action_information4,
1098 action_information5
1099 from pay_action_information
1100 where action_context_id = cp_asg_act_id
1101 and action_information_category = 'CA FOOTNOTES'
1102 and action_context_type = 'AAP'
1103 and action_information6 = 'T4A'
1104 order by action_information4;
1105
1106
1107 i number;
1108 lv_flag varchar2(2);
1109 ltr_amend_arch_data action_info_table;
1110 ltr_yepp_arch_data action_info_table;
1111 ln_yepp_box_count number;
1112 ln_amend_box_count number;
1113
1114 ltr_amend_footnote footnote_table;
1115 ltr_yepp_footnote footnote_table;
1116 ln_yepp_footnote_count number;
1117 ln_amend_footnote_count number;
1118
1119 begin
1120 -- hr_utility.trace_on('Y','TEST');
1121
1122 /* Initialization Process */
1123
1124 lv_flag := 'N';
1125
1126 if ltr_amend_arch_data.count > 0 then
1127 ltr_amend_arch_data.delete;
1128 end if;
1129
1130 if ltr_yepp_arch_data.count > 0 then
1131 ltr_yepp_arch_data.delete;
1132 end if;
1133
1134 if ltr_amend_footnote.count > 0 then
1135 ltr_amend_footnote.delete;
1136 end if;
1137
1138 if ltr_yepp_footnote.count > 0 then
1139 ltr_yepp_footnote.delete;
1140 end if;
1141
1142 /* Populate T4A Amendment Footnotes */
1143 open c_get_nonbox_footnote(p_assignment_action_id);
1144
1145 hr_utility.trace('Populating T4A Amendment Footnote ');
1146
1147 ln_amend_footnote_count := 0;
1148 loop
1149 fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
1150 exit when c_get_nonbox_footnote%NOTFOUND;
1151
1152 hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
1153 hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
1154
1155 ln_amend_footnote_count := ln_amend_footnote_count + 1;
1156 end loop;
1157
1158 close c_get_nonbox_footnote;
1159
1160 /* Populate T4A YEPP Footnotes */
1161 open c_get_nonbox_footnote(p_locked_action_id);
1162
1163 ln_yepp_footnote_count := 0;
1164 loop
1165 fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
1166 exit when c_get_nonbox_footnote%NOTFOUND;
1167
1168 hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
1169 hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
1170
1171 ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
1172 end loop;
1173
1174 close c_get_nonbox_footnote;
1175
1176
1177 /* Populate T4A Amendment Data for an assignment_action */
1178 open c_get_emp_t4a_data(p_assignment_action_id);
1179
1180 hr_utility.trace('Populating T4A Amendment Data ');
1181 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1182
1183 ln_amend_box_count := 0;
1184 loop
1185 fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1186 exit when c_get_emp_t4a_data%NOTFOUND;
1187
1188 hr_utility.trace('I :'||to_char(ln_amend_box_count));
1189 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1190 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1191 hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1192
1193 ln_amend_box_count := ln_amend_box_count + 1;
1194 end loop;
1195
1196 close c_get_emp_t4a_data;
1197
1198
1199 /* Populate T4A YEPP Data for an assignment_action */
1200 open c_get_emp_t4a_data(p_locked_action_id);
1201
1202 hr_utility.trace('Populating T4A YEPP Data ');
1203 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1204
1205 ln_yepp_box_count := 0;
1206 loop
1207 fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1208 exit when c_get_emp_t4a_data%NOTFOUND;
1209
1210 hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1211 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1212 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1213 hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1214
1215 ln_yepp_box_count := ln_yepp_box_count + 1;
1216 end loop;
1217
1218 close c_get_emp_t4a_data;
1219
1220 /* Compare T4A Amendment Data and T4A YEPP Data for an
1221 assignment_action */
1222
1223 hr_utility.trace('Comparing T4A Amend and T4A YEPP Data');
1224
1225 if ln_yepp_box_count <> ln_amend_box_count then
1226 lv_flag := 'Y';
1227 elsif ln_yepp_box_count = ln_amend_box_count then
1228 for i in ltr_yepp_arch_data.first..ltr_yepp_arch_data.last
1229 loop
1230 if (ltr_yepp_arch_data(i).archive_ue_id =
1231 ltr_amend_arch_data(i).archive_ue_id) then
1232
1233 if ((ltr_yepp_arch_data(i).archive_value <>
1234 ltr_amend_arch_data(i).archive_value) or
1235 (ltr_yepp_arch_data(i).archive_value is null and
1236 ltr_amend_arch_data(i).archive_value is not null) or
1237 (ltr_yepp_arch_data(i).archive_value is not null and
1238 ltr_amend_arch_data(i).archive_value is null)) then
1239
1240 lv_flag := 'Y';
1241 hr_utility.trace('Archive_UE_id with differnt value :'||to_char(ltr_yepp_arch_data(i).archive_ue_id));
1242 exit;
1243 end if;
1244 end if;
1245 end loop;
1246 end if;
1247
1248 /* Compare T4A Amendment Footnotes and T4A YEPP Footnotes for an
1249 assignment_action */
1250
1251 hr_utility.trace('Comparing T4A Amend and T4A YEPP Footnotes');
1252
1253 if lv_flag <> 'Y' then
1254 if ln_yepp_footnote_count <> ln_amend_footnote_count then
1255 lv_flag := 'Y';
1256 elsif ((ln_yepp_footnote_count = ln_amend_footnote_count) and
1257 (ln_yepp_footnote_count <> 0)) then
1258 for i in ltr_yepp_footnote.first..ltr_yepp_footnote.last
1259 loop
1260 if (ltr_yepp_footnote(i).message =
1261 ltr_amend_footnote(i).message) then
1262
1263 if ((ltr_yepp_footnote(i).value <>
1264 ltr_amend_footnote(i).value) or
1265 (ltr_yepp_footnote(i).value is null and
1266 ltr_amend_footnote(i).value is not null) or
1267 (ltr_yepp_footnote(i).value is not null and
1268 ltr_amend_footnote(i).value is null)) then
1269
1270 lv_flag := 'Y';
1271 hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
1272 exit;
1273 end if;
1274 end if;
1275 end loop;
1276 end if;
1277 end if;
1278
1279 /* If there is no value difference for Entire Employee data then set
1280 flag to 'N' */
1281
1282 if lv_flag <> 'Y' then
1283 lv_flag := 'N';
1284 hr_utility.trace('No value difference for an Employee Asg Action: '||
1285 to_char(p_assignment_action_id));
1286 end if;
1287
1288 hr_utility.trace('lv_flag :'||lv_flag);
1289
1290 return lv_flag;
1291
1292 -- hr_utility.trace_off;
1293
1294 end compare_archive_data;
1295
1296
1297 /* Name : eoy_archive_gre_data
1298 Purpose : This performs the CA specific employer data archiving.
1299 Arguments :
1300 Notes :
1301 */
1302
1303 procedure eoy_archive_gre_data(p_payroll_action_id in number,
1304 p_tax_unit_id in number,
1305 p_transmitter_gre_id in number)
1306 is
1307
1308 l_user_entity_id number;
1309 l_taxunit_context_id number;
1310 l_jursd_context_id number;
1311 l_value varchar2(240);
1312 l_sit_uid number;
1313 l_sui_uid number;
1314 l_fips_uid number;
1315 l_counter number;
1316 l_seq_tab pay_ca_t4aeoy_archive.number_data_type_table;
1317 l_context_id_tab pay_ca_t4aeoy_archive.number_data_type_table;
1318 l_context_val_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1319 l_user_entity_name_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1320 l_balance_type_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1321 l_user_entity_value_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1322 l_arch_gre_step number := 0;
1323 l_name varchar2(240);
1324 l_business_group_id number;
1325 l_seq number;
1326 l_context_id number;
1327 l_context_val varchar2(240);
1328 l_employer_ein varchar2(240);
1329 l_address_line_1 varchar2(240);
1330 l_address_line_2 varchar2(240);
1331 l_address_line_3 varchar2(240);
1332 l_town_or_city varchar2(240);
1333 l_province_code varchar2(240);
1334 l_postal_code varchar2(240);
1335 l_country_code varchar2(240);
1336 l_accounting_contact_name varchar2(240);
1337 l_accounting_contact_phone varchar2(240);
1338 l_accounting_contact_area_code varchar2(240);
1339 l_accounting_contact_extension varchar2(240);
1340
1341 l_trans_acct_contact_name varchar2(240);
1342 l_trans_acct_contact_area_code varchar2(240);
1343 l_trans_acct_contact_phone varchar2(240);
1344 l_trans_acct_contact_extn varchar2(240);
1345
1346 -- Added for bug 10388148
1347 l_trans_proprietor_sin_1 varchar2(240);
1348 l_trans_proprietor_sin_2 varchar2(240);
1349 -- Added for bug 10388148
1350
1351 l_proprietor_sin_1 varchar2(240);
1352 l_proprietor_sin_2 varchar2(240);
1353 l_transmitter_name varchar2(240);
1354 l_transmitter_type_indicator varchar2(240);
1355 l_transmitter_number varchar2(240);
1356 l_transmitter_type_code varchar2(240);
1357 l_transmitter_data_type_code varchar2(240);
1358 l_transmitter_addr_line_1 varchar2(240);
1359 l_transmitter_addr_line_2 varchar2(240);
1360 l_transmitter_addr_line_3 varchar2(240);
1361 l_transmitter_city varchar2(240);
1362 l_transmitter_province varchar2(240);
1363 l_Federal_Youth_Hire_Ind varchar2(80);
1364 l_transmitter_postal_code varchar2(240);
1365 l_transmitter_country varchar2(240);
1366 l_transmitter_orgid number;
1367 l_technical_contact_name varchar2(240);
1368 l_technical_contact_phone varchar2(240);
1369 l_technical_contact_area_code varchar2(240);
1370 l_technical_contact_language varchar2(240);
1371 l_technical_contact_email varchar2(240);
1372 l_technical_contact_extn varchar2(240);
1373 l_object_version_number number;
1374 l_some_warning boolean;
1375 l_archive_item_id number;
1376 l_taxation_year varchar2(240);
1377 l_effective_date date;
1378 result number;
1379
1380 /* Alternate query to avoid hr_tax_units_v in t4a archiver */
1381
1382 cursor employer_info is
1383 select nvl(hoi6.ORG_INFORMATION9,ou2.name) GRE_stat_report_name,
1384 bg.business_group_id Business_group_id,
1385 hoi6.ORG_INFORMATION1 Employer_identification_number,
1386 hl2.ADDRESS_LINE_1 GRE_addrline1,
1387 hl2.ADDRESS_LINE_2 GRE_addrline2,
1388 hl2.ADDRESS_LINE_3 GRE_addrline3,
1389 hl2.TOWN_OR_CITY GRE_town_or_city,
1390 DECODE(hl2.STYLE , 'US' , hl2.REGION_2 ,
1391 'CA' , hl2.REGION_1 ,
1392 'CA_GLB',hl2.region_1, ' ') GRE_province,
1393 hl2.POSTAL_CODE GRE_postal_code,
1394 hl2.COUNTRY GRE_country,
1395 nvl(hoi3.ORG_INFORMATION9,ou1.name) trans_stat_report_name,
1396 hl1.ADDRESS_LINE_1 trans_addrline1,
1397 hl1.ADDRESS_LINE_2 trans_addrline2,
1398 hl1.ADDRESS_LINE_3 trans_addrline3,
1399 hl1.TOWN_OR_CITY trans_town_or_city,
1400 DECODE(hl1.STYLE , 'US' , hl1.REGION_2 ,
1401 'CA' , hl1.REGION_1 ,
1402 'CA_GLB',hl1.region_1, ' ') trans_province,
1403 hl1.POSTAL_CODE trans_postal_code,
1404 hl1.COUNTRY trans_country,
1405 hoi2.org_information5 trans_type_indicator,
1406 hoi2.ORG_INFORMATION4 trans_number,
1407 hoi2.ORG_INFORMATION2 trans_type_code,
1408 hoi2.ORG_INFORMATION3 trans_datatype_code,
1409 hoi2.ORG_INFORMATION6 trans_tech_contact_name,
1410 hoi2.ORG_INFORMATION8 trans_tech_contact_phone,
1411 hoi2.ORG_INFORMATION7 trans_tech_contact_areacode,
1412 hoi2.ORG_INFORMATION9 trans_tech_contact_lang,
1413 hoi2.ORG_INFORMATION17 trans_tech_contact_extn,
1414 hoi2.ORG_INFORMATION18 trans_tech_contact_email,
1415 hoi2.ORG_INFORMATION10 trans_acct_contact_name,
1416 hoi2.ORG_INFORMATION11 trans_acct_contact_area_code,
1417 hoi2.ORG_INFORMATION12 trans_acct_contact_phone,
1418 hoi2.ORG_INFORMATION13 trans_acct_contact_extn,
1419 -- Added for bug 10388148
1420 hoi2.ORG_INFORMATION14 trans_proprietor_SIN#1,
1421 hoi2.ORG_INFORMATION15 trans_proprietor_SIN#2
1422 -- Added for bug 10388148
1423 from hr_all_organization_units ou1, /* transmitter org */
1424 hr_organization_information hoi1, /* Transmitter GRE to check
1425 GRE/Legal Classification is enabled */
1426 hr_organization_information hoi2, /* Transmitter GRE to check
1427 'Fed Magnetic Reporting' */
1428 hr_organization_information hoi3, /* Transmitter GRE to check
1429 'Employer Identification' */
1430 hr_locations_all hl1, /* trans location */
1431 hr_all_organization_units ou2, /* GRE Org */
1432 hr_organization_information hoi4, /* GRE to check GRE/Legal
1433 Classification is enabled */
1434 hr_organization_information hoi6, /* GRE to check
1435 'Employer Identification'*/
1436 hr_locations_all hl2, /* GRE location */
1437 per_business_groups bg
1438 where bg.business_group_id = ou1.business_group_id
1439 and bg.legislation_code = 'CA'
1440 and ou1.organization_id = p_transmitter_gre_id
1441 and ou1.organization_id = hoi1.organization_id
1442 and hoi1.org_information_context = 'CLASS'
1443 and hoi1.org_information1 = 'HR_LEGAL'
1444 and hoi1.org_information2 = 'Y'
1445 and ou1.location_id = hl1.location_id
1446 and ou1.organization_id = hoi2.organization_id
1447 and hoi2.org_information_context = 'Fed Magnetic Reporting'
1448 and hoi2.org_information1 = 'Y'
1449 and ou1.organization_id = hoi3.organization_id
1450 and hoi3.org_information_context = 'Canada Employer Identification'
1451 and hoi3.org_information5 in ('T4A/RL1','T4A/RL2')
1452 and bg.business_group_id = ou2.business_group_id
1453 and ou2.organization_id = p_tax_unit_id
1454 and ou2.organization_id = hoi4.organization_id
1455 and hoi4.org_information_context = 'CLASS'
1456 and hoi4.org_information1 = 'HR_LEGAL'
1457 and hoi4.org_information2 = 'Y'
1458 and ou2.location_id = hl2.location_id
1459 and ou2.organization_id = hoi6.organization_id
1460 and hoi6.org_information_context = 'Canada Employer Identification'
1461 and hoi6.ORG_INFORMATION5 in ('T4A/RL1','T4A/RL2');
1462
1463 /* Created this cursor to fix bug#2598802 */
1464 CURSOR c_get_gre_acct_info(cp_gre_id number) IS
1465 select hoi.ORG_INFORMATION10 GRE_acct_contact_name,
1466 hoi.ORG_INFORMATION12 GRE_acct_contact_phone,
1467 hoi.ORG_INFORMATION11 GRE_acct_contact_area_code,
1468 hoi.ORG_INFORMATION13 GRE_acct_contact_extn,
1469 hoi.ORG_INFORMATION14 GRE_Proprietor_SIN#1,
1470 hoi.ORG_INFORMATION15 GRE_Proprietor_SIN#2,
1471 hoi.ORG_INFORMATION16 GRE_Fedyouth_hire_Prgind
1472 from hr_organization_information hoi
1473 where hoi.organization_id = cp_gre_id
1474 and hoi.org_information_context = 'Fed Magnetic Reporting';
1475
1476 begin
1477 /* payroll action level database items */
1478
1479 l_arch_gre_step := 30;
1480
1481 /* Archive the Employer level data */
1482
1483 begin
1484 hr_utility.trace('getting employer data ');
1485
1486 open employer_info;
1487 fetch employer_info
1488 into l_name, l_business_group_id,
1489 l_employer_ein, l_address_line_1,
1490 l_address_line_2, l_address_line_3,
1491 l_town_or_city, l_province_code,
1492 l_postal_code, l_country_code,
1493 l_transmitter_name,
1494 l_transmitter_addr_line_1,
1495 l_transmitter_addr_line_2, l_transmitter_addr_line_3,
1496 l_transmitter_city, l_transmitter_province,
1497 l_transmitter_postal_code, l_transmitter_country,
1498 l_Transmitter_Type_Indicator, l_Transmitter_number,
1499 l_Transmitter_Type_code, l_Transmitter_data_type_code,
1500 l_technical_contact_name, l_technical_contact_phone,
1501 l_technical_contact_area_code, l_technical_contact_language,
1502 l_technical_contact_extn, l_technical_contact_email,
1503 l_trans_acct_contact_name, l_trans_acct_contact_area_code,
1504 l_trans_acct_contact_phone, l_trans_acct_contact_extn,
1505 -- Added for bug 10388148
1506 l_trans_proprietor_sin_1, l_trans_proprietor_sin_2;
1507
1508 /* Added this part to fix bug#2598802 */
1509 open c_get_gre_acct_info(p_tax_unit_id);
1510 fetch c_get_gre_acct_info into l_accounting_contact_name,
1511 l_accounting_contact_phone ,
1512 l_accounting_contact_area_code,
1513 l_accounting_contact_extension,
1514 l_proprietor_sin_1,
1515 l_proprietor_sin_2,
1516 l_federal_youth_hire_ind;
1517
1518
1519 if employer_info%FOUND then
1520 close employer_info;
1521 hr_utility.trace('got employer data ');
1522 else
1523 hr_utility.trace('cannot find employer data ');
1524 l_employer_ein := null;
1525 l_address_line_1 := null;
1526 l_address_line_2 := null;
1527 l_address_line_3 := null;
1528 l_town_or_city := null;
1529 l_province_code := null;
1530 l_postal_code := null;
1531 l_country_code := null;
1532 l_name := null;
1533 l_transmitter_name := null;
1534 l_transmitter_addr_line_1 := null;
1535 l_transmitter_addr_line_2 := null;
1536 l_transmitter_addr_line_3 := null;
1537 l_transmitter_city := null;
1538 l_transmitter_province := null;
1539 l_transmitter_postal_code := null;
1540 l_transmitter_country := null;
1541 l_technical_contact_name := null;
1542 l_technical_contact_phone := null;
1543 l_technical_contact_language := null;
1544
1545 close employer_info;
1546 hr_utility.raise_error;
1547 end if;
1548
1549 /* Added this part to fix bug#2598802 */
1550 if c_get_gre_acct_info%found then
1551 close c_get_gre_acct_info;
1552 else
1553 l_accounting_contact_name := null;
1554 l_accounting_contact_phone := null;
1555 l_accounting_contact_area_code := null;
1556 l_accounting_contact_extension := null;
1557 l_proprietor_sin_1 := null;
1558 l_proprietor_sin_2 := null;
1559 l_federal_youth_hire_ind := null;
1560 end if;
1561
1562 if l_accounting_contact_name is null then
1563 l_accounting_contact_name := l_trans_acct_contact_name;
1564 end if;
1565
1566 if l_accounting_contact_phone is null then
1567 l_accounting_contact_phone := l_trans_acct_contact_phone;
1568 end if;
1569
1570 if l_accounting_contact_area_code is null then
1571 l_accounting_contact_area_code := l_trans_acct_contact_area_code;
1572 end if;
1573
1574 if l_accounting_contact_extension is null then
1575 l_accounting_contact_extension := l_trans_acct_contact_extn;
1576 end if;
1577
1578 -- Added for bug 10388148
1579 if l_proprietor_sin_1 is null then
1580 l_proprietor_sin_1 := l_trans_proprietor_sin_1;
1581 end if;
1582
1583 if l_proprietor_sin_2 is null then
1584 l_proprietor_sin_2 := l_trans_proprietor_sin_2;
1585 end if;
1586 -- Added for bug 10388148
1587
1588 end;
1589
1590
1591 begin
1592 select to_char(effective_date,'YYYY'),
1593 add_months(trunc(effective_date, 'Y'),12) - 1
1594 into l_taxation_year,
1595 l_effective_date
1596 from pay_payroll_actions
1597 where payroll_action_id = p_payroll_action_id;
1598
1599 exception when no_data_found then
1600 l_taxation_year := null;
1601 l_effective_date := null;
1602
1603 end;
1604
1605 select context_id
1606 into l_taxunit_context_id
1607 from ff_contexts
1608 where context_name = 'TAX_UNIT_ID';
1609
1610 l_counter := 0;
1611 l_arch_gre_step := 40;
1612
1613 l_counter := l_counter + 1;
1614 l_user_entity_name_tab(l_counter) := 'CAEOY_TAXATION_YEAR';
1615 l_user_entity_value_tab(l_counter) := l_taxation_year;
1616
1617 l_counter := l_counter + 1;
1618 l_user_entity_name_tab(l_counter) := 'CAEOY_TAX_UNIT_ID';
1619 l_user_entity_value_tab(l_counter) := p_tax_unit_id;
1620
1621 l_counter := l_counter + 1;
1622 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_COUNTRY';
1623 l_user_entity_value_tab(l_counter) := l_transmitter_country;
1624
1625 l_counter := l_counter + 1;
1626 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NAME';
1627 -- l_user_entity_value_tab(l_counter) := l_transmitter_country;
1628 l_user_entity_value_tab(l_counter) := l_transmitter_name;
1629
1630 l_counter := l_counter + 1;
1631 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE1';
1632 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_1;
1633
1634 l_counter := l_counter + 1;
1635 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE2';
1636 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_2;
1637
1638 -- Bug 4517693
1639 l_counter := l_counter + 1;
1640 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_ADDRESS_LINE3';
1641 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_3;
1642
1643 l_counter := l_counter + 1;
1644 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_CITY';
1645 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1646
1647 l_counter := l_counter + 1;
1648 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_PROVINCE';
1649 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1650
1651 l_counter := l_counter + 1;
1652 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_POSTAL_CODE';
1653 l_user_entity_value_tab(l_counter) := l_transmitter_postal_code;
1654
1655 l_counter := l_counter + 1;
1656 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_INDICATOR';
1657 l_user_entity_value_tab(l_counter) := l_transmitter_type_indicator;
1658
1659 l_counter := l_counter + 1;
1660 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_NUMBER';
1661 l_user_entity_value_tab(l_counter) := l_transmitter_number;
1662
1663 l_counter := l_counter + 1;
1664 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_TYPE_CODE';
1665 l_user_entity_value_tab(l_counter) := l_transmitter_type_code;
1666
1667 l_counter := l_counter + 1;
1668 l_user_entity_name_tab(l_counter) := 'CAEOY_TRANSMITTER_DATA_TYPE_CODE';
1669 l_user_entity_value_tab(l_counter) := l_transmitter_data_type_code;
1670
1671 l_counter := l_counter + 1;
1672 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_NAME';
1673 l_user_entity_value_tab(l_counter) := l_technical_contact_name;
1674
1675 l_counter := l_counter + 1;
1676 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_PHONE';
1677 l_user_entity_value_tab(l_counter) := l_technical_contact_phone;
1678
1679 l_counter := l_counter + 1;
1680 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_AREA_CODE';
1681 l_user_entity_value_tab(l_counter) := l_technical_contact_area_code;
1682
1683 l_counter := l_counter + 1;
1684 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EXTN';
1685 l_user_entity_value_tab(l_counter) := l_technical_contact_extn;
1686
1687 l_counter := l_counter + 1;
1688 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_EMAIL';
1689 l_user_entity_value_tab(l_counter) := l_technical_contact_email;
1690
1691 l_counter := l_counter + 1;
1692 l_user_entity_name_tab(l_counter) := 'CAEOY_TECHNICAL_CONTACT_LANGUAGE';
1693 l_user_entity_value_tab(l_counter) := l_technical_contact_language;
1694
1695 l_counter := l_counter + 1;
1696 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_NAME';
1697 l_user_entity_value_tab(l_counter) := l_accounting_contact_name;
1698
1699 l_counter := l_counter + 1;
1700 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_PHONE';
1701 l_user_entity_value_tab(l_counter) := l_accounting_contact_phone ;
1702
1703 l_counter := l_counter + 1;
1704 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE';
1705 l_user_entity_value_tab(l_counter) := l_accounting_contact_area_code ;
1706
1707 l_counter := l_counter + 1;
1708 l_user_entity_name_tab(l_counter) := 'CAEOY_ACCOUNTING_CONTACT_EXTENSION';
1709 l_user_entity_value_tab(l_counter) := l_accounting_contact_extension ;
1710
1711
1712 l_counter := l_counter + 1;
1713 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_NAME';
1714 l_user_entity_value_tab(l_counter) := l_name;
1715
1716 l_counter := l_counter + 1;
1717 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER';
1718 l_user_entity_value_tab(l_counter) := l_employer_ein;
1719
1720 l_counter := l_counter + 1;
1721 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE1';
1722 l_user_entity_value_tab(l_counter) := l_address_line_1;
1723
1724 l_counter := l_counter + 1;
1725 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE2';
1726 l_user_entity_value_tab(l_counter) := l_address_line_2;
1727
1728 -- Added by Saurabh for bug 4517693
1729 l_counter := l_counter + 1;
1730 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_ADDRESS_LINE3';
1731 l_user_entity_value_tab(l_counter) := l_address_line_3;
1732
1733 l_counter := l_counter + 1;
1734 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_CITY';
1735 l_user_entity_value_tab(l_counter) := l_town_or_city;
1736
1737 l_counter := l_counter + 1;
1738 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_PROVINCE';
1739 l_user_entity_value_tab(l_counter) := l_province_code;
1740
1741 l_counter := l_counter + 1;
1742 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_COUNTRY';
1743 l_user_entity_value_tab(l_counter) := l_country_code;
1744
1745 l_counter := l_counter + 1;
1746 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYER_POSTAL_CODE';
1747 l_user_entity_value_tab(l_counter) := l_postal_code;
1748
1749
1750 l_counter := l_counter + 1;
1751 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN1';
1752 l_user_entity_value_tab(l_counter) := l_proprietor_sin_1;
1753
1754 l_counter := l_counter + 1;
1755 l_user_entity_name_tab(l_counter) := 'CAEOY_PROPRIETOR_SIN2';
1756 l_user_entity_value_tab(l_counter) := l_proprietor_sin_2;
1757
1758 l_counter := l_counter + 1;
1759 l_user_entity_name_tab(l_counter):='CAEOY_FEDERAL_YOUTH_HIRE_PROGRAM_INDICATOR';
1760 l_user_entity_value_tab(l_counter) := l_federal_youth_hire_ind;
1761
1762 for i in 1..l_counter loop
1763
1764 l_arch_gre_step := 42;
1765 hr_utility.trace('calling archive API ' || l_user_entity_name_tab(i));
1766 ff_archive_api.create_archive_item(
1767 -- p_validate => 'TRUE'
1768 p_archive_item_id => l_archive_item_id
1769 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1770 ,p_archive_value => l_user_entity_value_tab(i)
1771 ,p_archive_type => 'PA'
1772 ,p_action_id => p_payroll_action_id
1773 ,p_legislation_code => 'CA'
1774 ,p_object_version_number => l_object_version_number
1775 ,p_some_warning => l_some_warning
1776 );
1777 hr_utility.trace('Ended calling archive API');
1778 l_arch_gre_step := 47;
1779 end loop;
1780
1781 /* Removed the unnecessary archiving of db items with dimension _GRE_YTD */
1782
1783 --hr_utility.trace_off;
1784 g_archive_flag := 'Y';
1785 exception
1786 when others then
1787 g_archive_flag := 'N';
1788 if l_transmitter_name is null then
1789 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1790 hr_utility.set_message_token('ORGIND','GRE');
1791 /* push message into pay_message_lines */
1792 pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1793 pay_core_utils.push_token('ORGIND','GRE');
1794 hr_utility.raise_error;
1795 else
1796 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1797 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1798 hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1799 hr_utility.raise_error;
1800 end if;
1801 raise hr_utility.hr_error;
1802
1803 end eoy_archive_gre_data;
1804
1805 /* Name : chk_gre_archive
1806 Purpose : Function to check if the employer level data has been archived
1807 or not.
1808 Arguments :
1809 Notes :
1810 */
1811
1812 function chk_gre_archive (p_payroll_action_id number) return boolean is
1813
1814 l_flag varchar2(1);
1815
1816 cursor c_chk_payroll_action is
1817 select 'Y'
1818 from dual
1819 where exists (select null
1820 from ff_archive_items fai
1821 where fai.context1 = p_payroll_action_id
1822 and archive_type = 'PA');
1823 begin
1824
1825 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1826
1827 if g_archive_flag = 'Y' then
1828 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1829 return (TRUE);
1830 else
1831
1832 hr_utility.trace('chk_gre_archive - opening cursor');
1833
1834 open c_chk_payroll_action;
1835 fetch c_chk_payroll_action into l_flag;
1836 if c_chk_payroll_action%FOUND then
1837 hr_utility.trace('chk_gre_archive - found in cursor');
1838 g_archive_flag := 'Y';
1839 else
1840 hr_utility.trace('chk_gre_archive - not found in cursor');
1841 g_archive_flag := 'N';
1842 end if;
1843
1844 hr_utility.trace('chk_gre_archive - closing cursor');
1845 close c_chk_payroll_action;
1846 if g_archive_flag = 'Y' then
1847 hr_utility.trace('chk_gre_archive - returning true');
1848 return (TRUE);
1849 else
1850 hr_utility.trace('chk_gre_archive - returning false');
1851 return(FALSE);
1852 end if;
1853 end if;
1854 end chk_gre_archive;
1855
1856 /* Name : eoy_archinit
1857 Purpose : This performs the context initialization for the year end
1858 pre-process.
1859 Arguments :
1860 Notes :
1861 */
1862
1863
1864 procedure eoy_archinit(p_payroll_action_id in number) is
1865 l_jurisdiction_code VARCHAR2(30);
1866 l_tax_unit_id NUMBER(15);
1867 l_archive boolean:= FALSE;
1868 l_step number := 0;
1869
1870 cursor c_get_min_chunk is
1871 select min(paa.chunk_number)
1872 from pay_assignment_actions paa
1873 where paa.payroll_action_id = p_payroll_action_id;
1874
1875 begin
1876 open c_get_min_chunk;
1877 fetch c_get_min_chunk into g_min_chunk;
1878 l_step := 1;
1879 if c_get_min_chunk%NOTFOUND then
1880 g_min_chunk := -1;
1881 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1882 end if;
1883 close c_get_min_chunk;
1884
1885 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1886 l_step := 2;
1887 l_archive := chk_gre_archive(p_payroll_action_id);
1888
1889 l_step := 3;
1890 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1891 exception
1892 when others then
1893 raise_application_error(-20001,'eoy_archinit at '
1894 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1895 end eoy_archinit;
1896
1897 /* Name : eoy_archive_data_new_format
1898 Purpose : This performs archiving of payroll data for employee as per
1899 new format, bug 6456662.
1900 Arguments :
1901 Notes :
1902 */
1903
1904 procedure eoy_archive_data_new_format(p_assactid in number,
1905 p_effective_date in date,
1906 p_negative_balance_exists in varchar2 default 'N') is
1907
1908 l_aaid pay_assignment_actions.assignment_action_id%type;
1909 l_aaseq pay_assignment_actions.action_sequence%type;
1910 l_asgid pay_assignment_actions.assignment_id%type;
1911 l_date_earned date;
1912 l_user_entity_name_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1913 l_balance_type_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1914 l_user_entity_value_tab pay_ca_t4aeoy_archive.char240_data_type_table;
1915 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1916 l_business_group_id pay_assignment_actions.tax_unit_id%type;
1917 l_year_start date;
1918 l_year_end date;
1919 l_count number := 0;
1920
1921 lv_footnote_element varchar2(50);
1922 l_box38_footnote_code varchar2(10) := NULL;
1923 l_negative_balance_exists varchar2(5);
1924 l_chunk number;
1925 l_step number := 0;
1926 lv_serial_number varchar2(30);
1927 l_person_id number;
1928 earning_exists number := 0;
1929 result number := 0;
1930
1931 --l_box34_regno_flag added for bug 10099479
1932 l_box34_regno_flag number :=0;
1933
1934
1935 old_l_value number := 0;
1936 old_l_value1 number := 0;
1937 old_l_value2 number := 0;
1938 arch_l_value number := 0;
1939 l_registration_no varchar2(150);
1940 old_l_registration_no varchar2(150);
1941 old_l_registration_no1 varchar2(150);
1942 old_l_registration_no2 varchar2(150);
1943 arch_l_registration_no varchar2(150);
1944 l_balance_name varchar2(150);
1945 l_single_footnote_code varchar2(10);
1946
1947 l_payroll_action_id number;
1948 l_defined_balance_id number;
1949 l_archive_item_id number;
1950 l_element_type_id number(20);
1951 l_check_flag varchar2(2);
1952
1953 l_ele_classification_id number(20);
1954 l_ele_classification_name varchar2(50);
1955 l_value number := 0;
1956 l_object_version_number number;
1957
1958 lv_emplr_regno varchar2(20);
1959 lv_emplr_regno1 varchar2(20);
1960 lv_emplr_regno2 varchar2(20);
1961 ln_emplr_regamt number(30);
1962 ln_emplr_regamt1 number(30);
1963 ln_emplr_regamt2 number(30);
1964 l_some_warning boolean;
1965
1966 l_action_information_id_1 NUMBER ;
1967 l_object_version_number_1 NUMBER ;
1968
1969 l_status_indian varchar2(1);
1970
1971 /* cursor used to archive the footnote code values */
1972 cursor c_balance_feed_info (p_balance_name varchar2) is
1973 select distinct pet.element_information18,
1974 pbt1.balance_name
1975 from pay_balance_feeds_f pbf,
1976 pay_balance_types pbt,
1977 pay_balance_types pbt1,
1978 pay_input_values_f piv,
1979 pay_element_types_f pet,
1980 fnd_lookup_values flv
1981 where pbt.balance_name = p_balance_name
1982 and pbf.balance_type_id = pbt.balance_type_id
1983 and pbf.input_value_id = piv.input_value_id
1984 and piv.element_type_id = pet.element_type_id
1985 and pet.business_group_id = l_business_group_id
1986 and pbt1.balance_type_id = pet.element_information10
1987 and pet.element_information18 = flv.lookup_code
1988 and flv.lookup_type = 'PAY_CA_T4A_FOOTNOTES'
1989 and flv.language = userenv('LANG')
1990 order by pet.element_information18;
1991
1992 /* cursor used to archive the Pension Adjustment Registration Number */
1993 cursor c_reg_balance_feed_info (p_balance_name varchar2) is
1994 select distinct nvl(pet.element_information20,'NOT FOUND'),
1995 pbt1.balance_name,pet.element_type_id,
1996 pet.classification_id
1997 from pay_balance_feeds_f pbf,
1998 pay_balance_types pbt,
1999 pay_balance_types pbt1,
2000 pay_input_values_f piv,
2001 pay_element_types_f pet
2002 where pbt.balance_name = p_balance_name
2003 and pbf.balance_type_id = pbt.balance_type_id
2004 and pbf.input_value_id = piv.input_value_id
2005 and piv.element_type_id = pet.element_type_id
2006 and pet.business_group_id = l_business_group_id
2007 and pbt1.balance_type_id = pet.element_information10
2008 -- and pet.element_information_category = 'CA_EARNINGS'
2009 and pet.element_information20 is not null;
2010
2011 /* Cursor for T4A Nonbox Footnote archive to fix bug#2175045 */
2012 /* Modified the cur_non_box_mesg cursor to fix bug#3641353.
2013 Kept the Jurisdiction context validation because of performance
2014 for T4A Reporting and added action_type 'B' Balance Adj's */
2015 CURSOR cur_non_box_mesg( cp_asgact_id in number,
2016 cp_eff_date in date) is
2017 select distinct prrv1.result_value,
2018 prrv2.result_value,
2019 hoi.organization_id,
2020 run_ppa.effective_date,
2021 run_paa.assignment_action_id
2022 from pay_run_result_values prrv1
2023 , pay_run_result_values prrv2
2024 , pay_run_results prr
2025 , pay_element_types_f pet
2026 , pay_input_values_f piv1
2027 , pay_input_values_f piv2
2028 , pay_assignment_actions run_paa
2029 , pay_payroll_actions run_ppa
2030 , pay_assignment_actions arch_paa
2031 , pay_payroll_actions arch_ppa
2032 , per_all_assignments_f arch_paf
2033 , per_all_assignments_f all_paf
2034 , hr_all_organization_units hou
2035 , hr_organization_information hoi
2036 where arch_paa.assignment_action_id = cp_asgact_id
2037 and arch_ppa.payroll_action_id = arch_paa.payroll_action_id
2038 and hou.business_group_id = arch_ppa.business_group_id
2039 and hou.organization_id = hoi.organization_id
2040 and hoi.organization_id =
2041 to_number(pycadar_pkg.get_parameter('TRANSFER_GRE',arch_ppa.legislative_parameters))
2042 and hoi.org_information_context = 'Canada Employer Identification'
2043 and hoi.org_information5 IN ('T4A/RL1','T4A/RL2')
2044 and run_paa.tax_unit_id = hou.organization_id
2045 and run_ppa.payroll_action_id = run_paa.payroll_action_id
2046 and run_ppa.action_type in ( 'R', 'Q', 'B' )
2047 and to_char(run_ppa.effective_date,'YYYY' ) =
2048 to_char(cp_eff_date,'YYYY')
2049 and run_paa.action_status = 'C'
2050 and pet.element_name = lv_footnote_element --'T4A NonBox Footnotes'
2051 and prr.assignment_action_id = run_paa.assignment_action_id
2052 and prr.element_type_id = pet.element_type_id
2053 and piv1.element_type_id = pet.element_type_id
2054 and piv1.name = 'Message'
2055 and prrv1.run_result_id = prr.run_result_id
2056 and prrv1.input_value_id = piv1.input_value_id
2057 and piv2.element_type_id = pet.element_type_id
2058 and piv2.name = 'Amount'
2059 and prrv2.run_result_id = prrv1.run_result_id
2060 and prrv2.input_value_id = piv2.input_value_id
2061 and arch_paf.assignment_id = arch_paa.assignment_id
2062 and to_char(cp_eff_date,'YYYY')
2063 between to_char(arch_paf.effective_start_date,'YYYY')
2064 and to_char(arch_paf.effective_end_date,'YYYY')
2065 and all_paf.person_id = arch_paf.person_id
2066 and to_char(cp_eff_date,'YYYY')
2067 between to_char(all_paf.effective_start_date,'YYYY')
2068 and to_char(all_paf.effective_end_date,'YYYY')
2069 and run_paa.assignment_id = all_paf.assignment_id
2070 and exists (select 1
2071 from pay_action_contexts pac,ff_contexts ffc
2072 where ffc.context_name = 'JURISDICTION_CODE'
2073 and pac.context_id = ffc.context_id
2074 and pac.assignment_id = run_paa.assignment_id);
2075
2076
2077 /* Cursor to check the Employer Level PP Registration Number
2078 Bug fix#2696309 */
2079 CURSOR c_get_emplr_reg_no(cp_tax_unit_id varchar2
2080 ,cp_payroll_action_id number
2081 ,cp_reg_no varchar2
2082 ,cp_eff_date date) IS
2083 select action_information4,to_number(action_information5)
2084 from pay_action_information
2085 where action_context_id = cp_payroll_action_id
2086 and effective_date = cp_eff_date
2087 AND tax_unit_id = cp_tax_unit_id
2088 and action_information_category = 'CAEOY PENSION PLAN INFO'
2089 AND ACTION_INFORMATION4 = cp_reg_no;
2090
2091 CURSOR c_get_latest_asg(p_person_id number ) IS
2092 select paa.assignment_action_id
2093 from pay_assignment_actions paa,
2094 per_all_assignments_f paf,
2095 pay_payroll_actions ppa,
2096 pay_action_classifications pac
2097 where paf.person_id = p_person_id
2098 and paa.assignment_id = paf.assignment_id
2099 and paa.tax_unit_id = l_tax_unit_id
2100 and paa.payroll_action_id = ppa.payroll_action_id
2101 and ppa.action_type = pac.action_type
2102 and pac.classification_name = 'SEQUENCED'
2103 and ppa.effective_date +0 between paf.effective_start_date
2104 and paf.effective_end_date
2105 and ppa.effective_date +0 between l_year_start and
2106 l_year_end
2107 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
2108 and paa.source_action_id is null)
2109 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
2110 and paa.source_action_id is not null )
2111 or (ppa.action_type = 'V' and ppa.run_type_id is null
2112 and paa.run_type_id is not null
2113 and paa.source_action_id is null))
2114 order by paa.action_sequence desc;
2115
2116
2117 /* New cursors added for Federal YE Amendment Pre-Process Validation */
2118
2119 CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
2120 select ppa.report_type
2121 from pay_payroll_actions ppa,pay_assignment_actions paa
2122 where paa.assignment_action_id = cp_locked_actid
2123 and ppa.payroll_action_id = paa.payroll_action_id;
2124
2125 CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
2126 select locked_action_id
2127 from pay_action_interlocks
2128 where locking_action_id = cp_locking_act_id;
2129
2130 /* New cursor for checking for the employee been a Status Indian */
2131 CURSOR c_get_status_indian(cp_assign number,
2132 cp_effec_date date) IS
2133 select ca_tax_information1
2134 from pay_ca_emp_fed_tax_info_f pca
2135 where pca.assignment_id = cp_assign
2136 and cp_effec_date between pca.effective_start_date and
2137 pca.effective_end_date;
2138
2139 begin
2140
2141 -- hr_utility.trace_on('Y','ORACLEMM');
2142
2143 l_count := 0;
2144 l_box38_footnote_code := '00';
2145 -- l_negative_balance_exists := 'N';
2146 l_negative_balance_exists := p_negative_balance_exists;
2147
2148 hr_utility.set_location ('archive_data',1);
2149 hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
2150
2151
2152 SELECT aa.assignment_id,
2153 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2154 aa.tax_unit_id,
2155 aa.chunk_number,
2156 aa.payroll_action_id,
2157 aa.serial_number
2158 into l_asgid,
2159 l_date_earned,
2160 l_tax_unit_id,
2161 l_chunk,
2162 l_payroll_action_id,
2163 lv_serial_number
2164 FROM pay_assignment_actions aa
2165 WHERE aa.assignment_action_id = p_assactid;
2166
2167 /*Bug 4021563 Fetching the Status Indian flag */
2168 open c_get_status_indian(l_asgid,p_effective_date);
2169 fetch c_get_status_indian
2170 into l_status_indian;
2171 close c_get_status_indian;
2172
2173
2174 l_year_start := trunc(p_effective_date, 'Y');
2175 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2176
2177 hr_utility.trace('l_date_earned : '|| to_char(l_date_earned));
2178
2179 /* YE-2001 change to avoid hr_ca_tax_units_v view */
2180 select business_group_id
2181 into l_business_group_id
2182 from hr_all_organization_units
2183 where organization_id = l_tax_unit_id;
2184
2185 l_step := 1;
2186
2187 begin
2188 open c_get_latest_asg(lv_serial_number );
2189 fetch c_get_latest_asg into l_aaid;
2190 close c_get_latest_asg;
2191 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2192
2193 exception
2194 when no_data_found then
2195 l_aaid := -9999;
2196 raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2197 ||to_char(l_person_id));
2198 end;
2199 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2200 hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2201 hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2202
2203 -- code changes for T4A Redesign bug 6456662 starts here. sneelapa.
2204
2205 -- IF condition added for bug 6456662, 9980854
2206 --IF ( to_number(to_char(l_year_end,'YYYY')) < 2010) then
2207 /* Commented IF condition and modified code to Archive value 0 (zero)
2208 AMOUNT 026 and 027 from 2010 onwards.
2209 */
2210
2211 l_count := l_count + 1;
2212 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT026_PER_GRE_YTD';
2213 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT026';
2214
2215 l_count := l_count + 1;
2216 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT027_PER_GRE_YTD';
2217 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT027';
2218
2219 -- END IF;
2220
2221 l_count := l_count + 1;
2222 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT028_PER_GRE_YTD';
2223 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT028';
2224
2225 l_count := l_count + 1;
2226 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT030_PER_GRE_YTD';
2227 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT030';
2228
2229 l_count := l_count + 1;
2230 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT032_PER_GRE_YTD';
2231 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT032';
2232
2233 l_count := l_count + 1;
2234 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT034_PER_GRE_YTD';
2235 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT034';
2236
2237 l_count := l_count + 1;
2238 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT036_PER_GRE_YTD';
2239 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT036';
2240
2241 l_count := l_count + 1;
2242 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT040_PER_GRE_YTD';
2243 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT040';
2244
2245 l_count := l_count + 1;
2246 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT042_PER_GRE_YTD';
2247 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT042';
2248
2249 l_count := l_count + 1;
2250 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT046_PER_GRE_YTD';
2251 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT046';
2252
2253 l_count := l_count + 1;
2254 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT102_PER_GRE_YTD';
2255 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT102';
2256
2257 l_count := l_count + 1;
2258 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT104_PER_GRE_YTD';
2259 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT104';
2260
2261 l_count := l_count + 1;
2262 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT105_PER_GRE_YTD';
2263 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT105';
2264
2265 l_count := l_count + 1;
2266 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT106_PER_GRE_YTD';
2267 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT106';
2268
2269 l_count := l_count + 1;
2270 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT107_PER_GRE_YTD';
2271 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT107';
2272
2273 l_count := l_count + 1;
2274 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT108_PER_GRE_YTD';
2275 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT108';
2276
2277 l_count := l_count + 1;
2278 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT109_PER_GRE_YTD';
2279 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT109';
2280
2281 l_count := l_count + 1;
2282 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT110_PER_GRE_YTD';
2283 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT110';
2284
2285 l_count := l_count + 1;
2286 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT111_PER_GRE_YTD';
2287 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT111';
2288
2289 l_count := l_count + 1;
2290 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT115_PER_GRE_YTD';
2291 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT115';
2292
2293 l_count := l_count + 1;
2294 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT116_PER_GRE_YTD';
2295 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT116';
2296
2297 l_count := l_count + 1;
2298 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT117_PER_GRE_YTD';
2299 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT117';
2300
2301 l_count := l_count + 1;
2302 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT118_PER_GRE_YTD';
2303 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT118';
2304
2305 l_count := l_count + 1;
2306 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT119_PER_GRE_YTD';
2307 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT119';
2308
2309 l_count := l_count + 1;
2310 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT122_PER_GRE_YTD';
2311 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT122';
2312
2313 l_count := l_count + 1;
2314 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT123_PER_GRE_YTD';
2315 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT123';
2316
2317 l_count := l_count + 1;
2318 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT124_PER_GRE_YTD';
2319 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT124';
2320
2321 l_count := l_count + 1;
2322 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT125_PER_GRE_YTD';
2323 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT125';
2324
2325 l_count := l_count + 1;
2326 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT126_PER_GRE_YTD';
2327 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT126';
2328
2329 l_count := l_count + 1;
2330 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT127_PER_GRE_YTD';
2331 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT127';
2332
2333 l_count := l_count + 1;
2334 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT129_PER_GRE_YTD';
2335 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT129';
2336
2337 l_count := l_count + 1;
2338 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT130_PER_GRE_YTD';
2339 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT130';
2340
2341 l_count := l_count + 1;
2342 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT131_PER_GRE_YTD';
2343 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT131';
2344
2345 l_count := l_count + 1;
2346 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT132_PER_GRE_YTD';
2347 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT132';
2348
2349 l_count := l_count + 1;
2350 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT133_PER_GRE_YTD';
2351 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT133';
2352
2353 l_count := l_count + 1;
2354 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT134_PER_GRE_YTD';
2355 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT134';
2356
2357 l_count := l_count + 1;
2358 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT135_PER_GRE_YTD';
2359 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT135';
2360
2361 -- IF condition added for bug 6456662, 9980854
2362 --IF ( to_number(to_char(l_year_end,'YYYY')) < 2010) then
2363 /* Commented IF condition and modified code to Archive value 0 (zero)
2364 AMOUNT 142 and 143 from 2010 onwards.
2365 */
2366
2367 l_count := l_count + 1;
2368 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT142_PER_GRE_YTD';
2369 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT142';
2370
2371 l_count := l_count + 1;
2372 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT143_PER_GRE_YTD';
2373 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT143';
2374
2375 -- END IF;
2376
2377 l_count := l_count + 1;
2378 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT144_PER_GRE_YTD';
2379 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT144';
2380
2381 l_count := l_count + 1;
2382 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT146_PER_GRE_YTD';
2383 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT146';
2384
2385 l_count := l_count + 1;
2386 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT148_PER_GRE_YTD';
2387 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT148';
2388
2389 l_count := l_count + 1;
2390 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT150_PER_GRE_YTD';
2391 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT150';
2392
2393 l_count := l_count + 1;
2394 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT152_PER_GRE_YTD';
2395 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT152';
2396
2397 l_count := l_count + 1;
2398 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT154_PER_GRE_YTD';
2399 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT154';
2400
2401 l_count := l_count + 1;
2402 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT156_PER_GRE_YTD';
2403 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT156';
2404
2405 l_count := l_count + 1;
2406 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT158_PER_GRE_YTD';
2407 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT158';
2408
2409 l_count := l_count + 1;
2410 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT180_PER_GRE_YTD';
2411 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT180';
2412
2413 l_count := l_count + 1;
2414 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_OTHER_INFO_AMOUNT190_PER_GRE_YTD';
2415 l_balance_type_tab(l_count) := 'T4A_OTHER_INFO_AMOUNT190';
2416
2417 -- code changes for T4A Redesign bug 6456662 ends here. sneelapa.
2418
2419 /* Initializing variables as part of bug fix#2426517 */
2420
2421 if ( (pay_ca_balance_pkg.call_ca_balance_get_value
2422 ( 'Gross Earnings',
2423 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
2424 l_tax_unit_id, l_business_group_id, NULL)
2425 <> 0) OR
2426 (pay_ca_balance_pkg.call_ca_balance_get_value
2427 ( 'T4A No Gross Earnings',
2428 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
2429 l_tax_unit_id, l_business_group_id, NULL)
2430 <> 0) ) then
2431
2432 earning_exists := 1;
2433
2434 hr_utility.trace('starting loop for balances');
2435
2436 for i in 1 .. l_count
2437 loop
2438 result := 0;
2439 /* Now, set up the jurisdiction context for the db items that
2440 need the jurisdiction as a context.Here we are archiving all the
2441 jurisdictions we got from pay_action_contexts for all
2442 assignment_actions. So even though a particular assignment_action
2443 is for aparticular jurisdiction the archiver table has data for
2444 all the jurisdictions, but values of irrelevant jurisdictions will
2445 be 0 */
2446
2447 /* To get balances you must use the highest assignment action . Since
2448 T4A does not have Jurisdiction specific balances first we have to
2449 sum up balances for all jurisdictions. */
2450
2451 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2452 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2453
2454 hr_utility.trace('i is ' ||to_char(i));
2455 hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2456 hr_utility.trace('AAID is ' || to_char(l_aaid));
2457 hr_utility.trace('ASGID is ' || to_char(l_asgid));
2458 hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2459 hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2460
2461 result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2462 ( l_balance_type_tab(i),
2463 'YTD' ,
2464 l_aaid,
2465 l_asgid,
2466 NULL,
2467 'PER' ,
2468 l_tax_unit_id,
2469 l_business_group_id,
2470 NULL
2471 );
2472
2473 hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2474 hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2475 hr_utility.trace('Result is ' || to_char(result));
2476
2477 /* Added this condition to fix bug#2598777 */
2478 if l_user_entity_name_tab(i) = 'CAEOY_T4A_OTHER_INFO_AMOUNT034_PER_GRE_YTD' then
2479 result := round(result);
2480 end if;
2481 /* Bug 4021563 Added code for Status Indian type employee */
2482 /* if (l_balance_type_tab(i) in ( 'T4A_BOX26',
2483 'T4A_BOX27',
2484 'T4A_BOX28') and l_status_indian = 'Y') then
2485 */
2486 if (l_balance_type_tab(i) in ( 'T4A_OTHER_INFO_AMOUNT026',
2487 'T4A_OTHER_INFO_AMOUNT027',
2488 'T4A_OTHER_INFO_AMOUNT028') and l_status_indian = 'Y') then
2489 result := 0;
2490
2491 end if;
2492
2493 IF (l_balance_type_tab(i) in ( 'T4A_OTHER_INFO_AMOUNT026',
2494 'T4A_OTHER_INFO_AMOUNT027',
2495 'T4A_OTHER_INFO_AMOUNT142',
2496 'T4A_OTHER_INFO_AMOUNT143') and to_number(to_char(l_year_end,'YYYY')) >= 2010)
2497 then
2498 result := 0;
2499 END IF;
2500
2501 ff_archive_api.create_archive_item(
2502 p_archive_item_id => l_archive_item_id
2503 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
2504 ,p_archive_value => result
2505 ,p_archive_type => 'AAP'
2506 ,p_action_id => p_assactid
2507 ,p_legislation_code => 'CA'
2508 ,p_object_version_number => l_object_version_number
2509 ,p_context_name1 => 'TAX_UNIT_ID'
2510 ,p_context1 => l_tax_unit_id
2511 ,p_some_warning => l_some_warning
2512 );
2513
2514 if result < 0 then
2515 l_negative_balance_exists := 'Y';
2516 end if;
2517
2518 end loop; /* for archiving all T4A Balances */
2519
2520 /* start registration number archiving */
2521
2522 l_registration_no := NULL;
2523 old_l_registration_no := NULL;
2524 arch_l_registration_no := NULL;
2525 old_l_value := 0;
2526 old_l_registration_no1 := NULL;
2527 old_l_value1 := 0;
2528 old_l_registration_no2 := NULL;
2529 old_l_value2 := 0;
2530 arch_l_value := 0;
2531 l_value := 0;
2532
2533 begin
2534
2535 --open c_reg_balance_feed_info('T4A_BOX34');
2536 open c_reg_balance_feed_info('T4A_OTHER_INFO_AMOUNT034');
2537
2538 loop
2539
2540 fetch c_reg_balance_feed_info into l_registration_no,l_balance_name,
2541 l_element_type_id,l_ele_classification_id;
2542 exit when c_reg_balance_feed_info%NOTFOUND;
2543
2544 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2545 ( l_balance_name,
2546 'YTD' ,
2547 l_aaid,
2548 l_asgid,
2549 NULL,
2550 'PER' ,
2551 l_tax_unit_id,
2552 l_business_group_id,
2553 NULL );
2554
2555 if l_value is null then
2556 l_value := 0;
2557 end if;
2558
2559 hr_utility.trace('after check null l_value:'||to_char(l_value));
2560
2561
2562 /* Condition to check the amounts and determine the registration
2563 number to archive Bug fix 2408456 */
2564 if old_l_value = 0 then
2565 hr_utility.trace('in reg1');
2566 old_l_value := l_value;
2567 old_l_registration_no := l_registration_no;
2568 elsif old_l_value1 = 0 then
2569 hr_utility.trace('in reg2');
2570 old_l_value1 := l_value;
2571 old_l_registration_no1 := l_registration_no;
2572 elsif old_l_value2 = 0 then
2573 hr_utility.trace('in reg3');
2574 old_l_value2 := l_value;
2575 old_l_registration_no2 := l_registration_no;
2576 else
2577 if l_value > nvl(old_l_value,0) then
2578 hr_utility.trace('old_l1');
2579 old_l_value := l_value;
2580 old_l_registration_no := l_registration_no;
2581 elsif l_value > nvl(old_l_value1,0) then
2582 hr_utility.trace('old_2');
2583 old_l_value1 := l_value;
2584 old_l_registration_no1 := l_registration_no;
2585 elsif l_value > nvl(old_l_value2,0) then
2586 old_l_value2 := l_value;
2587 old_l_registration_no2 := l_registration_no;
2588 end if;
2589 end if;
2590 /* End of Condition to check amounts Bug fix 2408456 */
2591
2592 end loop;
2593 close c_reg_balance_feed_info;
2594 if old_l_value > old_l_value1 then
2595 hr_utility.trace('in reg4');
2596 if old_l_value> old_l_value2 then
2597 arch_l_registration_no := old_l_registration_no;
2598 arch_l_value := old_l_value;
2599 else
2600 arch_l_registration_no := old_l_registration_no2;
2601 arch_l_value := old_l_value2;
2602 end if;
2603 else
2604 if old_l_value1>old_l_value2 then
2605 arch_l_registration_no := old_l_registration_no1;
2606 arch_l_value := old_l_value1;
2607 else
2608 arch_l_registration_no := old_l_registration_no2;
2609 arch_l_value := old_l_value2;
2610 end if;
2611 end if;
2612
2613 /* archive registration number derived from T4A_OTHER_INFO_AMOUNT034 */
2614 if arch_l_registration_no is not null and arch_l_value > 0 then
2615
2616 -- l_box34_regno_flag added for 10099479
2617 l_box34_regno_flag := 1;
2618
2619 ff_archive_api.create_archive_item(
2620 -- p_validate => 'TRUE'
2621 p_archive_item_id => l_archive_item_id
2622 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_EMPLOYEE_REGISTRATION_NO')
2623 ,p_archive_value => arch_l_registration_no
2624 ,p_archive_type => 'AAP'
2625 ,p_action_id => p_assactid
2626 ,p_legislation_code => 'CA'
2627 ,p_object_version_number => l_object_version_number
2628 ,p_some_warning => l_some_warning
2629 );
2630 end if;
2631
2632
2633 if l_negative_balance_exists ='N' then
2634 -- l_negative_balance_exists condition added for bug 10420909
2635 /* Bug fix#2696309, Employer level Pension Plan Register Number */
2636 hr_utility.trace('Start of Employer Level PP Reg no ');
2637
2638 if old_l_registration_no is not null and old_l_value >0 then
2639 hr_utility.trace('in reg1 pay_action_information');
2640 hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2641 hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2642
2643 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2644 l_payroll_action_id
2645 ,old_l_registration_no
2646 ,p_effective_date);
2647 fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
2648 if c_get_emplr_reg_no%FOUND then
2649 hr_utility.trace('in ln_emplr_regamt new = ' || to_char(ln_emplr_regamt));
2650
2651 ln_emplr_regamt := ln_emplr_regamt + old_l_value;
2652
2653 update pay_action_information
2654 set action_information5 = to_char(ln_emplr_regamt)
2655 where action_context_id = l_payroll_action_id
2656 and tax_unit_id = l_tax_unit_id
2657 and effective_date = p_effective_date
2658 and action_information_category = 'CAEOY PENSION PLAN INFO'
2659 AND ACTION_INFORMATION4 = old_l_registration_no;
2660
2661
2662 else
2663
2664 hr_utility.trace('in reg1 new insert pay_action_information');
2665 -- insert a new record into pay_action_information
2666
2667 pay_action_information_api.create_action_information(
2668 p_action_information_id => l_action_information_id_1,
2669 p_object_version_number => l_object_version_number_1,
2670 p_action_information_category => 'CAEOY PENSION PLAN INFO',
2671 p_action_context_id => l_payroll_action_id,
2672 p_action_context_type => 'PA',
2673 p_jurisdiction_code => NULL,
2674 p_tax_unit_id => l_tax_unit_id,
2675 p_effective_date => p_effective_date,
2676 p_action_information1 => NULL,
2677 p_action_information2 => NULL,
2678 p_action_information3 => NULL,
2679 p_action_information4 => old_l_registration_no,
2680 p_action_information5 => to_char(old_l_value),
2681 p_action_information6 => NULL,
2682 p_action_information7 => NULL,
2683 p_action_information8 => NULL,
2684 p_action_information9 => NULL,
2685 p_action_information10 => NULL,
2686 p_action_information11 => NULL,
2687 p_action_information12 => NULL,
2688 p_action_information13 => NULL,
2689 p_action_information14 => NULL,
2690 p_action_information15 => NULL,
2691 p_action_information16 => NULL,
2692 p_action_information17 => NULL,
2693 p_action_information18 => NULL,
2694 p_action_information19 => NULL,
2695 p_action_information20 => NULL,
2696 p_action_information21 => NULL,
2697 p_action_information22 => NULL,
2698 p_action_information23 => NULL,
2699 p_action_information24 => NULL,
2700 p_action_information25 => NULL,
2701 p_action_information26 => NULL,
2702 p_action_information27 => NULL,
2703 p_action_information28 => NULL,
2704 p_action_information29 => NULL,
2705 p_action_information30 => NULL
2706 );
2707
2708 end if; -- c_get_emplr_reg_no%FOUND
2709 close c_get_emplr_reg_no;
2710 end if;
2711
2712 if old_l_registration_no1 is not null and old_l_value1 >0 then
2713 hr_utility.trace('in reg2 pay_action_information');
2714
2715 hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2716 hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2717
2718 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2719 l_payroll_action_id
2720 , old_l_registration_no1
2721 ,p_effective_date);
2722 fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
2723 if c_get_emplr_reg_no%FOUND then
2724
2725 hr_utility.trace('in ln_emplr_regamt1 new = ' || to_char(ln_emplr_regamt1));
2726
2727 ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
2728
2729 update pay_action_information
2730 set action_information5 = to_char(ln_emplr_regamt1)
2731 where action_context_id = l_payroll_action_id
2732 and tax_unit_id = l_tax_unit_id
2733 and effective_date = p_effective_date
2734 and action_information_category = 'CAEOY PENSION PLAN INFO'
2735 AND ACTION_INFORMATION4 = old_l_registration_no1;
2736
2737 else
2738 hr_utility.trace('in reg2 new insert pay_action_information');
2739
2740 -- insert a new record into pay_action_information
2741
2742 pay_action_information_api.create_action_information(
2743 p_action_information_id => l_action_information_id_1,
2744 p_object_version_number => l_object_version_number_1,
2745 p_action_information_category => 'CAEOY PENSION PLAN INFO',
2746 p_action_context_id => l_payroll_action_id,
2747 p_action_context_type => 'PA',
2748 p_jurisdiction_code => NULL,
2749 p_tax_unit_id => l_tax_unit_id,
2750 p_effective_date => p_effective_date,
2751 p_action_information1 => NULL,
2752 p_action_information2 => NULL,
2753 p_action_information3 => NULL,
2754 p_action_information4 => old_l_registration_no1,
2755 p_action_information5 => to_char(old_l_value1),
2756 p_action_information6 => NULL,
2757 p_action_information7 => NULL,
2758 p_action_information8 => NULL,
2759 p_action_information9 => NULL,
2760 p_action_information10 => NULL,
2761 p_action_information11 => NULL,
2762 p_action_information12 => NULL,
2763 p_action_information13 => NULL,
2764 p_action_information14 => NULL,
2765 p_action_information15 => NULL,
2766 p_action_information16 => NULL,
2767 p_action_information17 => NULL,
2768 p_action_information18 => NULL,
2769 p_action_information19 => NULL,
2770 p_action_information20 => NULL,
2771 p_action_information21 => NULL,
2772 p_action_information22 => NULL,
2773 p_action_information23 => NULL,
2774 p_action_information24 => NULL,
2775 p_action_information25 => NULL,
2776 p_action_information26 => NULL,
2777 p_action_information27 => NULL,
2778 p_action_information28 => NULL,
2779 p_action_information29 => NULL,
2780 p_action_information30 => NULL
2781 );
2782
2783 end if; -- c_get_emplr_reg_no%FOUND
2784 close c_get_emplr_reg_no;
2785 end if;
2786
2787 if old_l_registration_no2 is not null and old_l_value2 > 0 then
2788 hr_utility.trace('in reg3 pay_action_information');
2789 hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2790 hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2791 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2792 l_payroll_action_id
2793 , old_l_registration_no2
2794 ,p_effective_date);
2795 fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
2796 if c_get_emplr_reg_no%FOUND then
2797
2798 hr_utility.trace('in ln_emplr_regamt2 new = ' || to_char(ln_emplr_regamt2));
2799
2800 ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
2801
2802 update pay_action_information
2803 set action_information5 = to_char(ln_emplr_regamt2)
2804 where action_context_id = l_payroll_action_id
2805 and tax_unit_id = l_tax_unit_id
2806 and effective_date = p_effective_date
2807 and action_information_category = 'CAEOY PENSION PLAN INFO'
2808 AND ACTION_INFORMATION4 = old_l_registration_no2;
2809
2810 hr_utility.trace('Updated pay_action_information');
2811
2812 else
2813 hr_utility.trace('in reg3 new insert pay_action_information');
2814
2815 -- insert a new record into pay_action_information
2816
2817 pay_action_information_api.create_action_information(
2818 p_action_information_id => l_action_information_id_1,
2819 p_object_version_number => l_object_version_number_1,
2820 p_action_information_category => 'CAEOY PENSION PLAN INFO',
2821 p_action_context_id => l_payroll_action_id,
2822 p_action_context_type => 'PA',
2823 p_jurisdiction_code => NULL,
2824 p_tax_unit_id => l_tax_unit_id,
2825 p_effective_date => p_effective_date,
2826 p_action_information1 => NULL,
2827 p_action_information2 => NULL,
2828 p_action_information3 => NULL,
2829 p_action_information4 => old_l_registration_no2,
2830 p_action_information5 => to_char(old_l_value2),
2831 p_action_information6 => NULL,
2832 p_action_information7 => NULL,
2833 p_action_information8 => NULL,
2834 p_action_information9 => NULL,
2835 p_action_information10 => NULL,
2836 p_action_information11 => NULL,
2837 p_action_information12 => NULL,
2838 p_action_information13 => NULL,
2839 p_action_information14 => NULL,
2840 p_action_information15 => NULL,
2841 p_action_information16 => NULL,
2842 p_action_information17 => NULL,
2843 p_action_information18 => NULL,
2844 p_action_information19 => NULL,
2845 p_action_information20 => NULL,
2846 p_action_information21 => NULL,
2847 p_action_information22 => NULL,
2848 p_action_information23 => NULL,
2849 p_action_information24 => NULL,
2850 p_action_information25 => NULL,
2851 p_action_information26 => NULL,
2852 p_action_information27 => NULL,
2853 p_action_information28 => NULL,
2854 p_action_information29 => NULL,
2855 p_action_information30 => NULL
2856 );
2857
2858 end if; -- c_get_emplr_reg_no%FOUND
2859 close c_get_emplr_reg_no;
2860 end if;
2861
2862 end if; -- if l_negative_balance_exists ='N' then for bug 10420909
2863
2864 /* Added else part to fix bug#2408456
2865 if the registration number doesn't exist for the elements
2866 that are fed to balance T4A_OTHER_INFO_AMOUNT034 then check the elements
2867 that are fed to balance T4A_OTHER_INFO_AMOUNT032 and archive it */
2868
2869 /* l_registration_no := NULL;
2870 old_l_registration_no := NULL;
2871 arch_l_registration_no := NULL;
2872 old_l_value := 0;
2873 old_l_registration_no1 := NULL;
2874 old_l_value1 := 0;
2875 old_l_registration_no2 := NULL;
2876 old_l_value2 := 0;
2877 arch_l_value := 0;
2878 l_value := 0;
2879 */
2880
2881 if old_l_registration_no is null or
2882 old_l_registration_no1 is null or
2883 old_l_registration_no2 is null then
2884
2885 l_registration_no := NULL;
2886 old_l_registration_no := NULL; old_l_value := 0;
2887 old_l_registration_no1 := NULL;
2888 old_l_value1 := 0;
2889 old_l_registration_no2 := NULL;
2890 old_l_value2 := 0;
2891 l_value := 0;
2892 arch_l_registration_no := NULL;
2893 arch_l_value := 0;
2894 begin
2895
2896 --open c_reg_balance_feed_info('T4A_BOX32');
2897 open c_reg_balance_feed_info('T4A_OTHER_INFO_AMOUNT032');
2898
2899 loop
2900 fetch c_reg_balance_feed_info into l_registration_no,
2901 l_balance_name,l_element_type_id,
2902 l_ele_classification_id;
2903 exit when c_reg_balance_feed_info%NOTFOUND;
2904
2905 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2906 ( l_balance_name,
2907 'YTD' ,
2908 l_aaid,
2909 l_asgid,
2910 NULL,
2911 'PER' ,
2912 l_tax_unit_id,
2913 l_business_group_id,
2914 NULL );
2915
2916
2917 hr_utility.trace('l_value:'||to_char(l_value));
2918 if l_value is null then
2919 l_value := 0;
2920 end if;
2921
2922 /* Condition to check the amounts and determine the
2923 registration number to archive Bug fix 2408456 */
2924
2925 if l_value > nvl(old_l_value,0) then
2926
2927 old_l_value := l_value;
2928 old_l_registration_no := l_registration_no;
2929 elsif l_value > nvl(old_l_value1,0) then
2930 old_l_value1 := l_value;
2931 old_l_registration_no1 := l_registration_no;
2932 elsif l_value > nvl(old_l_value2,0) then
2933 old_l_value2 := l_value;
2934 old_l_registration_no2 := l_registration_no;
2935
2936 end if;
2937 /* End of Condition to check amounts Bug fix 2408456 */
2938
2939 end loop;
2940 close c_reg_balance_feed_info;
2941
2942 if old_l_value > old_l_value1 then
2943 if old_l_value> old_l_value2 then
2944 arch_l_registration_no := old_l_registration_no;
2945 arch_l_value := old_l_value;
2946 else
2947 arch_l_registration_no := old_l_registration_no2;
2948 arch_l_value := old_l_value2;
2949 end if;
2950 else
2951 if old_l_value1>old_l_value2 then
2952 arch_l_registration_no := old_l_registration_no1;
2953 arch_l_value := old_l_value1;
2954 else
2955 arch_l_registration_no := old_l_registration_no2;
2956 arch_l_value := old_l_value2;
2957 end if;
2958 end if;
2959
2960 if arch_l_registration_no is not null and arch_l_value > 0
2961 and l_box34_regno_flag = 0 then
2962 -- l_box34_regno_flag added for 10099479
2963
2964 ff_archive_api.create_archive_item(
2965 -- p_validate => 'TRUE'
2966 p_archive_item_id => l_archive_item_id
2967 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_EMPLOYEE_REGISTRATION_NO')
2968 ,p_archive_value => arch_l_registration_no
2969 ,p_archive_type => 'AAP'
2970 ,p_action_id => p_assactid
2971 ,p_legislation_code => 'CA'
2972 ,p_object_version_number => l_object_version_number
2973 ,p_some_warning => l_some_warning
2974 );
2975 end if;
2976
2977 if l_negative_balance_exists ='N' then
2978 -- l_negative_balance_exists condition added for bug 10420909
2979 if old_l_registration_no is not null and old_l_value > 0 then
2980
2981
2982 /* Bug fix#2696309, Employer level Pension Plan Register Number */
2983
2984 hr_utility.trace('Start of Employer Level PP Reg no ');
2985 hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2986 hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2987 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2988 l_payroll_action_id
2989 ,old_l_registration_no
2990 ,p_effective_date);
2991 fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
2992 if c_get_emplr_reg_no%FOUND then
2993 hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
2994 ln_emplr_regamt := ln_emplr_regamt + old_l_value;
2995
2996 update pay_action_information
2997 set action_information5 = to_char(ln_emplr_regamt)
2998 where action_context_id = l_payroll_action_id
2999 and tax_unit_id = l_tax_unit_id
3000 and effective_date = p_effective_date
3001 and action_information_category = 'CAEOY PENSION PLAN INFO'
3002 AND ACTION_INFORMATION4 = old_l_registration_no;
3003
3004 else
3005 -- insert a new record into pay_action_information
3006
3007 pay_action_information_api.create_action_information(
3008 p_action_information_id => l_action_information_id_1,
3009 p_object_version_number => l_object_version_number_1,
3010 p_action_information_category => 'CAEOY PENSION PLAN INFO',
3011 p_action_context_id => l_payroll_action_id,
3012 p_action_context_type => 'PA',
3013 p_jurisdiction_code => NULL ,
3014 p_tax_unit_id => l_tax_unit_id,
3015 p_effective_date => p_effective_date,
3016 p_assignment_id => l_asgid,
3017 p_action_information1 => NULL,
3018 p_action_information2 => NULL,
3019 p_action_information3 => NULL,
3020 p_action_information4 => old_l_registration_no,
3021 p_action_information5 => to_char(old_l_value),
3022 p_action_information6 => NULL,
3023 p_action_information7 => NULL,
3024 p_action_information8 => NULL,
3025 p_action_information9 => NULL,
3026 p_action_information10 => NULL,
3027 p_action_information11 => NULL,
3028 p_action_information12 => NULL,
3029 p_action_information13 => NULL,
3030 p_action_information14 => NULL,
3031 p_action_information15 => NULL,
3032 p_action_information16 => NULL,
3033 p_action_information17 => NULL,
3034 p_action_information18 => NULL,
3035 p_action_information19 => NULL,
3036 p_action_information20 => NULL,
3037 p_action_information21 => NULL,
3038 p_action_information22 => NULL,
3039 p_action_information23 => NULL,
3040 p_action_information24 => NULL,
3041 p_action_information25 => NULL,
3042 p_action_information26 => NULL,
3043 p_action_information27 => NULL,
3044 p_action_information28 => NULL,
3045 p_action_information29 => NULL,
3046 p_action_information30 => NULL
3047 );
3048
3049 end if; -- c_get_emplr_reg_no%FOUND
3050 close c_get_emplr_reg_no;
3051 end if;
3052 if old_l_registration_no1 is not null and old_l_value1 > 0 then
3053
3054
3055 /* Bug fix#2696309, Employer level Pension Plan Register Number */
3056
3057 hr_utility.trace('Start of Employer Level PP Reg no ');
3058 hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3059 hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3060
3061 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3062 l_payroll_action_id
3063 ,old_l_registration_no1
3064 ,p_effective_date);
3065 fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
3066 if c_get_emplr_reg_no%FOUND then
3067 hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
3068 ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
3069
3070 update pay_action_information
3071 set action_information5 = to_char(ln_emplr_regamt1)
3072 where action_context_id = l_payroll_action_id
3073 and tax_unit_id = l_tax_unit_id
3074 and effective_date = p_effective_date
3075 and action_information_category = 'CAEOY PENSION PLAN INFO'
3076 AND ACTION_INFORMATION4 = old_l_registration_no1;
3077
3078 else
3079 -- insert a new record into pay_action_information
3080
3081 pay_action_information_api.create_action_information(
3082 p_action_information_id => l_action_information_id_1,
3083 p_object_version_number => l_object_version_number_1,
3084 p_action_information_category => 'CAEOY PENSION PLAN INFO',
3085 p_action_context_id => l_payroll_action_id,
3086 p_action_context_type => 'PA',
3087 p_jurisdiction_code => NULL ,
3088 p_tax_unit_id => l_tax_unit_id,
3089 p_effective_date => p_effective_date,
3090 p_assignment_id => l_asgid,
3091 p_action_information1 => NULL,
3092 p_action_information2 => NULL,
3093 p_action_information3 => NULL,
3094 p_action_information4 => old_l_registration_no1,
3095 p_action_information5 => to_char(old_l_value1),
3096 p_action_information6 => NULL,
3097 p_action_information7 => NULL,
3098 p_action_information8 => NULL,
3099 p_action_information9 => NULL,
3100 p_action_information10 => NULL,
3101 p_action_information11 => NULL,
3102 p_action_information12 => NULL,
3103 p_action_information13 => NULL,
3104 p_action_information14 => NULL,
3105 p_action_information15 => NULL,
3106 p_action_information16 => NULL,
3107 p_action_information17 => NULL,
3108 p_action_information18 => NULL,
3109 p_action_information19 => NULL,
3110 p_action_information20 => NULL,
3111 p_action_information21 => NULL,
3112 p_action_information22 => NULL,
3113 p_action_information23 => NULL,
3114 p_action_information24 => NULL,
3115 p_action_information25 => NULL,
3116 p_action_information26 => NULL,
3117 p_action_information27 => NULL,
3118 p_action_information28 => NULL,
3119 p_action_information29 => NULL,
3120 p_action_information30 => NULL
3121 );
3122
3123 end if; -- c_get_emplr_reg_no%FOUND
3124 close c_get_emplr_reg_no;
3125
3126 end if; /* for old_l_registration_no1 is not null derived
3127 from T4A_BOX32 */
3128
3129 if old_l_registration_no2 is not null and old_l_value2 > 0 then
3130
3131 /* Bug fix#2696309, Employer level Pension Plan Register Number */
3132
3133 hr_utility.trace('Start of Employer Level PP Reg no ');
3134 hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3135 hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3136
3137 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3138 l_payroll_action_id
3139 ,old_l_registration_no2
3140 ,p_effective_date);
3141 fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
3142 hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
3143 if c_get_emplr_reg_no%FOUND then
3144 ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
3145
3146 update pay_action_information
3147 set action_information5 = to_char(ln_emplr_regamt2)
3148 where action_context_id = l_payroll_action_id
3149 and tax_unit_id = l_tax_unit_id
3150 and effective_date = p_effective_date
3151 and action_information_category = 'CAEOY PENSION PLAN INFO'
3152 AND ACTION_INFORMATION4 = old_l_registration_no2;
3153
3154 else
3155 -- insert a new record into pay_action_information
3156
3157 pay_action_information_api.create_action_information(
3158 p_action_information_id => l_action_information_id_1,
3159 p_object_version_number => l_object_version_number_1,
3160 p_action_information_category => 'CAEOY PENSION PLAN INFO',
3161 p_action_context_id => l_payroll_action_id,
3162 p_action_context_type => 'PA',
3163 p_jurisdiction_code => NULL ,
3164 p_tax_unit_id => l_tax_unit_id,
3165 p_effective_date => p_effective_date,
3166 p_assignment_id => l_asgid,
3167 p_action_information1 => NULL,
3168 p_action_information2 => NULL,
3169 p_action_information3 => NULL,
3170 p_action_information4 => old_l_registration_no2,
3171 p_action_information5 => to_char(old_l_value2),
3172 p_action_information6 => NULL,
3173 p_action_information7 => NULL,
3174 p_action_information8 => NULL,
3175 p_action_information9 => NULL,
3176 p_action_information10 => NULL,
3177 p_action_information11 => NULL,
3178 p_action_information12 => NULL,
3179 p_action_information13 => NULL,
3180 p_action_information14 => NULL,
3181 p_action_information15 => NULL,
3182 p_action_information16 => NULL,
3183 p_action_information17 => NULL,
3184 p_action_information18 => NULL,
3185 p_action_information19 => NULL,
3186 p_action_information20 => NULL,
3187 p_action_information21 => NULL,
3188 p_action_information22 => NULL,
3189 p_action_information23 => NULL,
3190 p_action_information24 => NULL,
3191 p_action_information25 => NULL,
3192 p_action_information26 => NULL,
3193 p_action_information27 => NULL,
3194 p_action_information28 => NULL,
3195 p_action_information29 => NULL,
3196 p_action_information30 => NULL
3197 );
3198
3199 end if; -- c_get_emplr_reg_no%FOUND
3200 close c_get_emplr_reg_no;
3201
3202 end if; /* for old_l_registration_no2 is not null derived
3203 from T4A_BOX32 */
3204
3205 end if; -- if l_negative_balance_exists ='N' then for bug 10420909
3206
3207 end;
3208
3209 /* End of bug fix for bug #2408456 */
3210 end if; /* for old_l_registration_no is not null derived
3211 from T4A_BOX34 */
3212 end;
3213 /* end registration number archiving */
3214 else
3215 hr_utility.trace('result is 0');
3216
3217 end if;
3218
3219 /* Archive the negative balance flag */
3220 ff_archive_api.create_archive_item(
3221 p_archive_item_id => l_archive_item_id
3222 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_NEGATIVE_BALANCE_EXISTS')
3223 ,p_archive_value => l_negative_balance_exists
3224 ,p_archive_type => 'AAP'
3225 ,p_action_id => p_assactid
3226 ,p_legislation_code => 'CA'
3227 ,p_object_version_number => l_object_version_number
3228 ,p_context_name1 => 'TAX_UNIT_ID'
3229 ,p_context1 => l_tax_unit_id
3230 ,p_some_warning => l_some_warning
3231 );
3232
3233 hr_utility.trace('end of eoy_archive_data_new_format');
3234 l_step := 37;
3235
3236 end eoy_archive_data_new_format;
3237
3238
3239 /* Name : eoy_archive_data
3240 Purpose : This performs the CA specific employee context setting for the
3241 Year End PreProcess.
3242 Arguments :
3243 Notes :
3244 */
3245
3246 procedure eoy_archive_data(p_assactid in number, p_effective_date in date) is
3247
3248 l_aaid pay_assignment_actions.assignment_action_id%type;
3249 l_aaseq pay_assignment_actions.action_sequence%type;
3250 l_asgid pay_assignment_actions.assignment_id%type;
3251 l_date_earned date;
3252 l_user_entity_name_tab pay_ca_t4aeoy_archive.char240_data_type_table;
3253 l_balance_type_tab pay_ca_t4aeoy_archive.char240_data_type_table;
3254 l_user_entity_value_tab pay_ca_t4aeoy_archive.char240_data_type_table;
3255 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
3256 l_business_group_id pay_assignment_actions.tax_unit_id%type;
3257 l_year_start date;
3258 l_year_end date;
3259 l_context_no number := 60;
3260 l_count number := 0;
3261 l_jurisdiction varchar2(11);
3262 l_province_uei ff_user_entities.user_entity_id%type;
3263 l_county_uei ff_user_entities.user_entity_id%type;
3264 l_city_uei ff_user_entities.user_entity_id%type;
3265 l_county_sd_uei ff_user_entities.user_entity_id%type;
3266 l_city_sd_uei ff_user_entities.user_entity_id%type;
3267 l_province_abbrev pay_us_states.state_abbrev%type;
3268 l_county_name pay_us_counties.county_name%type;
3269 l_city_name pay_us_city_names.city_name%type;
3270 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
3271 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
3272 l_step number := 0;
3273 l_county_code varchar2(3);
3274 l_city_code varchar2(4);
3275 l_jursd_context_id ff_contexts.context_id%type;
3276 l_taxunit_context_id ff_contexts.context_id%type;
3277 l_seq_tab pay_ca_t4aeoy_archive.number_data_type_table;
3278 l_context_id_tab pay_ca_t4aeoy_archive.number_data_type_table;
3279 l_context_val_tab pay_ca_t4aeoy_archive.char240_data_type_table;
3280 l_chunk number;
3281 l_payroll_action_id number;
3282 l_defined_balance_id number;
3283 l_result number;
3284 l_person_id number;
3285 l_organization_id number;
3286 l_location_id number;
3287 l_first_name varchar2(240);
3288 l_last_name varchar2(240);
3289 l_employee_number varchar2(240);
3290 l_national_identifier varchar2(240);
3291 l_pre_name_adjunct varchar2(240);
3292 l_middle_names varchar2(240);
3293 l_employee_phone_no varchar2(240);
3294 l_work_telephone varchar2(240);
3295 l_address_line1 varchar2(240);
3296 l_address_line2 varchar2(240);
3297 l_address_line3 varchar2(240);
3298 l_address_line4 varchar2(240);
3299 l_town_or_city varchar2(80);
3300 l_province_code varchar2(80);
3301 l_postal_code varchar2(80);
3302 l_telephone_number varchar2(80);
3303 l_country_code varchar2(80);
3304 l_counter number;
3305 l_archive_item_id number;
3306 result number := 0;
3307 earning_exists number := 0;
3308 l_object_version_number number;
3309 l_context_id number;
3310 l_context_val varchar2(80);
3311 l_some_warning boolean;
3312 l_cpp_exempt_flag varchar2(80);
3313 l_ei_exempt_flag varchar2(80);
3314 l_footnote_code varchar2(10);
3315 l_box38_footnote_code varchar2(10) := NULL;
3316 l_footnote_balance varchar2(80);
3317 l_footnote_amount number;
3318 old_l_footnote_code varchar2(80) := null;
3319 l_footnote_code_ue varchar2(80);
3320 l_box38_footnote_code_ue varchar2(80);
3321 l_footnote_amount_ue varchar2(80);
3322 l_no_of_fn_codes number := 0;
3323 l_box38_count number := 0;
3324 l_value number := 0;
3325 old_l_value number := 0;
3326 old_l_value1 number := 0;
3327 old_l_value2 number := 0;
3328 arch_l_value number := 0;
3329 l_registration_no varchar2(150);
3330 old_l_registration_no varchar2(150);
3331 old_l_registration_no1 varchar2(150);
3332 old_l_registration_no2 varchar2(150);
3333 arch_l_registration_no varchar2(150);
3334 l_balance_name varchar2(150);
3335 l_single_footnote_code varchar2(10);
3336 lv_serial_number varchar2(30);
3337 l_negative_balance_exists varchar2(5);
3338
3339 /* new variables added for Federal YE Amendment PP */
3340 ld_fapp_effective_date date;
3341 lv_fapp_report_type varchar2(20);
3342 ln_fapp_locked_action_id number;
3343 lv_fapp_flag varchar2(2);
3344 lv_fapp_locked_actid_reptype varchar2(20);
3345
3346 /* T4A Nonbox footnote variables */
3347 l_messages VARCHAR2(240);
3348 l_prev_messages VARCHAR2(240);
3349 l_mesg_amt NUMBER(16,2);
3350 l_total_mesg_amt NUMBER(16,2);
3351 ln_tax_unit_id NUMBER;
3352 ln_prev_tax_unit_id NUMBER;
3353 ld_eff_date DATE;
3354 ld_prev_eff_date DATE;
3355 ln_assignment_action_id NUMBER;
3356 l_context_value VARCHAR2(50);
3357 l_action_information_id_1 NUMBER ;
3358 l_object_version_number_1 NUMBER ;
3359
3360 /* T4A_Registration_no variables part of bug fix 2408456 */
3361 l_check_flag varchar2(2);
3362 l_element_type_id number(20);
3363 l_run_result_id number(20);
3364 l_ele_proc_eff_date date;
3365 l_info_ele_amt varchar2(20);
3366 l_ele_classification_id number(20);
3367 l_ele_classification_name varchar2(50);
3368
3369 lv_emplr_regno varchar2(20);
3370 lv_emplr_regno1 varchar2(20);
3371 lv_emplr_regno2 varchar2(20);
3372 ln_emplr_regamt number(30);
3373 ln_emplr_regamt1 number(30);
3374 ln_emplr_regamt2 number(30);
3375
3376 lv_footnote_element varchar2(50);
3377 l_transmitter_gre_id number;
3378
3379 l_status_indian varchar2(1);
3380
3381 -- l_box34_regno_flag added for 10099479
3382 l_box34_regno_flag number := 0;
3383
3384 /* cursor used to archive the footnote code values */
3385 cursor c_balance_feed_info (p_balance_name varchar2) is
3386 select distinct pet.element_information18,
3387 pbt1.balance_name
3388 from pay_balance_feeds_f pbf,
3389 pay_balance_types pbt,
3390 pay_balance_types pbt1,
3391 pay_input_values_f piv,
3392 pay_element_types_f pet,
3393 fnd_lookup_values flv
3394 where pbt.balance_name = p_balance_name
3395 and pbf.balance_type_id = pbt.balance_type_id
3396 and pbf.input_value_id = piv.input_value_id
3397 and piv.element_type_id = pet.element_type_id
3398 and pet.business_group_id = l_business_group_id
3399 and pbt1.balance_type_id = pet.element_information10
3400 and pet.element_information18 = flv.lookup_code
3401 and flv.lookup_type = 'PAY_CA_T4A_FOOTNOTES'
3402 and flv.language = userenv('LANG')
3403 order by pet.element_information18;
3404
3405 /* cursor used to archive the Pension Adjustment Registration Number */
3406 cursor c_reg_balance_feed_info (p_balance_name varchar2) is
3407 select distinct nvl(pet.element_information20,'NOT FOUND'),
3408 pbt1.balance_name,pet.element_type_id,
3409 pet.classification_id
3410 from pay_balance_feeds_f pbf,
3411 pay_balance_types pbt,
3412 pay_balance_types pbt1,
3413 pay_input_values_f piv,
3414 pay_element_types_f pet
3415 where pbt.balance_name = p_balance_name
3416 and pbf.balance_type_id = pbt.balance_type_id
3417 and pbf.input_value_id = piv.input_value_id
3418 and piv.element_type_id = pet.element_type_id
3419 and pet.business_group_id = l_business_group_id
3420 and pbt1.balance_type_id = pet.element_information10
3421 -- and pet.element_information_category = 'CA_EARNINGS'
3422 and pet.element_information20 is not null;
3423
3424 /* Cursor for T4A Nonbox Footnote archive to fix bug#2175045 */
3425 /* Modified the cur_non_box_mesg cursor to fix bug#3641353.
3426 Kept the Jurisdiction context validation because of performance
3427 for T4A Reporting and added action_type 'B' Balance Adj's */
3428 CURSOR cur_non_box_mesg( cp_asgact_id in number,
3429 cp_eff_date in date) is
3430 select distinct prrv1.result_value,
3431 prrv2.result_value,
3432 hoi.organization_id,
3433 run_ppa.effective_date,
3434 run_paa.assignment_action_id
3435 from pay_run_result_values prrv1
3436 , pay_run_result_values prrv2
3437 , pay_run_results prr
3438 , pay_element_types_f pet
3439 , pay_input_values_f piv1
3440 , pay_input_values_f piv2
3441 , pay_assignment_actions run_paa
3442 , pay_payroll_actions run_ppa
3443 , pay_assignment_actions arch_paa
3444 , pay_payroll_actions arch_ppa
3445 , per_all_assignments_f arch_paf
3446 , per_all_assignments_f all_paf
3447 , hr_all_organization_units hou
3448 , hr_organization_information hoi
3449 where arch_paa.assignment_action_id = cp_asgact_id
3450 and arch_ppa.payroll_action_id = arch_paa.payroll_action_id
3451 and hou.business_group_id = arch_ppa.business_group_id
3452 and hou.organization_id = hoi.organization_id
3453 and hoi.organization_id =
3454 to_number(pycadar_pkg.get_parameter('TRANSFER_GRE',arch_ppa.legislative_parameters))
3455 and hoi.org_information_context = 'Canada Employer Identification'
3456 and hoi.org_information5 IN ('T4A/RL1','T4A/RL2')
3457 and run_paa.tax_unit_id = hou.organization_id
3458 and run_ppa.payroll_action_id = run_paa.payroll_action_id
3459 and run_ppa.action_type in ( 'R', 'Q', 'B', 'V' )
3460 -- Added 'V' for bug 11065493
3461 and to_char(run_ppa.effective_date,'YYYY' ) =
3462 to_char(cp_eff_date,'YYYY')
3463 and run_paa.action_status = 'C'
3464 and pet.element_name = lv_footnote_element --'T4A NonBox Footnotes'
3465 and prr.assignment_action_id = run_paa.assignment_action_id
3466 and prr.element_type_id = pet.element_type_id
3467 and piv1.element_type_id = pet.element_type_id
3468 and piv1.name = 'Message'
3469 and prrv1.run_result_id = prr.run_result_id
3470 and prrv1.input_value_id = piv1.input_value_id
3471 and piv2.element_type_id = pet.element_type_id
3472 and piv2.name = 'Amount'
3473 and prrv2.run_result_id = prrv1.run_result_id
3474 and prrv2.input_value_id = piv2.input_value_id
3475 and arch_paf.assignment_id = arch_paa.assignment_id
3476 and to_char(cp_eff_date,'YYYY')
3477 between to_char(arch_paf.effective_start_date,'YYYY')
3478 and to_char(arch_paf.effective_end_date,'YYYY')
3479 and all_paf.person_id = arch_paf.person_id
3480 and to_char(cp_eff_date,'YYYY')
3481 between to_char(all_paf.effective_start_date,'YYYY')
3482 and to_char(all_paf.effective_end_date,'YYYY')
3483 and run_paa.assignment_id = all_paf.assignment_id
3484 and exists (select 1
3485 from pay_action_contexts pac,ff_contexts ffc
3486 where ffc.context_name = 'JURISDICTION_CODE'
3487 and pac.context_id = ffc.context_id
3488 and pac.assignment_id = run_paa.assignment_id);
3489
3490
3491 /* Cursor to check the Employer Level PP Registration Number
3492 Bug fix#2696309 */
3493 CURSOR c_get_emplr_reg_no(cp_tax_unit_id varchar2
3494 ,cp_payroll_action_id number
3495 ,cp_reg_no varchar2
3496 ,cp_eff_date date) IS
3497 select action_information4,to_number(action_information5)
3498 from pay_action_information
3499 where action_context_id = cp_payroll_action_id
3500 and effective_date = cp_eff_date
3501 AND tax_unit_id = cp_tax_unit_id
3502 and action_information_category = 'CAEOY PENSION PLAN INFO'
3503 AND ACTION_INFORMATION4 = cp_reg_no;
3504
3505 CURSOR c_get_latest_asg(p_person_id number ) IS
3506 select paa.assignment_action_id
3507 from pay_assignment_actions paa,
3508 per_all_assignments_f paf,
3509 pay_payroll_actions ppa,
3510 pay_action_classifications pac
3511 where paf.person_id = p_person_id
3512 and paa.assignment_id = paf.assignment_id
3513 and paa.tax_unit_id = l_tax_unit_id
3514 and paa.payroll_action_id = ppa.payroll_action_id
3515 and ppa.action_type = pac.action_type
3516 and pac.classification_name = 'SEQUENCED'
3517 and ppa.effective_date +0 between paf.effective_start_date
3518 and paf.effective_end_date
3519 and ppa.effective_date +0 between l_year_start and
3520 l_year_end
3521 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
3522 and paa.source_action_id is null)
3523 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
3524 and paa.source_action_id is not null )
3525 or (ppa.action_type = 'V' and ppa.run_type_id is null
3526 and paa.run_type_id is not null
3527 and paa.source_action_id is null))
3528 order by paa.action_sequence desc;
3529
3530
3531 /* New cursors added for Federal YE Amendment Pre-Process Validation */
3532
3533 CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
3534 select ppa.report_type
3535 from pay_payroll_actions ppa,pay_assignment_actions paa
3536 where paa.assignment_action_id = cp_locked_actid
3537 and ppa.payroll_action_id = paa.payroll_action_id;
3538
3539 CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
3540 select locked_action_id
3541 from pay_action_interlocks
3542 where locking_action_id = cp_locking_act_id;
3543
3544 /* New cursor for checking for the employee been a Status Indian */
3545 CURSOR c_get_status_indian(cp_assign number,
3546 cp_effec_date date) IS
3547 select ca_tax_information1
3548 from pay_ca_emp_fed_tax_info_f pca
3549 where pca.assignment_id = cp_assign
3550 and cp_effec_date between pca.effective_start_date and
3551 pca.effective_end_date;
3552
3553 begin
3554
3555 -- hr_utility.trace_on('Y','ORACLEMM');
3556
3557 l_count := 0;
3558 l_box38_footnote_code := '00';
3559 l_negative_balance_exists := 'N';
3560
3561 hr_utility.set_location ('archive_data',1);
3562 hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
3563
3564
3565 SELECT aa.assignment_id,
3566 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
3567 aa.tax_unit_id,
3568 aa.chunk_number,
3569 aa.payroll_action_id,
3570 aa.serial_number
3571 into l_asgid,
3572 l_date_earned,
3573 l_tax_unit_id,
3574 l_chunk,
3575 l_payroll_action_id,
3576 lv_serial_number
3577 FROM pay_assignment_actions aa
3578 WHERE aa.assignment_action_id = p_assactid;
3579
3580 /*Bug 4021563 Fetching the Status Indian flag */
3581 open c_get_status_indian(l_asgid,p_effective_date);
3582 fetch c_get_status_indian
3583 into l_status_indian;
3584 close c_get_status_indian;
3585
3586 /* Call the archive_gre_data procedure */
3587 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3588 select org_information11
3589 into l_transmitter_gre_id
3590 from hr_organization_information
3591 where organization_id = l_tax_unit_id
3592 and org_information_context = 'Canada Employer Identification';
3593
3594 l_step := 3;
3595 hr_utility.trace('eoy_archive_data archiving employer data');
3596 eoy_archive_gre_data(l_payroll_action_id,
3597 l_tax_unit_id,
3598 l_transmitter_gre_id);
3599
3600 l_step := 4;
3601 hr_utility.trace('eoy_archive_data archived employer data');
3602 else
3603 g_archive_flag := 'Y';
3604 end if;
3605
3606
3607 l_year_start := trunc(p_effective_date, 'Y');
3608 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3609
3610 if to_number(to_char(l_year_end,'YYYY')) > 2005 then
3611 lv_footnote_element := 'T4A Non Box Footnotes';
3612 else
3613 lv_footnote_element := 'T4A NonBox Footnotes';
3614 end if;
3615
3616 hr_utility.trace('l_date_earned : '|| to_char(l_date_earned));
3617
3618 /* YE-2001 change to avoid hr_ca_tax_units_v view */
3619 select business_group_id
3620 into l_business_group_id
3621 from hr_all_organization_units
3622 where organization_id = l_tax_unit_id;
3623
3624 l_step := 1;
3625
3626 /*
3627 select paa1.assignment_action_id
3628 into l_aaid
3629 from pay_assignment_actions paa1,
3630 per_all_assignments_f paf2
3631 where paa1.assignment_id = paf2.assignment_id
3632 and paa1.tax_unit_id = l_tax_unit_id
3633 and (paa1.action_sequence , paf2.person_id) =
3634 (SELECT MAX(paa.action_sequence), paf.person_id
3635 FROM pay_action_classifications pac,
3636 pay_payroll_actions ppa,
3637 pay_assignment_actions paa,
3638 per_all_assignments_f paf1,
3639 per_all_assignments_f paf
3640 WHERE paf.assignment_id = l_asgid
3641 AND paf1.person_id = paf.person_id
3642 AND paa.tax_unit_id = l_tax_unit_id
3643 AND paa.assignment_id = paf1.assignment_id
3644 AND paa.payroll_action_id = ppa.payroll_action_id
3645 AND ppa.action_type = pac.action_type
3646 AND pac.classification_name = 'SEQUENCED'
3647 AND ppa.effective_date <= p_effective_date
3648 group by paf.person_id)
3649 and rownum < 2;
3650 */
3651 begin
3652 open c_get_latest_asg(lv_serial_number );
3653 fetch c_get_latest_asg into l_aaid;
3654 close c_get_latest_asg;
3655 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3656
3657 exception
3658 when no_data_found then
3659 l_aaid := -9999;
3660 raise_application_error(-20001,'Balance Assignment Action does not exist for : '
3661 ||to_char(l_person_id));
3662 end;
3663 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3664 hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
3665 hr_utility.trace('l_asgid : ' || to_char(l_asgid));
3666
3667
3668
3669 /* Assign values to the PL/SQL tables */
3670
3671 l_step := 16;
3672
3673 l_seq_tab(2) := 2;
3674 l_context_id_tab(2) := l_taxunit_context_id;
3675 l_context_val_tab(2) := l_tax_unit_id;
3676
3677 /*
3678 l_count := l_count + 1;
3679 l_user_entity_name_tab(l_count) := 'CAEOY_GROSS_EARNINGS_PER_GRE_YTD';
3680 l_balance_type_tab(l_count) := 'Gross Earnings';
3681 */
3682
3683 l_count := l_count + 1;
3684 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX16_PER_GRE_YTD';
3685 l_balance_type_tab(l_count) := 'T4A_BOX16';
3686
3687 l_count := l_count + 1;
3688 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX18_PER_GRE_YTD';
3689 l_balance_type_tab(l_count) := 'T4A_BOX18';
3690
3691 l_count := l_count + 1;
3692 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX20_PER_GRE_YTD';
3693 l_balance_type_tab(l_count) := 'T4A_BOX20';
3694
3695 l_count := l_count + 1;
3696 l_user_entity_name_tab(l_count) := 'CAEOY_FED_WITHHELD_PER_GRE_YTD';
3697 l_balance_type_tab(l_count) := 'FED Withheld';
3698
3699 l_count := l_count + 1;
3700 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX24_PER_GRE_YTD';
3701 l_balance_type_tab(l_count) := 'T4A_BOX24';
3702
3703 -- IF condition added for bug 6456662
3704 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2010) then
3705
3706 l_count := l_count + 1;
3707 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX48_PER_GRE_YTD';
3708 l_balance_type_tab(l_count) := 'T4A_BOX48';
3709
3710 END IF;
3711
3712 -- IF condition added for bug 6456662, 9980854
3713
3714 -- IF condition added for bug 6456662, 9980854
3715
3716 BEGIN
3717 select effective_date,
3718 report_type
3719 into ld_fapp_effective_date,
3720 lv_fapp_report_type
3721 from pay_payroll_actions
3722 where payroll_action_id = l_payroll_action_id;
3723 EXCEPTION
3724 WHEN OTHERS THEN
3725 lv_fapp_report_type :='T4A';
3726 hr_utility.trace('Report type not found for given Payroll_action ');
3727 END;
3728
3729 hr_utility.trace('Fed Pre-Process Pactid :'||
3730 to_char(l_payroll_action_id));
3731 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3732
3733 if (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
3734
3735 hr_utility.trace('Archiving BOX28 etc., for payroll_action_id and report_type:'||
3736 l_payroll_action_id||' and '||lv_fapp_report_type);
3737
3738 l_count := l_count + 1;
3739 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX26_PER_GRE_YTD';
3740 l_balance_type_tab(l_count) := 'T4A_BOX26';
3741
3742 l_count := l_count + 1;
3743 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX27_PER_GRE_YTD';
3744 l_balance_type_tab(l_count) := 'T4A_BOX27';
3745
3746 l_count := l_count + 1;
3747 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX28_PER_GRE_YTD';
3748 l_balance_type_tab(l_count) := 'T4A_BOX28';
3749
3750 l_count := l_count + 1;
3751 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX30_PER_GRE_YTD';
3752 l_balance_type_tab(l_count) := 'T4A_BOX30';
3753
3754 l_count := l_count + 1;
3755 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX32_PER_GRE_YTD';
3756 l_balance_type_tab(l_count) := 'T4A_BOX32';
3757
3758 l_count := l_count + 1;
3759 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX34_PER_GRE_YTD';
3760 l_balance_type_tab(l_count) := 'T4A_BOX34';
3761 /*
3762 l_count := l_count + 1;
3763 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX36_PER_GRE_YTD';
3764 l_balance_type_tab(l_count) := 'T4A_BOX36';
3765 */
3766 l_count := l_count + 1;
3767 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX40_PER_GRE_YTD';
3768 l_balance_type_tab(l_count) := 'T4A_BOX40';
3769
3770 l_count := l_count + 1;
3771 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX42_PER_GRE_YTD';
3772 l_balance_type_tab(l_count) := 'T4A_BOX42';
3773
3774 l_count := l_count + 1;
3775 l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX46_PER_GRE_YTD';
3776 l_balance_type_tab(l_count) := 'T4A_BOX46';
3777
3778 end if;
3779 /* Initializing variables as part of bug fix#2426517 */
3780 l_box38_footnote_code := '00';
3781 l_box38_count := 0;
3782
3783 if ( (pay_ca_balance_pkg.call_ca_balance_get_value
3784 ( 'Gross Earnings',
3785 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
3786 l_tax_unit_id, l_business_group_id, NULL)
3787 <> 0) OR
3788 (pay_ca_balance_pkg.call_ca_balance_get_value
3789 ( 'T4A No Gross Earnings',
3790 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
3791 l_tax_unit_id, l_business_group_id, NULL)
3792 <> 0) ) then
3793
3794 earning_exists := 1;
3795
3796 hr_utility.trace('starting loop for balances');
3797
3798 for i in 1 .. l_count
3799 loop
3800 result := 0;
3801 /* Now, set up the jurisdiction context for the db items that
3802 need the jurisdiction as a context.Here we are archiving all the
3803 jurisdictions we got from pay_action_contexts for all
3804 assignment_actions. So even though a particular assignment_action
3805 is for aparticular jurisdiction the archiver table has data for
3806 all the jurisdictions, but values of irrelevant jurisdictions will
3807 be 0 */
3808
3809 /* To get balances you must use the highest assignment action . Since
3810 T4A does not have Jurisdiction specific balances first we have to
3811 sum up balances for all jurisdictions. */
3812
3813 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3814 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
3815
3816 hr_utility.trace('i is ' ||to_char(i));
3817 hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
3818 hr_utility.trace('AAID is ' || to_char(l_aaid));
3819 hr_utility.trace('ASGID is ' || to_char(l_asgid));
3820 hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
3821 hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
3822
3823 result := result + pay_ca_balance_pkg.call_ca_balance_get_value
3824 ( l_balance_type_tab(i),
3825 'YTD' ,
3826 l_aaid,
3827 l_asgid,
3828 NULL,
3829 'PER' ,
3830 l_tax_unit_id,
3831 l_business_group_id,
3832 NULL
3833 );
3834
3835 /* start footnote archiving */
3836
3837 l_footnote_code := NULL;
3838 l_footnote_balance := NULL;
3839 l_footnote_amount := 0;
3840 old_l_footnote_code := NULL;
3841 l_footnote_code_ue := NULL;
3842 l_box38_footnote_code_ue:= NULL;
3843 l_footnote_amount_ue := NULL;
3844 l_no_of_fn_codes := 0;
3845 l_value := 0;
3846 old_l_value := 0;
3847 old_l_value1 := 0;
3848 old_l_value2 := 0;
3849 l_count := 0;
3850 l_single_footnote_code := NULL;
3851
3852 -- IF condition added by sneelapa, bug 10115617
3853 IF (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
3854 if result <> 0 then
3855
3856 /* hr_utility.trace_on('Y','T4AARCH'); */
3857 hr_utility.trace('Result is ' || to_char(result));
3858 /* Check the footnote amounts and codes and archive them */
3859 /* Check which elements has fed the balance and what are their
3860 footnotes and if the primary balance for that element is 0 or not */
3861
3862 if l_balance_type_tab(i) in ( 'T4A_BOX16',
3863 'T4A_BOX18',
3864 'T4A_BOX24',
3865 'T4A_BOX26',
3866 'T4A_BOX27',
3867 'T4A_BOX28',
3868 'T4A_BOX32',
3869 'T4A_BOX40') then
3870 begin
3871 hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
3872 hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
3873 hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874 hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875 hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876 hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3877 hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
3878 hr_utility.trace('l_box38_count :'||l_box38_count);
3879
3880 open c_balance_feed_info(l_balance_type_tab(i));
3881
3882 hr_utility.trace('balance_type '||l_balance_type_tab(i));
3883
3884 loop
3885
3886 hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
3887 hr_utility.trace('666 l_count '|| l_count);
3888 hr_utility.trace('666 p_assactid '|| p_assactid);
3889 fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
3890 if l_balance_type_tab(i) = 'T4A_BOX24'
3891 and l_footnote_code = '10(BOX24)' then
3892 l_footnote_code := '10A';
3893 end if;
3894
3895 exit when c_balance_feed_info%NOTFOUND;
3896 l_count := l_count + 1;
3897
3898 hr_utility.trace('l_footnote_balance '||l_footnote_balance);
3899 hr_utility.trace('l_footnotecode '||l_footnote_code);
3900 hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
3901 if l_footnote_code <> old_l_footnote_code then /* footnote
3902 changed */
3903 if old_l_footnote_code is not null then /* not the first
3904 record */
3905
3906 hr_utility.trace('archive ft_amount_ue'
3907 ||l_footnote_amount_ue);
3908 hr_utility.trace('archive ft_amount'
3909 ||to_char(l_footnote_amount));
3910 if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3911 and l_footnote_amount <> 0 then
3912
3913 l_footnote_amount_ue := 'CAEOY_' || l_balance_type_tab(i) ||'_'||old_l_footnote_code||'_AMT_PER_GRE_YTD';
3914
3915 ff_archive_api.create_archive_item(
3916 p_archive_item_id => l_archive_item_id
3917 ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_amount_ue)
3918 ,p_archive_value => l_footnote_amount
3919 ,p_archive_type => 'AAP'
3920 ,p_action_id => p_assactid
3921 ,p_legislation_code => 'CA'
3922 ,p_object_version_number => l_object_version_number
3923 ,p_context_name1 => 'TAX_UNIT_ID'
3924 ,p_context1 => l_tax_unit_id
3925 ,p_some_warning => l_some_warning
3926 );
3927
3928 l_no_of_fn_codes := l_no_of_fn_codes + 1;
3929 l_box38_count := l_box38_count + 1;
3930 l_single_footnote_code := old_l_footnote_code;
3931
3932 if l_footnote_amount < 0 then
3933 l_negative_balance_exists := 'Y';
3934 end if;
3935
3936 end if;
3937 l_footnote_amount := 0;
3938 old_l_footnote_code := l_footnote_code ;
3939 end if;
3940 end if; /* end of if l_footnote_code <> old_l_footnote_code */
3941
3942 old_l_footnote_code := l_footnote_code ;
3943 l_footnote_amount_ue := 'CAEOY_' || l_balance_type_tab(i) ||'_'||old_l_footnote_code||'_AMT_PER_GRE_YTD';
3944
3945
3946 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
3947 ( l_footnote_balance,
3948 'YTD' ,
3949 l_aaid,
3950 l_asgid,
3951 NULL,
3952 'PER' ,
3953 l_tax_unit_id,
3954 l_business_group_id,
3955 NULL );
3956
3957 hr_utility.trace('666 l_footnote_balance '|| l_footnote_balance);
3958 hr_utility.trace('666 l_value '|| l_value);
3959 l_footnote_amount := l_footnote_amount + l_value ;
3960
3961 /* to fix bug#2426517 added one more validation to if stmt */
3962 /* if (l_value <> 0 and
3963 get_footnote_user_entity_id(l_footnote_amount_ue) <> 0 ) then
3964
3965 l_no_of_fn_codes := l_no_of_fn_codes + 1;
3966 l_box38_count := l_box38_count + 1;
3967 l_single_footnote_code := l_footnote_code;
3968
3969 hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
3970 hr_utility.trace('chk l_box38_count '|| l_box38_count);
3971 hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
3972 end if;
3973 */
3974 hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
3975 end loop;
3976 close c_balance_feed_info;
3977
3978 if l_footnote_code is not null and
3979 l_footnote_amount_ue is not null and
3980 l_footnote_amount <> 0 and
3981 get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3982 then
3983 hr_utility.trace('666archive footnote amount '|| l_footnote_amount);
3984 hr_utility.trace('666archive footnote amount ue'|| l_footnote_amount_ue);
3985
3986 ff_archive_api.create_archive_item(
3987 p_archive_item_id => l_archive_item_id
3988 ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_amount_ue)
3989 ,p_archive_value => l_footnote_amount
3990 ,p_archive_type => 'AAP'
3991 ,p_action_id => p_assactid
3992 ,p_legislation_code => 'CA'
3993 ,p_object_version_number => l_object_version_number
3994 ,p_context_name1 => 'TAX_UNIT_ID'
3995 ,p_context1 => l_tax_unit_id
3996 ,p_some_warning => l_some_warning
3997 );
3998
3999 l_no_of_fn_codes := l_no_of_fn_codes + 1;
4000 l_box38_count := l_box38_count + 1;
4001 l_single_footnote_code := l_footnote_code;
4002
4003 if l_footnote_amount < 0 then
4004 l_negative_balance_exists := 'Y';
4005 end if;
4006
4007 end if;
4008
4009 hr_utility.trace('666archive l_no_of_fn_codes '|| l_no_of_fn_codes);
4010 if l_no_of_fn_codes > 1 then
4011 l_footnote_code := '13';
4012 hr_utility.trace('666archive footnote code '|| l_footnote_code);
4013 /* changed here as part of bugfix#2426517 */
4014 elsif l_no_of_fn_codes = 1 then
4015 l_footnote_code := l_single_footnote_code;
4016 hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
4017 elsif l_no_of_fn_codes = 0 then
4018 l_footnote_code := '00';
4019 hr_utility.trace('666archive footnote code '|| l_footnote_code);
4020 end if;
4021
4022 l_footnote_code_ue := 'CAEOY_' || l_balance_type_tab(i) || '_FOOTNOTE_CODE';
4023
4024 hr_utility.trace('before archiving l_footnote_code_ue is '|| l_footnote_code_ue);
4025 /* Part of fix for bug#2426517, to avoid unnecessary archiving
4026 of footnote code added one more condiftion to if stmt before
4027 archiving the footnote code for the corresponding BOX balance */
4028
4029 if l_footnote_code is not null and l_no_of_fn_codes > 0 and
4030 get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
4031
4032 hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
4033 hr_utility.trace('l_footnote_code:'|| l_footnote_code);
4034 hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
4035 ff_archive_api.create_archive_item(
4036 -- p_validate => 'TRUE'
4037 p_archive_item_id => l_archive_item_id
4038 ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_code_ue)
4039 ,p_archive_value => l_footnote_code
4040 ,p_archive_type => 'AAP'
4041 ,p_action_id => p_assactid
4042 ,p_legislation_code => 'CA'
4043 ,p_object_version_number => l_object_version_number
4044 ,p_context_name1 => 'TAX_UNIT_ID'
4045 ,p_context1 => l_tax_unit_id
4046 ,p_some_warning => l_some_warning
4047 );
4048 end if;
4049
4050 /* assigning value to box38_footnote_code */
4051
4052 hr_utility.trace('999 l_box38_count '|| l_box38_count);
4053 /* initialised l_box38_footnote_code before checking
4054 gross earnings to this assignment action fix#2426517 */
4055
4056 if l_box38_count > 1 then
4057 l_box38_footnote_code := '13';
4058 hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
4059 /* Added one more condition to archive correct footnote code
4060 value for box38 as part of bug fix#2426517 and assigned
4061 l_single_footnote_code to l_box38_footnote_code variable */
4062 elsif l_box38_count = 1 and l_no_of_fn_codes > 0 then
4063 l_box38_footnote_code := l_single_footnote_code;
4064 hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
4065 end if;
4066
4067 end;
4068 end if;
4069 end if;
4070 end if; -- (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010)
4071
4072 /** End of Footnote archiving **/
4073
4074 --hr_utility.trace_off;
4075
4076 hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4077 hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
4078 hr_utility.trace('Result is ' || to_char(result));
4079
4080 /* Added this condition to fix bug#2598777 */
4081 if l_user_entity_name_tab(i) = 'CAEOY_T4A_BOX34_PER_GRE_YTD' then
4082 result := round(result);
4083 end if;
4084 /* Bug 4021563 Added code for Status Indian type employee */
4085 if (l_balance_type_tab(i) in ( 'T4A_BOX16', 'T4A_BOX18',
4086 'T4A_BOX26',
4087 'T4A_BOX27',
4088 'T4A_BOX28') and l_status_indian = 'Y') then
4089 result := 0;
4090
4091 end if;
4092 ff_archive_api.create_archive_item(
4093 p_archive_item_id => l_archive_item_id
4094 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
4095 ,p_archive_value => result
4096 ,p_archive_type => 'AAP'
4097 ,p_action_id => p_assactid
4098 ,p_legislation_code => 'CA'
4099 ,p_object_version_number => l_object_version_number
4100 ,p_context_name1 => 'TAX_UNIT_ID'
4101 ,p_context1 => l_tax_unit_id
4102 ,p_some_warning => l_some_warning
4103 );
4104
4105 if result < 0 then
4106 l_negative_balance_exists := 'Y';
4107 end if;
4108
4109 end loop; /* for archiving all T4A Balances */
4110
4111 /** Start box38 footnote archiving **/
4112 hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4113 hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
4114 hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
4115
4116 /** box38 footnote archive has been moved after nonbox footnote archive **/
4117
4118 -- IF condition added by sneelapa, bug 10099479
4119 IF (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
4120
4121 /* start registration number archiving */
4122
4123 l_registration_no := NULL;
4124 old_l_registration_no := NULL;
4125 arch_l_registration_no := NULL;
4126 old_l_value := 0;
4127 old_l_registration_no1 := NULL;
4128 old_l_value1 := 0;
4129 old_l_registration_no2 := NULL;
4130 old_l_value2 := 0;
4131 arch_l_value := 0;
4132 l_value := 0;
4133
4134 begin
4135
4136 open c_reg_balance_feed_info('T4A_BOX34');
4137
4138 loop
4139
4140 fetch c_reg_balance_feed_info into l_registration_no,l_balance_name,
4141 l_element_type_id,l_ele_classification_id;
4142 exit when c_reg_balance_feed_info%NOTFOUND;
4143
4144 hr_utility.trace('checking for T4A_BOX34');
4145 hr_utility.trace('p_assactid:'||to_char(p_assactid));
4146 hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147 hr_utility.trace('l_registration_no:'||l_registration_no);
4148 hr_utility.trace('l_balance_name:'||l_balance_name);
4149 hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4150 hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
4151 hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
4152
4153 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4154 ( l_balance_name,
4155 'YTD' ,
4156 l_aaid,
4157 l_asgid,
4158 NULL,
4159 'PER' ,
4160 l_tax_unit_id,
4161 l_business_group_id,
4162 NULL );
4163
4164 hr_utility.trace('before check null l_value:'||to_char(l_value));
4165 if l_value is null then
4166 l_value := 0;
4167 end if;
4168
4169 hr_utility.trace('after check null l_value:'||to_char(l_value));
4170
4171
4172 /* Condition to check the amounts and determine the registration
4173 number to archive Bug fix 2408456 */
4174 if old_l_value = 0 then
4175 hr_utility.trace('in reg1');
4176 old_l_value := l_value;
4177 old_l_registration_no := l_registration_no;
4178 elsif old_l_value1 = 0 then
4179 hr_utility.trace('in reg2');
4180 old_l_value1 := l_value;
4181 old_l_registration_no1 := l_registration_no;
4182 elsif old_l_value2 = 0 then
4183 hr_utility.trace('in reg3');
4184 old_l_value2 := l_value;
4185 old_l_registration_no2 := l_registration_no;
4186 else
4187 if l_value > nvl(old_l_value,0) then
4188 hr_utility.trace('old_l1');
4189 old_l_value := l_value;
4190 old_l_registration_no := l_registration_no;
4191 elsif l_value > nvl(old_l_value1,0) then
4192 hr_utility.trace('old_2');
4193 old_l_value1 := l_value;
4194 old_l_registration_no1 := l_registration_no;
4195 elsif l_value > nvl(old_l_value2,0) then
4196 old_l_value2 := l_value;
4197 old_l_registration_no2 := l_registration_no;
4198 end if;
4199 end if;
4200 /* End of Condition to check amounts Bug fix 2408456 */
4201
4202 end loop;
4203 close c_reg_balance_feed_info;
4204 if old_l_value > old_l_value1 then
4205 hr_utility.trace('in reg4');
4206 if old_l_value> old_l_value2 then
4207 arch_l_registration_no := old_l_registration_no;
4208 arch_l_value := old_l_value;
4209 else
4210 arch_l_registration_no := old_l_registration_no2;
4211 arch_l_value := old_l_value2;
4212 end if;
4213 else
4214 if old_l_value1>old_l_value2 then
4215 arch_l_registration_no := old_l_registration_no1;
4216 arch_l_value := old_l_value1;
4217 else
4218 arch_l_registration_no := old_l_registration_no2;
4219 arch_l_value := old_l_value2;
4220 end if;
4221 end if;
4222
4223 /* archive registration number derived from T4A_BOX34 */
4224 if arch_l_registration_no is not null and arch_l_value > 0 then
4225 -- l_box34_regno_flag added for 10099479
4226 l_box34_regno_flag := 1;
4227
4228 ff_archive_api.create_archive_item(
4229 -- p_validate => 'TRUE'
4230 p_archive_item_id => l_archive_item_id
4231 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_EMPLOYEE_REGISTRATION_NO')
4232 ,p_archive_value => arch_l_registration_no
4233 ,p_archive_type => 'AAP'
4234 ,p_action_id => p_assactid
4235 ,p_legislation_code => 'CA'
4236 ,p_object_version_number => l_object_version_number
4237 ,p_some_warning => l_some_warning
4238 );
4239 end if;
4240
4241 /* Bug fix#2696309, Employer level Pension Plan Register Number */
4242
4243 -- Added l_negative_balance_exists ='N' condition for bug 10420909
4244 if l_negative_balance_exists ='N' then
4245
4246 hr_utility.trace('Start of Employer Level PP Reg no ');
4247
4248 if old_l_registration_no is not null and old_l_value >0 then
4249 hr_utility.trace('in reg1 pay_action_information');
4250 hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4251 hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4252
4253 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4254 l_payroll_action_id
4255 ,old_l_registration_no
4256 ,p_effective_date);
4257 fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
4258 if c_get_emplr_reg_no%FOUND then
4259 hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
4260
4261 ln_emplr_regamt := ln_emplr_regamt + old_l_value;
4262
4263 update pay_action_information
4264 set action_information5 = to_char(ln_emplr_regamt)
4265 where action_context_id = l_payroll_action_id
4266 and tax_unit_id = l_tax_unit_id
4267 and effective_date = p_effective_date
4268 and action_information_category = 'CAEOY PENSION PLAN INFO'
4269 AND ACTION_INFORMATION4 = old_l_registration_no;
4270
4271
4272 else
4273
4274 hr_utility.trace('in reg1 insert pay_action_information');
4275 -- insert a new record into pay_action_information
4276
4277 pay_action_information_api.create_action_information(
4278 p_action_information_id => l_action_information_id_1,
4279 p_object_version_number => l_object_version_number_1,
4280 p_action_information_category => 'CAEOY PENSION PLAN INFO',
4281 p_action_context_id => l_payroll_action_id,
4282 p_action_context_type => 'PA',
4283 p_jurisdiction_code => NULL,
4284 p_tax_unit_id => l_tax_unit_id,
4285 p_effective_date => p_effective_date,
4286 p_action_information1 => NULL,
4287 p_action_information2 => NULL,
4288 p_action_information3 => NULL,
4289 p_action_information4 => old_l_registration_no,
4290 p_action_information5 => to_char(old_l_value),
4291 p_action_information6 => NULL,
4292 p_action_information7 => NULL,
4293 p_action_information8 => NULL,
4294 p_action_information9 => NULL,
4295 p_action_information10 => NULL,
4296 p_action_information11 => NULL,
4297 p_action_information12 => NULL,
4298 p_action_information13 => NULL,
4299 p_action_information14 => NULL,
4300 p_action_information15 => NULL,
4301 p_action_information16 => NULL,
4302 p_action_information17 => NULL,
4303 p_action_information18 => NULL,
4304 p_action_information19 => NULL,
4305 p_action_information20 => NULL,
4306 p_action_information21 => NULL,
4307 p_action_information22 => NULL,
4308 p_action_information23 => NULL,
4309 p_action_information24 => NULL,
4310 p_action_information25 => NULL,
4311 p_action_information26 => NULL,
4312 p_action_information27 => NULL,
4313 p_action_information28 => NULL,
4314 p_action_information29 => NULL,
4315 p_action_information30 => NULL
4316 );
4317
4318 end if; -- c_get_emplr_reg_no%FOUND
4319 close c_get_emplr_reg_no;
4320 end if;
4321
4322 if old_l_registration_no1 is not null and old_l_value1 >0 then
4323 hr_utility.trace('in reg2 pay_action_information');
4324
4325 hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4326 hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4327
4328 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4329 l_payroll_action_id
4330 , old_l_registration_no1
4331 ,p_effective_date);
4332 fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
4333 if c_get_emplr_reg_no%FOUND then
4334
4335 hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
4336
4337 ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
4338
4339 update pay_action_information
4340 set action_information5 = to_char(ln_emplr_regamt1)
4341 where action_context_id = l_payroll_action_id
4342 and tax_unit_id = l_tax_unit_id
4343 and effective_date = p_effective_date
4344 and action_information_category = 'CAEOY PENSION PLAN INFO'
4345 AND ACTION_INFORMATION4 = old_l_registration_no1;
4346
4347 else
4348 hr_utility.trace('in reg2 insert pay_action_information');
4349
4350 -- insert a new record into pay_action_information
4351
4352 pay_action_information_api.create_action_information(
4353 p_action_information_id => l_action_information_id_1,
4354 p_object_version_number => l_object_version_number_1,
4355 p_action_information_category => 'CAEOY PENSION PLAN INFO',
4356 p_action_context_id => l_payroll_action_id,
4357 p_action_context_type => 'PA',
4358 p_jurisdiction_code => NULL,
4359 p_tax_unit_id => l_tax_unit_id,
4360 p_effective_date => p_effective_date,
4361 p_action_information1 => NULL,
4362 p_action_information2 => NULL,
4363 p_action_information3 => NULL,
4364 p_action_information4 => old_l_registration_no1,
4365 p_action_information5 => to_char(old_l_value1),
4366 p_action_information6 => NULL,
4367 p_action_information7 => NULL,
4368 p_action_information8 => NULL,
4369 p_action_information9 => NULL,
4370 p_action_information10 => NULL,
4371 p_action_information11 => NULL,
4372 p_action_information12 => NULL,
4373 p_action_information13 => NULL,
4374 p_action_information14 => NULL,
4375 p_action_information15 => NULL,
4376 p_action_information16 => NULL,
4377 p_action_information17 => NULL,
4378 p_action_information18 => NULL,
4379 p_action_information19 => NULL,
4380 p_action_information20 => NULL,
4381 p_action_information21 => NULL,
4382 p_action_information22 => NULL,
4383 p_action_information23 => NULL,
4384 p_action_information24 => NULL,
4385 p_action_information25 => NULL,
4386 p_action_information26 => NULL,
4387 p_action_information27 => NULL,
4388 p_action_information28 => NULL,
4389 p_action_information29 => NULL,
4390 p_action_information30 => NULL
4391 );
4392
4393 end if; -- c_get_emplr_reg_no%FOUND
4394 close c_get_emplr_reg_no;
4395 end if;
4396
4397 if old_l_registration_no2 is not null and old_l_value2 > 0 then
4398 hr_utility.trace('in reg3 pay_action_information');
4399 hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4400 hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4401 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4402 l_payroll_action_id
4403 , old_l_registration_no2
4404 ,p_effective_date);
4405 fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
4406 if c_get_emplr_reg_no%FOUND then
4407
4408 hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
4409
4410 ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
4411
4412 update pay_action_information
4413 set action_information5 = to_char(ln_emplr_regamt2)
4414 where action_context_id = l_payroll_action_id
4415 and tax_unit_id = l_tax_unit_id
4416 and effective_date = p_effective_date
4417 and action_information_category = 'CAEOY PENSION PLAN INFO'
4418 AND ACTION_INFORMATION4 = old_l_registration_no2;
4419
4420 hr_utility.trace('Updated pay_action_information');
4421
4422 else
4423 hr_utility.trace('in reg3 insert pay_action_information');
4424
4425 -- insert a new record into pay_action_information
4426
4427 pay_action_information_api.create_action_information(
4428 p_action_information_id => l_action_information_id_1,
4429 p_object_version_number => l_object_version_number_1,
4430 p_action_information_category => 'CAEOY PENSION PLAN INFO',
4431 p_action_context_id => l_payroll_action_id,
4432 p_action_context_type => 'PA',
4433 p_jurisdiction_code => NULL,
4434 p_tax_unit_id => l_tax_unit_id,
4435 p_effective_date => p_effective_date,
4436 p_action_information1 => NULL,
4437 p_action_information2 => NULL,
4438 p_action_information3 => NULL,
4439 p_action_information4 => old_l_registration_no2,
4440 p_action_information5 => to_char(old_l_value2),
4441 p_action_information6 => NULL,
4442 p_action_information7 => NULL,
4443 p_action_information8 => NULL,
4444 p_action_information9 => NULL,
4445 p_action_information10 => NULL,
4446 p_action_information11 => NULL,
4447 p_action_information12 => NULL,
4448 p_action_information13 => NULL,
4449 p_action_information14 => NULL,
4450 p_action_information15 => NULL,
4451 p_action_information16 => NULL,
4452 p_action_information17 => NULL,
4453 p_action_information18 => NULL,
4454 p_action_information19 => NULL,
4455 p_action_information20 => NULL,
4456 p_action_information21 => NULL,
4457 p_action_information22 => NULL,
4458 p_action_information23 => NULL,
4459 p_action_information24 => NULL,
4460 p_action_information25 => NULL,
4461 p_action_information26 => NULL,
4462 p_action_information27 => NULL,
4463 p_action_information28 => NULL,
4464 p_action_information29 => NULL,
4465 p_action_information30 => NULL
4466 );
4467
4468 end if; -- c_get_emplr_reg_no%FOUND
4469 close c_get_emplr_reg_no;
4470 end if;
4471
4472 end if; -- if l_negative_balance_exists ='N' then added for bug 10420909
4473
4474
4475 /* Added else part to fix bug#2408456
4476 if the registration number doesn't exist for the elements
4477 that are fed to balance T4A_BOX34 then check the elements
4478 that are fed to balance T4A_BOX32 and archive it */
4479
4480 /* l_registration_no := NULL;
4481 old_l_registration_no := NULL;
4482 arch_l_registration_no := NULL;
4483 old_l_value := 0;
4484 old_l_registration_no1 := NULL;
4485 old_l_value1 := 0;
4486 old_l_registration_no2 := NULL;
4487 old_l_value2 := 0;
4488 arch_l_value := 0;
4489 l_value := 0;
4490 */
4491
4492 if old_l_registration_no is null or
4493 old_l_registration_no1 is null or
4494 old_l_registration_no2 is null then
4495
4496 l_registration_no := NULL;
4497 old_l_registration_no := NULL; old_l_value := 0;
4498 old_l_registration_no1 := NULL;
4499 old_l_value1 := 0;
4500 old_l_registration_no2 := NULL;
4501 old_l_value2 := 0;
4502 l_value := 0;
4503 arch_l_registration_no := NULL;
4504 arch_l_value := 0;
4505 begin
4506
4507 open c_reg_balance_feed_info('T4A_BOX32');
4508
4509 loop
4510 fetch c_reg_balance_feed_info into l_registration_no,
4511 l_balance_name,l_element_type_id,
4512 l_ele_classification_id;
4513 exit when c_reg_balance_feed_info%NOTFOUND;
4514
4515 hr_utility.trace('checking for T4A_BOX32 ');
4516 hr_utility.trace('p_assactid:'||to_char(p_assactid));
4517 hr_utility.trace('l_asgid:'||to_char(l_asgid));
4518 hr_utility.trace('l_registration_no:'||l_registration_no);
4519 hr_utility.trace('l_balance_name:'||l_balance_name);
4520 hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4521
4522 l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4523 ( l_balance_name,
4524 'YTD' ,
4525 l_aaid,
4526 l_asgid,
4527 NULL,
4528 'PER' ,
4529 l_tax_unit_id,
4530 l_business_group_id,
4531 NULL );
4532
4533
4534 hr_utility.trace('l_value:'||to_char(l_value));
4535 if l_value is null then
4536 l_value := 0;
4537 end if;
4538
4539 hr_utility.trace('before checking the new validation ');
4540 hr_utility.trace('l_value :'||to_char(l_value));
4541 hr_utility.trace('l_registration_no:'||l_registration_no);
4542 hr_utility.trace('old_l_value :'||to_char(old_l_value));
4543 hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
4544
4545 /* Condition to check the amounts and determine the
4546 registration number to archive Bug fix 2408456 */
4547
4548 if l_value > nvl(old_l_value,0) then
4549
4550 old_l_value := l_value;
4551 old_l_registration_no := l_registration_no;
4552 elsif l_value > nvl(old_l_value1,0) then
4553 old_l_value1 := l_value;
4554 old_l_registration_no1 := l_registration_no;
4555 elsif l_value > nvl(old_l_value2,0) then
4556 old_l_value2 := l_value;
4557 old_l_registration_no2 := l_registration_no;
4558
4559 end if;
4560 /* End of Condition to check amounts Bug fix 2408456 */
4561
4562 end loop;
4563 close c_reg_balance_feed_info;
4564
4565 if old_l_value > old_l_value1 then
4566 if old_l_value> old_l_value2 then
4567 arch_l_registration_no := old_l_registration_no;
4568 arch_l_value := old_l_value;
4569 else
4570 arch_l_registration_no := old_l_registration_no2;
4571 arch_l_value := old_l_value2;
4572 end if;
4573 else
4574 if old_l_value1>old_l_value2 then
4575 arch_l_registration_no := old_l_registration_no1;
4576 arch_l_value := old_l_value1;
4577 else
4578 arch_l_registration_no := old_l_registration_no2;
4579 arch_l_value := old_l_value2;
4580 end if;
4581 end if;
4582 if arch_l_registration_no is not null and arch_l_value > 0
4583 and l_box34_regno_flag = 0 then
4584 -- l_box34_regno_flag added for 10099479
4585
4586 ff_archive_api.create_archive_item(
4587 -- p_validate => 'TRUE'
4588 p_archive_item_id => l_archive_item_id
4589 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_EMPLOYEE_REGISTRATION_NO')
4590 ,p_archive_value => arch_l_registration_no
4591 ,p_archive_type => 'AAP'
4592 ,p_action_id => p_assactid
4593 ,p_legislation_code => 'CA'
4594 ,p_object_version_number => l_object_version_number
4595 ,p_some_warning => l_some_warning
4596 );
4597 end if;
4598
4599 -- l_negative_balance_exists condition added for bug 10420909
4600 if l_negative_balance_exists ='N' then
4601 if old_l_registration_no is not null and old_l_value > 0 then
4602
4603
4604 /* Bug fix#2696309, Employer level Pension Plan Register Number */
4605
4606 hr_utility.trace('Start of Employer Level PP Reg no ');
4607 hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4608 hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4609 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4610 l_payroll_action_id
4611 ,old_l_registration_no
4612 ,p_effective_date);
4613 fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
4614 if c_get_emplr_reg_no%FOUND then
4615 hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
4616 ln_emplr_regamt := ln_emplr_regamt + old_l_value;
4617
4618 update pay_action_information
4619 set action_information5 = to_char(ln_emplr_regamt)
4620 where action_context_id = l_payroll_action_id
4621 and tax_unit_id = l_tax_unit_id
4622 and effective_date = p_effective_date
4623 and action_information_category = 'CAEOY PENSION PLAN INFO'
4624 AND ACTION_INFORMATION4 = old_l_registration_no;
4625
4626 else
4627 -- insert a new record into pay_action_information
4628
4629 pay_action_information_api.create_action_information(
4630 p_action_information_id => l_action_information_id_1,
4631 p_object_version_number => l_object_version_number_1,
4632 p_action_information_category => 'CAEOY PENSION PLAN INFO',
4633 p_action_context_id => l_payroll_action_id,
4634 p_action_context_type => 'PA',
4635 p_jurisdiction_code => NULL ,
4636 p_tax_unit_id => l_tax_unit_id,
4637 p_effective_date => p_effective_date,
4638 p_assignment_id => l_asgid,
4639 p_action_information1 => NULL,
4640 p_action_information2 => NULL,
4641 p_action_information3 => NULL,
4642 p_action_information4 => old_l_registration_no,
4643 p_action_information5 => to_char(old_l_value),
4644 p_action_information6 => NULL,
4645 p_action_information7 => NULL,
4646 p_action_information8 => NULL,
4647 p_action_information9 => NULL,
4648 p_action_information10 => NULL,
4649 p_action_information11 => NULL,
4650 p_action_information12 => NULL,
4651 p_action_information13 => NULL,
4652 p_action_information14 => NULL,
4653 p_action_information15 => NULL,
4654 p_action_information16 => NULL,
4655 p_action_information17 => NULL,
4656 p_action_information18 => NULL,
4657 p_action_information19 => NULL,
4658 p_action_information20 => NULL,
4659 p_action_information21 => NULL,
4660 p_action_information22 => NULL,
4661 p_action_information23 => NULL,
4662 p_action_information24 => NULL,
4663 p_action_information25 => NULL,
4664 p_action_information26 => NULL,
4665 p_action_information27 => NULL,
4666 p_action_information28 => NULL,
4667 p_action_information29 => NULL,
4668 p_action_information30 => NULL
4669 );
4670
4671 end if; -- c_get_emplr_reg_no%FOUND
4672 close c_get_emplr_reg_no;
4673 end if;
4674 if old_l_registration_no1 is not null and old_l_value1 > 0 then
4675
4676
4677 /* Bug fix#2696309, Employer level Pension Plan Register Number */
4678
4679 hr_utility.trace('Start of Employer Level PP Reg no ');
4680 hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4681 hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4682
4683 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4684 l_payroll_action_id
4685 ,old_l_registration_no1
4686 ,p_effective_date);
4687 fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
4688 if c_get_emplr_reg_no%FOUND then
4689 hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
4690 ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
4691
4692 update pay_action_information
4693 set action_information5 = to_char(ln_emplr_regamt1)
4694 where action_context_id = l_payroll_action_id
4695 and tax_unit_id = l_tax_unit_id
4696 and effective_date = p_effective_date
4697 and action_information_category = 'CAEOY PENSION PLAN INFO'
4698 AND ACTION_INFORMATION4 = old_l_registration_no1;
4699
4700 else
4701 -- insert a new record into pay_action_information
4702
4703 pay_action_information_api.create_action_information(
4704 p_action_information_id => l_action_information_id_1,
4705 p_object_version_number => l_object_version_number_1,
4706 p_action_information_category => 'CAEOY PENSION PLAN INFO',
4707 p_action_context_id => l_payroll_action_id,
4708 p_action_context_type => 'PA',
4709 p_jurisdiction_code => NULL ,
4710 p_tax_unit_id => l_tax_unit_id,
4711 p_effective_date => p_effective_date,
4712 p_assignment_id => l_asgid,
4713 p_action_information1 => NULL,
4714 p_action_information2 => NULL,
4715 p_action_information3 => NULL,
4716 p_action_information4 => old_l_registration_no1,
4717 p_action_information5 => to_char(old_l_value1),
4718 p_action_information6 => NULL,
4719 p_action_information7 => NULL,
4720 p_action_information8 => NULL,
4721 p_action_information9 => NULL,
4722 p_action_information10 => NULL,
4723 p_action_information11 => NULL,
4724 p_action_information12 => NULL,
4725 p_action_information13 => NULL,
4726 p_action_information14 => NULL,
4727 p_action_information15 => NULL,
4728 p_action_information16 => NULL,
4729 p_action_information17 => NULL,
4730 p_action_information18 => NULL,
4731 p_action_information19 => NULL,
4732 p_action_information20 => NULL,
4733 p_action_information21 => NULL,
4734 p_action_information22 => NULL,
4735 p_action_information23 => NULL,
4736 p_action_information24 => NULL,
4737 p_action_information25 => NULL,
4738 p_action_information26 => NULL,
4739 p_action_information27 => NULL,
4740 p_action_information28 => NULL,
4741 p_action_information29 => NULL,
4742 p_action_information30 => NULL
4743 );
4744
4745 end if; -- c_get_emplr_reg_no%FOUND
4746 close c_get_emplr_reg_no;
4747
4748 end if; /* for old_l_registration_no1 is not null derived
4749 from T4A_BOX32 */
4750
4751 if old_l_registration_no2 is not null and old_l_value2 > 0 then
4752
4753 /* Bug fix#2696309, Employer level Pension Plan Register Number */
4754
4755 hr_utility.trace('Start of Employer Level PP Reg no ');
4756 hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4757 hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4758
4759 open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4760 l_payroll_action_id
4761 ,old_l_registration_no2
4762 ,p_effective_date);
4763 fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
4764 hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
4765 if c_get_emplr_reg_no%FOUND then
4766 ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
4767
4768 update pay_action_information
4769 set action_information5 = to_char(ln_emplr_regamt2)
4770 where action_context_id = l_payroll_action_id
4771 and tax_unit_id = l_tax_unit_id
4772 and effective_date = p_effective_date
4773 and action_information_category = 'CAEOY PENSION PLAN INFO'
4774 AND ACTION_INFORMATION4 = old_l_registration_no2;
4775
4776 else
4777 -- insert a new record into pay_action_information
4778
4779 pay_action_information_api.create_action_information(
4780 p_action_information_id => l_action_information_id_1,
4781 p_object_version_number => l_object_version_number_1,
4782 p_action_information_category => 'CAEOY PENSION PLAN INFO',
4783 p_action_context_id => l_payroll_action_id,
4784 p_action_context_type => 'PA',
4785 p_jurisdiction_code => NULL ,
4786 p_tax_unit_id => l_tax_unit_id,
4787 p_effective_date => p_effective_date,
4788 p_assignment_id => l_asgid,
4789 p_action_information1 => NULL,
4790 p_action_information2 => NULL,
4791 p_action_information3 => NULL,
4792 p_action_information4 => old_l_registration_no2,
4793 p_action_information5 => to_char(old_l_value2),
4794 p_action_information6 => NULL,
4795 p_action_information7 => NULL,
4796 p_action_information8 => NULL,
4797 p_action_information9 => NULL,
4798 p_action_information10 => NULL,
4799 p_action_information11 => NULL,
4800 p_action_information12 => NULL,
4801 p_action_information13 => NULL,
4802 p_action_information14 => NULL,
4803 p_action_information15 => NULL,
4804 p_action_information16 => NULL,
4805 p_action_information17 => NULL,
4806 p_action_information18 => NULL,
4807 p_action_information19 => NULL,
4808 p_action_information20 => NULL,
4809 p_action_information21 => NULL,
4810 p_action_information22 => NULL,
4811 p_action_information23 => NULL,
4812 p_action_information24 => NULL,
4813 p_action_information25 => NULL,
4814 p_action_information26 => NULL,
4815 p_action_information27 => NULL,
4816 p_action_information28 => NULL,
4817 p_action_information29 => NULL,
4818 p_action_information30 => NULL
4819 );
4820
4821 end if; -- c_get_emplr_reg_no%FOUND
4822 close c_get_emplr_reg_no;
4823
4824 end if; /* for old_l_registration_no2 is not null derived
4825 from T4A_BOX32 */
4826
4827 end if; -- if l_negative_balance_exists ='N' then added for bug 10420909
4828
4829
4830 end;
4831
4832 /* End of bug fix for bug #2408456 */
4833 end if; /* for old_l_registration_no is not null derived
4834 from T4A_BOX34 */
4835 end;
4836 /* end registration number archiving */
4837 end if; -- IF (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010)
4838 -- IF condition added by sneelapa, bug 10099479
4839 else
4840 hr_utility.trace('result is 0');
4841
4842 end if; /* end if for result <> 0 condition */
4843
4844 /* Need to add the T4A Nonbox Footnote archiving code to fix bug#2175045 */
4845 begin
4846
4847 l_total_mesg_amt := 0;
4848 l_mesg_amt := 0;
4849
4850 open cur_non_box_mesg(p_assactid, p_effective_date);
4851 loop
4852 fetch cur_non_box_mesg into l_messages,
4853 l_mesg_amt,
4854 ln_tax_unit_id,
4855 ld_eff_date,
4856 ln_assignment_action_id;
4857
4858 if cur_non_box_mesg%notfound then
4859 exit;
4860 end if;
4861
4862 hr_utility.trace('l_messages - '||l_messages);
4863 hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
4864
4865 /* If the same Non Box footnote is processed more than
4866 once during the year, then the sum of the associated
4867 amounts is archived */
4868
4869 if ((l_messages <> l_prev_messages) and
4870 (l_prev_messages is not null)) then
4871
4872 hr_utility.trace('l_prev_messages - '||l_prev_messages);
4873
4874 if l_total_mesg_amt <> 0 then
4875
4876 pay_action_information_api.create_action_information(
4877 p_action_information_id => l_action_information_id_1,
4878 p_object_version_number => l_object_version_number_1,
4879 p_action_information_category => 'CA FOOTNOTES',
4880 p_action_context_id => p_assactid,
4881 p_action_context_type => 'AAP',
4882 p_jurisdiction_code => NULL,
4883 p_tax_unit_id => ln_prev_tax_unit_id,
4884 p_effective_date => ld_prev_eff_date,
4885 p_assignment_id => l_asgid,
4886 p_action_information1 => NULL,
4887 p_action_information2 => NULL,
4888 p_action_information3 => NULL,
4889 p_action_information4 => l_prev_messages,
4890 p_action_information5 => l_total_mesg_amt,
4891 p_action_information6 => 'T4A',
4892 p_action_information7 => NULL,
4893 p_action_information8 => NULL,
4894 p_action_information9 => NULL,
4895 p_action_information10 => NULL,
4896 p_action_information11 => NULL,
4897 p_action_information12 => NULL,
4898 p_action_information13 => NULL,
4899 p_action_information14 => NULL,
4900 p_action_information15 => NULL,
4901 p_action_information16 => NULL,
4902 p_action_information17 => NULL,
4903 p_action_information18 => NULL,
4904 p_action_information19 => NULL,
4905 p_action_information20 => NULL,
4906 p_action_information21 => NULL,
4907 p_action_information22 => NULL,
4908 p_action_information23 => NULL,
4909 p_action_information24 => NULL,
4910 p_action_information25 => NULL,
4911 p_action_information26 => NULL,
4912 p_action_information27 => NULL,
4913 p_action_information28 => NULL,
4914 p_action_information29 => NULL,
4915 p_action_information30 => NULL
4916 );
4917
4918 if l_box38_footnote_code = '00' then
4919 l_box38_footnote_code := l_prev_messages;
4920 else
4921 if l_box38_footnote_code <> '13' then
4922 l_box38_footnote_code := '13';
4923 end if;
4924 end if;
4925
4926 if l_total_mesg_amt < 0 then
4927 l_negative_balance_exists := 'Y';
4928 end if;
4929
4930 end if;
4931
4932 l_total_mesg_amt := l_mesg_amt;
4933 else
4934 l_total_mesg_amt := l_total_mesg_amt + l_mesg_amt;
4935 end if;
4936
4937 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4938
4939 l_prev_messages := l_messages;
4940 ln_prev_tax_unit_id := ln_tax_unit_id;
4941 ld_prev_eff_date := ld_eff_date;
4942
4943 end loop;
4944
4945 close cur_non_box_mesg;
4946
4947 if (l_prev_messages is not null) then
4948
4949 hr_utility.trace('l_prev_messages - '||l_prev_messages);
4950 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4951
4952 if l_total_mesg_amt <> 0 then
4953
4954 pay_action_information_api.create_action_information(
4955 p_action_information_id => l_action_information_id_1,
4956 p_object_version_number => l_object_version_number_1,
4957 p_action_information_category => 'CA FOOTNOTES',
4958 p_action_context_id => p_assactid,
4959 p_action_context_type => 'AAP',
4960 p_jurisdiction_code => NULL,
4961 p_tax_unit_id => ln_prev_tax_unit_id,
4962 p_effective_date => ld_prev_eff_date,
4963 p_assignment_id => l_asgid,
4964 p_action_information1 => NULL,
4965 p_action_information2 => NULL,
4966 p_action_information3 => NULL,
4967 p_action_information4 => l_prev_messages,
4968 p_action_information5 => l_total_mesg_amt,
4969 p_action_information6 => 'T4A',
4970 p_action_information7 => NULL,
4971 p_action_information8 => NULL,
4972 p_action_information9 => NULL,
4973 p_action_information10 => NULL,
4974 p_action_information11 => NULL,
4975 p_action_information12 => NULL,
4976 p_action_information13 => NULL,
4977 p_action_information14 => NULL,
4978 p_action_information15 => NULL,
4979 p_action_information16 => NULL,
4980 p_action_information17 => NULL,
4981 p_action_information18 => NULL,
4982 p_action_information19 => NULL,
4983 p_action_information20 => NULL,
4984 p_action_information21 => NULL,
4985 p_action_information22 => NULL,
4986 p_action_information23 => NULL,
4987 p_action_information24 => NULL,
4988 p_action_information25 => NULL,
4989 p_action_information26 => NULL,
4990 p_action_information27 => NULL,
4991 p_action_information28 => NULL,
4992 p_action_information29 => NULL,
4993 p_action_information30 => NULL
4994 );
4995
4996 if l_box38_footnote_code = '00' then
4997 l_box38_footnote_code := l_prev_messages;
4998 else
4999 if l_box38_footnote_code <> '13' then
5000 l_box38_footnote_code := '13';
5001 end if;
5002 end if;
5003
5004 if l_total_mesg_amt < 0 then
5005 l_negative_balance_exists := 'Y';
5006 end if;
5007
5008 end if;
5009
5010 end if;
5011
5012 end; /* End of T4A Nonbox Footnote Archive end of bugfix#2175045 */
5013
5014 /* Archive the negative balance flag */
5015 /* Commented this as part of bug 6456662
5016 CAEOY_T4A_NEGATIVE_BALANCE_EXISTS should be archived only once for the EMPLOYEE
5017 This logic is moved to code which is calling eoy_archive_data_new_format
5018 procedure
5019
5020 ff_archive_api.create_archive_item(
5021 p_archive_item_id => l_archive_item_id
5022 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_NEGATIVE_BALANCE_EXISTS')
5023 ,p_archive_value => l_negative_balance_exists
5024 ,p_archive_type => 'AAP'
5025 ,p_action_id => p_assactid
5026 ,p_legislation_code => 'CA'
5027 ,p_object_version_number => l_object_version_number
5028 ,p_context_name1 => 'TAX_UNIT_ID'
5029 ,p_context1 => l_tax_unit_id
5030 ,p_some_warning => l_some_warning
5031 );
5032
5033 */
5034
5035 /* T4A Box 38 Footnote code archiving */
5036 ff_archive_api.create_archive_item(
5037 p_archive_item_id => l_archive_item_id
5038 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_FOOTNOTE_CODE')
5039 ,p_archive_value => l_box38_footnote_code
5040 ,p_archive_type => 'AAP'
5041 ,p_action_id => p_assactid
5042 ,p_legislation_code => 'CA'
5043 ,p_object_version_number => l_object_version_number
5044 ,p_context_name1 => 'TAX_UNIT_ID'
5045 ,p_context1 => l_tax_unit_id
5046 ,p_some_warning => l_some_warning
5047 );
5048 /* End of t4a box 38 archive */
5049
5050 begin
5051 l_counter := 0;
5052 hr_utility.trace('selecting people');
5053
5054 select PEOPLE.person_id,
5055 PEOPLE.first_name,
5056 PEOPLE.last_name,
5057 PEOPLE.employee_number,
5058 PEOPLE.WORK_TELEPHONE,
5059 replace(PEOPLE.national_identifier,' '),
5060 PEOPLE.middle_names, /* Bug:1474421 Changed pre_name_adjunct to middle_names */
5061 ASSIGN.organization_id,
5062 ASSIGN.location_id
5063 into l_person_id,
5064 l_first_name,
5065 l_last_name,
5066 l_employee_number,
5067 l_work_telephone,
5068 l_national_identifier,
5069 l_middle_names, /* changed variable l_pre_name_adjunct to l_middle_names */
5070 l_organization_id,
5071 l_location_id
5072 from
5073 per_all_assignments_f ASSIGN
5074 , per_all_people_f PEOPLE
5075 , per_person_types PTYPE
5076 , fnd_sessions SES
5077 where l_date_earned BETWEEN ASSIGN.effective_start_date
5078 AND ASSIGN.effective_end_date
5079 and ASSIGN.assignment_id = l_asgid
5080 and PEOPLE.person_id = ASSIGN.person_id
5081 and l_date_earned BETWEEN PEOPLE.effective_start_date
5082 AND PEOPLE.effective_end_date
5083 and PTYPE.person_type_id = PEOPLE.person_type_id
5084 and SES.session_id = USERENV('SESSIONID') ;
5085 exception
5086 when no_data_found then
5087 l_first_name := null;
5088 l_last_name := null;
5089 l_employee_number := null;
5090 l_work_telephone := null;
5091 l_national_identifier := null;
5092 l_middle_names := null; /* changed variable l_pre_name_adjunct
5093 to l_middle_names */
5094 hr_utility.raise_error;
5095 end;
5096
5097 begin
5098 select PHONE.phone_number
5099 into l_employee_phone_no
5100 from per_phones PHONE ,
5101 fnd_sessions SES
5102 where PHONE.parent_id (+) = l_person_id
5103 and PHONE.parent_table (+)= 'PER_ALL_PEOPLE_F'
5104 and PHONE.phone_type (+)= 'W1'
5105 and l_date_earned BETWEEN NVL(PHONE.date_from,SES.effective_date)
5106 AND NVL(PHONE.date_to,SES.effective_date)
5107 and SES.session_id = USERENV('SESSIONID') ;
5108 exception
5109 when no_data_found then
5110 l_employee_phone_no := l_work_telephone;
5111 end;
5112
5113 hr_utility.trace('selected people');
5114 /* Initialise l_count */
5115 l_count := 0;
5116
5117 /* hr_utility.trace_on('Y','ORACLE'); */
5118
5119 l_counter := l_counter + 1;
5120 l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
5121 l_user_entity_value_tab(l_counter) := l_person_id;
5122
5123 l_counter := l_counter + 1;
5124 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
5125 l_user_entity_value_tab(l_counter) := l_first_name;
5126
5127 l_counter := l_counter + 1;
5128 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
5129 l_user_entity_value_tab(l_counter) := l_last_name;
5130
5131 l_counter := l_counter + 1;
5132 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
5133 l_user_entity_value_tab(l_counter) := l_middle_names; /* changed variable
5134 l_pre_name_adjunct to l_middle_names */
5135
5136 l_counter := l_counter + 1;
5137 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
5138 l_user_entity_value_tab(l_counter) := l_national_identifier;
5139
5140 l_counter := l_counter + 1;
5141 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
5142 l_user_entity_value_tab(l_counter) := l_employee_number;
5143
5144 l_counter := l_counter + 1;
5145 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_ORGANIZATION_ID';
5146 l_user_entity_value_tab(l_counter) := l_organization_id;
5147
5148 l_counter := l_counter + 1;
5149 l_user_entity_name_tab(l_counter) := 'CAEOY_T4_LOCATION_ID';
5150 l_user_entity_value_tab(l_counter) := l_location_id;
5151
5152 if earning_exists = 1 then
5153 for i in 1..l_counter loop
5154
5155 l_context_id := l_taxunit_context_id;
5156 l_context_val := l_tax_unit_id;
5157
5158 ff_archive_api.create_archive_item(
5159 -- p_validate => 'TRUE'
5160 p_archive_item_id => l_archive_item_id
5161 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
5162 ,p_archive_value => l_user_entity_value_tab(i)
5163 ,p_archive_type => 'AAP'
5164 ,p_action_id => p_assactid
5165 ,p_legislation_code => 'CA'
5166 ,p_object_version_number => l_object_version_number
5167 ,p_some_warning => l_some_warning
5168 );
5169 end loop;
5170 end if;
5171
5172 begin
5173 hr_utility.trace('selecting address');
5174
5175 select addr.address_line1,
5176 addr.address_line2,
5177 addr.address_line3,
5178 addr.town_or_city,
5179 decode(addr.country,'CA',addr.region_1,'US',addr.region_2,' '),
5180 replace(addr.postal_code,' '),
5181 addr.telephone_number_1,
5182 country.territory_code
5183 into l_address_line1,
5184 l_address_line2,
5185 l_address_line3,
5186 l_town_or_city,
5187 l_province_code,
5188 l_postal_code,
5189 l_telephone_number,
5190 l_country_code
5191 from per_addresses addr,
5192 fnd_territories_vl country
5193 where addr.person_id = l_person_id
5194 and addr.primary_flag = 'Y'
5195 and p_effective_date
5196 between nvl(addr.date_from,p_effective_date)
5197 and nvl(addr.date_to, p_effective_date)
5198 and country.territory_code = addr.country;
5199 exception
5200 when no_data_found then
5201 l_address_line1 := null;
5202 l_address_line2 := null;
5203 l_address_line3 := null;
5204 l_address_line4 := null;
5205 l_town_or_city := null;
5206 l_province_code := null;
5207 l_postal_code := null;
5208 l_telephone_number := null;
5209 l_country_code := null;
5210 end;
5211
5212 hr_utility.trace('selected address');
5213
5214 l_counter := 0;
5215 l_counter := l_counter + 1;
5216 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
5217 l_user_entity_value_tab(l_counter) := l_address_line1;
5218
5219 l_counter := l_counter + 1;
5220 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE2';
5221 l_user_entity_value_tab(l_counter) := l_address_line2;
5222
5223 l_counter := l_counter + 1;
5224 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE3';
5225 l_user_entity_value_tab(l_counter) := l_address_line3;
5226
5227 l_counter := l_counter + 1;
5228 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE4';
5229 l_user_entity_value_tab(l_counter) := l_address_line4;
5230
5231 l_counter := l_counter + 1;
5232 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_CITY';
5233 l_user_entity_value_tab(l_counter) := l_town_or_city;
5234
5235 l_counter := l_counter + 1;
5236 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_PROVINCE';
5237 l_user_entity_value_tab(l_counter) := l_province_code;
5238
5239 l_counter := l_counter + 1;
5240 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_COUNTRY';
5241 l_user_entity_value_tab(l_counter) := l_country_code;
5242
5243 l_counter := l_counter + 1;
5244 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_POSTAL_CODE';
5245 l_user_entity_value_tab(l_counter) := l_postal_code;
5246
5247 /*
5248 l_counter := l_counter + 1;
5249 l_user_entity_value_tab(l_counter) := 'CAEOY_EMPLOYEE_BUSINESS_NUMBER';
5250 l_user_entity_name_tab(l_counter) := 'To be decided';
5251 */
5252 if earning_exists = 1 then
5253 for i in 1..l_counter loop
5254
5255 l_context_id := l_taxunit_context_id;
5256 l_context_val := l_tax_unit_id;
5257
5258 hr_utility.trace('archiving address');
5259 ff_archive_api.create_archive_item(
5260 p_archive_item_id => l_archive_item_id
5261 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
5262 ,p_archive_value => l_user_entity_value_tab(i)
5263 ,p_archive_type => 'AAP'
5264 ,p_action_id => p_assactid
5265 ,p_legislation_code => 'CA'
5266 ,p_object_version_number => l_object_version_number
5267 ,p_some_warning => l_some_warning
5268 );
5269 hr_utility.trace('archived address');
5270 end loop;
5271 end if;
5272 hr_utility.trace('end of eoy_archive_data');
5273 l_step := 37;
5274
5275 -- Moidification for bug 6456662 starts here.
5276 /*
5277 Added below piece of code for bug 10247374 and calling the same
5278 before CAEOY_T4A_AMENDMENT_FLAG is archived.
5279
5280 */
5281
5282
5283 IF (lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP'
5284 or
5285 to_number(to_char(l_year_end,'YYYY')) >= 2010
5286 ) then
5287
5288 hr_utility.trace('Calling new procedure for archiving Other Info Amts.');
5289
5290 eoy_archive_data_new_format(p_assactid , p_effective_date,l_negative_balance_exists );
5291
5292 ELSE
5293 -- Archive the negative balance flag
5294 -- Archive l_negative_balance_exists if new procedure is not getting called
5295
5296 ff_archive_api.create_archive_item(
5297 p_archive_item_id => l_archive_item_id
5298 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_NEGATIVE_BALANCE_EXISTS')
5299 ,p_archive_value => l_negative_balance_exists
5300 ,p_archive_type => 'AAP'
5301 ,p_action_id => p_assactid
5302 ,p_legislation_code => 'CA'
5303 ,p_object_version_number => l_object_version_number
5304 ,p_context_name1 => 'TAX_UNIT_ID'
5305 ,p_context1 => l_tax_unit_id
5306 ,p_some_warning => l_some_warning
5307 );
5308
5309 END IF;
5310
5311
5312 -- Moidification for bug 6456662 ends here.
5313
5314 -- Federal YE Amendment Pre-Process Validation (T4A Amendment Archiver code)
5315
5316 Begin
5317
5318 hr_utility.trace('Started Federal YE Amendment PP Validation ');
5319
5320 -- This query is called above, before archiving T4A_BOX28 etc.,
5321
5322 /*
5323 select effective_date,
5324 report_type
5325 into ld_fapp_effective_date,
5326 lv_fapp_report_type
5327 from pay_payroll_actions
5328 where payroll_action_id = l_payroll_action_id;
5329 */
5330
5331
5332 hr_utility.trace('Fed Amend Pre-Process Pactid :'||
5333 to_char(l_payroll_action_id));
5334 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
5335
5336 if lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP' then
5337
5338 hr_utility.trace('Archiving CAEOY_T4A_AMEND_PP for payroll_action_id and report_type:'||
5339 l_payroll_action_id||' and '||lv_fapp_report_type);
5340
5341 begin
5342
5343 open c_get_fapp_locked_action_id(p_assactid);
5344 fetch c_get_fapp_locked_action_id
5345 into ln_fapp_locked_action_id;
5346
5347 close c_get_fapp_locked_action_id;
5348
5349 hr_utility.trace('T4A Amend PP Action ID : '||to_char(p_assactid));
5350 hr_utility.trace('ln_fapp_locked_action_id :'||
5351 to_char(ln_fapp_locked_action_id));
5352
5353 open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
5354 fetch c_get_fapp_lkd_actid_rtype
5355 into lv_fapp_locked_actid_reptype;
5356
5357 close c_get_fapp_lkd_actid_rtype;
5358
5359 hr_utility.trace('lv_fapp_locked_actid_reptype :'||
5360 lv_fapp_locked_actid_reptype);
5361
5362 lv_fapp_flag := compare_archive_data(p_assactid,
5363 ln_fapp_locked_action_id);
5364
5365 if lv_fapp_flag = 'Y' then
5366
5367 hr_utility.trace('Archiving T4A Amendment Flag is : ' || lv_fapp_flag);
5368
5369 ff_archive_api.create_archive_item(
5370 p_archive_item_id => l_archive_item_id
5371 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_AMENDMENT_FLAG')
5372 ,p_archive_value => lv_fapp_flag
5373 ,p_archive_type => 'AAP'
5374 ,p_action_id => p_assactid
5375 ,p_legislation_code => 'CA'
5376 ,p_object_version_number => l_object_version_number
5377 ,p_context_name1 => 'TAX_UNIT_ID'
5378 ,p_context1 => l_tax_unit_id
5379 ,p_some_warning => l_some_warning
5380 );
5381
5382 end if;
5383
5384 end; -- report_type validation
5385
5386 end if; -- report type validation for FAPP
5387 hr_utility.trace('End of Federal YE Amendment PP Validation');
5388
5389 exception when no_data_found then
5390 hr_utility.trace('Report type not found for given Payroll_action ');
5391 null;
5392 end;
5393
5394 -- End of Federal YE Amendment Pre-Process Validation
5395
5396 -- Moidification for bug 6456662 starts here.
5397 /*
5398 Commented below piece of code for bug 10247374 and calling the same
5399 before CAEOY_T4A_AMENDMENT_FLAG is archived.
5400
5401 */
5402
5403
5404 /* IF (lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP'
5405 or
5406 to_number(to_char(l_year_end,'YYYY')) >= 2010
5407 ) then
5408
5409 hr_utility.trace('Calling new procedure for archiving Other Info Amts.');
5410
5411 eoy_archive_data_new_format(p_assactid , p_effective_date,l_negative_balance_exists );
5412
5413 ELSE
5414 -- Archive the negative balance flag
5415 -- Archive l_negative_balance_exists if new procedure is not getting called
5416
5417 ff_archive_api.create_archive_item(
5418 p_archive_item_id => l_archive_item_id
5419 ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_NEGATIVE_BALANCE_EXISTS')
5420 ,p_archive_value => l_negative_balance_exists
5421 ,p_archive_type => 'AAP'
5422 ,p_action_id => p_assactid
5423 ,p_legislation_code => 'CA'
5424 ,p_object_version_number => l_object_version_number
5425 ,p_context_name1 => 'TAX_UNIT_ID'
5426 ,p_context1 => l_tax_unit_id
5427 ,p_some_warning => l_some_warning
5428 );
5429
5430 END IF;
5431 */
5432
5433 -- Moidification for bug 6456662 ends here.
5434
5435 end eoy_archive_data;
5436
5437
5438 /* Name : eoy_range_cursor
5439 Purpose : This returns the select statement that is used to created the
5440 range rows for the Year End Pre-Process.
5441 Arguments :
5442 Notes :
5443 */
5444
5445 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
5446
5447 l_legislative_parameters varchar2(240);
5448 l_eoy_tax_unit_id number;
5449 l_transmitter_gre_id number;
5450 l_archive boolean:= FALSE;
5451 l_business_group number;
5452 l_year_start date;
5453 l_year_end date;
5454
5455 begin
5456
5457 select legislative_parameters,
5458 trunc(effective_date,'Y'),
5459 effective_date,
5460 business_group_id
5461 into l_legislative_parameters,
5462 l_year_start,
5463 l_year_end,
5464 l_business_group
5465 from pay_payroll_actions
5466 where payroll_action_id = pactid;
5467
5468 hr_utility.trace('legislative prameter is '|| l_legislative_parameters);
5469
5470 l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
5471
5472 select org_information11
5473 into l_transmitter_gre_id
5474 from hr_organization_information
5475 where organization_id = l_eoy_tax_unit_id
5476 and org_information_context = 'Canada Employer Identification'
5477 and org_information5 in ('T4A/RL1','T4A/RL2');
5478
5479 hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
5480 hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
5481
5482 if l_eoy_tax_unit_id <> -99999 then
5483
5484 sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
5485 PPA PAY_PAYROLL_ACTIONS_N51,
5486 PAA PAY_ASSIGNMENT_ACTIONS_N50,
5487 ASG PER_ASSIGNMENTS_F_PK,
5488 PPA1 PAY_PAYROLL_ACTIONS_PK)
5489 USE_NL(PPY, PPA, PAA, ASG, PPA1) */
5490 distinct asg.person_id
5491 from pay_all_payrolls_f ppy,
5492 pay_payroll_actions ppa,
5493 pay_assignment_actions paa,
5494 per_all_assignments_f asg,
5495 pay_payroll_actions ppa1
5496 where ppa1.payroll_action_id = :payroll_action_id
5497 and ppa.effective_date between
5498 fnd_date.canonical_to_date('''||
5499 fnd_date.date_to_canonical(l_year_start)||''') and
5500 fnd_date.canonical_to_date('''||
5501 fnd_date.date_to_canonical(l_year_end)||''')
5502 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
5503 and ppa.action_status = ''C''
5504 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
5505 and ppa.payroll_action_id = paa.payroll_action_id
5506 and paa.tax_unit_id = '|| to_char(l_eoy_tax_unit_id)||'
5507 and paa.action_status = ''C''
5508 and paa.assignment_id = asg.assignment_id
5509 and ppa.business_group_id = asg.business_group_id + 0
5510 and ppa.effective_date between asg.effective_start_date
5511 and asg.effective_end_date
5512 and asg.assignment_type = ''E''
5513 and ppa.payroll_id = ppy.payroll_id
5514 and ppy.business_group_id = '||to_char(l_business_group)||'
5515 order by asg.person_id';
5516
5517 l_archive := chk_gre_archive(pactid);
5518
5519 if g_archive_flag = 'N' then
5520 hr_utility.trace('eoy_range_cursor archiving employer data');
5521
5522 -- now the archiver has provision for archiving payroll_action_level data .
5523 -- So make use of that
5524
5525 hr_utility.trace('eoy_range_cursor archiving employer data');
5526
5527 eoy_archive_gre_data(pactid,
5528 l_eoy_tax_unit_id,
5529 l_transmitter_gre_id);
5530 end if;
5531
5532 end if;
5533
5534 end eoy_range_cursor;
5535
5536 end pay_ca_t4aeoy_archive;