[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_RL1_ARCHIVE
Source
1 package body pay_ca_eoy_rl1_archive as
2 /* $Header: pycarlar.pkb 120.41.12020000.6 2012/12/28 12:06:59 sbachu ship $ */
3
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6 ******************************************************************
7 * *
8 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
9 * Chertsey, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24
25 Description : Package and procedure to build sql for payroll processes.
26
27 Change List
28
29 Date Name Vers Bug No Description
30
31 14-JAN-2000 M.Mukherjee 110.0 Created
32 18-JAN-2000 M.Mukherjee 110.1 Taken out trace_on and archiving
33 QBN at PA level
34 18-JAN-2000 M.Mukherjee 110.2 Archiving taxation year
35 21-JAN-2000 M.Mukherjee 110.3 Archiving BOXO balances
36 27-JAN-2000 M.Mukherjee 110.4 Put the check for not to take
37 put T4A earnings in Gross Earnings
38 which is Box A
39 31-JAN-2000 M.Mukherjee 110.4 If condition for T4A/Gross earning
40 combinations are written differently
41 31-JAN-2000 M.Mukherjee 110.5 Corrected condition so that it does
42 not archive employees who does not
43 have payroll runs for that QC_ID
44 02-FEB-2000 M.Mukherjee 110.6 Taking Quebec ID no 16 chars
45 every where and stopped archiving
46 non quebec employees
47 16-MAY-2000 M.Mukherjee 115.3 Changed the report_type to RL1
48 10-JUL-2000 V.PANDYA 115.4 Added Existance of record in
49 PAY_ACTION_CONTEXT for Juridiction Code
50 for QC in Range Cursor and Action
51 Creation query so it takes only those
52 employees who have been paid in Quebec.
53 18-AUG-2000 M.Mukherjee 115.5 Added RL1_SLIP_NUMBER archiving
54 31-AUG-2000 M.Mukherjee 115.6,115.7 Added footnote,CPP, BOXO archiving
55 18-SEP-2000 V.PANDYA 115.8 Remove assignment_id condition to get
56 asg.actid for more asgid.
57 20-SEP-2000 mmukherj 115.11 Corrected footnote archiving logic
58 20-SEP-2000 V.PANDYA 115.12 Corrected footnote archiving logic
59 18-OCT-2000 M.Mukherjee 115.13-15 Corrected footnote archiving logic and
60 slip number archiving logic.
61 20-OCT-2000 M.Mukherjee 115.16-17 Corrected slip number archiving logic.
62 30-OCT-2000 M.Mukherjee 115.18 Corrected slip number archiving logic
63 instead of global variable, a sequence
64 will be used , because global variable
65 is not effective across diff sessions
66 so it will not work in a multi threaded
67 environment.
68 30-OCT-2000 M.Mukherjee 115.19 balance_feeds are being checked for
69 that business group id.Bug1482190
70 06-DEC-2000 M.Mukherjee 115.20 added to_char(hoi.organization_id) in
71 c_eoy_qbin cursor, it was returning
72 invalid_number.
73 08-DEC-2000 M.Mukherjee 115.21 added business group in the select
74 queries, otherwise it will fetch
75 duplicate data.
76 08-DEC-2000 M.Mukherjee 115.22 changed comments double dash to
77 slash/star,
78 otherwise adchkdrv will fail
79 08-DEC-2000 VPandya 115.23,26 Trying to solve PI on driver
80 says to replace dashes
81 12-DEC-2000 MMukherjee 115.27 Added parameter l_has_been_paid to
82 avoid archiving 0 salary records.
83 13-DEC-2000 MMukherjee 115.28 Stopped assignment action creation
84 if the employee has not been paid
85 anything in that year, even though
86 there is payroll run.
87 29-DEC-2000 P.Ganguly 115.29 Added a check if Taxable Benefits
88 for Federal is present then subtract
89 it from the gross earnings.
90 08-NOV-2001 VPandya 115.30 Added QPP Basic Exemption, QPP Exempt
91 Earnings. Archiving RL1 NonBox Footnotes
92 in pay_action_information table.
93 10-NOV-2001 vpandya 115.31 Added set veify off at top as per GSCC.
94 12-NOV-2001 vpandya 115.32 Added dbdrv line.
95 27-DEC-2001 vpandya 115.33 Archiving new DBI
96 CAEOY_EMPLOYEE_DATE_OF_BIRTH
97 08-Jan-2002 vpandya 115.34 Archiving new DBI
98 CAEOY_EMPLOYEE_HIRE_DATE
99 02-Jul-2002 vpandya 115.36 Commented out below clause while getting
100 max-assignment action id query
101 AND pac1.assignment_id = l_asgid
102 01-Aug-2002 vpandya 115.37 Indention of archive_data and not include
103 status indian(BOXR) in to Gross Earnings
104 (BOXA).
105 10-Aug-2002 mmukherj 115.38 Bugfix for #2458533. The cursor
106 employer_info has been changed so that
107 it checks the business_group_id.
108 16-Aug-2002 vpandya 115.39 Bugfix for 2192914:archiving termination
109 date.
110 Archiving transmitter name instead of
111 transmitter org id in DBI
112 CAEOY_RL1_TRANSMITTER_NAME(ref.2192914)
113 21-Aug-2002 vpandya 115.40 Bugfix for 2449408:archiving DBI
114 CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE
115 Changed cursor employer_info, added
116 column org_information19 for Archiving
117 Accounting Resource Language, also given
118 alias to all information columns.
119 06-Oct-2002 vpandya 115.41 Changed archiver to archive Box-O
120 footnote.
121 08-Oct-2002 vpandya 115.43 Initializing variables l_footnote_amount
122 and l_footnote_amount_ue to avoid
123 duplicate archiving of footnotes.
124 22-Oct-2002 vpandya 115.44 Bug 2681250: changed cursor c_get_addr
125 of eoy_archive_data. If country is CA
126 take data from region_1 to get province
127 code and if it is US take data from
128 region_2 to get state code.
129 02-DEC-2002 vpandya 115.45 Added nocopy with out parameter
130 as per GSCC.
131 06-DEC-2002 vpandya 115.46 Bug 2698320,RL1 BOX-O codes RA to RZ
132 should be excluded from BOX A on the RL1.
133 Done using ln_boxo_exclude_from_boxa.
134 11-DEC-2002 vpandya 115.47 Bug 2698320,not excluding Box-O amount of
135 T4A/RL1 GRE from Box-A. Put this cond.
136 getting balance in ln_balance_value first
137 and summing up after for the same balance
138 for different GREs.
139 27-AUG-2003 ssouresr 115.49 If the balance 'RL1 No Gross Earnings' is
140 non zero then archiving takes place even
141 if Gross Earnings is zero.
142 Also the balance 'RL1 Non Taxable Earnings'
143 is deducted from Gross Earnings.
144 18-Sep-2003 vpandya 115.50 Archiving dates in canonical format
145 (YYYY/MM/DD HH:MI:SS) using
146 fnd_date.date_to_canonical_to_date
147 instead of using to_char with default
148 format to fix gscc date conversion error.
149 21-OCT-2003 ssouresr 115.51 Added RL1 Amendment Archiving logic
150 in eoy_archive_data procedure. Also
151 added new local function
152 compare_archive_data used for RL1
153 Amendment Archiver.
154 The organization_id of the Prov Reporting
155 Est will now be used instead of the QIN
156 04-NOV-2003 ssouresr 115.52 Converted the pre printed form number
157 select to a cursor as more than one
158 record can be returned
159 10-NOV-2003 ssouresr 115.53 Archiving pre printed form number both
160 for RL1 and RL1 Amendment. This will
161 ensure that the function
162 compare_archive_data compares the
163 correct data.
164 12-NOV-2003 ssouresr 115.54 Modified the function
165 compare_archive_data so that if the
166 number of archived items to be compared
167 is different then the amendment flag is
168 set to Y without checking all the
169 individual data records.
170 21-FEB-2004 pganguly 115.55 Fixed bug# 3459723. Changed the cursor
171 c_get_asg_id so that it picks
172 assignment of type 'E' only.
173 02-APR-2004 ssattini 115.56 11510 Changes to fix bug#3356533.
174 Added new cursor c_get_max_asg_act_id
175 in action_creation procedure. Modified
176 cursor c_all_gres_for_person and added
177 two new cursors c_get_max_asgactid_jd,
178 c_get_max_asgactid in eoy_archive_data
179 procedure.
180 23-APR-2004 ssouresr 115.57 Modified the cursor cur_non_box_mesg to
181 stop returning duplicate nonbox footnotes
182 06-JUN-2004 ssattini 115.60 Modified the cursors
183 c_get_max_asg_act_id,
184 c_get_max_asgactid_jd and
185 c_get_max_asgactid to get max asgact_id
186 based on person_id. Bug fix bug#3638928.
187 05-AUG-2004 ssouresr 115.61 Footnote codes for BoxQ can now be
188 archived Also added check to make sure
189 an appropriate error message is written
190 to the log if no transmitter has been
191 specified for the PRE. Bug#3353450.
192 05-AUG-2004 ssattini 115.62 Modified the cursor cur_non_box_mesg
193 to archive the balance adjustments
194 for Non-Box footnotes. Fix bug#3641353.
195 10-AUG-2004 ssouresr 115.63 Added the negative balance flag bug#3311402
196 Also modified the non box footnote logic
197 so that the amounts for identical footnote
198 codes are summed up bug#3641308
199 01-SEP-2004 ssouresr 115.64 BoxO can now have a negative balance
200 Bug 3863016, previously negative values
201 for this box were being ignored
202 02-SEP-2004 ssouresr 115.65 Changed to use the function get_parameter
203 to retrieve PRE_ORGANIZATION_ID
204 04-OCT-2004 ssouresr 115.66 The negative balance flag is archived as Y
205 when either a box or nonbox footnote is negative
206 05-NOV-2004 ssouresr 115.67 RL1 No Gross Earnings needs to be retrieved
207 across GREs
208 08-NOV-2004 ssouresr 115.68 All footnotes were reviewed.
209 BOXL and BOXO RW do not require any footnotes.
210 BOXO RX and BOX RY are not valid anymore. Also
211 BOXR has been changed to only have footnote
212 code 14 (Income from an office or employment)
213 17-NOV-2004 ssouresr 115.70 BoxO Code is now archived correctly
214 18-NOV-2004 ssouresr 115.71 Added BOXO_RR to list of balances to archive
215 19-NOV-2004 ssouresr 115.72 Footnotes for Gross Earnings(BOXA) are now archived
216 and archiving of BOXO_RZ has been removed
217 28-NOV-2004 ssouresr 115.73 Modified the cursor c_footnote_info to only return
218 RL1 footnotes, was previously returning RL2 footnotes
219 as well.
220 28-NOV-2004 ssouresr 115.74 Added date range to the latest assignment action cursor
221 29-NOV-2004 ssouresr 115.75 RL1 footnotes should be archived with Jurisdiction QC
222 30-NOV-2004 ssouresr 115.76 Archiving CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD for BoxG
223 02-FEB-2005 ssouresr 115.77 NonBox Footnotes with a value of 0 are not archived
224 04-MAR-2005 ssouresr 115.78 The archiver uses a new NonBox Footnote Element which
225 has a Jurisdiction input value from the beginning of 2006
226 26-APR-2005 ssouresr 115.79 The archiver will now recognize amendments made
227 only to non box footnotes
228 05-AUG-2005 saurgupt 115.80 Bug 4517693: Added Address_line3 for RL1 archiver.
229 08-AUG-2005 mmukherj 115.81 The procedure eoy_archinit has been
230 modified to set the minimum chunk
231 no, which is required to re archive
232 the data while retrying the Archiver
233 in the payroll action level.
234 Bugfix: #4525642
235 31-AUG-2005 ssouresr 115.82 New RL1 Nonbox footnote for Taxable Benefits without pay
236 27-SEP-2005 ssouresr 115.83 Corrected footnote condition in the function
237 compare_archive_data
238 10-NOV-2005 ssouresr 115.84 Added Footnote for BOXO RN
239 07-FEB-2006 ssouresr 115.85 Modified range cursor and main action creation
240 query to remove the table hr_soft_coding_keyflex
241 13-Apr-2006 ssmukher 115.86 Modified the sqlstr statement in eoy_range_cursor
242 procedure for Bug #5120627 fix
243 07-Aug-2006 ydevi 115.87 5096509 Archiver archives two PPIP EE Withheld
244 and PPIP EE Taxable into database itens
245 CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD
246 and CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD
247 respectively
248 18-AUG-2006 meshah 115.88 5202869 For performance reason changed the
249 query to remove per_people_f and
250 also disabled some indexes. With this
251 change the cost of the query
252 increases however now the path taken
253 is now more correct. Cursor
254 c_eoy_qbin has been changed.
255 28-AUG-2006 meshah 115.89 5495704 the way indexes were disabled has
256 been changed from using +0 to ||
257 16-Nov-2006 ydevi 115.90 5159150 archiving RL1_BOXV and RL1_BOXW
258 into db item CAEOY_RL1_BOXV_PER_JD_YTD
259 and CAEOY_RL1_BOXW_PER_JD_YTD
260 21-Dec-2006 ssmukher 115.91 5706335 Archiving BoxI value into DBI
261 CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD
262 05-Feb-2007 meshah 115.92 5768390 Removed the if condition that would
263 not populate boxA when the GRE type
264 is T4A/RL1
265 21-Aug-2007 amigarg 115.93 5558604 Added date track and enabled flag
266 condtion in c_footnote_info
267 21-Sep-2007 amigarg 115.95 6440125 added date track condition in employee
268 archiving
269 10-Jan-2008 sapalani 115.96 6525899 Added check to not to archive the
270 RL1_BOXO_AMOUNT_RW balance from 2007
271 13-Nov-2008 sapalani 115.97 7555410 Added check to not to archive the
272 RL1_BOXO_AMOUNT_RF balance from 2008.
273 Added code to archive balance
274 RL1_BOXO_AMOUNT_RX starting from 2008.
275 25-Nov-2008 sapalani 115.98 7555410 Modified cursor cur_non_box_mesg to
276 pick only active footnotes.
277 25-Mar-2009 sapalani 115.100 8366352 Added new cursor c_non_box_lookup to
278 fetch and archive only active non box
279 footnotes. Removed this logic from
280 cursor cur_non_box_mesg.
281 10-Apr-2009 sapalani 115.101 6768167 Added function gen_rl1_pdf_seq to
282 generate sequence number for RL1 PDF.
283 The generated sequence numeber is
284 archived in eoy_archive_data.
285 08-May-2009 sapalani 115.102 8500723 Added function getnext_seq_num to
286 calculate check digit for PDF sequence.
287 In function gen_rl1_pdf_seq added call
288 to ff_archive_api.create_archive_item.
289 This archives CAEOY_RL1_PDF_SEQ_NUMBER
290 when PDF is run for a period and
291 archive item doesn't exist in that
292 period.
293 17-Aug-2009 sapalani 115.103 8732218 In function gen_rl1_pdf_seq, replaced
294 call to ff_archive_api.update_archive
295 _item with direct update statement.
296 09-Sep-2009 sapalani 115.104 6853279 Added order by clause to cursor
297 cur_non_box_mesg.
298 24-Nov-2009 sneelapa 115.105 9135372 Modified eoy_archive_data procedure to
299 archive data for BOX O new codes.
300 07-Dec-2009 sneelapa 115.106 9177694 Modified IF Condition to END of
301 c_non_box_lookup CURSOR LOOP.
302 18-Dec-2009 aneghosh 115.107 9215185 Modified cursor c_get_emp_rl1box_data
303 to ignore 'CAEOY_RL1_PDF_SEQ_NUMBER'
304 while comparing the data across two
305 archivers to set 'CAEOY_RL1_AMENDMENT_FLAG'.
306 22-Feb-2010 sneelapa 115.109 9184985 Obsoleted 115.108 version, as issue was found
307 during QA testing. Modified current package
308 with 115.107 as base.
309 Modified eoy_archive_data procedure.
310 Modified cursor cur_non_box_mesg
311 Added run_paa.assignment_id = arch_paa.assignment_id
312 condition for improving performance.
313 22-Feb-2010 sneelapa 115.110 9184985 Modified eoy_archive_data procedure.
314 Modified cursor cur_non_box_mesg
315 Added cp_start_date parameter and modified WHERE
316 conditions to use this date and avoid TO_CHAR function.
317 TO_CHAR function was hindering the performance.
318 12-Jan-2011 sneelapa 115.111 11065493 Modified cursor cur_non_box_mesg to
319 consider Reversal of Quickpay/Payroll Runs.
320
321 10-Feb-2011 sneelapa 115.112 11654691 Modified eoy_archive_data procedure to
322 archive slip_number as 9 character
323 if slip number is 12, lpad will be done
324 with 7 ZEROs to make it 9 character.
325
326 29-Aug-2011 sneelapa 115.113 10399514 Introduced new CURSOR c_eoy_qbin_range
327 it will be called in place of
328 c_eoy_qbin CURSOR, if RANGE_PERSON_ID
329 is enabled.
330 12-SEP-2011 rgottipa 115.114 11694701 Introduced new cursor c_get_rl1_pdf_slip
331 It will be used to get Starting and
332 Ending slip numbers.
333 14-SEP-2011 rgottipa 115.115 11694701 Romoved the new cursor c_get_rl1_pdf_slip.
334 Trying to fetch Starting and Ending slip
335 numbers from hr_organization_information.
336 19-SEP-2011 rgottipa 115.116 12996280 Handling no_data_found exception
337 while capturing starting and ending
338 slip numbers.
339 08-Dec-2011 rgottipa 115.117 13360872 Changes done to support new database
340 codes
341 09-Dec-2011 rgottipa 115.118 13360872 Archiving 'Taxable benefit paid in kind'
342 foot note from year 2011
343 29-Dec-2011 rgottipa 115.120 13497300 Created new procedure
344 eoy_archive_further_info to archive
345 new further information balances
346 07-May-2012 rgottipa 115.121 14030417 If CPP amount is negative then that
347 Emp should report in error report.
348 13-Sep-2012 sbachu 115.122 8895534 Added CAEOY_CPP_EE_WITHHELD_PER_YTD
349 also as a criteria to set amendment flag.
350 20-Nov-2012 sbachu 115.123 14701466 RL1 EI and RL1 PDF changes for 2012.
351 14616599
352 13564765
353 28-Nov-2012 sbachu 115.124 15914635 G-2 SHOULD BE SUM OF ALL NON-QC
354 PROVINCE VALUE OF T4 BOX 26
355 28-Dec-2012 sbachu 115.125 15886329 CHANGE IN REPORTING OF QPP PENSIONABLE
356 EARNINGS
357 */
358
359 sqwl_range varchar2(4000);
360 eoy_gre_range varchar2(4000);
361 eoy_all_qbin varchar2(4000);
362
363
364 /* Name : bal_db_item
365 Purpose : Given the name of a balance DB item as would be seen in a fast formula
366 it returns the defined_balance_id of the balance it represents.
367 Arguments :
368 Notes : A defined balance_id is required by the PLSQL balance function.
369 */
370
371 function bal_db_item
372 (
373 p_db_item_name varchar2
374 ) return number is
375
376 /* Get the defined_balance_id for the specified balance DB item. */
377
378 cursor csr_defined_balance is
379 select to_number(UE.creator_id)
380 from ff_user_entities UE,
381 ff_database_items DI
382 where DI.user_name = p_db_item_name
383 and UE.user_entity_id = DI.user_entity_id
384 and Ue.creator_type = 'B';
385
386 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
387
388 begin
389
390 open csr_defined_balance;
391 fetch csr_defined_balance into l_defined_balance_id;
392 if csr_defined_balance%notfound then
393 close csr_defined_balance;
394 raise hr_utility.hr_error;
395 else
396 close csr_defined_balance;
397 end if;
398
399 return (l_defined_balance_id);
400
401 end bal_db_item;
402
403
404 /* Name : get_dates
405 Purpose : The dates are dependent on the report being run
406 For T4 it is year end dates.
407
408 Arguments :
409 Notes :
410 */
411
412 procedure get_dates
413 (
414 p_report_type in varchar2,
415 p_effective_date in date,
416 p_period_end in out nocopy date,
417 p_quarter_start in out nocopy date,
418 p_quarter_end in out nocopy date,
419 p_year_start in out nocopy date,
420 p_year_end in out nocopy date
421 ) is
422 begin
423
424 if p_report_type = 'RL1' then
425
426 /* Year End Pre-process is a yearly process where the identifier
427 indicates the year eg. 1998. The expected values for the example
428 should be
429 p_period_end 31-DEC-1998
430 p_quarter_start 01-OCT-1998
431 p_quarter_end 31-DEC-1998
432 p_year_start 01-JAN-1998
433 p_year_end 31-DEC-1998
434 */
435
436 p_period_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
437 p_quarter_start := trunc(p_period_end, 'Q');
438 p_quarter_end := p_period_end;
439
440 /* For EOY */
441
442 end if;
443
444 p_year_start := trunc(p_effective_date, 'Y');
445 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
446
447 end get_dates;
448
449
450 /* Name : get_selection_information
451 Purpose : Returns information used in the selection of people to be reported on.
452 Arguments :
453
454 The following values are returned :
455
456 p_period_start - The start of the period over which to select
457 the people.
458 p_period_end - The end of the period over which to select
459 the people.
460 p_defined_balance_id - The balance which must be non zero for each
461 person to be included in the report.
462 p_group_by_gre - should the people be grouped by GRE.
463 p_group_by_medicare - Should the people ,be grouped by medicare
464 within GRE NB. this is not currently supported.
465 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
466 the testing of the balance.
467 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
468 for the testing of the balance.
469
470 Notes : This routine provides a way of coding explicit rules for
471 individual reports where they are different from the
472 standard selection criteria for the report type ie. in
473 NY state the selection of people in the 4th quarter is
474 different from the first 3.
475 */
476
477 procedure get_selection_information
478 (
479
480 /* Identifies the type of report, the authority for which it is being run,
481 and the period being reported. */
482 p_report_type varchar2,
483 p_quarter_start date,
484 p_quarter_end date,
485 p_year_start date,
486 p_year_end date,
487 /* Information returned is used to control the selection of people to
488 report on. */
489 p_period_start in out nocopy date,
490 p_period_end in out nocopy date,
491 p_defined_balance_id in out nocopy number,
492 p_group_by_gre in out nocopy boolean,
493 p_group_by_medicare in out nocopy boolean,
494 p_tax_unit_context in out nocopy boolean,
495 p_jurisdiction_context in out nocopy boolean
496 ) is
497
498 begin
499
500 /* Depending on the report being processed, derive all the information
501 required to be able to select the people to report on. */
502
503 if p_report_type = 'RL1' then
504
505 /* Default settings for Year End Preprocess. */
506
507 hr_utility.trace('in getting selection information ');
508 p_period_start := p_year_start;
509 p_period_end := p_year_end;
510 /* p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'); */
511 p_defined_balance_id := 0;
512 p_group_by_gre := FALSE;
513 p_group_by_medicare := FALSE;
514 p_tax_unit_context := FALSE;
515 p_jurisdiction_context := FALSE;
516
517 /* For EOY end */
518
519 /* An invalid report type has been passed so fail. */
520
521 else
522 hr_utility.trace('in error of getting selection information ');
523
524 raise hr_utility.hr_error;
525
526 end if;
527
528 end get_selection_information;
529
530
531 /* Name : lookup_jurisdiction_code
532 Purpose : Given a state code ie. AL it returns the jurisdiction code that
533 represents that state.
534 Arguments :
535 Notes :
536 */
537
538 function lookup_jurisdiction_code
539 (
540 p_state varchar2
541 ) return varchar2 is
542
543 /* Get the jurisdiction_code for the specified state code. */
544
545 cursor csr_jurisdiction_code is
546 select SR.jurisdiction_code
547 from pay_state_rules SR
548 where SR.state_code = p_state;
549
550 l_jurisdiction_code pay_state_rules.jurisdiction_code%type;
551
552 begin
553
554 open csr_jurisdiction_code;
555 fetch csr_jurisdiction_code into l_jurisdiction_code;
556 if csr_jurisdiction_code%notfound then
557 close csr_jurisdiction_code;
558 raise hr_utility.hr_error;
559 else
560 close csr_jurisdiction_code;
561 end if;
562
563 return (l_jurisdiction_code);
564
565 end lookup_jurisdiction_code;
566
567
568 /*
569 Name : get_user_entity_id
570 Purpose : This gets the user_entity_id for a specific database item name.
571 Arguments : p_dbi_name > database item name.
572 Notes :
573 */
574
575 function get_user_entity_id (p_dbi_name in varchar2)
576 return number is
577 l_user_entity_id number;
578
579 begin
580
581 select user_entity_id
582 into l_user_entity_id
583 from ff_database_items
584 where user_name = p_dbi_name;
585
586 return l_user_entity_id;
587
588 exception
589 when others then
590 hr_utility.trace('Error while getting the user_entity_id'
591 || p_dbi_name);
592 raise hr_utility.hr_error;
593
594 end get_user_entity_id;
595
596
597 /*
598 Name : get_footnote_user_entity_id
599 Purpose : This gets the user_entity_id for a specific database item name.
600 and it does not raise error if the the user entity is not found
601 Arguments : p_dbi_name > database item name.
602 Notes :
603 */
604
605 function get_footnote_user_entity_id (p_dbi_name in varchar2)
606 return number is
607 l_user_entity_id number;
608
609 begin
610
611 if p_dbi_name is not null then
612 begin
613 select user_entity_id
614 into l_user_entity_id
615 from ff_database_items
616 where user_name = p_dbi_name;
617
618 return l_user_entity_id;
619
620 exception
621 when others then
622 hr_utility.trace('skipping the record because no dbi of name:'
623 || p_dbi_name);
624 return 0;
625 end;
626 end if;
627
628 return 0;
629
630 end get_footnote_user_entity_id;
631
632 /*
633 Name : compare_archive_data
634 Purpose : compares Provincial YEPP data and Provincial YE Amendment Data
635 Arguments : p_assignment_action_id -> Assignment_action_id
636 p_locked_action_id -> YEPP Assignment_action_id
637 p_jurisdiction -> Jurisdiction_code
638
639 Notes : Used for Provincial YE Amendment Pre-Process (YE-2003)
640 */
641
642 FUNCTION compare_archive_data(p_assignment_action_id in number,
643 p_locked_action_id in number,
644 p_jurisdiction in varchar2)
645 RETURN VARCHAR2 IS
646 TYPE act_info_rec IS RECORD
647 (archive_context1 number(25),
648 archive_ue_id number(25),
649 archive_value varchar2(240));
650
651 TYPE footnote_rec IS RECORD
652 (message varchar2(240)
653 ,value varchar2(240));
654
655 TYPE number_data_type_table IS TABLE OF NUMBER
656 INDEX BY BINARY_INTEGER;
657
658 TYPE action_info_table IS TABLE OF act_info_rec
659 INDEX BY BINARY_INTEGER;
660
661 TYPE footnote_table IS TABLE OF footnote_rec
662 INDEX BY BINARY_INTEGER;
663
664 ltr_amend_arch_data action_info_table;
665 ltr_yepp_arch_data action_info_table;
666 ltr_amend_emp_data action_info_table;
667 ltr_yepp_emp_data action_info_table;
668 ltr_emp_ue_id number_data_type_table;
669
670 ltr_amend_footnote footnote_table;
671 ltr_yepp_footnote footnote_table;
672 ln_yepp_footnote_count number;
673 ln_amend_footnote_count number;
674
675 cursor c_get_nonbox_footnote(cp_asg_act_id number) is
676 select action_information4,
677 action_information5
678 from pay_action_information
679 where action_context_id = cp_asg_act_id
680 and action_information_category = 'CA FOOTNOTES'
681 and action_context_type = 'AAP'
682 and action_information6 = 'RL1'
683 order by action_information4;
684
685 -- Cursor to get archived values based on Asg_act_id,jurisdiction
686 CURSOR c_get_emp_rl1box_data(cp_asg_act_id number) IS
687 SELECT fai1.context1,
688 fdi1.user_entity_id,
689 fai1.value
690 FROM ff_archive_items fai1,
691 ff_database_items fdi1,
692 ff_archive_item_contexts faic,
693 ff_contexts fc
694 WHERE fai1.user_entity_id = fdi1.user_entity_id
695 AND fai1.archive_item_id = faic.archive_item_id
696 AND fc.context_id = faic.context_id
697 AND fc.context_name = 'JURISDICTION_CODE'
698 AND faic.context = 'QC'
699 AND fai1.CONTEXT1 = cp_asg_act_id
700 AND fdi1.user_name NOT IN ('CAEOY_RL1_AMENDMENT_FLAG','CAEOY_RL1_PDF_SEQ_NUMBER') --For Bug 9215185
701 ORDER BY fdi1.user_name;
702
703 -- Cursor to get archived values based on Asg_act_id
704 CURSOR c_get_employee_data(cp_asg_act_id number,
705 cp_dbi_ue_id number) IS
706 SELECT fai.context1,fai.user_entity_id,fai.value
707 FROM ff_archive_items fai
708 WHERE fai.user_entity_id = cp_dbi_ue_id
709 AND fai.context1 = cp_asg_act_id;
710
711 i number;
712 j number;
713 ln_box number;
714 ln_amend_box number;
715
716 lv_flag varchar2(2):= 'N';
717
718 begin
719
720 /* Initialization Process */
721 if ltr_amend_arch_data.count > 0 then
722 ltr_amend_arch_data.delete;
723 end if;
724
725 if ltr_yepp_arch_data.count > 0 then
726 ltr_yepp_arch_data.delete;
727 end if;
728
729 if ltr_amend_emp_data.count > 0 then
730 ltr_amend_emp_data.delete;
731 end if;
732
733 if ltr_yepp_emp_data.count > 0 then
734 ltr_yepp_emp_data.delete;
735 end if;
736
737 if ltr_emp_ue_id.count > 0 then
738 ltr_emp_ue_id.delete;
739 end if;
740
741 if ltr_amend_footnote.count > 0 then
742 ltr_amend_footnote.delete;
743 end if;
744
745 if ltr_yepp_footnote.count > 0 then
746 ltr_yepp_footnote.delete;
747 end if;
748
749 j := 0;
750
751 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_INITIAL');
752
753 j := j+1;
754 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_FIRST_NAME');
755
756 j := j+1;
757 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_LAST_NAME');
758
759 j := j+1;
760 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_SIN');
761
762 j := j+1;
763 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_NUMBER');
764
765 j := j+1;
766 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE1');
767
768 j := j+1;
769 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE2');
770
771 j := j+1;
772 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_ADDRESS_LINE3');
773
774 j := j+1;
775 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_CITY');
776
777 j := j+1;
778 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_PROVINCE');
779
780 j := j+1;
781 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_COUNTRY');
782
783 j := j+1;
784 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_POSTAL_CODE');
785
786 j := j+1; /*8895534 sbachu*/
787 ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_CPP_EE_WITHHELD_PER_YTD');
788
789 /* Populate RL1 Amendment Box Data for an assignment_action */
790 open c_get_emp_rl1box_data(p_assignment_action_id);
791 hr_utility.trace('Populating RL1 Amendment Box Data ');
792 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
793 ln_amend_box := 0;
794 loop
795 fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
796 exit when c_get_emp_rl1box_data%NOTFOUND;
797
798 hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
799 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
800 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
801 hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
802 ln_amend_box := ln_amend_box + 1;
803 end loop;
804
805 close c_get_emp_rl1box_data;
806
807 /* Populate RL1 Amendment Employee Data for an assignment_action */
808 hr_utility.trace('Populating Amendment Employee Data ');
809 hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
810 for i in 0 .. j
811 loop
812 open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
813 fetch c_get_employee_data into ltr_amend_emp_data(i);
814
815 hr_utility.trace('I :'||to_char(i));
816 hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
817 hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
818 hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
819
820 close c_get_employee_data;
821 end loop;
822
823
824 /* Populate RL1 YEPP Box Data for an assignment_action */
825 open c_get_emp_rl1box_data(p_locked_action_id);
826 hr_utility.trace('Populating RL1 YEPP Box Data ');
827 hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
828 ln_box := 0;
829 loop
830 fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
831 exit when c_get_emp_rl1box_data%NOTFOUND;
832
833 hr_utility.trace('ln_box :'||to_char(ln_box));
834 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
835 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
836 hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
837 ln_box := ln_box + 1;
838 end loop;
839
840 close c_get_emp_rl1box_data;
841
842 /* Populate RL1 YEPP Employee Data for an assignment_action */
843 hr_utility.trace('Populating YEPP Employee Data ');
844 hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
845
846 for i in 0 .. j
847 loop
848 open c_get_employee_data(p_locked_action_id, ltr_emp_ue_id(i));
849 fetch c_get_employee_data into ltr_yepp_emp_data(i);
850 exit when c_get_employee_data%NOTFOUND;
851
852 hr_utility.trace('I :'||to_char(i));
853 hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
854 hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
855 hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
856
857 close c_get_employee_data;
858 end loop;
859
860 /* Populate RL1 Amendment Footnotes */
861 open c_get_nonbox_footnote(p_assignment_action_id);
862
863 hr_utility.trace('Populating RL1 Amendment Footnote ');
864
865 ln_amend_footnote_count := 0;
866 loop
867 fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
868 exit when c_get_nonbox_footnote%NOTFOUND;
869
870 hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
871 hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
872
873 ln_amend_footnote_count := ln_amend_footnote_count + 1;
874 end loop;
875
876 close c_get_nonbox_footnote;
877
878 /* Populate RL1 YEPP Footnotes */
879 open c_get_nonbox_footnote(p_locked_action_id);
880
881 ln_yepp_footnote_count := 0;
882 loop
883 fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
884 exit when c_get_nonbox_footnote%NOTFOUND;
885
886 hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
887 hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
888
889 ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
890 end loop;
891
892 close c_get_nonbox_footnote;
893
894
895 /* Compare RL1 Amendment Box Data and RL1 YEPP Box Data for an
896 assignment_action */
897
898 hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Box Data ');
899
900 if ln_box <> ln_amend_box then
901 lv_flag := 'Y';
902 elsif ln_box = ln_amend_box then
903 for i in ltr_yepp_arch_data.first..ltr_yepp_arch_data.last
904 loop
905 if (ltr_yepp_arch_data(i).archive_ue_id =
906 ltr_amend_arch_data(i).archive_ue_id) then
907
908 if ((ltr_yepp_arch_data(i).archive_value <>
909 ltr_amend_arch_data(i).archive_value) or
910 (ltr_yepp_arch_data(i).archive_value is null and
911 ltr_amend_arch_data(i).archive_value is not null) or
912 (ltr_yepp_arch_data(i).archive_value is not null and
913 ltr_amend_arch_data(i).archive_value is null)) then
914
915 lv_flag := 'Y';
916 hr_utility.trace('Archive_UE_id with differnt value :'||
917 to_char(ltr_yepp_arch_data(i).archive_ue_id));
918 exit;
919 end if;
920
921 end if;
922
923 end loop;
924
925 end if;
926
927 /* Compare RL1 Employee Data and RL1 YEPP Employee Data for an
928 assignment_action */
929 If lv_flag <> 'Y' then
930
931 hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Employee Data ');
932 for i in ltr_yepp_emp_data.first..ltr_yepp_emp_data.last
933 loop
934 if (ltr_yepp_emp_data(i).archive_ue_id =
935 ltr_amend_emp_data(i).archive_ue_id) then
936
937 hr_utility.trace('ltr_yepp_emp_data(i).archive_value : '||
938 ltr_yepp_emp_data(i).archive_value);
939 hr_utility.trace('ltr_amend_emp_data(i).archive_value: '||
940 ltr_amend_emp_data(i).archive_value);
941
942 if ((ltr_yepp_emp_data(i).archive_value <>
943 ltr_amend_emp_data(i).archive_value) or
944 (ltr_yepp_emp_data(i).archive_value is null and
945 ltr_amend_emp_data(i).archive_value is not null) or
946 (ltr_yepp_emp_data(i).archive_value is not null and
947 ltr_amend_emp_data(i).archive_value is null)) then
948
949 lv_flag := 'Y';
950 hr_utility.trace('Archive_UE_id with different value :'||
951 to_char(ltr_yepp_arch_data(i).archive_ue_id));
952 exit;
953 end if;
954
955 end if;
956 end loop;
957
958 End if; -- p_flag <> 'Y'
959
960 /* Compare RL1 Amendment Footnotes and RL1 YEPP Footnotes for an
961 assignment_action */
962
963 hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Footnotes');
964
965 if lv_flag <> 'Y' then
966
967 if ln_yepp_footnote_count <> ln_amend_footnote_count then
968 lv_flag := 'Y';
969 elsif ((ln_yepp_footnote_count = ln_amend_footnote_count) and
970 (ln_yepp_footnote_count <> 0)) then
971 for i in ltr_yepp_footnote.first..ltr_yepp_footnote.last
972 loop
973 if (ltr_yepp_footnote(i).message =
974 ltr_amend_footnote(i).message) then
975
976 if ((ltr_yepp_footnote(i).value <>
977 ltr_amend_footnote(i).value) or
978 (ltr_yepp_footnote(i).value is null and
979 ltr_amend_footnote(i).value is not null) or
980 (ltr_yepp_footnote(i).value is not null and
981 ltr_amend_footnote(i).value is null)) then
982
983 lv_flag := 'Y';
984 hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
985 exit;
986 end if;
987 end if;
988 end loop;
989 end if;
990
991 end if;
992
993 /* If there is no value difference for Entire Employee data then set
994 flag to 'N' */
995
996 if lv_flag <> 'Y' then
997 lv_flag := 'N';
998 hr_utility.trace('No value difference for an Employee Asg Action: '||
999 to_char(p_assignment_action_id));
1000 end if;
1001
1002 hr_utility.trace('lv_flag :'||lv_flag);
1003 return lv_flag;
1004
1005 end compare_archive_data;
1006
1007
1008 /*
1009 Name : eoy_action_creation
1010 Purpose : This creates the assignment actions for a specific chunk
1011 of people to be archived by the year end preprocess.
1012 Arguments :
1013 Notes :
1014 */
1015
1016 procedure eoy_action_creation(pactid in number,
1017 stperson in number,
1018 endperson in number,
1019 chunk in number) is
1020
1021
1022
1023 /* Variables used to hold the select columns from the SQL statement.*/
1024
1025 l_person_id number;
1026 l_assignment_id number;
1027 l_tax_unit_id number;
1028 l_eoy_tax_unit_id number;
1029 l_effective_end_date date;
1030 l_object_version_number number;
1031 l_some_warning boolean;
1032 l_counter number;
1033 l_user_entity_name_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1034 l_user_entity_value_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1035 l_user_entity_name varchar2(240);
1036
1037 /* Variables used to hold the values used as bind variables within the
1038 SQL statement. */
1039
1040 l_bus_group_id number;
1041 l_period_start date;
1042 l_period_end date;
1043
1044 /* Variables used to hold the details of the payroll and assignment actions
1045 that are created. */
1046
1047 l_payroll_action_created boolean := false;
1048 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
1049 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1050 l_archive_item_id number;
1051
1052 /* Variable holding the balance to be tested. */
1053
1054 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
1055
1056 /* Indicator variables used to control how the people are grouped. */
1057
1058 l_group_by_gre boolean := FALSE;
1059 l_group_by_medicare boolean := FALSE;
1060
1061 /* Indicator variables used to control which contexts are set up for
1062 balance. */
1063
1064 l_tax_unit_context boolean := FALSE;
1065 l_jurisdiction_context boolean := FALSE;
1066
1067 /* Variables used to hold the current values returned within the loop for
1068 checking against the new values returned from within the loop on the
1069 next iteration. */
1070
1071 l_prev_person_id per_all_people_f.person_id%type;
1072 l_prev_tax_unit_id hr_all_organization_units.organization_id%type;
1073
1074 /* Variable to hold the jurisdiction code used as a context for state
1075 reporting. */
1076
1077 l_jurisdiction_code varchar2(30);
1078
1079 /* general process variables */
1080
1081 l_report_type pay_payroll_actions.report_type%type;
1082
1083 -- Variables declared for bug 10399514
1084 l_person_on boolean ;
1085 l_report_cat pay_payroll_actions.report_category%type;
1086 l_state pay_payroll_actions.report_qualifier%type;
1087 l_report_format pay_report_format_mappings_f.report_format%type;
1088 -- Variables declared for bug 10399514
1089
1090 l_province pay_payroll_actions.report_qualifier%type;
1091 l_value number;
1092 l_effective_date date;
1093 l_quarter_start date;
1094 l_quarter_end date;
1095 l_year_start date;
1096 l_year_end date;
1097 lockingactid number;
1098 l_max_aaid number;
1099 l_pre_organization_id varchar2(17);
1100 l_prev_pre_organization_id varchar2(17);
1101 l_primary_asg pay_assignment_actions.assignment_id%type;
1102 ln_no_gross_earnings number;
1103 ln_nontaxable_earnings number;
1104
1105
1106 /* For Year End Preprocess we have to archive the assignments
1107 belonging to a GRE */
1108
1109 /* For Year End Preprocess we can also archive the assignments
1110 belonging to all GREs */
1111 /*
1112 CURSOR c_eoy_qbin IS
1113 SELECT ASG.person_id person_id,
1114 ASG.assignment_id assignment_id,
1115 ASG.effective_end_date effective_end_date
1116 FROM per_all_assignments_f ASG,
1117 pay_all_payrolls_f PPY,
1118 hr_soft_coding_keyflex SCL
1119 WHERE ASG.business_group_id + 0 = l_bus_group_id
1120 AND ASG.person_id between stperson and endperson
1121 AND ASG.assignment_type = 'E'
1122 AND ASG.effective_start_date <= l_period_end
1123 AND ASG.effective_end_date >= l_period_start
1124 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1125 AND (
1126 (rtrim(ltrim(SCL.segment1)) in
1127 (select to_char(hoi.organization_id)
1128 from hr_organization_information hoi
1129 where hoi.org_information_context = 'Canada Employer Identification'
1130 and hoi.org_information2 = l_pre_organization_id))
1131 or
1132 (rtrim(ltrim(SCL.segment11)) in
1133 (select to_char(hoi.organization_id)
1134 from hr_organization_information hoi
1135 where hoi.org_information_context = 'Canada Employer Identification'
1136 and hoi.org_information2 = l_pre_organization_id))
1137 )
1138 AND PPY.payroll_id = ASG.payroll_id
1139 and exists ( select 'X' from pay_action_contexts pac, ff_contexts fc
1140 where pac.assignment_id = asg.assignment_id
1141 and pac.context_id = fc.context_id
1142 and fc.context_name = 'JURISDICTION_CODE'
1143 and pac.context_value = 'QC' )
1144 ORDER BY 1, 3 DESC, 2; */
1145
1146 /* bug 5202869. For performance reason changed the query to remove per_people_f
1147 and also disabled some indexes. With this change the cost of the query
1148 increases however now the path taken is now more correct.
1149 */
1150 CURSOR c_eoy_qbin IS
1151 SELECT asg.person_id person_id,
1152 asg.assignment_id assignment_id,
1153 asg.effective_end_date effective_end_date
1154 FROM per_all_assignments_f asg,
1155 pay_assignment_actions paa,
1156 pay_payroll_actions ppa
1157 WHERE ppa.effective_date between l_period_start
1158 and l_period_end
1159 AND ppa.action_type in ('R','Q','V','B','I')
1160 AND ppa.business_group_id +0 = l_bus_group_id
1161 AND ppa.payroll_action_id = paa.payroll_action_id
1162 AND paa.tax_unit_id in (select hoi.organization_id
1163 from hr_organization_information hoi
1164 where hoi.org_information_context ||''= 'Canada Employer Identification'
1165 and hoi.org_information2 = l_pre_organization_id
1166 and hoi.org_information5 in ('T4/RL1','T4A/RL1'))
1167 AND paa.assignment_id = asg.assignment_id
1168 AND ppa.business_group_id = asg.business_group_id +0
1169 AND asg.person_id between stperson and endperson
1170 AND asg.assignment_type = 'E'
1171 AND ppa.effective_date between asg.effective_start_date
1172 and asg.effective_end_date
1173 AND EXISTS (select 1
1174 from pay_action_contexts pac,
1175 ff_contexts fc
1176 where pac.assignment_id = paa.assignment_id
1177 and pac.assignment_action_id = paa.assignment_action_id
1178 and pac.context_id = fc.context_id
1179 and fc.context_name ||'' = 'JURISDICTION_CODE'
1180 and pac.context_value ||'' = 'QC')
1181 ORDER BY 1, 3 DESC, 2;
1182
1183 -- Added for Bug# 10399514
1184 -- Used when RANGE_PERSON_ID functionality is available
1185
1186 CURSOR c_eoy_qbin_range IS
1187 SELECT asg.person_id person_id,
1188 asg.assignment_id assignment_id,
1189 asg.effective_end_date effective_end_date
1190 FROM per_all_assignments_f asg,
1191 pay_assignment_actions paa,
1192 pay_payroll_actions ppa,
1193 pay_population_ranges ppr
1194 WHERE ppa.effective_date between l_period_start
1195 and l_period_end
1196 AND ppa.action_type in ('R','Q','V','B','I')
1197 AND ppa.business_group_id +0 = l_bus_group_id
1198 AND ppa.payroll_action_id = paa.payroll_action_id
1199 AND paa.tax_unit_id in (select hoi.organization_id
1200 from hr_organization_information hoi
1201 where hoi.org_information_context ||''= 'Canada Employer Identification'
1202 and hoi.org_information2 = l_pre_organization_id
1203 and hoi.org_information5 in ('T4/RL1','T4A/RL1'))
1204 AND paa.assignment_id = asg.assignment_id
1205 AND ppa.business_group_id = asg.business_group_id +0
1206 -- AND asg.person_id between stperson and endperson
1207 AND ppr.payroll_action_id = pactid
1208 AND ppr.chunk_number = chunk
1209 AND ppr.person_id = ASG.person_id
1210 AND asg.assignment_type = 'E'
1211 AND ppa.effective_date between asg.effective_start_date
1212 and asg.effective_end_date
1213 AND EXISTS (select 1
1214 from pay_action_contexts pac,
1215 ff_contexts fc
1216 where pac.assignment_id = paa.assignment_id
1217 and pac.assignment_action_id = paa.assignment_action_id
1218 and pac.context_id = fc.context_id
1219 and fc.context_name ||'' = 'JURISDICTION_CODE'
1220 and pac.context_value ||'' = 'QC')
1221 ORDER BY 1, 3 DESC, 2;
1222
1223 cursor c_all_qbin_gres is
1224 select hoi.organization_id
1225 from hr_organization_information hoi
1226 where hoi.org_information_context = 'Canada Employer Identification'
1227 and hoi.org_information2 = l_pre_organization_id;
1228
1229 /* Get the primary assignment for the given person_id */
1230
1231 CURSOR c_get_asg_id (p_person_id number) IS
1232 SELECT assignment_id
1233 from per_all_assignments_f paf
1234 where person_id = p_person_id
1235 and primary_flag = 'Y'
1236 and assignment_type = 'E'
1237 and paf.effective_start_date <= l_period_end
1238 and paf.effective_end_date >= l_period_start
1239 ORDER BY assignment_id desc;
1240
1241 /* Added cursor for 11510 Changes Bug#3356533. Changed cursor to get
1242 max asgact_id based on person_id, fix for bug#3638928. */
1243 CURSOR c_get_max_asg_act_id(cp_person_id number,
1244 cp_tax_unit_id number,
1245 cp_period_start date,
1246 cp_period_end date) IS
1247 select paa.assignment_action_id
1248 from pay_assignment_actions paa,
1249 per_all_assignments_f paf,
1250 per_all_people_f ppf,
1251 pay_payroll_actions ppa,
1252 pay_action_classifications pac
1253 where ppf.person_id = cp_person_id
1254 and paf.person_id = ppf.person_id
1255 and paa.assignment_id = paf.assignment_id
1256 and paa.tax_unit_id = cp_tax_unit_id
1257 and ppa.payroll_action_id = paa.payroll_action_id
1258 and ppa.effective_date between cp_period_start and cp_period_end
1259 and ppa.effective_date between ppf.effective_start_date
1260 and ppf.effective_end_date
1261 and ppa.effective_date between paf.effective_start_date
1262 and paf.effective_end_date
1263 and ppa.action_type = pac.action_type
1264 and pac.classification_name = 'SEQUENCED'
1265 order by paa.action_sequence desc;
1266
1267 begin
1268
1269 /* Get the report type, report qualifier, business group id and the
1270 gre for which the archiving has to be done */
1271
1272 hr_utility.trace('getting report type ');
1273
1274 select effective_date,
1275 report_type,
1276 -- Added for bug 10399514
1277 report_qualifier,
1278 report_category,
1279 -- Added for bug 10399514
1280 business_group_id,
1281 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
1282 legislative_parameters)
1283 into l_effective_date,
1284 l_report_type,
1285 -- Added for bug 10399514
1286 l_state,
1287 l_report_cat,
1288 -- Added for bug 10399514
1289 l_bus_group_id,
1290 l_pre_organization_id
1291 from pay_payroll_actions
1292 where payroll_action_id = pactid;
1293
1294 hr_utility.trace('getting dates');
1295
1296 get_dates(l_report_type,
1297 l_effective_date,
1298 l_period_end,
1299 l_quarter_start,
1300 l_quarter_end,
1301 l_year_start,
1302 l_year_end);
1303
1304 hr_utility.trace('getting selection information');
1305 hr_utility.trace('report type '|| l_report_type);
1306 hr_utility.trace('report qualifier '|| l_state);
1307 hr_utility.trace('report category '|| l_report_cat);
1308 hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309 hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1310 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1311 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1312
1313 get_selection_information
1314 (l_report_type,
1315 l_quarter_start,
1316 l_quarter_end,
1317 l_year_start,
1318 l_year_end,
1319 l_period_start,
1320 l_period_end,
1321 l_defined_balance_id,
1322 l_group_by_gre,
1323 l_group_by_medicare,
1324 l_tax_unit_context,
1325 l_jurisdiction_context);
1326
1327 hr_utility.trace('Out of get selection information');
1328
1329 -- Code modification for bug 10399514 starts here
1330 /* Initializing variable */
1331 l_person_on := FALSE ;
1332
1333 Begin
1334 select report_format
1335 into l_report_format
1336 from pay_report_format_mappings_f
1337 where report_type = l_report_type
1338 and report_qualifier = l_state
1339 and report_category = l_report_cat ;
1340 Exception
1341 When Others Then
1342 l_report_format := Null ;
1343 End ;
1344
1345 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
1346 p_report_format => l_report_format,
1347 p_report_qualifier => l_state,
1348 p_report_category => l_report_cat) ;
1349
1350 -- open c_eoy_qbin;
1351
1352 if l_person_on then
1353 hr_utility.trace('opening c_eoy_qbin_range CURSOR');
1354 OPEN c_eoy_qbin_range ;
1355 else
1356 hr_utility.trace('opening c_eoy_qbin CURSOR');
1357 OPEN c_eoy_qbin;
1358 end if ;
1359
1360 -- Code modification for bug 10399514 ends here
1361
1362 /* Loop for all rows returned for SQL statement. */
1363
1364 hr_utility.trace('Entering loop');
1365
1366 loop
1367
1368 -- Code modification for bug 10399514 starts here
1369 if l_person_on then
1370 hr_utility.trace('fetching from c_eoy_qbin_range CURSOR');
1371 fetch c_eoy_qbin_range
1372 into l_person_id,
1373 l_assignment_id,
1374 l_effective_end_date;
1375 exit when c_eoy_qbin_range%NOTFOUND;
1376 else
1377 hr_utility.trace('fetching from c_eoy_qbin CURSOR');
1378 fetch c_eoy_qbin
1379 into l_person_id,
1380 l_assignment_id,
1381 l_effective_end_date;
1382 exit when c_eoy_qbin%NOTFOUND;
1383 end if ;
1384 -- Code modification for bug 10399514 ends here
1385
1386 /* If the new row is the same as the previous row according to the way
1387 the rows are grouped then discard the row ie. grouping by PRE
1388 organization id requires a single row for each person / PRE
1389 combination. */
1390
1391 hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1392 hr_utility.trace('previous pre_organization_id is '||
1393 l_prev_pre_organization_id);
1394 hr_utility.trace('person_id is '||
1395 to_char(l_person_id));
1396 hr_utility.trace('previous person_id is '||
1397 to_char(l_prev_person_id));
1398
1399 if (l_person_id = l_prev_person_id and
1400 l_pre_organization_id = l_prev_pre_organization_id) then
1401
1402 hr_utility.trace('Not creating assignment action');
1403
1404 else
1405 /* Check whether the person has 0 payment or not */
1406
1407 l_value := 0;
1408 ln_no_gross_earnings := 0;
1409 ln_nontaxable_earnings := 0;
1410
1411 open c_all_qbin_gres;
1412 loop
1413 fetch c_all_qbin_gres into l_tax_unit_id;
1414 exit when c_all_qbin_gres%NOTFOUND;
1415
1416 /* select the maximum assignment action id, removed the select stmt
1417 and replaced it with cursor c_get_max_asg_act_id 11510 Changes
1418 Bug#3356533. Passing person_id to fix bug#3638928 */
1419 begin
1420 open c_get_max_asg_act_id(l_person_id,
1421 l_tax_unit_id,
1422 l_period_start,
1423 l_period_end);
1424 fetch c_get_max_asg_act_id into l_max_aaid;
1425 if c_get_max_asg_act_id%NOTFOUND then
1426 l_max_aaid := -9999;
1427 end if;
1428 close c_get_max_asg_act_id;
1429
1430 end;
1431
1432 if l_max_aaid <> -9999 then
1433 l_value := l_value +
1434 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1435 ('Gross Earnings',
1436 'YTD' ,
1437 l_max_aaid,
1438 l_assignment_id ,
1439 NULL,
1440 'PER' ,
1441 l_tax_unit_id,
1442 l_bus_group_id,
1443 'QC'
1444 ),0) ;
1445
1446 ln_no_gross_earnings := ln_no_gross_earnings +
1447 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1448 ('RL1 No Gross Earnings',
1449 'YTD' ,
1450 l_max_aaid,
1451 l_assignment_id ,
1452 NULL,
1453 'PER' ,
1454 l_tax_unit_id,
1455 l_bus_group_id,
1456 'QC'
1457 ),0);
1458
1459 ln_nontaxable_earnings := ln_nontaxable_earnings +
1460 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1461 ('RL1 Non Taxable Earnings',
1462 'YTD' ,
1463 l_max_aaid,
1464 l_assignment_id ,
1465 NULL,
1466 'PER' ,
1467 l_tax_unit_id,
1468 l_bus_group_id,
1469 'QC'
1470 ),0);
1471 end if; /* end l_max_id <> -9999 */
1472 end loop;
1473 close c_all_qbin_gres;
1474 /* end of checking whether the person has 0 payment */
1475
1476 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1477 hr_utility.trace('person is '|| to_char(l_person_id));
1478 hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1479
1480
1481 /* Have a new unique row according to the way the rows are grouped.
1482 The inclusion of the person is dependent on having a non zero
1483 balance.
1484 If the balance is non zero then an assignment action is created to
1485 indicate their inclusion in the magnetic tape report. */
1486
1487 /* Set up the context of tax unit id */
1488
1489 hr_utility.trace('Setting context');
1490
1491 /* Only create assignment actions if Gross Earnings are not 0 and are not
1492 made up of only nontaxable earnings or the No Gross Earnings balance is
1493 non zero */
1494
1495 if (((l_value <> 0) and
1496 (ln_nontaxable_earnings <> l_value)) or
1497 (ln_no_gross_earnings <> 0)) then
1498
1499 /* Get the primary assignment */
1500 open c_get_asg_id(l_person_id);
1501 fetch c_get_asg_id into l_primary_asg;
1502
1503 if c_get_asg_id%NOTFOUND then
1504 close c_get_asg_id;
1505 raise hr_utility.hr_error;
1506 else
1507 close c_get_asg_id;
1508 end if;
1509
1510 /* Create the assignment action to represnt the person / tax unit
1511 combination. */
1512
1513 select pay_assignment_actions_s.nextval
1514 into lockingactid
1515 from dual;
1516
1517 /* Insert into pay_assignment_actions. */
1518
1519 hr_utility.trace('creating assignment action');
1520
1521 /* Passing tax unit id as null */
1522
1523 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1524 pactid,chunk,null);
1525
1526 /* Update the serial number column with the person id
1527 so that the mag routine and the RL1 view will not have
1528 to do an additional checking against the assignment
1529 table */
1530
1531 hr_utility.trace('updating assignment action' || to_char(lockingactid));
1532
1533 update pay_assignment_actions aa
1534 set aa.serial_number = to_char(l_person_id)
1535 where aa.assignment_action_id = lockingactid;
1536
1537
1538 /* Since the API checks the presence of a row in pay_report_format_items for
1539 action type AAC and PA , check it here also to avoid API error */
1540 /*
1541 l_counter := 1;
1542 l_user_entity_name := 'CAEOY_RL1_QUEBEC_BN';
1543
1544 hr_utility.trace('Archiving AAC level data for ' || to_char(lockingactid));
1545 ff_archive_api.create_archive_item(
1546 p_archive_item_id => l_archive_item_id
1547 ,p_user_entity_id => get_user_entity_id(l_user_entity_name)
1548 ,p_archive_value => l_pre_organization_id
1549 ,p_archive_type => 'AAC'
1550 ,p_action_id => lockingactid
1551 ,p_legislation_code => 'CA'
1552 ,p_object_version_number => l_object_version_number
1553 ,p_some_warning => l_some_warning
1554 );
1555 hr_utility.trace('Archived AAC level data');
1556 */
1557
1558 /* I have to enter data in new archive table also with archive type as AAC, ie
1559 assignment_action_creation */
1560 end if; /* end of l_value <> 0 OR ln_no_gross_earnings <> 0 */
1561 end if; /* end of l_person_id <> l_prev_person_id */
1562
1563 /* Record the current values for the next time around the loop. */
1564
1565 l_prev_person_id := l_person_id;
1566 l_prev_pre_organization_id := l_pre_organization_id;
1567
1568 end loop;
1569
1570 hr_utility.trace('Action creation done');
1571
1572 -- Code modification for bug 10399514 starts here
1573 if l_person_on then
1574 hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1575 close c_eoy_qbin_range;
1576 else
1577 hr_utility.trace('closing c_eoy_qbin CURSOR');
1578 close c_eoy_qbin;
1579 end if ;
1580 -- Code modification for bug 10399514 ends here
1581
1582 end eoy_action_creation;
1583
1584
1585
1586 /* Name : eoy_get_jursd_level
1587 Purpose : This returns the jurisdiction level of the non balance
1588 database items.
1589 Arguments :
1590 Notes :
1591 */
1592
1593 function eoy_get_jursd_level(p_route_id number,
1594 p_user_entity_id number) return number is
1595 l_jursd_value number:= 0;
1596
1597 begin
1598
1599 select frpv.value
1600 into l_jursd_value
1601 from ff_route_parameter_values frpv,
1602 ff_route_parameters frp
1603 where frpv.route_parameter_id = frp.route_parameter_id
1604 and frpv.user_entity_id = p_user_entity_id
1605 and frp.route_id = p_route_id
1606 and frp.parameter_name = 'Jursd. Level';
1607
1608 return(l_jursd_value);
1609
1610 exception
1611 when no_data_found then
1612 return(0);
1613 when others then
1614 hr_utility.trace('Error while getting the jursd. value ' ||
1615 to_char(sqlcode));
1616
1617 end eoy_get_jursd_level;
1618
1619
1620
1621 /* Name : eoy_archive_gre_data
1622 Purpose : This performs the CA specific employer data archiving.
1623 Arguments :
1624 Notes :
1625 */
1626
1627 PROCEDURE eoy_archive_gre_data(p_payroll_action_id in number,
1628 p_pre_organization_id in varchar2)
1629 IS
1630
1631 l_user_entity_id number;
1632 l_taxunit_context_id number;
1633 l_jursd_context_id number;
1634 l_value varchar2(240);
1635 l_sit_uid number;
1636 l_sui_uid number;
1637 l_fips_uid number;
1638 l_seq_tab pay_ca_eoy_rl1_archive.number_data_type_table;
1639 l_context_id_tab pay_ca_eoy_rl1_archive.number_data_type_table;
1640 l_context_val_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1641 l_user_entity_name_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1642 l_user_entity_value_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
1643 l_arch_gre_step number := 0;
1644 l_archive_item_id number;
1645 l_town_or_city varchar2(240);
1646 l_province_code varchar2(240);
1647 l_postal_code varchar2(240);
1648 l_organization_id_of_qin number;
1649 l_transmitter_org_id number;
1650 l_country_code varchar2(240);
1651 l_transmitter_name varchar2(240);
1652 l_Transmitter_Type_Indicator varchar2(240);
1653 l_transmitter_gre_ind varchar2(240);
1654 l_Transmitter_number varchar2(240);
1655 l_transmitter_addr_line_1 varchar2(240);
1656 l_transmitter_addr_line_2 varchar2(240);
1657 l_transmitter_addr_line_3 varchar2(240);
1658 l_transmitter_city varchar2(240);
1659 l_transmitter_province varchar2(240);
1660 l_transmitter_postal_code varchar2(240);
1661 l_transmitter_country varchar2(240);
1662 l_rl_data_type varchar2(240);
1663 l_rl_package_type varchar2(240);
1664 l_rl_source_of_slips varchar2(240);
1665 l_technical_contact_name varchar2(240);
1666 l_technical_contact_phone varchar2(240);
1667 l_technical_contact_area_code varchar2(240);
1668 l_technical_contact_extension varchar2(240);
1669 l_technical_contact_language varchar2(240);
1670 l_accounting_contact_name varchar2(240);
1671 l_accounting_contact_phone varchar2(240);
1672 l_accounting_contact_area_code varchar2(240);
1673 l_accounting_contact_extension varchar2(240);
1674 l_accounting_contact_language varchar2(240);
1675 l_proprietor_sin varchar2(240);
1676 l_name varchar2(240);
1677 l_employer_ein varchar2(240);
1678 l_address_line_1 varchar2(240);
1679 l_address_line_2 varchar2(240);
1680 l_address_line_3 varchar2(240);
1681 l_counter number := 0;
1682 l_object_version_number number;
1683 l_business_group_id varchar2(240);
1684 l_some_warning boolean;
1685 l_step number := 0;
1686 l_taxation_year varchar2(4);
1687 l_rl1_last_slip_number number ;
1688 l_employer_info_found varchar2(1);
1689 l_max_slip_number varchar2(80);
1690
1691 cursor employer_info is
1692 select target1.organization_id,
1693 target2.name,
1694 target2.business_group_id,
1695 target1.ORG_INFORMATION2 Prov_Identi_Number,
1696 target1.ORG_INFORMATION7 Type_of_Transmitter,
1697 target1.ORG_INFORMATION5 Transmitter_Number,
1698 target1.ORG_INFORMATION4 Type_of_Data,
1699 target1.ORG_INFORMATION6 Type_of_Package,
1700 target1.ORG_INFORMATION8 Source_of_RL_slips_used,
1701 target1.ORG_INFORMATION9 Tech_Res_Person_Name,
1702 target1.ORG_INFORMATION11 Tech_Res_Phone,
1703 target1.ORG_INFORMATION10 Tech_Res_Area_Code,
1704 target1.ORG_INFORMATION12 Tech_Res_Extension,
1705 decode(target1.ORG_INFORMATION13,'E','A',
1706 target1.ORG_INFORMATION13) Tech_Res_Language,
1707 target1.ORG_INFORMATION14 Acct_Res_Person_Name,
1708 target1.ORG_INFORMATION16 Acct_Res_Phone,
1709 target1.ORG_INFORMATION15 Acct_Res_Area_Code,
1710 target1.ORG_INFORMATION17 Acct_Res_Extension,
1711 decode(target1.ORG_INFORMATION19,'E','A',
1712 target1.ORG_INFORMATION19) Acct_Res_Language,
1713 substr(target1.ORG_INFORMATION18,1,8) RL1_Slip_Number,
1714 decode(target1.org_information3,'Y',target1.organization_id,
1715 target1.ORG_INFORMATION20),
1716 target1.ORG_INFORMATION3
1717 from hr_organization_information target1 ,
1718 hr_all_organization_units target2
1719 where target1.organization_id = to_number(p_pre_organization_id)
1720 and target2.business_group_id = l_business_group_id
1721 and target2.organization_id = target1.organization_id
1722 and target1.org_information_context = 'Prov Reporting Est';
1723
1724 /* payroll action level database items */
1725
1726 BEGIN
1727
1728 /*hr_utility.trace_on('Y','RL1'); */
1729
1730 select to_char(effective_date,'YYYY'),business_group_id
1731 into l_taxation_year,l_business_group_id
1732 from pay_payroll_actions
1733 where payroll_action_id = p_payroll_action_id;
1734
1735 open employer_info;
1736
1737 fetch employer_info
1738 into l_organization_id_of_qin,
1739 l_name, l_business_group_id,
1740 l_employer_ein,
1741 l_Transmitter_Type_Indicator, l_transmitter_number,
1742 l_rl_data_type, l_rl_package_type,
1743 l_rl_source_of_slips,
1744 l_technical_contact_name, l_technical_contact_phone,
1745 l_technical_contact_area_code, l_technical_contact_extension,
1746 l_technical_contact_language, l_accounting_contact_name,
1747 l_accounting_contact_phone ,
1748 l_accounting_contact_area_code ,
1749 l_accounting_contact_extension ,
1750 l_accounting_contact_language,
1751 l_rl1_last_slip_number,
1752 l_transmitter_org_id,
1753 l_transmitter_gre_ind;
1754
1755 l_arch_gre_step := 40;
1756 hr_utility.trace('eoy_archive_gre_data 1');
1757
1758 if employer_info%FOUND then
1759
1760 close employer_info;
1761 hr_utility.trace('got employer data ');
1762
1763 l_employer_info_found := 'Y';
1764
1765 begin
1766 select
1767 L.ADDRESS_LINE_1
1768 , L.ADDRESS_LINE_2
1769 , L.ADDRESS_LINE_3
1770 , L.TOWN_OR_CITY
1771 , DECODE(L.STYLE,'US',L.REGION_2,'CA',L.REGION_1,'CA_GLB',L.REGION_1,' ')
1772 , replace(L.POSTAL_CODE,' ')
1773 , L.COUNTRY
1774 into
1775 l_address_line_1
1776 , l_address_line_2
1777 , l_address_line_3
1778 , l_town_or_city
1779 , l_province_code
1780 , l_postal_code
1781 , l_country_code
1782 from hr_all_organization_units O,
1783 hr_locations_all L
1784 where L.LOCATION_ID = O.LOCATION_ID
1785 AND O.ORGANIZATION_ID = l_organization_id_of_qin;
1786
1787 /* Find out the highest slip number for that transmitter */
1788
1789 if l_transmitter_gre_ind = 'Y' then
1790
1791 l_transmitter_org_id := l_organization_id_of_qin;
1792
1793 l_transmitter_addr_line_1 := l_address_line_1;
1794 l_transmitter_addr_line_2 := l_address_line_2;
1795 l_transmitter_addr_line_3 := l_address_line_3;
1796 l_transmitter_city := l_town_or_city;
1797 l_transmitter_province := l_province_code;
1798 l_transmitter_postal_code := l_postal_code;
1799 l_transmitter_country := l_country_code;
1800
1801 end if;
1802
1803 exception when no_data_found then
1804 l_address_line_1 := NULL;
1805 l_address_line_2 := NULL;
1806 l_address_line_3 := NULL;
1807 l_town_or_city := NULL;
1808 l_province_code := NULL;
1809 l_postal_code := NULL;
1810 l_country_code := NULL;
1811 end;
1812
1813 begin
1814 select name
1815 into l_transmitter_name
1816 from hr_all_organization_units
1817 where organization_id = l_transmitter_org_id;
1818
1819 EXCEPTION
1820 when no_data_found then
1821 l_transmitter_name := null;
1822 end;
1823
1824 else
1825 l_employer_ein := 'TEST_DATA';
1826 l_address_line_1 := 'TEST_DATA';
1827 l_address_line_2 := 'TEST_DATA';
1828 l_address_line_3 := 'TEST_DATA';
1829 l_town_or_city := 'TEST_DATA';
1830 l_province_code := 'TEST_DATA';
1831 l_postal_code := 'TEST_DATA';
1832 l_country_code := 'TEST_DATA';
1833 l_name := 'TEST_DATA';
1834 l_transmitter_name := 'TEST_DATA';
1835 l_transmitter_addr_line_1 := 'TEST_DATA';
1836 l_transmitter_addr_line_2 := 'TEST_DATA';
1837 l_transmitter_addr_line_3 := 'TEST_DATA';
1838 l_transmitter_city := 'TEST_DATA';
1839 l_transmitter_province := 'TEST_DATA';
1840 l_transmitter_postal_code := 'TEST_DATA';
1841 l_transmitter_country := 'TEST_DATA';
1842 l_technical_contact_name := 'TEST_DATA';
1843 l_technical_contact_phone := 'TEST_DATA';
1844 l_technical_contact_language := 'TEST_DATA';
1845 l_accounting_contact_name := 'TEST_DATA';
1846 l_accounting_contact_phone := 'TEST_DATA';
1847 l_accounting_contact_language:= 'TEST_DATA';
1848 l_proprietor_sin := 'TEST_DATA';
1849 l_arch_gre_step := 424;
1850
1851 hr_utility.trace('eoy_archive_gre_data 2');
1852 close employer_info;
1853
1854 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1855 hr_utility.set_message_token('ORGIND','GRE');
1856 hr_utility.raise_error;
1857 end if;
1858
1859 /* archive Releve 1 data */
1860
1861 l_counter := l_counter + 1;
1862 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_QUEBEC_BN';
1863 l_user_entity_value_tab(l_counter) := l_employer_ein;
1864
1865 l_counter := l_counter + 1;
1866 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_PRE_ORG_ID';
1867 l_user_entity_value_tab(l_counter) := p_pre_organization_id;
1868
1869 l_counter := l_counter + 1;
1870 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_NUMBER';
1871 l_user_entity_value_tab(l_counter) := l_transmitter_number;
1872
1873 l_counter := l_counter + 1;
1874 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_DATA_TYPE';
1875 l_user_entity_value_tab(l_counter) := l_rl_data_type;
1876
1877 l_counter := l_counter + 1;
1878 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE';
1879 l_user_entity_value_tab(l_counter) := l_rl_package_type;
1880
1881 l_counter := l_counter + 1;
1882 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_TYPE';
1883 l_user_entity_value_tab(l_counter) := l_Transmitter_Type_Indicator;
1884
1885 l_counter := l_counter + 1;
1886 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_SOURCE_OF_SLIPS';
1887 l_user_entity_value_tab(l_counter) := l_rl_source_of_slips;
1888
1889 l_counter := l_counter + 1;
1890 l_user_entity_name_tab(l_counter) := 'CAEOY_TAXATION_YEAR';
1891 l_user_entity_value_tab(l_counter) := l_taxation_year;
1892
1893 l_arch_gre_step := 428;
1894 l_counter := l_counter + 1;
1895 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_COUNTRY';
1896 l_user_entity_value_tab(l_counter) := l_transmitter_country;
1897
1898 l_arch_gre_step := 429;
1899 l_counter := l_counter + 1;
1900 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_NAME';
1901 l_user_entity_value_tab(l_counter) := l_transmitter_name;
1902
1903 l_arch_gre_step := 4210;
1904 l_counter := l_counter + 1;
1905 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1';
1906 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_1;
1907
1908 l_arch_gre_step := 4211;
1909 l_counter := l_counter + 1;
1910 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2';
1911 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_2;
1912
1913 -- Bug 4517693
1914 l_arch_gre_step := 4212;
1915 l_counter := l_counter + 1;
1916 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE3';
1917 l_user_entity_value_tab(l_counter) := l_transmitter_addr_line_3;
1918
1919 l_arch_gre_step := 4213;
1920 l_counter := l_counter + 1;
1921 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_CITY';
1922 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1923
1924 l_arch_gre_step := 4214;
1925 l_counter := l_counter + 1;
1926 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_PROVINCE';
1927 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1928
1929 /*-- This is original
1930 l_arch_gre_step := 4212;
1931 l_counter := l_counter + 1;
1932 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_CITY';
1933 l_user_entity_value_tab(l_counter) := l_transmitter_city;
1934
1935 l_arch_gre_step := 4213;
1936 l_counter := l_counter + 1;
1937 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_PROVINCE';
1938 l_user_entity_value_tab(l_counter) := l_transmitter_province;
1939 */
1940
1941 l_arch_gre_step := 4215;
1942 l_counter := l_counter + 1;
1943 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE';
1944 l_user_entity_value_tab(l_counter) := l_transmitter_postal_code;
1945
1946 l_arch_gre_step := 4216;
1947 l_counter := l_counter + 1;
1948 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TECHNICAL_CONTACT_NAME';
1949 l_user_entity_value_tab(l_counter) := l_technical_contact_name;
1950
1951 l_arch_gre_step := 4217;
1952 l_counter := l_counter + 1;
1953 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE';
1954 l_user_entity_value_tab(l_counter) := l_technical_contact_phone;
1955
1956 l_counter := l_counter + 1;
1957 l_user_entity_name_tab(l_counter) :=
1958 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE';
1959 l_user_entity_value_tab(l_counter) := l_technical_contact_area_code;
1960
1961 l_counter := l_counter + 1;
1962 l_user_entity_name_tab(l_counter) :=
1963 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION';
1964 l_user_entity_value_tab(l_counter) := l_technical_contact_extension;
1965
1966 l_arch_gre_step := 4218;
1967 l_counter := l_counter + 1;
1968 l_user_entity_name_tab(l_counter) :=
1969 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE';
1970 l_user_entity_value_tab(l_counter) := l_technical_contact_language;
1971
1972 l_arch_gre_step := 4219;
1973 l_counter := l_counter + 1;
1974 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_ACCOUNTING_CONTACT_NAME';
1975 l_user_entity_value_tab(l_counter) := l_accounting_contact_name;
1976
1977 l_counter := l_counter + 1;
1978 l_user_entity_name_tab(l_counter) :=
1979 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE';
1980 l_user_entity_value_tab(l_counter) := l_accounting_contact_area_code;
1981
1982 l_arch_gre_step := 42110;
1983 l_counter := l_counter + 1;
1984 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE';
1985 l_user_entity_value_tab(l_counter) := l_accounting_contact_phone ;
1986
1987 l_counter := l_counter + 1;
1988 l_user_entity_name_tab(l_counter) :=
1989 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION';
1990 l_user_entity_value_tab(l_counter) := l_accounting_contact_extension;
1991
1992 l_arch_gre_step := 4218;
1993 l_counter := l_counter + 1;
1994 l_user_entity_name_tab(l_counter) :=
1995 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE';
1996 l_user_entity_value_tab(l_counter) := l_accounting_contact_language;
1997
1998 l_arch_gre_step := 42111;
1999 l_counter := l_counter + 1;
2000 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_NAME';
2001
2002 l_user_entity_value_tab(l_counter) := 'TEST_DATA';
2003 l_user_entity_value_tab(l_counter) := l_name;
2004
2005 l_counter := l_counter + 1;
2006 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1';
2007 l_user_entity_value_tab(l_counter) := l_address_line_1;
2008
2009 l_counter := l_counter + 1;
2010 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2';
2011 l_user_entity_value_tab(l_counter) := l_address_line_2;
2012
2013 -- Bug 4517693
2014 l_counter := l_counter + 1;
2015 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3';
2016 l_user_entity_value_tab(l_counter) := l_address_line_3;
2017 --
2018 l_counter := l_counter + 1;
2019 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_CITY';
2020 l_user_entity_value_tab(l_counter) := l_town_or_city;
2021
2022 l_counter := l_counter + 1;
2023 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_PROVINCE';
2024 l_user_entity_value_tab(l_counter) := l_province_code;
2025
2026 l_counter := l_counter + 1;
2027 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_COUNTRY';
2028 l_user_entity_value_tab(l_counter) := l_country_code;
2029
2030 l_counter := l_counter + 1;
2031 l_user_entity_name_tab(l_counter) := 'CAEOY_RL1_EMPLOYER_POSTAL_CODE';
2032 l_user_entity_value_tab(l_counter) := l_postal_code;
2033
2034 l_arch_gre_step := 50;
2035 l_arch_gre_step := 51;
2036
2037 /* Other employer level data for RL-1 total is to be discussed ,
2038 whether it is for Quebec only or not */
2039
2040 g_archive_flag := 'Y';
2041
2042 for i in 1..l_counter loop
2043
2044 /*
2045 Since the API checks the presence of a row in pay_report_format_items for
2046 action type AAC and PA , check it here also to avoid API error To be done
2047 */
2048
2049 l_arch_gre_step := 52;
2050
2051 /*hr_utility.trace_on('Y','RL1'); */
2052
2053 hr_utility.trace('user_entity id is : ' || l_user_entity_name_tab(i));
2054
2055 ff_archive_api.create_archive_item(
2056 p_archive_item_id => l_archive_item_id
2057 ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
2058 ,p_archive_value => l_user_entity_value_tab(i)
2059 ,p_archive_type => 'PA'
2060 ,p_action_id => p_payroll_action_id
2061 ,p_legislation_code => 'CA'
2062 ,p_object_version_number=> l_object_version_number
2063 ,p_some_warning => l_some_warning
2064 );
2065 l_arch_gre_step := 53;
2066 end loop;
2067
2068 EXCEPTION
2069 when others then
2070 g_archive_flag := 'N';
2071 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2072 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2073 if l_arch_gre_step = 40 then
2074 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2075 hr_utility.set_message_token('ORGIND','ORG');
2076 end if;
2077 hr_utility.raise_error;
2078
2079 END eoy_archive_gre_data;
2080
2081 /* Name : chk_gre_archive
2082 Purpose : Function to check if the employer level data has been archived
2083 or not.
2084 Arguments :
2085 Notes :
2086 */
2087
2088 function chk_gre_archive (p_payroll_action_id number) return boolean is
2089
2090 l_flag varchar2(1);
2091
2092 cursor c_chk_payroll_action is
2093 select 'Y'
2094 from dual
2095 where exists (select null
2096 from ff_archive_items fai
2097 where fai.context1 = p_payroll_action_id);
2098 begin
2099
2100 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2101
2102 if g_archive_flag = 'Y' then
2103 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2104 return (TRUE);
2105 else
2106
2107 hr_utility.trace('chk_gre_archive - opening cursor');
2108
2109 open c_chk_payroll_action;
2110 fetch c_chk_payroll_action into l_flag;
2111 if c_chk_payroll_action%FOUND then
2112 hr_utility.trace('chk_gre_archive - found in cursor');
2113 g_archive_flag := 'Y';
2114 else
2115 hr_utility.trace('chk_gre_archive - not found in cursor');
2116 g_archive_flag := 'N';
2117 end if;
2118
2119 hr_utility.trace('chk_gre_archive - closing cursor');
2120 close c_chk_payroll_action;
2121 if g_archive_flag = 'Y' then
2122 hr_utility.trace('chk_gre_archive - returning true');
2123 return (TRUE);
2124 else
2125 hr_utility.trace('chk_gre_archive - returning false');
2126 return(FALSE);
2127 end if;
2128 end if;
2129 end chk_gre_archive;
2130
2131 /* Name : eoy_archinit
2132 Purpose : This performs the context initialization for the year end
2133 pre-process.
2134 Arguments :
2135 Notes :
2136 */
2137
2138
2139 procedure eoy_archinit(p_payroll_action_id in number) is
2140 l_jurisdiction_code VARCHAR2(30);
2141 l_tax_unit_id NUMBER(15);
2142 l_archive boolean:= FALSE;
2143 l_step number := 0;
2144
2145 cursor c_get_min_chunk is
2146 select min(paa.chunk_number)
2147 from pay_assignment_actions paa
2148 where paa.payroll_action_id = p_payroll_action_id;
2149 begin
2150 open c_get_min_chunk;
2151 fetch c_get_min_chunk into g_min_chunk;
2152 l_step := 1;
2153 if c_get_min_chunk%NOTFOUND then
2154 g_min_chunk := -1;
2155 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
2156 end if;
2157 close c_get_min_chunk;
2158
2159 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
2160 l_step := 2;
2161 l_archive := chk_gre_archive(p_payroll_action_id);
2162
2163 l_step := 3;
2164 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
2165 exception
2166 when others then
2167 raise_application_error(-20001,'eoy_archinit at '
2168 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2169 end eoy_archinit;
2170
2171 /* Name : getnext_seq_num
2172 Purpose : Calculates and inserts check digit to PDF sequence number
2173 */
2174
2175 FUNCTION getnext_seq_num (p_curr_seq IN NUMBER)
2176 RETURN NUMBER IS
2177 l_seq_number number;
2178 l_check_number number;
2179 BEGIN
2180
2181 l_check_number := mod(p_curr_seq,7);
2182 hr_utility.trace('l_check_number ='|| l_check_number);
2183 l_seq_number := (p_curr_seq * 10) + l_check_number;
2184 hr_utility.trace('l_seq_number ='|| l_seq_number);
2185 return l_seq_number;
2186 END;
2187
2188
2189 /* Name : gen_rl1_pdf_seq
2190 Purpose : Generates sequence number for RL1 PDF. Bug 6768167.
2191 */
2192
2193 FUNCTION gen_rl1_pdf_seq(p_aaid number,
2194 p_reporting_year varchar2,
2195 p_jurisdiction varchar2,
2196 called_from varchar2)
2197 return varchar2 is
2198
2199 cursor c_get_arch_seq_num(cp_aaid varchar2,
2200 cp_jurisdiction varchar2) is
2201 SELECT fai1.value, fai1.archive_item_id, fai1.object_version_number
2202 FROM FF_ARCHIVE_ITEMS FAI1,
2203 ff_database_items fdi1,
2204 ff_archive_item_contexts faic,
2205 ff_contexts fc
2206 WHERE FAI1.USER_ENTITY_ID = fdi1.user_entity_id
2207 and fdi1.user_name = 'CAEOY_RL1_PDF_SEQ_NUMBER'
2208 and fai1.archive_item_id = faic.archive_item_id
2209 and fc.context_id = faic.context_id
2210 and fc.context_name = 'JURISDICTION_CODE'
2211 and faic.context = cp_jurisdiction
2212 and fai1.context1 = cp_aaid;
2213
2214 cursor c_get_seq_num_range(cp_run_year varchar2) is
2215 select ROW_LOW_RANGE_OR_NAME range_start,
2216 ROW_HIGH_RANGE range_end
2217 from pay_user_tables put,
2218 pay_user_rows_f pur
2219 where pur.USER_TABLE_ID=put.USER_TABLE_ID
2220 and put.USER_TABLE_NAME = 'RL1 PDF Sequence Range'
2221 and fnd_date.string_to_date('31/12/'||cp_run_year,'DD/MM/YYYY')
2222 between pur.EFFECTIVE_START_DATE and pur.EFFECTIVE_END_DATE;
2223
2224 /*cursor c_get_act_info(aaid number) is
2225 select ACTION_INFORMATION_ID, OBJECT_VERSION_NUMBER
2226 from pay_action_information
2227 where action_context_id = aaid
2228 and action_information_category='CAEOY RL2 EMPLOYEE INFO2';
2229 */
2230
2231 l_final_seq_num varchar2(240); /* Bug 13564765 sbachu*/
2232 l_start_seq_num varchar2(25);
2233 l_end_seq_num varchar2(25);
2234 l_seq_offset number;
2235 l_obj_ver number;
2236 l_warning boolean;
2237 l_archive_item_id number;
2238
2239 begin
2240 hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 10');
2241
2242 if (called_from = 'XMLPROC') then
2243 hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 20');
2244
2245 open c_get_arch_seq_num(p_aaid, p_jurisdiction);
2246 fetch c_get_arch_seq_num into l_final_seq_num,l_archive_item_id,l_obj_ver;
2247 close c_get_arch_seq_num;
2248
2249 if (l_final_seq_num is not null) then
2250 return l_final_seq_num;
2251 end if;
2252
2253 end if;
2254
2255 l_start_seq_num := null;
2256 open c_get_seq_num_range(p_reporting_year);
2257 fetch c_get_seq_num_range into l_start_seq_num,l_end_seq_num;
2258 close c_get_seq_num_range;
2259
2260 if (l_start_seq_num is not null) then
2261
2262 hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 30');
2263 hr_utility.trace('l_start_seq_num = '||l_start_seq_num);
2264 hr_utility.trace('l_end_seq_num = '||l_end_seq_num);
2265
2266 select PAY_CA_RL1_PDF_SEQ_COUNT_S.nextval into l_seq_offset
2267 from dual;
2268 l_final_seq_num := getnext_seq_num(l_start_seq_num + l_seq_offset);
2269
2270 hr_utility.trace('l_seq_offset = '||l_seq_offset);
2271 hr_utility.trace('l_final_seq_num = '||l_final_seq_num);
2272
2273 elsif (called_from ='ARCHIVER') then
2274 l_final_seq_num := null;
2275
2276 end if;
2277
2278 if (called_from ='XMLPROC') then
2279 if(l_archive_item_id is null) then --If DBI is not archived when PDF is run
2280 hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 40');
2281
2282 ff_archive_api.create_archive_item(
2283 p_archive_item_id => l_archive_item_id
2284 ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_PDF_SEQ_NUMBER')
2285 ,p_archive_value => l_final_seq_num
2286 ,p_archive_type => 'AAP'
2287 ,p_action_id => p_aaid
2288 ,p_legislation_code => 'CA'
2289 ,p_object_version_number => l_obj_ver
2290 ,p_context_name1 => 'JURISDICTION_CODE'
2291 ,p_context1 => 'QC'
2292 ,p_some_warning => l_warning
2293 );
2294 else -- If DBI is archived but with null value then update it with new value
2295 hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 50');
2296
2297 /* Commented for bug 8732218
2298 ff_archive_api.update_archive_item( p_archive_item_id => l_archive_item_id
2299 ,p_effective_date => fnd_date.string_to_date('31/12/'||p_reporting_year,'DD/MM/YYYY')
2300 --,p_validate in boolean default false
2301 ,p_archive_value => l_final_seq_num
2302 ,p_object_version_number => l_obj_ver
2303 ,p_some_warning => l_warning ); */
2304
2305 update ff_archive_items set VALUE= l_final_seq_num
2306 where ARCHIVE_ITEM_ID= l_archive_item_id;
2307
2308 end if;
2309 end if;
2310
2311 return l_final_seq_num;
2312
2313 end gen_rl1_pdf_seq;
2314
2315 /* Name : gen_rl1_slip_no
2316 Purpose : Generates RL1 Slip Number. Bug 13564765.
2317 */
2318
2319 FUNCTION gen_rl1_slip_no(p_payroll_action_id number)
2320 return varchar2 is
2321 l_rl1_slip_number_last_digit number;
2322 -- added for bug 11694701
2323 l_rl1_starting_slip_num number;
2324 l_rl1_ending_slip_num number;
2325 -- end changes for bug 11694701
2326 l_transmitter_name1 varchar2(80);
2327 l_rl1_last_slip_number number;
2328 l_rl1_curr_slip_number number;
2329 l_max_slip_number varchar2(80);
2330 l_rl1_slip_number varchar2(20);
2331 Begin
2332
2333 select decode(hoi.org_information3,'Y',hoi.organization_id,
2334 hoi.org_information20)
2335 into l_transmitter_name1
2336 from pay_payroll_actions ppa,
2337 hr_organization_information hoi,
2338 hr_all_organization_units hou
2339 WHERE hou.business_group_id = ppa.business_group_id
2340 and hoi.organization_id = hou.organization_id
2341 and hoi.org_information_context='Prov Reporting Est'
2342 and hoi.organization_id =
2343 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2344 ppa.legislative_parameters )
2345 and ppa.payroll_action_id = p_payroll_action_id
2346 and hoi.org_information4 = 'P01';
2347
2348 hr_utility.trace('l_transmitter ' || l_transmitter_name1);
2349
2350 hr_utility.trace('3');
2351 if l_transmitter_name1 is not null then
2352
2353 -- Added for bug 11694701
2354 /* select to_number(target.ORG_INFORMATION18)
2355 into l_rl1_last_slip_number
2356 from hr_organization_information target
2357 where target.organization_id = l_transmitter_name1
2358 and target.org_information_context = 'Prov Reporting Est'
2359 and target.ORG_INFORMATION3 = 'Y';*/
2360 Begin
2361 select to_number(target.ORG_INFORMATION1),to_number(target.ORG_INFORMATION2)
2362 into l_rl1_starting_slip_num,l_rl1_ending_slip_num
2363 from hr_organization_information target
2364 where target.organization_id = l_transmitter_name1
2365 and target.org_information_context = 'Prov Reporting Est3'
2366 and exists (select 'X' from hr_organization_information target1
2367 where target1.organization_id = l_transmitter_name1
2368 and target1.org_information_context = 'Prov Reporting Est'
2369 and target1.ORG_INFORMATION3 = 'Y');
2370 exception -- added exception for bug 12996280
2371 when no_data_found then
2372 null;
2373 end;
2374 if l_rl1_starting_slip_num is NULL then
2375 hr_utility.trace('Warning: Starting Slip number is NULL');
2376 end if;
2377
2378 if l_rl1_ending_slip_num is NULL then
2379 hr_utility.trace('Warning: Ending Slip number is NULL');
2380 end if;
2381
2382 hr_utility.trace('Starting Slip number: '||l_rl1_starting_slip_num);
2383 hr_utility.trace('Ending Slip number: '||l_rl1_ending_slip_num);
2384 -- Ended changes for bug 11694701
2385
2386 else
2387 hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2388 hr_utility.set_message_token('ORGIND','PRE');
2389 hr_utility.raise_error;
2390 end if;
2391
2392 hr_utility.trace('2');
2393
2394 -- Added for bug 11694701
2395 /*select l_rl1_last_slip_number + pay_ca_eoy_rl1_s.nextval - 1
2396 into l_rl1_curr_slip_number from dual;*/
2397 select l_rl1_starting_slip_num + pay_ca_eoy_rl1_s.nextval - 1
2398 into l_rl1_curr_slip_number from dual;
2399 hr_utility.trace('l_rl1_curr_slip_number: '||l_rl1_curr_slip_number);
2400
2401 if l_rl1_ending_slip_num is not NULL and l_rl1_curr_slip_number is not NULL
2402 and l_rl1_curr_slip_number > l_rl1_ending_slip_num then
2403 hr_utility.trace('Warning: Generated Slip number '||l_rl1_curr_slip_number
2404 ||' is greater than Ending slip number '||l_rl1_ending_slip_num);
2405 end if;
2406 -- Ended changes for bug 11694701
2407
2408 hr_utility.trace('1');
2409
2410 select mod(l_rl1_curr_slip_number,7)
2411 into l_rl1_slip_number_last_digit
2412 from dual;
2413
2414 hr_utility.trace('l_rl1_slip_number_last_digit : '|| l_rl1_slip_number_last_digit);
2415
2416 /* Commented and modified as below by sneelapa, bug 11654691
2417
2418 l_rl1_slip_number := (l_rl1_curr_slip_number)||
2419 l_rl1_slip_number_last_digit;
2420 */
2421
2422 l_rl1_slip_number :=lpad ((l_rl1_curr_slip_number)||l_rl1_slip_number_last_digit, 9, 0);
2423
2424 hr_utility.trace('l_rl1_slip_number : ' || l_rl1_slip_number);
2425 return l_rl1_slip_number;
2426
2427 end gen_rl1_slip_no;
2428
2429
2430 /* Name : eoy_archive_further_info
2431 Purpose : This Archive the all RL1 further infos which are available
2432 from 2011 year
2433 Arguments :
2434 Notes : Bug 13497300
2435 */
2436
2437 PROCEDURE eoy_archive_further_info(p_assactid in number,
2438 p_effective_date in date,
2439 p_negative_balance_exists in out NOCOPY varchar2,
2440 p_temp_nz_fi in out NOCOPY number) IS /*Bug 13564765 sbachu*/
2441
2442 l_aaid pay_assignment_actions.assignment_action_id%type;
2443 l_asgid pay_assignment_actions.assignment_id%type;
2444 l_date_earned date;
2445 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2446 l_business_group_id number;
2447 l_year_start date;
2448 l_year_end date;
2449 l_count number := 0;
2450 l_jurisdiction varchar2(11);
2451 lv_serial_number varchar2(30);
2452 l_chunk number;
2453 l_payroll_action_id number;
2454 result number := 0;
2455 l_some_warning boolean;
2456 l_negative_balance_exists varchar2(5);
2457 l_archive_item_id number;
2458 l_object_version_number number;
2459 l_user_entity_name_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2460 l_balance_type_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2461
2462 CURSOR c_get_max_asgactid_jd(cp_person_id number,
2463 cp_tax_unit_id number,
2464 cp_period_start date,
2465 cp_period_end date
2466 ) IS
2467 select /*+ Ordered */ paa.assignment_action_id
2468 from per_all_people_f ppf,
2469 per_all_assignments_f paf,
2470 pay_assignment_actions paa,
2471 pay_payroll_actions ppa,
2472 pay_action_classifications pac,
2473 pay_action_contexts pac1,
2474 ff_contexts fc
2475 where ppf.person_id = cp_person_id
2476 and paf.person_id = ppf.person_id
2477 and paf.assignment_id = paa.assignment_id
2478 and paa.tax_unit_id = cp_tax_unit_id
2479 and ppa.payroll_action_id = paa.payroll_action_id
2480 and ppa.effective_date between cp_period_start and cp_period_end
2481 and ppa.effective_date between ppf.effective_start_date
2482 and ppf.effective_end_date
2483 and ppa.effective_date between paf.effective_start_date
2484 and paf.effective_end_date
2485 and ppa.action_type = pac.action_type
2486 and pac.classification_name = 'SEQUENCED'
2487 AND pac1.assignment_action_id = paa.assignment_action_id
2488 AND pac1.context_id = fc.context_id
2489 AND fc.context_name = 'JURISDICTION_CODE'
2490 AND pac1.context_value = 'QC'
2491 order by paa.action_sequence desc;
2492
2493 cursor c_all_gres_for_further_info(asgactid number) is
2494 select hoi.organization_id
2495 from pay_payroll_actions ppa,
2496 pay_assignment_actions paa,
2497 hr_organization_information hoi
2498 where paa.assignment_action_id = asgactid
2499 and ppa.payroll_action_id = paa.payroll_action_id
2500 and hoi.org_information2 =
2501 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2502 ppa.legislative_parameters)
2503 and hoi.org_information_context = 'Canada Employer Identification'
2504 and hoi.org_information5 in ('T4/RL1','T4A/RL1')
2505 order by organization_id;
2506
2507 BEGIN
2508
2509 --hr_utility.trace_on(null,'RL1');
2510 hr_utility.set_location ('archive_data',1);
2511 hr_utility.trace('getting assignment');
2512
2513 l_negative_balance_exists := p_negative_balance_exists;
2514
2515 SELECT aa.assignment_id,
2516 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2517 aa.tax_unit_id,
2518 aa.chunk_number,
2519 aa.payroll_action_id,
2520 aa.serial_number
2521 into l_asgid,
2522 l_date_earned,
2523 l_tax_unit_id,
2524 l_chunk,
2525 l_payroll_action_id,
2526 lv_serial_number
2527 FROM pay_assignment_actions aa
2528 WHERE aa.assignment_action_id = p_assactid;
2529
2530 /* If the chunk of the assignment is same as the minimun chunk
2531 for the payroll_action_id and the gre data has not yet been
2532 archived then archive the gre data i.e. the employer data */
2533
2534 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2535
2536 hr_utility.trace('eoy_archive_further_info archiving employer data');
2537 hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2538
2539 select business_group_id
2540 into l_business_group_id
2541 from pay_payroll_actions
2542 where payroll_action_id = l_payroll_action_id;
2543
2544 end if;
2545
2546 hr_utility.set_location ('eoy_archive_further_info',2);
2547
2548 hr_utility.trace('assignment '|| to_char(l_asgid));
2549 hr_utility.trace('date_earned '|| to_char(l_date_earned));
2550 hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2551 hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2552
2553 /* Derive the beginning and end of the effective year */
2554
2555 hr_utility.trace('getting begin and end dates');
2556
2557 l_year_start := trunc(p_effective_date, 'Y');
2558 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2559
2560 hr_utility.trace('year start '|| to_char(l_year_start));
2561 hr_utility.trace('year end '|| to_char(l_year_end));
2562
2563 /* Initialise the PL/SQL table before populating it */
2564
2565 hr_utility.trace('Initialising Pl/SQL table');
2566 l_count := 0;
2567
2568 l_count := l_count + 1;
2569 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A1_AMT_PER_JD_YTD';
2570 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A1';
2571
2572 l_count := l_count + 1;
2573 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A2_AMT_PER_JD_YTD';
2574 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A2';
2575
2576 l_count := l_count + 1;
2577 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A3_AMT_PER_JD_YTD';
2578 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A3';
2579
2580 l_count := l_count + 1;
2581 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A4_AMT_PER_JD_YTD';
2582 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A4';
2583
2584 l_count := l_count + 1;
2585 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A5_AMT_PER_JD_YTD';
2586 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A5';
2587
2588 l_count := l_count + 1;
2589 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A6_AMT_PER_JD_YTD';
2590 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A6';
2591
2592 l_count := l_count + 1;
2593 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A7_AMT_PER_JD_YTD';
2594 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A7';
2595
2596 l_count := l_count + 1;
2597 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A8_AMT_PER_JD_YTD';
2598 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A8';
2599
2600 l_count := l_count + 1;
2601 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A9_AMT_PER_JD_YTD';
2602 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A9';
2603
2604 l_count := l_count + 1;
2605 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A10_AMT_PER_JD_YTD';
2606 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A10';
2607
2608 l_count := l_count + 1;
2609 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A11_AMT_PER_JD_YTD';
2610 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A11';
2611
2612 l_count := l_count + 1;
2613 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A12_AMT_PER_JD_YTD';
2614 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A12';
2615
2616 l_count := l_count + 1;
2617 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A13_AMT_PER_JD_YTD';
2618 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_A13';
2619
2620 l_count := l_count + 1;
2621 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_D1_AMT_PER_JD_YTD';
2622 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_D1';
2623
2624 l_count := l_count + 1;
2625 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_D2_AMT_PER_JD_YTD';
2626 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_D2';
2627
2628 l_count := l_count + 1;
2629 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_D3_AMT_PER_JD_YTD';
2630 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_D3';
2631
2632 l_count := l_count + 1;
2633 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_K1_AMT_PER_JD_YTD';
2634 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_K1';
2635
2636 l_count := l_count + 1;
2637 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_L2_AMT_PER_JD_YTD';
2638 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_L2';
2639
2640 l_count := l_count + 1;
2641 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_L3_AMT_PER_JD_YTD';
2642 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_L3';
2643
2644 l_count := l_count + 1;
2645 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_L4_AMT_PER_JD_YTD';
2646 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_L4';
2647
2648 l_count := l_count + 1;
2649 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_L5_AMT_PER_JD_YTD';
2650 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_L5';
2651
2652 l_count := l_count + 1;
2653 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_L6_AMT_PER_JD_YTD';
2654 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_L6';
2655
2656 l_count := l_count + 1;
2657 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_L7_AMT_PER_JD_YTD';
2658 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_L7';
2659
2660 l_count := l_count + 1;
2661 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_L8_AMT_PER_JD_YTD';
2662 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_L8';
2663
2664 l_count := l_count + 1;
2665 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_O2_AMT_PER_JD_YTD';
2666 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_O2';
2667
2668 l_count := l_count + 1;
2669 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_O3_AMT_PER_JD_YTD';
2670 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_O3';
2671
2672 l_count := l_count + 1;
2673 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_O4_AMT_PER_JD_YTD';
2674 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_O4';
2675
2676 l_count := l_count + 1;
2677 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_R1_AMT_PER_JD_YTD';
2678 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_R1';
2679
2680 l_count := l_count + 1;
2681 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_V1_AMT_PER_JD_YTD';
2682 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_V1';
2683
2684 l_count := l_count + 1;
2685 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD';
2686 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_201';
2687
2688 /* Bug 14701466,14616599 sbachu*/
2689 l_count := l_count + 1;
2690 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_211_AMT_PER_JD_YTD';
2691 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_211';
2692
2693 l_count := l_count + 1;
2694 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD';
2695 l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_235';
2696
2697
2698 hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2699 hr_utility.trace('l_asgid '||l_asgid);
2700 hr_utility.trace('l_business_group_id '||l_business_group_id);
2701 l_jurisdiction := 'QC';
2702 p_temp_nz_fi := 0; /* Bug 13564765 sbachu*/
2703 for i in 1 .. l_count
2704 loop
2705 /* Get the context_id for 'Jurisdiction' from ff_contexts */
2706 result := 0;
2707 open c_all_gres_for_further_info(p_assactid);
2708 loop
2709 hr_utility.trace('Fetching all GREs');
2710 fetch c_all_gres_for_further_info into l_tax_unit_id;
2711 exit when c_all_gres_for_further_info%NOTFOUND;
2712
2713 hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
2714 hr_utility.trace('Asgid is ' || l_asgid);
2715 hr_utility.trace('Effective date is '|| p_effective_date);
2716 begin
2717
2718 open c_get_max_asgactid_jd(to_number(lv_serial_number),
2719 l_tax_unit_id,
2720 l_year_start,
2721 l_year_end);
2722 fetch c_get_max_asgactid_jd into l_aaid;
2723 close c_get_max_asgactid_jd;
2724
2725 select target1.business_group_id
2726 into l_business_group_id
2727 from hr_all_organization_units target1
2728 where target1.organization_id = l_tax_unit_id;
2729
2730 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2731 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2732 result := result+nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2733 ( l_balance_type_tab(i),
2734 'YTD' ,
2735 l_aaid,
2736 l_asgid ,
2737 NULL,
2738 'PER' ,
2739 l_tax_unit_id,
2740 l_business_group_id,
2741 'QC'
2742 ),0) ;
2743 hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2744 hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2745 hr_utility.trace('Result is ' || to_char(result));
2746 exception when no_data_found then
2747 hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2748 end;
2749 end loop; -- end of c_all_gres_for_further_info
2750 close c_all_gres_for_further_info;
2751 ff_archive_api.create_archive_item(
2752 p_archive_item_id => l_archive_item_id
2753 ,p_user_entity_id =>
2754 get_footnote_user_entity_id(l_user_entity_name_tab(i))
2755 ,p_archive_value => result
2756 ,p_archive_type => 'AAP'
2757 ,p_action_id => p_assactid
2758 ,p_legislation_code => 'CA'
2759 ,p_object_version_number => l_object_version_number
2760 ,p_context_name1 => 'JURISDICTION_CODE'
2761 ,p_context1 => 'QC'
2762 ,p_some_warning => l_some_warning
2763 );
2764 /*Bug 13564765 sbachu*/
2765 if result is not null and result <> 0 then
2766 p_temp_nz_fi := p_temp_nz_fi +1;
2767 end if;
2768
2769 if result < 0 and l_negative_balance_exists <> 'Y' then
2770 l_negative_balance_exists := 'Y';
2771 end if;
2772 end loop;
2773
2774 p_negative_balance_exists := l_negative_balance_exists;
2775 hr_utility.trace('p_temp_nz_fi = '||p_temp_nz_fi);
2776 hr_utility.trace('End of Provincial YE Amendment PP Validation');
2777
2778 Exception when no_data_found then
2779 hr_utility.trace('Report type not found for given Payroll_action ');
2780 null;
2781 -- End of Provincial YE Amendment Pre-Process Validation
2782 end eoy_archive_further_info;
2783
2784 /* Name : eoy_archive_data
2785 Purpose : This performs the CA specific employee context setting for the
2786 Year End PreProcess.
2787 Arguments :
2788 Notes :
2789 */
2790
2791 PROCEDURE eoy_archive_data(p_assactid in number,
2792 p_effective_date in date) IS
2793
2794 l_aaid pay_assignment_actions.assignment_action_id%type;
2795 l_aaid1 pay_assignment_actions.assignment_action_id%type;
2796 l_aaseq pay_assignment_actions.action_sequence%type;
2797 l_asgid pay_assignment_actions.assignment_id%type;
2798 l_date_earned date;
2799 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2800 l_reporting_type varchar2(240);
2801 l_prev_tax_unit_id pay_assignment_actions.tax_unit_id%type := null;
2802 l_business_group_id number;
2803 l_year_start date;
2804 l_year_end date;
2805 l_context_no number := 60;
2806 l_count number := 0;
2807 l_jurisdiction varchar2(11);
2808 l_province_uei ff_user_entities.user_entity_id%type;
2809 l_county_uei ff_user_entities.user_entity_id%type;
2810 l_city_uei ff_user_entities.user_entity_id%type;
2811 l_county_sd_uei ff_user_entities.user_entity_id%type;
2812 l_city_sd_uei ff_user_entities.user_entity_id%type;
2813 l_province_abbrev pay_us_states.state_abbrev%type;
2814 l_county_name pay_us_counties.county_name%type;
2815 l_city_name pay_us_city_names.city_name%type;
2816 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
2817 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
2818 l_step number := 0;
2819 l_county_code varchar2(3);
2820 l_city_code varchar2(4);
2821 l_jursd_context_id ff_contexts.context_id%type;
2822 l_taxunit_context_id ff_contexts.context_id%type;
2823 l_seq_tab pay_ca_eoy_rl1_archive.number_data_type_table;
2824 l_context_id_tab pay_ca_eoy_rl1_archive.number_data_type_table;
2825 l_context_val_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2826 l_chunk number;
2827 l_payroll_action_id number;
2828 l_person_id number;
2829 l_defined_balance_id number;
2830 l_archive_item_id number;
2831 l_date_of_birth date;
2832 l_hire_date date;
2833 l_termination_date date;
2834 l_first_name varchar2(240);
2835 l_middle_name varchar2(240);
2836 l_last_name varchar2(240);
2837 l_employee_number varchar2(240);
2838 l_pre_name_adjunct varchar2(240);
2839 l_employee_phone_no varchar2(240);
2840 l_address_line1 varchar2(240);
2841 l_address_line2 varchar2(240);
2842 l_address_line3 varchar2(240);
2843 l_town_or_city varchar2(80);
2844 l_province_code varchar2(80);
2845 l_postal_code varchar2(80);
2846 l_telephone_number varchar2(80);
2847 l_country_code varchar2(80);
2848 l_counter number := 0;
2849
2850 l_count_start_for_boxo number := 0;
2851 l_count_end_for_boxo number := 0;
2852 l_count_for_boxo_code number := 0;
2853 l_pre_organization_id varchar2(80);
2854 l_national_identifier varchar2(240);
2855 l_user_entity_value_tab_boxo number := 0;
2856 l_user_entity_code_tab_boxo VARCHAR2(4) := NULL;
2857 l_object_version_number number;
2858 -- commented by sneelapa and redefined for bug 11654691
2859 -- l_rl1_slip_number number;
2860 l_rl1_slip_number varchar2(240);
2861 l_some_warning boolean;
2862 result number;
2863 l_no_of_payroll_run number := 0;
2864 l_has_been_paid varchar2(3) := 'N';
2865 l_user_entity_name_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2866 l_user_entity_value_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2867 l_balance_type_tab pay_ca_eoy_rl1_archive.char240_data_type_table;
2868 l_footnote_balance_type_tab varchar2(80);
2869 l_footnote_code varchar2(30);
2870 l_footnote_balance varchar2(80);
2871 l_footnote_amount number := 0;
2872 old_l_footnote_code varchar2(80) := null;
2873 old_balance_type_tab varchar2(80) := null;
2874 l_footnote_code_ue varchar2(80);
2875 l_footnote_amount_ue varchar2(80);
2876 l_no_of_fn_codes number := 0;
2877 l_value number := 0;
2878 fed_result number;
2879 non_taxable_earnings number;
2880 l_negative_balance_exists varchar2(5);
2881 l_boxr_flag varchar2(5);
2882
2883 ln_balance_value NUMBER := 0;
2884 ln_no_gross_earnings NUMBER := 0;
2885
2886 l_messages VARCHAR2(240);
2887 l_prev_messages VARCHAR2(240);
2888 l_mesg_amt NUMBER(12,2);
2889 l_total_mesg_amt NUMBER(12,2);
2890
2891 l_action_information_id_1 NUMBER ;
2892 l_object_version_number_1 NUMBER ;
2893 ln_tax_unit_id NUMBER ;
2894 ln_prev_tax_unit_id NUMBER ;
2895 ld_eff_date DATE ;
2896 ld_prev_eff_date DATE ;
2897 ln_assignment_action_id NUMBER;
2898
2899 ln_status_indian NUMBER := 0;
2900 ln_boxo_exclude_from_boxa NUMBER := 0;
2901 lv_footnote_bal varchar2(80);
2902
2903 /* added these 3 new variables for 11510 changes bug#3356533 */
2904 l_ft_aaid pay_assignment_actions.assignment_action_id%type;
2905 l_ft_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2906 l_ft_reporting_type varchar2(240);
2907 lv_serial_number varchar2(30);
2908
2909 /* new variables added for Provincial YE Amendment PP */
2910 lv_fapp_effective_date varchar2(5);
2911 ln_fapp_pre_org_id number;
2912 lv_fapp_report_type varchar2(20);
2913 ln_fapp_locked_action_id number;
2914 lv_fapp_prov varchar2(5);
2915 lv_fapp_flag varchar2(2):= 'N';
2916 lv_fapp_locked_actid_reptype varchar2(20);
2917 ln_fapp_prev_amend_actid number;
2918
2919 /* new variables added for pre-printed form number */
2920 lv_eit_year varchar2(30);
2921 lv_eit_pre_org_id varchar2(40);
2922 lv_eit_form_no varchar2(20);
2923 ln_form_no_archived varchar2(2);
2924
2925 lv_footnote_element varchar2(50);
2926
2927 lv_max_pensionable_earnings number;
2928 lv_qpp_pensionable_earnings number;
2929 lv_cpp_pensionable_earnings number;
2930 lv_total_pensionable_earnings number;
2931 lv_taxable_benefit_with_no_rem number;
2932 lv_tax_ben_no_rem_all_prov number; /*Bug 15914635*/
2933 lv_non_box_lookup number;
2934
2935 /*Bug 13564765 sbachu*/
2936 ln_no_nz_fi number := 0; /* Number of non zero further info codes*/
2937 ln_no_fi_per_slip number := 4; /* Number of further info codes per slip */
2938 l_rl1_seq_number varchar2(240);
2939 l_k number;
2940 lv_pre_pr_form_no varchar2(240);
2941
2942 /*Bug 15886329 sbachu*/
2943 ln_qpp_txble_arch_item_id number := 0;
2944 ln_qpp_rate number := 0;
2945 ln_max_cpp_earnings number := 0;
2946 lv_qpp_to_be_rprted varchar2(240) := NULL;
2947 lv_archived_qpp_whld varchar2(240) := NULL;
2948 lv_archived_qpp_txble varchar2(240) := NULL;
2949
2950 /* !!Report type 'RL1' or 'RL2' in the GRE might have
2951 to be checked too-Check */
2952
2953 cursor c_all_gres(asgactid number) is
2954 select hoi.organization_id ,
2955 hoi.org_information5
2956 from pay_payroll_actions ppa,
2957 pay_assignment_actions paa,
2958 hr_organization_information hoi
2959 where paa.assignment_action_id = asgactid
2960 and ppa.payroll_action_id = paa.payroll_action_id
2961 and hoi.org_information2 =
2962 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2963 ppa.legislative_parameters)
2964 and hoi.org_information_context = 'Canada Employer Identification'
2965 and hoi.org_information5 in ('T4/RL1','T4A/RL1')
2966 order by organization_id;
2967
2968 cursor c_all_gres_for_footnote(asgactid number) is
2969 select hoi.organization_id ,
2970 hoi.org_information5
2971 from pay_payroll_actions ppa,
2972 pay_assignment_actions paa,
2973 hr_organization_information hoi
2974 where paa.assignment_action_id = asgactid
2975 and ppa.payroll_action_id = paa.payroll_action_id
2976 and hoi.org_information2 =
2977 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2978 ppa.legislative_parameters)
2979 and hoi.org_information_context = 'Canada Employer Identification'
2980 and hoi.org_information5 in ('T4/RL1','T4A/RL1')
2981 order by organization_id;
2982
2983 /* !!To calculate CPP withheld select all the GREs
2984 the person has worked in */
2985
2986 /* 11510 changes for bug#3356533, replaced the old query for
2987 cursor c_all_gres_for_person with this to improve performance.
2988 Using assignment_id instead of assignment_action_id
2989 */
2990 cursor c_all_gres_for_person(cp_asg_id number,cp_eff_date date) is
2991 select distinct paa.tax_unit_id
2992 from pay_assignment_actions paa,
2993 pay_payroll_actions ppa,
2994 per_all_assignments_f paf
2995 where paa.assignment_id = cp_asg_id
2996 and paf.assignment_id = cp_asg_id
2997 and paf.assignment_id = paa.assignment_id
2998 and paa.action_status = 'C'
2999 and ppa.payroll_action_id = paa.payroll_action_id
3000 and ppa.effective_date <= cp_eff_date
3001 and ppa.action_type in ('R', 'Q')
3002 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
3003 and exists ( select 1 from pay_run_types_f prt
3004 where prt.legislation_code = 'CA'
3005 and prt.run_type_id = paa.run_type_id
3006 and prt.run_method <> 'C' );
3007
3008 /* Get the jurisdiction code of all the cities
3009 for the person_id corresponding to the
3010 assignment_id . Take it from pay_action_context table. */
3011
3012 cursor c_get_province is
3013 select distinct context_value
3014 from pay_action_contexts pac
3015 where pac.assignment_id = l_asgid;
3016
3017 cursor c_footnote_info(p_balance_name varchar2) is
3018 select distinct pet.element_information19,
3019 pbt1.balance_name
3020 from pay_balance_feeds_f pbf,
3021 pay_balance_types pbt,
3022 pay_balance_types pbt1,
3023 pay_input_values_f piv,
3024 pay_element_types_f pet,
3025 fnd_lookup_values flv
3026 where pbt.balance_name = p_balance_name
3027 and pbf.balance_type_id = pbt.balance_type_id
3028 and pbf.input_value_id = piv.input_value_id
3029 and piv.element_type_id = pet.element_type_id
3030 and pbt1.balance_type_id = pet.element_information10
3031 and pet.business_group_id = l_business_group_id
3032 and pet.element_information19 = flv.lookup_code
3033 and flv.lookup_type = 'PAY_CA_RL1_FOOTNOTES'
3034 --bug 5558604 starts
3035 and flv.enabled_flag = 'Y'
3036 and l_date_earned between nvl(flv.start_Date_active,l_date_earned)
3037 and nvl(flv.end_date_Active,l_date_earned)
3038 --bug 5558604 starts
3039 and flv.language = userenv('LANG')
3040 order by pet.element_information19;
3041
3042 cursor c_get_addr is
3043 select addr.address_line1,
3044 addr.address_line2,
3045 addr.address_line3,
3046 addr.town_or_city,
3047 decode(addr.country,'CA', addr.region_1 , 'US' , addr.region_2 , ' '),
3048 replace(addr.postal_code,' '),
3049 addr.telephone_number_1,
3050 country.territory_code
3051 from per_addresses addr,
3052 fnd_territories_vl country
3053 where addr.person_id = l_person_id
3054 and addr.primary_flag = 'Y'
3055 and l_date_earned between nvl(addr.date_from, l_date_earned)
3056 and nvl(addr.date_to, l_date_earned)
3057 and country.territory_code = addr.country
3058 order by date_from desc;
3059
3060 /* Modified the cursor to fix bug#3641353 and added
3061 action_type 'B' to consider Balance Adjustments */
3062 cursor cur_non_box_mesg( cp_asgactid in number,
3063 cp_eff_date in date,
3064 cp_start_date in date ) is
3065 select /*+ index (PET PAY_ELEMENT_TYPES_F_PK) */ distinct prrv1.result_value,
3066 prrv2.result_value,
3067 hoi.organization_id,
3068 run_ppa.effective_date,
3069 run_paa.assignment_action_id
3070 from pay_run_result_values prrv1
3071 , pay_run_result_values prrv2
3072 , pay_run_results prr
3073 , pay_element_types_f pet
3074 , pay_input_values_f piv1
3075 , pay_input_values_f piv2
3076 , pay_assignment_actions run_paa
3077 , pay_payroll_actions run_ppa
3078 , pay_assignment_actions arch_paa
3079 , pay_payroll_actions arch_ppa
3080 , per_all_assignments_f arch_paf
3081 , per_all_assignments_f all_paf
3082 , hr_all_organization_units hou
3083 , hr_organization_information hoi
3084 where arch_paa.assignment_action_id = cp_asgactid
3085 and arch_ppa.payroll_action_id = arch_paa.payroll_action_id
3086 and hou.business_group_id + 0 = arch_ppa.business_group_id
3087 and hou.organization_id = hoi.organization_id
3088 and hoi.org_information2 = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
3089 arch_ppa.legislative_parameters)
3090 and hoi.org_information_context = 'Canada Employer Identification'
3091 and run_paa.assignment_id = arch_paa.assignment_id
3092 and run_paa.tax_unit_id = hou.organization_id
3093 and run_ppa.payroll_action_id = run_paa.payroll_action_id
3094 and run_ppa.action_type in ( 'R', 'Q','B', 'V' )
3095 -- Added 'V' for bug 11065493
3096 and run_ppa.effective_date between cp_start_date and cp_eff_date
3097 and run_paa.action_status = 'C'
3098 and pet.element_name = lv_footnote_element --'RL1 NonBox Footnotes'
3099 and prr.assignment_action_id = run_paa.assignment_action_id
3100 and prr.element_type_id = pet.element_type_id
3101 and piv1.element_type_id = pet.element_type_id
3102 and piv1.name = 'Message'
3103 and prrv1.run_result_id = prr.run_result_id
3104 and prrv1.input_value_id = piv1.input_value_id
3105 and piv2.element_type_id = pet.element_type_id
3106 and piv2.name = 'Amount'
3107 and prrv2.run_result_id = prrv1.run_result_id
3108 and prrv2.input_value_id = piv2.input_value_id
3109 and arch_paf.assignment_id = arch_paa.assignment_id
3110 and cp_eff_date
3111 between arch_paf.effective_start_date
3112 and arch_paf.effective_end_date
3113 and all_paf.person_id = arch_paf.person_id
3114 and cp_eff_date
3115 between all_paf.effective_start_date
3116 and all_paf.effective_end_date
3117 and run_paa.assignment_id = all_paf.assignment_id
3118 and exists (select 1
3119 from pay_action_contexts pac,ff_contexts ffc
3120 where ffc.context_name = 'JURISDICTION_CODE'
3121 and pac.context_id + 0 = ffc.context_id
3122 and pac.assignment_id = run_paa.assignment_id
3123 and pac.context_value = 'QC')
3124 order by 1; --Bug 6853279
3125 /* and exists (select 1 --Bug 7555410
3126 from hr_lookups hrl
3127 where hrl.lookup_code=prrv1.result_value
3128 and lookup_type='PAY_CA_RL1_NONBOX_FOOTNOTES'
3129 and cp_eff_date
3130 between nvl(hrl.start_date_active,to_date('1900/01/01','YYYY/MM/DD'))
3131 and nvl(hrl.end_date_active,to_date('4712/12/31','YYYY/MM/DD'))) */
3132
3133
3134 /*For performance of non box footnote amounts - bug 8227027 */
3135 cursor c_non_box_lookup is
3136 select 1
3137 from hr_lookups hrl
3138 where hrl.lookup_code=l_messages
3139 and lookup_type='PAY_CA_RL1_NONBOX_FOOTNOTES'
3140 and p_effective_date
3141 between nvl(hrl.start_date_active,to_date('1900/01/01','YYYY/MM/DD'))
3142 and nvl(hrl.end_date_active,to_date('4712/12/31','YYYY/MM/DD'));
3143
3144
3145 /* New cursors added for Provincial YE Amendment Pre-Process Validation */
3146 CURSOR c_get_fapp_prov_emp(cp_assignment_action_id number) IS
3147 select fai.value
3148 from ff_archive_items fai,
3149 ff_database_items fdi
3150 where fdi.user_entity_id = fai.user_entity_id
3151 and fai.context1 = cp_assignment_action_id
3152 and fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT';
3153
3154 CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
3155 select ppa.report_type
3156 from pay_payroll_actions ppa,pay_assignment_actions paa
3157 where paa.assignment_action_id = cp_locked_actid
3158 and ppa.payroll_action_id = paa.payroll_action_id;
3159
3160 CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
3161 select locked_action_id
3162 from pay_action_interlocks
3163 where locking_action_id = cp_locking_act_id;
3164
3165 CURSOR c_get_preprinted_form_no (cp_person_id number,
3166 cp_pre_org_id number) IS
3167 select pei_information5,
3168 pei_information6,
3169 pei_information7
3170 from per_people_extra_info
3171 where person_id = cp_person_id
3172 and pei_information6 = to_char(cp_pre_org_id)
3173 and pei_information_category = 'PAY_CA_RL1_FORM_NO'
3174 order by pei_information7; /* Bug 13564765 sbachu*/
3175
3176 /* 11510 Changes Bug#3356533. Changed the cursor to get max asgact_id
3177 based on person_id, to fix bug#3638928. */
3178 CURSOR c_get_max_asgactid_jd(cp_person_id number,
3179 cp_tax_unit_id number,
3180 cp_period_start date,
3181 cp_period_end date
3182 ) IS
3183 select /*+ Ordered */ paa.assignment_action_id
3184 from per_all_people_f ppf,
3185 per_all_assignments_f paf,
3186 pay_assignment_actions paa,
3187 pay_payroll_actions ppa,
3188 pay_action_classifications pac,
3189 pay_action_contexts pac1,
3190 ff_contexts fc
3191 where ppf.person_id = cp_person_id
3192 and paf.person_id = ppf.person_id
3193 and paf.assignment_id = paa.assignment_id
3194 and paa.tax_unit_id = cp_tax_unit_id
3195 and ppa.payroll_action_id = paa.payroll_action_id
3196 and ppa.effective_date between cp_period_start and cp_period_end
3197 and ppa.effective_date between ppf.effective_start_date
3198 and ppf.effective_end_date
3199 and ppa.effective_date between paf.effective_start_date
3200 and paf.effective_end_date
3201 and ppa.action_type = pac.action_type
3202 and pac.classification_name = 'SEQUENCED'
3203 AND pac1.assignment_action_id = paa.assignment_action_id
3204 AND pac1.context_id = fc.context_id
3205 AND fc.context_name = 'JURISDICTION_CODE'
3206 AND pac1.context_value = 'QC'
3207 order by paa.action_sequence desc;
3208
3209 /* 11510 changes for bug#3356533. Changed the cursor to get max asgact_id
3210 based on person_id, to fix bug#3638928. */
3211 CURSOR c_get_max_asgactid(cp_person_id number,
3212 cp_tax_unit_id number,
3213 cp_period_start date,
3214 cp_period_end date) IS
3215 select paa.assignment_action_id
3216 from pay_assignment_actions paa,
3217 per_all_assignments_f paf,
3218 per_all_people_f ppf,
3219 pay_payroll_actions ppa,
3220 pay_action_classifications pac
3221 where ppf.person_id = cp_person_id
3222 and paf.person_id = ppf.person_id
3223 and paf.assignment_id = paa.assignment_id
3224 and paa.tax_unit_id = cp_tax_unit_id
3225 and ppa.payroll_action_id = paa.payroll_action_id
3226 and ppa.effective_date between cp_period_start and cp_period_end
3227 and ppa.effective_date between ppf.effective_start_date
3228 and ppf.effective_end_date
3229 and ppa.effective_date between paf.effective_start_date
3230 and paf.effective_end_date
3231 and ppa.action_type = pac.action_type
3232 and pac.classification_name = 'SEQUENCED'
3233 order by paa.action_sequence desc;
3234
3235 BEGIN
3236
3237 --hr_utility.trace_on(null,'RL1');
3238 hr_utility.set_location ('archive_data',1);
3239 hr_utility.trace('getting assignment');
3240
3241 l_negative_balance_exists := 'N';
3242 lv_qpp_pensionable_earnings := 0;
3243 l_step := 1;
3244
3245 SELECT aa.assignment_id,
3246 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
3247 aa.tax_unit_id,
3248 aa.chunk_number,
3249 aa.payroll_action_id,
3250 aa.serial_number
3251 into l_asgid,
3252 l_date_earned,
3253 l_tax_unit_id,
3254 l_chunk,
3255 l_payroll_action_id,
3256 lv_serial_number
3257 FROM pay_assignment_actions aa
3258 WHERE aa.assignment_action_id = p_assactid;
3259
3260 /* If the chunk of the assignment is same as the minimun chunk
3261 for the payroll_action_id and the gre data has not yet been
3262 archived then archive the gre data i.e. the employer data */
3263
3264 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3265
3266 hr_utility.trace('eoy_archive_data archiving employer data');
3267 hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
3268
3269 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
3270 legislative_parameters),
3271 business_group_id
3272 into l_pre_organization_id,l_business_group_id
3273 from pay_payroll_actions
3274 where payroll_action_id = l_payroll_action_id;
3275
3276 eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
3277 p_pre_organization_id=>l_pre_organization_id);
3278
3279 hr_utility.trace('eoy_archive_data archived employer data');
3280
3281 end if;
3282
3283 hr_utility.set_location ('archive_data',2);
3284
3285 hr_utility.trace('assignment '|| to_char(l_asgid));
3286 hr_utility.trace('date_earned '|| to_char(l_date_earned));
3287 hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
3288 hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
3289
3290 /* Derive the beginning and end of the effective year */
3291
3292 hr_utility.trace('getting begin and end dates');
3293
3294 l_step := 2;
3295
3296 l_year_start := trunc(p_effective_date, 'Y');
3297 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3298
3299 hr_utility.trace('year start '|| to_char(l_year_start));
3300 hr_utility.trace('year end '|| to_char(l_year_end));
3301
3302 if to_number(to_char(l_year_end,'YYYY')) > 2005 then
3303 lv_footnote_element := 'RL1 Non Box Footnotes';
3304 else
3305 lv_footnote_element := 'RL1 NonBox Footnotes';
3306 end if;
3307
3308 /* Initialise the PL/SQL table before populating it */
3309
3310 hr_utility.trace('Initialising Pl/SQL table');
3311
3312 l_step := 3;
3313
3314 /* Get the context_id for 'Jurisdiction' from ff_contexts */
3315
3316 l_step := 5;
3317
3318 select context_id
3319 into l_jursd_context_id
3320 from ff_contexts
3321 where context_name = 'JURISDICTION_CODE';
3322
3323 select context_id
3324 into l_taxunit_context_id
3325 from ff_contexts
3326 where context_name = 'TAX_UNIT_ID';
3327
3328 l_step := 6;
3329
3330 l_jurisdiction := 'QC';
3331
3332 l_step := 12;
3333
3334 l_count := l_count + 1;
3335
3336 hr_utility.trace('archiving CAEOY_RL1_PROVINCE_OF_EMPLOYMENT');
3337
3338 ff_archive_api.create_archive_item(
3339 /*p_validate => 'TRUE' */
3340 p_archive_item_id => l_archive_item_id
3341 ,p_user_entity_id =>
3342 get_user_entity_id('CAEOY_RL1_PROVINCE_OF_EMPLOYMENT')
3343 ,p_archive_value => l_jurisdiction
3344 ,p_archive_type => 'AAP'
3345 ,p_action_id => p_assactid
3346 ,p_legislation_code => 'CA'
3347 ,p_object_version_number => l_object_version_number
3348 ,p_some_warning => l_some_warning
3349 );
3350
3351 hr_utility.trace('archived caeoy_rl1_employment_province');
3352
3353 /* We can archive the balance level dbis also because for employee level
3354 balances jurisdiction is always a context. */
3355
3356 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3357
3358 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3359 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
3360
3361 hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
3362 /* RL1 Slip number generation part has been moved
3363 as a seperate function for bug 13564765*/
3364
3365 l_count := 0;
3366
3367 l_count := l_count + 1;
3368 l_user_entity_name_tab(l_count) := 'CAEOY_GROSS_EARNINGS_PER_JD_YTD';
3369 l_balance_type_tab(l_count) := 'Gross Earnings';
3370
3371 l_count := l_count + 1;
3372 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_WITHHELD_PER_JD_YTD';
3373 l_balance_type_tab(l_count) := 'QPP EE Withheld';
3374 /**********************************************************/
3375 l_count := l_count + 1;
3376 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD';
3377 l_balance_type_tab(l_count) := 'PPIP EE Withheld';
3378 /****************************tombi******************/
3379 l_count := l_count + 1;
3380 l_user_entity_name_tab(l_count) := 'CAEOY_EI_EE_WITHHELD_PER_JD_YTD';
3381 l_balance_type_tab(l_count) := 'EI EE Withheld';
3382
3383 /* Quebec Income tax withheld */
3384 l_count := l_count + 1;
3385 l_user_entity_name_tab(l_count) := 'CAEOY_PROV_WITHHELD_PER_JD_YTD';
3386 l_balance_type_tab(l_count) := 'PROV Withheld';
3387
3388 /* Registered pension plan */
3389 l_count := l_count + 1;
3390 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXD_PER_JD_YTD';
3391 l_balance_type_tab(l_count) := 'RL1_BOXD';
3392
3393 /* Union Dues */
3394 l_count := l_count + 1;
3395 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXF_PER_JD_YTD';
3396 l_balance_type_tab(l_count) := 'RL1_BOXF';
3397
3398 /* Pensionable Earnings under Quebec pension plan */
3399 l_count := l_count + 1;
3400 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD';
3401 l_balance_type_tab(l_count) := 'QPP EE Taxable';
3402
3403 /**********************************************/
3404 l_count := l_count + 1;
3405 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD';
3406 l_balance_type_tab(l_count) := 'PPIP EE Taxable';
3407 /***************tombi************************/
3408
3409 /* QPP EE Basic Exemption ( EOY 2001 for YE Exemption Report ) */
3410 l_count := l_count + 1;
3411 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_BASIC_EXEMPTION_PER_JD_YTD';
3412 l_balance_type_tab(l_count) := 'QPP EE Basic Exemption';
3413
3414 /* QPP Exempt ( EOY 2001 for YE Exemption Report ) */
3415 l_count := l_count + 1;
3416 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_EXEMPT_PER_JD_YTD';
3417 l_balance_type_tab(l_count) := 'QPP Exempt';
3418
3419 /* QPP Reduced Subject for Box G (EOY 2004) */
3420 l_count := l_count + 1;
3421 l_user_entity_name_tab(l_count) := 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD';
3422 l_balance_type_tab(l_count) := 'QPP Reduced Subject';
3423
3424 /* PPIP Reduced Subject for Box I (EOY 2006) */
3425 l_count := l_count + 1;
3426 l_user_entity_name_tab(l_count) := 'CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD';
3427 l_balance_type_tab(l_count) := 'PPIP Reduced Subject';
3428
3429 /* Meals and accommodations */
3430 l_count := l_count + 1;
3431 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXV_PER_JD_YTD';
3432 l_balance_type_tab(l_count) := 'RL1_BOXV';
3433 --l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXH_PER_JD_YTD';
3434 --l_balance_type_tab(l_count) := 'RL1_BOXH';
3435
3436 /* Use of a motor vehicle for personal purpose */
3437 l_count := l_count + 1;
3438 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXW_PER_JD_YTD';
3439 l_balance_type_tab(l_count) := 'RL1_BOXW';
3440 --l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXI_PER_JD_YTD';
3441 --l_balance_type_tab(l_count) := 'RL1_BOXI';
3442
3443 /* Contribution paid by the employer by the employer under
3444 a private health */
3445 l_count := l_count + 1;
3446 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXJ_PER_JD_YTD';
3447 l_balance_type_tab(l_count) := 'RL1_BOXJ';
3448
3449 /* Trips made by residents of designated remote areas */
3450 l_count := l_count + 1;
3451 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXK_PER_JD_YTD';
3452 l_balance_type_tab(l_count) := 'RL1_BOXK';
3453
3454 /* Other Benefits */
3455 l_count := l_count + 1;
3456 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXL_PER_JD_YTD';
3457 l_balance_type_tab(l_count) := 'RL1_BOXL';
3458
3459 /* Commissions included in amount in box A */
3460 l_count := l_count + 1;
3461 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXM_PER_JD_YTD';
3462 l_balance_type_tab(l_count) := 'RL1_BOXM';
3463
3464 /* Charitable Donations */
3465 l_count := l_count + 1;
3466 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXN_PER_JD_YTD';
3467 l_balance_type_tab(l_count) := 'RL1_BOXN';
3468
3469 l_count := l_count + 1;
3470 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RA_PER_JD_YTD';
3471 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RA';
3472 l_count_start_for_boxo := l_count;
3473
3474 l_count := l_count + 1;
3475 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RB_PER_JD_YTD';
3476 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RB';
3477
3478 l_count := l_count + 1;
3479 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RC_PER_JD_YTD';
3480 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RC';
3481
3482 l_count := l_count + 1;
3483 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RD_PER_JD_YTD';
3484 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RD';
3485
3486 /* Bug 7555410 */
3487 IF ( to_number(to_char(l_year_end,'YYYY')) < 2008) then
3488 l_count := l_count + 1;
3489 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RF_PER_JD_YTD';
3490 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RF';
3491 END IF;
3492
3493 l_count := l_count + 1;
3494 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RG_PER_JD_YTD';
3495 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RG';
3496
3497 l_count := l_count + 1;
3498 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RH_PER_JD_YTD';
3499 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RH';
3500
3501 l_count := l_count + 1;
3502 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RI_PER_JD_YTD';
3503 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RI';
3504
3505 l_count := l_count + 1;
3506 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RJ_PER_JD_YTD';
3507 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RJ';
3508
3509 l_count := l_count + 1;
3510 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RK_PER_JD_YTD';
3511 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RK';
3512
3513 l_count := l_count + 1;
3514 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RL_PER_JD_YTD';
3515 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RL';
3516
3517 l_count := l_count + 1;
3518 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RM_PER_JD_YTD';
3519 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RM';
3520
3521 l_count := l_count + 1;
3522 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RN_PER_JD_YTD';
3523 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RN';
3524
3525 l_count := l_count + 1;
3526 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RO_PER_JD_YTD';
3527 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RO';
3528
3529 l_count := l_count + 1;
3530 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RP_PER_JD_YTD';
3531 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RP';
3532
3533 l_count := l_count + 1;
3534 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RQ_PER_JD_YTD';
3535 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RQ';
3536
3537 l_count := l_count + 1;
3538 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RR_PER_JD_YTD';
3539 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RR';
3540
3541 l_count := l_count + 1;
3542 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RS_PER_JD_YTD';
3543 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RS';
3544
3545 l_count := l_count + 1;
3546 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RT_PER_JD_YTD';
3547 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RT';
3548
3549 l_count := l_count + 1;
3550 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RU_PER_JD_YTD';
3551 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RU';
3552
3553 l_count := l_count + 1;
3554 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RV_PER_JD_YTD';
3555 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RV';
3556
3557 --Bug 6525899. Added check to not to archive this balance from 2007
3558 IF ( to_number(to_char(l_year_end,'YYYY')) < 2007) then
3559 l_count := l_count + 1;
3560 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RW_PER_JD_YTD';
3561 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RW';
3562 END IF;
3563 --End 6525899
3564
3565 /* Added balance RL1_BOXO_AMOUNT_RX for Bug 7555410 */
3566 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2008) then
3567 l_count := l_count + 1;
3568 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_RX_PER_JD_YTD';
3569 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_RX';
3570 END IF;
3571
3572 /* Added balance RL1_BOXO_AMOUNT_CA for Bug 9135372 */
3573 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2009) then
3574 l_count := l_count + 1;
3575 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_CA_PER_JD_YTD';
3576 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_CA';
3577 END IF;
3578
3579 /* Added balance RL1_BOXO_AMOUNT_CB for Bug 9135372 */
3580 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2009) then
3581 l_count := l_count + 1;
3582 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_CB_PER_JD_YTD';
3583 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_CB';
3584 END IF;
3585
3586 /* Added balance RL1_BOXO_AMOUNT_CC for Bug 9135372 */
3587 IF ( to_number(to_char(l_year_end,'YYYY')) >= 2009) then
3588 l_count := l_count + 1;
3589 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXO_AMOUNT_CC_PER_JD_YTD';
3590 l_balance_type_tab(l_count) := 'RL1_BOXO_AMOUNT_CC';
3591 END IF;
3592
3593 l_count_end_for_boxo := l_count;
3594
3595 /* Contributions to a multi-employer insurance plan */
3596 l_count := l_count + 1;
3597 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXP_PER_JD_YTD';
3598 l_balance_type_tab(l_count) := 'RL1_BOXP';
3599
3600 /* Deferred salary or wages */
3601 l_count := l_count + 1;
3602 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXQ_PER_JD_YTD';
3603 l_balance_type_tab(l_count) := 'RL1_BOXQ';
3604
3605 /* Tax exempt income paid to an Indian */
3606 l_count := l_count + 1;
3607 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXR_PER_JD_YTD';
3608 l_balance_type_tab(l_count) := 'PROV STATUS INDIAN Subject';
3609
3610 /* Tips received */
3611 l_count := l_count + 1;
3612 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXS_PER_JD_YTD';
3613 l_balance_type_tab(l_count) := 'RL1_BOXS';
3614
3615 /* Tips allocated */
3616 l_count := l_count + 1;
3617 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXT_PER_JD_YTD';
3618 l_balance_type_tab(l_count) := 'RL1_BOXT';
3619
3620 /* Phased retirement */
3621 l_count := l_count + 1;
3622 l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXU_PER_JD_YTD';
3623 l_balance_type_tab(l_count) := 'RL1_BOXU';
3624
3625 hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
3626
3627 for i in 1 .. l_count
3628 loop
3629 hr_utility.trace('Initialising values');
3630 l_user_entity_value_tab(i) := 0;
3631 end loop;
3632
3633 open c_all_gres(p_assactid);
3634
3635 loop
3636
3637 hr_utility.trace('Fetching all GREs');
3638 fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3639 exit when c_all_gres%NOTFOUND;
3640
3641 hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3642 hr_utility.trace('Asgid is ' || to_char(l_asgid));
3643 hr_utility.trace('Person id is ' || lv_serial_number);
3644 hr_utility.trace('Reporting_type is ' || l_reporting_type);
3645 hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3646
3647 begin
3648 /* Removed select stmt to get max asgact_id and replaced it with
3649 cursor c_get_max_asgactid_jd. 11510 Changes Bug#3356533.
3650 Changed the cursor to get max asgact_id based on person_id to
3651 fix bug#3638928 */
3652 open c_get_max_asgactid_jd(to_number(lv_serial_number),
3653 l_tax_unit_id,
3654 l_year_start,
3655 l_year_end);
3656 fetch c_get_max_asgactid_jd into l_aaid;
3657 close c_get_max_asgactid_jd;
3658
3659 hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3660 hr_utility.trace('l_count is ' || to_char(l_count));
3661
3662 ln_no_gross_earnings := ln_no_gross_earnings +
3663 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3664 ('RL1 No Gross Earnings',
3665 'YTD' ,
3666 l_aaid,
3667 l_asgid,
3668 NULL,
3669 'PER' ,
3670 l_tax_unit_id,
3671 l_business_group_id,
3672 'QC'
3673 ),0);
3674
3675 l_no_of_payroll_run := l_no_of_payroll_run + 1;
3676
3677 select target1.business_group_id
3678 into l_business_group_id
3679 from hr_all_organization_units target1
3680 where target1.organization_id = l_tax_unit_id;
3681
3682 if l_tax_unit_id <> l_prev_tax_unit_id or
3683 l_prev_tax_unit_id is null then
3684
3685 hr_utility.trace('l_business_group_id is '||l_business_group_id);
3686
3687 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3688 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3689 Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3690
3691 for i in 1 .. l_count
3692 loop
3693
3694 hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
3695 hr_utility.trace('i is ' || i);
3696
3697 /* T4A earnings should not go to BOX A of RL1 */
3698
3699 /* bug 5768390
3700 if l_reporting_type = 'T4A/RL1' and
3701 l_balance_type_tab(i) = 'Gross Earnings'
3702 then
3703 null;
3704 else
3705 bug 5768390 */
3706
3707 /* l_user_entity_value_tab(i) := 0; */
3708
3709 if l_balance_type_tab(i) = 'Gross Earnings' then
3710
3711 fed_result :=
3712 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3713 ('Taxable Benefits for Federal',
3714 'YTD' ,
3715 l_aaid,
3716 l_asgid ,
3717 NULL,
3718 'PER' ,
3719 l_tax_unit_id,
3720 l_business_group_id,
3721 'QC'
3722 ),0);
3723
3724 non_taxable_earnings :=
3725 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3726 ('RL1 Non Taxable Earnings',
3727 'YTD' ,
3728 l_aaid,
3729 l_asgid ,
3730 NULL,
3731 'PER' ,
3732 l_tax_unit_id,
3733 l_business_group_id,
3734 'QC'
3735 ),0);
3736
3737 hr_utility.trace('Fed Result = ' || fed_result);
3738 hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
3739 else
3740 fed_result := 0;
3741 non_taxable_earnings := 0;
3742 hr_utility.trace('Fed Result = ' || fed_result);
3743 hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
3744 end if;
3745
3746 ln_balance_value :=
3747 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3748 ( l_balance_type_tab(i),
3749 'YTD' ,
3750 l_aaid,
3751 l_asgid ,
3752 NULL,
3753 'PER' ,
3754 l_tax_unit_id,
3755 l_business_group_id,
3756 'QC'
3757 ),0);
3758
3759 /* Get QPP Pensionable Earnings for use when processing nonbox footnotes */
3760 if l_balance_type_tab(i) = 'QPP EE Taxable' then
3761 lv_qpp_pensionable_earnings := lv_qpp_pensionable_earnings + ln_balance_value;
3762 end if;
3763
3764 hr_utility.trace('Balance value is '|| ln_balance_value);
3765
3766 if ln_balance_value <> 0 then
3767 l_has_been_paid := 'Y';
3768 if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
3769 ln_status_indian := ln_status_indian +
3770 ln_balance_value;
3771 end if;
3772 end if;
3773
3774 if instr(l_balance_type_tab(i), 'RL1_BOXO') > 0 and
3775 ln_balance_value <> 0 then
3776
3777 /* bug 5768390
3778 if l_reporting_type <> 'T4A/RL1' then
3779 bug 5768390 */
3780 ln_boxo_exclude_from_boxa := ln_boxo_exclude_from_boxa +
3781 ln_balance_value;
3782 /* bug 5768390
3783 end if;
3784 bug 5768390 */
3785
3786
3787 hr_utility.trace('REPORT_TYPE '||l_reporting_type);
3788 hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
3789 hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
3790 hr_utility.trace('Assignemnt ID '|| l_asgid);
3791 hr_utility.trace('ln_boxo_exclude_from_boxa '||
3792 ln_boxo_exclude_from_boxa);
3793
3794 end if;
3795 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) +
3796 ln_balance_value -
3797 fed_result -
3798 non_taxable_earnings;
3799
3800 /* bug 5768390
3801 end if;
3802 bug 5768390 */
3803
3804 hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
3805 l_prev_tax_unit_id := l_tax_unit_id ;
3806
3807 end loop;
3808 end if;
3809
3810 exception
3811 when no_data_found then
3812 hr_utility.trace('This Tax unit id has no payroll run, so skip it');
3813 end;
3814 end loop;
3815 close c_all_gres;
3816
3817 hr_utility.trace('l_no_of_payroll_run is ' || l_no_of_payroll_run);
3818
3819 if ((l_no_of_payroll_run > 0) and
3820 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3821 if to_number(to_char(l_year_end,'YYYY')) >= 2011 then
3822 eoy_archive_further_info(p_assactid,p_effective_date,l_negative_balance_exists,ln_no_nz_fi); /* bug 13564765*/
3823 end if;
3824 hr_utility.trace('ln_no_nz_fi4 = '||ln_no_nz_fi);
3825 -- hr_utility.trace_on('Y','RL1');
3826 for i in 1 .. l_count
3827 loop
3828
3829 hr_utility.trace('in the create_archive_item loop');
3830 hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3831 hr_utility.trace('archive value is ');
3832
3833 /* Archiving footnotes */
3834
3835 old_l_footnote_code := NULL;
3836 old_balance_type_tab := NULL;
3837
3838 hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3839 hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3840
3841 if l_user_entity_value_tab(i) <> 0 then
3842
3843 if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
3844 l_footnote_balance_type_tab := 'RL1_BOXR';
3845 elsif l_balance_type_tab(i) = 'Gross Earnings' then
3846 l_footnote_balance_type_tab := 'RL1_BOXA';
3847 if ln_status_indian <> 0 then
3848 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) -
3849 ln_status_indian;
3850 ln_status_indian := 0;
3851 end if;
3852 if ln_boxo_exclude_from_boxa <> 0 then
3853 l_user_entity_value_tab(i) := l_user_entity_value_tab(i) -
3854 ln_boxo_exclude_from_boxa;
3855 ln_boxo_exclude_from_boxa := 0;
3856 end if;
3857 else
3858 l_footnote_balance_type_tab := l_balance_type_tab(i);
3859 end if;
3860
3861 if l_footnote_balance_type_tab in ('RL1_BOXA',
3862 'RL1_BOXD',
3863 'RL1_BOXK',
3864 'RL1_BOXR',
3865 'RL1_BOXQ',
3866 'RL1_BOXO_AMOUNT_RL',
3867 'RL1_BOXO_AMOUNT_RN') then
3868 begin
3869
3870 if l_footnote_balance_type_tab = 'RL1_BOXR' then
3871 lv_footnote_bal := 'PROV STATUS INDIAN Subject';
3872 elsif l_footnote_balance_type_tab = 'RL1_BOXA' then
3873 lv_footnote_bal := 'Gross Earnings';
3874 else
3875 lv_footnote_bal := l_footnote_balance_type_tab;
3876 end if;
3877
3878 open c_footnote_info(lv_footnote_bal);
3879 loop
3880 fetch c_footnote_info into l_footnote_code,
3881 l_footnote_balance;
3882 exit when c_footnote_info%NOTFOUND;
3883
3884 hr_utility.trace('l_footnote_amount_balance is '||
3885 l_footnote_balance);
3886 hr_utility.trace('l_footnote_code is '||
3887 l_footnote_code);
3888 hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3889 l_footnote_amount_ue);
3890
3891 /* Must ensure that BOXR is only used with footnote code 14 */
3892 l_boxr_flag := 'Y';
3893 if ((l_footnote_balance_type_tab = 'RL1_BOXR') and
3894 (l_footnote_code <> '14')) then
3895 l_boxr_flag := 'N';
3896 end if;
3897
3898 if l_boxr_flag = 'Y' then
3899
3900 if ( l_footnote_code <> old_l_footnote_code or
3901 old_l_footnote_code is null )
3902 then
3903 hr_utility.trace('old_l_footnote_code is '||
3904 nvl(old_l_footnote_code,'NULL'));
3905 if old_l_footnote_code is not null then
3906
3907 l_footnote_amount_ue := 'CAEOY_' ||old_balance_type_tab
3908 ||'_'||old_l_footnote_code||'_AMT_PER_JD_YTD';
3909
3910 if get_footnote_user_entity_id(l_footnote_amount_ue)<>0
3911 and l_footnote_amount <> 0
3912 then
3913 ff_archive_api.create_archive_item(
3914 p_archive_item_id => l_archive_item_id
3915 ,p_user_entity_id =>
3916 get_footnote_user_entity_id(l_footnote_amount_ue)
3917 ,p_archive_value => l_footnote_amount
3918 ,p_archive_type => 'AAP'
3919 ,p_action_id => p_assactid
3920 ,p_legislation_code => 'CA'
3921 ,p_object_version_number => l_object_version_number
3922 ,p_context_name1 => 'JURISDICTION_CODE'
3923 ,p_context1 => 'QC'
3924 ,p_some_warning => l_some_warning
3925 );
3926
3927 if l_footnote_amount < 0 then
3928 l_negative_balance_exists := 'Y';
3929 end if;
3930
3931 end if;
3932
3933 end if;
3934
3935 l_footnote_amount := 0;
3936 old_l_footnote_code := l_footnote_code ;
3937 old_balance_type_tab := l_footnote_balance_type_tab ;
3938 l_footnote_amount_ue := 'CAEOY_' ||
3939 l_footnote_balance_type_tab||
3940 '_'||l_footnote_code||'_AMT_PER_JD_YTD';
3941 hr_utility.trace('l_footnote_amount_ue is '||
3942 l_footnote_amount_ue);
3943 end if;
3944
3945 l_footnote_amount_ue := 'CAEOY_' ||
3946 l_footnote_balance_type_tab||
3947 '_'||l_footnote_code||'_AMT_PER_JD_YTD';
3948 l_prev_tax_unit_id := NULL;
3949
3950 /* get the footnote_balance */
3951 open c_all_gres_for_footnote(p_assactid);
3952 loop
3953 hr_utility.trace('Fetching all GREs');
3954 fetch c_all_gres_for_footnote into l_ft_tax_unit_id,
3955 l_ft_reporting_type;
3956 exit when c_all_gres_for_footnote%NOTFOUND;
3957
3958 hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3959 hr_utility.trace('Asgid is ' || l_asgid);
3960 hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3961 hr_utility.trace('Effective date is '|| p_effective_date);
3962 begin
3963 /* Removed select stmt to get max asgact_id and replaced
3964 it with cursor c_get_max_asgactid_jd, reusing the same
3965 cursor used above. 11510 Changes Bug#3356533. Changed
3966 cursor to get max asg_act_id based on person_id to
3967 fix bug#3638928. */
3968 open c_get_max_asgactid_jd(to_number(lv_serial_number),
3969 l_ft_tax_unit_id,
3970 l_year_start,
3971 l_year_end);
3972 fetch c_get_max_asgactid_jd into l_ft_aaid;
3973 close c_get_max_asgactid_jd;
3974
3975 hr_utility.trace('l_aaid is ' || l_ft_aaid);
3976 hr_utility.trace('l_count is ' || l_count);
3977
3978 l_no_of_payroll_run := l_no_of_payroll_run + 1;
3979
3980 select target1.business_group_id
3981 into l_business_group_id
3982 from hr_all_organization_units target1
3983 where target1.organization_id = l_ft_tax_unit_id;
3984
3985 if ( l_ft_tax_unit_id <> l_prev_tax_unit_id or
3986 l_prev_tax_unit_id is null )
3987 then
3988 hr_utility.trace('l_business_group_id is ' ||
3989 l_business_group_id);
3990
3991 pay_balance_pkg.set_context('TAX_UNIT_ID',
3992 l_ft_tax_unit_id);
3993 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',
3994 l_ft_aaid);
3995 pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3996
3997 l_footnote_amount := l_footnote_amount +
3998 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3999 ( l_footnote_balance,
4000 'YTD' ,
4001 l_ft_aaid,
4002 l_asgid ,
4003 NULL,
4004 'PER' ,
4005 l_ft_tax_unit_id,
4006 l_business_group_id,
4007 'QC'
4008 ),0) ;
4009 end if;
4010
4011 l_prev_tax_unit_id := l_ft_tax_unit_id ;
4012 exception
4013 when no_data_found then
4014 hr_utility.trace('This Tax unit id has no payroll run,'||
4015 ' so skip it');
4016 end;
4017 end loop;
4018 close c_all_gres_for_footnote;
4019
4020 /* end of getting balnce */
4021
4022 l_footnote_amount := l_footnote_amount + l_value ;
4023
4024 if l_value <> 0 then
4025 l_no_of_fn_codes := l_no_of_fn_codes + 1;
4026 end if;
4027
4028 end if; /* l_boxr_flag */
4029
4030 end loop; /* c_footnote_info loop */
4031 close c_footnote_info;
4032
4033 hr_utility.trace('before archiving l_footnote_amount_ue is '||
4034 l_footnote_amount_ue);
4035
4036 if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
4037 and l_footnote_amount <> 0 then
4038
4039 ff_archive_api.create_archive_item(
4040 p_archive_item_id => l_archive_item_id
4041 ,p_user_entity_id =>
4042 get_footnote_user_entity_id(l_footnote_amount_ue)
4043 ,p_archive_value => l_footnote_amount
4044 ,p_archive_type => 'AAP'
4045 ,p_action_id => p_assactid
4046 ,p_legislation_code => 'CA'
4047 ,p_object_version_number => l_object_version_number
4048 ,p_context_name1 => 'JURISDICTION_CODE'
4049 ,p_context1 => 'QC'
4050 ,p_some_warning => l_some_warning
4051 );
4052
4053 if l_footnote_amount < 0 then
4054 l_negative_balance_exists := 'Y';
4055 end if;
4056
4057 l_footnote_amount := 0;
4058 l_footnote_amount_ue := null;
4059 end if;
4060 end;
4061 end if;
4062 end if;
4063
4064 /* End of footnote archiving */
4065
4066 /* archive the box balances */
4067 hr_utility.trace('here1');
4068 hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
4069 hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070 hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071 hr_utility.trace('p_assactid ='|| p_assactid);
4072 hr_utility.trace('l_object_version_number ='|| l_object_version_number);
4073 -- hr_utility.trace('l_some_warning ='|| l_some_warning);
4074 ff_archive_api.create_archive_item(
4075 /* p_validate => 'TRUE' */
4076 p_archive_item_id => l_archive_item_id
4077 ,p_user_entity_id =>
4078 get_user_entity_id(l_user_entity_name_tab(i))
4079 ,p_archive_value => l_user_entity_value_tab(i)
4080 ,p_archive_type => 'AAP'
4081 ,p_action_id => p_assactid
4082 ,p_legislation_code => 'CA'
4083 ,p_object_version_number => l_object_version_number
4084 ,p_context_name1 => 'JURISDICTION_CODE'
4085 ,p_context1 => 'QC'
4086 ,p_some_warning => l_some_warning
4087 );
4088 hr_utility.trace('after the call');
4089 if l_user_entity_value_tab(i) < 0 then
4090 l_negative_balance_exists := 'Y';
4091 end if;
4092 /*Bug 15886329 sbachu*/
4093 If l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_WITHHELD_PER_JD_YTD' then
4094 lv_archived_qpp_whld := l_user_entity_value_tab(i);
4095 elsif l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD' then
4096 lv_archived_qpp_txble := l_user_entity_value_tab(i);
4097 ln_qpp_txble_arch_item_id := l_archive_item_id;
4098 end if;
4099
4100 end loop;
4101
4102 /* Archive BOXO, which is sum of all the individual
4103 balances under BOXO, also determine the correct
4104 BOXO code that needs to be archived */
4105
4106 l_user_entity_value_tab_boxo := 0;
4107 l_count_for_boxo_code := 0;
4108 l_user_entity_code_tab_boxo := NULL;
4109
4110 for i in l_count_start_for_boxo..l_count_end_for_boxo
4111 loop
4112
4113 if to_number(l_user_entity_value_tab(i)) <> 0 then
4114
4115 l_count_for_boxo_code := l_count_for_boxo_code + 1;
4116
4117 l_user_entity_code_tab_boxo :=
4118 substr(l_user_entity_name_tab(i),23,2);
4119
4120 l_user_entity_value_tab_boxo :=
4121 l_user_entity_value_tab_boxo + l_user_entity_value_tab(i);
4122 end if;
4123
4124 end loop;
4125
4126 if l_count_for_boxo_code > 1 then
4127 l_user_entity_code_tab_boxo := 'RZ' ;
4128 ln_no_nz_fi := ln_no_nz_fi + l_count_for_boxo_code ; /*Bug 13564765 sbachu*/
4129 end if;
4130 hr_utility.trace('ln_no_nz_fi3 = '||ln_no_nz_fi);
4131 if l_user_entity_value_tab_boxo < 0 then
4132 l_negative_balance_exists := 'Y';
4133 end if;
4134
4135 ff_archive_api.create_archive_item(
4136 p_archive_item_id => l_archive_item_id
4137 ,p_user_entity_id =>
4138 get_user_entity_id('CAEOY_RL1_BOXO_PER_JD_YTD')
4139 ,p_archive_value => l_user_entity_value_tab_boxo
4140 ,p_archive_type => 'AAP'
4141 ,p_action_id => p_assactid
4142 ,p_legislation_code => 'CA'
4143 ,p_object_version_number => l_object_version_number
4144 ,p_context_name1 => 'JURISDICTION_CODE'
4145 ,p_context1 => 'QC'
4146 ,p_some_warning => l_some_warning
4147 );
4148
4149 ff_archive_api.create_archive_item(
4150 /* p_validate => 'TRUE' */
4151 p_archive_item_id => l_archive_item_id
4152 ,p_user_entity_id =>
4153 get_user_entity_id('CAEOY_RL1_BOXO_CODE_PER_JD_YTD')
4154 ,p_archive_value => l_user_entity_code_tab_boxo
4155 ,p_archive_type => 'AAP'
4156 ,p_action_id => p_assactid
4157 ,p_legislation_code => 'CA'
4158 ,p_object_version_number => l_object_version_number
4159 ,p_context_name1 => 'JURISDICTION_CODE'
4160 ,p_context1 => 'QC'
4161 ,p_some_warning => l_some_warning
4162 );
4163
4164 /* for box o archiving */
4165 /* archive RL1 slip number has been moved down so that slip
4166 numbers can be archived based on number of
4167 Further Information Codes for bug 13564765*/
4168 /* archive CPP amount */
4169
4170 /* 11510 changes done to c_all_gres_for_person cursor
4171 passing asgid instead of p_assactid */
4172
4173 open c_all_gres_for_person(l_asgid,p_effective_date);
4174
4175 result := 0;
4176 lv_cpp_pensionable_earnings := 0;
4177 lv_taxable_benefit_with_no_rem := 0;
4178 lv_tax_ben_no_rem_all_prov := 0;/* Bug 15914635*/
4179
4180 loop
4181 hr_utility.trace('Fetching all GREs for the person');
4182 fetch c_all_gres_for_person into l_tax_unit_id;
4183 exit when c_all_gres_for_person%NOTFOUND;
4184
4185 begin
4186 /* Removed the select stmt to get max asgact_id and replaced it
4187 with cursor c_get_max_asgactid. 11510 changes for bug#3356533.
4188 Changed the cursor to get max asg_act_id based on person_id
4189 to fix bug#3638928. */
4190 open c_get_max_asgactid(to_number(lv_serial_number),
4191 l_tax_unit_id,
4192 l_year_start,
4193 l_year_end);
4194 fetch c_get_max_asgactid into l_aaid1;
4195 close c_get_max_asgactid;
4196
4197 result := result +
4198 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
4199 ('CPP EE Withheld',
4200 'YTD' ,
4201 l_aaid1,
4202 l_asgid,
4203 NULL,
4204 'PER' ,
4205 l_tax_unit_id,
4206 l_business_group_id,
4207 NULL),0);
4208
4209 lv_cpp_pensionable_earnings := lv_cpp_pensionable_earnings +
4210 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
4211 ('CPP EE Taxable',
4212 'YTD' ,
4213 l_aaid1,
4214 l_asgid,
4215 NULL,
4216 'PER' ,
4217 l_tax_unit_id,
4218 l_business_group_id,
4219 NULL),0);
4220
4221 lv_taxable_benefit_with_no_rem := lv_taxable_benefit_with_no_rem +
4222 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
4223 ('Taxable Benefit without Remuneration',
4224 'YTD' ,
4225 l_aaid1,
4226 l_asgid ,
4227 NULL,
4228 'PER' ,
4229 l_tax_unit_id,
4230 l_business_group_id,
4231 'QC'),0);
4232
4233 /*Bug 15914635 sbachu*/
4234 lv_tax_ben_no_rem_all_prov := lv_tax_ben_no_rem_all_prov +
4235 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
4236 ('Taxable Benefit without Remuneration',
4237 'YTD' ,
4238 l_aaid1,
4239 l_asgid ,
4240 NULL,
4241 'PER' ,
4242 l_tax_unit_id,
4243 l_business_group_id,
4244 NULL),0);
4245
4246 end;
4247 end loop;
4248 close c_all_gres_for_person;
4249 hr_utility.trace('closed all GREs for the person');
4250
4251 ff_archive_api.create_archive_item(
4252 /* p_validate => 'TRUE' */
4253 p_archive_item_id => l_archive_item_id
4254 ,p_user_entity_id =>
4255 get_user_entity_id('CAEOY_CPP_EE_WITHHELD_PER_YTD')
4256 ,p_archive_value => result
4257 ,p_archive_type => 'AAP'
4258 ,p_action_id => p_assactid
4259 ,p_legislation_code => 'CA'
4260 ,p_object_version_number => l_object_version_number
4261 ,p_some_warning => l_some_warning);
4262
4263 /*Bug 13564765 sbachu*/
4264 if result is not null and result <> 0 then
4265 ln_no_nz_fi := ln_no_nz_fi +1;
4266 end if;
4267 hr_utility.trace('ln_no_nz_fi2 = '||ln_no_nz_fi);
4268 /* bug 14030417, CPP value is negative then emp should report in
4269 error report */
4270 if result < 0 then
4271 l_negative_balance_exists := 'Y';
4272 end if;
4273
4274 /* Bug 14701466,15914635 sbachu*/
4275 ff_archive_api.create_archive_item(
4276 /* p_validate => 'TRUE' */
4277 p_archive_item_id => l_archive_item_id
4278 ,p_user_entity_id =>
4279 get_user_entity_id('CAEOY_CPP_EE_TAXABLE_PER_YTD')
4280 ,p_archive_value => (lv_cpp_pensionable_earnings + lv_tax_ben_no_rem_all_prov - lv_taxable_benefit_with_no_rem)
4281 ,p_archive_type => 'AAP'
4282 ,p_action_id => p_assactid
4283 ,p_legislation_code => 'CA'
4284 ,p_object_version_number => l_object_version_number
4285 ,p_some_warning => l_some_warning);
4286
4287 /* End of CPP archiving */
4288
4289 /*Bug 15886329 starts here sbachu*/
4290 /* Need to report non zero qpp pensionable earnings in Box G when there
4291 is non zero qpp contribution in Box B as per the new requirement for the case where
4292 maximum pensionable earnings are already reached in non quebec provinces */
4293
4294 select fnd_number.canonical_to_number(information_value)
4295 into ln_max_cpp_earnings
4296 from pay_ca_legislation_info
4297 where information_type = 'MAX_CPP_EARNINGS'
4298 and l_year_end between start_date
4299 and end_date;
4300
4301 if lv_cpp_pensionable_earnings = ln_max_cpp_earnings
4302 and to_number(lv_archived_qpp_txble) = 0
4303 and to_number(lv_archived_qpp_whld) <> 0 then
4304 select fnd_number.canonical_to_number(information_value)
4305 into ln_qpp_rate
4306 from pay_ca_legislation_info
4307 where information_type = 'QPP_RATE'
4308 and l_year_end between start_date
4309 and end_date;
4310 lv_qpp_to_be_rprted := to_char(round(lv_archived_qpp_whld * 100 / ln_qpp_rate , 2));
4311 if ln_qpp_txble_arch_item_id <> 0 then
4312 UPDATE ff_archive_items
4313 SET value = lv_qpp_to_be_rprted
4314 WHERE archive_item_id = ln_qpp_txble_arch_item_id;
4315 end if;
4316 end if;
4317
4318 /*Bug 15886329 ends here*/
4319 /*Bug 13564765 starts here sbachu*/
4320 if lv_cpp_pensionable_earnings is not null and lv_cpp_pensionable_earnings <> 0 then
4321 ln_no_nz_fi := ln_no_nz_fi +1;
4322 end if;
4323 hr_utility.trace('ln_no_nz_fi5 = '||ln_no_nz_fi);
4324 lv_max_pensionable_earnings := 0;
4325 select fnd_number.canonical_to_number(information_value)
4326 into lv_max_pensionable_earnings
4327 from pay_ca_legislation_info
4328 where information_type = 'MAX_CPP_EARNINGS'
4329 and l_year_end between start_date
4330 and end_date;
4331 if ((lv_max_pensionable_earnings > (lv_cpp_pensionable_earnings +
4332 lv_qpp_pensionable_earnings)) and
4333 (lv_taxable_benefit_with_no_rem <> 0)) then
4334 ln_no_nz_fi := ln_no_nz_fi +1;
4335 end if;
4336 hr_utility.trace('ln_no_nz_fi1 = '||ln_no_nz_fi);
4337
4338 /* RL1 Slip number generation */
4339 l_rl1_slip_number := gen_rl1_slip_no(l_payroll_action_id);
4340 l_rl1_seq_number := gen_rl1_pdf_seq(p_assactid,
4341 to_char(l_year_end,'YYYY'),
4342 'QC',
4343 'ARCHIVER');
4344 if ln_no_nz_fi > ln_no_fi_per_slip then
4345 if mod(ln_no_nz_fi,ln_no_fi_per_slip) = 0 then
4346 for l_i in 1..trunc(ln_no_nz_fi/ln_no_fi_per_slip)-1
4347 loop
4348 l_rl1_slip_number := l_rl1_slip_number || '|' || gen_rl1_slip_no(l_payroll_action_id);
4349 l_rl1_seq_number := l_rl1_seq_number || '|' || gen_rl1_pdf_seq(p_assactid,
4350 to_char(l_year_end,'YYYY'),
4351 'QC',
4352 'ARCHIVER');
4353 hr_utility.trace('l_rl1_slip_number = '||l_rl1_slip_number);
4354 hr_utility.trace('l_rl1_seq_number = '||l_rl1_seq_number);
4355 end loop;
4356 else
4357 for l_i in 1..trunc(ln_no_nz_fi/ln_no_fi_per_slip)
4358 loop
4359 l_rl1_slip_number := l_rl1_slip_number || '|' || gen_rl1_slip_no(l_payroll_action_id);
4360 l_rl1_seq_number := l_rl1_seq_number || '|' || gen_rl1_pdf_seq(p_assactid,
4361 to_char(l_year_end,'YYYY'),
4362 'QC',
4363 'ARCHIVER');
4364 hr_utility.trace('l_rl1_slip_number = '||l_rl1_slip_number);
4365 hr_utility.trace('l_rl1_seq_number = '||l_rl1_seq_number);
4366 end loop;
4367 end if;
4368 end if;
4369
4370 /* archive RL1 slip number */
4371
4372 ff_archive_api.create_archive_item(
4373 /* p_validate => 'TRUE' */
4374 p_archive_item_id => l_archive_item_id
4375 ,p_user_entity_id =>
4376 get_user_entity_id('CAEOY_RL1_SLIP_NUMBER')
4377 ,p_archive_value => l_rl1_slip_number
4378 ,p_archive_type => 'AAP'
4379 ,p_action_id => p_assactid
4380 ,p_legislation_code => 'CA'
4381 ,p_object_version_number => l_object_version_number
4382 ,p_some_warning => l_some_warning );
4383
4384 /* archiving RL1 PDF Sequence Number -Bug 6768167*/
4385
4386 ff_archive_api.create_archive_item(
4387 p_archive_item_id => l_archive_item_id
4388 ,p_user_entity_id =>
4389 get_user_entity_id('CAEOY_RL1_PDF_SEQ_NUMBER')
4390 ,p_archive_value => l_rl1_seq_number
4391 ,p_archive_type => 'AAP'
4392 ,p_action_id => p_assactid
4393 ,p_legislation_code => 'CA'
4394 ,p_object_version_number => l_object_version_number
4395 ,p_context_name1 => 'JURISDICTION_CODE'
4396 ,p_context1 => 'QC'
4397 ,p_some_warning => l_some_warning
4398 );
4399 /*Bug 13564765 ends here sbachu*/
4400 --hr_utility.trace_off;
4401 end if;
4402
4403 hr_utility.trace('Out of province loop ');
4404
4405 /* Archiving of Non-Box Footnotes */
4406 begin
4407 --hr_utility.trace_on('Y','NONBOX');
4408
4409 /* Archive Nonbox footnote for Taxable Benefits that are processed on their
4410 own if the total pensionable earnings is less than the maximum bug# 3369317 */
4411
4412 lv_max_pensionable_earnings := 0;
4413 lv_total_pensionable_earnings := 0;
4414
4415 select fnd_number.canonical_to_number(information_value)
4416 into lv_max_pensionable_earnings
4417 from pay_ca_legislation_info
4418 where information_type = 'MAX_CPP_EARNINGS'
4419 and l_year_end between start_date
4420 and end_date;
4421
4422 lv_total_pensionable_earnings := lv_cpp_pensionable_earnings +
4423 lv_qpp_pensionable_earnings;
4424
4425 if ((lv_max_pensionable_earnings > lv_total_pensionable_earnings) and
4426 (lv_taxable_benefit_with_no_rem <> 0)) then
4427 if to_number(to_char(l_year_end,'YYYY')) < 2011 then
4428 pay_action_information_api.create_action_information(
4429 p_action_information_id => l_action_information_id_1,
4430 p_object_version_number => l_object_version_number_1,
4431 p_action_information_category => 'CA FOOTNOTES',
4432 p_action_context_id => p_assactid,
4433 p_action_context_type => 'AAP',
4434 p_jurisdiction_code => 'QC',
4435 p_tax_unit_id => NULL,
4436 p_effective_date => l_year_end,
4437 p_assignment_id => l_asgid,
4438 p_action_information1 => NULL,
4439 p_action_information2 => NULL,
4440 p_action_information3 => NULL,
4441 p_action_information4 => '10', /* QPP - Taxable benefit in kind */
4442 p_action_information5 => lv_taxable_benefit_with_no_rem,
4443 p_action_information6 => 'RL1',
4444 p_action_information7 => NULL,
4445 p_action_information8 => NULL,
4446 p_action_information9 => NULL,
4447 p_action_information10 => NULL,
4448 p_action_information11 => NULL,
4449 p_action_information12 => NULL,
4450 p_action_information13 => NULL,
4451 p_action_information14 => NULL,
4452 p_action_information15 => NULL,
4453 p_action_information16 => NULL,
4454 p_action_information17 => NULL,
4455 p_action_information18 => NULL,
4456 p_action_information19 => NULL,
4457 p_action_information20 => NULL,
4458 p_action_information21 => NULL,
4459 p_action_information22 => NULL,
4460 p_action_information23 => NULL,
4461 p_action_information24 => NULL,
4462 p_action_information25 => NULL,
4463 p_action_information26 => NULL,
4464 p_action_information27 => NULL,
4465 p_action_information28 => NULL,
4466 p_action_information29 => NULL,
4467 p_action_information30 => NULL);
4468 else
4469 pay_action_information_api.create_action_information(
4470 p_action_information_id => l_action_information_id_1,
4471 p_object_version_number => l_object_version_number_1,
4472 p_action_information_category => 'CA FOOTNOTES',
4473 p_action_context_id => p_assactid,
4474 p_action_context_type => 'AAP',
4475 p_jurisdiction_code => 'QC',
4476 p_tax_unit_id => NULL,
4477 p_effective_date => l_year_end,
4478 p_assignment_id => l_asgid,
4479 p_action_information1 => NULL,
4480 p_action_information2 => NULL,
4481 p_action_information3 => NULL,
4482 p_action_information4 => 'G-1', /* Taxable benefit paid in kind */
4483 p_action_information5 => lv_taxable_benefit_with_no_rem,
4484 p_action_information6 => 'RL1',
4485 p_action_information7 => NULL,
4486 p_action_information8 => NULL,
4487 p_action_information9 => NULL,
4488 p_action_information10 => NULL,
4489 p_action_information11 => NULL,
4490 p_action_information12 => NULL,
4491 p_action_information13 => NULL,
4492 p_action_information14 => NULL,
4493 p_action_information15 => NULL,
4494 p_action_information16 => NULL,
4495 p_action_information17 => NULL,
4496 p_action_information18 => NULL,
4497 p_action_information19 => NULL,
4498 p_action_information20 => NULL,
4499 p_action_information21 => NULL,
4500 p_action_information22 => NULL,
4501 p_action_information23 => NULL,
4502 p_action_information24 => NULL,
4503 p_action_information25 => NULL,
4504 p_action_information26 => NULL,
4505 p_action_information27 => NULL,
4506 p_action_information28 => NULL,
4507 p_action_information29 => NULL,
4508 p_action_information30 => NULL);
4509 end if;
4510 if lv_taxable_benefit_with_no_rem < 0 then
4511 l_negative_balance_exists := 'Y';
4512 end if;
4513 end if;
4514
4515 l_total_mesg_amt := 0;
4516 l_mesg_amt := 0;
4517 hr_utility.trace('l_year_start - '||l_year_start);
4518 open cur_non_box_mesg(p_assactid, p_effective_date,l_year_start);
4519 loop
4520 fetch cur_non_box_mesg into l_messages,
4521 l_mesg_amt,
4522 ln_tax_unit_id,
4523 ld_eff_date,
4524 ln_assignment_action_id;
4525 if cur_non_box_mesg%notfound then
4526 exit;
4527 end if;
4528
4529 hr_utility.trace('l_messages - '||l_messages);
4530 hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
4531 hr_utility.trace('effective date - '||p_effective_date);
4532
4533 /* If the same Non Box footnote is processed more than
4534 once during the year, then the sum of the associated
4535 amounts is archived */
4536
4537 open c_non_box_lookup; -- Bug 8366352
4538 fetch c_non_box_lookup into lv_non_box_lookup;
4539
4540 if (c_non_box_lookup%found) then
4541 if ((l_messages <> l_prev_messages) and
4542 (l_prev_messages is not null)) then
4543
4544 hr_utility.trace('l_prev_messages - '||l_prev_messages);
4545
4546 if l_total_mesg_amt <> 0 then
4547
4548 pay_action_information_api.create_action_information(
4549 p_action_information_id => l_action_information_id_1,
4550 p_object_version_number => l_object_version_number_1,
4551 p_action_information_category => 'CA FOOTNOTES',
4552 p_action_context_id => p_assactid,
4553 p_action_context_type => 'AAP',
4554 p_jurisdiction_code => 'QC',
4555 p_tax_unit_id => ln_prev_tax_unit_id,
4556 p_effective_date => ld_prev_eff_date,
4557 p_assignment_id => l_asgid,
4558 p_action_information1 => NULL,
4559 p_action_information2 => NULL,
4560 p_action_information3 => NULL,
4561 p_action_information4 => l_prev_messages,
4562 p_action_information5 => l_total_mesg_amt,
4563 p_action_information6 => 'RL1',
4564 p_action_information7 => NULL,
4565 p_action_information8 => NULL,
4566 p_action_information9 => NULL,
4567 p_action_information10 => NULL,
4568 p_action_information11 => NULL,
4569 p_action_information12 => NULL,
4570 p_action_information13 => NULL,
4571 p_action_information14 => NULL,
4572 p_action_information15 => NULL,
4573 p_action_information16 => NULL,
4574 p_action_information17 => NULL,
4575 p_action_information18 => NULL,
4576 p_action_information19 => NULL,
4577 p_action_information20 => NULL,
4578 p_action_information21 => NULL,
4579 p_action_information22 => NULL,
4580 p_action_information23 => NULL,
4581 p_action_information24 => NULL,
4582 p_action_information25 => NULL,
4583 p_action_information26 => NULL,
4584 p_action_information27 => NULL,
4585 p_action_information28 => NULL,
4586 p_action_information29 => NULL,
4587 p_action_information30 => NULL
4588 );
4589
4590 if l_total_mesg_amt < 0 then
4591 l_negative_balance_exists := 'Y';
4592 end if;
4593
4594 end if;
4595
4596 l_total_mesg_amt := l_mesg_amt;
4597 else
4598 l_total_mesg_amt := l_total_mesg_amt + l_mesg_amt;
4599 end if;
4600 -- Moved END IF condition to before END LOOP, bug 9177694
4601
4602 /* end if; --c_non_box_lookup%found
4603
4604 close c_non_box_lookup; */
4605
4606 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4607
4608 l_prev_messages := l_messages;
4609 ln_prev_tax_unit_id := ln_tax_unit_id;
4610 ld_prev_eff_date := ld_eff_date;
4611
4612 end if; --c_non_box_lookup%found
4613
4614 close c_non_box_lookup;
4615
4616 end loop;
4617
4618 close cur_non_box_mesg;
4619
4620 if (l_prev_messages is not null) then
4621
4622 hr_utility.trace('l_prev_messages - '||l_prev_messages);
4623 hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4624
4625 if l_total_mesg_amt <> 0 then
4626
4627 pay_action_information_api.create_action_information(
4628 p_action_information_id => l_action_information_id_1,
4629 p_object_version_number => l_object_version_number_1,
4630 p_action_information_category => 'CA FOOTNOTES',
4631 p_action_context_id => p_assactid,
4632 p_action_context_type => 'AAP',
4633 p_jurisdiction_code => 'QC',
4634 p_tax_unit_id => ln_prev_tax_unit_id,
4635 p_effective_date => ld_prev_eff_date,
4636 p_assignment_id => l_asgid,
4637 p_action_information1 => NULL,
4638 p_action_information2 => NULL,
4639 p_action_information3 => NULL,
4640 p_action_information4 => l_prev_messages,
4641 p_action_information5 => l_total_mesg_amt,
4642 p_action_information6 => 'RL1',
4643 p_action_information7 => NULL,
4644 p_action_information8 => NULL,
4645 p_action_information9 => NULL,
4646 p_action_information10 => NULL,
4647 p_action_information11 => NULL,
4648 p_action_information12 => NULL,
4649 p_action_information13 => NULL,
4650 p_action_information14 => NULL,
4651 p_action_information15 => NULL,
4652 p_action_information16 => NULL,
4653 p_action_information17 => NULL,
4654 p_action_information18 => NULL,
4655 p_action_information19 => NULL,
4656 p_action_information20 => NULL,
4657 p_action_information21 => NULL,
4658 p_action_information22 => NULL,
4659 p_action_information23 => NULL,
4660 p_action_information24 => NULL,
4661 p_action_information25 => NULL,
4662 p_action_information26 => NULL,
4663 p_action_information27 => NULL,
4664 p_action_information28 => NULL,
4665 p_action_information29 => NULL,
4666 p_action_information30 => NULL
4667 );
4668
4669 if l_total_mesg_amt < 0 then
4670 l_negative_balance_exists := 'Y';
4671 end if;
4672
4673 end if;
4674
4675 end if;
4676
4677 --hr_utility.trace_off;
4678 end;
4679
4680 ff_archive_api.create_archive_item(
4681 p_archive_item_id => l_archive_item_id
4682 ,p_user_entity_id =>
4683 get_user_entity_id('CAEOY_RL1_NEGATIVE_BALANCE_EXISTS')
4684 ,p_archive_value => l_negative_balance_exists
4685 ,p_archive_type => 'AAP'
4686 ,p_action_id => p_assactid
4687 ,p_legislation_code => 'CA'
4688 ,p_object_version_number => l_object_version_number
4689 ,p_context_name1 => 'JURISDICTION_CODE'
4690 ,p_context1 => 'QC'
4691 ,p_some_warning => l_some_warning
4692 );
4693
4694 l_count := 0;
4695 /* Similarly create archive data for employee surname,employee first name,
4696 employee initial, employee address ,city,province,country,postal code,
4697 SIN, employee number , business number .
4698 Not all of them has jurisdiction context.*/
4699
4700 if ((l_no_of_payroll_run > 0) and
4701 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
4702 begin
4703
4704 --code fix started for bug 5893569
4705 /*
4706 select PEOPLE.person_id,
4707 PEOPLE.first_name,
4708 PEOPLE.middle_names,
4709 PEOPLE.last_name,
4710 PEOPLE.employee_number,
4711 PEOPLE.date_of_birth,
4712 replace(PEOPLE.national_identifier,' '),
4713 PEOPLE.pre_name_adjunct,
4714 NVL(PHONE.phone_number,PEOPLE.work_telephone)
4715 into l_person_id,
4716 l_first_name,
4717 l_middle_name,
4718 l_last_name,
4719 l_employee_number,
4720 l_date_of_birth,
4721 l_national_identifier,
4722 l_pre_name_adjunct,
4723 l_employee_phone_no
4724 from per_all_assignments_f ASSIGN
4725 ,per_all_people_f PEOPLE
4726 ,per_person_types PTYPE
4727 ,per_phones PHONE
4728 ,fnd_sessions SES
4729 where l_date_earned BETWEEN ASSIGN.effective_start_date
4730 AND ASSIGN.effective_end_date
4731 and ASSIGN.assignment_id = l_asgid
4732 and PEOPLE.person_id = ASSIGN.person_id
4733 and l_date_earned BETWEEN PEOPLE.effective_start_date
4734 AND PEOPLE.effective_end_date
4735 and PTYPE.person_type_id = PEOPLE.person_type_id
4736 and PHONE.parent_id (+) = PEOPLE.person_id
4737 and PHONE.parent_table (+)= 'PER_ALL_PEOPLE_F'
4738 and PHONE.phone_type (+)= 'W1'
4739 and l_date_earned
4740 BETWEEN NVL(PHONE.date_from,l_date_earned)
4741 AND NVL(PHONE.date_to,l_date_earned)
4742 and SES.session_id = USERENV('SESSIONID');
4743 */
4744
4745 select PEOPLE.person_id,
4746 PEOPLE.first_name,
4747 PEOPLE.middle_names,
4748 PEOPLE.last_name,
4749 PEOPLE.employee_number,
4750 PEOPLE.date_of_birth,
4751 replace(PEOPLE.national_identifier,' '),
4752 PEOPLE.pre_name_adjunct
4753 into l_person_id,
4754 l_first_name,
4755 l_middle_name,
4756 l_last_name,
4757 l_employee_number,
4758 l_date_of_birth,
4759 l_national_identifier,
4760 l_pre_name_adjunct
4761 from per_all_assignments_f ASSIGN
4762 ,per_all_people_f PEOPLE
4763 where ASSIGN.assignment_id =l_asgid
4764 and PEOPLE.person_id = ASSIGN.person_id
4765 -- code fix started for 6440125
4766 and l_date_earned BETWEEN ASSIGN.effective_start_date
4767 AND ASSIGN.effective_end_date
4768 and l_date_earned BETWEEN PEOPLE.effective_start_date
4769 AND PEOPLE.effective_end_date;
4770
4771 --code fix ended for 6440125
4772 --code fix ended for bug 5893569
4773
4774 exception
4775 when no_data_found then
4776 l_first_name := null;
4777 l_middle_name := null;
4778 l_last_name := null;
4779 l_employee_number := null;
4780 l_national_identifier := null;
4781 l_pre_name_adjunct := null;
4782 l_employee_phone_no := null;
4783 l_date_of_birth := null;
4784 end;
4785
4786 begin
4787
4788 select max(date_start)
4789 ,max(actual_termination_date)
4790 into l_hire_date
4791 ,l_termination_date
4792 from per_periods_of_service
4793 where person_id = l_person_id;
4794
4795 exception
4796 when no_data_found then
4797 l_hire_date := null;
4798 l_termination_date := null;
4799
4800 end;
4801
4802 hr_utility.trace('Before counter of asgid '|| l_asgid);
4803
4804 l_counter := l_counter + 1;
4805 l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
4806 l_user_entity_value_tab(l_counter):= l_person_id;
4807
4808 l_counter := l_counter + 1;
4809 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
4810 l_user_entity_value_tab(l_counter):= l_first_name;
4811
4812 hr_utility.trace('Before counter 2');
4813 l_counter := l_counter + 1;
4814 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
4815 l_user_entity_value_tab(l_counter):= l_last_name ;
4816
4817 hr_utility.trace('Before counter 3');
4818 l_counter := l_counter + 1;
4819 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
4820 l_user_entity_value_tab(l_counter):= l_middle_name ;
4821
4822 hr_utility.trace('Before counter 3');
4823 l_counter := l_counter + 1;
4824 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
4825 l_user_entity_value_tab(l_counter):= l_national_identifier;
4826
4827 hr_utility.trace('Before counter 3');
4828 l_counter := l_counter + 1;
4829 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_DATE_OF_BIRTH';
4830 l_user_entity_value_tab(l_counter):=
4831 fnd_date.date_to_canonical(l_date_of_birth);
4832
4833 hr_utility.trace('Before counter 3');
4834 l_counter := l_counter + 1;
4835 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_HIRE_DATE';
4836 l_user_entity_value_tab(l_counter):=
4837 fnd_date.date_to_canonical(l_hire_date);
4838
4839 hr_utility.trace('Before counter 3');
4840 l_counter := l_counter + 1;
4841 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_TERMINATION_DATE';
4842 l_user_entity_value_tab(l_counter):=
4843 fnd_date.date_to_canonical(l_termination_date);
4844
4845 hr_utility.trace('Before counter 3');
4846 l_counter := l_counter + 1;
4847 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
4848 l_user_entity_value_tab(l_counter):= l_employee_number;
4849
4850 for i in 1 .. l_counter
4851 loop
4852
4853 hr_utility.trace('inside create loop '||l_user_entity_value_tab(i));
4854
4855 ff_archive_api.create_archive_item(
4856 /* p_validate => 'TRUE' */
4857 p_archive_item_id => l_archive_item_id
4858 ,p_user_entity_id =>
4859 get_user_entity_id(l_user_entity_name_tab(i))
4860 ,p_archive_value => l_user_entity_value_tab(i)
4861 ,p_archive_type => 'AAP'
4862 ,p_action_id => p_assactid
4863 ,p_legislation_code => 'CA'
4864 ,p_object_version_number => l_object_version_number
4865 ,p_some_warning => l_some_warning
4866 );
4867 end loop;
4868 end if;
4869
4870 l_counter := 0;
4871
4872 if ((l_no_of_payroll_run > 0) and
4873 ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
4874
4875 begin
4876 open c_get_addr;
4877 fetch c_get_addr into l_address_line1
4878 ,l_address_line2
4879 ,l_address_line3
4880 ,l_town_or_city
4881 ,l_province_code
4882 ,l_postal_code
4883 ,l_telephone_number
4884 ,l_country_code;
4885
4886 if c_get_addr%NOTFOUND then
4887 l_address_line1 := null;
4888 l_address_line2 := null;
4889 l_address_line3 := null;
4890 l_town_or_city := null;
4891 l_province_code := null;
4892 l_postal_code := null;
4893 l_telephone_number := null;
4894 l_country_code := null;
4895 end if;
4896 close c_get_addr;
4897 end;
4898
4899 l_counter := l_counter + 1;
4900 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
4901 l_user_entity_value_tab(l_counter) := l_address_line1;
4902
4903
4904 l_counter := l_counter + 1;
4905 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE2';
4906 l_user_entity_value_tab(l_counter) := l_address_line2;
4907
4908 l_counter := l_counter + 1;
4909 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE3';
4910 l_user_entity_value_tab(l_counter) := l_address_line3;
4911
4912 l_counter := l_counter + 1;
4913 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_CITY';
4914 l_user_entity_value_tab(l_counter) := l_town_or_city;
4915
4916
4917 l_counter := l_counter + 1;
4918 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_PROVINCE';
4919 l_user_entity_value_tab(l_counter) := l_province_code;
4920
4921 l_counter := l_counter + 1;
4922 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_COUNTRY';
4923 l_user_entity_value_tab(l_counter) := l_country_code;
4924
4925 l_counter := l_counter + 1;
4926 l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_POSTAL_CODE';
4927 l_user_entity_value_tab(l_counter) := l_postal_code;
4928
4929
4930 for i in 1 .. l_counter
4931 loop
4932 ff_archive_api.create_archive_item(
4933 p_archive_item_id => l_archive_item_id
4934 ,p_user_entity_id =>
4935 get_user_entity_id(l_user_entity_name_tab(i))
4936 ,p_archive_value => l_user_entity_value_tab(i)
4937 ,p_archive_type => 'AAP'
4938 ,p_action_id => p_assactid
4939 ,p_legislation_code => 'CA'
4940 ,p_object_version_number => l_object_version_number
4941 ,p_some_warning => l_some_warning
4942 );
4943 end loop;
4944 end if;
4945
4946 Begin
4947
4948 hr_utility.trace('Started Provincial YE Amendment PP Validation ');
4949 select to_char(effective_date,'YYYY'),
4950 report_type,
4951 to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID'
4952 ,legislative_parameters))
4953 into lv_fapp_effective_date,
4954 lv_fapp_report_type,
4955 ln_fapp_pre_org_id
4956 from pay_payroll_actions
4957 where payroll_action_id = l_payroll_action_id;
4958
4959 hr_utility.trace('Prov Amend Pre-Process Pactid :'||
4960 to_char(l_payroll_action_id));
4961 hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
4962
4963 /* Archive the Pre-Printed form number for the RL1 YEPP
4964 and Amendment Pre-Process if one exists*/
4965
4966 ln_form_no_archived := 'N';
4967 /*Bug 13564765 starts here sbachu*/
4968 l_k := 0;
4969 open c_get_preprinted_form_no (l_person_id, ln_fapp_pre_org_id);
4970 loop
4971 fetch c_get_preprinted_form_no
4972 into lv_eit_year,
4973 lv_eit_pre_org_id,
4974 lv_eit_form_no;
4975
4976 exit when c_get_preprinted_form_no%NOTFOUND;
4977
4978 if ((lv_fapp_effective_date =to_char(fnd_date.canonical_to_date(lv_eit_year), 'YYYY')) and
4979 (ln_fapp_pre_org_id = to_number(lv_eit_pre_org_id))) then
4980 if l_k = 0 then
4981 l_k := 1;
4982 lv_pre_pr_form_no := lv_eit_form_no;
4983 ln_form_no_archived := 'Y';
4984 else
4985 lv_pre_pr_form_no := lv_pre_pr_form_no || '|' || lv_eit_form_no;
4986 ln_form_no_archived := 'Y';
4987 end if;
4988 end if;
4989
4990 end loop;
4991
4992 close c_get_preprinted_form_no;
4993
4994 if ln_form_no_archived = 'Y' then
4995
4996 ff_archive_api.create_archive_item(
4997 p_archive_item_id => l_archive_item_id
4998 ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_PRE_PRINTED_FORM_NO')
4999 ,p_archive_value => lv_pre_pr_form_no
5000 ,p_archive_type => 'AAP'
5001 ,p_action_id => p_assactid
5002 ,p_legislation_code => 'CA'
5003 ,p_object_version_number => l_object_version_number
5004 ,p_context_name1 => 'JURISDICTION_CODE'
5005 ,p_context1 => 'QC'
5006 ,p_some_warning => l_some_warning
5007 );
5008 else
5009 ff_archive_api.create_archive_item(
5010 p_archive_item_id => l_archive_item_id
5011 ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_PRE_PRINTED_FORM_NO')
5012 ,p_archive_value => NULL
5013 ,p_archive_type => 'AAP'
5014 ,p_action_id => p_assactid
5015 ,p_legislation_code => 'CA'
5016 ,p_object_version_number => l_object_version_number
5017 ,p_context_name1 => 'JURISDICTION_CODE'
5018 ,p_context1 => 'QC'
5019 ,p_some_warning => l_some_warning
5020 );
5021
5022 end if;
5023 /*Bug 13564765 ends here*/
5024 IF lv_fapp_report_type = 'CAEOY_RL1_AMEND_PP' then
5025 begin
5026
5027 open c_get_fapp_locked_action_id(p_assactid);
5028 fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
5029 close c_get_fapp_locked_action_id;
5030
5031 hr_utility.trace('RL1 Amend PP Action ID : '||to_char(p_assactid));
5032 hr_utility.trace('ln_fapp_locked_action_id :'||
5033 to_char(ln_fapp_locked_action_id));
5034 open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
5035 fetch c_get_fapp_lkd_actid_rtype
5036 into lv_fapp_locked_actid_reptype;
5037 close c_get_fapp_lkd_actid_rtype;
5038 hr_utility.trace('lv_fapp_locked_actid_reptype :'||
5039 lv_fapp_locked_actid_reptype);
5040
5041 open c_get_fapp_prov_emp(p_assactid);
5042 loop
5043 fetch c_get_fapp_prov_emp into lv_fapp_prov;
5044 exit when c_get_fapp_prov_emp%NOTFOUND;
5045 hr_utility.trace('lv_fapp_prov : '||lv_fapp_prov);
5046
5047 lv_fapp_flag := compare_archive_data(p_assactid,
5048 ln_fapp_locked_action_id,
5049 lv_fapp_prov);
5050
5051 if lv_fapp_flag = 'Y' then
5052
5053 hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
5054 hr_utility.trace('Archiving RL1 Amendment Flag : ' || lv_fapp_flag);
5055
5056 ff_archive_api.create_archive_item(
5057 p_archive_item_id => l_archive_item_id
5058 ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_AMENDMENT_FLAG')
5059 ,p_archive_value => lv_fapp_flag
5060 ,p_archive_type => 'AAP'
5061 ,p_action_id => p_assactid
5062 ,p_legislation_code => 'CA'
5063 ,p_object_version_number => l_object_version_number
5064 ,p_context_name1 => 'JURISDICTION_CODE'
5065 ,p_context1 => lv_fapp_prov
5066 ,p_context_name2 => 'TAX_UNIT_ID'
5067 ,p_context2 => l_tax_unit_id
5068 ,p_some_warning => l_some_warning
5069 );
5070
5071 end if;
5072
5073 end loop;
5074 close c_get_fapp_prov_emp;
5075
5076 end; -- report_type validation
5077
5078 END IF; -- report type validation for FAPP
5079 hr_utility.trace('End of Provincial YE Amendment PP Validation');
5080
5081 Exception when no_data_found then
5082 hr_utility.trace('Report type not found for given Payroll_action ');
5083 null;
5084 End;
5085 -- End of Provincial YE Amendment Pre-Process Validation
5086
5087 end eoy_archive_data;
5088
5089
5090 /* Name : eoy_range_cursor
5091 Purpose : This returns the select statement that is used to created the
5092 range rows for the Year End Pre-Process.
5093 Arguments :
5094 Notes :
5095 */
5096
5097 procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
5098
5099 l_pre_organization_id varchar2(50);
5100 l_archive boolean:= FALSE;
5101 l_business_group number;
5102 l_year_start date;
5103 l_year_end date;
5104
5105 begin
5106
5107 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
5108 legislative_parameters),
5109 trunc(effective_date,'Y'),
5110 effective_date,
5111 business_group_id
5112 into l_pre_organization_id,
5113 l_year_start,
5114 l_year_end,
5115 l_business_group
5116 from pay_payroll_actions
5117 where payroll_action_id = pactid;
5118
5119 sqlstr := 'select distinct asg.person_id
5120 from pay_all_payrolls_f ppy,
5121 pay_payroll_actions ppa,
5122 pay_assignment_actions paa,
5123 per_all_assignments_f asg,
5124 pay_payroll_actions ppa1
5125 where ppa1.payroll_action_id = :payroll_action_id
5126 and ppa.effective_date between
5127 fnd_date.canonical_to_date('''||
5128 fnd_date.date_to_canonical(l_year_start)||''') and
5129 fnd_date.canonical_to_date('''||
5130 fnd_date.date_to_canonical(l_year_end)||''')
5131 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
5132 and ppa.action_status = ''C''
5133 and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
5134 and ppa.payroll_action_id = paa.payroll_action_id
5135 and paa.tax_unit_id in
5136 (select hoi.organization_id
5137 from hr_organization_information hoi
5138 where hoi.org_information_context = ''Canada Employer Identification''
5139 and hoi.org_information2 = '''|| l_pre_organization_id ||''''||'
5140 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1''))
5141 and paa.action_status = ''C''
5142 and paa.assignment_id = asg.assignment_id
5143 and ppa.business_group_id = asg.business_group_id + 0
5144 and ppa.effective_date between asg.effective_start_date
5145 and asg.effective_end_date
5146 and asg.assignment_type = ''E''
5147 and ppa.payroll_id = ppy.payroll_id
5148 and ppy.business_group_id = '||to_char(l_business_group)||'
5149 and exists (select 1
5150 from pay_action_contexts pac,
5151 ff_contexts fc
5152 where pac.assignment_id = paa.assignment_id
5153 and pac.assignment_action_id = paa.assignment_action_id
5154 and pac.context_id = fc.context_id
5155 and fc.context_name = ''JURISDICTION_CODE''
5156 and pac.context_value = ''QC'' )
5157 order by asg.person_id';
5158
5159 l_archive := chk_gre_archive(pactid);
5160 if g_archive_flag = 'N' then
5161 hr_utility.trace('eoy_range_cursor archiving employer data');
5162 /* Now the archiver has provision for archiving payroll_action_level data . So make use of that */
5163 eoy_archive_gre_data(pactid,
5164 l_pre_organization_id);
5165 hr_utility.trace('eoy_range_cursor archived employer data');
5166 end if;
5167
5168 end eoy_range_cursor;
5169
5170 end pay_ca_eoy_rl1_archive;