[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_YREND_ARCH
Source
1 PACKAGE BODY PAY_MX_YREND_ARCH AS
2 /* $Header: paymxyrendarch.pkb 120.32.12010000.4 2008/08/06 06:37:33 ubhat ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2005 Oracle Corporation |
5 | IDC, Hyderabad, India |
6 | All rights reserved. |
7 +=========================================================================+
8 Package File Name : paymxyrendarch.pkb
9 Description : This package contains the procedures needed to
10 implement Year End Archiver for Mexico HRMS
11 localization (MX).
12
13
14 Change List:
15 ------------
16
17 =========================================================================
18 Version Date Author Bug No. Description of Change
19 ------- ----------- -------- ------- --------------------------------
20 115.0 06-SEP-2005 ardsouza Initial Version
21 115.1 16-SEP-2005 ardsouza Modified range_cursor to check
22 only Completed runs of past
23 archiver or Format 37.
24 Relaxed date constraint on cursor
25 c_get_eff_date to allow terminated
26 employees.
27 115.2 29-SEP-2005 sdahiya 4625794 Modified range_code and
28 assignment_action_code. Added
29 sub-programs gre_exists and
30 load_gre.
31 115.3 04-OCT-2005 sdahiya Modified archive_code to archive
32 data only for that legal employer
33 which was selected at the
34 parameter window.
35
36 4649954 Union Worker should be archived as
37 "N" when collective agreement on
38 assignment form is null.
39 115.4 14-OCT-2005 sdahiya - Removed action_status = 'C' check
40 - Modified range code to pick
41 terminated/re-hired persons too.
42 - Added missing join condition
43 for effective dates in cursor
44 c_get_ytd_aaid.
45 - Added join with
46 pay_action_classifications in
47 c_get_ytd_aaid.
48 115.5 18-OCT-2005 ardsouza Modified to store start and end
49 dates instead of months.
50 Collective agreement of all
51 assignments for the person checked
52 to derive Union Worker flag.
53 115.6 24-OCT-2005 ardsouza - Modified to stamp 31st Dec on
54 archive record for active EEs.
55 - State ID archived.
56 - 31st Dec always used for fetching
57 latest YTD aaid .
58 4690778 - Seniority archived as null for
59 Active EEs.
60 - Person to be picked up if any
61 assignment found in assignment set.
62 4687345 - Added date check in cursor
63 c_get_emp_asg_range.
64 4693525 - Corrected calculation for Tax
65 Subsidy Proportion.
66 115.7 26-OCT-2005 ardsouza - Modified cursors c_get_emp_asg
67 and c_get_emp_asg_range to create
68 multiple assignment actions for a
69 re-hired person, if archiver not
70 already run for previous stint.
71 - Relaxed date constraint on
72 c_get_ytd_aaid to allow terminated
73 EEs.
74 4703130 Hyphens not used for validation in
75 ER RFC.
76 115.8 02-NOV-2005 ardsouza 4712450 - Subsidy Proportion applied only
77 if a different one used and if
78 Annual Tax Adjustment is run.
79 - Archived "ISR Exempt by Previous
80 ER".
81 - Rounded Subsidy Proportion to 4
82 places instead of 2.
83 115.9 03-NOV-2005 ardsouza 4693525 - Reverted changes made in 115.6
84 for "Subsidy Proportion Applied".
85 The changes are needed only for
86 "Subsidy Proportion".
87 115.10 07-NOV-2005 ardsouza - Annual Tax Adj Run checked only
88 for "Subsidy Proportion Applied"
89 and not "Subsidy Proportion".
90 115.11 14-DEC-2005 ardsouza - Modified to allow multiple runs
91 of Archiver for same period of
92 service as long as payroll runs
93 exist after the last archiver was
94 run.
95 - Effective date of balance calls
96 to be the effective date specified
97 as parameter.
98 - The second archiver run would
99 always lock the first.
100 115.12 06-JAN-2006 vpandya Replace get_seniority function with
101 get_seniority_social_security to
102 get seniority years.
103 115.13 12-JAN-2006 ardsouza 4938724 - Modified to use p_effective_date
104 as the effective_date for all
105 purposes.
106 115.14 17-JAN-2006 ardsouza 4960302 - Termination Date would still be
107 used to fetch person details.
108 4956977 Reverted changes in 115.12.
109 115.15 17-JAN-2006 ardsouza Bumped version to fix arcs message.
110 115.16 25-JAN-2006 ardsouza 4998030 Corrected c_chk_last_archiver.
111 115.17 02-FEB-2006 ardsouza 5004297 '<First Name> <Second Name>' to be
112 stored under "Names".
113 5002968 Seniority not archived if archiver
114 is run for PTU, even for ex-EEs.
115 115.18 06-FEB-2006 ardsouza 5019199 R,Q,B,V,I actions after the prev
116 archiver would be detected based on
117 effective date rather than action
118 sequence because Archiver itself
119 is a Non-Sequenced action.
120 115.19 06-FEB-2006 ardsouza 5019199 Fix in 115.18 modified to restrict
121 R,Q,B,V,I actions only upto the
122 effective date of the archiver.
123 115.20 13-FEB-2006 vpandya 5035094 Changed populate_balances:
124 When YREND Archiver run previously
125 and it is run again for PTU,
126 ISR Withheld would be
127 ISR Withheld YTD - ISR Withheld of
128 previous archived value.
129 115.21 15-FEB-2006 ardsouza 5002968 Seniority displayed as 0 instead
130 of NULL, when not needed.
131 115.22 10-MAR-2006 ardsouza PL-SQL table g_gre_tab made public
132 for use within "PAY_MX_PTU_CALC".
133 115.23 04-MAY-2006 ardsouza 5205255 Removed unwanted table references
134 in cursor c_chk_asg.
135 115.24 02-AUG-2006 sbairagi 5042700 Cursor c_get_emp_asg of procedure
136 assignment_action_code is tuned.
137 115.25 03-AUG-2006 vpandya same as 115.24. Arcsed in 120
138 version mistakenly. Got error and
139 corrected in 115.25.
140 115.26 07-AUG-2006 nragavar 5457394 Archive_code to archive 'Y' where
141 there exists AnnTaxAdj process run.
142 Pkg has been modified to take out
143 all un-wanted comments to make the
144 package more readable. Procedure
145 archive_code has been modifed to
146 consider the action_status to 'C'
147 ie to select the assignments that
148 had been processed successfully.
149 115.28 20-Sep-2006 nragavar 5552748 added code to archive two flags
150 RATE_1991_IND,RATE_FISCAL_YEAR_IND
151 115.29 26-Sep-2006 vmehta 5565656 Changed the logic for identifying
152 union member. Use the
153 LABOUR_UNION_MEMBER_FLAG instead
154 of collective agreement lookup.
155 115.30 06-Dec-2006 vpandya 5701000 Changed assignment_action_code.
156 Initializing previous archiver date
157 and asg act id for each assignment.
158 115.31 06-Dec-2006 vpandya 5701701 Changed archive_code:
159 Taking greatest of hire date and
160 archiver start date. Also taking
161 least of archiver end date and
162 p_effective_date.
163 115.32 03-Jan-2007 vpandya 5714195 Changed assignment_action_code:
164 cursors c_chk_last_archiver and
165 c_chk_non_arch_runs.
166 Also changed archive_code:
167 added a condition where date for
168 PTU is populating.
169 115.33 11-Sep-2007 nrgavar 5923989 Modified to archive ISR Calculated,
170 Creditable Subsidy and
171 non-creditable subsidy.
172 115.34 17-Sep-2007 vpandya 5002968 Changed archive_date: seniority
173 should not be archived for term-ee
174 when YREND arch is run only 4 PTU.
175 as mentioned in 115.17
176 115.36 25-Feb-2008 nragavar 6807997 modified the function archive_code
177 115.37 25-Feb-2008 nragavar 6807997 modified the function populate_balances
178 */
179 --
180 /******************************************************************************
181 ** Global Variables
182 ******************************************************************************/
183 gv_package VARCHAR2(100);
184 gn_prev_asg_act_id NUMBER;
185
186
187 --------------------------------------------------------------------------
188 -- --
189 -- Name : load_gre --
190 -- Type : Procedure --
191 -- Access : Private --
192 -- Description : Procedure to load all GREs for a given legal --
193 -- employer based on the Mexico Statutory Reporting --
194 -- Hierarchy as on the given date. --
195 -- Parameters : --
196 -- IN : p_business_group_id NUMBER --
197 -- p_le_id NUMBER --
198 -- p_effective_date DATE --
199 -- OUT : N/A --
200 -- --
201 --------------------------------------------------------------------------
202 PROCEDURE load_gre(p_business_group_id NUMBER,
203 p_le_id NUMBER,
204 p_effective_date DATE) IS
205 --
206 CURSOR csr_get_gres IS
207 SELECT gre_node.entity_id
208 FROM per_gen_hierarchy_nodes gre_node,
209 per_gen_hierarchy_nodes le_node,
210 per_gen_hierarchy_versions hier_ver,
211 fnd_lookup_values lv
212 WHERE gre_node.node_type = 'MX GRE'
213 AND le_node.node_type = 'MX LEGAL EMPLOYER'
214 AND le_node.entity_id = p_le_id
215 AND le_node.business_group_id = p_business_group_id
216 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
217 AND gre_node.business_group_id = le_node.business_group_id
218 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
219 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
220 AND status = lv.lookup_code
221 AND lv.meaning = 'Active'
222 AND lv.LANGUAGE = 'US'
223 AND lv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
224 AND p_effective_date BETWEEN hier_ver.date_from
225 AND NVL(hier_ver.date_to, hr_general.end_of_time);
226
227 lv_procedure_name VARCHAR2(100);
228 ln_gre_id NUMBER;
229
230 BEGIN
231
232 lv_procedure_name := '.load_gre';
233
234 hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
235
236 hr_utility.trace ('parameters ...');
237 hr_utility.trace ('p_business_group_id = '||p_business_group_id);
238 hr_utility.trace ('p_le_id = '||p_le_id);
239 hr_utility.trace ('p_effective_date = '||p_effective_date);
240
241 g_gre_tab.delete();
242 OPEN csr_get_gres;
243 LOOP
244 FETCH csr_get_gres INTO ln_gre_id;
245 EXIT WHEN csr_get_gres%NOTFOUND;
246 g_gre_tab (g_gre_tab.count() + 1) := ln_gre_id;
247 END LOOP;
248 CLOSE csr_get_gres;
249
250 IF g_gre_tab.count() > 0 THEN
251 hr_utility.trace('List of GREs ...');
252 FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
253 hr_utility.trace(g_gre_tab(cntr_gre));
254 END LOOP;
255 ELSE
256 hr_utility.trace('No GREs found.');
257 END IF;
258
259 hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
260 END load_gre;
261
262 --------------------------------------------------------------------------
263 -- --
264 -- Name : gre_exists --
265 -- Type : Function --
266 -- Access : Public --
267 -- Description : Function to determine whether a GRE exists in the --
268 -- global variable g_gre_tab --
269 -- Parameters : --
270 -- IN : p_gre_id NUMBER --
271 -- OUT : N/A --
272 -- RETURN : NUMBER --
273 -- --
274 --------------------------------------------------------------------------
275 FUNCTION gre_exists (p_gre_id NUMBER)
276 RETURN NUMBER IS
277
278 lv_procedure_name VARCHAR2(100);
279
280 BEGIN
281 lv_procedure_name := '.gre_exists';
282 hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
283 hr_utility.trace('p_gre_id = ' || p_gre_id);
284
285 IF g_gre_tab.count() <> 0 THEN
286 FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
287 IF g_gre_tab (cntr_gre) = p_gre_id THEN
288 hr_utility.trace ('GRE exists');
289 hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
290 RETURN 1;
291 END IF;
292 END LOOP;
293 END IF;
294
295 hr_utility.trace ('GRE does not exist');
296 hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
297 RETURN 0;
298 END gre_exists;
299
300 /******************************************************************************
301 Name : get_payroll_action_info
302 Purpose : This returns the Payroll Action level
303 information for Year End Archiver.
304 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
305 p_end_date - End date of Archiver
306 p_business_group_id - Business Group ID
307 p_legal_employer_id - Legal Employer ID when submitting Archiver
308 p_asg_set_id - Assignment Set ID when submitting Archiver
309 ******************************************************************************/
310 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
311 ,p_end_date OUT NOCOPY DATE
312 ,p_business_group_id OUT NOCOPY NUMBER
313 ,p_legal_employer_id OUT NOCOPY NUMBER
314 ,p_asg_set_id OUT NOCOPY NUMBER
315 )
316 IS
317 CURSOR c_payroll_Action_info
318 (cp_payroll_action_id IN NUMBER) IS
319 SELECT effective_date,
320 business_group_id,
321 pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
322 legislative_parameters) Legal_Employer_ID,
323 pay_mx_utility.get_parameter('TRANSFER_ASSIGNMENT_SET_ID',
324 legislative_parameters) Assignment_SET_ID
325 FROM pay_payroll_actions
326 WHERE payroll_action_id = cp_payroll_action_id;
327
328 ld_end_date DATE;
329 ln_business_group_id NUMBER;
330 ln_asg_set_id NUMBER;
331 ln_legal_er_id NUMBER;
332 lv_procedure_name VARCHAR2(100);
333
334 lv_error_message VARCHAR2(200);
335 ln_step NUMBER;
336
337 BEGIN
338 lv_procedure_name := '.get_payroll_action_info';
339
340 hr_utility.set_location(gv_package || lv_procedure_name, 10);
341 ln_step := 1;
342 OPEN c_payroll_action_info(p_payroll_action_id);
343 FETCH c_payroll_action_info INTO ld_end_date,
344 ln_business_group_id,
345 ln_legal_er_id,
346 ln_asg_set_id;
347 CLOSE c_payroll_action_info;
348
349 hr_utility.set_location(gv_package || lv_procedure_name, 30);
350
351 p_end_date := ld_end_date;
352 p_business_group_id := ln_business_group_id;
353 p_legal_employer_id := ln_legal_er_id;
354 p_asg_set_id := ln_asg_set_id;
355
356 hr_utility.set_location(gv_package || lv_procedure_name, 50);
357 ln_step := 2;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
362 gv_package || lv_procedure_name;
363
364 hr_utility.trace(lv_error_message || '-' || SQLERRM);
365
366 lv_error_message :=
367 pay_emp_action_arch.set_error_message(lv_error_message);
368
369 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
370 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
371 hr_utility.raise_error;
372
373 END get_payroll_action_info;
374
375 /************************************************************
376 Name : populate_balances
377 Purpose : This procedure archives Balances which are used
378 in Year End Reporting for Mexico.
379 Arguments :
380 Notes :
381 ************************************************************/
382 PROCEDURE populate_balances(p_archive_action_id IN NUMBER
383 ,p_ytd_action_id IN NUMBER
384 ,p_tax_unit_id IN NUMBER
385 ,p_prev_archiver_exists IN VARCHAR2)
386 IS
387 --
388 lv_procedure_name VARCHAR2(100);
389 lv_error_message VARCHAR2(200);
390 ln_step NUMBER;
391 ln_index NUMBER;
392
393 ln_arch_user_entity_id NUMBER;
394 ln_value NUMBER;
395 ln_ovn NUMBER;
396 l_some_warning BOOLEAN;
397 ln_archive_item_id NUMBER;
398 ln_prev_isr_whld_value NUMBER;
399 lv_arch_user_entity_name ff_user_entities.user_entity_name%TYPE;
400 lv_live_user_entity_name ff_user_entities.user_entity_name%TYPE;
401 ln_arc_item NUMBER;
402
403 -- Get balances for archival
404 CURSOR c_get_balances IS
405 SELECT DISTINCT
406 fue_live.user_entity_name,
407 pay_balance_pkg.get_value(fue_live.creator_id,
408 p_ytd_action_id)
409 FROM pay_bal_attribute_definitions pbad,
410 pay_balance_attributes pba,
411 pay_defined_balances pdb_attr,
412 pay_defined_balances pdb_call,
413 pay_balance_dimensions pbd,
414 ff_user_entities fue_live
415 WHERE pbad.attribute_name = 'Year End Balances'
416 AND pbad.legislation_code = 'MX'
417 AND pba.attribute_id = pbad.attribute_id
418 AND pdb_attr.defined_balance_id = pba.defined_balance_id
419 AND pdb_attr.balance_type_id = pdb_call.balance_type_id
420 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
421 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
422 AND pbd.legislation_code = pbad.legislation_code
423 AND fue_live.creator_id = pdb_call.defined_balance_id
424 AND fue_live.creator_type = 'B'
425 ORDER BY fue_live.user_entity_name;
426
427 -- Get Profit Sharing balances for archival
428 CURSOR c_get_PTU_balances IS
429 SELECT DISTINCT
430 fue_live.user_entity_name,
431 pay_balance_pkg.get_value(fue_live.creator_id,
432 p_ytd_action_id)
433 FROM pay_defined_balances pdb_call,
434 pay_balance_dimensions pbd,
435 pay_balance_types pbt,
436 ff_user_entities fue_live
437 WHERE pbt.balance_name IN ('ISR Withheld',
438 'Year End ISR Subject for Profit Sharing',
439 'Year End ISR Exempt for Profit Sharing')
440 AND pbt.balance_type_id = pdb_call.balance_type_id
441 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
442 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
443 AND pbd.legislation_code = pbt.legislation_code
444 AND pbt.legislation_code = 'MX'
445 AND fue_live.creator_id = pdb_call.defined_balance_id
446 AND fue_live.creator_type = 'B'
447 ORDER BY fue_live.user_entity_name;
448
449 -- Get archive DBI user entity ID
450 CURSOR c_get_arch_ue_id(cp_archive_item_name VARCHAR2)
451 IS
452 SELECT user_entity_id
453 FROM ff_user_entities
454 WHERE user_entity_name = cp_archive_item_name
455 AND creator_type = 'X'
456 AND creator_id = 0
457 AND legislation_code = 'MX';
458
459 BEGIN
460 --
461 lv_procedure_name := '.populate_balances';
462 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
463
464 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
465
466 -- Create the Archive DBI routes if they don't exist
467 --
468 hr_utility.set_location(gv_package || lv_procedure_name, 10);
469 pay_mx_archive_dbi_pkg.create_archive_routes;
470
471 IF p_prev_archiver_exists = 'Y' THEN
472
473 hr_utility.set_location(gv_package || lv_procedure_name, 20);
474 OPEN c_get_PTU_balances;
475
476 ELSE
477
478 hr_utility.set_location(gv_package || lv_procedure_name, 30);
479 OPEN c_get_balances;
480
481 END IF;
482
483 LOOP
484
485 IF p_prev_archiver_exists = 'Y' THEN
486
487 hr_utility.set_location(gv_package || lv_procedure_name, 40);
488 FETCH c_get_PTU_balances INTO lv_live_user_entity_name,
489 ln_value;
490 EXIT WHEN c_get_PTU_balances%NOTFOUND;
491
492
493 ELSE
494
495 hr_utility.set_location(gv_package || lv_procedure_name, 50);
496 FETCH c_get_balances INTO lv_live_user_entity_name,
497 ln_value;
498 EXIT WHEN c_get_balances%NOTFOUND;
499
500 END IF;
501
502 -- Create the Archive DBI, if it doesn't exist
503 --
504 hr_utility.set_location(gv_package || lv_procedure_name, 60);
505 lv_arch_user_entity_name := 'A_' || lv_live_user_entity_name;
506 pay_mx_archive_dbi_pkg.create_archive_dbi(lv_arch_user_entity_name);
507
508 hr_utility.set_location(gv_package || lv_procedure_name, 70);
509 OPEN c_get_arch_ue_id(lv_arch_user_entity_name);
510 FETCH c_get_arch_ue_id INTO ln_arch_user_entity_id;
511 CLOSE c_get_arch_ue_id;
512
513 hr_utility.set_location(gv_package || lv_procedure_name, 80);
514 hr_utility.trace('Archive User Entity ID: '||
515 ln_arch_user_entity_id);
516 hr_utility.trace('Value: ' || ln_value);
517
518 IF p_prev_archiver_exists = 'Y' AND
519 lv_live_user_entity_name = 'ISR_WITHHELD_PER_PDS_GRE_YTD' THEN
520
521 /**************************************************************
522 ** ISR Withheld for PTU would be
523 ** ISR Withheld YTD - ISR Withheld of previous archived value
524 ** Whenevere there is previous archiver is run.
525 **************************************************************/
526 begin
527 hr_utility.set_location(gv_package || lv_procedure_name, 85);
528 SELECT fai.value
529 INTO ln_prev_isr_whld_value
530 FROM ff_archive_items fai
531 WHERE fai.context1 = gn_prev_asg_act_id
532 AND fai.user_entity_id = ln_arch_user_entity_id;
533 hr_utility.set_location(gv_package || lv_procedure_name, 87);
534 exception
535 when no_data_found then
536 ln_prev_isr_whld_value := 0;
537 end;
538
539
540 ln_value := ln_value - ln_prev_isr_whld_value;
541
542 END IF;
543
544 select count(*)
545 into ln_arc_item
546 from ff_archive_items fai,
547 ff_archive_item_contexts faic
548 where fai.archive_item_id = faic.archive_item_id
549 and fai.user_entity_id = ln_arch_user_entity_id
550 and fai.context1 = p_archive_action_id
551 and fai.value = ln_value
552 and faic.context = p_tax_unit_id;
553
554 if nvl(ln_arc_item,0) = 0 then
555
556 ff_archive_api.create_archive_item(
557 p_archive_item_id => ln_archive_item_id,
558 p_user_entity_id => ln_arch_user_entity_id,
559 p_archive_value => ln_value,
560 p_archive_type => 'AAP',
561 p_action_id => p_archive_action_id,
562 p_legislation_code => 'MX',
563 p_object_version_number => ln_ovn,
564 p_some_warning => l_some_warning,
565 p_context_name1 => 'TAX_UNIT_ID',
566 p_context1 => p_tax_unit_id);
567 end if;
568 END LOOP;
569
570 hr_utility.set_location(gv_package || lv_procedure_name, 90);
571
572 IF p_prev_archiver_exists = 'Y' THEN
573 close c_get_PTU_balances;
574 ELSE
575 close c_get_balances;
576 END IF;
577
578 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
579 --
580 END populate_balances;
581
582 /******************************************************************
583 Name : range_code
584 Purpose : This returns the select statement that is
585 used to create the range rows for the Year End
586 Archiver.
587 Arguments :
588 Notes : Calls procedure - get_payroll_action_info
589 ******************************************************************/
590 PROCEDURE range_code(
591 p_payroll_action_id IN NUMBER
592 ,p_sqlstr OUT NOCOPY VARCHAR2)
593 IS
594
595 ld_end_date DATE;
596 ld_start_date DATE;
597 ln_business_group_id NUMBER;
598 ln_asg_set_id NUMBER;
599 ln_legal_employer_id NUMBER;
600
601 lv_sql_string VARCHAR2(32000);
602 lv_procedure_name VARCHAR2(100);
603
604 BEGIN
605 lv_procedure_name := '.range_code';
606
607 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
608 hr_utility.set_location(gv_package || lv_procedure_name, 10);
609 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
610 ,p_end_date => ld_end_date
611 ,p_business_group_id => ln_business_group_id
612 ,p_legal_employer_id => ln_legal_employer_id
613 ,p_asg_set_id => ln_asg_set_id);
614 hr_utility.set_location(gv_package || lv_procedure_name, 20);
615
616 load_gre (ln_business_group_id,
617 ln_legal_employer_id,
618 ld_end_date);
619
620 ld_start_date := TRUNC(ld_end_date, 'Y');
621
622 IF ln_asg_set_id IS NULL THEN
623
624 lv_sql_string :=
625 'SELECT DISTINCT paf.person_id
626 FROM pay_assignment_actions paa,
627 pay_payroll_actions ppa,
628 per_assignments_f paf
629 WHERE ppa.business_group_id = ' || ln_business_group_id || '
630 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
631 fnd_date.date_to_canonical(ld_start_date) || ''')
632 AND fnd_date.canonical_to_date(''' ||
633 fnd_date.date_to_canonical(ld_end_date) || ''')
634 AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
635 AND paa.action_status = ''C''
636 AND ppa.payroll_action_id = paa.payroll_action_id
637 AND paa.source_action_id IS NULL
638 AND paf.assignment_id = paa.assignment_id
639 AND ppa.effective_date BETWEEN paf.effective_start_date
640 AND paf.effective_end_date
641 AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
642 AND :payroll_action_id > 0
643 ORDER BY paf.person_id';
644 ELSE
645
646 lv_sql_string :=
647 'SELECT DISTINCT paf.person_id
648 FROM pay_assignment_actions paa,
649 pay_payroll_actions ppa,
650 per_assignments_f paf
651 WHERE ppa.business_group_id = ' || ln_business_group_id || '
652 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
653 fnd_date.date_to_canonical(ld_start_date) || ''')
654 AND fnd_date.canonical_to_date(''' ||
655 fnd_date.date_to_canonical(ld_end_date) || ''')
656 AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
657 AND paa.action_status = ''C''
658 AND ppa.payroll_action_id = paa.payroll_action_id
659 AND paa.source_action_id IS NULL
660 AND paf.assignment_id = paa.assignment_id
661 AND ppa.effective_date BETWEEN paf.effective_start_date
662 AND paf.effective_end_date
663 AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
664 AND EXISTS
665 (SELECT ''x''
666 FROM hr_assignment_sets has,
667 hr_assignment_set_amendments hasa,
668 per_assignments_f paf_all
669 WHERE has.assignment_set_id = ' || ln_asg_set_id || '
670 AND has.assignment_set_id = hasa.assignment_set_id
671 AND hasa.assignment_id = paf_all.assignment_id
672 AND paf_all.person_id = paf.person_id
673 AND hasa.include_or_exclude = ''I'')
674 AND :payroll_action_id > 0
675 ORDER BY paf.person_id';
676
677 END IF; -- ln_asg_set_id is null
678
679 hr_utility.set_location(gv_package || lv_procedure_name, 30);
680 p_sqlstr := lv_sql_string;
681 hr_utility.trace ('SQL string :' ||p_sqlstr);
682 hr_utility.set_location(gv_package || lv_procedure_name, 50);
683 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
684
685 END range_code;
686
687 /************************************************************
688 Name : assignment_action_code
689 Purpose : This creates the assignment actions for
690 a specific chunk of people to be archived
691 by the Year End Archiver process.
692 Arguments :
693 Notes : Calls procedure - get_payroll_action_info
694 ************************************************************/
695 PROCEDURE assignment_action_code(
696 p_payroll_action_id IN NUMBER
697 ,p_start_person_id IN NUMBER
698 ,p_end_person_id IN NUMBER
699 ,p_chunk IN NUMBER)
700 IS
701
702 CURSOR c_chk_asg (cp_asg_set_id NUMBER,
703 cp_asg_id NUMBER) IS
704 SELECT 'X'
705 FROM hr_assignment_sets has,
706 hr_assignment_set_amendments hasa
707 WHERE has.assignment_set_id = cp_asg_set_id
708 AND has.assignment_set_id = hasa.assignment_set_id
709 AND hasa.assignment_id IN (SELECT DISTINCT
710 paf_all.assignment_id
711 FROM per_assignments_f paf,
712 per_assignments_f paf_all
713 WHERE paf.person_id = paf_all.person_id
714 AND paf.assignment_id = cp_asg_id)
715 AND hasa.include_or_exclude = 'E';
716
717 CURSOR c_get_emp_asg_range (cp_gre_id NUMBER,
718 cp_start_date DATE,
719 cp_end_date DATE) IS
720 SELECT paf_pri.assignment_id,
721 paf_pri.person_id,
722 paf_pri.period_of_service_id
723 FROM per_assignments_f paf,
724 per_assignments_f paf_pri,
725 pay_assignment_actions paa,
726 pay_payroll_actions ppa,
727 pay_population_ranges ppr
728 WHERE paf.assignment_id = paa.assignment_id
729 AND paa.tax_unit_id = cp_gre_id
730 AND ppr.payroll_action_id = p_payroll_action_id
731 AND ppr.chunk_number = p_chunk
732 AND ppr.person_id = paf.person_id
733 AND paf_pri.period_of_service_id = paf.period_of_service_id
734 AND paf_pri.primary_flag = 'Y'
735 AND paa.payroll_action_id = ppa.payroll_action_id
736 AND ppa.action_type IN ('Q','R','B','V','I')
737 AND ppa.effective_date BETWEEN cp_start_date
738 AND cp_end_date
739 AND paf_pri.effective_start_date <= cp_end_date
740 AND paf_pri.effective_end_date >= cp_start_date
741 AND ppa.effective_date BETWEEN paf.effective_start_date
742 AND paf.effective_end_date
743 ORDER BY paf_pri.person_id,
744 paf_pri.effective_end_date DESC;
745
746 CURSOR c_get_emp_asg (cp_gre_id NUMBER,
747 cp_bg_id NUMBER,
748 cp_start_date DATE,
749 cp_end_date DATE) IS
750 SELECT /*+ USE_NL(pap paf) */
751 paf_pri.assignment_id,
752 paf_pri.person_id,
753 paf_pri.period_of_service_id
754 FROM per_assignments_f paf,
755 per_assignments_f paf_pri,
756 pay_assignment_actions paa,
757 pay_payroll_actions ppa,
758 pay_all_payrolls_f pap
759 WHERE ppa.business_group_id + 0 = cp_bg_id
760 AND ppa.effective_date BETWEEN cp_start_date
761 AND cp_end_date
762 AND ppa.action_type IN ('Q','R','B','V','I')
763 AND pap.business_group_id = cp_bg_id
764 AND ppa.payroll_id = pap.payroll_id
765 AND ppa.payroll_action_id = paa.payroll_action_id
766 AND paa.source_action_id IS NULL
767 AND paf.assignment_id = paa.assignment_id
768 AND paf_pri.period_of_service_id = paf.period_of_service_id
769 AND paf_pri.primary_flag = 'Y'
770 AND ppa.effective_date BETWEEN paf.effective_start_date
771 AND paf.effective_end_date
772 AND paf_pri.effective_start_date <= cp_end_date
773 AND paf_pri.effective_end_date >= cp_start_date
774 AND paa.tax_unit_id = cp_gre_id
775 AND paf_pri.person_id = paf.person_id
776 AND paf.person_id BETWEEN p_start_person_id
777 AND p_end_person_id
778 ORDER BY paf_pri.person_id,
779 paf_pri.effective_end_date DESC;
780
781 -- Check if any previous archiver exists for the same period of service
782 --
783 CURSOR c_chk_last_archiver(cp_period_of_service_id NUMBER,
784 cp_start_date DATE,
785 cp_end_date DATE) IS
786 SELECT ppa1.effective_date,
787 paa1.assignment_action_id
788 FROM pay_payroll_actions ppa1,
789 pay_assignment_actions paa1,
790 per_assignments_f paf1
791 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
792 AND paa1.assignment_id = paf1.assignment_id
793 AND paf1.period_of_service_id = cp_period_of_service_id
794 AND ppa1.report_type = 'MX_YREND_ARCHIVE'
795 AND ppa1.report_qualifier = 'MX'
796 AND ppa1.report_category = 'ARCHIVE'
797 AND paf1.effective_start_date <= cp_end_date
798 AND paf1.effective_end_date >= cp_start_date
799 AND TO_CHAR(ppa1.effective_date, 'YYYY')
800 = TO_CHAR(cp_end_date, 'YYYY')
801 ORDER BY ppa1.effective_date DESC;
802
803 -- Check if runs exist after the last archiver run
804 --
805 CURSOR c_chk_non_arch_runs(cp_period_of_service_id NUMBER,
806 cp_prev_arch_eff_date DATE,
807 cp_start_date DATE,
808 cp_end_date DATE) IS
809 SELECT 'Y'
810 FROM pay_payroll_actions ppa2,
811 pay_assignment_actions paa2,
812 per_assignments_f paf2
813 WHERE ppa2.payroll_action_id = paa2.payroll_action_id
814 AND ppa2.action_type IN ('R', 'Q', 'B', 'V', 'I')
815 AND paa2.assignment_id = paf2.assignment_id
816 AND paf2.period_of_service_id = cp_period_of_service_id
817 AND ppa2.effective_date > cp_prev_arch_eff_date
818 AND ppa2.effective_date <= cp_end_date
819 AND paf2.effective_start_date <= cp_end_date
820 AND paf2.effective_end_date >= cp_start_date;
821
822 ln_assignment_id NUMBER;
823 ln_tax_unit_id NUMBER;
824
825 ld_end_date DATE;
826 ld_start_date DATE;
827 ln_business_group_id NUMBER;
828 ln_legal_employer_id NUMBER;
829 ln_asg_set_id NUMBER;
830
831 ln_yrend_action_id NUMBER;
832
833 lv_procedure_name VARCHAR2(100);
834 lv_error_message VARCHAR2(200);
835 ln_step NUMBER;
836
837 lb_range_person BOOLEAN;
838 ln_person_id NUMBER;
839 ln_prev_pos_id NUMBER;
840 ln_pos_id NUMBER;
841 lv_excl_flag VARCHAR2(2);
842 lv_run_exists VARCHAR2(2);
843
844 ln_prev_arch_aaid NUMBER;
845 ld_prev_arch_eff_date DATE;
846
847 BEGIN
848 lv_procedure_name := '.assignment_action_code';
849 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
850
851 ln_pos_id := -1;
852 ln_prev_pos_id := -1;
853 lv_excl_flag := '-1';
854 lv_run_exists := 'N';
855
856 hr_utility.trace('p_payroll_action_id = '|| p_payroll_action_id);
857 hr_utility.trace('p_start_person_id = '|| p_start_person_id);
858 hr_utility.trace('p_end_person_id = '|| p_end_person_id);
859 hr_utility.trace('p_chunk = '|| p_chunk);
860
861 ln_step := 1;
862 hr_utility.set_location(gv_package || lv_procedure_name, 10);
863
864 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
865 ,p_end_date => ld_end_date
866 ,p_business_group_id => ln_business_group_id
867 ,p_legal_employer_id => ln_legal_employer_id
868 ,p_asg_set_id => ln_asg_set_id);
869
870 hr_utility.set_location(gv_package || lv_procedure_name, 20);
871 hr_utility.trace('ld_end_date: ' || ld_end_date);
872 hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
873 hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
874 hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
875
876 ld_start_date := TRUNC(ld_end_date, 'Y');
877
878 hr_utility.trace('ld_start_date: ' || ld_start_date);
879
880 IF g_gre_tab.count() = 0 THEN
881
882 hr_utility.set_location(gv_package || lv_procedure_name, 30);
883
884 load_gre (ln_business_group_id,
885 ln_legal_employer_id,
886 ld_end_date);
887 END IF;
888
889 lb_range_person := pay_ac_utility.range_person_on(
890 p_report_type => 'MX_YREND_ARCHIVE'
891 ,p_report_format => 'MX_YREND_ARCHIVE'
892 ,p_report_qualifier => 'MX'
893 ,p_report_category => 'ARCHIVE');
894
895 FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
896 IF lb_range_person THEN
897 hr_utility.set_location(gv_package || lv_procedure_name, 40);
898
899 OPEN c_get_emp_asg_range(g_gre_tab(cntr_gre),
900 ld_start_date,
901 ld_end_date);
902 ELSE
903 hr_utility.set_location(gv_package || lv_procedure_name, 50);
904
905 OPEN c_get_emp_asg (g_gre_tab(cntr_gre),
906 ln_business_group_id,
907 ld_start_date,
908 ld_end_date);
909 END IF;
910
911 LOOP
912 IF lb_range_person THEN
913
914 hr_utility.trace('lb_range_person');
915
916 FETCH c_get_emp_asg_range INTO ln_assignment_id,
917 ln_person_id,
918 ln_pos_id;
919 EXIT WHEN c_get_emp_asg_range%NOTFOUND;
920 ELSE
921 FETCH c_get_emp_asg INTO ln_assignment_id,
922 ln_person_id,
923 ln_pos_id;
924 EXIT WHEN c_get_emp_asg%NOTFOUND;
925 END IF;
926
927 hr_utility.trace('Previous period of service = ' || ln_prev_pos_id);
928 hr_utility.trace('Current period of service = ' || ln_pos_id);
929 hr_utility.trace('Person ID= ' || ln_person_id);
930 hr_utility.trace('Assignment ID= ' || ln_assignment_id);
931
932 IF ln_pos_id <> ln_prev_pos_id THEN
933
934 ln_prev_pos_id := ln_pos_id;
935
936 IF ln_asg_set_id IS NOT NULL THEN
937
938 hr_utility.set_location(gv_package || lv_procedure_name,60);
939 hr_utility.trace('Assignment SET ID FOUND');
940
941 lv_excl_flag := '-1';
942 OPEN c_chk_asg (ln_asg_set_id, ln_assignment_id);
943 FETCH c_chk_asg INTO lv_excl_flag;
944 CLOSE c_chk_asg;
945
946
947 END IF;
948
949 hr_utility.trace('lv_excl_flag: '||lv_excl_flag);
950
951 IF lv_excl_flag <> 'X' THEN
952
953 ld_prev_arch_eff_date := NULL;
954 ln_prev_arch_aaid := NULL;
955
956 hr_utility.set_location(gv_package || lv_procedure_name,70);
957
958 OPEN c_chk_last_archiver(ln_pos_id,
959 ld_start_date,
960 ld_end_date);
961 FETCH c_chk_last_archiver INTO ld_prev_arch_eff_date,
962 ln_prev_arch_aaid;
963 CLOSE c_chk_last_archiver;
964
965 IF ld_prev_arch_eff_date IS NOT NULL THEN
966
967 -- A previous Year End Archiver run exists for the person's
968 -- period of service.
969 --
970
971 hr_utility.trace('Prev Arch Effective Date = ' ||
972 fnd_date.date_to_canonical(ld_prev_arch_eff_date));
973 hr_utility.trace('Prev Arch Asg Action ID = ' ||
974 ln_prev_arch_aaid);
975 hr_utility.set_location(gv_package ||
976 lv_procedure_name, 80);
977
978 lv_run_exists := 'N';
979
980 -- Check if payroll is run after year end
981 -- archiver
982
983 OPEN c_chk_non_arch_runs (ln_pos_id,
984 ld_prev_arch_eff_date,
985 ld_start_date,
986 ld_end_date);
987 FETCH c_chk_non_arch_runs INTO lv_run_exists;
988 CLOSE c_chk_non_arch_runs;
989
990 hr_utility.trace('lv_run_exists: '||lv_run_exists);
991
992 IF lv_run_exists = 'Y' THEN
993
994 -- The person has had a SEQUENCED action since the last
995 -- archiver run and should therefore be archived. The
996 -- last archiver action will be locked by this new
997 -- action.
998
999 hr_utility.set_location(gv_package ||
1000 lv_procedure_name, 90);
1001
1002 SELECT pay_assignment_actions_s.NEXTVAL
1003 INTO ln_yrend_action_id
1004 FROM dual;
1005
1006 hr_nonrun_asact.insact(ln_yrend_action_id,
1007 ln_assignment_id,
1008 p_payroll_action_id,
1009 p_chunk,
1010 g_gre_tab(cntr_gre),
1011 NULL,
1012 'U',
1013 NULL);
1014
1015 hr_utility.set_location(gv_package ||
1016 lv_procedure_name, 100);
1017
1018 UPDATE pay_assignment_actions
1019 SET serial_number = ln_person_id
1020 WHERE assignment_action_id = ln_yrend_action_id;
1021
1022 hr_utility.trace('Archiver asg action ' ||
1023 ln_yrend_action_id || ' created.');
1024
1025 -- insert an interlock to this action
1026 hr_utility.trace('Locking Action = ' ||
1027 ln_yrend_action_id);
1028 hr_utility.trace('Locked Action = ' ||
1029 ln_prev_arch_aaid);
1030 hr_nonrun_asact.insint(ln_yrend_action_id,
1031 ln_prev_arch_aaid);
1032
1033 hr_utility.set_location(gv_package ||
1034 lv_procedure_name, 110);
1035
1036 ELSE
1037
1038 hr_utility.set_location(gv_package ||
1039 lv_procedure_name, 120);
1040
1041 hr_utility.trace('The person has not been paid ' ||
1042 'since the last archiver and is therefore skipped.');
1043
1044 END IF;
1045
1046 ELSE
1047
1048 -- No previous archiver run exists for this person.
1049 -- New assignment action would still be created, but no
1050 -- interlocks inserted.
1051
1052 hr_utility.set_location(gv_package ||
1053 lv_procedure_name, 130);
1054
1055 SELECT pay_assignment_actions_s.NEXTVAL
1056 INTO ln_yrend_action_id
1057 FROM dual;
1058
1059 hr_utility.set_location(gv_package ||
1060 lv_procedure_name, 140);
1061
1062 hr_nonrun_asact.insact(ln_yrend_action_id,
1063 ln_assignment_id,
1064 p_payroll_action_id,
1065 p_chunk,
1066 g_gre_tab(cntr_gre),
1067 NULL,
1068 'U',
1069 NULL);
1070
1071 hr_utility.set_location(gv_package ||
1072 lv_procedure_name, 150);
1073
1074 UPDATE pay_assignment_actions
1075 SET serial_number = ln_person_id
1076 WHERE assignment_action_id = ln_yrend_action_id;
1077
1078 hr_utility.trace('Archiver asg action ' ||
1079 ln_yrend_action_id || ' created.');
1080
1081 END IF;
1082
1083 ELSE
1084 hr_utility.trace('Assignment is excluded in asg set.');
1085 lv_excl_flag := '-1';
1086 END IF;
1087 ELSE
1088 hr_utility.trace ('Assignment skipped.');
1089 END IF;
1090 END LOOP;
1091
1092 IF lb_range_person THEN
1093 CLOSE c_get_emp_asg_range;
1094 ELSE
1095 CLOSE c_get_emp_asg;
1096 END IF;
1097 END LOOP;
1098
1099 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1104 gv_package || lv_procedure_name;
1105
1106 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1107
1108 lv_error_message :=
1109 pay_emp_action_arch.set_error_message(lv_error_message);
1110
1111 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1112 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1113 hr_utility.raise_error;
1114
1115 END assignment_action_code;
1116
1117 /************************************************************
1118 Name : initialization_code
1119 Purpose : This performs the context initialization.
1120 Arguments :
1121 Notes :
1122 ************************************************************/
1123
1124 PROCEDURE initialization_code(p_payroll_action_id IN NUMBER) IS
1125 --
1126 lv_procedure_name VARCHAR2(100);
1127 lv_error_message VARCHAR2(200);
1128 ln_step NUMBER;
1129
1130 ld_end_date DATE;
1131 ln_business_group_id NUMBER;
1132 ln_legal_employer_id NUMBER;
1133 ln_asg_set_id NUMBER;
1134
1135 CURSOR c_get_legal_er_info(cp_legal_er_id NUMBER,
1136 cp_effective_date DATE)
1137 IS
1138 SELECT hoi.org_information1 "Name",
1139 hoi.org_information2 "Employer RFC",
1140 ppf.full_name "Legal Representative Name",
1141 ppf.per_information2 "Legal Representative RFC",
1142 ppf.national_identifier "Legal Representative CURP"
1143 FROM hr_organization_information hoi,
1144 per_people_f ppf
1145 WHERE hoi.organization_id = cp_legal_er_id
1146 AND hoi.org_information_context = 'MX_TAX_REGISTRATION'
1147 AND hoi.org_information3 = ppf.person_id
1148 AND cp_effective_date BETWEEN ppf.effective_start_date
1149 AND ppf.effective_end_date;
1150
1151
1152 BEGIN
1153 lv_procedure_name := '.initialization_code';
1154
1155 ln_step := 1;
1156
1157 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1158
1159 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1160 ,p_end_date => ld_end_date
1161 ,p_business_group_id => ln_business_group_id
1162 ,p_legal_employer_id => ln_legal_employer_id
1163 ,p_asg_set_id => ln_asg_set_id);
1164
1165 g_payroll_action_id := p_payroll_action_id;
1166
1167 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1168
1169 ln_step := 2;
1170
1171 OPEN c_get_legal_er_info(ln_legal_employer_id,
1172 ld_end_date);
1173 FETCH c_get_legal_er_info INTO g_ER_legal_name,
1174 g_ER_RFC,
1175 g_ER_legal_rep_name,
1176 g_ER_legal_rep_RFC,
1177 g_ER_legal_rep_CURP;
1178 CLOSE c_get_legal_er_info;
1179
1180 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1181
1182 ln_step := 3;
1183
1184 SELECT TO_CHAR(ld_end_date, 'YYYY')
1185 INTO g_fiscal_year
1186 FROM dual;
1187
1188 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1193 gv_package || lv_procedure_name;
1194
1195 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1196
1197 lv_error_message :=
1198 pay_emp_action_arch.set_error_message(lv_error_message);
1199
1200 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1201 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1202 hr_utility.raise_error;
1203
1204 END initialization_code;
1205
1206 /************************************************************
1207 Name : archive_code
1208 Purpose : This procedure Archives data which is used in
1209 Year End Reporting for Mexico.
1210 Arguments : p_archive_action_id IN NUMBER
1211 p_effective_date IN DATE
1212 Notes :
1213 ************************************************************/
1214 PROCEDURE archive_code(p_archive_action_id IN NUMBER
1215 ,p_effective_date IN DATE)
1216 IS
1217 --
1218 lv_procedure_name VARCHAR2(100);
1219 lv_error_message VARCHAR2(200);
1220 ln_step NUMBER;
1221 ln_index NUMBER;
1222 ln_pay_action_count NUMBER;
1223
1224 lv_economic_zone VARCHAR2(1);
1225 ld_effective_date DATE;
1226
1227 ln_business_group_id NUMBER;
1228 ln_person_id NUMBER;
1229 ln_assignment_id NUMBER;
1230 ln_tax_unit_id NUMBER;
1231 ln_chunk_number NUMBER;
1232 lv_paternal_last_name per_people_f.last_name%TYPE;
1233 lv_maternal_last_name per_people_f.per_information1%TYPE;
1234 lv_names per_people_f.full_name%TYPE;
1235 lv_CURP per_people_f.national_identifier%TYPE;
1236 lv_RFC_ID per_people_f.per_information2%TYPE;
1237 ld_arch_start_date DATE;
1238 ld_arch_end_date DATE;
1239 ln_seniority NUMBER;
1240 ln_tax_subsidy_prop NUMBER;
1241 lv_jurisdiction VARCHAR2(10);
1242 lv_is_union_worker VARCHAR2(1);
1243 ld_hire_date DATE;
1244
1245 ln_legal_er_id NUMBER;
1246 ln_gre_id NUMBER;
1247 i NUMBER;
1248
1249 TYPE other_ER_rec IS RECORD
1250 (RFC VARCHAR2(30),
1251 ISR_Withheld NUMBER,
1252 Cred_Subsidy NUMBER,
1253 Non_Cred_Subsidy NUMBER,
1254 Total_Earnings NUMBER,
1255 Exempt_Earnings NUMBER);
1256
1257 TYPE other_ER_tbl IS TABLE OF other_ER_rec INDEX BY BINARY_INTEGER;
1258
1259 PEI other_ER_tbl;
1260
1261 ln_total_cred_subsidy NUMBER;
1262 ln_total_subsidy NUMBER;
1263 ln_ytd_aa_id NUMBER;
1264 ld_start_date DATE;
1265 ld_end_date DATE;
1266 ln_cred_subsidy NUMBER;
1267 ln_non_cred_subsidy NUMBER;
1268 ln_isr_calc NUMBER;
1269 ln_total_isr_calc NUMBER;
1270 ld_PTU_date DATE;
1271 ln_gross_earnings NUMBER;
1272 ln_profit_sharing NUMBER;
1273 l_valid_rfc VARCHAR2(30);
1274 lv_plain_rfc VARCHAR2(30);
1275 ln_legal_employer_id NUMBER;
1276 ln_asg_set_id NUMBER;
1277 lb_is_term_ee BOOLEAN;
1278 lv_prev_arch_exists VARCHAR2(1);
1279 lv_arch_for_ptu_only VARCHAR2(1);
1280
1281 ln_amends_aaid NUMBER;
1282 ld_amends_date_earned DATE;
1283 ln_amends_payroll_id NUMBER;
1284
1285 ln_LMOS NUMBER;
1286 ln_ISR_on_LMOS NUMBER;
1287
1288 lv_ann_adj VARCHAR2(1);
1289 ln_row_count NUMBER;
1290
1291 INVALID_RFC EXCEPTION;
1292
1293 -- Get employee details
1294 CURSOR c_emp_details(cp_effective_date DATE) IS
1295 select per_det.*, rownum row_count
1296 from (SELECT DISTINCT
1297 paf.business_group_id,
1298 ppf.person_id,
1299 paf.assignment_id,
1300 paa.tax_unit_id,
1301 paa.chunk_number,
1302 ppf.last_name "Paternal Last Name",
1303 ppf.per_information1 "Maternal Last Name",
1304 ppf.first_name || ' ' || ppf.middle_names,
1305 ppf.national_identifier "CURP",
1306 ppf.per_information2 "RFC ID",
1307 GREATEST(fnd_date.canonical_to_date(g_fiscal_year || '/01/01'),
1308 DECODE(TO_CHAR(pps.date_start, 'YYYY'),
1309 TO_CHAR(cp_effective_date, 'YYYY'),
1310 pps.date_start,
1311 fnd_date.canonical_to_date(g_fiscal_year ||
1312 '/01/01'))
1313 ),
1314
1315 hr_mx_utility.get_seniority(paf.business_group_id,
1316 paa.tax_unit_id,
1317 paf.payroll_id,
1318 ppf.person_id,
1319 cp_effective_date),
1320 NVL(paf_all.labour_union_member_flag, 'N'),
1321 hoi.org_information7 "Economic Zone",
1322 ROUND(0.5 + 0.005 * hr_mx_utility.get_tax_subsidy_percent(
1323 ppf.business_group_id,
1324 paa.tax_unit_id,
1325 cp_effective_date), 4),
1326 hl.region_1 "Jurisdiction"
1327 FROM per_people_f ppf,
1328 per_assignments_f paf,
1329 per_assignments_f paf_all,
1330 pay_assignment_actions paa,
1331 per_periods_of_service pps,
1332 hr_organization_units hou,
1333 hr_organization_information hoi,
1334 hr_locations_all hl,
1335 pay_payroll_actions ppa
1336 WHERE paa.assignment_action_id in
1337 (select assignment_action_id
1338 from pay_assignment_actions
1339 where assignment_id in
1340 (select assignment_id
1341 from pay_assignment_actions
1342 where assignment_action_id = p_archive_action_id)
1343 and payroll_action_id = ppa.payroll_action_id )
1344 and not exists
1345 ( select 1 from pay_action_information
1346 where action_context_id in
1347 (select assignment_action_id
1348 from pay_assignment_actions
1349 where assignment_id in
1350 (select assignment_id
1351 from pay_assignment_actions
1352 where assignment_action_id = p_archive_action_id)
1353 and payroll_action_id = ppa.payroll_action_id) )
1354 and ppa.payroll_action_id = paa.payroll_action_id
1355 and paa.tax_unit_id in
1356 (SELECT DISTINCT gre_node.entity_id
1357 FROM per_gen_hierarchy_nodes gre_node,
1358 per_gen_hierarchy_nodes le_node,
1359 per_gen_hierarchy_versions hier_ver,
1360 fnd_lookup_values flv
1361 WHERE gre_node.node_type = 'MX GRE'
1362 AND gre_node.business_group_id = paf.business_group_id
1363 AND gre_exists (gre_node.entity_id) = 1
1364 AND le_node.node_type = 'MX LEGAL EMPLOYER'
1365 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
1366 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
1367 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
1368 AND status = flv.lookup_code
1369 AND flv.meaning = 'Active'
1370 AND flv.LANGUAGE = 'US'
1371 AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
1372 AND cp_effective_date BETWEEN hier_ver.date_from
1373 AND NVL(hier_ver.date_to,
1374 hr_general.end_of_time))
1375 AND cp_effective_date BETWEEN ppf.effective_start_date
1376 AND ppf.effective_end_date
1377 AND cp_effective_date BETWEEN paf.effective_start_date
1378 AND paf.effective_end_date
1379 AND cp_effective_date BETWEEN paf_all.effective_start_date
1380 AND paf_all.effective_end_date
1381 AND paf.assignment_id = paa.assignment_id
1382 and paf_all.assignment_id = paf.assignment_id
1383 and paf.assignment_id = paf_all.assignment_id
1384 AND ppf.person_id = paf.person_id
1385 AND paf.person_id = paf_all.person_id
1386 and pps.person_id = ppf.person_id
1387 AND pps.period_of_service_id = paf.period_of_service_id
1388 AND hou.organization_id = paa.tax_unit_id
1389 AND hou.organization_id = hoi.organization_id
1390 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
1391 AND hl.location_id = paf.location_id) per_det;
1392
1393 -- Get Other ER info for ERs of the current year
1394 CURSOR c_get_other_er_info(cp_person_id NUMBER,
1395 cp_effective_date DATE) IS
1396 SELECT pei_information1 RFC,
1397 fnd_number.canonical_to_number(pei_information5) ISR_Withheld,
1398 fnd_number.canonical_to_number(pei_information6) Cr_Subsidy,
1399 fnd_number.canonical_to_number(pei_information7) Non_Cr_Subsidy,
1400 fnd_number.canonical_to_number(pei_information8) Total_Earnings,
1401 fnd_number.canonical_to_number(pei_information9) Exempt_Earnings
1402 FROM per_people_extra_info
1403 WHERE information_type = 'MX_PREV_EMPLOYMENT_INFO'
1404 AND person_id = cp_person_id
1405 AND TO_CHAR(fnd_date.canonical_to_date(pei_information4), 'YYYY') =
1406 TO_CHAR(cp_effective_date, 'YYYY')
1407 ORDER BY pei_information4 DESC;
1408
1409 -- Get end date of Format 37 for the person
1410 CURSOR c_get_eff_date IS
1411 SELECT DISTINCT
1412 NVL(pps.actual_termination_date,
1413 nvl(paf.effective_end_date, p_effective_date)),
1414 NVL(pps.actual_termination_date,
1415 fnd_date.canonical_to_date(g_fiscal_year || '/12/31')
1416 )
1417 FROM per_people_f ppf,
1418 per_assignments_f paf,
1419 pay_assignment_actions paa,
1420 pay_payroll_actions ppa,
1421 per_periods_of_service pps
1422 WHERE paa.assignment_action_id = p_archive_action_id
1423 AND ppa.payroll_action_id = paa.payroll_action_id
1424 AND paf.assignment_id = paa.assignment_id
1425 AND ppf.person_id = paf.person_id
1426 AND pps.period_of_service_id = paf.period_of_service_id;
1427
1428 CURSOR c_check_pay_action(cp_payroll_action_id IN NUMBER) IS
1429 SELECT count(*)
1430 FROM pay_action_information
1431 WHERE action_context_id = cp_payroll_action_id
1432 AND action_context_type = 'PA';
1433
1434 -- Get Generic Hierarchy Details for the current BG
1435 CURSOR c_get_gen_hier_details(cp_business_group_id NUMBER,
1436 cp_effective_date DATE
1437 ) IS
1438 SELECT DISTINCT le_node.entity_id,
1439 gre_node.entity_id
1440 FROM per_gen_hierarchy_nodes gre_node,
1441 per_gen_hierarchy_nodes le_node,
1442 per_gen_hierarchy_versions hier_ver,
1443 fnd_lookup_values flv
1444 WHERE gre_node.node_type = 'MX GRE'
1445 AND gre_node.business_group_id = cp_business_group_id
1446 AND gre_exists (gre_node.entity_id) = 1
1447 AND le_node.node_type = 'MX LEGAL EMPLOYER'
1448 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
1449 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
1450 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
1451 AND status = flv.lookup_code
1452 AND flv.meaning = 'Active'
1453 AND flv.LANGUAGE = 'US'
1454 AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
1455 AND cp_effective_date BETWEEN hier_ver.date_from
1456 AND NVL(hier_ver.date_to,
1457 hr_general.end_of_time);
1458
1459 -- Get latest ytd aaid
1460 -- Date constraint relaxed since terminated assignments are also included.
1461 CURSOR c_get_ytd_aaid(cp_arch_period_start_date DATE,
1462 cp_arch_period_end_date DATE,
1463 cp_tax_unit_id NUMBER) IS
1464 SELECT paa_all.assignment_action_id
1465 FROM pay_assignment_actions paa_all,
1466 pay_assignment_actions paa_pri,
1467 pay_payroll_actions ppa,
1468 per_assignments_f paf_pri,
1469 per_assignments_f paf_all,
1470 pay_action_classifications pac
1471 WHERE paa_pri.assignment_action_id = p_archive_action_id
1472 AND paf_pri.assignment_id = paa_pri.assignment_id
1473 AND paf_all.period_of_service_id = paf_pri.period_of_service_id
1474 AND paa_all.tax_unit_id = cp_tax_unit_id
1475 AND paa_all.assignment_id = paf_all.assignment_id
1476 AND paa_all.payroll_action_id = ppa.payroll_action_id
1477 AND ppa.action_type = pac.action_type
1478 AND pac.classification_name = 'SEQUENCED'
1479 AND paa_all.action_status = 'C'
1480 AND ppa.effective_date BETWEEN cp_arch_period_start_date
1481 AND cp_arch_period_end_date
1482 ORDER BY paa_all.action_sequence DESC;
1483
1484 -- Get the creditable and non-creditable subsidy for the person under
1485 -- the current employer
1486 /*CURSOR c_get_subsidy(cp_ytd_action_id NUMBER
1487 ) IS
1488 SELECT pay_balance_pkg.get_value(pdb_cr.defined_balance_id,
1489 cp_ytd_action_id),
1490 pay_balance_pkg.get_value(pdb_ncr.defined_balance_id,
1491 cp_ytd_action_id)
1492 FROM pay_defined_balances pdb_cr,
1493 pay_defined_balances pdb_ncr,
1494 pay_balance_types pbt_cr,
1495 pay_balance_types pbt_ncr,
1496 pay_balance_dimensions pbd
1497 WHERE pdb_cr.balance_type_id = pbt_cr.balance_type_id
1498 AND pdb_ncr.balance_type_id = pbt_ncr.balance_type_id
1499 AND pdb_cr.balance_dimension_id = pbd.balance_dimension_id
1500 AND pdb_ncr.balance_dimension_id = pbd.balance_dimension_id
1501 AND pbt_cr.balance_name = 'ISR Creditable Subsidy'
1502 AND pbt_ncr.balance_name = 'ISR Non Creditable Subsidy'
1503 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1504 AND pbt_cr.legislation_code = 'MX'
1505 AND pbt_ncr.legislation_code = pbt_cr.legislation_code
1506 AND pbd.legislation_code = pbt_ncr.legislation_code; */
1507
1508 -- Get the ISR Calculated, creditable and non-creditable subsidy
1509 -- for the person under the current employer
1510 CURSOR c_get_subsidy(cp_ytd_action_id NUMBER
1511 ) IS
1512 SELECT pay_balance_pkg.get_value(pdb_cr.defined_balance_id,
1513 cp_ytd_action_id)
1514 FROM pay_defined_balances pdb_cr,
1515 pay_balance_types pbt_cr,
1516 pay_balance_dimensions pbd
1517 WHERE pdb_cr.balance_type_id = pbt_cr.balance_type_id
1518 AND pdb_cr.balance_dimension_id = pbd.balance_dimension_id
1519 AND pbt_cr.balance_name = 'ISR Creditable Subsidy'
1520 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1521 AND pbt_cr.legislation_code = 'MX';
1522
1523 CURSOR c_get_nonsubsidy(cp_ytd_action_id NUMBER
1524 ) IS
1525 select pay_balance_pkg.get_value(pdb_ncr.defined_balance_id,
1526 cp_ytd_action_id)
1527 FROM pay_defined_balances pdb_ncr,
1528 pay_balance_types pbt_ncr,
1529 pay_balance_dimensions pbd
1530 WHERE pdb_ncr.balance_type_id = pbt_ncr.balance_type_id
1531 AND pdb_ncr.balance_dimension_id = pbd.balance_dimension_id
1532 AND pbt_ncr.balance_name = 'ISR Non Creditable Subsidy'
1533 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1534 AND pbt_ncr.legislation_code = 'MX'
1535 AND pbd.legislation_code = pbt_ncr.legislation_code;
1536
1537 CURSOR c_get_calc(cp_ytd_action_id NUMBER
1538 ) IS
1539 select pay_balance_pkg.get_value(pdb_calc.defined_balance_id,
1540 cp_ytd_action_id)
1541 FROM pay_defined_balances pdb_calc,
1542 pay_balance_types pbt_calc,
1543 pay_balance_dimensions pbd
1544 WHERE pdb_calc.balance_type_id = pbt_calc.balance_type_id
1545 AND pdb_calc.balance_dimension_id = pbd.balance_dimension_id
1546 AND pbt_calc.balance_name = 'ISR Calculated'
1547 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1548 AND pbt_calc.legislation_code = 'MX'
1549 AND pbd.legislation_code = pbt_calc.legislation_code;
1550
1551
1552 -- Get the month of payment of Profit Sharing
1553 CURSOR c_get_PTU_month(cp_person_id NUMBER,
1554 cp_tax_unit_id NUMBER,
1555 cp_start_date DATE,
1556 cp_end_date DATE
1557 ) IS
1558 SELECT DISTINCT ppa.effective_date
1559 FROM pay_run_results prr,
1560 pay_run_result_values prrv,
1561 pay_assignment_actions paa,
1562 pay_payroll_actions ppa,
1563 pay_input_values_f piv,
1564 pay_balance_feeds_f pbf,
1565 pay_balance_types pbt,
1566 per_assignments_f paf
1567 WHERE pbt.balance_name = 'Profit Sharing'
1568 AND pbt.legislation_code = 'MX'
1569 AND pbf.balance_type_id = pbt.balance_type_id
1570 AND piv.input_value_id = pbf.input_value_id
1571 AND prr.element_type_id = piv.element_type_id
1572 AND prrv.run_result_id = prr.run_result_id
1573 AND prr.assignment_action_id = paa.assignment_action_id
1574 AND paa.assignment_id = paf.assignment_id
1575 AND paf.person_id = cp_person_id
1576 AND paa.tax_unit_id = cp_tax_unit_id
1577 AND ppa.payroll_action_id = paa.payroll_action_id
1578 AND ppa.action_type IN ('R', 'Q', 'B', 'V', 'I')
1579 AND ppa.effective_date BETWEEN piv.effective_start_date
1580 AND piv.effective_end_date
1581 AND ppa.effective_date BETWEEN pbf.effective_start_date
1582 AND pbf.effective_end_date
1583 AND ppa.effective_date BETWEEN paf.effective_start_date
1584 AND paf.effective_end_date
1585 AND ppa.effective_date BETWEEN cp_start_date
1586 AND cp_end_date
1587 ORDER BY 1 DESC;
1588
1589 -- Check if any previous archiver exists for the same period of service
1590 --
1591 CURSOR c_chk_last_archiver(cp_assignment_id NUMBER,
1592 cp_start_date DATE,
1593 cp_end_date DATE,
1594 cp_tax_unit_id NUMBER) IS
1595 SELECT paa1.assignment_action_id, 'Y'
1596 FROM pay_payroll_actions ppa1,
1597 pay_assignment_actions paa1,
1598 per_assignments_f paf1,
1599 per_assignments_f paf2
1600 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
1601 AND paa1.assignment_id = paf1.assignment_id
1602 AND paf1.period_of_service_id = paf2.period_of_service_id
1603 AND paf2.assignment_id = cp_assignment_id
1604 AND ppa1.report_type = 'MX_YREND_ARCHIVE'
1605 AND ppa1.report_qualifier = 'MX'
1606 AND ppa1.report_category = 'ARCHIVE'
1607 AND paa1.assignment_action_id <> p_archive_action_id
1608 AND paf1.effective_start_date <= cp_end_date
1609 AND paf1.effective_end_date >= cp_start_date
1610 AND paf2.effective_start_date <= cp_end_date
1611 AND paf2.effective_end_date >= cp_start_date
1612 AND TO_CHAR(ppa1.effective_date, 'YYYY') = TO_CHAR(cp_end_date, 'YYYY')
1613 AND paa1.tax_unit_id = cp_tax_unit_id
1614 ORDER BY 1 desc;
1615
1616 CURSOR c_fetch_Ann_adj(cp_person_id NUMBER
1617 ,cd_end_date DATE ) is
1618 SELECT distinct 'Y'
1619 FROM per_all_assignments_f paf
1620 WHERE paf.person_id = cp_person_id
1621 AND EXISTS ( SELECT 1
1622 FROM pay_assignment_actions paa
1623 ,pay_payroll_actions ppa
1624 WHERE paa.payroll_action_id = ppa.payroll_action_id
1625 AND ppa.action_type = 'B'
1626 AND ppa.effective_date BETWEEN trunc(cd_end_date,'Y')
1627 and cd_end_date
1628 AND ppa.business_group_id = ln_business_group_id
1629 AND pay_mx_utility.get_legi_param_val('PROCESS',
1630 legislative_parameters) = 'MX_ANN_ADJ'
1631 AND paa.assignment_id = paf.assignment_id
1632 );
1633
1634 CURSOR c_ann_tax_type (cp_business_group_id NUMBER
1635 ,cp_effective_date DATE
1636 ,cp_person_id NUMBER) IS
1637 SELECT pay_mx_utility.get_legi_param_val('CALC_MODE'
1638 ,legislative_parameters)
1639 ,paa.assignment_action_id
1640 FROM per_all_assignments_f paf
1641 ,pay_assignment_actions paa
1642 ,pay_payroll_actions ppa
1643 WHERE person_id = cp_person_id
1644 AND paa.payroll_action_id = ppa.payroll_action_id
1645 AND ppa.action_type = 'B'
1646 AND ppa.effective_date = cp_effective_date
1647 AND ppa.business_group_id = cp_business_group_id
1648 AND pay_mx_utility.get_legi_param_val('PROCESS'
1649 ,legislative_parameters) = 'MX_ANN_ADJ'
1650 AND paa.assignment_id = paf.assignment_id
1651 ORDER BY ppa.payroll_action_id desc;
1652
1653 CURSOR c_pact_info ( cp_assignment_action_id NUMBER) IS
1654 select ppa.business_group_id
1655 ,ppa.effective_date
1656 from pay_payroll_actions ppa
1657 ,pay_assignment_actions paa
1658 where paa.assignment_action_id = cp_assignment_action_id
1659 and ppa.payroll_action_id = paa.payroll_action_id;
1660
1661 CURSOR c_input_value_id IS
1662 SELECT piv.input_value_id
1663 FROM pay_element_types_f pet
1664 ,pay_input_values_f piv
1665 WHERE pet.legislation_code = 'MX'
1666 AND pet.element_name = 'Annual Tax Adjustment'
1667 AND piv.element_type_id = pet.element_type_id
1668 AND piv.name = 'Calculation Mode';
1669
1670 CURSOR c_get_anntaxadj_article ( cp_assignment_action_id NUMBER
1671 ,cp_input_value_id NUMBER ) IS
1672 SELECT result_value
1673 FROM pay_run_results prr
1674 ,pay_run_result_values prrv
1675 WHERE prr.assignment_action_id = cp_assignment_action_id
1676 AND prrv.run_result_id = prr.run_result_id
1677 AND prrv.input_value_id = cp_input_value_id;
1678
1679 CURSOR c_get_hire_date ( cp_person_id NUMBER
1680 ,cp_effective_date DATE ) IS
1681 SELECT MAX (pps.date_start)
1682 FROM per_periods_of_service pps
1683 WHERE pps.person_id = cp_person_id
1684 AND pps.date_start <= cp_effective_date;
1685
1686 lv_ann_tax_calc_type VARCHAR2(240);
1687 ln_anntaxadj_asgactid NUMBER;
1688 ln_input_value_id NUMBER;
1689 lv_anntaxadj_article VARCHAR2(240);
1690
1691
1692 BEGIN
1693 lv_procedure_name := '.archive_code';
1694 --hr_utility.trace_on(null,'MX_NR');
1695 lv_prev_arch_exists := 'N';
1696 lv_arch_for_ptu_only := 'N';
1697 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1698
1699 ln_step := 1;
1700 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1701
1702 -- Load GRE cache
1703 IF g_gre_tab.count() = 0 THEN
1704
1705 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1706
1707 get_payroll_action_info(p_payroll_action_id => g_payroll_action_id
1708 ,p_end_date => ld_end_date
1709 ,p_business_group_id => ln_business_group_id
1710 ,p_legal_employer_id => ln_legal_employer_id
1711 ,p_asg_set_id => ln_asg_set_id);
1712
1713 hr_utility.trace('ld_end_date: ' || ld_end_date);
1714 hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1715 hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1716 hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1717
1718 load_gre (ln_business_group_id,
1719 ln_legal_employer_id,
1720 ld_end_date);
1721 END IF;
1722
1723
1724 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1725
1726 OPEN c_get_eff_date;
1727 FETCH c_get_eff_date INTO ld_effective_date, ld_arch_end_date;
1728 CLOSE c_get_eff_date;
1729
1730 hr_utility.trace('ld_effective_date: '||ld_effective_date);
1731 hr_utility.trace('ld_arch_end_date: '||ld_arch_end_date);
1732
1733 IF ld_arch_end_date <> fnd_date.canonical_to_date(g_fiscal_year ||
1734 '/12/31') THEN
1735
1736 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1737 lb_is_term_ee := TRUE;
1738 hr_utility.trace('lb_is_term_ee= TRUE');
1739 ELSE
1740 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1741 lb_is_term_ee := FALSE;
1742 hr_utility.trace('lb_is_term_ee= FALSE');
1743 END IF;
1744
1745 ln_step := 2;
1746 hr_utility.trace('Effective Date of archiver for the person: ' ||
1747 fnd_date.date_to_canonical(p_effective_date));
1748
1749
1750 ln_step := 3;
1751 ld_start_date := fnd_date.canonical_to_date(g_fiscal_year || '/01/01');
1752
1753 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1754
1755 -- EE details need to be fetched as on the Actual
1756 -- Termination Date, or p_effective_date, in that order.
1757 --
1758 OPEN c_emp_details(ld_effective_date);
1759 loop
1760 FETCH c_emp_details INTO ln_business_group_id,
1761 ln_person_id,
1762 ln_assignment_id,
1763 ln_tax_unit_id,
1764 ln_chunk_number,
1765 lv_paternal_last_name,
1766 lv_maternal_last_name,
1767 lv_names,
1768 lv_CURP,
1769 lv_RFC_ID,
1770 ld_arch_start_date,
1771 ln_seniority,
1772 lv_is_union_worker,
1773 lv_economic_zone,
1774 ln_tax_subsidy_prop,
1775 lv_jurisdiction,
1776 ln_row_count;
1777 exit when c_emp_details%NOTFOUND;
1778
1779 --CLOSE c_emp_details;
1780
1781 ln_step := 4;
1782 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1783 hr_utility.trace('ld_start_date: ' || ld_start_date);
1784 hr_utility.trace('p_effective_date: ' || p_effective_date);
1785 hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
1786 hr_utility.trace('ln_person_id: ' || ln_person_id);
1787 hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
1788 hr_utility.trace('ln_row_count: ' || ln_row_count);
1789
1790 -- Fetch the YTD Assignment Action ID.
1791 --
1792 ln_ytd_aa_id := NULL;
1793
1794 OPEN c_get_ytd_aaid(ld_start_date,
1795 p_effective_date,
1796 ln_tax_unit_id);
1797 FETCH c_get_ytd_aaid INTO ln_ytd_aa_id;
1798
1799 IF c_get_ytd_aaid%NOTFOUND THEN
1800
1801 hr_utility.trace('No assignment action found for the person''s ' ||
1802 'period of service within the GRE!');
1803 hr_utility.raise_error;
1804
1805 ELSE
1806
1807 hr_utility.trace('YTD Assactid: '|| ln_ytd_aa_id);
1808
1809 END IF;
1810
1811 CLOSE c_get_ytd_aaid;
1812
1813 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
1814
1815 ln_step := 5;
1816 hr_utility.set_location(gv_package || lv_procedure_name, 80);
1817
1818
1819 OPEN c_chk_last_archiver(ln_assignment_id,
1820 ld_start_date,
1821 p_effective_date,
1822 ln_tax_unit_id);
1823 FETCH c_chk_last_archiver INTO gn_prev_asg_act_id
1824 ,lv_prev_arch_exists;
1825 CLOSE c_chk_last_archiver;
1826
1827 hr_utility.trace('ln_person_id: '|| ln_person_id);
1828 hr_utility.trace('ln_assignment_id: '|| ln_assignment_id);
1829 hr_utility.trace('gn_prev_asg_act_id: '|| gn_prev_asg_act_id);
1830 hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
1831
1832 IF ( lv_prev_arch_exists = 'Y' OR lb_is_term_ee ) THEN
1833
1834 hr_utility.set_location(gv_package || lv_procedure_name, 90);
1835
1836 ld_PTU_date := NULL;
1837
1838 OPEN c_get_PTU_month(ln_person_id,
1839 ln_tax_unit_id,
1840 ld_start_date,
1841 p_effective_date);
1842 FETCH c_get_PTU_month INTO ld_PTU_date;
1843 CLOSE c_get_PTU_month;
1844
1845 IF ld_PTU_date IS NOT NULL THEN
1846
1847 ld_arch_start_date := TRUNC(ld_PTU_date, 'MM');
1848 ld_arch_end_date := ADD_MONTHS(ld_arch_start_date, 1) - 1;
1849
1850 hr_utility.trace('PTU ld_arch_start_date: '||ld_arch_start_date);
1851 hr_utility.trace('PTU ld_arch_end_date: '||ld_arch_end_date);
1852
1853 lv_arch_for_ptu_only := 'Y';
1854
1855 END IF;
1856
1857 END IF;
1858
1859 ln_step := 6;
1860 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1861
1862 /*OPEN c_get_subsidy(ln_ytd_aa_id);
1863 FETCH c_get_subsidy INTO ln_cred_subsidy,
1864 ln_non_cred_subsidy;
1865 CLOSE c_get_subsidy;
1866 */
1867 OPEN c_get_calc(ln_ytd_aa_id);
1868 FETCH c_get_calc INTO ln_isr_calc;
1869 CLOSE c_get_calc;
1870
1871 OPEN c_get_subsidy(ln_ytd_aa_id);
1872 FETCH c_get_subsidy INTO ln_cred_subsidy;
1873 CLOSE c_get_subsidy;
1874
1875 OPEN c_get_nonsubsidy(ln_ytd_aa_id);
1876 FETCH c_get_nonsubsidy INTO ln_non_cred_subsidy;
1877 CLOSE c_get_nonsubsidy;
1878
1879
1880 -- Initialise the variables
1881 --
1882 ln_total_subsidy := ln_cred_subsidy + ln_non_cred_subsidy;
1883 ln_total_cred_subsidy := ln_cred_subsidy;
1884 i := 0;
1885 --
1886 hr_utility.set_location(gv_package || lv_procedure_name, 110);
1887 FOR c_rec IN c_get_other_er_info(ln_person_id,
1888 p_effective_date)
1889 LOOP
1890 i := i + 1;
1891 PEI(i).isr_withheld := c_rec.isr_withheld;
1892 PEI(i).cred_subsidy := c_rec.cr_subsidy;
1893 PEI(i).non_cred_subsidy := c_rec.non_cr_subsidy;
1894 PEI(i).total_earnings := c_rec.total_earnings;
1895 PEI(i).exempt_earnings := c_rec.exempt_earnings;
1896
1897 ln_total_cred_subsidy := ln_total_cred_subsidy +
1898 NVL(PEI(i).cred_subsidy, 0);
1899 ln_total_subsidy := ln_total_subsidy + NVL(PEI(i).cred_subsidy, 0) +
1900 NVL(PEI(i).non_cred_subsidy, 0);
1901
1902 -- RFC Validation to be performed
1903 -- Hyphens are stripped and RFC is then validated.
1904 --
1905 hr_utility.set_location(gv_package || lv_procedure_name, 120);
1906 lv_plain_rfc :=
1907 TRANSLATE(c_rec.RFC, 'A !"$%^&*()-_+=`[]{};''#:@~<>?','A');
1908
1909 l_valid_rfc := hr_ni_chk_pkg.chk_nat_id_format(lv_plain_rfc,
1910 'AAADDDDDDXXX');
1911 IF l_valid_rfc = '0' THEN
1912 hr_utility.set_location(gv_package || lv_procedure_name, 130);
1913 RAISE INVALID_RFC;
1914 ELSE
1915 PEI(i).RFC := l_valid_rfc;
1916 END IF;
1917
1918 END LOOP;
1919
1920 lv_ann_adj := NULL;
1921
1922 OPEN c_fetch_Ann_adj(ln_person_id,p_effective_date);
1923 FETCH c_fetch_Ann_adj INTO lv_ann_adj;
1924 CLOSE c_fetch_Ann_adj;
1925
1926 ld_hire_date := fnd_date.canonical_to_date('1900/01/01');
1927
1928 OPEN c_get_hire_date(ln_person_id,p_effective_date);
1929 FETCH c_get_hire_date INTO ld_hire_date;
1930 CLOSE c_get_hire_date;
1931
1932 hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
1933 hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
1934 hr_utility.trace('ld_hire_date: '||ld_hire_date);
1935
1936 ld_arch_start_date := GREATEST( ld_arch_start_date, ld_hire_date );
1937
1938 IF TRUNC( ld_arch_end_date, 'Y' ) = TRUNC( p_effective_date, 'Y' ) THEN
1939
1940 ld_arch_end_date := LEAST( ld_arch_end_date, p_effective_date );
1941
1942 ELSE
1943
1944 ld_arch_end_date := p_effective_date;
1945
1946 END IF;
1947
1948 hr_utility.trace('AFTER ld_arch_start_date: '||ld_arch_start_date);
1949 hr_utility.trace('AFTER ld_arch_end_date: '||ld_arch_end_date);
1950
1951 ln_step := 7;
1952
1953 If ln_row_count = 1 then
1954 ln_index := pai_tab.count;
1955
1956 pai_tab(ln_index).action_info_category := 'MX YREND EE DETAILS';
1957 pai_tab(ln_index).jurisdiction_code := lv_jurisdiction;
1958 pai_tab(ln_index).action_context_id := p_archive_action_id;
1959 pai_tab(ln_index).act_info1 := lv_paternal_last_name;
1960 pai_tab(ln_index).act_info2 := lv_maternal_last_name;
1961 pai_tab(ln_index).act_info3 := lv_names;
1962 pai_tab(ln_index).act_info4 := lv_CURP;
1963 pai_tab(ln_index).act_info5 := lv_RFC_ID;
1964 pai_tab(ln_index).act_info6 :=
1965 fnd_date.date_to_canonical(ld_arch_start_date);
1966 pai_tab(ln_index).act_info7 :=
1967 fnd_date.date_to_canonical(ld_arch_end_date);
1968
1969 IF lb_is_term_ee AND lv_prev_arch_exists = 'N' AND
1970 lv_arch_for_ptu_only = 'N' THEN
1971
1972 pai_tab(ln_index).act_info8 := ln_seniority;
1973
1974 ELSE
1975
1976 pai_tab(ln_index).act_info8 := 0;
1977
1978 END IF;
1979
1980 -- sets the flag for the person where Annual Tax Adjusment process
1981 -- has been run act_info9
1982 pai_tab(ln_index).act_info9 := lv_ann_adj;
1983 pai_tab(ln_index).act_info10 := lv_is_union_worker;
1984 pai_tab(ln_index).act_info11 := lv_economic_zone;
1985
1986 hr_utility.set_location(gv_package || lv_procedure_name, 140);
1987 --
1988 IF ln_total_subsidy > 0 THEN
1989
1990 pai_tab(ln_index).act_info12 := ln_tax_subsidy_prop;
1991 ELSE
1992
1993 pai_tab(ln_index).act_info12 := NULL;
1994 END IF;
1995
1996 --
1997 -- Archived only if Annual Tax Adjustment is run for the EE
1998 -- and a different subsidy proportion has been used.
1999 --
2000 IF ln_total_subsidy > 0 AND lv_ann_adj = 'Y' THEN
2001
2002 hr_utility.set_location(gv_package || lv_procedure_name, 150);
2003 pai_tab(ln_index).act_info13 :=
2004 ROUND(ln_total_cred_subsidy / ln_total_subsidy, 4);
2005
2006 IF ln_tax_subsidy_prop <> pai_tab(ln_index).act_info13 THEN
2007
2008 hr_utility.set_location(gv_package || lv_procedure_name, 160);
2009 pai_tab(ln_index).act_info13 := NULL;
2010
2011 END IF;
2012
2013 ELSE
2014
2015 hr_utility.set_location(gv_package || lv_procedure_name, 170);
2016 pai_tab(ln_index).act_info13 := NULL;
2017
2018 END IF;
2019
2020 hr_utility.set_location(gv_package || lv_procedure_name, 180);
2021 --
2022 IF PEI.EXISTS(1) THEN
2023 pai_tab(ln_index).act_info14 := PEI(1).RFC;
2024 pai_tab(ln_index).act_info24 := PEI(1).total_earnings;
2025 pai_tab(ln_index).act_info25 := PEI(1).isr_withheld;
2026 pai_tab(ln_index).act_info27 := PEI(1).exempt_earnings;
2027
2028 ELSE -- No "Other Employer Info" exists.
2029
2030 pai_tab(ln_index).act_info14 := NULL;
2031 pai_tab(ln_index).act_info24 := NULL;
2032 pai_tab(ln_index).act_info25 := NULL;
2033 END IF;
2034
2035 IF PEI.EXISTS(2) THEN
2036 pai_tab(ln_index).act_info15 := PEI(2).RFC;
2037 ELSE
2038 pai_tab(ln_index).act_info15 := NULL;
2039 END IF;
2040
2041 IF PEI.EXISTS(3) THEN
2042 pai_tab(ln_index).act_info16 := PEI(3).RFC;
2043 ELSE
2044 pai_tab(ln_index).act_info16 := NULL;
2045 END IF;
2046
2047 IF PEI.EXISTS(4) THEN
2048 pai_tab(ln_index).act_info17 := PEI(4).RFC;
2049 ELSE
2050 pai_tab(ln_index).act_info17 := NULL;
2051 END IF;
2052
2053 IF PEI.EXISTS(5) THEN
2054 pai_tab(ln_index).act_info18 := PEI(5).RFC;
2055 ELSE
2056 pai_tab(ln_index).act_info18 := NULL;
2057 END IF;
2058
2059 IF PEI.EXISTS(6) THEN
2060 pai_tab(ln_index).act_info19 := PEI(6).RFC;
2061 ELSE
2062 pai_tab(ln_index).act_info19 := NULL;
2063 END IF;
2064
2065 IF PEI.EXISTS(7) THEN
2066 pai_tab(ln_index).act_info20 := PEI(7).RFC;
2067 ELSE
2068 pai_tab(ln_index).act_info20 := NULL;
2069 END IF;
2070
2071 IF PEI.EXISTS(8) THEN
2072 pai_tab(ln_index).act_info21 := PEI(8).RFC;
2073 ELSE
2074 pai_tab(ln_index).act_info21 := NULL;
2075 END IF;
2076
2077 IF PEI.EXISTS(9) THEN
2078 pai_tab(ln_index).act_info22 := PEI(9).RFC;
2079 ELSE
2080 pai_tab(ln_index).act_info22 := NULL;
2081 END IF;
2082
2083 IF PEI.EXISTS(10) THEN
2084 pai_tab(ln_index).act_info23 := PEI(10).RFC;
2085 ELSE
2086 pai_tab(ln_index).act_info23 := NULL;
2087 END IF;
2088 --
2089
2090 pai_tab(ln_index).act_info26 :=
2091 hr_general.decode_lookup('PAY_MX_STATE_IDS', lv_jurisdiction);
2092 --
2093 -- to get the values for RATE_1991_IND and RATE_FISCAL_YEAR_IND
2094 --
2095 pai_tab(ln_index).act_info28 := '0';
2096 pai_tab(ln_index).act_info29 := '1';
2097
2098 IF lv_ann_adj = 'Y' THEN
2099
2100 OPEN c_pact_info(p_archive_action_id);
2101 FETCH c_pact_info INTO ln_business_group_id
2102 ,ld_effective_date;
2103 CLOSE c_pact_info;
2104
2105 OPEN c_ann_tax_type( ln_business_group_id
2106 ,ld_effective_date
2107 ,ln_person_id);
2108 FETCH c_ann_tax_type INTO lv_ann_tax_calc_type
2109 ,ln_anntaxadj_asgactid;
2110 CLOSE c_ann_tax_type;
2111
2112 IF lv_ann_tax_calc_type = 'BEST' THEN
2113
2114 OPEN c_input_value_id;
2115 FETCH c_input_value_id INTO ln_input_value_id;
2116 CLOSE c_input_value_id;
2117
2118 OPEN c_get_anntaxadj_article( ln_anntaxadj_asgactid
2119 ,ln_input_value_id);
2120 FETCH c_get_anntaxadj_article INTO lv_anntaxadj_article;
2121 CLOSE c_get_anntaxadj_article;
2122
2123 ELSE
2124
2125 lv_anntaxadj_article := lv_ann_tax_calc_type;
2126
2127 END IF;
2128
2129 IF lv_anntaxadj_article = 'ARTICLE141' THEN
2130
2131 pai_tab(ln_index).act_info28 := '1';
2132 pai_tab(ln_index).act_info29 := '2';
2133
2134 ELSIF lv_anntaxadj_article = 'ARTICLE177' THEN
2135
2136 pai_tab(ln_index).act_info28 := '2';
2137 pai_tab(ln_index).act_info29 := '1';
2138
2139 END IF;
2140
2141 END IF;
2142
2143 ln_step := 8;
2144 hr_utility.set_location(gv_package || lv_procedure_name, 190);
2145 pay_emp_action_arch.insert_rows_thro_api_process(
2146 p_action_context_id => p_archive_action_id
2147 ,p_action_context_type => 'AAP'
2148 ,p_assignment_id => ln_assignment_id
2149 ,p_tax_unit_id => ln_tax_unit_id
2150 ,p_curr_pymt_eff_date => p_effective_date
2151 ,p_tab_rec_data => pai_tab
2152 );
2153 pai_tab.delete;
2154
2155 ln_step := 9;
2156 hr_utility.set_location(gv_package || lv_procedure_name, 200);
2157 OPEN c_check_pay_action(g_payroll_action_id);
2158 FETCH c_check_pay_action INTO ln_pay_action_count;
2159 CLOSE c_check_pay_action;
2160
2161 ln_step := 10;
2162 IF ln_pay_action_count = 0 THEN
2163
2164 hr_utility.set_location(gv_package || lv_procedure_name, 210);
2165 IF ln_row_count = 1 THEN
2166
2167 ln_step := 11;
2168 ln_index := pai_tab.count;
2169
2170 pai_tab(ln_index).action_info_category :='MX YREND LEGAL ER DETAILS';
2171 pai_tab(ln_index).jurisdiction_code := NULL;
2172 pai_tab(ln_index).action_context_id := g_payroll_action_id;
2173 pai_tab(ln_index).act_info1 := g_fiscal_year;
2174 pai_tab(ln_index).act_info2 := g_ER_RFC;
2175 pai_tab(ln_index).act_info3 := g_ER_legal_name;
2176 pai_tab(ln_index).act_info4 := g_ER_legal_rep_name;
2177 pai_tab(ln_index).act_info5 := g_ER_legal_rep_RFC;
2178 pai_tab(ln_index).act_info6 := g_ER_legal_rep_CURP;
2179
2180 hr_utility.set_location(gv_package || lv_procedure_name, 220);
2181
2182 OPEN c_get_gen_hier_details(ln_business_group_id,
2183 p_effective_date);
2184 LOOP
2185
2186 ln_step := 12;
2187 hr_utility.set_location(gv_package || lv_procedure_name, 230);
2188
2189 FETCH c_get_gen_hier_details INTO ln_legal_er_id,
2190 ln_gre_id;
2191 EXIT WHEN c_get_gen_hier_details%NOTFOUND;
2192
2193 ln_index := pai_tab.count;
2194
2195 hr_utility.set_location(gv_package || lv_procedure_name, 240);
2196
2197 pai_tab(ln_index).action_info_category := 'MX GENERIC ' ||
2198 'HIERARCHY DETAILS';
2199 pai_tab(ln_index).jurisdiction_code := NULL;
2200 pai_tab(ln_index).action_context_id := g_payroll_action_id;
2201 pai_tab(ln_index).act_info1 := ln_gre_id;
2202 pai_tab(ln_index).act_info2 := ln_legal_er_id;
2203
2204 END LOOP;
2205
2206 ln_step := 13;
2207 hr_utility.set_location(gv_package || lv_procedure_name, 250);
2208
2209 pay_emp_action_arch.insert_rows_thro_api_process(
2210 p_action_context_id => g_payroll_action_id
2211 ,p_action_context_type => 'PA'
2212 ,p_assignment_id => NULL
2213 ,p_tax_unit_id => NULL
2214 ,p_curr_pymt_eff_date => p_effective_date
2215 ,p_tab_rec_data => pai_tab);
2216
2217 hr_utility.set_location(gv_package || lv_procedure_name, 260);
2218 pai_tab.delete;
2219
2220 END IF;
2221
2222 END IF;
2223 end if;
2224
2225 ln_step := 14;
2226 hr_utility.set_location(gv_package || lv_procedure_name, 270);
2227
2228
2229 populate_balances(p_archive_action_id => p_archive_action_id
2230 ,p_ytd_action_id => ln_ytd_aa_id
2231 ,p_tax_unit_id => ln_tax_unit_id
2232 ,p_prev_archiver_exists => lv_prev_arch_exists);
2233
2234
2235 end loop;
2236 Close c_emp_details;
2237 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
2238
2239 EXCEPTION
2240 WHEN INVALID_RFC THEN
2241 hr_utility.set_message(800, 'HR_MX_INVALID_ER_RFC');
2242 hr_utility.raise_error;
2243
2244 WHEN OTHERS THEN
2245 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
2246 gv_package || lv_procedure_name;
2247
2248 hr_utility.trace(lv_error_message || '-' || SQLERRM);
2249
2250 lv_error_message :=
2251 pay_emp_action_arch.set_error_message(lv_error_message);
2252
2253 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2254 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2255 hr_utility.raise_error;
2256
2257 END archive_code;
2258
2259 BEGIN
2260 --hr_utility.trace_on (NULL, 'MX_IDC');
2261 gv_package := 'pay_mx_yrend_arch';
2262 END pay_mx_yrend_arch;