[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_YREND_ARCH
Source
1 PACKAGE BODY PAY_MX_YREND_ARCH AS
2 /* $Header: paymxyrendarch.pkb 120.49.12020000.8 2013/02/06 12:46:12 jeisaac 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 115.41 26-Feb-2009 sivanara 7529502 Changed techinal logic for performance
179 issue.
180 115.42 08-Apr-2009 sivanara 8402505 Modified cursor c_get_eff_date, to get
181 actual termination date also.
182 115.43 17-Apr-2009 sivanara 8402464 Added condition
183 ld_PUT_DATE > ld_arch_end_date.
184 for getting archive end date
185 and enabling only_PTU_flag.
186 115.44 20-Apr-2009 sivanara 8402464 Added code computation of seniority
187 for test case ,when emp
188 terminated and ran PTU before the
189 year end process.
190 115.45 04-Jan-2011 sjawid 9820914 Handling Special chars in Employee Name and
191 Organization Name using pay_mx_rules.strip_spl_chars
192 115.46 01-Feb-2011 sjawid 11677462 Modified cursor load_gre to avoid invalid number
193 error by adding to_char to legal_employer_id check
194 to_char(p_le_id)
195 115.47 10-Feb-2011 sjawid 11715919 Added cursor c_get_max_arch_end_date to fix the
196 Issue with Multiple Terminations of the employee.
197 115.48 14-Feb-2011 sjawid 11716646 Added Legal Employer Check to cursor
198 c_chk_last_archiver.
199 115.49 14-Feb-2011 vvijayku 11738478 Added condition to prevent archiving of Seniority
200 twice by the Archiver.
201 115.50 15-Feb-2011 vvijayku 11772866 Moved the cursor c_get_max_arch_end_date call out
202 of the IF clause and also moved the assigning of the
203 variable lb_latest_assignment out of the IF clause.
204 115.51 19-Oct-2011 vvijayku 13111454 Corrected the load_ye_balance procedure such that the
205 g_ptu_balance_value_tab pl/sql table is getting
206 initialized with the correct Defined Balance Ids.
207 115.52 28-Oct-2011 vvijayku 13093752 Added the cursor to fetch the
208 A_AMENDS_PER_PDS_GRE_YTD balance value and also
209 added condition to archive the seniority value only
210 if the balance A_AMENDS_PER_PDS_GRE_YTD is greater
211 than 0.
212 115.53 09-Feb-2012 vvijayku 13688040 Modified the c_emp_details cursor in the
213 archive_code procedure for the performance improvement
214 of the Year End Archiver process.
215 115.54 03-Apr-2012 jeisaac 13776183 Moved populate_balances call inside get_emp_details cursor
216 loop in archive_code
217 115.55 29-Aug-2012 jeisaac 14339322 Modified c_get_eff_date cursor in archive_code procedure to
218 fetch the latest record for an assignment.
219 115.56 02-Nov-2012 jeisaac 14800027 Moved the populate_balances procedure call to the beginning
220 of loop in archive_code procedure
221 115.57 24-Jan-2013 jeisaac 16076577 Modified c_emp_details cursor in archive_code procedure to exclude
222 assignment actions which do not fall within the archiver period.
223 115.58 25-Jan-2013 jeisaac 16218468 ISR Tax to Charge value will be archived as 0 if value is negative
224 115.59 06-Feb-2013 jeisaac 16270938 ISR Tax to Charge will take the value of ISR Withheld when Annual
225 Tax adjustment process is not run for an employee.
226 */
227 --
228 /******************************************************************************
229 ** Global Variables
230 ******************************************************************************/
231 gv_package VARCHAR2(100);
232 gn_prev_asg_act_id NUMBER;
233 /* Bug 7529502*/
234 TYPE rec_entity_details IS RECORD
235 ( user_entity_name ff_user_entities.user_entity_name%TYPE,
236 def_bal_id ff_user_entities.creator_id%TYPE,
237 bal_value NUMBER);
238 TYPE entity_details_tab IS TABLE OF rec_entity_details INDEX BY BINARY_INTEGER;
239 g_archive_item_details entity_details_tab;
240 g_ptu_bal_details entity_details_tab;
241 ---------------------------------------------------------------------------------------------------------------------------+
242 --For year end Balances define global variable to store defined_balance_id's and the corresponding balance values for BBR.
243 ---------------------------------------------------------------------------------------------------------------------------+
244 g_ye_balance_value_tab pay_balance_pkg.t_balance_value_tab;
245 ---------------------------------------------------------------------------------------------------------------------------+
246 --For year end Balances define global variable to store defined_balance_id's and the corresponding balance values for BBR.
247 ---------------------------------------------------------------------------------------------------------------------------+
248 g_ptu_balance_value_tab pay_balance_pkg.t_balance_value_tab;
249 --------------------------------------------------------------------------
250
251 -- --
252 -- Name : load_ye_balance --
253 -- Type : Procedure --
254 -- Access : Private --
255 -- Description : Procedure to load the year end balance value --
256 -- Parameters : --
257 -- OUT : N/A --
258 -- --
259 --------------------------------------------------------------------------
260 /*Added for bug 7529502*/
261 procedure load_ye_balance as
262 -- Get balances for archival
263 CURSOR c_get_balances IS
264 SELECT DISTINCT
265 fue_live.user_entity_name,
266 fue_live.creator_id,
267 0 tmp_bal_value
268 FROM pay_bal_attribute_definitions pbad,
269 pay_balance_attributes pba,
270 pay_defined_balances pdb_attr,
271 pay_defined_balances pdb_call,
272 pay_balance_dimensions pbd,
273 ff_user_entities fue_live
274 WHERE pbad.attribute_name = 'Year End Balances'
275 AND pbad.legislation_code = 'MX'
276 AND pba.attribute_id = pbad.attribute_id
277 AND pdb_attr.defined_balance_id = pba.defined_balance_id
278 AND pdb_attr.balance_type_id = pdb_call.balance_type_id
279 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
280 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
281 AND pbd.legislation_code = pbad.legislation_code
282 AND fue_live.creator_id = pdb_call.defined_balance_id
283 AND fue_live.creator_type = 'B'
284 ORDER BY fue_live.user_entity_name;
285
286 -- Get Profit Sharing balances for archival
287 CURSOR c_get_PTU_balances IS
288 SELECT DISTINCT
289 fue_live.user_entity_name,
290 fue_live.creator_id,
291 0 tmp_bal_value
292 FROM pay_defined_balances pdb_call,
293 pay_balance_dimensions pbd,
294 pay_balance_types pbt,
295 ff_user_entities fue_live
296 WHERE pbt.balance_name IN ('ISR Withheld',
297 'Year End ISR Subject for Profit Sharing',
298 'Year End ISR Exempt for Profit Sharing')
299 AND pbt.balance_type_id = pdb_call.balance_type_id
300 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
301 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
302 AND pbd.legislation_code = pbt.legislation_code
303 AND pbt.legislation_code = 'MX'
304 AND fue_live.creator_id = pdb_call.defined_balance_id
305 AND fue_live.creator_type = 'B'
306 ORDER BY fue_live.user_entity_name;
307
308 lv_procedure_name varchar2(30) := 'load_ye_balance';
309
310 begin
311 hr_utility.set_location(gv_package || lv_procedure_name, 10);
312 -- Clearing the global tables before initializing ..
313 g_archive_item_details.DELETE;
314 g_ptu_bal_details.DELETE;
315 g_ptu_balance_value_tab.DELETE;
316 g_ye_balance_value_tab.DELETE;
317
318 /*This code will be move to script and mapped to this file if needed*/
319 pay_mx_archive_dbi_pkg.create_archive_routes;
320 hr_utility.trace('Called from initialization_code and initialized for whole process');
321 hr_utility.trace('Getting the year end balances ');
322 OPEN c_get_balances ;
323 FETCH c_get_balances BULK COLLECT INTO g_archive_item_details;
324 CLOSE c_get_balances;
325 hr_utility.trace('Getting the PTU year end balances ');
326 OPEN c_get_PTU_balances;
327 FETCH c_get_PTU_balances BULK COLLECT INTO g_ptu_bal_details;
328 CLOSE c_get_PTU_balances;
329 /*This code will be move to script and mapped to this file if needed*/
330 FOR i IN g_archive_item_details.first..g_archive_item_details.last
331 LOOP
332 -- initialize for year end balance id for BBR
333 g_ye_balance_value_tab(i).defined_balance_id := g_archive_item_details(i).def_bal_id;
334 pay_mx_archive_dbi_pkg.create_archive_dbi('A_' || g_archive_item_details(i).user_entity_name);
335 END loop;
336
337 FOR j IN g_ptu_bal_details.first..g_ptu_bal_details.last
338 LOOP
339 -- initialize for ptu year end balance id for BBR
340 g_ptu_balance_value_tab(j).defined_balance_id := g_ptu_bal_details(j).def_bal_id;
341 pay_mx_archive_dbi_pkg.create_archive_dbi('A_' || g_ptu_bal_details(j).user_entity_name);
342 END loop;
343
344 end load_ye_balance;
345
346 --------------------------------------------------------------------------
347 -- --
348 -- Name : load_gre --
349 -- Type : Procedure --
350 -- Access : Private --
351 -- Description : Procedure to load all GREs for a given legal --
352 -- employer based on the Mexico Statutory Reporting --
353 -- Hierarchy as on the given date. --
354 -- Parameters : --
355 -- IN : p_business_group_id NUMBER --
356 -- p_le_id NUMBER --
357 -- p_effective_date DATE --
358 -- OUT : N/A --
359 -- --
360 --------------------------------------------------------------------------
361 PROCEDURE load_gre(p_business_group_id NUMBER,
362 p_le_id NUMBER,
363 p_effective_date DATE) IS
364 --
365 CURSOR csr_get_gres IS
366 SELECT gre_node.entity_id
367 FROM per_gen_hierarchy_nodes gre_node,
368 per_gen_hierarchy_nodes le_node,
369 per_gen_hierarchy_versions hier_ver,
370 fnd_lookup_values lv
371 WHERE gre_node.node_type = 'MX GRE'
372 AND le_node.node_type = 'MX LEGAL EMPLOYER'
373 AND le_node.entity_id = to_char(p_le_id) /* bug 11677462 */
374 AND le_node.business_group_id = p_business_group_id
375 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
376 AND gre_node.business_group_id = le_node.business_group_id
377 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
378 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
379 AND status = lv.lookup_code
380 AND lv.meaning = 'Active'
381 AND lv.LANGUAGE = 'US'
382 AND lv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
383 AND p_effective_date BETWEEN hier_ver.date_from
384 AND NVL(hier_ver.date_to, hr_general.end_of_time);
385
386 lv_procedure_name VARCHAR2(100);
387 ln_gre_id NUMBER;
388
389 BEGIN
390
391 lv_procedure_name := '.load_gre';
392
393 hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
394
395 hr_utility.trace ('parameters ...');
396 hr_utility.trace ('p_business_group_id = '||p_business_group_id);
397 hr_utility.trace ('p_le_id = '||p_le_id);
398 hr_utility.trace ('p_effective_date = '||p_effective_date);
399
400 g_gre_tab.delete();
401 OPEN csr_get_gres;
402 LOOP
403 FETCH csr_get_gres INTO ln_gre_id;
404 EXIT WHEN csr_get_gres%NOTFOUND;
405 g_gre_tab (g_gre_tab.count() + 1) := ln_gre_id;
406 END LOOP;
407 CLOSE csr_get_gres;
408
409 IF g_gre_tab.count() > 0 THEN
410 hr_utility.trace('List of GREs ...');
411 FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
412 hr_utility.trace(g_gre_tab(cntr_gre));
413 END LOOP;
414 ELSE
415 hr_utility.trace('No GREs found.');
416 END IF;
417
418 hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
419 END load_gre;
420
421 --------------------------------------------------------------------------
422 -- --
423 -- Name : gre_exists --
424 -- Type : Function --
425 -- Access : Public --
426 -- Description : Function to determine whether a GRE exists in the --
427 -- global variable g_gre_tab --
428 -- Parameters : --
429 -- IN : p_gre_id NUMBER --
430 -- OUT : N/A --
431 -- RETURN : NUMBER --
432 -- --
433 --------------------------------------------------------------------------
434 FUNCTION gre_exists (p_gre_id NUMBER)
435 RETURN NUMBER IS
436
437 lv_procedure_name VARCHAR2(100);
438
439 BEGIN
440 lv_procedure_name := '.gre_exists';
441 hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
442 hr_utility.trace('p_gre_id = ' || p_gre_id);
443
444 IF g_gre_tab.count() <> 0 THEN
445 FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
446 IF g_gre_tab (cntr_gre) = p_gre_id THEN
447 hr_utility.trace ('GRE exists');
448 hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
449 RETURN 1;
450 END IF;
451 END LOOP;
452 END IF;
453
454 hr_utility.trace ('GRE does not exist');
455 hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
456 RETURN 0;
457 END gre_exists;
458
459 /******************************************************************************
460 Name : get_payroll_action_info
461 Purpose : This returns the Payroll Action level
462 information for Year End Archiver.
463 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
464 p_end_date - End date of Archiver
465 p_business_group_id - Business Group ID
466 p_legal_employer_id - Legal Employer ID when submitting Archiver
467 p_asg_set_id - Assignment Set ID when submitting Archiver
468 ******************************************************************************/
469 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
470 ,p_end_date OUT NOCOPY DATE
471 ,p_business_group_id OUT NOCOPY NUMBER
472 ,p_legal_employer_id OUT NOCOPY NUMBER
473 ,p_asg_set_id OUT NOCOPY NUMBER
474 )
475 IS
476 CURSOR c_payroll_Action_info
477 (cp_payroll_action_id IN NUMBER) IS
478 SELECT effective_date,
479 business_group_id,
480 pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
481 legislative_parameters) Legal_Employer_ID,
482 pay_mx_utility.get_parameter('TRANSFER_ASSIGNMENT_SET_ID',
483 legislative_parameters) Assignment_SET_ID
484 FROM pay_payroll_actions
485 WHERE payroll_action_id = cp_payroll_action_id;
486
487 ld_end_date DATE;
488 ln_business_group_id NUMBER;
489 ln_asg_set_id NUMBER;
490 ln_legal_er_id NUMBER;
491 lv_procedure_name VARCHAR2(100);
492
493 lv_error_message VARCHAR2(200);
494 ln_step NUMBER;
495
496 BEGIN
497 lv_procedure_name := '.get_payroll_action_info';
498
499 hr_utility.set_location(gv_package || lv_procedure_name, 10);
500 ln_step := 1;
501 OPEN c_payroll_action_info(p_payroll_action_id);
502 FETCH c_payroll_action_info INTO ld_end_date,
503 ln_business_group_id,
504 ln_legal_er_id,
505 ln_asg_set_id;
506 CLOSE c_payroll_action_info;
507
508 hr_utility.set_location(gv_package || lv_procedure_name, 30);
509
510 p_end_date := ld_end_date;
511 p_business_group_id := ln_business_group_id;
512 p_legal_employer_id := ln_legal_er_id;
513 p_asg_set_id := ln_asg_set_id;
514
515 hr_utility.set_location(gv_package || lv_procedure_name, 50);
516 ln_step := 2;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
521 gv_package || lv_procedure_name;
522
523 hr_utility.trace(lv_error_message || '-' || SQLERRM);
524
525 lv_error_message :=
526 pay_emp_action_arch.set_error_message(lv_error_message);
527
528 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
529 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
530 hr_utility.raise_error;
531
532 END get_payroll_action_info;
533
534 /******************************************************************************
535 Name : create_archive_item
536 Purpose : This procedure creates the archive item for the assignments by calling api.
537 Arguments : p_user_entity_name - archiver_item name
538 p_balance_value - Archiver item bal value
539 p_prev_archiver_exists - Flag to create PTU archiver item
540 p_tax_unit_id - GRE id
541 p_archive_action_id - current Assignment action ID.
542 ******************************************************************************/
543 /*Added for bug 7529502*/
544
545 PROCEDURE create_archive_item(p_user_entity_name IN ff_user_entities.user_entity_name%TYPE
546 ,p_balance_value IN NUMBER
547 ,p_prev_archiver_exists IN VARCHAR2
548 ,p_tax_unit_id IN NUMBER
549 ,p_archive_action_id IN NUMBER)
550
551 IS
552 --
553 lv_procedure_name VARCHAR2(100);
554 lv_error_message VARCHAR2(200);
555 ln_arch_user_entity_id NUMBER;
556 ln_value NUMBER;
557 ln_ovn NUMBER;
558 l_some_warning BOOLEAN;
559 ln_archive_item_id NUMBER;
560 ln_prev_isr_whld_value NUMBER;
561 lv_arch_user_entity_name ff_user_entities.user_entity_name%TYPE;
562 lv_live_user_entity_name ff_user_entities.user_entity_name%TYPE;
563 ltab_entity_det entity_details_tab;
564 ln_count NUMBER;
565 ln_arc_item NUMBER;
566
567 -- Get archive DBI user entity ID
568 CURSOR c_get_arch_ue_id(cp_archive_item_name VARCHAR2)
569 IS
570 SELECT user_entity_id
571 FROM ff_user_entities
572 WHERE user_entity_name = cp_archive_item_name
573 AND creator_type = 'X'
574 AND creator_id = 0
575 AND legislation_code = 'MX';
576
577 BEGIN
578 -- Creating the archiver item code.
579 lv_procedure_name := '.populate_balances';
580 hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
581 hr_utility.set_location(gv_package || lv_procedure_name, 10);
582
583 ln_value := p_balance_value;
584 OPEN c_get_arch_ue_id('A_' || p_user_entity_name);
585 FETCH c_get_arch_ue_id INTO ln_arch_user_entity_id;
586 CLOSE c_get_arch_ue_id;
587 hr_utility.set_location(gv_package || lv_procedure_name, 20);
588 hr_utility.trace('Archive User Entity ID: '|| ln_arch_user_entity_id);
589 hr_utility.trace('Item Name ' || p_user_entity_name);
590 hr_utility.trace('Value: ' || ln_value);
591
592 IF p_prev_archiver_exists = 'Y' AND p_user_entity_name = 'ISR_WITHHELD_PER_PDS_GRE_YTD' THEN
593
594 /**************************************************************
595 ** ISR Withheld for PTU would be
596 ** ISR Withheld YTD - ISR Withheld of previous archived value
597 ** Whenevere there is previous archiver is run.
598 **************************************************************/
599 begin
600 hr_utility.set_location(gv_package || lv_procedure_name, 30);
601 SELECT fai.value
602 INTO ln_prev_isr_whld_value
603 FROM ff_archive_items fai
604 WHERE fai.context1 = gn_prev_asg_act_id
605 AND fai.user_entity_id = ln_arch_user_entity_id;
606 hr_utility.set_location(gv_package || lv_procedure_name, 40);
607 exception
608 when no_data_found then
609 ln_prev_isr_whld_value := 0;
610 end;
611
612
613 ln_value := ln_value - ln_prev_isr_whld_value;
614
615 END IF;
616 hr_utility.set_location(gv_package || lv_procedure_name, 50);
617 select count(1)
618 into ln_arc_item
619 from ff_archive_items fai,
620 ff_archive_item_contexts faic
621 where fai.archive_item_id = faic.archive_item_id
622 and fai.user_entity_id = ln_arch_user_entity_id
623 and fai.context1 = p_archive_action_id
624 and fai.value = ln_value
625 and faic.context = p_tax_unit_id;
626
627 if nvl(ln_arc_item,0) = 0 then
628
629 ff_archive_api.create_archive_item(
630 p_archive_item_id => ln_archive_item_id,
631 p_user_entity_id => ln_arch_user_entity_id,
632 p_archive_value => ln_value,
633 p_archive_type => 'AAP',
634 p_action_id => p_archive_action_id,
635 p_legislation_code => 'MX',
636 p_object_version_number => ln_ovn,
637 p_some_warning => l_some_warning,
638 p_context_name1 => 'TAX_UNIT_ID',
639 p_context1 => p_tax_unit_id);
640 end if;
641
642 hr_utility.set_location(gv_package || lv_procedure_name, 60);
643 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
644 END create_archive_item;
645
646
647 /************************************************************
648 Name : populate_balances
649 Purpose : This procedure archives Balances which are used
650 in Year End Reporting for Mexico.
651 Arguments :
652 Notes :
653 ************************************************************/
654 PROCEDURE populate_balances(p_archive_action_id IN NUMBER
655 ,p_ytd_action_id IN NUMBER
656 ,p_tax_unit_id IN NUMBER
657 ,p_prev_archiver_exists IN VARCHAR2
658 ,p_ann_adj_flag IN VARCHAR2)
659 IS
660 --
661 CURSOR c_get_arch_val(cp_archive_item_name VARCHAR2)
662 IS
663 SELECT ffa.archive_item_id, ffa.value
664 FROM ff_user_entities ffu, ff_archive_items ffa
665 WHERE ffu.user_entity_name = cp_archive_item_name
666 AND ffu.creator_type = 'X'
667 AND ffu.creator_id = 0
668 AND ffu.legislation_code = 'MX'
669 AND ffu.user_entity_id = ffa.user_entity_id
670 AND ffa.context1 = p_archive_action_id;
671
672 lv_procedure_name VARCHAR2(100);
673 lv_error_message VARCHAR2(200);
674 ln_step NUMBER;
675 ln_index NUMBER;
676
677 ltab_entity_det entity_details_tab;
678 ln_count NUMBER;
679 lt_ye_bal_context_tab pay_balance_pkg.t_context_tab;
680 lt_ye_bal_result_tab pay_balance_pkg.t_detailed_bal_out_tab;
681 lt_ptu_bal_result_tab pay_balance_pkg.t_detailed_bal_out_tab;
682
683 ln_isr_withheld_val VARCHAR2(240);
684 ln_tax_to_charge_val VARCHAR2(240);
685 ln_isr_withheld_aid NUMBER;
686 ln_tax_to_charge_aid NUMBER;
687
688 BEGIN
689 --
690 lv_procedure_name := '.populate_balances';
691 ln_count := 0;
692 ltab_entity_det.DELETE;
693 lt_ye_bal_result_tab.DELETE;
694 lt_ptu_bal_result_tab.DELETE;
695
696 lt_ye_bal_context_tab(1).tax_unit_id := p_tax_unit_id;
697
698 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
699 --
700 -- pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
701
702 -- Create the Archive DBI routes if they don't exist
703 --
704 hr_utility.set_location(gv_package || lv_procedure_name, 10);
705 /* pay_mx_archive_dbi_pkg.create_archive_routes;*/
706 hr_utility.trace('p_prev_archiver_exists ' || p_prev_archiver_exists);
707 hr_utility.trace('p_ytd_action_id ' || p_ytd_action_id);
708 /*Modified the code logic, by using BBR for bug 7529502*/
709 IF p_prev_archiver_exists = 'Y' THEN
710
711 pay_balance_pkg.get_value
712 (p_assignment_action_id => p_ytd_action_id
713 ,p_defined_balance_lst => g_ptu_balance_value_tab
714 ,p_context_lst => lt_ye_bal_context_tab
715 ,p_output_table => lt_ptu_bal_result_tab
716 );
717
718 FOR j IN g_ptu_bal_details.first..g_ptu_bal_details.last
719 LOOP
720 hr_utility.trace('About populate ptu balance');
721 hr_utility.trace('Def Bal Id....' ||g_ptu_bal_details(j).def_bal_id);
722 hr_utility.trace('lt_ptu_bal_result_tab Id....' ||lt_ptu_bal_result_tab(j).defined_balance_id);
723 hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name);
724 ln_count := ln_count +1;
725 g_ptu_bal_details(j).bal_value := nvl(lt_ptu_bal_result_tab(j).balance_value,0);
726 hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name||' bal value' || g_ptu_bal_details(j).bal_value);
727 ltab_entity_det(ln_count) := g_ptu_bal_details(j);
728 -- creating archive item for the assignment action
729 create_archive_item(p_user_entity_name => g_ptu_bal_details(j).user_entity_name
730 ,p_balance_value => g_ptu_bal_details(j).bal_value
731 ,p_prev_archiver_exists => p_prev_archiver_exists
732 ,p_tax_unit_id => p_tax_unit_id
733 ,p_archive_action_id => p_archive_action_id );
734
735 END LOOP;
736 ELSE
737 --
738 pay_balance_pkg.get_value
739 (p_assignment_action_id => p_ytd_action_id
740 ,p_defined_balance_lst => g_ye_balance_value_tab
741 ,p_context_lst => lt_ye_bal_context_tab
742 ,p_output_table => lt_ye_bal_result_tab
743 );
744
745 FOR i IN g_archive_item_details.first..g_archive_item_details.last
746 LOOP
747 hr_utility.trace('About populate other balance');
748 hr_utility.trace('Def Bal Id....' ||g_archive_item_details(i).def_bal_id);
749 hr_utility.trace('lt_ye_bal_result_tab Id....' ||lt_ye_bal_result_tab(i).defined_balance_id);
750 hr_utility.trace('Item Name ....' ||g_archive_item_details(i).user_entity_name);
751 ln_count := ln_count +1;
752 g_archive_item_details(i).bal_value := nvl(lt_ye_bal_result_tab(i).balance_value,0);
753 IF (g_archive_item_details(i).user_entity_name = 'ISR_TAX_TO_CHARGE_PER_PDS_GRE_YTD') THEN
754 ln_tax_to_charge_val := g_archive_item_details(i).bal_value;
755 END IF;
756 hr_utility.trace('Item Name ....' ||g_archive_item_details(i).user_entity_name||' bal value' || g_archive_item_details(i).bal_value);
757 ltab_entity_det(ln_count) := g_archive_item_details(i);
758
759 -- creating archive item for the assignment action
760 IF (g_archive_item_details(i).user_entity_name <> 'ISR_TAX_TO_CHARGE_PER_PDS_GRE_YTD') THEN
761 create_archive_item(p_user_entity_name => g_archive_item_details(i).user_entity_name
762 ,p_balance_value => g_archive_item_details(i).bal_value
763 ,p_prev_archiver_exists => p_prev_archiver_exists
764 ,p_tax_unit_id => p_tax_unit_id
765 ,p_archive_action_id => p_archive_action_id );
766 END IF;
767 END LOOP;
768
769 /*Bug 16270938 - When Annual Tax adj process is not run for employees, ISR Tax to Charge will take the value of ISR Withheld
770 - To be reverted in YE 2013*/
771 IF p_ann_adj_flag = 'N' THEN
772 OPEN c_get_arch_val('A_ISR_WITHHELD_PER_PDS_GRE_YTD');
773 FETCH c_get_arch_val INTO ln_isr_withheld_aid, ln_isr_withheld_val;
774 CLOSE c_get_arch_val;
775
776 ln_tax_to_charge_val := ln_isr_withheld_val;
777 END IF;
778
779 IF ln_tax_to_charge_val < 0 THEN
780 ln_tax_to_charge_val := 0;
781 END IF;
782
783 create_archive_item(p_user_entity_name => 'ISR_TAX_TO_CHARGE_PER_PDS_GRE_YTD'
784 ,p_balance_value => ln_tax_to_charge_val
785 ,p_prev_archiver_exists => p_prev_archiver_exists
786 ,p_tax_unit_id => p_tax_unit_id
787 ,p_archive_action_id => p_archive_action_id );
788
789 END IF;
790
791 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
792 --
793 END populate_balances;
794
795 /******************************************************************
796 Name : range_code
797 Purpose : This returns the select statement that is
798 used to create the range rows for the Year End
799 Archiver.
800 Arguments :
801 Notes : Calls procedure - get_payroll_action_info
802 ******************************************************************/
803 PROCEDURE range_code(
804 p_payroll_action_id IN NUMBER
805 ,p_sqlstr OUT NOCOPY VARCHAR2)
806 IS
807
808 ld_end_date DATE;
809 ld_start_date DATE;
810 ln_business_group_id NUMBER;
811 ln_asg_set_id NUMBER;
812 ln_legal_employer_id NUMBER;
813
814 lv_sql_string VARCHAR2(32000);
815 lv_procedure_name VARCHAR2(100);
816
817 BEGIN
818 lv_procedure_name := '.range_code';
819
820 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
821 hr_utility.set_location(gv_package || lv_procedure_name, 10);
822 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
823 ,p_end_date => ld_end_date
824 ,p_business_group_id => ln_business_group_id
825 ,p_legal_employer_id => ln_legal_employer_id
826 ,p_asg_set_id => ln_asg_set_id);
827 hr_utility.set_location(gv_package || lv_procedure_name, 20);
828
829 load_gre (ln_business_group_id,
830 ln_legal_employer_id,
831 ld_end_date);
832
833 ld_start_date := TRUNC(ld_end_date, 'Y');
834
835 IF ln_asg_set_id IS NULL THEN
836
837 lv_sql_string :=
838 'SELECT DISTINCT paf.person_id
839 FROM pay_assignment_actions paa,
840 pay_payroll_actions ppa,
841 per_assignments_f paf
842 WHERE ppa.business_group_id = ' || ln_business_group_id || '
843 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
844 fnd_date.date_to_canonical(ld_start_date) || ''')
845 AND fnd_date.canonical_to_date(''' ||
846 fnd_date.date_to_canonical(ld_end_date) || ''')
847 AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
848 AND paa.action_status = ''C''
849 AND ppa.payroll_action_id = paa.payroll_action_id
850 AND paa.source_action_id IS NULL
851 AND paf.assignment_id = paa.assignment_id
852 AND ppa.effective_date BETWEEN paf.effective_start_date
853 AND paf.effective_end_date
854 AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
855 AND :payroll_action_id > 0
856 ORDER BY paf.person_id';
857 ELSE
858
859 lv_sql_string :=
860 'SELECT DISTINCT paf.person_id
861 FROM pay_assignment_actions paa,
862 pay_payroll_actions ppa,
863 per_assignments_f paf
864 WHERE ppa.business_group_id = ' || ln_business_group_id || '
865 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
866 fnd_date.date_to_canonical(ld_start_date) || ''')
867 AND fnd_date.canonical_to_date(''' ||
868 fnd_date.date_to_canonical(ld_end_date) || ''')
869 AND ppa.action_type IN (''Q'',''R'',''B'',''V'',''I'')
870 AND paa.action_status = ''C''
871 AND ppa.payroll_action_id = paa.payroll_action_id
872 AND paa.source_action_id IS NULL
873 AND paf.assignment_id = paa.assignment_id
874 AND ppa.effective_date BETWEEN paf.effective_start_date
875 AND paf.effective_end_date
876 AND pay_mx_yrend_arch.gre_exists (paa.tax_unit_id) = 1
877 AND EXISTS
878 (SELECT ''x''
879 FROM hr_assignment_sets has,
880 hr_assignment_set_amendments hasa,
881 per_assignments_f paf_all
882 WHERE has.assignment_set_id = ' || ln_asg_set_id || '
883 AND has.assignment_set_id = hasa.assignment_set_id
884 AND hasa.assignment_id = paf_all.assignment_id
885 AND paf_all.person_id = paf.person_id
886 AND hasa.include_or_exclude = ''I'')
887 AND :payroll_action_id > 0
888 ORDER BY paf.person_id';
889
890 END IF; -- ln_asg_set_id is null
891
892 hr_utility.set_location(gv_package || lv_procedure_name, 30);
893 p_sqlstr := lv_sql_string;
894 hr_utility.trace ('SQL string :' ||p_sqlstr);
895 hr_utility.set_location(gv_package || lv_procedure_name, 50);
896 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
897
898 END range_code;
899
900 /************************************************************
901 Name : assignment_action_code
902 Purpose : This creates the assignment actions for
903 a specific chunk of people to be archived
904 by the Year End Archiver process.
905 Arguments :
906 Notes : Calls procedure - get_payroll_action_info
907 ************************************************************/
908 PROCEDURE assignment_action_code(
909 p_payroll_action_id IN NUMBER
910 ,p_start_person_id IN NUMBER
911 ,p_end_person_id IN NUMBER
912 ,p_chunk IN NUMBER)
913 IS
914
915
916 CURSOR c_chk_asg (cp_asg_set_id NUMBER,
917 cp_asg_id NUMBER) IS
918 SELECT 'X'
919 FROM hr_assignment_sets has,
920 hr_assignment_set_amendments hasa
921 WHERE has.assignment_set_id = cp_asg_set_id
922 AND has.assignment_set_id = hasa.assignment_set_id
923 AND hasa.assignment_id IN (SELECT DISTINCT
924 paf_all.assignment_id
925 FROM per_assignments_f paf,
926 per_assignments_f paf_all
927 WHERE paf.person_id = paf_all.person_id
928 AND paf.assignment_id = cp_asg_id)
929 AND hasa.include_or_exclude = 'E';
930
931 CURSOR c_get_emp_asg_range (cp_gre_id NUMBER,
932 cp_start_date DATE,
933 cp_end_date DATE) IS
934 SELECT /*+ index(PPA PAY_PAYROLL_ACTIONS_PK) */ paf_pri.assignment_id,
935 paf_pri.person_id,
936 paf_pri.period_of_service_id
937 FROM per_assignments_f paf,
938 per_assignments_f paf_pri,
939 pay_assignment_actions paa,
940 pay_payroll_actions ppa,
941 pay_population_ranges ppr
942 WHERE paf.assignment_id = paa.assignment_id
943 AND paa.tax_unit_id = cp_gre_id
944 AND ppr.payroll_action_id = p_payroll_action_id
945 AND ppr.chunk_number = p_chunk
946 AND ppr.person_id = paf.person_id
947 AND paf_pri.period_of_service_id = paf.period_of_service_id
948 AND paf_pri.primary_flag = 'Y'
949 AND paa.payroll_action_id = ppa.payroll_action_id
950 AND ppa.action_type IN ('Q','R','B','V','I')
951 AND paa.action_status = 'C'
952 AND ppa.effective_date BETWEEN cp_start_date
953 AND cp_end_date
954 AND paf_pri.effective_start_date <= cp_end_date
955 AND paf_pri.effective_end_date >= cp_start_date
956 AND ppa.effective_date BETWEEN paf.effective_start_date
957 AND paf.effective_end_date
958 ORDER BY paf_pri.person_id,
959 paf_pri.effective_end_date DESC;
960
961 CURSOR c_get_emp_asg (cp_gre_id NUMBER,
962 cp_bg_id NUMBER,
963 cp_start_date DATE,
964 cp_end_date DATE) IS
965 SELECT /*+ USE_NL(pap paf) */
966 paf_pri.assignment_id,
967 paf_pri.person_id,
968 paf_pri.period_of_service_id
969 FROM per_assignments_f paf,
970 per_assignments_f paf_pri,
971 pay_assignment_actions paa,
972 pay_payroll_actions ppa,
973 pay_all_payrolls_f pap
974 WHERE ppa.business_group_id + 0 = cp_bg_id
975 AND ppa.effective_date BETWEEN cp_start_date
976 AND cp_end_date
977 AND ppa.action_type IN ('Q','R','B','V','I')
978 AND paa.action_status = 'C'
979 AND pap.business_group_id = cp_bg_id
980 AND ppa.payroll_id = pap.payroll_id
981 AND ppa.payroll_action_id = paa.payroll_action_id
982 AND paa.source_action_id IS NULL
983 AND paf.assignment_id = paa.assignment_id
984 AND paf_pri.period_of_service_id = paf.period_of_service_id
985 AND paf_pri.primary_flag = 'Y'
986 AND ppa.effective_date BETWEEN paf.effective_start_date
987 AND paf.effective_end_date
988 AND paf_pri.effective_start_date <= cp_end_date
989 AND paf_pri.effective_end_date >= cp_start_date
990 AND paa.tax_unit_id = cp_gre_id
991 AND paf_pri.person_id = paf.person_id
992 AND paf.person_id BETWEEN p_start_person_id
993 AND p_end_person_id
994 ORDER BY paf_pri.person_id,
995 paf_pri.effective_end_date DESC;
996
997 -- Check if any previous archiver exists for the same period of service
998 --
999 CURSOR c_chk_last_archiver(cp_period_of_service_id NUMBER,
1000 cp_start_date DATE,
1001 cp_end_date DATE,
1002 cp_legal_employer_id NUMBER) IS
1003 SELECT ppa1.effective_date,
1004 paa1.assignment_action_id
1005 FROM pay_payroll_actions ppa1,
1006 pay_assignment_actions paa1,
1007 per_assignments_f paf1
1008 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
1009 AND paa1.assignment_id = paf1.assignment_id
1010 AND paf1.period_of_service_id = cp_period_of_service_id
1011 AND ppa1.report_type = 'MX_YREND_ARCHIVE'
1012 AND ppa1.report_qualifier = 'MX'
1013 AND ppa1.report_category = 'ARCHIVE'
1014 AND paf1.effective_start_date <= cp_end_date
1015 AND paf1.effective_end_date >= cp_start_date
1016 AND TO_CHAR(ppa1.effective_date, 'YYYY')
1017 = TO_CHAR(cp_end_date, 'YYYY')
1018 AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
1019 ppa1.legislative_parameters) = cp_legal_employer_id
1020 ORDER BY ppa1.effective_date DESC;
1021
1022 -- Check if runs exist after the last archiver run
1023 --
1024 CURSOR c_chk_non_arch_runs(cp_period_of_service_id NUMBER,
1025 cp_prev_arch_eff_date DATE,
1026 cp_start_date DATE,
1027 cp_end_date DATE) IS
1028 SELECT 'Y'
1029 FROM pay_payroll_actions ppa2,
1030 pay_assignment_actions paa2,
1031 per_assignments_f paf2
1032 WHERE ppa2.payroll_action_id = paa2.payroll_action_id
1033 AND ppa2.action_type IN ('R', 'Q', 'B', 'V', 'I')
1034 AND paa2.assignment_id = paf2.assignment_id
1035 AND paf2.period_of_service_id = cp_period_of_service_id
1036 AND ppa2.effective_date > cp_prev_arch_eff_date
1037 AND ppa2.effective_date <= cp_end_date
1038 AND paf2.effective_start_date <= cp_end_date
1039 AND paf2.effective_end_date >= cp_start_date;
1040
1041 ln_assignment_id NUMBER;
1042 ln_tax_unit_id NUMBER;
1043
1044 ld_end_date DATE;
1045 ld_start_date DATE;
1046 ln_business_group_id NUMBER;
1047 ln_legal_employer_id NUMBER;
1048 ln_asg_set_id NUMBER;
1049
1050 ln_yrend_action_id NUMBER;
1051
1052 lv_procedure_name VARCHAR2(100);
1053 lv_error_message VARCHAR2(200);
1054 ln_step NUMBER;
1055
1056 lb_range_person BOOLEAN;
1057 ln_person_id NUMBER;
1058 ln_prev_pos_id NUMBER;
1059 ln_pos_id NUMBER;
1060 lv_excl_flag VARCHAR2(2);
1061 lv_run_exists VARCHAR2(2);
1062
1063 ln_prev_arch_aaid NUMBER;
1064 ld_prev_arch_eff_date DATE;
1065
1066 BEGIN
1067 lv_procedure_name := '.assignment_action_code';
1068 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1069
1070 ln_pos_id := -1;
1071 ln_prev_pos_id := -1;
1072 lv_excl_flag := '-1';
1073 lv_run_exists := 'N';
1074
1075 hr_utility.trace('p_payroll_action_id = '|| p_payroll_action_id);
1076 hr_utility.trace('p_start_person_id = '|| p_start_person_id);
1077 hr_utility.trace('p_end_person_id = '|| p_end_person_id);
1078 hr_utility.trace('p_chunk = '|| p_chunk);
1079
1080 ln_step := 1;
1081 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1082
1083 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1084 ,p_end_date => ld_end_date
1085 ,p_business_group_id => ln_business_group_id
1086 ,p_legal_employer_id => ln_legal_employer_id
1087 ,p_asg_set_id => ln_asg_set_id);
1088
1089 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1090 hr_utility.trace('ld_end_date: ' || ld_end_date);
1091 hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1092 hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1093 hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1094
1095 ld_start_date := TRUNC(ld_end_date, 'Y');
1096
1097 hr_utility.trace('ld_start_date: ' || ld_start_date);
1098
1099 IF g_gre_tab.count() = 0 THEN
1100
1101 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1102
1103 load_gre (ln_business_group_id,
1104 ln_legal_employer_id,
1105 ld_end_date);
1106 END IF;
1107
1108 lb_range_person := pay_ac_utility.range_person_on(
1109 p_report_type => 'MX_YREND_ARCHIVE'
1110 ,p_report_format => 'MX_YREND_ARCHIVE'
1111 ,p_report_qualifier => 'MX'
1112 ,p_report_category => 'ARCHIVE');
1113
1114 FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
1115 IF lb_range_person THEN
1116 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1117
1118 OPEN c_get_emp_asg_range(g_gre_tab(cntr_gre),
1119 ld_start_date,
1120 ld_end_date);
1121 ELSE
1122 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1123
1124 OPEN c_get_emp_asg (g_gre_tab(cntr_gre),
1125 ln_business_group_id,
1126 ld_start_date,
1127 ld_end_date);
1128 END IF;
1129
1130 LOOP
1131 IF lb_range_person THEN
1132
1133 hr_utility.trace('lb_range_person');
1134
1135 FETCH c_get_emp_asg_range INTO ln_assignment_id,
1136 ln_person_id,
1137 ln_pos_id;
1138 EXIT WHEN c_get_emp_asg_range%NOTFOUND;
1139 ELSE
1140 FETCH c_get_emp_asg INTO ln_assignment_id,
1141 ln_person_id,
1142 ln_pos_id;
1143 EXIT WHEN c_get_emp_asg%NOTFOUND;
1144 END IF;
1145
1146 hr_utility.trace('Previous period of service = ' || ln_prev_pos_id);
1147 hr_utility.trace('Current period of service = ' || ln_pos_id);
1148 hr_utility.trace('Person ID= ' || ln_person_id);
1149 hr_utility.trace('Assignment ID= ' || ln_assignment_id);
1150
1151 IF ln_pos_id <> ln_prev_pos_id THEN
1152
1153 ln_prev_pos_id := ln_pos_id;
1154
1155 IF ln_asg_set_id IS NOT NULL THEN
1156
1157 hr_utility.set_location(gv_package || lv_procedure_name,60);
1158 hr_utility.trace('Assignment SET ID FOUND');
1159
1160 lv_excl_flag := '-1';
1161 OPEN c_chk_asg (ln_asg_set_id, ln_assignment_id);
1162 FETCH c_chk_asg INTO lv_excl_flag;
1163 CLOSE c_chk_asg;
1164
1165
1166 END IF;
1167
1168 hr_utility.trace('lv_excl_flag: '||lv_excl_flag);
1169
1170 IF lv_excl_flag <> 'X' THEN
1171
1172 ld_prev_arch_eff_date := NULL;
1173 ln_prev_arch_aaid := NULL;
1174
1175 hr_utility.set_location(gv_package || lv_procedure_name,70);
1176 /*bug:11715919 */
1177 OPEN c_chk_last_archiver(ln_pos_id,
1178 ld_start_date,
1179 ld_end_date,
1180 ln_legal_employer_id);
1181 FETCH c_chk_last_archiver INTO ld_prev_arch_eff_date,
1182 ln_prev_arch_aaid;
1183 CLOSE c_chk_last_archiver;
1184
1185 IF ld_prev_arch_eff_date IS NOT NULL THEN
1186
1187 -- A previous Year End Archiver run exists for the person's
1188 -- period of service.
1189 --
1190
1191 hr_utility.trace('Prev Arch Effective Date = ' ||
1192 fnd_date.date_to_canonical(ld_prev_arch_eff_date));
1193 hr_utility.trace('Prev Arch Asg Action ID = ' ||
1194 ln_prev_arch_aaid);
1195 hr_utility.set_location(gv_package ||
1196 lv_procedure_name, 80);
1197
1198 lv_run_exists := 'N';
1199
1200 -- Check if payroll is run after year end
1201 -- archiver
1202
1203 OPEN c_chk_non_arch_runs (ln_pos_id,
1204 ld_prev_arch_eff_date,
1205 ld_start_date,
1206 ld_end_date);
1207 FETCH c_chk_non_arch_runs INTO lv_run_exists;
1208 CLOSE c_chk_non_arch_runs;
1209
1210 hr_utility.trace('lv_run_exists: '||lv_run_exists);
1211
1212 IF lv_run_exists = 'Y' THEN
1213
1214 -- The person has had a SEQUENCED action since the last
1215 -- archiver run and should therefore be archived. The
1216 -- last archiver action will be locked by this new
1217 -- action.
1218
1219 hr_utility.set_location(gv_package ||
1220 lv_procedure_name, 90);
1221
1222 SELECT pay_assignment_actions_s.NEXTVAL
1223 INTO ln_yrend_action_id
1224 FROM dual;
1225
1226 hr_nonrun_asact.insact(ln_yrend_action_id,
1227 ln_assignment_id,
1228 p_payroll_action_id,
1229 p_chunk,
1230 g_gre_tab(cntr_gre),
1231 NULL,
1232 'U',
1233 NULL);
1234
1235 hr_utility.set_location(gv_package ||
1236 lv_procedure_name, 100);
1237
1238 UPDATE pay_assignment_actions
1239 SET serial_number = ln_person_id
1240 WHERE assignment_action_id = ln_yrend_action_id;
1241
1242 hr_utility.trace('Archiver asg action ' ||
1243 ln_yrend_action_id || ' created.');
1244
1245 -- insert an interlock to this action
1246 hr_utility.trace('Locking Action = ' ||
1247 ln_yrend_action_id);
1248 hr_utility.trace('Locked Action = ' ||
1249 ln_prev_arch_aaid);
1250 hr_nonrun_asact.insint(ln_yrend_action_id,
1251 ln_prev_arch_aaid);
1252
1253 hr_utility.set_location(gv_package ||
1254 lv_procedure_name, 110);
1255
1256 ELSE
1257
1258 hr_utility.set_location(gv_package ||
1259 lv_procedure_name, 120);
1260
1261 hr_utility.trace('The person has not been paid ' ||
1262 'since the last archiver and is therefore skipped.');
1263
1264 END IF;
1265
1266 ELSE
1267
1268 -- No previous archiver run exists for this person.
1269 -- New assignment action would still be created, but no
1270 -- interlocks inserted.
1271
1272 hr_utility.set_location(gv_package ||
1273 lv_procedure_name, 130);
1274
1275 SELECT pay_assignment_actions_s.NEXTVAL
1276 INTO ln_yrend_action_id
1277 FROM dual;
1278
1279 hr_utility.set_location(gv_package ||
1280 lv_procedure_name, 140);
1281
1282 hr_nonrun_asact.insact(ln_yrend_action_id,
1283 ln_assignment_id,
1284 p_payroll_action_id,
1285 p_chunk,
1286 g_gre_tab(cntr_gre),
1287 NULL,
1288 'U',
1289 NULL);
1290
1291 hr_utility.set_location(gv_package ||
1292 lv_procedure_name, 150);
1293
1294 UPDATE pay_assignment_actions
1295 SET serial_number = ln_person_id
1296 WHERE assignment_action_id = ln_yrend_action_id;
1297
1298 hr_utility.trace('Archiver asg action ' ||
1299 ln_yrend_action_id || ' created.');
1300
1301 END IF;
1302
1303 ELSE
1304 hr_utility.trace('Assignment is excluded in asg set.');
1305 lv_excl_flag := '-1';
1306 END IF;
1307 ELSE
1308 hr_utility.trace ('Assignment skipped.');
1309 END IF;
1310 END LOOP;
1311
1312 IF lb_range_person THEN
1313 CLOSE c_get_emp_asg_range;
1314 ELSE
1315 CLOSE c_get_emp_asg;
1316 END IF;
1317 END LOOP;
1318
1319 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1324 gv_package || lv_procedure_name;
1325
1326 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1327
1328 lv_error_message :=
1329 pay_emp_action_arch.set_error_message(lv_error_message);
1330
1331 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1332 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1333 hr_utility.raise_error;
1334
1335 END assignment_action_code;
1336
1337 /************************************************************
1338 Name : initialization_code
1339 Purpose : This performs the context initialization.
1340 Arguments :
1341 Notes :
1342 ************************************************************/
1343
1344 PROCEDURE initialization_code(p_payroll_action_id IN NUMBER) IS
1345 --
1346 lv_procedure_name VARCHAR2(100);
1347 lv_error_message VARCHAR2(200);
1348 ln_step NUMBER;
1349
1350 ld_end_date DATE;
1351 ln_business_group_id NUMBER;
1352 ln_legal_employer_id NUMBER;
1353 ln_asg_set_id NUMBER;
1354
1355 CURSOR c_get_legal_er_info(cp_legal_er_id NUMBER,
1356 cp_effective_date DATE)
1357 IS
1358 SELECT hoi.org_information1 "Name",
1359 hoi.org_information2 "Employer RFC",
1360 ppf.full_name "Legal Representative Name",
1361 ppf.per_information2 "Legal Representative RFC",
1362 ppf.national_identifier "Legal Representative CURP"
1363 FROM hr_organization_information hoi,
1364 per_people_f ppf
1365 WHERE hoi.organization_id = cp_legal_er_id
1366 AND hoi.org_information_context = 'MX_TAX_REGISTRATION'
1367 AND hoi.org_information3 = ppf.person_id
1368 AND cp_effective_date BETWEEN ppf.effective_start_date
1369 AND ppf.effective_end_date;
1370
1371
1372 BEGIN
1373 lv_procedure_name := '.initialization_code';
1374
1375 ln_step := 1;
1376
1377 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1378
1379 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1380 ,p_end_date => ld_end_date
1381 ,p_business_group_id => ln_business_group_id
1382 ,p_legal_employer_id => ln_legal_employer_id
1383 ,p_asg_set_id => ln_asg_set_id);
1384
1385 g_payroll_action_id := p_payroll_action_id;
1386
1387 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1388
1389 ln_step := 2;
1390
1391 OPEN c_get_legal_er_info(ln_legal_employer_id,
1392 ld_end_date);
1393 FETCH c_get_legal_er_info INTO g_ER_legal_name,
1394 g_ER_RFC,
1395 g_ER_legal_rep_name,
1396 g_ER_legal_rep_RFC,
1397 g_ER_legal_rep_CURP;
1398 CLOSE c_get_legal_er_info;
1399
1400 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1401
1402 ln_step := 3;
1403
1404 SELECT TO_CHAR(ld_end_date, 'YYYY')
1405 INTO g_fiscal_year
1406 FROM dual;
1407
1408 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1409 hr_utility.trace('About to initialize the route and database items');
1410 /*Added for bug 7529502*/
1411 load_ye_balance;
1412 hr_utility.trace('Global data for archive items are set ' ||g_archive_item_details.count);
1413 hr_utility.trace('Global data for PTU balance are set ' ||g_ptu_bal_details.count);
1414 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1418 gv_package || lv_procedure_name;
1419
1420 hr_utility.trace(lv_error_message || '-' || SQLERRM);
1421
1422 lv_error_message :=
1423 pay_emp_action_arch.set_error_message(lv_error_message);
1424
1425 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1426 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1427 hr_utility.raise_error;
1428
1429 END initialization_code;
1430
1431 /************************************************************
1432 Name : archive_code
1433 Purpose : This procedure Archives data which is used in
1434 Year End Reporting for Mexico.
1435 Arguments : p_archive_action_id IN NUMBER
1436 p_effective_date IN DATE
1437 Notes :
1438 ************************************************************/
1439 PROCEDURE archive_code(p_archive_action_id IN NUMBER
1440 ,p_effective_date IN DATE)
1441 IS
1442 --
1443 lv_procedure_name VARCHAR2(100);
1444 lv_error_message VARCHAR2(200);
1445 ln_step NUMBER;
1446 ln_index NUMBER;
1447 ln_pay_action_count NUMBER;
1448
1449 lv_economic_zone VARCHAR2(1);
1450 ld_effective_date DATE;
1451
1452 ln_business_group_id NUMBER;
1453 ln_person_id NUMBER;
1454 ln_assignment_id NUMBER;
1455 ln_tax_unit_id NUMBER;
1456 ln_chunk_number NUMBER;
1457 lv_paternal_last_name per_people_f.last_name%TYPE;
1458 lv_maternal_last_name per_people_f.per_information1%TYPE;
1459 lv_names per_people_f.full_name%TYPE;
1460 lv_CURP per_people_f.national_identifier%TYPE;
1461 lv_RFC_ID per_people_f.per_information2%TYPE;
1462 ld_arch_start_date DATE;
1463 ld_arch_end_date DATE;
1464 ln_seniority NUMBER;
1465 ln_tax_subsidy_prop NUMBER;
1466 lv_jurisdiction VARCHAR2(10);
1467 lv_is_union_worker VARCHAR2(1);
1468 ld_hire_date DATE;
1469 ln_curr_tax_unit_id NUMBER;
1470 ln_prev_tax_unit_id NUMBER;
1471
1472 ln_legal_er_id NUMBER;
1473 ln_gre_id NUMBER;
1474 i NUMBER;
1475
1476 TYPE other_ER_rec IS RECORD
1477 (RFC VARCHAR2(30),
1478 ISR_Withheld NUMBER,
1479 Cred_Subsidy NUMBER,
1480 Non_Cred_Subsidy NUMBER,
1481 Total_Earnings NUMBER,
1482 Exempt_Earnings NUMBER);
1483
1484 TYPE other_ER_tbl IS TABLE OF other_ER_rec INDEX BY BINARY_INTEGER;
1485
1486 PEI other_ER_tbl;
1487
1488 ln_total_cred_subsidy NUMBER;
1489 ln_total_subsidy NUMBER;
1490 ln_ytd_aa_id NUMBER;
1491 ld_start_date DATE;
1492 ld_end_date DATE;
1493 ln_cred_subsidy NUMBER;
1494 ln_non_cred_subsidy NUMBER;
1495 ln_isr_calc NUMBER;
1496 ln_total_isr_calc NUMBER;
1497 ld_PTU_date DATE;
1498 ln_gross_earnings NUMBER;
1499 ln_profit_sharing NUMBER;
1500 l_valid_rfc VARCHAR2(30);
1501 lv_plain_rfc VARCHAR2(30);
1502 ln_legal_employer_id NUMBER;
1503 ln_asg_set_id NUMBER;
1504 lb_is_term_ee BOOLEAN;
1505 lb_latest_assignment BOOLEAN; --Bug 11738478
1506 lv_prev_arch_exists VARCHAR2(1);
1507 lv_arch_for_ptu_only VARCHAR2(1);
1508
1509 ln_amends_aaid NUMBER;
1510 ld_amends_date_earned DATE;
1511 ln_amends_payroll_id NUMBER;
1512
1513 ln_LMOS NUMBER;
1514 ln_ISR_on_LMOS NUMBER;
1515
1516 lv_ann_adj VARCHAR2(1);
1517 ln_row_count NUMBER;
1518 ld_act_term_date DATE; /*8402505*/
1519
1520 ld_max_arch_end_date DATE; --sjawid new
1521 INVALID_RFC EXCEPTION;
1522
1523 -- Added for Perf bug 7529502
1524 CURSOR c_asg_action_details IS
1525 SELECT assignment_id,
1526 serial_number,
1527 tax_unit_id
1528 FROM pay_assignment_actions
1529 WHERE assignment_action_id = p_archive_action_id;
1530
1531 -- Get employee details
1532 CURSOR c_emp_details(cp_effective_date DATE) IS
1533 select per_det.*, rownum row_count
1534 from (SELECT DISTINCT
1535 paf.business_group_id,
1536 ppf.person_id,
1537 paf.assignment_id,
1538 paa.tax_unit_id,
1539 paa.chunk_number,
1540 ppf.last_name "Paternal Last Name",
1541 ppf.per_information1 "Maternal Last Name",
1542 ppf.first_name || ' ' || ppf.middle_names,
1543 ppf.national_identifier "CURP",
1544 ppf.per_information2 "RFC ID",
1545 GREATEST(fnd_date.canonical_to_date(g_fiscal_year || '/01/01'),
1546 DECODE(TO_CHAR(pps.date_start, 'YYYY'),
1547 TO_CHAR(cp_effective_date, 'YYYY'),
1548 pps.date_start,
1549 fnd_date.canonical_to_date(g_fiscal_year ||
1550 '/01/01'))
1551 ),
1552
1553 hr_mx_utility.get_seniority(paf.business_group_id,
1554 paa.tax_unit_id,
1555 paf.payroll_id,
1556 ppf.person_id,
1557 cp_effective_date),
1558 NVL(paf_all.labour_union_member_flag, 'N'),
1559 hoi.org_information7 "Economic Zone",
1560 ROUND(0.5 + 0.005 * hr_mx_utility.get_tax_subsidy_percent(
1561 ppf.business_group_id,
1562 paa.tax_unit_id,
1563 cp_effective_date), 4),
1564 hl.region_1 "Jurisdiction"
1565 FROM per_people_f ppf,
1566 per_assignments_f paf,
1567 per_assignments_f paf_all,
1568 pay_assignment_actions paa,
1569 per_periods_of_service pps,
1570 hr_organization_units hou,
1571 hr_organization_information hoi,
1572 hr_locations_all hl,
1573 pay_payroll_actions ppa
1574 WHERE paa.assignment_action_id in
1575 (select assignment_action_id
1576 from pay_assignment_actions paa1
1577 where paa1.assignment_id = paf.assignment_id
1578 and paa1.assignment_id in
1579 (select assignment_id
1580 from pay_assignment_actions
1581 where assignment_action_id = p_archive_action_id
1582 and assignment_id = paf.assignment_id)
1583 and payroll_action_id = ppa.payroll_action_id )
1584 and not exists
1585 ( select 1 from pay_action_information
1586 where action_context_id in
1587 (select assignment_action_id
1588 from pay_assignment_actions
1589 where assignment_id in
1590 (select assignment_id
1591 from pay_assignment_actions
1592 where assignment_action_id = p_archive_action_id)
1593 and payroll_action_id = ppa.payroll_action_id) )
1594 and ppa.payroll_action_id = paa.payroll_action_id
1595 and paa.tax_unit_id in (SELECT DISTINCT gre_node.entity_id
1596 FROM per_gen_hierarchy_nodes gre_node,
1597 per_gen_hierarchy_nodes le_node,
1598 per_gen_hierarchy_versions hier_ver,
1599 fnd_lookup_values flv
1600 WHERE gre_node.node_type = 'MX GRE'
1601 AND gre_node.business_group_id = paf.business_group_id
1602 AND gre_exists (gre_node.entity_id) = 1
1603 AND le_node.node_type = 'MX LEGAL EMPLOYER'
1604 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
1605 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
1606 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
1607 AND status = flv.lookup_code
1608 AND flv.meaning = 'Active'
1609 AND flv.LANGUAGE = 'US'
1610 AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
1611 AND cp_effective_date BETWEEN hier_ver.date_from
1612 AND NVL(hier_ver.date_to,
1613 hr_general.end_of_time))
1614 AND cp_effective_date BETWEEN ppf.effective_start_date
1615 AND ppf.effective_end_date
1616 AND cp_effective_date BETWEEN paf.effective_start_date
1617 AND paf.effective_end_date
1618 AND cp_effective_date BETWEEN paf_all.effective_start_date
1619 AND paf_all.effective_end_date
1620 AND paf.assignment_id = paa.assignment_id
1621 and paf_all.assignment_id = paf.assignment_id
1622 and paf.assignment_id = paf_all.assignment_id
1623 AND ppf.person_id = paf.person_id
1624 AND paf.person_id = paf_all.person_id
1625 and pps.person_id = ppf.person_id
1626 AND pps.period_of_service_id = paf.period_of_service_id
1627 AND hou.organization_id = paa.tax_unit_id
1628 AND hou.organization_id = hoi.organization_id
1629 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
1630 AND hl.location_id = paf.location_id
1631 AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(g_fiscal_year || '/01/01')
1632 AND p_effective_date
1633 AND ppa.action_type in ('Q','R','B','V','I')) per_det;
1634
1635 -- Get Other ER info for ERs of the current year
1636 CURSOR c_get_other_er_info(cp_person_id NUMBER,
1637 cp_effective_date DATE) IS
1638 SELECT pei_information1 RFC,
1639 fnd_number.canonical_to_number(pei_information5) ISR_Withheld,
1640 fnd_number.canonical_to_number(pei_information6) Cr_Subsidy,
1641 fnd_number.canonical_to_number(pei_information7) Non_Cr_Subsidy,
1642 fnd_number.canonical_to_number(pei_information8) Total_Earnings,
1643 fnd_number.canonical_to_number(pei_information9) Exempt_Earnings
1644 FROM per_people_extra_info
1645 WHERE information_type = 'MX_PREV_EMPLOYMENT_INFO'
1646 AND person_id = cp_person_id
1647 AND TO_CHAR(fnd_date.canonical_to_date(pei_information4), 'YYYY') =
1648 TO_CHAR(cp_effective_date, 'YYYY')
1649 ORDER BY pei_information4 DESC;
1650
1651 -- Get end date of Format 37 for the person
1652 CURSOR c_get_eff_date IS
1653 SELECT DISTINCT
1654 pps.actual_termination_date, /*Bug 8402505*/
1655 NVL(pps.actual_termination_date,
1656 nvl(paf.effective_end_date, p_effective_date)) effective_date,
1657 NVL(pps.actual_termination_date,
1658 fnd_date.canonical_to_date(g_fiscal_year || '/12/31')
1659 )
1660 FROM per_people_f ppf,
1661 per_assignments_f paf,
1662 pay_assignment_actions paa,
1663 pay_payroll_actions ppa,
1664 per_periods_of_service pps
1665 WHERE paa.assignment_action_id = p_archive_action_id
1666 AND ppa.payroll_action_id = paa.payroll_action_id
1667 AND paf.assignment_id = paa.assignment_id
1668 AND ppf.person_id = paf.person_id
1669 AND pps.period_of_service_id = paf.period_of_service_id
1670 ORDER BY effective_date DESC;
1671
1672
1673
1674 CURSOR c_check_pay_action(cp_payroll_action_id IN NUMBER) IS
1675 SELECT count(*)
1676 FROM pay_action_information
1677 WHERE action_context_id = cp_payroll_action_id
1678 AND action_context_type = 'PA';
1679
1680 -- Get Generic Hierarchy Details for the current BG
1681 CURSOR c_get_gen_hier_details(cp_business_group_id NUMBER,
1682 cp_effective_date DATE
1683 ) IS
1684 SELECT DISTINCT le_node.entity_id,
1685 gre_node.entity_id
1686 FROM per_gen_hierarchy_nodes gre_node,
1687 per_gen_hierarchy_nodes le_node,
1688 per_gen_hierarchy_versions hier_ver,
1689 fnd_lookup_values flv
1690 WHERE gre_node.node_type = 'MX GRE'
1691 AND gre_node.business_group_id = cp_business_group_id
1692 AND gre_exists (gre_node.entity_id) = 1
1693 AND le_node.node_type = 'MX LEGAL EMPLOYER'
1694 AND gre_node.hierarchy_version_id = le_node.hierarchy_version_id
1695 AND le_node.hierarchy_node_id = gre_node.parent_hierarchy_node_id
1696 AND gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id
1697 AND status = flv.lookup_code
1698 AND flv.meaning = 'Active'
1699 AND flv.LANGUAGE = 'US'
1700 AND flv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS'
1701 AND cp_effective_date BETWEEN hier_ver.date_from
1702 AND NVL(hier_ver.date_to,
1703 hr_general.end_of_time);
1704
1705 -- Get latest ytd aaid
1706 -- Date constraint relaxed since terminated assignments are also included.
1707 CURSOR c_get_ytd_aaid(cp_arch_period_start_date DATE,
1708 cp_arch_period_end_date DATE,
1709 cp_tax_unit_id NUMBER) IS
1710 select /*+ ordered index(PPA PAY_PAYROLL_ACTIONS_PK)*/ paa_all.assignment_action_id
1711 from pay_assignment_actions paa_pri ,
1712 per_assignments_f paf_pri ,
1713 per_assignments_f paf_all ,
1714 pay_assignment_actions paa_all,
1715 pay_payroll_actions ppa ,
1716 pay_action_classifications pac
1717 WHERE paa_pri.assignment_action_id = p_archive_action_id
1718 AND paf_pri.assignment_id = paa_pri.assignment_id
1719 AND paf_all.period_of_service_id = paf_pri.period_of_service_id
1720 AND paa_all.tax_unit_id = cp_tax_unit_id
1721 AND paa_all.assignment_id = paf_all.assignment_id
1722 AND paa_all.payroll_action_id = ppa.payroll_action_id
1723 AND ppa.action_type = pac.action_type
1724 AND pac.classification_name = 'SEQUENCED'
1725 AND paa_all.action_status = 'C'
1726 AND ppa.effective_date BETWEEN cp_arch_period_start_date
1727 AND cp_arch_period_end_date
1728 ORDER BY paa_all.action_sequence DESC;
1729
1730 -- Get the creditable and non-creditable subsidy for the person under
1731 -- the current employer
1732 /*CURSOR c_get_subsidy(cp_ytd_action_id NUMBER
1733 ) IS
1734 SELECT pay_balance_pkg.get_value(pdb_cr.defined_balance_id,
1735 cp_ytd_action_id),
1736 pay_balance_pkg.get_value(pdb_ncr.defined_balance_id,
1737 cp_ytd_action_id)
1738 FROM pay_defined_balances pdb_cr,
1739 pay_defined_balances pdb_ncr,
1740 pay_balance_types pbt_cr,
1741 pay_balance_types pbt_ncr,
1742 pay_balance_dimensions pbd
1743 WHERE pdb_cr.balance_type_id = pbt_cr.balance_type_id
1744 AND pdb_ncr.balance_type_id = pbt_ncr.balance_type_id
1745 AND pdb_cr.balance_dimension_id = pbd.balance_dimension_id
1746 AND pdb_ncr.balance_dimension_id = pbd.balance_dimension_id
1747 AND pbt_cr.balance_name = 'ISR Creditable Subsidy'
1748 AND pbt_ncr.balance_name = 'ISR Non Creditable Subsidy'
1749 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1750 AND pbt_cr.legislation_code = 'MX'
1751 AND pbt_ncr.legislation_code = pbt_cr.legislation_code
1752 AND pbd.legislation_code = pbt_ncr.legislation_code; */
1753
1754 -- Get the ISR Calculated, creditable and non-creditable subsidy
1755 -- for the person under the current employer
1756 CURSOR c_get_subsidy(cp_ytd_action_id NUMBER
1757 ) IS
1758 SELECT pay_balance_pkg.get_value(pdb_cr.defined_balance_id,
1759 cp_ytd_action_id)
1760 FROM pay_defined_balances pdb_cr,
1761 pay_balance_types pbt_cr,
1762 pay_balance_dimensions pbd
1763 WHERE pdb_cr.balance_type_id = pbt_cr.balance_type_id
1764 AND pdb_cr.balance_dimension_id = pbd.balance_dimension_id
1765 AND pbt_cr.balance_name = 'ISR Creditable Subsidy'
1766 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1767 AND pbt_cr.legislation_code = 'MX';
1768
1769 CURSOR c_get_nonsubsidy(cp_ytd_action_id NUMBER
1770 ) IS
1771 select pay_balance_pkg.get_value(pdb_ncr.defined_balance_id,
1772 cp_ytd_action_id)
1773 FROM pay_defined_balances pdb_ncr,
1774 pay_balance_types pbt_ncr,
1775 pay_balance_dimensions pbd
1776 WHERE pdb_ncr.balance_type_id = pbt_ncr.balance_type_id
1777 AND pdb_ncr.balance_dimension_id = pbd.balance_dimension_id
1778 AND pbt_ncr.balance_name = 'ISR Non Creditable Subsidy'
1779 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1780 AND pbt_ncr.legislation_code = 'MX'
1781 AND pbd.legislation_code = pbt_ncr.legislation_code;
1782
1783 CURSOR c_get_calc(cp_ytd_action_id NUMBER
1784 ) IS
1785 select pay_balance_pkg.get_value(pdb_calc.defined_balance_id,
1786 cp_ytd_action_id)
1787 FROM pay_defined_balances pdb_calc,
1788 pay_balance_types pbt_calc,
1789 pay_balance_dimensions pbd
1790 WHERE pdb_calc.balance_type_id = pbt_calc.balance_type_id
1791 AND pdb_calc.balance_dimension_id = pbd.balance_dimension_id
1792 AND pbt_calc.balance_name = 'ISR Calculated'
1793 AND pbd.database_item_suffix = '_PER_PDS_GRE_YTD'
1794 AND pbt_calc.legislation_code = 'MX'
1795 AND pbd.legislation_code = pbt_calc.legislation_code;
1796
1797
1798 -- Get the month of payment of Profit Sharing
1799 CURSOR c_get_PTU_month(cp_person_id NUMBER,
1800 cp_tax_unit_id NUMBER,
1801 cp_start_date DATE,
1802 cp_end_date DATE
1803 ) IS
1804 SELECT /*+ index(PPA PAY_PAYROLL_ACTIONS_PK)*/ DISTINCT ppa.effective_date
1805 FROM pay_run_results prr,
1806 pay_run_result_values prrv,
1807 pay_assignment_actions paa,
1808 pay_payroll_actions ppa,
1809 pay_input_values_f piv,
1810 pay_balance_feeds_f pbf,
1811 pay_balance_types pbt,
1812 per_assignments_f paf
1813 WHERE pbt.balance_name = 'Profit Sharing'
1814 AND pbt.legislation_code = 'MX'
1815 AND pbf.balance_type_id = pbt.balance_type_id
1816 AND piv.input_value_id = pbf.input_value_id
1817 AND prr.element_type_id = piv.element_type_id
1818 AND prrv.run_result_id = prr.run_result_id
1819 AND prr.assignment_action_id = paa.assignment_action_id
1820 AND paa.assignment_id = paf.assignment_id
1821 AND paf.person_id = cp_person_id
1822 AND paa.tax_unit_id = cp_tax_unit_id
1823 AND ppa.payroll_action_id = paa.payroll_action_id
1824 AND ppa.action_type IN ('R', 'Q', 'B', 'V', 'I')
1825 AND ppa.effective_date BETWEEN piv.effective_start_date
1826 AND piv.effective_end_date
1827 AND ppa.effective_date BETWEEN pbf.effective_start_date
1828 AND pbf.effective_end_date
1829 AND ppa.effective_date BETWEEN paf.effective_start_date
1830 AND paf.effective_end_date
1831 AND ppa.effective_date BETWEEN cp_start_date
1832 AND cp_end_date
1833 ORDER BY 1 DESC;
1834
1835 -- Check if any previous archiver exists for the same period of service
1836 --
1837 CURSOR c_chk_last_archiver(cp_assignment_id NUMBER,
1838 cp_start_date DATE,
1839 cp_end_date DATE,
1840 cp_tax_unit_id NUMBER) IS
1841 SELECT paa1.assignment_action_id, 'Y'
1842 FROM pay_payroll_actions ppa1,
1843 pay_assignment_actions paa1,
1844 per_assignments_f paf1,
1845 per_assignments_f paf2
1846 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
1847 AND paa1.assignment_id = paf1.assignment_id
1848 AND paf1.period_of_service_id = paf2.period_of_service_id
1849 AND paf2.assignment_id = cp_assignment_id
1850 AND ppa1.report_type = 'MX_YREND_ARCHIVE'
1851 AND ppa1.report_qualifier = 'MX'
1852 AND ppa1.report_category = 'ARCHIVE'
1853 AND paa1.assignment_action_id <> p_archive_action_id
1854 AND paf1.effective_start_date <= cp_end_date
1855 AND paf1.effective_end_date >= cp_start_date
1856 AND paf2.effective_start_date <= cp_end_date
1857 AND paf2.effective_end_date >= cp_start_date
1858 AND TO_CHAR(ppa1.effective_date, 'YYYY') = TO_CHAR(cp_end_date, 'YYYY')
1859 AND paa1.tax_unit_id = cp_tax_unit_id
1860 ORDER BY 1 desc;
1861
1862 CURSOR c_fetch_Ann_adj(cp_person_id NUMBER
1863 ,cd_end_date DATE ) is
1864 SELECT distinct 'Y'
1865 FROM per_all_assignments_f paf
1866 WHERE paf.person_id = cp_person_id
1867 AND EXISTS ( SELECT 1
1868 FROM pay_assignment_actions paa
1869 ,pay_payroll_actions ppa
1870 WHERE paa.payroll_action_id = ppa.payroll_action_id
1871 AND ppa.action_type = 'B'
1872 AND ppa.effective_date BETWEEN trunc(cd_end_date,'Y')
1873 and cd_end_date
1874 AND ppa.business_group_id = ln_business_group_id
1875 AND pay_mx_utility.get_legi_param_val('PROCESS',
1876 legislative_parameters) = 'MX_ANN_ADJ'
1877 AND paa.assignment_id = paf.assignment_id
1878 );
1879
1880 CURSOR c_ann_tax_type (cp_business_group_id NUMBER
1881 ,cp_effective_date DATE
1882 ,cp_person_id NUMBER) IS
1883 SELECT pay_mx_utility.get_legi_param_val('CALC_MODE'
1884 ,legislative_parameters)
1885 ,paa.assignment_action_id
1886 FROM per_all_assignments_f paf
1887 ,pay_assignment_actions paa
1888 ,pay_payroll_actions ppa
1889 WHERE person_id = cp_person_id
1890 AND paa.payroll_action_id = ppa.payroll_action_id
1891 AND ppa.action_type = 'B'
1892 AND ppa.effective_date = cp_effective_date
1893 AND ppa.business_group_id = cp_business_group_id
1894 AND pay_mx_utility.get_legi_param_val('PROCESS'
1895 ,legislative_parameters) = 'MX_ANN_ADJ'
1896 AND paa.assignment_id = paf.assignment_id
1897 ORDER BY ppa.payroll_action_id desc;
1898
1899 CURSOR c_pact_info ( cp_assignment_action_id NUMBER) IS
1900 select ppa.business_group_id
1901 ,ppa.effective_date
1902 from pay_payroll_actions ppa
1903 ,pay_assignment_actions paa
1904 where paa.assignment_action_id = cp_assignment_action_id
1905 and ppa.payroll_action_id = paa.payroll_action_id;
1906
1907 CURSOR c_input_value_id IS
1908 SELECT piv.input_value_id
1909 FROM pay_element_types_f pet
1910 ,pay_input_values_f piv
1911 WHERE pet.legislation_code = 'MX'
1912 AND pet.element_name = 'Annual Tax Adjustment'
1913 AND piv.element_type_id = pet.element_type_id
1914 AND piv.name = 'Calculation Mode';
1915
1916 CURSOR c_get_anntaxadj_article ( cp_assignment_action_id NUMBER
1917 ,cp_input_value_id NUMBER ) IS
1918 SELECT result_value
1919 FROM pay_run_results prr
1920 ,pay_run_result_values prrv
1921 WHERE prr.assignment_action_id = cp_assignment_action_id
1922 AND prrv.run_result_id = prr.run_result_id
1923 AND prrv.input_value_id = cp_input_value_id;
1924
1925 CURSOR c_get_hire_date ( cp_person_id NUMBER
1926 ,cp_effective_date DATE ) IS
1927 SELECT MAX (pps.date_start)
1928 FROM per_periods_of_service pps
1929 WHERE pps.person_id = cp_person_id
1930 AND pps.date_start <= cp_effective_date;
1931
1932 /*bug 11715919 */
1933 CURSOR c_get_max_arch_end_date ( cp_person_id NUMBER) IS
1934 SELECT NVL(max(actual_termination_date),
1935 fnd_date.canonical_to_date(g_fiscal_year || '/12/31'))
1936 FROM per_periods_of_service
1937 WHERE person_id = cp_person_id
1938 AND actual_termination_date
1939 BETWEEN fnd_date.canonical_to_date(g_fiscal_year || '/01/01')
1940 AND fnd_date.canonical_to_date(g_fiscal_year || '/12/31');
1941 /* Bug 13093752 */
1942 CURSOR c_get_amends_amount IS
1943 SELECT nvl(fnd_number.canonical_to_number(value),0)
1944 FROM ff_archive_items
1945 WHERE user_entity_id = (SELECT user_entity_id
1946 FROM ff_user_entities
1947 WHERE user_entity_name = 'A_AMENDS_PER_PDS_GRE_YTD')
1948 AND context1 = p_archive_action_id;
1949
1950 lv_ann_tax_calc_type VARCHAR2(240);
1951 ln_anntaxadj_asgactid NUMBER;
1952 ln_input_value_id NUMBER;
1953 lv_anntaxadj_article VARCHAR2(240);
1954 lv_amends_amount NUMBER := 0; -- Bug 13093752
1955
1956
1957 BEGIN
1958 lv_procedure_name := '.archive_code';
1959 --hr_utility.trace_on(null,'MX_NR');
1960 lv_prev_arch_exists := 'N';
1961 lv_arch_for_ptu_only := 'N';
1962 hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1963
1964 hr_utility.trace('p_archive_action_id: ' ||p_archive_action_id);
1965 hr_utility.trace('p_effective_date: ' ||p_effective_date);
1966
1967 ln_step := 1;
1968 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1969
1970 -- Load GRE cache
1971 IF g_gre_tab.count() = 0 THEN
1972
1973 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1974
1975 get_payroll_action_info(p_payroll_action_id => g_payroll_action_id
1976 ,p_end_date => ld_end_date
1977 ,p_business_group_id => ln_business_group_id
1978 ,p_legal_employer_id => ln_legal_employer_id
1979 ,p_asg_set_id => ln_asg_set_id);
1980
1981 hr_utility.trace('ld_end_date: ' || ld_end_date);
1982 hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1983 hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1984 hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1985
1986 load_gre (ln_business_group_id,
1987 ln_legal_employer_id,
1988 ld_end_date);
1989 END IF;
1990
1991 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1992 OPEN c_asg_action_details;
1993 FETCH c_asg_action_details INTO ln_assignment_id,ln_person_id,ln_tax_unit_id;
1994 CLOSE c_asg_action_details;
1995
1996 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1997
1998 OPEN c_get_eff_date;
1999 FETCH c_get_eff_date INTO ld_act_term_date,ld_effective_date, ld_arch_end_date;
2000 CLOSE c_get_eff_date;
2001
2002 hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2003 hr_utility.trace('ld_effective_date: '||ld_effective_date);
2004 hr_utility.trace('ld_arch_end_date: '||ld_arch_end_date);
2005
2006 IF ld_arch_end_date <> fnd_date.canonical_to_date(g_fiscal_year ||
2007 '/12/31') THEN
2008
2009 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2010 lb_is_term_ee := TRUE;
2011 hr_utility.trace('lb_is_term_ee= TRUE');
2012 ELSIF (ld_act_term_date IS NOT NULL) AND (ld_act_term_date = fnd_date.canonical_to_date
2013 (g_fiscal_year ||'/12/31')) THEN
2014 hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2015 hr_utility.set_location(gv_package || lv_procedure_name, 45);
2016 hr_utility.trace('Emp is terminated on last pay period');
2017 lb_is_term_ee := TRUE;
2018 hr_utility.trace('lb_is_term_ee= TRUE');
2019 ELSE
2020 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2021 lb_is_term_ee := FALSE;
2022 hr_utility.trace('lb_is_term_ee= FALSE');
2023 END IF;
2024
2025 ln_step := 2;
2026 hr_utility.trace('Effective Date of archiver for the person: ' ||
2027 fnd_date.date_to_canonical(p_effective_date));
2028
2029
2030 ln_step := 3;
2031 ld_start_date := fnd_date.canonical_to_date(g_fiscal_year || '/01/01');
2032
2033 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2034
2035 -- Fetch the YTD Assignment Action ID.
2036 --
2037 ln_ytd_aa_id := NULL;
2038
2039 OPEN c_get_ytd_aaid(ld_start_date,
2040 p_effective_date,
2041 ln_tax_unit_id);
2042 FETCH c_get_ytd_aaid INTO ln_ytd_aa_id;
2043
2044 IF c_get_ytd_aaid%NOTFOUND THEN
2045
2046 hr_utility.trace('No assignment action found for the person''s ' ||
2047 'period of service within the GRE!');
2048 hr_utility.raise_error;
2049
2050 ELSE
2051
2052 hr_utility.trace('YTD Assactid: '|| ln_ytd_aa_id);
2053
2054 END IF;
2055
2056 CLOSE c_get_ytd_aaid;
2057
2058 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2059
2060 ln_step := 5;
2061 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2062
2063
2064 OPEN c_chk_last_archiver(ln_assignment_id,
2065 ld_start_date,
2066 p_effective_date,
2067 ln_tax_unit_id);
2068 FETCH c_chk_last_archiver INTO gn_prev_asg_act_id
2069 ,lv_prev_arch_exists;
2070 CLOSE c_chk_last_archiver;
2071
2072 hr_utility.trace('ln_person_id: '|| ln_person_id);
2073 hr_utility.trace('ln_assignment_id: '|| ln_assignment_id);
2074 hr_utility.trace('gn_prev_asg_act_id: '|| gn_prev_asg_act_id);
2075 hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2076
2077 IF ( lv_prev_arch_exists = 'Y' OR lb_is_term_ee ) THEN
2078
2079 hr_utility.set_location(gv_package || lv_procedure_name, 90);
2080
2081 ld_PTU_date := NULL;
2082
2083
2084
2085 OPEN c_get_PTU_month(ln_person_id,
2086 ln_tax_unit_id,
2087 ld_start_date,
2088 p_effective_date);
2089 FETCH c_get_PTU_month INTO ld_PTU_date;
2090 CLOSE c_get_PTU_month;
2091
2092 /* bug 11715919 */
2093 OPEN c_get_max_arch_end_date(ln_person_id);
2094 FETCH c_get_max_arch_end_date INTO ld_max_arch_end_date;
2095 CLOSE c_get_max_arch_end_date;
2096
2097 hr_utility.trace('ld_PTU_date: = '|| ld_PTU_date);
2098 hr_utility.trace('ld_max_arch_end_date: ='|| ld_max_arch_end_date);
2099
2100 IF ld_max_arch_end_date IS NULL THEN
2101 ld_max_arch_end_date := fnd_date.canonical_to_date(g_fiscal_year || '/12/31');
2102 END IF;
2103
2104 hr_utility.trace('ld_max_arch_end_date: '||ld_max_arch_end_date);
2105
2106 IF ld_max_arch_end_date = ld_arch_end_date THEN
2107 hr_utility.trace('lb_latest_assignment is set as TRUE');
2108 lb_latest_assignment := TRUE; --Bug 11738478
2109 END IF;
2110
2111 IF ld_PTU_date IS NOT NULL
2112 AND (ld_PTU_DATE > ld_arch_end_date) -- Added for bug 8402464
2113 THEN
2114
2115 ld_PTU_date := TRUNC(ld_PTU_date, 'MM');
2116
2117 /* bug 11715919 */
2118 IF ld_max_arch_end_date = ld_arch_end_date THEN
2119 ld_arch_end_date := ADD_MONTHS(ld_PTU_date, 1) - 1;
2120 END IF;
2121
2122 hr_utility.trace('PTU ld_arch_start_date: '||ld_PTU_date);
2123 hr_utility.trace('PTU ld_arch_end_date: '||ld_arch_end_date);
2124 hr_utility.trace('ld_PTU_date,YYYY: '||to_char(ld_PTU_date,'YYYY'));
2125 hr_utility.trace('ld_act_term_date: '||to_char(ld_act_term_date,'YYYY'));
2126 /*For seniority computation 8402464*/
2127 IF lv_prev_arch_exists = 'N'
2128 AND (TO_CHAR (ld_PTU_date,'YYYY') = TO_CHAR(ld_act_term_date,'YYYY'))
2129 THEN
2130 lv_arch_for_ptu_only := 'N';
2131 ELSE
2132 lv_arch_for_ptu_only := 'Y';
2133 END IF;
2134 END IF;
2135
2136 END IF;
2137
2138 ln_step := 6;
2139 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2140
2141 /*OPEN c_get_subsidy(ln_ytd_aa_id);
2142 FETCH c_get_subsidy INTO ln_cred_subsidy,
2143 ln_non_cred_subsidy;
2144 CLOSE c_get_subsidy;
2145
2146 OPEN c_get_calc(ln_ytd_aa_id);
2147 FETCH c_get_calc INTO ln_isr_calc;
2148 CLOSE c_get_calc;
2149 */
2150 OPEN c_get_subsidy(ln_ytd_aa_id);
2151 FETCH c_get_subsidy INTO ln_cred_subsidy;
2152 CLOSE c_get_subsidy;
2153
2154 OPEN c_get_nonsubsidy(ln_ytd_aa_id);
2155 FETCH c_get_nonsubsidy INTO ln_non_cred_subsidy;
2156 CLOSE c_get_nonsubsidy;
2157
2158 ln_step := 7;
2159 hr_utility.set_location(gv_package || lv_procedure_name, 105);
2160 -- Initialise the variables
2161 --
2162 ln_total_subsidy := ln_cred_subsidy + ln_non_cred_subsidy;
2163 ln_total_cred_subsidy := ln_cred_subsidy;
2164 i := 0;
2165 --
2166 hr_utility.set_location(gv_package || lv_procedure_name, 110);
2167
2168 -- EE details need to be fetched as on the Actual
2169 -- Termination Date, or p_effective_date, in that order.
2170
2171 /* Begin - Bug 13093752 */
2172
2173 /*OPEN c_get_amends_amount;
2174 FETCH c_get_amends_amount INTO lv_amends_amount;
2175 CLOSE c_get_amends_amount;*/
2176
2177 ln_curr_tax_unit_id := -1;
2178 ln_prev_tax_unit_id := -1;
2179
2180 /* End - Bug 13093752 */
2181
2182 OPEN c_emp_details(ld_effective_date);
2183 loop
2184 FETCH c_emp_details INTO ln_business_group_id,
2185 ln_person_id,
2186 ln_assignment_id,
2187 ln_tax_unit_id,
2188 ln_chunk_number,
2189 lv_paternal_last_name,
2190 lv_maternal_last_name,
2191 lv_names,
2192 lv_CURP,
2193 lv_RFC_ID,
2194 ld_arch_start_date,
2195 ln_seniority,
2196 lv_is_union_worker,
2197 lv_economic_zone,
2198 ln_tax_subsidy_prop,
2199 lv_jurisdiction,
2200 ln_row_count;
2201 exit when c_emp_details%NOTFOUND;
2202
2203 --CLOSE c_emp_details;
2204
2205 ln_curr_tax_unit_id := ln_tax_unit_id;
2206
2207 ln_step := 4;
2208 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2209 hr_utility.trace('ld_start_date: ' || ld_start_date);
2210 hr_utility.trace('p_effective_date: ' || p_effective_date);
2211 hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212 hr_utility.trace('ln_person_id: ' || ln_person_id);
2213 hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
2214 hr_utility.trace('ln_row_count: ' || ln_row_count);
2215
2216 /*FOR c_rec IN c_get_other_er_info(ln_person_id,
2217 p_effective_date)
2218 LOOP
2219 i := i + 1;
2220 PEI(i).isr_withheld := c_rec.isr_withheld;
2221 PEI(i).cred_subsidy := c_rec.cr_subsidy;
2222 PEI(i).non_cred_subsidy := c_rec.non_cr_subsidy;
2223 PEI(i).total_earnings := c_rec.total_earnings;
2224 PEI(i).exempt_earnings := c_rec.exempt_earnings;
2225
2226 ln_total_cred_subsidy := ln_total_cred_subsidy +
2227 NVL(PEI(i).cred_subsidy, 0);
2228 ln_total_subsidy := ln_total_subsidy + NVL(PEI(i).cred_subsidy, 0) +
2229 NVL(PEI(i).non_cred_subsidy, 0);
2230
2231 -- RFC Validation to be performed
2232 -- Hyphens are stripped and RFC is then validated.
2233 --
2234 hr_utility.set_location(gv_package || lv_procedure_name, 120);
2235 lv_plain_rfc :=
2236 TRANSLATE(c_rec.RFC, 'A !"$%^&*()-_+=`[]{};''#:@~<>?','A');
2237
2238 l_valid_rfc := hr_ni_chk_pkg.chk_nat_id_format(lv_plain_rfc,
2239 'AAADDDDDDXXX');
2240 IF l_valid_rfc = '0' THEN
2241 hr_utility.set_location(gv_package || lv_procedure_name, 130);
2242 RAISE INVALID_RFC;
2243 ELSE
2244 PEI(i).RFC := l_valid_rfc;
2245 END IF;
2246
2247 END LOOP;*/
2248
2249 lv_ann_adj := NULL;
2250
2251 OPEN c_fetch_Ann_adj(ln_person_id,p_effective_date);
2252 FETCH c_fetch_Ann_adj INTO lv_ann_adj;
2253 CLOSE c_fetch_Ann_adj;
2254
2255 IF lv_ann_adj IS NULL THEN
2256 lv_ann_adj := 'N';
2257 END IF;
2258
2259 /*ld_hire_date := fnd_date.canonical_to_date('1900/01/01');
2260
2261 OPEN c_get_hire_date(ln_person_id,ld_effective_date); --Re-hire bug
2262 FETCH c_get_hire_date INTO ld_hire_date;
2263 CLOSE c_get_hire_date;
2264
2265 hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2266 hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2267 hr_utility.trace('ld_hire_date: '||ld_hire_date);
2268
2269 IF lv_arch_for_ptu_only = 'Y' THEN
2270 ld_arch_start_date := ld_PTU_date;
2271 END IF ;
2272 hr_utility.trace('After PTU check ld_arch_start_date: '||ld_arch_start_date);
2273 ld_arch_start_date := GREATEST( ld_arch_start_date, ld_hire_date );
2274
2275 IF TRUNC( ld_arch_end_date, 'Y' ) = TRUNC( p_effective_date, 'Y' ) THEN
2276
2277 ld_arch_end_date := LEAST( ld_arch_end_date, p_effective_date );
2278
2279 ELSE
2280
2281 ld_arch_end_date := p_effective_date;
2282
2283 END IF;
2284
2285 hr_utility.trace('AFTER ld_arch_start_date: '||ld_arch_start_date);
2286 hr_utility.trace('AFTER ld_arch_end_date: '||ld_arch_end_date);*/
2287
2288 IF ln_curr_tax_unit_id <> ln_prev_tax_unit_id THEN
2289 OPEN c_get_ytd_aaid(ld_start_date,
2290 p_effective_date,
2291 ln_tax_unit_id);
2292 FETCH c_get_ytd_aaid INTO ln_ytd_aa_id;
2293 IF c_get_ytd_aaid%NOTFOUND THEN
2294 hr_utility.trace('No assignment action found for the person''s ' ||
2295 'period of service within the GRE!');
2296 hr_utility.raise_error;
2297
2298 ELSE
2299 hr_utility.trace('YTD Assactid: '|| ln_ytd_aa_id);
2300
2301 END IF;
2302 CLOSE c_get_ytd_aaid;
2303
2304 hr_utility.trace('--Before calling populate_balances--');
2305 hr_utility.trace('--TAX_UNIT_ID--'||ln_tax_unit_id);
2306 hr_utility.trace('--YTD ASSACT ID--'||ln_ytd_aa_id);
2307 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2308
2309 populate_balances(p_archive_action_id => p_archive_action_id
2310 ,p_ytd_action_id => ln_ytd_aa_id
2311 ,p_tax_unit_id => ln_tax_unit_id
2312 ,p_prev_archiver_exists => lv_prev_arch_exists
2313 ,p_ann_adj_flag => lv_ann_adj);
2314 END IF;
2315
2316 ln_prev_tax_unit_id := ln_curr_tax_unit_id;
2317
2318 ln_step := 7;
2319
2320 If ln_row_count = 1 then
2321
2322 FOR c_rec IN c_get_other_er_info(ln_person_id,
2323 p_effective_date)
2324 LOOP
2325 i := i + 1;
2326 PEI(i).isr_withheld := c_rec.isr_withheld;
2327 PEI(i).cred_subsidy := c_rec.cr_subsidy;
2328 PEI(i).non_cred_subsidy := c_rec.non_cr_subsidy;
2329 PEI(i).total_earnings := c_rec.total_earnings;
2330 PEI(i).exempt_earnings := c_rec.exempt_earnings;
2331
2332 ln_total_cred_subsidy := ln_total_cred_subsidy +
2333 NVL(PEI(i).cred_subsidy, 0);
2334 ln_total_subsidy := ln_total_subsidy + NVL(PEI(i).cred_subsidy, 0) +
2335 NVL(PEI(i).non_cred_subsidy, 0);
2336
2337 -- RFC Validation to be performed
2338 -- Hyphens are stripped and RFC is then validated.
2339 --
2340 hr_utility.set_location(gv_package || lv_procedure_name, 120);
2341 lv_plain_rfc :=
2342 TRANSLATE(c_rec.RFC, 'A !"$%^&*()-_+=`[]{};''#:@~<>?','A');
2343
2344 l_valid_rfc := hr_ni_chk_pkg.chk_nat_id_format(lv_plain_rfc,
2345 'AAADDDDDDXXX');
2346 IF l_valid_rfc = '0' THEN
2347 hr_utility.set_location(gv_package || lv_procedure_name, 130);
2348 RAISE INVALID_RFC;
2349 ELSE
2350 PEI(i).RFC := l_valid_rfc;
2351 END IF;
2352
2353 END LOOP;
2354
2355 /*lv_ann_adj := NULL;
2356
2357 OPEN c_fetch_Ann_adj(ln_person_id,p_effective_date);
2358 FETCH c_fetch_Ann_adj INTO lv_ann_adj;
2359 CLOSE c_fetch_Ann_adj;*/
2360
2361 ld_hire_date := fnd_date.canonical_to_date('1900/01/01');
2362
2363 OPEN c_get_hire_date(ln_person_id,ld_effective_date); --Re-hire bug
2364 FETCH c_get_hire_date INTO ld_hire_date;
2365 CLOSE c_get_hire_date;
2366
2367 OPEN c_get_amends_amount;
2368 FETCH c_get_amends_amount INTO lv_amends_amount;
2369 CLOSE c_get_amends_amount;
2370
2371 hr_utility.trace('lv_amends_amount: '||to_char(lv_amends_amount));
2372
2373 hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2374 hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2375 hr_utility.trace('ld_hire_date: '||ld_hire_date);
2376
2377 IF lv_arch_for_ptu_only = 'Y' THEN
2378 ld_arch_start_date := ld_PTU_date;
2379 END IF ;
2380 hr_utility.trace('After PTU check ld_arch_start_date: '||ld_arch_start_date);
2381 ld_arch_start_date := GREATEST( ld_arch_start_date, ld_hire_date );
2382
2383 IF TRUNC( ld_arch_end_date, 'Y' ) = TRUNC( p_effective_date, 'Y' ) THEN
2384
2385 ld_arch_end_date := LEAST( ld_arch_end_date, p_effective_date );
2386
2387 ELSE
2388
2389 ld_arch_end_date := p_effective_date;
2390
2391 END IF;
2392
2393 hr_utility.trace('AFTER ld_arch_start_date: '||ld_arch_start_date);
2394 hr_utility.trace('AFTER ld_arch_end_date: '||ld_arch_end_date);
2395
2396 ln_index := pai_tab.count;
2397
2398 pai_tab(ln_index).action_info_category := 'MX YREND EE DETAILS';
2399 pai_tab(ln_index).jurisdiction_code := lv_jurisdiction;
2400 pai_tab(ln_index).action_context_id := p_archive_action_id;
2401 /* Bug: 9820914: Handling Special Chars in Employee name */
2402 pai_tab(ln_index).act_info1 := replace(pay_mx_rules.strip_spl_chars(lv_paternal_last_name),'/','N');
2403 pai_tab(ln_index).act_info2 := replace(pay_mx_rules.strip_spl_chars(lv_maternal_last_name),'/','N');
2404 pai_tab(ln_index).act_info3 := replace(pay_mx_rules.strip_spl_chars(lv_names),'/','N');
2405 pai_tab(ln_index).act_info4 := lv_CURP;
2406 pai_tab(ln_index).act_info5 := lv_RFC_ID;
2407 pai_tab(ln_index).act_info6 :=
2408 fnd_date.date_to_canonical(ld_arch_start_date);
2409 pai_tab(ln_index).act_info7 :=
2410 fnd_date.date_to_canonical(ld_arch_end_date);
2411
2412 hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2413 hr_utility.trace('lv_arch_for_ptu_only: '|| lv_arch_for_ptu_only);
2414 hr_utility.trace('lv_amends_amount: '|| lv_amends_amount);
2415
2416 IF lb_is_term_ee AND lv_prev_arch_exists = 'N' AND
2417 lv_arch_for_ptu_only = 'N' AND lb_latest_assignment AND lv_amends_amount > 0 THEN -- Bug 11738478, Bug 13093752
2418
2419 hr_utility.trace('Setting the seniority');
2420 pai_tab(ln_index).act_info8 := ln_seniority;
2421
2422 ELSE
2423
2424 pai_tab(ln_index).act_info8 := 0;
2425
2426 END IF;
2427
2428 -- sets the flag for the person where Annual Tax Adjusment process
2429 -- has been run act_info9
2430 pai_tab(ln_index).act_info9 := lv_ann_adj;
2431 pai_tab(ln_index).act_info10 := lv_is_union_worker;
2432 pai_tab(ln_index).act_info11 := lv_economic_zone;
2433
2434 hr_utility.set_location(gv_package || lv_procedure_name, 140);
2435 --
2436 IF ln_total_subsidy > 0 THEN
2437
2438 pai_tab(ln_index).act_info12 := ln_tax_subsidy_prop;
2439 ELSE
2440
2441 pai_tab(ln_index).act_info12 := NULL;
2442 END IF;
2443
2444 --
2445 -- Archived only if Annual Tax Adjustment is run for the EE
2446 -- and a different subsidy proportion has been used.
2447 --
2448 IF ln_total_subsidy > 0 AND lv_ann_adj = 'Y' THEN
2449
2450 hr_utility.set_location(gv_package || lv_procedure_name, 150);
2451 pai_tab(ln_index).act_info13 :=
2452 ROUND(ln_total_cred_subsidy / ln_total_subsidy, 4);
2453
2454 IF ln_tax_subsidy_prop <> pai_tab(ln_index).act_info13 THEN
2455
2456 hr_utility.set_location(gv_package || lv_procedure_name, 160);
2457 pai_tab(ln_index).act_info13 := NULL;
2458
2459 END IF;
2460
2461 ELSE
2462
2463 hr_utility.set_location(gv_package || lv_procedure_name, 170);
2464 pai_tab(ln_index).act_info13 := NULL;
2465
2466 END IF;
2467
2468 hr_utility.set_location(gv_package || lv_procedure_name, 180);
2469 --
2470 IF PEI.EXISTS(1) THEN
2471 pai_tab(ln_index).act_info14 := PEI(1).RFC;
2472 pai_tab(ln_index).act_info24 := PEI(1).total_earnings;
2473 pai_tab(ln_index).act_info25 := PEI(1).isr_withheld;
2474 pai_tab(ln_index).act_info27 := PEI(1).exempt_earnings;
2475
2476 ELSE -- No "Other Employer Info" exists.
2477
2478 pai_tab(ln_index).act_info14 := NULL;
2479 pai_tab(ln_index).act_info24 := NULL;
2480 pai_tab(ln_index).act_info25 := NULL;
2481 END IF;
2482
2483 IF PEI.EXISTS(2) THEN
2484 pai_tab(ln_index).act_info15 := PEI(2).RFC;
2485 ELSE
2486 pai_tab(ln_index).act_info15 := NULL;
2487 END IF;
2488
2489 IF PEI.EXISTS(3) THEN
2490 pai_tab(ln_index).act_info16 := PEI(3).RFC;
2491 ELSE
2492 pai_tab(ln_index).act_info16 := NULL;
2493 END IF;
2494
2495 IF PEI.EXISTS(4) THEN
2496 pai_tab(ln_index).act_info17 := PEI(4).RFC;
2497 ELSE
2498 pai_tab(ln_index).act_info17 := NULL;
2499 END IF;
2500
2501 IF PEI.EXISTS(5) THEN
2502 pai_tab(ln_index).act_info18 := PEI(5).RFC;
2503 ELSE
2504 pai_tab(ln_index).act_info18 := NULL;
2505 END IF;
2506
2507 IF PEI.EXISTS(6) THEN
2508 pai_tab(ln_index).act_info19 := PEI(6).RFC;
2509 ELSE
2510 pai_tab(ln_index).act_info19 := NULL;
2511 END IF;
2512
2513 IF PEI.EXISTS(7) THEN
2514 pai_tab(ln_index).act_info20 := PEI(7).RFC;
2515 ELSE
2516 pai_tab(ln_index).act_info20 := NULL;
2517 END IF;
2518
2519 IF PEI.EXISTS(8) THEN
2520 pai_tab(ln_index).act_info21 := PEI(8).RFC;
2521 ELSE
2522 pai_tab(ln_index).act_info21 := NULL;
2523 END IF;
2524
2525 IF PEI.EXISTS(9) THEN
2526 pai_tab(ln_index).act_info22 := PEI(9).RFC;
2527 ELSE
2528 pai_tab(ln_index).act_info22 := NULL;
2529 END IF;
2530
2531 IF PEI.EXISTS(10) THEN
2532 pai_tab(ln_index).act_info23 := PEI(10).RFC;
2533 ELSE
2534 pai_tab(ln_index).act_info23 := NULL;
2535 END IF;
2536 --
2537
2538 pai_tab(ln_index).act_info26 :=
2539 hr_general.decode_lookup('PAY_MX_STATE_IDS', lv_jurisdiction);
2540 --
2541 -- to get the values for RATE_1991_IND and RATE_FISCAL_YEAR_IND
2542 --
2543 pai_tab(ln_index).act_info28 := '0';
2544 pai_tab(ln_index).act_info29 := '1';
2545
2546 IF lv_ann_adj = 'Y' THEN
2547
2548 OPEN c_pact_info(p_archive_action_id);
2549 FETCH c_pact_info INTO ln_business_group_id
2550 ,ld_effective_date;
2551 CLOSE c_pact_info;
2552
2553 OPEN c_ann_tax_type( ln_business_group_id
2554 ,ld_effective_date
2555 ,ln_person_id);
2556 FETCH c_ann_tax_type INTO lv_ann_tax_calc_type
2557 ,ln_anntaxadj_asgactid;
2558 CLOSE c_ann_tax_type;
2559
2560 IF lv_ann_tax_calc_type = 'BEST' THEN
2561
2562 OPEN c_input_value_id;
2563 FETCH c_input_value_id INTO ln_input_value_id;
2564 CLOSE c_input_value_id;
2565
2566 OPEN c_get_anntaxadj_article( ln_anntaxadj_asgactid
2567 ,ln_input_value_id);
2568 FETCH c_get_anntaxadj_article INTO lv_anntaxadj_article;
2569 CLOSE c_get_anntaxadj_article;
2570
2571 ELSE
2572
2573 lv_anntaxadj_article := lv_ann_tax_calc_type;
2574
2575 END IF;
2576
2577 IF lv_anntaxadj_article = 'ARTICLE141' THEN
2578
2579 pai_tab(ln_index).act_info28 := '1';
2580 pai_tab(ln_index).act_info29 := '2';
2581
2582 ELSIF lv_anntaxadj_article = 'ARTICLE177' THEN
2583
2584 pai_tab(ln_index).act_info28 := '2';
2585 pai_tab(ln_index).act_info29 := '1';
2586
2587 END IF;
2588
2589 END IF;
2590
2591 ln_step := 8;
2592 hr_utility.set_location(gv_package || lv_procedure_name, 190);
2593 pay_emp_action_arch.insert_rows_thro_api_process(
2594 p_action_context_id => p_archive_action_id
2595 ,p_action_context_type => 'AAP'
2596 ,p_assignment_id => ln_assignment_id
2597 ,p_tax_unit_id => ln_tax_unit_id
2598 ,p_curr_pymt_eff_date => p_effective_date
2599 ,p_tab_rec_data => pai_tab
2600 );
2601 pai_tab.delete;
2602
2603 ln_step := 9;
2604 hr_utility.set_location(gv_package || lv_procedure_name, 200);
2605 OPEN c_check_pay_action(g_payroll_action_id);
2606 FETCH c_check_pay_action INTO ln_pay_action_count;
2607 CLOSE c_check_pay_action;
2608
2609 ln_step := 10;
2610 IF ln_pay_action_count = 0 THEN
2611
2612 hr_utility.set_location(gv_package || lv_procedure_name, 210);
2613 IF ln_row_count = 1 THEN
2614
2615 ln_step := 11;
2616 ln_index := pai_tab.count;
2617
2618 pai_tab(ln_index).action_info_category :='MX YREND LEGAL ER DETAILS';
2619 pai_tab(ln_index).jurisdiction_code := NULL;
2620 pai_tab(ln_index).action_context_id := g_payroll_action_id;
2621 pai_tab(ln_index).act_info1 := g_fiscal_year;
2622 pai_tab(ln_index).act_info2 := g_ER_RFC;
2623 /* Bug: 9820914: Handling special chars */
2624 pai_tab(ln_index).act_info3 := replace(pay_mx_rules.strip_spl_chars (g_ER_legal_name),'/','N');
2625 pai_tab(ln_index).act_info4 := replace(pay_mx_rules.strip_spl_chars (g_ER_legal_rep_name),'/','N');
2626 pai_tab(ln_index).act_info5 := g_ER_legal_rep_RFC;
2627 pai_tab(ln_index).act_info6 := g_ER_legal_rep_CURP;
2628
2629 hr_utility.set_location(gv_package || lv_procedure_name, 220);
2630
2631 OPEN c_get_gen_hier_details(ln_business_group_id,
2632 p_effective_date);
2633 LOOP
2634
2635 ln_step := 12;
2636 hr_utility.set_location(gv_package || lv_procedure_name, 230);
2637
2638 FETCH c_get_gen_hier_details INTO ln_legal_er_id,
2639 ln_gre_id;
2640 EXIT WHEN c_get_gen_hier_details%NOTFOUND;
2641
2642 ln_index := pai_tab.count;
2643
2644 hr_utility.set_location(gv_package || lv_procedure_name, 240);
2645
2646 pai_tab(ln_index).action_info_category := 'MX GENERIC ' ||
2647 'HIERARCHY DETAILS';
2648 pai_tab(ln_index).jurisdiction_code := NULL;
2649 pai_tab(ln_index).action_context_id := g_payroll_action_id;
2650 pai_tab(ln_index).act_info1 := ln_gre_id;
2651 pai_tab(ln_index).act_info2 := ln_legal_er_id;
2652
2653 END LOOP;
2654
2655 ln_step := 13;
2656 hr_utility.set_location(gv_package || lv_procedure_name, 250);
2657
2658 pay_emp_action_arch.insert_rows_thro_api_process(
2659 p_action_context_id => g_payroll_action_id
2660 ,p_action_context_type => 'PA'
2661 ,p_assignment_id => NULL
2662 ,p_tax_unit_id => NULL
2663 ,p_curr_pymt_eff_date => p_effective_date
2664 ,p_tab_rec_data => pai_tab);
2665
2666 hr_utility.set_location(gv_package || lv_procedure_name, 260);
2667 pai_tab.delete;
2668
2669 END IF;
2670
2671 END IF;
2672 end if;
2673
2674
2675 ln_step := 14;
2676 hr_utility.set_location(gv_package || lv_procedure_name, 270);
2677
2678 end loop;
2679 Close c_emp_details;
2680 hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
2681
2682 EXCEPTION
2683 WHEN INVALID_RFC THEN
2684 hr_utility.set_message(800, 'HR_MX_INVALID_ER_RFC');
2685 hr_utility.raise_error;
2686
2687 WHEN OTHERS THEN
2688 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
2689 gv_package || lv_procedure_name;
2690
2691 hr_utility.trace(lv_error_message || '-' || SQLERRM);
2692
2693 lv_error_message :=
2694 pay_emp_action_arch.set_error_message(lv_error_message);
2695
2696 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2697 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2698 hr_utility.raise_error;
2699
2700 END archive_code;
2701
2702 BEGIN
2703 --hr_utility.trace_on (NULL, 'MX_IDC');
2704 gv_package := 'pay_mx_yrend_arch';
2705 END pay_mx_yrend_arch;