[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_SOC_SEC_ARCHIVE
Source
1 PACKAGE BODY PAY_MX_SOC_SEC_ARCHIVE AS
2 /* $Header: paymxsocsecarch.pkb 120.88.12020000.4 2012/11/17 07:57:06 jeisaac ship $ */
3 /*
4 +=======================================================================+
5 | Copyright (c) 2003 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +=======================================================================+
9 Package Header Name : pay_mx_soc_sec_archive
10 Package File Name : paymxsocsecarch.pkb
11
12 Description : Used for Social Security Archiver.
13
14 Change List:
15 ------------
16
17 Name Date Version Bug Text
18 ------------- ----------- ------- ------- ---------------------------------
19 vpandya 28-Apr-2005 115.0 Initial Version
20 vpandya 02-Jun-2005 115.1 4409303 Changed get_start_date function
21 and required procedures
22 for SUA functionality.
23 vmehta 14-Jun-2005 115.2 4431932 corrected the parameters in the
24 call to pay_mx_ff_udfs.get_idw
25 vmehta 17-Jun-2005 115.3 4435714 Initialized gn_implementation to
26 zero so that person data is
27 re-archived upon retry
28 vmehta 19-Jun-2005 115.4 Moved get_idw in the arch_hire_sep
29 procedure so that it is called for
30 the hire transaction with the hire
31 date.
32 vmehta 22-Jun-2005 115.5 Procedure get_start_date:
33 Modified to get default
34 implementation date when the
35 implementation date at LE is null
36
37 Modified arch_other_transactions
38 look for Fixed IDW based on
39 element extra info, Variable IDW
40 based on secondary classification
41 and absence/disability based on
42 element entry creator type
43 Only archive those absence
44 transactions that are marked as
45 'Report to Social Security' on
46 Further Absence type DDF.
47 vmehta 27-Jun-2005 115.6 4455393 Changed range_cursor and the
48 action creation cursor so that
49 assignments terminated or rehired
50 at a previous date are also picked
51 up
52 vmehta 27-Jun-2005 115.7 4458243 Modified the way hire and
53 separation transactions are
54 processed in
55 archive_hire_sep_transactions
56 vmehta 27-Jun-2005 115.8 Fixed GSCC error
57 vpandya 11-Jul-2005 115.11 Added logic for INFONAVIT Info.
58 vpandya 18-Jul-2005 115.12 Added logic for variable IDW.
59 Changed following procedures:
60 - range_cursor
61 - action_creation
62 - archinit
63 - arch_other_transaction
64 vpandya 21-Jul-2005 115.13 4450685 Added logic to create only one
65 record for Hire/Separation if it
66 follows by the same record.
67 vpandya 27-Jul-2005 115.14 Changed logic in range cursor and
68 action creation cursors. Archiver
69 can be run with bimonthly option
70 more than one time for the same
71 period and pick only those asg
72 that are not picked before for
73 the same period for bimonthly.
74 vpandya 28-Jul-2005 115.15 Changed logic in range cursor and
75 action_creation. Now variable
76 gv_periodic_end_date is used for
77 getting assingments for variable
78 salary change.
79 vpandya 05-Aug-2005 115.16 Ignore transaction if eff date of
80 event is 31-Dec-4712 as per VM.
81 vpandya 17-Aug-2005 115.17 4558178 Passing lv_transmitter_gre_id to
82 c_get_org_information cursor
83 instead of lv_transmitter.
84 vpandya 18-Aug-2005 115.18 4561824 Added a condition in cursor
85 c_minimum_wage_zonea to get the
86 correct minimum wage based on
87 the effective_date.
88 vpandya 31-Oct-2005 115.19 4710619 Changed range_cursor procedure.
89 Stamping 31-Dec-4712 in eff date
90 in pay_payroll_actions table
91 to view terminated and
92 rehired employees transactions.
93 sdahiya 28-Dec-2005 115.20 Support for salary change
94 transaction caused due to
95 seniority changes and IDW factor
96 table updates.
97 sdahiya 28-Jan-2006 115.21 5002283 Modified cursor c_IDW_events to
98 fetch element entry updates done
99 in "Update" mode in addition to
100 "Correction" mode.
101 sdahiya 01-Feb-2006 115.22 Modified cursor c_IDW_events to
102 use events' effective date
103 (instead of creation date) for IDW
104 calculation.
105 sdahiya 01-Feb-2006 115.23 5002283 Modified cursor c_IDW_events to
106 use events' effective date
107 (instead of creation date) to
108 date effectively identify element
109 entries and element types.
110 sdahiya 02-Feb-2006 115.24 Modified action_creation and
111 archinit to stamp appropriate date
112 in pay_recorded_requests when
113 archiver is run in retry mode.
114 sdahiya 10-Apr-2006 115.25 5146225 Modified function get_idw to call
115 pay_mx_ff_udfs.get_idw in
116 BIMONTH_REPORT mode only if
117 gv_variable_idw is 'Y'.
118 sdahiya 17-Apr-2006 115.26 5005254 Archiver should archive
119 termination date instead of a day
120 prior to the date stamped in
121 pay_process_events.
122 sdahiya 19-Apr-2006 115.27 Calculate IDW on
123 LEAST(assignment's end date,
124 process end date)
125 sdahiya 11-May-2006 115.28 5033056 Modified cursor c_person_detail
126 to select future dated hires for
127 archival.
128 vpandya 18-May-2006 115.29 5234584 Modified cursor c_person_detail
129 to select future dated hires and
130 for any UPDATE or CORRECTION in
131 employee name.
132 sdahiya 19-Jun-2006 115.30 SUA 2006 changes.
133 vpandya 22-Jun-2006 115.31 5353025 Changed c_abs_info cursor.
134 Now passing eff start and end date
135 of element entry instead of the
136 archiver.
137 sdahiya 27-Jun-2006 115.32 5354858 Modified all references involving
138 INFONAVIT element entries to
139 consider INFONAVIT transactions
140 occuring in future with respect to
141 archiver's end date.
142 sdahiya 28-Jun-2006 115.33 5355325 Removed undesired join with
143 pay_element_entry_values_f in
144 cursor c_abs_info.
145 vpandya 25-Aug-2006 115.34 Initializing gn_implementation to
146 zero when there is no payroll
147 action before the current one so
148 that if retry is run for the
149 very first SS Archiver process
150 after running SS Archiver multiple
151 times, it should archive Person
152 Information.
153 Also Changed value for rww.
154 Added logic for separation.
155 sdahiya 20-Sep-2006 115.35 5552692 'S' should be archived if
156 Reduction Table Applies input
157 value is 'Y'.
158 sdahiya 23-Sep-2006 115.36 5558838 INFONAVIT transactions effective in
159 past with respect to archiver
160 start date should be considered
161 for transactions 18, 19 and 20.
162 vmehta 26-Sep-2006 115.37 5568202 modified load_infonavit_trans to
163 call load_infonavit_info with
164 effective_start_date + 1
165 and effective_end_date + 1
166 This to ensure that we load the
167 structure with current values and
168 not old values.
169 sdahiya 24-Jan-2007 115.38 Modified the archiver so that
170 transaction date is now archived
171 in action_information2 and employer
172 SS identifier in action_information5.
173 Data upgrade will be carried out
174 using the generic upgrade mechanism.
175 Function arch_exists_without_upgrade
176 created to restrict running of
177 archiver without upgrading existing
178 archived data.
179 sdahiya 13-Feb-2007 115.39 5875096 Fixed get_person_information so that
180 it uses correct dates to fetch person
181 data for future dated events.
182 sdahiya 06-Mar-2007 115.40 5908010 Only those assignments which belong
183 to the current GRE should be
184 considered to fetch person data.
185 sdahiya 13-Mar-2007 115.41 5921945, Each event should be checked for its
186 5899264, existence under GRE for which
187 5922046 archiver is run.
188 sdahiya 14-Mar-2007 115.42 5888285 Events for EFFECTIVE_END_DATE should
189 be ignored if future asg records
190 exist.
191 vpandya 20-Mar-2007 115.43 5944540 Leapfrog ver 115.37 to resolve R12
192 Branch Line issue.
193 vpandya 20-Mar-2007 115.44 This is the same as 115.42.
194 sdahiya 21-Mar-2007 115.45 Modified seniority_changed to check
195 seniority on MAX(hire date, previous
196 archiver run date).
197 sdahiya 22-Mar-2007 115.46 5885473 Modified chk_person_rec_chng to
198 identify changes in IMSS medical
199 center (PER_INFORMATION4).
200 sdahiya 20-Apr-2007 115.47 6005922 Fixed seniority calculation for
201 future-dated hires.
202 sdahiya 22-Apr-2007 115.48 08 and 02 transactions should not be
203 archived if person-GRE relation
204 exists due to assignments other than
205 the current one.
206 sdahiya 24-Apr-2007 115.49 6013218 Employee social security number
207 should be fetched from person
208 record effective on transaction
209 date.
210 sdahiya 25-Apr-2007 115.50 6005853 Terminations due to SCL changes
211 should be checked for qualification
212 under current GRE.
213 sdahiya 25-Apr-2007 115.51 Modified cursor csr_per_gre so that
214 it checks for person-GRE association
215 for the current assignment in
216 addition to others.
217 sdahiya 26-Apr-2007 115.52 6019466 Modified cursor csr_per_gre to ensure
218 that only active assignments are
219 checked to establish person's
220 relation with GRE.
221 sdahiya 27-Apr-2007 115.53 6020160 Added NVL check in
222 chk_person_rec_chng procedure.
223 vpandya 10-May-2007 115.54 6019849 Changed cursor c_person_detail:
224 removing trailing blank if second
225 name is not entered.(rtrim)
226 sdahiya 15-May-2007 115.55 6021768 Modified arch_other_transactions
227 so that 07 is archived only if
228 person is not a new hire.
229 sdahiya 16-May-2007 115.56 07 transactions effective on a date
230 different from the hire date should
231 be archived. Modified cache_idw_date
232 for this.
233 sdahiya 18-May-2007 115.57 6060052 Run through transactions only if
234 transactions' cache is not empty.
235 nragavar 12-Jul-2007 115.58 6198089 modified to log absences correctly.
236 vpandya 16-Jul-2007 115.59 6238481 Changed: arch_other_transactions
237 Added end if for event_qualified
238 for INFONAVIT.
239 vpandya 18-Jul-2007 115.60 6198089 Changed: range_cursor and removed
240 6130744 condition for ppe.effective_date.
241 For bimonthly period, the date
242 for 07 trans would be first day
243 of next bimonthly period.
244 vpandya 20-Jul-2007 115.62 6264202 Changed: archive_data
245 Filter transaction if trn date is
246 4712/12/31. Call an API if pl/sql
247 table count is greater than 0.
248 vpandya 20-Jul-2007 115.63 Changed: archive_data
249 vpandya 20-Aug-2007 115.64 Changed: get_idw, truncating
250 effective_date while calling get_idw
251 of udfs.
252 vpandya 21-Aug-2007 115.65 6353167 Changed: get_idw, using mode to
253 REPORT now on as bimonthly IDW gets
254 when first day of next bimonth period
255 .
256 prechand 21-Feb-2008 115.66 6820541 Start date is replaced by effective_
257 start_date in the get person information
258 query for getting the latest hire date
259 sivanara 07-Mar-2008 115.67 6862116 Added cursor c_check_active_employee
260 to archive_data cursor, so that
261 archive data is only for "Employee"
262 sivanara 25-Apr-2008 115.68 6960481 Added new parameter to event_qualified
263 to filter out applicant event.
264 sivanara 17-Jun-2008 115.70 7185703 Removed fnd_date function from the cursor
265 csr_get_asg_end_date and csr_per_gre.
266 sivanara 20-Aug-2008 115.71 7341327 For the cursor csr_per_gre added condition
267 for applicant.
268 swamukhi 01-Oct-2008 115.72 6451017 For the cursor csr_per_gre added a condition
269 to check the effective_start_date.
270 vvijayku 07-Nov-2008 115.73 6451017 Added a new cursor c_get_report_term_rehire
271 to retrieve the value of the reporting option
272 and later archiving it.
273 vvijayku 10-Nov-2008 115.74 7342321 Added a new cursor c_first_sal_date which retrieves
274 the date on which the first salary was attached to
275 the assignment.
276 vvijayku 21-Nov-2008 115.75 7342321 The complete fix did not go into the earlier version
277 115.74. This version has the complete fix.
278 vvijayku 21-Nov-2008 115.76 7342321 Had to remove some compilation errors,which was arcsed
279 in by mistake.
280 sjawid 30-Jul-2009 115.77 6933682 Added extra parameters p_payroll_action_id,
281 p_execute_old_idw_code to
282 function call pay_mx_ff_udfs.get_idw.
283 Added new cursor c_salary_type in
284 procedure arch_hire_separation
285 to correct the salary_type for newhire employees.
286 sjawid 30-Jul-2009 115.78 6933682 Corrected pay_mx_ff_udfs.get_idw function call
287 vvijayku 20-Nov-2009 115.79 8988585 Corrected the to_char idw conversion to the correct
288 format.
289 sjawid 09-Nov-2009 115.80 8912736 Modified cursor c_disabilities_info, added decode function
290 to disability_control to get the correct codes as per
291 statutory requirement.
292 sjawid 19-Nov-2009 115.81 9128410 Changed the get_idw function call for the person info .
293 Passing assignment_start_date to get_idw function
294 when the person is processing first time.
295 sjawid 04-Jan-2011 115.82 9820914 Handling the Special Chars in Employee Name and GRE Name
296 using function pay_mx_rules.strip_spl_chars.
297 vvijayku 03-Sep-2011 115.84 8438074 Added code to enable the selective reporting of SS
298 transactions.
299 vvijayku 10-Oct-2011 115.85 8438074 Modified the function GET_SS_EXCLUSION_DATES and
300 added the NOCOPY hint.
301 vvijayku 11-Oct-2011 115.86 8438074 Corrected the function call GET_SS_EXCLUSION_DATES
302 and replaced the duplicate parameter
303 lv_exclude_start_date_from_cur with
304 lv_exclude_end_date_from_cur
305 vvijayku 08-Nov-2011 115.87 13357684 Added Message statements to be displayed in the
306 Payroll Processes Messages form which will help
307 in easily identifying the SS Archiver runs.
308 jeisaac 11-Jun-2012 115.88 14179408 Moved the function call to GET_SS_EXCLUSION_DATES,
309 to procedure get_transactions.
310 sjawid 11-Nov-2012 115.89 15839415 Modified Range Cursor query
311 Introduced new function validate_person_id.
312 The validations in the range cursor has been
313 moved to the function validate_person_id
314 jeisaac 17-Nov-2012 115.90 15881643 Modified Range cursor query to pick employees when variable
315 IDW option is set as No while submitting the process.
316 ============================================================================*/
317
318 --
319 -- Global Variables
320 --
321 TYPE gre_rec_type IS RECORD(
322 assignment_id NUMBER,
323 effective_start_date DATE,
324 effective_end_date DATE,
325 gre_id NUMBER);
326
327 TYPE gre_tab_type IS TABLE OF gre_rec_type INDEX BY BINARY_INTEGER;
328
329 gt_gre_cache gre_tab_type;
330 gv_package VARCHAR2(240);
331 gv_debug BOOLEAN;
332 gn_implementation NUMBER;
333 gn_person_rec_chng NUMBER;
334 gn_gre_found NUMBER;
335 gn_idw NUMBER;
336 gv_credit_no VARCHAR2(240);
337 gv_credit_start_date VARCHAR2(240);
338 gv_crdt_grant_dt VARCHAR2(240);
339 gv_discount_type VARCHAR2(240);
340 gv_discount_value VARCHAR2(240);
341 gv_variable_idw VARCHAR2(10);
342 gv_IDW_calc_method hr_organization_information.org_information10%type;
343
344 PROCEDURE hr_utility_trace (p_data IN VARCHAR2) IS
345 BEGIN
346 IF gv_debug THEN
347 hr_utility.trace (p_data);
348 END IF;
349 END;
350
351 FUNCTION event_qualified(p_person_id NUMBER,
352 p_assignment_id NUMBER,
353 p_effective_date DATE,
354 p_gre_id NUMBER) RETURN BOOLEAN IS
355 CURSOR csr_asg IS
356 SELECT assignment_id,
357 effective_start_date,
358 effective_end_date,
359 per_mx_ssaffl_archive.derive_gre_from_loc_scl(
360 location_id,
361 business_group_id,
362 soft_coding_keyflex_id,
363 effective_end_date) gre_id
364 FROM per_assignments_f
365 WHERE assignment_id = p_assignment_id;
366
367 /*Added to check for the applicant type */
368 CURSOR c_check_per_status (p_person_id IN VARCHAR2 ,
369 p_effective_date IN VARCHAR2) IS
370
371 SELECT per.current_applicant_flag
372 FROM per_all_people_f per
373 WHERE per.person_id = p_person_id
374 AND fnd_date.canonical_to_date(p_effective_date)
375 BETWEEN per.effective_start_date AND per.effective_end_date;
376
377
378 l_return BOOLEAN;
379 ln_cntr NUMBER;
380 l_proc_name varchar2(100);
381 lv_chk_emp_status VARCHAR2(1);
382
383 BEGIN
384 l_proc_name := gv_package || 'event_qualified';
385 hr_utility_trace ('Entering '||l_proc_name);
386 hr_utility_trace ('p_assignment_id = '||p_assignment_id);
387 hr_utility_trace ('p_effective_date = '||
388 fnd_date.date_to_canonical(p_effective_date));
389 hr_utility_trace ('p_gre_id = '||p_gre_id);
390
391 l_return := FALSE;
392 -- Check if assignment is cached.
393 IF gt_gre_cache.count() > 0 THEN
394 FOR ln_cntr IN gt_gre_cache.first()..gt_gre_cache.last() LOOP
395 IF p_assignment_id = gt_gre_cache(ln_cntr).assignment_id THEN
396 hr_utility_trace('Assignment '||p_assignment_id||
397 ' found in cache.');
398 l_return := TRUE;
399 EXIT;
400 END IF;
401 END LOOP;
402 END IF;
403
404 IF l_return THEN
405 -- Assignment is cached. Check if event is qualified.
406 l_return := FALSE;
407 FOR ln_cntr IN gt_gre_cache.first()..gt_gre_cache.last() LOOP
408 IF p_assignment_id = gt_gre_cache(ln_cntr).assignment_id AND
409 p_gre_id = gt_gre_cache(ln_cntr).gre_id AND
410 p_effective_date BETWEEN gt_gre_cache(ln_cntr).effective_start_date
411 AND gt_gre_cache(ln_cntr).effective_end_date
412 THEN
413 l_return := TRUE;
414 EXIT;
415 END IF;
416 END LOOP;
417 ELSE
418 -- Assignment is not cached. Load cache. Check if event is qualified.
419 hr_utility_trace('Assignment '||p_assignment_id||
420 ' not found in cache. Hitting database now.');
421 l_return := FALSE;
422 FOR csr_asg_rec IN csr_asg LOOP
423 ln_cntr := gt_gre_cache.count();
424 gt_gre_cache(ln_cntr).assignment_id := csr_asg_rec.assignment_id;
425 gt_gre_cache(ln_cntr).effective_start_date :=
426 csr_asg_rec.effective_start_date;
427 gt_gre_cache(ln_cntr).effective_end_date :=
428 csr_asg_rec.effective_end_date;
429 gt_gre_cache(ln_cntr).gre_id := csr_asg_rec.gre_id;
430
431 IF p_assignment_id = gt_gre_cache(ln_cntr).assignment_id AND
432 p_gre_id = gt_gre_cache(ln_cntr).gre_id AND
433 p_effective_date BETWEEN gt_gre_cache(ln_cntr).effective_start_date
434 AND gt_gre_cache(ln_cntr).effective_end_date
435 THEN
436 l_return := TRUE;
437 END IF;
438 END LOOP;
439 END IF;
440 hr_utility_trace('Checking for applicant record');
441 OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_effective_date));
442 FETCH c_check_per_status INTO lv_chk_emp_status;
443 CLOSE c_check_per_status;
444
445 IF lv_chk_emp_status = 'Y' THEN
446 l_return := FALSE;
447 END IF;
448
449
450 IF l_return THEN
451 hr_utility_trace ('Event qualified.');
452 ELSE
453 hr_utility_trace ('Event not qualified.');
454 END IF;
455 hr_utility_trace ('Leaving '||l_proc_name);
456 RETURN (l_return);
457 END event_qualified;
458
459 /*8438074 Begin*/
460 /*****************************************************************************
461 Name : GET_SS_EXCLUSION_DATES
462 Purpose : This fetches the exclusion dates entered in the Person Extra
463 Information Form.
464 *****************************************************************************/
465 FUNCTION GET_SS_EXCLUSION_DATES(P_PERSON_ID IN NUMBER
466 ,P_EXCLUSION_START_DATE OUT NOCOPY VARCHAR2
467 ,P_EXCLUSION_END_DATE OUT NOCOPY VARCHAR2) RETURN NUMBER IS
468 CURSOR C_SS_EXCLUDE_REPORTING IS
469 SELECT PEI_INFORMATION1,
470 PEI_INFORMATION2
471 FROM PER_PEOPLE_EXTRA_INFO
472 WHERE PERSON_ID = P_PERSON_ID
473 AND PEI_INFORMATION_CATEGORY = 'MX_EMP_EXCLUDE_SS_REPORTING';
474
475 BEGIN
476 OPEN C_SS_EXCLUDE_REPORTING;
477 FETCH C_SS_EXCLUDE_REPORTING INTO P_EXCLUSION_START_DATE,
478 P_EXCLUSION_END_DATE;
479 CLOSE C_SS_EXCLUDE_REPORTING;
480
481 RETURN 1;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 RETURN 0;
486 END GET_SS_EXCLUSION_DATES;
487
488 /*8438074 End*/
489
490
491 FUNCTION get_start_date( p_gre_id IN VARCHAR2 )
492 RETURN VARCHAR2 IS
493
494 CURSOR c_get_bus_grp_id(cp_organization_id IN NUMBER) IS
495 SELECT business_group_id
496 FROM hr_all_organization_units
497 WHERE organization_id = cp_organization_id;
498
499 CURSOR c_get_start_date(cp_tax_unit_id IN NUMBER) IS
500 SELECT pay_mx_utility.get_legi_param_val('END_DATE',LEGISLATIVE_PARAMETERS)
501 FROM pay_payroll_actions ppa
502 WHERE ppa.report_type = 'SS_ARCHIVE'
503 AND ppa.report_qualifier = 'SS_ARCHIVE'
504 AND ppa.report_category = 'RT'
505 AND pay_mx_utility.get_legi_param_val('GRE',LEGISLATIVE_PARAMETERS) =
506 cp_tax_unit_id
507 ORDER BY ppa.payroll_action_id desc ;
508
509
510 CURSOR c_get_imp_date(cp_organization_id IN NUMBER) IS
511 SELECT fnd_date.canonical_to_date(org_information6)
512 FROM hr_organization_information
513 WHERE org_information_context = 'MX_TAX_REGISTRATION'
514 AND organization_id = cp_organization_id ;
515
516 ld_report_imp_date date;
517 ld_start_date date;
518 lv_start_date varchar2(50);
519 ln_tax_unit_id NUMBER;
520 ln_legal_employer_id NUMBER;
521 ln_bus_grp_id NUMBER;
522 ln_count NUMBER;
523
524 BEGIN
525
526 hr_utility_trace('p_gre_id '||nvl( p_gre_id, -999));
527
528 pay_recorded_requests_pkg.get_recorded_date_no_ins(
529 p_process => 'MX_SOC_SEC_ARCH',
530 p_recorded_date => ld_start_date,
531 p_attribute1 => p_gre_id,
532 p_attribute2 => NULL,
533 p_attribute3 => NULL,
534 p_attribute4 => NULL,
535 p_attribute5 => NULL,
536 p_attribute6 => NULL,
537 p_attribute7 => NULL,
538 p_attribute8 => NULL,
539 p_attribute9 => NULL,
540 p_attribute10 => NULL,
541 p_attribute11 => NULL,
542 p_attribute12 => NULL,
543 p_attribute13 => NULL,
544 p_attribute14 => NULL,
545 p_attribute15 => NULL,
546 p_attribute16 => NULL,
547 p_attribute17 => NULL,
548 p_attribute18 => NULL,
549 p_attribute19 => NULL,
550 p_attribute20 => NULL);
551
552 /* Above procedure returns hr_api.g_sot if no records are found in
553 pay_recorded_requests. So, use the implementation date if date
554 fetched above is equal to hr_api.g_sot */
555
556 IF NVL( ld_start_date, hr_api.g_sot ) <> hr_api.g_sot THEN
557
558 lv_start_date := fnd_date.date_to_canonical( ld_start_date );
559
560 ELSE
561
562 IF p_gre_id IS NOT NULL THEN
563
564 -- GET LEGAL EMPLOYER ID FROM GRE ID
565
566 OPEN c_get_bus_grp_id(p_gre_id);
567 FETCH c_get_bus_grp_id INTO ln_bus_grp_id;
568 CLOSE c_get_bus_grp_id;
569
570 hr_utility_trace('ln_bus_grp_id '||ln_bus_grp_id);
571
572 SELECT count(*)
573 INTO ln_count
574 FROM fnd_sessions
575 WHERE session_id = USERENV('sessionid');
576
577 hr_utility_trace('ln_count '||ln_count);
578
579 ln_legal_employer_id :=
580 hr_mx_utility.get_legal_employer(ln_bus_grp_id, p_gre_id);
581
582 hr_utility_trace('ln_legal_employer_id '||ln_legal_employer_id);
583
584 -- get the report Implementation Date from p_legal_emp_id
585
586 OPEN c_get_imp_date(ln_legal_employer_id);
587 FETCH c_get_imp_date INTO ld_report_imp_date ;
588
589 IF c_get_imp_date%NOTFOUND OR ld_report_imp_date is NULL THEN
590
591 -- defaulting to Report Implementation Date from
592 -- mx pay legislation info table
593 ld_report_imp_date := fnd_date.canonical_to_date(
594 pay_mx_utility.get_default_imp_date) ;
595
596 END IF;
597
598 CLOSE c_get_imp_date;
599
600 hr_utility_trace('ld_report_imp_date '||ld_report_imp_date);
601
602 ln_tax_unit_id := to_number(p_gre_id) ;
603
604 OPEN c_get_start_date(ln_tax_unit_id);
605 FETCH c_get_start_date INTO lv_start_date ;
606
607 IF c_get_start_date%NOTFOUND THEN
608
609 -- assign the ld_start_date from rep imp date
610 lv_start_date := fnd_date.date_to_canonical(ld_report_imp_date);
611
612 END IF;
613
614 CLOSE c_get_start_date;
615
616 hr_utility_trace('lv_start_date '||lv_start_date);
617
618 ld_start_date := fnd_date.canonical_to_date(lv_start_date) ;
619
620 hr_utility_trace('ld_start_date '||ld_start_date);
621
622 ELSE
623
624 SELECT fnd_date.date_to_canonical(sysdate)
625 INTO lv_start_date
626 FROM DUAL;
627
628 END IF; -- p_gre_id IS NOT NULL
629
630 END IF; -- ld_start_date <> hr_api.g_sot
631
632 hr_utility_trace('lv_start_date '||lv_start_date);
633
634 RETURN lv_start_date ;
635
636 END get_start_date;
637
638 FUNCTION get_dates_for_valueset(p_date IN VARCHAR2)
639 RETURN VARCHAR2 IS
640
641 lv_dates VARCHAR2(240);
642
643 BEGIN
644
645 lv_dates := NULL;
646
647 SELECT fnd_date.date_to_displaydate( ADD_MONTHS (
648 fnd_date.canonical_to_date( p_date ), -1 ) ) || ' - ' ||
649 fnd_date.date_to_displaydate( ADD_MONTHS (
650 fnd_date.canonical_to_date( p_date ), 1 ) -1 )
651 INTO lv_dates
652 FROM dual;
653
654 RETURN lv_dates;
655
656 END get_dates_for_valueset;
657
658 /*****************************************************************************
659 Name : get_payroll_action_info
660 Purpose : This returns the Payroll Action level
661 information for Tax Filing (FLS)/Payslip Archiver.
662 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
663 p_start_date - Start date of Archiver
664 p_end_date - End date of Archiver
665 p_business_group_id - Business Group ID
666 p_gre_id - GRE ID (Organization ID of the GRE)
667 *****************************************************************************/
668 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
669 ,p_end_date OUT NOCOPY VARCHAR2
670 ,p_start_date OUT NOCOPY VARCHAR2
671 ,p_business_group_id OUT NOCOPY NUMBER
672 ,p_gre_id OUT NOCOPY NUMBER
673 )
674 IS
675 CURSOR c_payroll_Action_info (cp_payroll_action_id IN NUMBER) IS
676 SELECT business_group_id
677 ,pay_mx_utility.get_legi_param_val('START_DATE',
678 LEGISLATIVE_PARAMETERS) start_date
679 ,pay_mx_utility.get_legi_param_val('END_DATE',
680 LEGISLATIVE_PARAMETERS) end_date
681 ,pay_mx_utility.get_legi_param_val('GRE',LEGISLATIVE_PARAMETERS) GRE
682 ,pay_mx_utility.get_legi_param_val('MODE',
683 LEGISLATIVE_PARAMETERS) REPORT_MODE
684 ,pay_mx_utility.get_legi_param_val('PERIOD_ENDING_DATE',
685 LEGISLATIVE_PARAMETERS) PERIOD_ENDING_DATE
686 FROM pay_payroll_actions
687 WHERE payroll_action_id = cp_payroll_action_id;
688
689 lv_end_date VARCHAR2(50);
690 lv_start_date VARCHAR2(50);
691 ln_business_group_id NUMBER;
692 ln_gre_id NUMBER;
693 lv_mode VARCHAR2(50);
694 lv_periodic_end_date VARCHAR2(50);
695
696 lv_procedure_name VARCHAR2(100);
697 lv_error_message VARCHAR2(2000);
698 ln_step NUMBER;
699
700 BEGIN
701
702 lv_procedure_name := 'get_payroll_action_info';
703
704 hr_utility.set_location(gv_package || lv_procedure_name, 10);
705 ln_step := 1;
706
707 OPEN c_payroll_action_info(p_payroll_action_id);
708 FETCH c_payroll_action_info INTO ln_business_group_id
709 ,lv_start_date
710 ,lv_end_date
711 ,ln_gre_id
712 ,lv_mode
713 ,lv_periodic_end_date;
714 CLOSE c_payroll_action_info;
715
716 hr_utility.set_location(gv_package || lv_procedure_name, 30);
717
718 IF lv_periodic_end_date IS NOT NULL THEN
719
720 IF TRUNC(fnd_date.canonical_to_date(lv_end_date)) -
721 TRUNC(fnd_date.canonical_to_date(lv_periodic_end_date)) >= 1 THEN
722
723 gv_periodic_end_date :=
724 to_char(fnd_date.canonical_to_date(lv_periodic_end_date)
725 , 'YYYY/MM/DD') ||' 23:59:59';
726
727 ELSE
728
729 gv_periodic_end_date :=
730 to_char(fnd_date.canonical_to_date(lv_periodic_end_date)
731 , 'YYYY/MM/DD') ||' '||
732 to_char(fnd_date.canonical_to_date(lv_end_date)
733 , 'HH24:MI:SS');
734
735 END IF;
736
737 ELSE
738
739 gv_periodic_end_date := lv_end_date;
740
741 END IF;
742
743 p_end_date := lv_end_date;
744 p_start_date := lv_start_date;
745 p_business_group_id := ln_business_group_id;
746 p_gre_id := ln_gre_id;
747 gv_mode := lv_mode;
748
749 hr_utility.set_location(gv_package || lv_procedure_name, 50);
750 ln_step := 2;
751
752 IF gv_mode = 'P' THEN
753
754 gv_periodic_start_date :=
755 fnd_date.date_to_canonical(
756 TRUNC(add_months(fnd_date.canonical_to_date(
757 lv_periodic_end_date),-2)+1));
758
759 ELSE
760
761 gv_periodic_start_date := lv_start_date;
762
763 END IF;
764
765 hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
766
767 END get_payroll_action_info;
768
769 /********************************************************************
770 Name : get_rww_ind
771 Purpose : This function returns the reduced working week indicator
772 ********************************************************************/
773
774 PROCEDURE get_rww_ind(p_business_group_id IN NUMBER
775 ,p_workschedule IN VARCHAR2
776 ,p_rww_ind OUT NOCOPY VARCHAR2) IS
777
778 CURSOR c_rww ( cp_business_group_id IN NUMBER
779 ,cp_workschedule IN VARCHAR2 ) IS
780 SELECT sum(decode(to_number(puci.value),0,0,1)) total_days
781 ,sum(to_number(puci.value)) total_hours
782 FROM pay_user_column_instances_f puci,
783 pay_user_columns puc
784 WHERE puc.user_column_name = cp_workschedule
785 AND ( puc.legislation_code = 'MX' OR
786 puc.business_group_id = cp_business_group_id )
787 AND puc.user_column_id = puci.user_column_id;
788
789 ln_rww NUMBER;
790 ln_total_hours NUMBER;
791 ln_calculated_days NUMBER;
792
793 BEGIN
794
795 IF p_workschedule IS NOT NULL THEN
796
797 OPEN c_rww(p_business_group_id, p_workschedule) ;
798 FETCH c_rww INTO ln_rww
799 ,ln_total_hours;
800 CLOSE c_rww ;
801
802 /******************************************************************
803 What would be Reduced Working Week Indicator's value ?
804 Sum up number of hours from Work Schedule, Divide it by 8,
805 which gives number of days. If no of days are 6 or more then
806 the value would be zero otherwise it is no of days.
807
808 Examples:
809
810 +-------------------------------------------------------------+
811 | Work Schedule |Total Hours| Calculate Days| Value |
812 | | | Total Hours/8 | |
813 |-------------------------------------------------------------|
814 |8-0-0-0-0-0-0 | 8 | 8/8 = 1 | 1 |
815 |8-4-0-0-0-0-0 | 12 | 12/8 = 1.5 | 2 |
816 |8-2-0-0-0-0-0 | 10 | 10/8 = 1.25 | 2 |
817 |10-11-10-9-0-0-0 | 40 | 40/8 = 5 | 5 |
818 |8-8-8-8-8-8-8 | 56 | 56/8 = 7 | 0 |
819 |96-96-96-96-96-0-0| 48 | 48/8 = 6 | 0 |
820 +-------------------------------------------------------------+
821 ******************************************************************/
822
823 ln_calculated_days := CEIL ( ln_total_hours / 8 );
824
825 IF ln_calculated_days >= 6 THEN
826
827 -- If work schedule is 6 or more, it is considered as Normal Week
828 -- in this case, the reduced working week indicator should be zero
829 -- as per VM
830
831 ln_calculated_days := 0 ;
832
833 END IF;
834
835 p_rww_ind := to_char(ln_calculated_days) ;
836
837 ELSE
838
839 p_rww_ind := null ;
840
841 END IF;
842
843 END get_rww_ind;
844
845 FUNCTION get_idw( p_assignment_id IN NUMBER
846 ,p_tax_unit_id IN NUMBER
847 ,p_effective_date IN DATE
848 ,p_fixed_idw OUT NOCOPY NUMBER
849 ,p_variable_idw OUT NOCOPY NUMBER )
850 RETURN NUMBER IS
851
852 CURSOR c_minimum_wage_zonea( cp_effective_date DATE ) IS
853 SELECT fnd_number.canonical_to_number(legislation_info2)
854 FROM pay_mx_legislation_info_f
855 WHERE legislation_info_type = 'MX Minimum Wage Information'
856 AND legislation_info1 = 'MWA'
857 AND cp_effective_date BETWEEN effective_start_date
858 AND effective_end_date;
859
860 ln_min_wage NUMBER;
861 ln_idw NUMBER;
862 ln_fixed_idw NUMBER;
863 ln_variable_idw NUMBER;
864
865 lv_procedure_name VARCHAR2(100);
866 lv_error_message VARCHAR2(2000);
867 ln_step NUMBER;
868 lv_mode VARCHAR2(15);
869
870 BEGIN
871
872 lv_procedure_name := 'get_idw';
873
874 hr_utility.set_location(gv_package || lv_procedure_name, 10);
875 ln_step := 1;
876
877 ln_min_wage := 0;
878 ln_idw := 0;
879 ln_fixed_idw := 0;
880 ln_variable_idw := 0;
881
882 hr_utility.set_location(gv_package || lv_procedure_name, 20);
883
884 -- get the minimum wage for Zone A ( Mexico City )
885
886 OPEN c_minimum_wage_zonea(p_effective_date);
887 FETCH c_minimum_wage_zonea INTO ln_min_wage;
888 CLOSE c_minimum_wage_zonea;
889
890 hr_utility.set_location(gv_package || lv_procedure_name, 30);
891
892
893 BEGIN
894
895 hr_utility.set_location(gv_package || lv_procedure_name, 40);
896
897 ln_step := 2;
898
899 lv_mode := 'REPORT';
900
901 ln_idw := pay_mx_ff_udfs.get_idw( p_assignment_id => p_assignment_id
902 ,p_tax_unit_id => p_tax_unit_id
903 ,p_effective_date =>
904 TRUNC(p_effective_date)
905 ,p_payroll_action_id => NULL
906 ,p_mode => lv_mode
907 ,p_fixed_idw => ln_fixed_idw
908 ,p_variable_idw => ln_variable_idw
909 ,p_execute_old_idw_code => 'Y'
910 );
911
912 hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
913 hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
914 hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
915
916 hr_utility.set_location(gv_package || lv_procedure_name, 50);
917
918 EXCEPTION WHEN others THEN
919 hr_utility.set_location(gv_package || lv_procedure_name, 60);
920 NULL;
921
922 END;
923
924 ln_step := 3;
925
926 -- check the IDW with 25 times of zone A minimum wage
927 -- if idw is greater than 25 times of zone A minimum wage then
928 -- idw = 25 times of zone A minimum wage
929 -- else
930 -- idw = calculated one
931 -- end if
932
933 IF ln_idw > ( 25 * ln_min_wage ) THEN
934 ln_idw := 25 * ln_min_wage;
935 hr_utility.trace('SS_ARCH get_idw ln_idw > 25 * ln_min_wage');
936 hr_utility.trace('25 times of zone A minimum wage');
937 END IF;
938
939 hr_utility.set_location(gv_package || lv_procedure_name, 70);
940
941 -- round to 2 decimal and archive
942
943 p_fixed_idw := ROUND(LEAST(ln_fixed_idw, 25 * ln_min_wage), 2);
944 p_variable_idw := ROUND(LEAST(ln_variable_idw, 25 * ln_min_wage), 2);
945
946 hr_utility.trace('SS_ARCH get_idw p_fixed_idw: '|| p_fixed_idw);
947 hr_utility.trace('SS_ARCH get_idw p_variable_idw: '|| p_variable_idw);
948
949 ln_idw := round(ln_idw,2);
950
951 hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
952
953 RETURN ln_idw;
954
955 EXCEPTION
956 WHEN others THEN
957 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
958 gv_package || lv_procedure_name;
959
960 hr_utility_trace(lv_error_message || '-' || sqlerrm);
961
962 lv_error_message :=
963 pay_emp_action_arch.set_error_message(lv_error_message);
964
965 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
966 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
967 hr_utility.raise_error;
968
969 END get_idw;
970
971 PROCEDURE arch_pay_action_level_data(
972 p_payroll_action_id IN NUMBER
973 ,p_assignment_id IN NUMBER
974 ,p_effective_Date IN DATE
975 ,p_tax_unit_id IN NUMBER
976 ) IS
977
978 CURSOR c_get_org_information ( cp_organization_id IN NUMBER) IS
979 SELECT replace(org_information1,'-','') Social_Security_ID
980 ,org_information3 Transmitter_Yes_No
981 ,org_information5 WayBill_Number
982 ,org_information6 Transmitter_GRE_ID
983 FROM hr_organization_information
984 WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
985 AND organization_id = cp_organization_id ;
986
987 CURSOR c_org_name ( cp_organization_id IN NUMBER) IS
988 SELECT name
989 FROM hr_organization_units
990 WHERE organization_id = cp_organization_id;
991
992 CURSOR c_waybill_of_trnsmtr ( cp_organization_id IN NUMBER) IS
993 SELECT org_information5 WayBill_Number
994 FROM hr_organization_information
995 WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
996 AND organization_id = cp_organization_id ;
997
998 lv_soc_sec_id VARCHAR2(240);
999 lv_transmitter VARCHAR2(240);
1000 lv_waybill_no VARCHAR2(240);
1001 lv_transmitter_gre_id VARCHAR2(240);
1002
1003 lv_gre_name VARCHAR2(240);
1004 lv_transmitter_gre_name VARCHAR2(240);
1005
1006 ln_index NUMBER;
1007
1008 lv_procedure_name VARCHAR2(100);
1009 lv_error_message VARCHAR2(2000);
1010 ln_step NUMBER;
1011
1012 BEGIN
1013
1014 lv_procedure_name := 'arch_pay_action_level_data';
1015
1016 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1017 ln_step := 1;
1018
1019 OPEN c_get_org_information(p_tax_unit_id);
1020 FETCH c_get_org_information INTO lv_soc_sec_id
1021 ,lv_transmitter
1022 ,lv_waybill_no
1023 ,lv_transmitter_gre_id;
1024 CLOSE c_get_org_information;
1025
1026 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1027 ln_step := 2;
1028
1029 OPEN c_org_name(p_tax_unit_id);
1030 FETCH c_org_name INTO lv_gre_name;
1031 CLOSE c_org_name;
1032
1033 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1034 ln_step := 3;
1035
1036 IF lv_transmitter = 'Y' THEN
1037
1038 lv_transmitter_gre_id := p_tax_unit_id;
1039 lv_transmitter_gre_name := lv_gre_name;
1040
1041 ELSE
1042
1043 IF lv_transmitter IS NOT NULL THEN
1044
1045 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1046 ln_step := 4;
1047
1048 OPEN c_org_name(lv_transmitter_gre_id);
1049 FETCH c_org_name INTO lv_transmitter_gre_name;
1050 CLOSE c_org_name;
1051
1052 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1053 ln_step := 5;
1054
1055 OPEN c_waybill_of_trnsmtr(lv_transmitter_gre_id);
1056 FETCH c_waybill_of_trnsmtr INTO lv_waybill_no;
1057 CLOSE c_waybill_of_trnsmtr;
1058
1059 END IF; -- lv_transmitter
1060
1061 END IF;
1062
1063 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1064 ln_step := 6;
1065
1066 ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
1067
1068 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category
1069 := 'MX SS GRE INFORMATION';
1070 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code := NULL;
1071 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1 := lv_soc_sec_id;
1072 /*bug:9820914 : Handling Spcial Chars in GRE Name */
1073 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2
1074 := replace(pay_mx_rules.strip_spl_chars (lv_gre_name),'/','N');
1075 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info3
1076 := lv_transmitter_gre_id;
1077 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4
1078 := replace(pay_mx_rules.strip_spl_chars (lv_transmitter_gre_name),'/','N');
1079 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info5 := lv_waybill_no;
1080
1081 pay_emp_action_arch.insert_rows_thro_api_process(
1082 p_action_context_id => p_payroll_action_id
1083 ,p_action_context_type => 'PA'
1084 ,p_assignment_id => null
1085 ,p_tax_unit_id => p_tax_unit_id
1086 ,p_curr_pymt_eff_date => p_effective_date
1087 ,p_tab_rec_data => pay_mx_soc_sec_archive.lrr_act_tab
1088 );
1089
1090 pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
1091
1092 EXCEPTION
1093 WHEN others THEN
1094 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1095 gv_package || lv_procedure_name;
1096
1097 hr_utility_trace(lv_error_message || '-' || sqlerrm);
1098
1099 lv_error_message :=
1100 pay_emp_action_arch.set_error_message(lv_error_message);
1101
1102 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1103 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1104 hr_utility.raise_error;
1105
1106 END arch_pay_action_level_data;
1107
1108 PROCEDURE chk_person_rec_chng (
1109 p_per_events IN pay_interpreter_pkg.t_detailed_output_table_type
1110 ) IS
1111
1112 lv_old_value VARCHAR2(150);
1113 lv_new_value VARCHAR2(150);
1114 lv_change_values VARCHAR2(150);
1115
1116 lv_procedure_name VARCHAR2(100);
1117 lv_error_message VARCHAR2(2000);
1118 ln_step NUMBER;
1119
1120 BEGIN
1121
1122 lv_procedure_name := 'chk_person_rec_chng';
1123
1124 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1125 ln_step := 1;
1126
1127
1128 FOR i IN p_per_events.FIRST..p_per_events.LAST LOOP
1129
1130 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1131 ln_step := 2;
1132
1133 lv_change_values := p_per_events(i).change_values ;
1134
1135 lv_old_value := ltrim(rtrim(SUBSTR(lv_change_values,1,
1136 INSTR(lv_change_values,'->')-1)));
1137
1138 lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,
1139 INSTR(lv_change_values,'->')+3)));
1140
1141 hr_utility_trace('lv_change_values : '||lv_change_values);
1142 hr_utility_trace('lv_old_value : '||lv_old_value);
1143 hr_utility_trace('lv_new_value : '||lv_new_value);
1144 hr_utility_trace('column_name : '||p_per_events(i).column_name);
1145
1146 IF p_per_events(i).column_name in ( 'LAST_NAME', 'FIRST_NAME',
1147 'MIDDLE_NAMES', 'PER_INFORMATION1',
1148 'PER_INFORMATION4') -- Bug 5885473
1149 THEN
1150
1151 IF NVL(lv_old_value,-1) <> NVL(lv_new_value,-1) THEN -- Bug 6020160
1152
1153 gn_person_rec_chng := 1;
1154 EXIT;
1155
1156 END IF;
1157
1158 END IF;
1159
1160 END LOOP;
1161
1162 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1163 ln_step := 3;
1164
1165 EXCEPTION
1166 WHEN others THEN
1167 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1168 gv_package || lv_procedure_name;
1169
1170 hr_utility_trace(lv_error_message || '-' || sqlerrm);
1171
1172 lv_error_message :=
1173 pay_emp_action_arch.set_error_message(lv_error_message);
1174
1175 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1176 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1177 hr_utility.raise_error;
1178
1179 END chk_person_rec_chng;
1180
1181 PROCEDURE arch_other_transactions (
1182 p_payroll_action_id IN NUMBER
1183 ,p_asg_action_id IN NUMBER
1184 ,p_effective_date IN DATE
1185 ,p_assignment_id IN NUMBER
1186 ,p_person_id IN NUMBER
1187 ,p_chunk_number IN NUMBER
1188 ,p_start_date IN DATE
1189 ,p_end_date IN DATE
1190 ,p_business_group_id IN NUMBER
1191 ,p_gre_id IN NUMBER
1192 ,p_eff_start_date IN DATE
1193 ,p_eff_end_date IN DATE
1194 ,p_exc_start_date IN VARCHAR2
1195 ,p_exc_end_date IN VARCHAR2) IS
1196
1197 CURSOR c_IDW_events IS
1198 SELECT ppe.effective_date
1199 FROM pay_process_events ppe
1200 ,pay_event_updates peu
1201 ,pay_dated_tables pdt
1202 ,pay_element_entry_values_f peev
1203 ,pay_input_values_f piv
1204 ,pay_element_types_f pet
1205 WHERE ppe.business_group_id = p_business_group_id
1206 AND ppe.assignment_id = p_assignment_id
1207 AND ppe.change_type = 'DATE_EARNED'
1208 AND ppe.creation_date BETWEEN p_start_date
1209 AND p_end_date
1210 AND peu.event_update_id = ppe.event_update_id
1211 AND pdt.dated_table_id = peu.dated_table_id
1212 AND pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
1213 AND ppe.surrogate_key = peev.element_entry_value_id
1214 AND peev.input_value_id = piv.input_value_id
1215 AND piv.element_type_id = pet.element_type_id
1216 AND pet.element_name = 'Integrated Daily Wage'
1217 AND piv.name = 'IDW Factor Table'
1218 AND pet.legislation_code = 'MX'
1219 AND ppe.effective_date BETWEEN peev.effective_start_date
1220 AND peev.effective_end_date
1221 AND ppe.effective_date BETWEEN piv.effective_start_date
1222 AND piv.effective_end_date
1223 AND ppe.effective_date BETWEEN pet.effective_start_date
1224 AND pet.effective_end_date
1225 UNION
1226 -- Bug 5002283
1227 SELECT ppe.effective_date
1228 FROM pay_process_events ppe
1229 ,pay_event_updates peu
1230 ,pay_dated_tables pdt
1231 ,pay_element_entries_f pee
1232 ,pay_element_types_f pet
1233 WHERE ppe.business_group_id = p_business_group_id
1234 AND ppe.assignment_id = p_assignment_id
1235 AND ppe.change_type = 'DATE_EARNED'
1236 AND ppe.creation_date BETWEEN p_start_date
1237 AND p_end_date
1238 AND peu.event_update_id = ppe.event_update_id
1239 AND pdt.dated_table_id = peu.dated_table_id
1240 AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
1241 AND ppe.surrogate_key = pee.element_entry_id
1242 AND pee.element_type_id = pet.element_type_id
1243 AND pet.element_name = 'Integrated Daily Wage'
1244 AND pet.legislation_code = 'MX'
1245 AND ppe.effective_date BETWEEN pee.effective_start_date
1246 AND pee.effective_end_date
1247 AND ppe.effective_date BETWEEN pet.effective_start_date
1248 AND pet.effective_end_date;
1249
1250 CURSOR c_all_ele_entries (cp_effective_date IN DATE) IS
1251 SELECT element_entry_id
1252 FROM pay_element_entries_f
1253 WHERE assignment_id = p_assignment_id
1254 AND cp_effective_date BETWEEN effective_start_date
1255 AND effective_end_date;
1256
1257 CURSOR c_ele_entries ( cp_business_group_id IN NUMBER
1258 , cp_assignment_id IN NUMBER
1259 , cp_start_date IN DATE
1260 , cp_end_date IN DATE ) IS
1261 SELECT DISTINCT ppe.effective_date
1262 ,ppe.description change_values
1263 ,ppe.surrogate_key element_entry_id
1264 ,ppe.calculation_date
1265 ,peu.event_type
1266 FROM pay_process_events ppe
1267 ,pay_event_updates peu
1268 ,pay_dated_tables pdt
1269 WHERE ppe.business_group_id = cp_business_group_id
1270 AND ppe.assignment_id = cp_assignment_id
1271 AND ppe.change_type = 'DATE_EARNED'
1272 AND ppe.creation_date BETWEEN cp_start_date
1273 AND cp_end_date
1274 AND peu.event_update_id = ppe.event_update_id
1275 AND pdt.dated_table_id = peu.dated_table_id
1276 AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
1277 ORDER BY ppe.effective_date;
1278
1279 CURSOR c_ele_type_id ( cp_element_entry_id IN NUMBER
1280 , cp_effective_date IN DATE ) IS
1281 SELECT element_type_id
1282 ,creator_type
1283 ,effective_start_date
1284 ,effective_end_date
1285 FROM pay_element_entries_f
1286 WHERE element_entry_id = cp_element_entry_id
1287 AND cp_effective_date BETWEEN effective_start_date
1288 AND effective_end_date;
1289
1290 CURSOR c_ele_extra_info ( cp_element_type_id IN NUMBER
1291 ,cp_effective_date DATE ) IS
1292 SELECT 'MX_IDWF' eei_information1
1293 FROM pay_element_type_extra_info
1294 WHERE element_type_id = cp_element_type_id
1295 AND information_type = 'PQP_UK_RATE_TYPE'
1296 AND eei_information_category = 'PQP_UK_RATE_TYPE'
1297 AND ((eei_information1 = 'MX_BASE' AND gv_IDW_calc_method = 'B')
1298 OR (eei_information1 = 'MX_IDWF' AND gv_IDW_calc_method <> 'B'))
1299 UNION ALL
1300 SELECT 'MX_IDWV'
1301 FROM pay_element_types_f pet
1302 ,pay_element_classifications pec
1303 ,pay_sub_classification_rules_f psr
1304 WHERE pet.element_type_id = cp_element_type_id
1305 AND cp_effective_date BETWEEN pet.effective_start_date
1306 AND pet.effective_end_date
1307 AND psr.element_type_id = pet.element_type_id
1308 AND cp_effective_date BETWEEN psr.effective_start_date
1309 AND psr.effective_end_date
1310 AND pec.classification_id = psr.classification_id
1311 AND psr.legislation_code = 'MX'
1312 AND INSTR(pec.classification_name,
1313 'Eligible Compensation for IDW (Variable Basis)') > 0;
1314
1315 CURSOR c_get_org_information ( cp_organization_id IN NUMBER) IS
1316 SELECT replace(org_information1,'-','') Social_Security_ID
1317 FROM hr_organization_information
1318 WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
1319 AND organization_id = cp_organization_id ;
1320
1321 CURSOR c_person_detail (cp_person_id IN NUMBER
1322 ,cp_effective_date IN DATE) IS
1323 SELECT replace(ppf.per_information3,'-','') emp_ssnumber
1324 FROM per_all_people_f ppf
1325 WHERE ppf.person_id = cp_person_id
1326 -- Bug 6013218
1327 AND cp_effective_date BETWEEN ppf.effective_start_date AND
1328 ppf.effective_end_date;
1329 /*AND ppf.effective_start_date =
1330 ( SELECT max(ppf_in.effective_start_date)
1331 FROM per_all_people_f ppf_in
1332 WHERE ppf_in.person_id = ppf.person_id
1333 AND trunc(cp_end_date) >= ppf_in.effective_start_date
1334 AND trunc(cp_start_date) <= ppf_in.effective_end_date);*/
1335
1336 CURSOR c_abs_info ( cp_business_group_id NUMBER
1337 ,cp_assignment_id NUMBER
1338 ,cp_element_entry_id NUMBER
1339 ,cp_element_type_id NUMBER
1340 ,cp_person_id NUMBER
1341 ,cp_start_date DATE
1342 ,cp_end_date DATE ) IS
1343 SELECT paat.absence_attendance_type_id
1344 ,paa.absence_attendance_id
1345 ,paa.absence_days
1346 ,paa.date_start
1347 ,paa.date_end
1348 ,paa.abs_information_category
1349 --,paa.abs_information1 disability_type
1350 ,paa.abs_information2 disability_id
1351 FROM per_absence_attendance_types paat
1352 ,pay_input_values_f piv
1353 ,pay_element_entries_f pee
1354 --,pay_element_entry_values_f peev (Bug 5355325)
1355 ,per_absence_attendances paa
1356 WHERE paat.business_group_id = cp_business_group_id
1357 AND NVL(paat.information1, 'N')= 'Y'
1358 /*
1359 information1 for MX specifies if absence should be
1360 reported to Social Security
1361 */
1362 AND piv.input_value_id = paat.input_value_id
1363 AND piv.effective_start_date <= cp_end_date
1364 AND piv.effective_end_date >= cp_start_date
1365 AND piv.element_type_id = cp_element_type_id
1366 AND pee.element_entry_id = cp_element_entry_id
1367 AND pee.assignment_id = cp_assignment_id
1368 AND pee.element_type_id = piv.element_type_id
1369 AND pee.effective_start_date <= cp_end_date
1370 AND pee.effective_end_date >= cp_start_date
1371 --AND peev.element_entry_id = pee.element_entry_id
1372 --AND peev.effective_start_date <= cp_end_date
1373 --AND peev.effective_end_date >= cp_start_date
1374 AND paa.absence_attendance_id = pee.creator_id
1375 AND paa.person_id = cp_person_id
1376 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
1377 AND paa.date_start BETWEEN cp_start_date
1378 AND cp_end_date;
1379
1380 CURSOR c_get_infonavit ( cp_element_type_id IN NUMBER ) IS
1381 SELECT eei_information1
1382 FROM pay_element_type_extra_info
1383 WHERE element_type_id = cp_element_type_id
1384 AND information_type = 'MX_DEDUCTION_PROCESSING'
1385 AND eei_information_category = 'MX_DEDUCTION_PROCESSING'
1386 AND eei_information1 = 'INFONAVIT';
1387
1388 CURSOR c_infonavit_info ( cp_assignment_id NUMBER
1389 ,cp_element_entry_id NUMBER
1390 ,cp_element_type_id NUMBER
1391 ,cp_start_date DATE
1392 ,cp_end_date DATE ) IS
1393 SELECT piv.name
1394 ,piv.input_value_id
1395 ,pee.element_entry_id
1396 ,pee.assignment_id
1397 ,peev.screen_entry_value
1398 ,pee.effective_start_date
1399 ,pee.effective_end_date
1400 FROM pay_input_values_f piv
1401 ,pay_element_entries_f pee
1402 ,pay_element_entry_values_f peev
1403 WHERE piv.effective_start_date <= cp_end_date
1404 AND piv.effective_end_date >= cp_start_date
1405 AND piv.element_type_id = cp_element_type_id
1406 AND pee.element_entry_id = cp_element_entry_id
1407 AND pee.assignment_id = cp_assignment_id
1408 AND pee.element_type_id = piv.element_type_id
1409 AND pee.effective_start_date <= cp_end_date
1410 AND pee.effective_end_date >= cp_start_date
1411 AND peev.element_entry_id = pee.element_entry_id
1412 AND peev.effective_start_date <= cp_end_date
1413 AND peev.effective_end_date >= cp_start_date
1414 AND pee.effective_start_date = peev.effective_start_date
1415 AND pee.effective_end_date = peev.effective_end_date
1416 AND peev.input_value_id = piv.input_value_id
1417 ORDER BY piv.display_sequence;
1418
1419 CURSOR csr_infonavit_tran_16 (cp_element_type_id NUMBER,
1420 cp_start_date DATE,
1421 cp_end_date DATE) IS
1422 SELECT element_entry_id
1423 FROM pay_element_entries_f
1424 WHERE assignment_id = p_assignment_id
1425 AND element_type_id = cp_element_type_id
1426 AND effective_start_date BETWEEN cp_start_date AND cp_end_date;
1427
1428 CURSOR c_disabilities_info (cp_registration_id VARCHAR2) IS /*bug 8912736*/
1429 SELECT pdf.degree,
1430 pdf.dis_information2 subsidized_days,
1431 pdf.dis_information3 disability_type,
1432 pdf.dis_information4 consequence,
1433 DECODE(pdf.dis_information5,'6','7','7','8','8','9',pdf.dis_information5) disability_control,
1434 pdf.incident_id
1435 FROM per_disabilities_f pdf
1436 WHERE pdf.person_id = p_person_id
1437 AND pdf.registration_id = cp_registration_id
1438 AND p_effective_date BETWEEN pdf.effective_start_date
1439 AND pdf.effective_end_date;
1440
1441 CURSOR c_work_incident_info (cp_incident_id NUMBER) IS
1442 SELECT pwi.inc_information1 risk_type
1443 FROM per_work_incidents pwi
1444 WHERE pwi.person_id = p_person_id
1445 AND pwi.incident_id = cp_incident_id;
1446
1447 ld_effective_date DATE;
1448 lv_change_values VARCHAR2(240);
1449 ln_element_entry_id NUMBER;
1450 ld_calculation_date DATE;
1451 lv_event_type VARCHAR2(100);
1452
1453 ln_element_type_id NUMBER;
1454 ln_classification_id NUMBER;
1455 lv_classification_name VARCHAR2(240);
1456 ln_incident_id NUMBER;
1457
1458 fix_var_idw fixed_variable_idw;
1459 fix_var_idw_uniq fixed_variable_idw;
1460 trn transaction;
1461
1462 lv_fix_var_idw_found VARCHAR2(1);
1463 ln_count NUMBER;
1464 ln_trn_cnt NUMBER;
1465 ln_index NUMBER;
1466
1467 ln_idw NUMBER;
1468 ln_fixed_idw NUMBER;
1469 ln_variable_idw NUMBER;
1470 lv_employee_ssn VARCHAR2(100);
1471 lv_employer_ss_id VARCHAR2(100);
1472 prev_eff_date DATE;
1473
1474 ln_abs_attend_type_id NUMBER;
1475 ln_abs_attendance_id NUMBER;
1476 ln_absence_days NUMBER;
1477 ld_date_start DATE;
1478 ld_date_end DATE;
1479 lv_abs_info_category VARCHAR2(240);
1480 --lv_disability_type VARCHAR2(240);
1481 lv_disability_id VARCHAR2(240);
1482 lv_idw_type VARCHAR2(20);
1483 lv_creator_type VARCHAR2(5);
1484 ld_ee_eff_start_date DATE;
1485 ld_ee_eff_end_date DATE;
1486
1487 lv_infonavit VARCHAR2(240);
1488
1489 lv_procedure_name VARCHAR2(100);
1490 lv_error_message VARCHAR2(2000);
1491 ln_step NUMBER;
1492 ld_anniversary_date DATE;
1493 ld_hire_anniversary DATE;
1494 lb_tran_16_found BOOLEAN;
1495
1496 ln_next_element_entry_id NUMBER;
1497
1498 /*8438074 Begin*/
1499
1500 lv_exclude_start_date VARCHAR2(50);
1501 lv_exclude_end_date VARCHAR2(50);
1502 --ln_dummy_flag NUMBER;
1503 lv_do_not_report VARCHAR2(5);
1504 --lv_exclude_start_date_from_cur VARCHAR2(50);
1505 --lv_exclude_end_date_from_cur VARCHAR2(50);
1506
1507 /*8438074 End*/
1508
1509 /* This procedure loads a cache of dates, which will be later used as
1510 effective dates for IDW calculation. These dates will archived as
1511 "transaction dates" for transaction type 07. */
1512 PROCEDURE cache_IDW_date (p_idw_type VARCHAR2,
1513 p_effective_date DATE) IS
1514 ln_count NUMBER;
1515 lb_new_hire BOOLEAN;
1516 ld_effective_date DATE;
1517 ln_fix_idw NUMBER;
1518 ln_var_idw NUMBER;
1519
1520 /*The Cursor c_first_sal_date gets the date on which the first
1521 salary was attached to the employee. This is done to prevent
1522 the reporting of the new salary as a salary change if it is
1523 attached to the assignments on a date after the hire date of
1524 the employee. Refer Bug 7342321 */
1525
1526 CURSOR c_first_sal_date IS
1527
1528 SELECT max(ppe.effective_date)
1529 FROM pay_process_events ppe ,
1530 pay_event_updates peu ,
1531 pay_dated_tables pdt
1532 WHERE ppe.business_group_id = p_business_group_id
1533 AND ppe.assignment_id = p_assignment_id
1534 AND ppe.change_type = 'DATE_EARNED'
1535 AND peu.event_update_id = ppe.event_update_id
1536 AND pdt.dated_table_id = peu.dated_table_id
1537 AND ((pdt.table_name = 'PAY_ELEMENT_ENTRIES_F')
1538 OR (pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'))
1539 AND peu.event_type = 'INSERT';
1540 BEGIN
1541 IF event_qualified (p_person_id,
1542 p_assignment_id,
1543 p_effective_date,
1544 p_gre_id) THEN
1545 -- Archive a 07 only if current person is not a new hire
1546 -- (Bug 6021768)
1547 lb_new_hire := FALSE;
1548 hr_utility_trace('Checking for hire ');
1549 hr_utility_trace('Archived SS transaxtions ' ||pay_mx_soc_sec_archive.lrr_act_tab.COUNT());
1550 hr_utility_trace('p_effective_date ' || p_effective_date);
1551
1552 OPEN c_first_sal_date;
1553 FETCH c_first_sal_date into ld_effective_date;
1554 CLOSE c_first_sal_date;
1555
1556 IF pay_mx_soc_sec_archive.lrr_act_tab.COUNT() > 0 THEN --Bug 6060052
1557 FOR cntr IN pay_mx_soc_sec_archive.lrr_act_tab.FIRST()..
1558 pay_mx_soc_sec_archive.lrr_act_tab.LAST() LOOP
1559 hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category ' ||
1560 pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category);
1561 hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) ' ||
1562 pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2);
1563 hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 ' ||
1564 pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4);
1565
1566 IF pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category
1567 = 'MX SS TRANSACTIONS' AND
1568 fnd_date.canonical_to_date(
1569 pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) =
1570 p_effective_date AND
1571 pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 = '08'
1572 THEN
1573 lb_new_hire := TRUE;
1574 EXIT;
1575 ELSIF
1576 p_effective_date = ld_effective_date AND --BUG 7342321
1577 pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category
1578 = 'MX SS TRANSACTIONS' AND
1579 pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 = '08'
1580 THEN
1581 lb_new_hire := TRUE;
1582 hr_utility_trace('Going to run the get_idw to get the correct IDW for the first salary.');
1583 ln_idw := get_idw( p_assignment_id => p_assignment_id
1584 ,p_tax_unit_id => p_gre_id
1585 ,p_effective_date => ld_effective_date
1586 ,p_fixed_idw => ln_fix_idw
1587 ,p_variable_idw => ln_var_idw );
1588 pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info8:= ln_idw;
1589 hr_utility_trace('Exiting the ELSIF part to prevent 07 transaction');
1590 EXIT;
1591 END IF;
1592 END LOOP;
1593 END IF;
1594 IF NOT lb_new_hire THEN
1595 IF p_idw_type = 'MX_IDWF' THEN
1596 ln_count := fix_var_idw.COUNT;
1597 fix_var_idw(ln_count).idw_type := 'FIXED';
1598 fix_var_idw(ln_count).idw_date := p_effective_date;
1599 ELSIF p_idw_type = 'MX_IDWV' THEN
1600 ln_count := fix_var_idw.COUNT;
1601 fix_var_idw(ln_count).idw_type := 'VARIABLE';
1602 fix_var_idw(ln_count).idw_date := p_effective_date;
1603 END IF;
1604 ELSE
1605 hr_utility_trace('Current person is a new hire. 07 effective '||
1606 'on '||fnd_date.date_to_canonical(p_effective_date)
1607 ||' will not be archived.');
1608 END IF;
1609 END IF;
1610 END cache_IDW_date;
1611
1612
1613 /* This procedure goes through all element entries of current assignemnt
1614 and prepares transaction dates for salary change transaction. */
1615 PROCEDURE parse_all_ele_entries (p_effective_date DATE) IS
1616 ln_element_entry_id NUMBER;
1617 ln_element_type_id NUMBER;
1618 lv_idw_type VARCHAR2(40);
1619 lv_creator_type pay_element_types_f.creator_type%type;
1620 BEGIN
1621 OPEN c_all_ele_entries (p_effective_date);
1622 LOOP
1623 FETCH c_all_ele_entries INTO ln_element_entry_id;
1624 EXIT WHEN c_all_ele_entries%NOTFOUND;
1625 hr_utility_trace('Element Entry ID = '||
1626 ln_element_entry_id);
1627
1628 OPEN c_ele_type_id (ln_element_entry_id,
1629 p_effective_date);
1630 FETCH c_ele_type_id INTO ln_element_type_id,
1631 lv_creator_type,
1632 ld_ee_eff_start_date,
1633 ld_ee_eff_end_date;
1634 CLOSE c_ele_type_id;
1635
1636 lv_idw_type := NULL;
1637 OPEN c_ele_extra_info (ln_element_type_id,
1638 p_effective_date);
1639 FETCH c_ele_extra_info INTO lv_idw_type;
1640 CLOSE c_ele_extra_info;
1641
1642 IF lv_idw_type IS NOT NULL THEN
1643 hr_utility_trace('element entry id '||
1644 ln_element_entry_id||' has following IDW info: -');
1645 hr_utility_trace('IDW type = '||lv_idw_type);
1646 hr_utility_trace('IDW effective date = '||
1647 fnd_date.date_to_canonical(p_effective_date));
1648 ELSE
1649 hr_utility_trace('element entry id '||
1650 ln_element_entry_id||' has no IDW information.');
1651 END IF;
1652 cache_IDW_date (lv_idw_type,
1653 p_effective_date);
1654
1655 /* Stop processing element entries as soon as we run into
1656 first element entry with IDW information. This is
1657 because a change in IDW table or seniority is applicable
1658 to all element entries. Though we remove duplicate dates
1659 from cache later down the line, this action should save
1660 some processing. */
1661 EXIT WHEN lv_idw_type IS NOT NULL;
1662 END LOOP;
1663 CLOSE c_all_ele_entries;
1664 END parse_all_ele_entries;
1665
1666
1667 PROCEDURE load_infonavit_info (p_assignment_id NUMBER
1668 ,p_element_entry_id NUMBER
1669 ,p_element_type_id NUMBER
1670 ,p_start_date DATE
1671 ,p_end_date DATE
1672 ,p_index NUMBER) IS
1673
1674 CURSOR c_infonavit_info IS
1675 SELECT piv.name
1676 ,piv.input_value_id
1677 ,pee.element_entry_id
1678 ,pee.assignment_id
1679 ,peev.screen_entry_value
1680 ,pee.effective_start_date
1681 ,pee.effective_end_date
1682 FROM pay_input_values_f piv
1683 ,pay_element_entries_f pee
1684 ,pay_element_entry_values_f peev
1685 WHERE piv.effective_start_date <= p_end_date
1686 AND piv.effective_end_date >= p_start_date
1687 AND piv.element_type_id = p_element_type_id
1688 AND pee.element_entry_id = p_element_entry_id
1689 AND pee.assignment_id = p_assignment_id
1690 AND pee.element_type_id = piv.element_type_id
1691 AND pee.effective_start_date <= p_end_date
1692 AND pee.effective_end_date >= p_start_date
1693 AND peev.element_entry_id = pee.element_entry_id
1694 AND peev.effective_start_date <= p_end_date
1695 AND peev.effective_end_date >= p_start_date
1696 AND pee.effective_start_date = peev.effective_start_date
1697 AND pee.effective_end_date = peev.effective_end_date
1698 AND peev.input_value_id = piv.input_value_id
1699 ORDER BY piv.display_sequence;
1700 BEGIN
1701
1702 FOR c_infonavit_info_rec IN c_infonavit_info LOOP
1703 IF c_infonavit_info_rec.name = 'Credit Number' THEN
1704 trn(p_index).credit_number :=
1705 c_infonavit_info_rec.screen_entry_value;
1706 ELSIF c_infonavit_info_rec.name = 'Discount Type' THEN
1707 trn(p_index).discount_type :=
1708 c_infonavit_info_rec.screen_entry_value;
1709 ELSIF c_infonavit_info_rec.name = 'Discount Value' THEN
1710 trn(p_index).discount_value :=
1711 c_infonavit_info_rec.screen_entry_value;
1712 ELSIF c_infonavit_info_rec.name = 'Reduction Table Applies' THEN
1713 -- Bug 5552692
1714 SELECT DECODE (c_infonavit_info_rec.screen_entry_value,
1715 'Y', 'S',
1716 'N') INTO trn(p_index).redxn_table_applies
1717 FROM DUAL;
1718 END IF;
1719 END LOOP;
1720 END load_infonavit_info;
1721
1722 /* This procedure loads transaction cache with data corresponding to
1723 INFONAVIT transactions 18, 19 and 20. */
1724 PROCEDURE load_infonavit_trans (
1725 p_element_entry_id NUMBER,
1726 p_element_type_id NUMBER,
1727 p_iv_name pay_input_values_f.name%type,
1728 p_effective_start_date DATE,
1729 p_effective_end_date DATE,
1730 p_screen_entry_value pay_element_entry_values_f.screen_entry_value%type,
1731 p_tran_type VARCHAR2) IS
1732
1733 CURSOR c_infonavit_info ( cp_assignment_id NUMBER
1734 ,cp_element_entry_id NUMBER
1735 ,cp_element_type_id NUMBER
1736 ,cp_start_date DATE
1737 ,cp_end_date DATE ) IS
1738 SELECT piv.name
1739 ,piv.input_value_id
1740 ,pee.element_entry_id
1741 ,pee.assignment_id
1742 ,peev.screen_entry_value
1743 ,pee.effective_start_date
1744 ,pee.effective_end_date
1745 FROM pay_input_values_f piv
1746 ,pay_element_entries_f pee
1747 ,pay_element_entry_values_f peev
1748 WHERE piv.effective_start_date <= cp_end_date
1749 AND piv.effective_end_date >= cp_start_date
1750 AND piv.element_type_id = cp_element_type_id
1751 AND pee.element_entry_id = cp_element_entry_id
1752 AND pee.assignment_id = cp_assignment_id
1753 AND pee.element_type_id = piv.element_type_id
1754 AND pee.effective_start_date <= cp_end_date
1755 AND pee.effective_end_date >= cp_start_date
1756 AND peev.element_entry_id = pee.element_entry_id
1757 AND peev.effective_start_date <= cp_end_date
1758 AND peev.effective_end_date >= cp_start_date
1759 AND pee.effective_start_date = peev.effective_start_date
1760 AND pee.effective_end_date = peev.effective_end_date
1761 AND peev.input_value_id = piv.input_value_id
1762 ORDER BY piv.display_sequence;
1763
1764 ln_trn_cnt NUMBER;
1765 lb_tran_found BOOLEAN;
1766 BEGIN
1767 ln_trn_cnt := trn.count();
1768 lb_tran_found := FALSE;
1769 FOR c_infonavit_info_rec IN c_infonavit_info ( p_assignment_id
1770 ,p_element_entry_id
1771 ,p_element_type_id
1772 ,p_effective_start_date
1773 ,p_effective_end_date)
1774 LOOP
1775 IF p_iv_name = c_infonavit_info_rec.name AND
1776 p_screen_entry_value <> c_infonavit_info_rec.screen_entry_value
1777 THEN
1778 trn(ln_trn_cnt).type := p_tran_type;
1779 trn(ln_trn_cnt).date := fnd_date.date_to_canonical(
1780 p_effective_end_date + 1);
1781 hr_utility_trace('Transaction '|| p_tran_type ||' found.');
1782 lb_tran_found := TRUE;
1783 EXIT;
1784 END IF;
1785 END LOOP;
1786
1787 IF lb_tran_found THEN
1788 load_infonavit_info (p_assignment_id
1789 ,p_element_entry_id
1790 ,p_element_type_id
1791 ,p_effective_start_date + 1 --bug 5568202
1792 ,p_effective_end_date + 1 --bug 5568202
1793 ,ln_trn_cnt);
1794 /*FOR c_infonavit_info_rec IN c_infonavit_info ( p_assignment_id
1795 ,p_element_entry_id
1796 ,p_element_type_id
1797 ,p_effective_start_date
1798 ,p_effective_end_date)
1799 LOOP
1800 IF c_infonavit_info_rec.name = 'Credit Number' THEN
1801 trn(ln_trn_cnt).credit_number :=
1802 c_infonavit_info_rec.screen_entry_value;
1803 ELSIF c_infonavit_info_rec.name = 'Discount Type' THEN
1804 trn(ln_trn_cnt).discount_type :=
1805 c_infonavit_info_rec.screen_entry_value;
1806 ELSIF c_infonavit_info_rec.name = 'Discount Value' THEN
1807 trn(ln_trn_cnt).discount_value :=
1808 c_infonavit_info_rec.screen_entry_value;
1809 END IF;
1810 END LOOP;*/
1811 END IF;
1812 END load_infonavit_trans;
1813
1814 BEGIN -- Main
1815
1816 lv_procedure_name := 'arch_other_transactions';
1817
1818 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1819 ln_step := 1;
1820
1821 fix_var_idw.DELETE;
1822 fix_var_idw_uniq.DELETE;
1823 prev_eff_date := fnd_date.canonical_to_date('0001/01/01');
1824
1825 hr_utility.set_location(gv_package || lv_procedure_name, 910);
1826 ln_step := 91;
1827
1828 OPEN c_get_org_information(p_gre_id);
1829 FETCH c_get_org_information INTO lv_employer_ss_id;
1830 CLOSE c_get_org_information;
1831
1832 hr_utility.set_location(gv_package || lv_procedure_name, 920);
1833 ln_step := 92;
1834
1835 hr_utility.set_location(gv_package || lv_procedure_name, 930);
1836 ln_step := 93;
1837
1838 IF gv_IDW_calc_method = 'B' THEN
1839 /*-- IDW factor table support --*/
1840 OPEN c_IDW_events;
1841 LOOP
1842 FETCH c_IDW_events INTO ld_effective_date;
1843 EXIT WHEN c_IDW_events%NOTFOUND;
1844 hr_utility_trace('Timestamp of IDW table update event = '||
1845 fnd_date.date_to_canonical(ld_effective_date));
1846 parse_all_ele_entries (ld_effective_date);
1847 END LOOP;
1848 CLOSE c_IDW_events;
1849
1850 /*-- Support for change in seniority --*/
1851 IF pay_mx_soc_sec_archive.seniority_changed (p_person_id,
1852 p_end_date,
1853 p_start_date) = 'Y' THEN
1854 hr_utility_trace ('Seniority of person '||p_person_id||
1855 ' has changed since last archiver run. IDW will'||
1856 ' be recomputed for this person.');
1857 -- Get hire anniversary date
1858 ld_hire_anniversary := hr_mx_utility.get_hire_anniversary(
1859 p_person_id,
1860 p_end_date);
1861 hr_utility_trace ('Hire anniversary date of person '||p_person_id||
1862 ' = '||fnd_date.date_to_canonical(ld_hire_anniversary));
1863
1864 -- Calculate anniversary date in current year
1865 SELECT ADD_MONTHS (TRUNC (p_end_date, 'Y'),
1866 MONTHS_BETWEEN (ld_hire_anniversary,
1867 TRUNC (ld_hire_anniversary, 'Y'))
1868 ) +
1869 (ld_hire_anniversary - TRUNC (ld_hire_anniversary, 'MM'))
1870 INTO ld_anniversary_date
1871 FROM dual;
1872
1873 hr_utility_trace ('Anniversary date of person '||p_person_id||
1874 ' in the year of archiver run = '||
1875 fnd_date.date_to_canonical(ld_anniversary_date));
1876
1877 parse_all_ele_entries (ld_anniversary_date);
1878 END IF; -- seniority_changed?
1879 END IF; -- gv_IDW_calc_method = 'B'
1880
1881 OPEN c_ele_entries( p_business_group_id
1882 ,p_assignment_id
1883 ,p_start_date
1884 ,p_end_date );
1885
1886 LOOP
1887
1888 FETCH c_ele_entries INTO ld_effective_date
1889 ,lv_change_values
1890 ,ln_element_entry_id
1891 ,ld_calculation_date
1892 ,lv_event_type;
1893
1894 EXIT WHEN c_ele_entries%NOTFOUND;
1895 /* Adding event qualification mechanism so that only those events that
1896 belong to current GRE are picked for archival. (Bug 5921945)*/
1897
1898 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1899 ln_step := 2;
1900
1901 hr_utility_trace('ld_effective_date :' || ld_effective_date);
1902 hr_utility_trace('lv_change_values :' || lv_change_values);
1903 hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1904 hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1905 hr_utility_trace('lv_event_type :' || lv_event_type);
1906
1907 OPEN c_ele_type_id ( ln_element_entry_id
1908 ,ld_effective_date );
1909 FETCH c_ele_type_id INTO ln_element_type_id
1910 ,lv_creator_type
1911 ,ld_ee_eff_start_date
1912 ,ld_ee_eff_end_date;
1913 CLOSE c_ele_type_id;
1914
1915 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1916 ln_step := 3;
1917
1918 hr_utility_trace('ln_element_type_id :' || ln_element_type_id);
1919
1920 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1921 ln_step := 4;
1922
1923 lv_idw_type := NULL;
1924 OPEN c_ele_extra_info (ln_element_type_id, ld_effective_date);
1925
1926 FETCH c_ele_extra_info
1927 INTO lv_idw_type;
1928
1929 CLOSE c_ele_extra_info;
1930
1931 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1932 ln_step := 5;
1933
1934 hr_utility_trace('IDW_TYPE :' || lv_idw_type);
1935
1936 IF lv_idw_type IS NOT NULL THEN
1937 cache_IDW_date (lv_idw_type,
1938 ld_effective_date);
1939 END IF;
1940
1941 IF lv_creator_type = 'A' THEN
1942
1943 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1944 ln_step := 6;
1945
1946 ln_abs_attend_type_id := 0;
1947
1948 OPEN c_abs_info ( p_business_group_id
1949 ,p_assignment_id
1950 ,ln_element_entry_id
1951 ,ln_element_type_id
1952 ,p_person_id
1953 ,ld_ee_eff_start_date
1954 ,ld_ee_eff_end_date );
1955 --,p_start_date
1956 --,p_end_date );
1957
1958 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1959 ln_step := 7;
1960
1961 LOOP
1962
1963 FETCH c_abs_info INTO ln_abs_attend_type_id
1964 ,ln_abs_attendance_id
1965 ,ln_absence_days
1966 ,ld_date_start
1967 ,ld_date_end
1968 ,lv_abs_info_category
1969 --,lv_disability_type
1970 ,lv_disability_id;
1971
1972 EXIT WHEN c_abs_info%NOTFOUND;
1973
1974 hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1975 hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1976 hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977 hr_utility_trace('ld_date_start : '|| ld_date_start);
1978 hr_utility_trace('ld_date_end : '|| ld_date_end);
1979 hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1980 --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1981 hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1982
1983 IF event_qualified (p_person_id,
1984 p_assignment_id,
1985 ld_date_start,
1986 p_gre_id) THEN
1987
1988 ln_trn_cnt := trn.COUNT;
1989
1990 IF lv_disability_id IS NOT NULL THEN
1991
1992 trn(ln_trn_cnt).type := '12';
1993 --trn(ln_trn_cnt).dis_insurance_type := lv_disability_type;
1994 trn(ln_trn_cnt).dis_num := lv_disability_id;
1995 trn(ln_trn_cnt).abs_start_date := fnd_date.date_to_canonical(
1996 ld_date_start);
1997 trn(ln_trn_cnt).abs_end_date := fnd_date.date_to_canonical(
1998 ld_date_end);
1999
2000 OPEN c_disabilities_info (lv_disability_id);
2001 FETCH c_disabilities_info
2002 INTO trn(ln_trn_cnt).disability_percent,
2003 trn(ln_trn_cnt).subsidized_days,
2004 trn(ln_trn_cnt).dis_insurance_type,
2005 trn(ln_trn_cnt).consequence,
2006 trn(ln_trn_cnt).disability_control,
2007 ln_incident_id;
2008 CLOSE c_disabilities_info;
2009
2010 OPEN c_work_incident_info (ln_incident_id);
2011 FETCH c_work_incident_info INTO trn(ln_trn_cnt).risk_type;
2012 CLOSE c_work_incident_info;
2013
2014 ELSE
2015
2016 trn(ln_trn_cnt).type := '11';
2017 trn(ln_trn_cnt).dis_num := NULL;
2018
2019 END IF;
2020
2021 trn(ln_trn_cnt).date := fnd_date.date_to_canonical(ld_date_start);
2022
2023 trn(ln_trn_cnt).abs_days := ln_absence_days;
2024 trn(ln_trn_cnt).idw_vol_contr := NULL;
2025 trn(ln_trn_cnt).salary_type := NULL;
2026
2027 END IF; -- event_qualified for Absence
2028
2029 END LOOP;
2030
2031 CLOSE c_abs_info;
2032
2033 END IF; -- lv_creator_type = 'A'
2034
2035
2036 IF event_qualified (p_person_id,
2037 p_assignment_id,
2038 ld_effective_date,
2039 p_gre_id) THEN
2040
2041 lv_infonavit := NULL;
2042
2043 OPEN c_get_infonavit(ln_element_type_id);
2044 FETCH c_get_infonavit INTO lv_infonavit;
2045 CLOSE c_get_infonavit;
2046
2047 hr_utility_trace('lv_infonavit : '|| nvl(lv_infonavit, 'NULL'));
2048
2049 IF lv_infonavit = 'INFONAVIT' THEN
2050
2051 lb_tran_16_found := FALSE;
2052 FOR infonavit IN c_infonavit_info ( p_assignment_id
2053 ,ln_element_entry_id
2054 ,ln_element_type_id
2055 ,ld_ee_eff_start_date
2056 ,ld_ee_eff_end_date)
2057 LOOP
2058
2059 hr_utility_trace('----------------------');
2060 hr_utility_trace('name : '|| infonavit.name);
2061 hr_utility_trace('screen_entry_value : '||
2062 infonavit.screen_entry_value);
2063 hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2064 hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2065 hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
2066 hr_utility_trace('effective_start_date : '||
2067 infonavit.effective_start_date);
2068 hr_utility_trace('effective_end_date : '||
2069 infonavit.effective_end_date);
2070
2071 IF infonavit.name = 'Credit Number' THEN
2072
2073 gv_credit_no := infonavit.screen_entry_value;
2074 -- transaction 20
2075 load_infonavit_trans (ln_element_entry_id,
2076 ln_element_type_id,
2077 infonavit.name,
2078 ld_ee_eff_start_date - 1,
2079 infonavit.effective_start_date - 1,
2080 infonavit.screen_entry_value,
2081 '20');
2082
2083 ELSIF infonavit.name IN ('Credit Start Date',
2084 'Discount Start Date') THEN
2085
2086 gv_credit_start_date := infonavit.screen_entry_value;
2087
2088 ELSIF infonavit.name = 'Credit Grant Date' THEN
2089
2090 gv_crdt_grant_dt := infonavit.screen_entry_value;
2091
2092 ELSIF infonavit.name = 'Discount Type' THEN
2093
2094 gv_discount_type := infonavit.screen_entry_value;
2095 -- transaction 18
2096 load_infonavit_trans (ln_element_entry_id,
2097 ln_element_type_id,
2098 infonavit.name,
2099 ld_ee_eff_start_date - 1,
2100 infonavit.effective_start_date - 1,
2101 infonavit.screen_entry_value,
2102 '18');
2103
2104 ELSIF infonavit.name = 'Discount Value' THEN
2105
2106 gv_discount_value := infonavit.screen_entry_value;
2107 -- transaction 19
2108 load_infonavit_trans (ln_element_entry_id,
2109 ln_element_type_id,
2110 infonavit.name,
2111 ld_ee_eff_start_date - 1,
2112 infonavit.effective_start_date - 1,
2113 infonavit.screen_entry_value,
2114 '19');
2115
2116 /*-- Identify INFONAVIT transaction - Suspension of Discount --*/
2117 ELSIF infonavit.effective_end_date < hr_general.end_of_time AND
2118 NOT lb_tran_16_found THEN
2119
2120 ln_next_element_entry_id := -1;
2121
2122 OPEN csr_infonavit_tran_16 (ln_element_type_id,
2123 infonavit.effective_end_date + 1,
2124 p_end_date);
2125 FETCH csr_infonavit_tran_16 INTO ln_next_element_entry_id;
2126 CLOSE csr_infonavit_tran_16;
2127
2128 IF ln_next_element_entry_id = -1 THEN
2129
2130 ln_trn_cnt := trn.count();
2131 trn (ln_trn_cnt).type := '16';
2132 trn (ln_trn_cnt).date := fnd_date.date_to_canonical (
2133 infonavit.effective_end_date);
2134
2135 load_infonavit_info (p_assignment_id
2136 ,ln_element_entry_id
2137 ,ln_element_type_id
2138 ,ld_ee_eff_start_date
2139 ,ld_ee_eff_end_date
2140 ,ln_trn_cnt);
2141 lb_tran_16_found := TRUE;
2142 hr_utility_trace ('Transaction 16 found.');
2143
2144 END IF;
2145
2146 ELSIF infonavit.name = 'Transaction Type' AND
2147 infonavit.screen_entry_value IS NOT NULL AND
2148 NOT lb_tran_16_found THEN
2149
2150 ln_trn_cnt := trn.count();
2151
2152 IF infonavit.screen_entry_value = 'CREDIT_BEGIN' THEN
2153
2154 trn(ln_trn_cnt).type := '15';
2155 hr_utility_trace ('Transaction 15 found.');
2156
2157 ELSIF infonavit.screen_entry_value = 'DISC_RESUME' THEN
2158
2159 trn(ln_trn_cnt).type := '17';
2160 hr_utility_trace ('Transaction 17 found.');
2161
2162 END IF;
2163
2164 -- gv_credit_start_date is already in canonical date format.
2165
2166 trn(ln_trn_cnt).date := nvl(gv_credit_start_date,
2167 fnd_date.date_to_canonical(
2168 infonavit.effective_start_date));
2169
2170 load_infonavit_info (p_assignment_id
2171 ,ln_element_entry_id
2172 ,ln_element_type_id
2173 ,ld_ee_eff_start_date
2174 ,ld_ee_eff_end_date
2175 ,ln_trn_cnt);
2176
2177 END IF;
2178
2179 END LOOP; -- infonavit
2180
2181 IF gv_credit_no IS NOT NULL THEN
2182 gn_person_rec_chng := 1;
2183 END IF;
2184
2185 END IF; -- lv_infonavit = 'INFONAVIT'
2186
2187 END IF; -- event_qualified for 'INFONAVIT'
2188
2189 END LOOP; -- c_ele_entries
2190
2191 CLOSE c_ele_entries;
2192
2193 IF fix_var_idw.COUNT > 0 THEN
2194
2195 fix_var_idw_uniq.DELETE;
2196
2197 FOR i in fix_var_idw.FIRST..fix_var_idw.LAST
2198 LOOP
2199
2200 lv_fix_var_idw_found := 'N';
2201 hr_utility_trace('fix_var_idw(i).idw_type '||i||': '||
2202 fix_var_idw(i).idw_type );
2203 hr_utility_trace('fix_var_idw(i).idw_date '||i||': '||
2204 fix_var_idw(i).idw_date );
2205 hr_utility_trace('---------------------------------------');
2206
2207 IF fix_var_idw_uniq.COUNT > 0 THEN
2208
2209 FOR j in fix_var_idw_uniq.FIRST..fix_var_idw_uniq.LAST
2210 LOOP
2211 hr_utility_trace('fix_var_idw_uniq(j).idw_type '||j||': '||
2212 fix_var_idw_uniq(j).idw_type );
2213 hr_utility_trace('fix_var_idw_uniq(j).idw_date '||j||': '||
2214 fix_var_idw_uniq(j).idw_date );
2215
2216 IF fix_var_idw(i).idw_type = fix_var_idw_uniq(j).idw_type AND
2217 fix_var_idw(i).idw_date = fix_var_idw_uniq(j).idw_date THEN
2218
2219 lv_fix_var_idw_found := 'Y';
2220 hr_utility_trace('FOUND');
2221
2222 END IF;
2223
2224 END LOOP;
2225
2226 END IF;
2227
2228 hr_utility_trace('---------------------------------------');
2229
2230 IF lv_fix_var_idw_found = 'N' AND
2231 fix_var_idw(i).idw_type = 'FIXED' THEN
2232
2233 hr_utility_trace('NOT FOUND');
2234 hr_utility_trace(' ');
2235 ln_count := fix_var_idw_uniq.COUNT;
2236 fix_var_idw_uniq(ln_count).idw_type := fix_var_idw(i).idw_type;
2237 fix_var_idw_uniq(ln_count).idw_date := fix_var_idw(i).idw_date;
2238
2239 END IF;
2240
2241 END LOOP;
2242
2243 fix_var_idw.DELETE;
2244 fix_var_idw := fix_var_idw_uniq;
2245 fix_var_idw_uniq.DELETE;
2246
2247 FOR i IN fix_var_idw.FIRST..fix_var_idw.LAST
2248 LOOP
2249
2250 IF prev_eff_date <> fix_var_idw(i).idw_date THEN
2251
2252 ln_idw := 0;
2253 ln_fixed_idw := 0;
2254 ln_variable_idw := 0;
2255
2256 ln_idw := get_idw( p_assignment_id => p_assignment_id
2257 ,p_tax_unit_id => p_gre_id
2258 ,p_effective_date => fix_var_idw(i).idw_date
2259 ,p_fixed_idw => ln_fixed_idw
2260 ,p_variable_idw => ln_variable_idw );
2261
2262 hr_utility.trace('SS_ARCH other TRN ln_idw: '||ln_idw);
2263 hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2264 hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2265 ln_variable_idw);
2266
2267 hr_utility.set_location(gv_package || lv_procedure_name, 2030);
2268 ln_step := 203;
2269
2270 END IF;
2271
2272 prev_eff_date := fix_var_idw(i).idw_date;
2273
2274 ln_trn_cnt := trn.COUNT;
2275
2276 trn(ln_trn_cnt).type := '07';
2277 trn(ln_trn_cnt).date :=
2278 fnd_date.date_to_canonical(fix_var_idw(i).idw_date);
2279 trn(ln_trn_cnt).dis_num := NULL;
2280 trn(ln_trn_cnt).abs_days := NULL;
2281
2282 IF fix_var_idw(i).idw_type = 'FIXED' THEN
2283 trn(ln_trn_cnt).idw_vol_contr := ln_idw; --ln_fixed_idw;
2284 ELSE
2285 trn(ln_trn_cnt).idw_vol_contr := ln_idw; --ln_variable_idw;
2286 END IF;
2287
2288 trn(ln_trn_cnt).salary_type := fix_var_idw(i).idw_type;
2289
2290 END LOOP;
2291
2292 END IF; -- fix_var_idw.COUNT > 0 THEN
2293
2294 IF gv_variable_idw = 'Y' THEN
2295
2296 ln_idw := 0;
2297 ln_fixed_idw := 0;
2298 ln_variable_idw := 0;
2299
2300 ln_idw := get_idw( p_assignment_id => p_assignment_id
2301 ,p_tax_unit_id => p_gre_id
2302 ,p_effective_date =>
2303 fnd_date.canonical_to_date(gv_periodic_end_date) + 1
2304 ,p_fixed_idw => ln_fixed_idw
2305 ,p_variable_idw => ln_variable_idw );
2306
2307 hr_utility.trace('SS_ARCH other TRN VARIABLE ln_idw: '||ln_idw);
2308 hr_utility.trace('SS_ARCH other TRN VARIABLE ln_fixed_idw: '||
2309 ln_fixed_idw);
2310 hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2311 ln_variable_idw);
2312
2313 hr_utility.set_location(gv_package || lv_procedure_name, 2040);
2314 ln_step := 204;
2315
2316 ln_trn_cnt := trn.COUNT;
2317
2318 trn(ln_trn_cnt).type := '07';
2319 trn(ln_trn_cnt).date :=
2320 fnd_date.date_to_canonical(
2321 trunc(fnd_date.canonical_to_date(gv_periodic_end_date)) + 1);
2322 trn(ln_trn_cnt).dis_num := NULL;
2323 trn(ln_trn_cnt).abs_days := NULL;
2324 trn(ln_trn_cnt).idw_vol_contr := ln_idw;
2325 trn(ln_trn_cnt).salary_type := 'VARIABLE';
2326
2327 hr_utility.set_location(gv_package || lv_procedure_name, 2050);
2328
2329 END IF; -- gv_variable_idw = 'Y'
2330
2331 IF trn.COUNT > 0 THEN
2332
2333 /*
2334 lv_exclude_start_date := NULL;
2335 lv_exclude_end_date := NULL;
2336 lv_exclude_start_date_from_cur := NULL;
2337 lv_exclude_end_date_from_cur := NULL;
2338
2339 ln_dummy_flag := get_ss_exclusion_dates(p_person_id
2340 ,lv_exclude_start_date_from_cur
2341 ,lv_exclude_end_date_from_cur);
2342 */
2343
2344 lv_exclude_start_date := NULL;
2345 lv_exclude_end_date := NULL;
2346
2347 FOR i IN trn.FIRST..trn.LAST LOOP
2348
2349 /*8438074 Begin*/
2350 lv_do_not_report := NULL;
2351 lv_exclude_start_date := p_exc_start_date;
2352 lv_exclude_end_date := p_exc_end_date;
2353 /*8438074 End*/
2354
2355 OPEN c_person_detail (p_person_id
2356 ,fnd_date.canonical_to_date(trn(i).date));
2357 FETCH c_person_detail INTO lv_employee_ssn;
2358 CLOSE c_person_detail;
2359
2360 /*8438074 Begin*/
2361 IF (lv_exclude_start_date IS NOT NULL AND lv_exclude_end_date IS NOT NULL) AND
2362 (fnd_date.canonical_to_date(lv_exclude_start_date)<=fnd_date.canonical_to_date(lv_exclude_end_date)) AND
2363 (fnd_date.canonical_to_date(trn(i).date) BETWEEN fnd_date.canonical_to_date(lv_exclude_start_date) AND fnd_date.canonical_to_date(lv_exclude_end_date)) THEN
2364
2365 lv_do_not_report := 'Y';
2366
2367 ELSE lv_exclude_start_date := NULL;
2368 lv_exclude_end_date := NULL;
2369
2370 END IF;
2371 /*8438074 End*/
2372
2373 ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
2374
2375 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category
2376 := 'MX SS TRANSACTIONS';
2377 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code
2378 := NULL;
2379 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1
2380 := p_person_id;
2381 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2
2382 := trn(i).date;
2383 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info3
2384 := lv_employee_ssn;
2385 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4
2386 := trn(i).type;
2387 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info5
2388 := lv_employer_ss_id;
2389 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info6
2390 := trn(i).dis_num;
2391 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info7
2392 := trn(i).abs_days;
2393 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info8
2394 := to_char(trn(i).idw_vol_contr,'99999.99');
2395 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info9
2396 := NULL;
2397 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info10
2398 := lv_do_not_report;
2399 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info11
2400 := trn(i).salary_type;
2401 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info12
2402 := trn(i).credit_number;
2403 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info13
2404 := trn(i).discount_type;
2405 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info14
2406 := trn(i).discount_value;
2407 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info15
2408 := trn(i).redxn_table_applies;
2409 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info16
2410 := trn(i).abs_start_date;
2411 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info17
2412 := trn(i).subsidized_days;
2413 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info18
2414 := trn(i).disability_percent;
2415 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info19
2416 := trn(i).dis_insurance_type;
2417 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info20
2418 := trn(i).risk_type;
2419 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info21
2420 := trn(i).consequence;
2421 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info22
2422 := trn(i).disability_control;
2423 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info23
2424 := trn(i).abs_end_date;
2425 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info25
2426 := lv_exclude_start_date; /*8438074*/
2427 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info26
2428 := lv_exclude_end_date; /*8438074*/
2429
2430 END LOOP;
2431
2432 END IF; -- trn.COUNT > 0
2433
2434 EXCEPTION
2435 WHEN others THEN
2436 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
2437 gv_package || lv_procedure_name;
2438
2439 hr_utility_trace(lv_error_message || '-' || sqlerrm);
2440
2441 lv_error_message :=
2442 pay_emp_action_arch.set_error_message(lv_error_message);
2443
2444 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2445 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2446 hr_utility.raise_error;
2447
2448 END arch_other_transactions;
2449
2450 PROCEDURE arch_hire_separation (
2451 p_payroll_action_id IN NUMBER
2452 ,p_asg_action_id IN NUMBER
2453 ,p_effective_date IN DATE
2454 ,p_assignment_id IN NUMBER
2455 ,p_person_id IN NUMBER
2456 ,p_chunk_number IN NUMBER
2457 ,p_start_date IN DATE
2458 ,p_end_date IN DATE
2459 ,p_business_group_id IN NUMBER
2460 ,p_gre_id IN NUMBER
2461 ,p_eff_start_date IN DATE
2462 ,p_eff_end_date IN DATE
2463 ,p_asg_events IN
2464 pay_interpreter_pkg.t_detailed_output_table_type
2465 ,p_exc_start_date IN VARCHAR2
2466 ,p_exc_end_date IN VARCHAR2
2467 ) IS
2468
2469 CURSOR c_get_report_term_rehire (cp_asg_id IN NUMBER) IS
2470 SELECT segment10
2471 FROM per_all_assignments_f paf
2472 ,hr_soft_coding_keyflex hck
2473 WHERE paf.assignment_id = cp_asg_id
2474 AND paf.soft_coding_keyflex_id = hck.soft_coding_keyflex_id;
2475
2476 CURSOR c_get_asg_scl (cp_asg_id IN NUMBER
2477 ,cp_effective_date IN DATE) IS
2478 SELECT segment1
2479 ,assignment_number
2480 FROM per_all_assignments_f paf
2481 ,hr_soft_coding_keyflex hck
2482 WHERE paf.assignment_id = cp_asg_id
2483 AND cp_effective_date BETWEEN paf.effective_start_date
2484 AND paf.effective_end_date
2485 AND paf.soft_coding_keyflex_id = hck.soft_coding_keyflex_id;
2486
2487 CURSOR c_get_asg_loc (cp_asg_id IN NUMBER
2488 ,cp_effective_date IN DATE) IS
2489 SELECT location_id
2490 ,assignment_number
2491 FROM per_all_assignments_f
2492 WHERE assignment_id = cp_asg_id
2493 AND cp_effective_date BETWEEN effective_start_date
2494 AND effective_end_date;
2495
2496
2497 CURSOR c_get_org_information ( cp_organization_id IN NUMBER) IS
2498 SELECT replace(org_information1,'-','') Social_Security_ID
2499 FROM hr_organization_information
2500 WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
2501 AND organization_id = cp_organization_id ;
2502
2503 CURSOR c_person_detail (cp_person_id IN NUMBER
2504 ,cp_effective_date IN DATE) IS
2505 SELECT replace(ppf.per_information3,'-','') emp_ssnumber
2506 FROM per_all_people_f ppf
2507 WHERE ppf.person_id = cp_person_id
2508 -- Bug 6013218
2509 AND cp_effective_date BETWEEN ppf.effective_start_date AND
2510 ppf.effective_end_date;
2511 /*AND ppf.effective_start_date =
2512 ( SELECT max(ppf_in.effective_start_date)
2513 FROM per_all_people_f ppf_in
2514 WHERE ppf_in.person_id = ppf.person_id
2515 AND trunc(cp_end_date) >= ppf_in.effective_start_date
2516 AND trunc(cp_start_date) <= ppf_in.effective_end_date);*/
2517
2518 CURSOR c_get_leaving_reason ( cp_assignment_id IN NUMBER
2519 ,cp_effective_date IN DATE
2520 ,cp_gre_id IN NUMBER ) IS
2521 SELECT aei_information3
2522 FROM per_assignment_extra_info pae
2523 WHERE pae.assignment_id = cp_assignment_id
2524 AND information_type = 'MX_SS_EMP_TRANS_REASON'
2525 AND fnd_date.canonical_to_date(aei_information1) = cp_effective_date
2526 AND aei_information2 = cp_gre_id ;
2527
2528 CURSOR c_get_pos_leaving_reason ( cp_assignment_id IN NUMBER
2529 ,cp_effective_date IN DATE ) IS
2530 SELECT pds_information1
2531 ,actual_termination_date
2532 FROM per_periods_of_service ppos,
2533 per_all_assignments_f paf
2534 WHERE paf.assignment_id = cp_assignment_id
2535 AND paf.person_id = ppos.person_id
2536 AND cp_effective_date BETWEEN paf.effective_start_date
2537 AND paf.effective_end_date
2538 AND pds_information_category='MX';
2539
2540 CURSOR c_asg_status_type ( cp_asg_status_type_id IN NUMBER) IS
2541 SELECT per_system_status
2542 FROM per_assignment_status_types
2543 WHERE assignment_status_type_id = cp_asg_status_type_id;
2544
2545 CURSOR csr_asg_exists (cp_effective_date DATE) IS
2546 SELECT 'X'
2547 FROM per_assignments_f paf
2548 WHERE paf.assignment_id = p_assignment_id
2549 AND cp_effective_date BETWEEN paf.effective_start_date
2550 AND paf.effective_end_date
2551 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2552 paf.location_id
2553 ,paf.business_group_id
2554 ,paf.soft_coding_keyflex_id
2555 ,cp_effective_date) = p_gre_id;
2556
2557
2558 CURSOR csr_per_gre (cp_effective_date DATE,
2559 cp_tran_type VARCHAR2) IS
2560 SELECT 'Y'
2561 FROM per_assignments_f paf,
2562 per_assignment_status_types pst
2563 WHERE paf.person_id = p_person_id
2564 --AND paf.assignment_id <> p_assignment_id
2565 AND paf.assignment_status_type_id = pst.assignment_status_type_id
2566 AND ((cp_effective_date < paf.effective_end_date AND
2567 cp_tran_type = '02' AND
2568 -- Bug 6019466
2569 pst.per_system_status = 'ACTIVE_ASSIGN') OR
2570 (cp_effective_date > paf.effective_start_date AND
2571 cp_tran_type = '08'))
2572 AND paf.assignment_type = 'E'
2573 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2574 paf.location_id
2575 ,paf.business_group_id
2576 ,paf.soft_coding_keyflex_id
2577 ,cp_effective_date) = p_gre_id
2578 AND EXISTS (SELECT 1
2579 FROM per_all_people_f per
2580 WHERE per.person_id = paf.person_id
2581 AND cp_effective_date
2582 BETWEEN per.effective_start_date AND per.effective_end_date
2583 AND NVL(per.current_applicant_flag,'N') <> 'Y'
2584 AND paf.effective_start_date BETWEEN per.effective_start_date AND per.effective_end_date);
2585
2586 /*Added to check for the applicant type */
2587 CURSOR c_check_per_status (p_person_id IN VARCHAR2 ,
2588 p_effective_date IN VARCHAR2) IS
2589
2590 SELECT per.current_applicant_flag
2591 FROM per_all_people_f per
2592 WHERE per.person_id = p_person_id
2593 AND fnd_date.canonical_to_date(p_effective_date)
2594 BETWEEN per.effective_start_date AND per.effective_end_date;
2595
2596 /*bug 6933682*/
2597 CURSOR c_salary_type ( cp_assignment_id IN NUMBER
2598 ,cp_effective_date IN DATE) IS
2599 SELECT hck.segment6
2600 FROM per_all_assignments_f paf
2601 ,hr_soft_coding_keyflex hck
2602 WHERE paf.assignment_id = cp_assignment_id
2603 AND paf.soft_coding_keyflex_id = hck.soft_coding_keyflex_id
2604 AND cp_effective_date BETWEEN paf.effective_start_date
2605 AND paf.effective_end_date ;
2606
2607
2608 lv_hire VARCHAR2(1);
2609 lv_separation VARCHAR2(1);
2610
2611 lv_transaction_type VARCHAR2(50);
2612 lv_employer_ss_id VARCHAR2(50);
2613 lv_employee_ssn VARCHAR2(50);
2614 lv_transaction_date VARCHAR2(50);
2615 lv_leaving_reason VARCHAR2(50);
2616 lv_relation_exists VARCHAR2(1);
2617 lv_report_yes_no VARCHAR2(4);
2618
2619 ld_sep_date DATE;
2620 ld_eff_date DATE;
2621 ln_min_wage NUMBER;
2622
2623 ln_index NUMBER;
2624
2625 hire_sep hire_separation;
2626 hire_sep_uniq hire_separation;
2627
2628 ln_soft_cod_kflx_found NUMBER;
2629 lv_table_name VARCHAR2(150);
2630 lv_old_value VARCHAR2(150);
2631 lv_new_value VARCHAR2(150);
2632 lv_change_values VARCHAR2(150);
2633 lv_msg_txt VARCHAR2(250);
2634 lv_asg_number VARCHAR2(150);
2635 ln_first_time NUMBER;
2636 ln_asg_scl_old NUMBER;
2637 ln_asg_scl_new NUMBER;
2638 ln_asg_loc_old NUMBER;
2639 ln_asg_loc_new NUMBER;
2640 ln_count NUMBER;
2641 ln_old_gre_id NUMBER;
2642 ln_new_gre_id NUMBER;
2643 lv_hire_sep_found VARCHAR2(15);
2644 lv_sep_already_in VARCHAR2(15);
2645 lv_hire_already_in VARCHAR2(15);
2646 lv_old_asg_status VARCHAR2(100);
2647 lv_new_asg_status VARCHAR2(100);
2648 ln_asg_count NUMBER;
2649
2650
2651 ln_idw NUMBER;
2652 ln_fixed_idw NUMBER;
2653 ln_variable_idw NUMBER;
2654 lv_idw VARCHAR2(100);
2655
2656 lv_procedure_name VARCHAR2(100);
2657 lv_error_message VARCHAR2(2000);
2658 ln_step NUMBER;
2659
2660 lv_check_applicant varchar2(1);
2661 ln_salary_type VARCHAR2(10);
2662
2663 /*8438074 Begin*/
2664 lv_exclude_start_date VARCHAR2(50);
2665 lv_exclude_end_date VARCHAR2(50);
2666 --ln_dummy_flag NUMBER;
2667 lv_do_not_report VARCHAR2(5);
2668 --lv_exclude_start_date_from_cur VARCHAR2(50);
2669 --lv_exclude_end_date_from_cur VARCHAR2(50);
2670 /*8438074 End*/
2671
2672 BEGIN
2673
2674 lv_procedure_name := 'arch_hire_separation';
2675
2676 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2677 ln_step := 1;
2678
2679 lv_leaving_reason := NULL;
2680 lv_idw := NULL;
2681 ld_eff_date := p_eff_end_date;
2682 ln_soft_cod_kflx_found := 0;
2683 ln_idw := 0;
2684 ln_fixed_idw := 0;
2685 ln_variable_idw := 0;
2686 lv_hire := 'N';
2687
2688 hire_sep.DELETE;
2689
2690 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2691 ln_step := 2;
2692
2693 FOR i IN 1..p_asg_events.COUNT
2694 LOOP
2695
2696 lv_table_name :=
2697 dated_tbls(p_asg_events(i).dated_table_id).table_name;
2698
2699 hr_utility_trace('-----------------------------------------------');
2700 hr_utility_trace('Result row :' ||to_char(i));
2701 hr_utility_trace('lv_table_name :' ||lv_table_name );
2702 hr_utility_trace('Datetracked_event: '||
2703 p_asg_events(i).datetracked_event );
2704 hr_utility_trace('Change_mode : '||
2705 p_asg_events(i).change_mode );
2706 hr_utility_trace('Effective_date : '||
2707 to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2708 hr_utility_trace('dated_table_id : '||
2709 TO_CHAR(p_asg_events(i).dated_table_id));
2710 hr_utility_trace('column_name : '||
2711 p_asg_events(i).column_name );
2712 hr_utility_trace('Update_type : '||
2713 p_asg_events(i).update_type );
2714 hr_utility_trace('old_value : '||
2715 p_asg_events(i).old_value );
2716 hr_utility_trace('new_value : '||
2717 p_asg_events(i).new_value );
2718 hr_utility_trace('change_values : '||
2719 p_asg_events(i).change_values );
2720 hr_utility_trace('-----------------------------------------------');
2721
2722 ln_old_gre_id := -9;
2723 ln_new_gre_id := -9;
2724
2725 lv_change_values := p_asg_events(i).change_values ;
2726
2727 lv_old_value := ltrim(rtrim(SUBSTR(lv_change_values,1,
2728 INSTR(lv_change_values,'->')-1)));
2729
2730 lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,
2731 INSTR(lv_change_values,'->')+3)));
2732
2733 IF lv_old_value = '<null>' THEN
2734 lv_old_value := NULL;
2735 END IF;
2736
2737 IF lv_new_value = '<null>' THEN
2738 lv_new_value := NULL;
2739 END IF;
2740
2741 IF p_asg_events(i).update_type = 'I' THEN
2742
2743 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2744 ln_step := 3;
2745
2746 lv_hire := 'Y';
2747 ln_count := hire_sep.COUNT;
2748 hire_sep(ln_count).trn_type := 'HIRE';
2749 hire_sep(ln_count).trn_date := p_asg_events(i).effective_date;
2750
2751 ELSE
2752
2753 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2754 ln_step := 4;
2755 hr_utility_trace ('column_name = PER_ALL_ASSIGNMENTS_F.'||
2756 p_asg_events(i).column_name);
2757 IF p_asg_events(i).column_name = 'LOCATION_ID' THEN
2758
2759 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2760
2761 /*
2762 * Retrieve the gre off the soft coding keyflex (scl). If a GRE has
2763 * been specified at the scl, location changes are moot.
2764 */
2765 OPEN c_get_asg_scl(p_assignment_id
2766 ,p_asg_events(i).effective_date - 1);
2767
2768 FETCH c_get_asg_scl
2769 INTO ln_asg_scl_old
2770 ,lv_asg_number;
2771
2772 CLOSE c_get_asg_scl;
2773
2774 OPEN c_get_asg_scl(p_assignment_id
2775 ,p_asg_events(i).effective_date);
2776
2777 FETCH c_get_asg_scl
2778 INTO ln_asg_scl_new
2779 ,lv_asg_number;
2780
2781 CLOSE c_get_asg_scl;
2782
2783 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2784 ln_step := 6;
2785
2786 ln_old_gre_id :=
2787 per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2788 lv_old_value
2789 ,p_business_group_id
2790 ,ln_asg_scl_old
2791 ,p_asg_events(i).effective_date - 1);
2792
2793 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2794
2795 IF ln_old_gre_id < 0 THEN
2796 lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2797 lv_asg_number ||'], Effective Date ['||
2798 TO_CHAR(p_asg_events(i).effective_date - 1)||']';
2799
2800 pay_core_utils.push_message(p_applid => 800
2801 ,p_msg_name => NULL
2802 ,p_msg_txt => lv_msg_txt
2803 ,p_level => 'I');
2804 END IF;
2805
2806 hr_utility_trace('Checking wherther the person is applicant');
2807 OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2808 FETCH c_check_per_status INTO lv_check_applicant;
2809
2810 CLOSE c_check_per_status;
2811
2812 IF lv_check_applicant = 'Y' THEN
2813 hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2814 hr_utility_trace('making -9 for the gre that got by location');
2815 ln_old_gre_id := -9;
2816 END IF;
2817
2818 ln_step := 7;
2819
2820 ln_new_gre_id :=
2821 per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2822 lv_new_value
2823 ,p_business_group_id
2824 ,ln_asg_scl_new
2825 ,p_asg_events(i).effective_date);
2826
2827 IF ln_new_gre_id < 0 THEN
2828 lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2829 lv_asg_number ||'], Effective Date ['||
2830 TO_CHAR(p_asg_events(i).effective_date)||']';
2831
2832 pay_core_utils.push_message(p_applid => 800
2833 ,p_msg_name => NULL
2834 ,p_msg_txt => lv_msg_txt
2835 ,p_level => 'I');
2836 END IF;
2837
2838 /*IF ( p_asg_events(1).update_type <> 'I' AND
2839 p_asg_events(i).update_type <> 'I' ) THEN*/
2840
2841 IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2842 THEN
2843
2844 hr_utility.set_location(gv_package||lv_procedure_name,100);
2845 ln_step := 10;
2846
2847 ln_count := hire_sep.COUNT;
2848 hire_sep(ln_count).trn_type := 'SEPARATION';
2849 hire_sep(ln_count).trn_date :=
2850 p_asg_events(i).effective_date - 1;
2851
2852 END IF;
2853
2854 IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2855 THEN
2856
2857 hr_utility.set_location(gv_package||lv_procedure_name,110);
2858 ln_step := 11;
2859
2860 ln_count := hire_sep.COUNT;
2861 hire_sep(ln_count).trn_type := 'HIRE';
2862 hire_sep(ln_count).trn_date :=
2863 p_asg_events(i).effective_date;
2864
2865 END IF;
2866
2867 --END IF; -- update_type <> 'I'
2868
2869
2870 ELSIF p_asg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' THEN
2871
2872 hr_utility.set_location(gv_package||lv_procedure_name,120);
2873 ln_step := 12;
2874 hr_utility_trace('Inside get_transaction and sof_coding :');
2875 hr_utility_trace('p_assignment_id :' || p_assignment_id);
2876 hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2877 OPEN c_get_asg_loc(p_assignment_id
2878 ,p_asg_events(i).effective_date - 1);
2879
2880 FETCH c_get_asg_loc
2881 INTO ln_asg_loc_old
2882 ,lv_asg_number;
2883
2884 CLOSE c_get_asg_loc;
2885 hr_utility_trace('Value from the cursor c_get_asg_loc');
2886 hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2887 hr_utility_trace('lv_asg_number '||lv_asg_number);
2888 hr_utility.set_location(gv_package||lv_procedure_name,130);
2889 ln_step := 13;
2890
2891 ln_old_gre_id := NVL(
2892 per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2893 ln_asg_loc_old
2894 ,p_business_group_id
2895 ,lv_old_value
2896 ,p_asg_events(i).effective_date - 1), -9);
2897 hr_utility_trace('Checking wherther the person is applicant');
2898 OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2899 FETCH c_check_per_status INTO lv_check_applicant;
2900
2901 CLOSE c_check_per_status;
2902
2903 IF lv_check_applicant = 'Y' THEN
2904 hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2905 hr_utility_trace('making -9 for the gre that got by location');
2906 ln_old_gre_id := -9;
2907 END IF;
2908
2909 hr_utility.set_location(gv_package||lv_procedure_name,135);
2910 hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2911
2912 IF ln_old_gre_id < 0 THEN
2913 hr_utility_trace('ln_old_gre_id <0');
2914 lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2915 lv_asg_number ||'], Effective Date ['||
2916 TO_CHAR(p_asg_events(i).effective_date - 1)||']';
2917
2918 pay_core_utils.push_message(p_applid => 800
2919 ,p_msg_name => NULL
2920 ,p_msg_txt => lv_msg_txt
2921 ,p_level => 'I');
2922 END IF;
2923
2924 hr_utility.set_location(gv_package||lv_procedure_name,140);
2925 ln_step := 14;
2926 hr_utility_trace('p_assignment_id :' || p_assignment_id);
2927 hr_utility_trace('p_asg_events(i).effective_date :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date ));
2928 OPEN c_get_asg_loc(p_assignment_id
2929 ,p_asg_events(i).effective_date);
2930
2931 FETCH c_get_asg_loc
2932 INTO ln_asg_loc_new
2933 ,lv_asg_number;
2934
2935 CLOSE c_get_asg_loc;
2936 hr_utility_trace('Value from the cursor c_get_asg_loc for new loc');
2937 hr_utility_trace('ln_asg_loc_new '||ln_asg_loc_new);
2938 hr_utility_trace('lv_asg_number '||lv_asg_number);
2939 ln_new_gre_id := NVL(
2940 per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2941 ln_asg_loc_new
2942 ,p_business_group_id
2943 ,lv_new_value
2944 ,p_asg_events(i).effective_date), -9);
2945
2946 IF ln_new_gre_id < 0 THEN
2947 hr_utility_trace('ln_old_gre_id <0');
2948 lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2949 lv_asg_number ||'], Effective Date ['||
2950 TO_CHAR(p_asg_events(i).effective_date - 1)||']';
2951
2952 pay_core_utils.push_message(p_applid => 800
2953 ,p_msg_name => NULL
2954 ,p_msg_txt => lv_msg_txt
2955 ,p_level => 'I');
2956 END IF;
2957 hr_utility_trace('p_gre_id '||p_gre_id);
2958 hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2959 hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2960 IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2961 THEN
2962 hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2963 hr_utility.set_location(gv_package||lv_procedure_name,100);
2964 ln_step := 10;
2965
2966 ln_count := hire_sep.COUNT;
2967 hire_sep(ln_count).trn_type := 'SEPARATION';
2968 hire_sep(ln_count).trn_date :=
2969 p_asg_events(i).effective_date - 1;
2970 hr_utility_trace('Separation');
2971 END IF;
2972
2973 IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2974 THEN
2975 hr_utility_trace('ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id');
2976 hr_utility.set_location(gv_package||lv_procedure_name,110);
2977 ln_step := 11;
2978
2979 ln_count := hire_sep.COUNT;
2980 hire_sep(ln_count).trn_type := 'HIRE';
2981 hire_sep(ln_count).trn_date :=
2982 p_asg_events(i).effective_date;
2983 hr_utility_trace('Hire');
2984 END IF;
2985
2986 ELSIF p_asg_events(i).column_name = 'ASSIGNMENT_STATUS_TYPE_ID' THEN
2987
2988 -- ACTIVE_ASSIGN to TERM_ASSIGN ok
2989 -- TERM_ASSIGN TO ACTIVE_ASSIGN
2990 -- ( This is a reverse termination. We need to record this as a HIRE
2991 -- Transaction. If the termination was reported to Social Security
2992 -- SUA and the Rehire needs to reported.)
2993
2994 IF lv_old_value is NOT NULL THEN
2995 OPEN c_asg_status_type(TO_NUMBER(lv_old_value)) ;
2996 FETCH c_asg_status_type INTO lv_old_asg_status ;
2997 CLOSE c_asg_status_type ;
2998 END IF;
2999
3000 IF lv_new_value IS NOT NULL THEN
3001 OPEN c_asg_status_type(TO_NUMBER(lv_new_value)) ;
3002 FETCH c_asg_status_type INTO lv_new_asg_status ;
3003 CLOSE c_asg_status_type ;
3004 END IF;
3005
3006 hr_utility_trace( 'old assignment status :'||lv_old_asg_status );
3007 hr_utility_trace( 'new assignment status :'||lv_new_asg_status );
3008
3009 IF lv_old_asg_status = 'ACTIVE_ASSIGN' AND
3010 lv_new_asg_status = 'TERM_ASSIGN' THEN
3011
3012 ln_count := hire_sep.COUNT;
3013 hire_sep(ln_count).trn_type := 'SEPARATION';
3014
3015 SELECT COUNT(*)
3016 INTO ln_asg_count
3017 FROM per_all_assignments_f
3018 WHERE assignment_id = p_assignment_id
3019 AND effective_start_date = p_asg_events(i).effective_date;
3020
3021 IF ln_asg_count > 0 THEN
3022
3023 hire_sep(ln_count).trn_date :=
3024 p_asg_events(i).effective_date - 1;
3025
3026 ELSE
3027
3028 hire_sep(ln_count).trn_date := p_asg_events(i).effective_date;
3029
3030 END IF;
3031
3032
3033 ELSE
3034 ln_count := hire_sep.COUNT;
3035 hire_sep(ln_count).trn_type := 'HIRE';
3036 hire_sep(ln_count).trn_date := p_asg_events(i).effective_date;
3037
3038 END IF;
3039
3040 ELSIF p_asg_events(i).column_name = 'EMPLOYMENT_CATEGORY' THEN
3041
3042 IF NVL(lv_old_value, 'NULL') <> NVL(lv_new_value, 'NULL') THEN
3043
3044 IF gn_person_rec_chng = 0 THEN
3045 gn_person_rec_chng := 1;
3046 END IF;
3047
3048 END IF;
3049
3050 ELSIF p_asg_events(i).column_name = 'EFFECTIVE_END_DATE' THEN
3051
3052 /**********************************************************
3053 ** IGNORE THE TRANSACTION IF EFFECTIVE_DATE is 31-Dec-4712,
3054 ** AS PER VM.
3055 **********************************************************/
3056
3057 /* PEM returns a change in EFFECTIVE_END_DATE for *every*
3058 date-tracked update to the asg record. We need to ignore
3059 EFFECTIVE_END_DATE events if assignment is not terminated.
3060 (Bug 5888285)*/
3061 lv_old_asg_status := NULL;
3062 OPEN csr_asg_exists(p_asg_events(i).effective_date + 1);
3063 FETCH csr_asg_exists INTO lv_old_asg_status;
3064 CLOSE csr_asg_exists;
3065
3066 /*IF p_asg_events(i).effective_date <>
3067 to_date('4712/12/31', 'yyyy/mm/dd') THEN*/
3068 IF lv_old_asg_status IS NULL THEN
3069 ln_count := hire_sep.COUNT;
3070 hire_sep(ln_count).trn_type := 'SEPARATION';
3071 hire_sep(ln_count).trn_date := p_asg_events(i).effective_date;
3072 END IF;
3073
3074 END IF;
3075
3076 END IF;
3077
3078 hr_utility_trace('---------------------------------------');
3079 hr_utility_trace('Row in Location :' ||i);
3080 hr_utility_trace('lv_change_values :' ||lv_change_values);
3081 hr_utility_trace('lv_old_value :' ||lv_old_value );
3082 hr_utility_trace('lv_new_value :' ||lv_new_value );
3083 hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3084 hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
3085 hr_utility_trace('p_gre_id :' ||p_gre_id );
3086
3087 IF hire_sep.COUNT > 0 THEN
3088
3089 hr_utility_trace('ln_count :' ||ln_count);
3090 hr_utility_trace('TRN_TYPE :' ||hire_sep(ln_count).trn_type);
3091 hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
3092
3093 ELSE
3094
3095 hr_utility_trace('NO RECORD FOUND in hire_sep table');
3096 END IF;
3097
3098 hr_utility_trace('---------------------------------------');
3099
3100 END LOOP;
3101
3102 IF hire_sep.COUNT > 0 THEN
3103
3104 hire_sep_uniq.DELETE;
3105
3106 lv_sep_already_in := 'N';
3107 lv_hire_already_in := 'N';
3108
3109 FOR i in hire_sep.FIRST..hire_sep.LAST
3110 LOOP
3111
3112 lv_hire_sep_found := 'N';
3113
3114 hr_utility_trace('hire_sep(i).trn_type '||i||': '||
3115 hire_sep(i).trn_type );
3116 hr_utility_trace('hire_sep(i).trn_date '||i||': '||
3117 hire_sep(i).trn_date );
3118 hr_utility_trace('---------------------------------------');
3119
3120 IF hire_sep_uniq.COUNT > 0 THEN
3121
3122 FOR j in hire_sep_uniq.FIRST..hire_sep_uniq.LAST
3123 LOOP
3124 hr_utility_trace('hire_sep_uniq(j).trn_type '||j||': '||
3125 hire_sep_uniq(j).trn_type );
3126 hr_utility_trace('hire_sep_uniq(j).trn_date '||j||': '||
3127 hire_sep_uniq(j).trn_date );
3128
3129 IF hire_sep(i).trn_type = hire_sep_uniq(j).trn_type AND
3130 hire_sep(i).trn_date = hire_sep_uniq(j).trn_date THEN
3131
3132 lv_hire_sep_found := 'Y';
3133 hr_utility_trace('FOUND');
3134
3135 END IF;
3136
3137 END LOOP;
3138
3139 END IF;
3140
3141 hr_utility_trace('---------------------------------------');
3142
3143 /**************************************************************
3144 ** There should not be a hire/separation record followed by
3145 ** another hire/separation record.
3146 ** That means, there should be an hire/sepatation record
3147 ** between two separation/hire records.
3148 ** lv_sep_already_in and lv_hire_already_in flags are used to
3149 ** fulfill above requirement.
3150 ***************************************************************/
3151
3152 IF lv_hire_sep_found = 'N' THEN
3153
3154 IF hire_sep(i).trn_type = 'HIRE' THEN
3155
3156 IF lv_hire_already_in = 'N' THEN
3157
3158 lv_hire_already_in := 'Y';
3159 lv_sep_already_in := 'N';
3160
3161 hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3162 hr_utility_trace(' ');
3163 ln_count := hire_sep_uniq.COUNT;
3164 hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3165 hire_sep_uniq(ln_count).trn_date := hire_sep(i).trn_date;
3166
3167 END IF; -- lv_hire_already_in = 'N'
3168
3169 ELSIF hire_sep(i).trn_type = 'SEPARATION' THEN
3170
3171 IF lv_sep_already_in = 'N' THEN
3172
3173 lv_hire_already_in := 'N';
3174 lv_sep_already_in := 'Y';
3175
3176 hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3177 hr_utility_trace(' ');
3178
3179 ln_count := hire_sep_uniq.COUNT;
3180 hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3181 -- Bug 5005254
3182 hire_sep_uniq(ln_count).trn_date := hire_sep(i).trn_date;
3183
3184 END IF; -- lv_sep_already_in = 'N'
3185
3186 END IF; -- hire_sep(i).trn_type
3187
3188 END IF; -- lv_hire_sep_found = 'N'
3189
3190 END LOOP;
3191
3192 hire_sep.DELETE;
3193 hire_sep := hire_sep_uniq;
3194 hire_sep_uniq.DELETE;
3195
3196 hr_utility.set_location(gv_package || lv_procedure_name, 210);
3197 ln_step := 21;
3198
3199 OPEN c_get_org_information(p_gre_id);
3200 FETCH c_get_org_information INTO lv_employer_ss_id;
3201 CLOSE c_get_org_information;
3202
3203 hr_utility.set_location(gv_package || lv_procedure_name, 220);
3204 ln_step := 22;
3205
3206 /*
3207 lv_exclude_start_date := NULL;
3208 lv_exclude_end_date := NULL;
3209 lv_exclude_start_date_from_cur := NULL;
3210 lv_exclude_end_date_from_cur := NULL;
3211
3212 ln_dummy_flag := get_ss_exclusion_dates(p_person_id
3213 ,lv_exclude_start_date_from_cur
3214 ,lv_exclude_end_date_from_cur);
3215 */
3216
3217 lv_exclude_start_date := NULL;
3218 lv_exclude_end_date := NULL;
3219
3220 FOR i in hire_sep.FIRST..hire_sep.LAST
3221 LOOP
3222
3223 /*8438074 Begin*/
3224 lv_do_not_report := NULL;
3225 lv_exclude_start_date := p_exc_start_date;
3226 lv_exclude_end_date := p_exc_end_date;
3227 /*8438074 End*/
3228
3229 hr_utility.trace('Count hire_sep array :'|| i);
3230 lv_relation_exists := 'N';
3231 IF hire_sep(i).trn_type = 'HIRE' THEN
3232
3233 hr_utility.set_location(gv_package || lv_procedure_name, 240);
3234 ln_step := 24;
3235
3236 lv_transaction_type := '08';
3237 lv_transaction_date :=
3238 fnd_date.date_to_canonical(hire_sep(i).trn_date);
3239 lv_leaving_reason := NULL;
3240 hr_utility.trace('lv_transaction_date is: '||lv_transaction_date);
3241 /* Do not archive this transaction if person-GRE relation
3242 already exists prior to transaction date. */
3243 OPEN csr_per_gre(hire_sep(i).trn_date,
3244 lv_transaction_type);
3245 FETCH csr_per_gre INTO lv_relation_exists;
3246 CLOSE csr_per_gre;
3247 hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3248 hr_utility.set_location(gv_package || lv_procedure_name, 230);
3249 ln_step := 23;
3250
3251 ln_idw := get_idw( p_assignment_id => p_assignment_id
3252 ,p_tax_unit_id => p_gre_id
3253 ,p_effective_date => hire_sep(i).trn_date
3254 ,p_fixed_idw => ln_fixed_idw
3255 ,p_variable_idw => ln_variable_idw );
3256
3257 hr_utility.trace('SS_ARCH hire_sep ln_idw: '||ln_idw);
3258 hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3259 hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3260 ln_variable_idw);
3261
3262 hr_utility.set_location(gv_package || lv_procedure_name, 2030);
3263 ln_step := 203;
3264
3265 lv_idw := to_char(ln_idw, '99999.99'); --Bug 8988585
3266
3267 hr_utility.trace('SS_ARCH hire_sep lv_idw: '||lv_idw);
3268
3269 IF gn_person_rec_chng = 0 THEN
3270 gn_person_rec_chng := 1;
3271 END IF;
3272
3273 ELSIF hire_sep(i).trn_type = 'SEPARATION' THEN
3274
3275 hr_utility.set_location(gv_package || lv_procedure_name, 250);
3276 ln_step := 25;
3277
3278 lv_transaction_type := '02';
3279 lv_transaction_date :=
3280 fnd_date.date_to_canonical(hire_sep(i).trn_date);
3281
3282 ld_eff_date := hire_sep(i).trn_date;
3283 lv_idw := NULL;
3284
3285 /* Do not archive this transaction if person-GRE relation
3286 already exists after transaction date. */
3287 OPEN csr_per_gre(hire_sep(i).trn_date,
3288 lv_transaction_type);
3289 FETCH csr_per_gre INTO lv_relation_exists;
3290 CLOSE csr_per_gre;
3291 hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3292
3293 hr_utility.set_location(gv_package || lv_procedure_name, 260);
3294 ln_step := 26;
3295
3296 OPEN c_get_leaving_reason( p_assignment_id
3297 ,ld_eff_date
3298 ,p_gre_id
3299 );
3300 FETCH c_get_leaving_reason INTO lv_leaving_reason ;
3301 CLOSE c_get_leaving_reason;
3302
3303 IF lv_leaving_reason IS NULL THEN
3304
3305 hr_utility.set_location(gv_package || lv_procedure_name, 270);
3306 ln_step := 27;
3307
3308 -- get it from periods of service
3309 -- also the effective date passed is not correct
3310 -- so need to get the actual termination date
3311
3312 OPEN c_get_pos_leaving_reason( p_assignment_id
3313 ,ld_eff_date );
3314 FETCH c_get_pos_leaving_reason INTO lv_leaving_reason
3315 ,ld_sep_date;
3316 CLOSE c_get_pos_leaving_reason;
3317
3318 END IF;
3319
3320 END IF;
3321
3322 IF lv_relation_exists = 'N' THEN
3323 hr_utility.set_location(gv_package || lv_procedure_name, 280);
3324 ln_step := 28;
3325 hr_utility_trace('lv_transaction_date :'|| lv_transaction_date);
3326 OPEN c_person_detail (p_person_id
3327 ,fnd_date.canonical_to_date
3328 (lv_transaction_date));
3329 FETCH c_person_detail INTO lv_employee_ssn;
3330 CLOSE c_person_detail;
3331
3332 OPEN c_get_report_term_rehire (p_assignment_id);
3333 FETCH c_get_report_term_rehire INTO lv_report_yes_no;
3334 CLOSE c_get_report_term_rehire;
3335
3336 IF lv_report_yes_no = 'N' THEN
3337 lv_report_yes_no := 'No';
3338 ELSE lv_report_yes_no := 'Yes';
3339 END IF;
3340
3341 /*6933682*/
3342 OPEN c_salary_type( p_assignment_id
3343 ,hire_sep(i).trn_date);
3344 FETCH c_salary_type INTO ln_salary_type;
3345 CLOSE c_salary_type;
3346 hr_utility.set_location(gv_package || lv_procedure_name, 300);
3347 ln_step := 28;
3348 IF ln_salary_type ='1' THEN
3349 ln_salary_type :='VARIABLE';
3350 ELSIF ln_salary_type ='2' THEN
3351 ln_salary_type :='MIXED';
3352 ELSIF ln_salary_type = '0' THEN
3353 ln_salary_type :='FIXED';
3354 END IF;
3355
3356 /*8438074 Begin*/
3357 IF (lv_exclude_start_date IS NOT NULL AND lv_exclude_end_date IS NOT NULL) AND
3358 (fnd_date.canonical_to_date(lv_exclude_start_date)<=fnd_date.canonical_to_date(lv_exclude_end_date)) AND
3359 (fnd_date.canonical_to_date(lv_transaction_date) BETWEEN fnd_date.canonical_to_date(lv_exclude_start_date) AND fnd_date.canonical_to_date(lv_exclude_end_date)) THEN
3360
3361 lv_do_not_report := 'Y';
3362
3363 ELSE lv_exclude_start_date := NULL;
3364 lv_exclude_end_date := NULL;
3365
3366 END IF;
3367 /*8438074 End*/
3368
3369 ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
3370
3371 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category
3372 := 'MX SS TRANSACTIONS';
3373 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code
3374 := NULL;
3375 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1
3376 := p_person_id;
3377 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2
3378 := lv_transaction_date;
3379 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info3
3380 := lv_employee_ssn;
3381 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4
3382 := lv_transaction_type;
3383 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info5
3384 := lv_employer_ss_id;
3385 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info6
3386 := NULL;
3387 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info7
3388 := NULL;
3389 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info8
3390 := lv_idw;
3391 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info9
3392 := lv_leaving_reason;
3393 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info10
3394 := lv_do_not_report;
3395 IF pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info11 IS NULL THEN
3396 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info11
3397 := ln_salary_type;
3398 END IF; /*6933682*/
3399 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info24
3400 := lv_report_yes_no;
3401 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info25
3402 := lv_exclude_start_date; /*8438074*/
3403 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info26
3404 := lv_exclude_end_date; /*8438074*/
3405 hr_utility_trace('Archived MX SS Transaction for hire');
3406 hr_utility_trace('-----------------------------------');
3407 hr_utility_trace('index ' || ln_index);
3408 hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3409 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3410 hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3411 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3412 hr_utility_trace('Person ID ' ||
3413 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3414 hr_utility_trace('Transaction date ' ||
3415 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3416 hr_utility_trace('transaction type ' ||
3417 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4);
3418 hr_utility_trace('-----------------------------------');
3419
3420
3421 ELSE
3422 hr_utility_trace('Person-GRE association exits. Transaction '||
3423 lv_transaction_type||' ('||lv_transaction_date||
3424 ') will not be archived.');
3425 END IF;
3426 END LOOP;
3427
3428 END IF;
3429
3430
3431 EXCEPTION
3432 WHEN others THEN
3433 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3434 gv_package || lv_procedure_name;
3435
3436 hr_utility_trace(lv_error_message || '-' || sqlerrm);
3437
3438 lv_error_message :=
3439 pay_emp_action_arch.set_error_message(lv_error_message);
3440
3441 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3442 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3443 hr_utility.raise_error;
3444
3445 END arch_hire_separation;
3446
3447 PROCEDURE get_transactions( p_payroll_action_id IN NUMBER
3448 ,p_asg_action_id IN NUMBER
3449 ,p_effective_date IN DATE
3450 ,p_assignment_id IN NUMBER
3451 ,p_person_id IN NUMBER
3452 ,p_chunk_number IN NUMBER
3453 ,p_start_date IN DATE
3454 ,p_end_date IN DATE
3455 ,p_business_group_id IN NUMBER
3456 ,p_gre_id IN NUMBER ) IS
3457
3458 CURSOR c_get_event_group (cp_event_group_name IN VARCHAR2) IS
3459 SELECT event_group_id
3460 FROM pay_event_groups
3461 WHERE event_group_name = cp_event_group_name;
3462
3463 CURSOR c_assignments ( cp_assignment_id IN NUMBER
3464 ,cp_start_date IN DATE
3465 ,cp_end_date IN DATE
3466 ,cp_gre_id IN NUMBER ) IS
3467 SELECT paf.assignment_id
3468 ,paf.location_id
3469 ,paf.soft_coding_keyflex_id
3470 ,paf.effective_start_date
3471 ,paf.effective_end_date
3472 FROM per_all_assignments_f paf
3473 WHERE paf.assignment_id = cp_assignment_id
3474 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
3475 paf.location_id
3476 ,paf.business_group_id
3477 ,paf.soft_coding_keyflex_id
3478 ,trunc(cp_end_date)) = cp_gre_id
3479 ORDER BY paf.assignment_id
3480 ,paf.effective_start_date desc
3481 ,paf.effective_end_date desc;
3482
3483 CURSOR csr_get_asg_end_date (cp_effective_date DATE) IS
3484 SELECT effective_end_date
3485 FROM per_assignments_f pa
3486 WHERE pa.assignment_id = p_assignment_id
3487 AND pa.effective_end_date = cp_effective_date
3488 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
3489 location_id
3490 ,business_group_id
3491 ,soft_coding_keyflex_id
3492 ,cp_effective_date) = p_gre_id
3493 AND EXISTS (SELECT 1
3494 FROM per_all_people_f per
3495 WHERE per.person_id = pa.person_id
3496 AND cp_effective_date
3497 BETWEEN per.effective_start_date AND per.effective_end_date
3498 AND NVL(per.current_applicant_flag,'N') <> 'Y');
3499
3500
3501 ln_assignment_id NUMBER;
3502 ln_location_id NUMBER;
3503 ln_soft_cod_kflx_id NUMBER;
3504 ld_eff_start_date DATE;
3505 ld_eff_end_date DATE;
3506 ld_effective_date DATE;
3507
3508 ln_gre_id NUMBER;
3509 ln_event_group_id NUMBER;
3510
3511 int_pkg_events pay_interpreter_pkg.t_detailed_output_table_type;
3512 --asg_events_table t_int_asg_event_table;
3513 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
3514 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
3515 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
3516 l_global_env pay_interpreter_pkg.t_global_env_rec;
3517 l_dynamic_sql pay_interpreter_pkg.t_dynamic_sql_tab;
3518
3519
3520 asg_events pay_interpreter_pkg.t_detailed_output_table_type;
3521 per_events pay_interpreter_pkg.t_detailed_output_table_type;
3522 ele_events pay_interpreter_pkg.t_detailed_output_table_type;
3523 eev_events pay_interpreter_pkg.t_detailed_output_table_type;
3524 asg_count NUMBER;
3525 per_count NUMBER;
3526 ele_count NUMBER;
3527 eev_count NUMBER;
3528 ln_dummy_flag NUMBER;
3529 lv_exclude_start_date_from_cur VARCHAR2(50);
3530 lv_exclude_end_date_from_cur VARCHAR2(50);
3531
3532 lv_table_name VARCHAR2(150);
3533
3534 lv_procedure_name VARCHAR2(100);
3535 lv_error_message VARCHAR2(2000);
3536 ln_step NUMBER;
3537
3538 BEGIN
3539
3540 lv_procedure_name := 'get_transactions';
3541
3542 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3543 ln_step := 1;
3544
3545 ld_eff_start_date := p_start_date;
3546 ld_eff_end_date := p_end_date;
3547 asg_count := 0;
3548 per_count := 0;
3549 ele_count := 0;
3550 eev_count := 0;
3551
3552 asg_events.DELETE;
3553 per_events.DELETE;
3554 ele_events.DELETE;
3555 eev_events.DELETE;
3556
3557 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3558 ln_step := 2;
3559
3560 OPEN c_get_event_group ('Mexico Social Security Reports');
3561 FETCH c_get_event_group INTO ln_event_group_id;
3562 CLOSE c_get_event_group;
3563
3564 hr_utility_trace('p_person_id : ' || p_person_id);
3565 hr_utility_trace('p_start_date : ' || p_start_date);
3566 hr_utility_trace('p_end_date : ' || p_end_date);
3567 hr_utility_trace('p_gre_id : ' || p_gre_id);
3568
3569 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3570 ln_step := 3;
3571
3572 OPEN c_assignments ( p_assignment_id
3573 ,p_start_date
3574 ,p_end_date
3575 ,p_gre_id);
3576 FETCH c_assignments INTO ln_assignment_id
3577 ,ln_location_id
3578 ,ln_soft_cod_kflx_id
3579 ,ld_eff_start_date
3580 ,ld_eff_end_date;
3581 CLOSE c_assignments;
3582
3583 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3584 ln_step := 4;
3585
3586 pay_interpreter_pkg.entry_affected(
3587 p_element_entry_id => NULL
3588 ,p_assignment_action_id => NULL
3589 ,p_assignment_id => p_assignment_id
3590 ,p_mode => NULL
3591 ,p_process => NULL
3592 ,p_event_group_id => ln_event_group_id
3593 ,p_process_mode => 'ENTRY_CREATION_DATE'
3594 ,p_start_date => p_start_date
3595 ,p_end_date => p_end_date
3596 ,p_unique_sort => 'N' --tells intrprtr not to do unique sort
3597 ,p_business_group_id => NULL
3598 ,t_detailed_output => int_pkg_events --OUTPUT OF RESULTS
3599 ,t_proration_dates => l_proration_dates
3600 ,t_proration_change_type => l_proration_changes
3601 ,t_proration_type => l_pro_type_tab);
3602
3603 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3604 ln_step := 5;
3605 hr_utility_trace('pay_interpreter_pkg.entry_affected Returned Rows');
3606
3607 IF int_pkg_events.COUNT > 0 THEN
3608
3609 FOR i IN 1..int_pkg_events.COUNT
3610 LOOP
3611 /*IF lv_table_name = 'PER_ALL_ASSIGNMENTS_F' THEN
3612 IF int_pkg_events(i).column_name = 'LOCATION_ID' THEN
3613 int_pkg_events(i).effective_date :=
3614 int_pkg_events(i).effective_date - 1;
3615 END IF;
3616 END IF;*/
3617
3618 /* Adding event qualification mechanism so that only those events that
3619 belong to current GRE are picked for archival. (Bug 5921945)*/
3620 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3621 ld_effective_date := int_pkg_events(i).effective_date;
3622 hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3623 -- Bug 6005853
3624 IF int_pkg_events(i).column_name IN ('LOCATION_ID',
3625 'SOFT_CODING_KEYFLEX_ID') THEN
3626 hr_utility_trace('Inside the event on location or soft key flex');
3627 OPEN csr_get_asg_end_date (int_pkg_events(i).effective_date-1);
3628 FETCH csr_get_asg_end_date INTO ld_effective_date;
3629 CLOSE csr_get_asg_end_date;
3630 hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3631 END IF;
3632 hr_utility_trace('final ld_effective_date :' || ld_effective_date);
3633 IF event_qualified(p_person_id,
3634 p_assignment_id,
3635 ld_effective_date,
3636 p_gre_id) THEN
3637 lv_table_name :=
3638 dated_tbls(int_pkg_events(i).dated_table_id).table_name;
3639
3640 hr_utility_trace('-----------------------------------------------');
3641 hr_utility_trace('lv_table_name :' ||lv_table_name );
3642
3643
3644 IF lv_table_name = 'PER_ALL_ASSIGNMENTS_F' THEN
3645
3646 asg_count := asg_count + 1;
3647 asg_events(asg_count) := int_pkg_events(i);
3648
3649 ELSIF lv_table_name = 'PER_ALL_PEOPLE_F' THEN
3650
3651 per_count := per_count + 1;
3652 per_events(per_count) := int_pkg_events(i);
3653
3654 ELSIF lv_table_name = 'PAY_ELEMENT_ENTRIES_F' THEN
3655
3656 ele_count := ele_count + 1;
3657 ele_events(ele_count) := int_pkg_events(i);
3658
3659 ELSIF lv_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F' THEN
3660
3661 eev_count := eev_count + 1;
3662 eev_events(eev_count) := int_pkg_events(i);
3663
3664 END IF;
3665 END IF;
3666 END LOOP;
3667
3668 END IF;
3669
3670 ln_dummy_flag := get_ss_exclusion_dates(p_person_id
3671 ,lv_exclude_start_date_from_cur
3672 ,lv_exclude_end_date_from_cur);
3673
3674 IF asg_events.COUNT > 0 THEN
3675
3676 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3677 ln_step := 6;
3678
3679 arch_hire_separation ( p_payroll_action_id => p_payroll_action_id
3680 ,p_asg_action_id => p_asg_action_id
3681 ,p_effective_Date => p_effective_Date
3682 ,p_assignment_id => p_assignment_id
3683 ,p_person_id => p_person_id
3684 ,p_chunk_number => p_chunk_number
3685 ,p_start_date => p_start_date
3686 ,p_end_date => p_end_date
3687 ,p_business_group_id => p_business_group_id
3688 ,p_gre_id => p_gre_id
3689 ,p_eff_start_date => ld_eff_start_date
3690 ,p_eff_end_date => ld_eff_end_date
3691 ,p_exc_start_date => lv_exclude_start_date_from_cur
3692 ,p_exc_end_date => lv_exclude_end_date_from_cur
3693 ,p_asg_events => asg_events
3694 );
3695
3696 END IF;
3697
3698 IF gn_person_rec_chng = 0 AND per_events.COUNT > 0 THEN
3699
3700 hr_utility.set_location(gv_package || lv_procedure_name, 70);
3701 ln_step := 7;
3702
3703 chk_person_rec_chng( p_per_events => per_events );
3704
3705 END IF;
3706
3707 hr_utility.set_location(gv_package || lv_procedure_name, 80);
3708 ln_step := 8;
3709
3710 arch_other_transactions ( p_payroll_action_id => p_payroll_action_id
3711 ,p_asg_action_id => p_asg_action_id
3712 ,p_effective_Date => p_effective_Date
3713 ,p_assignment_id => p_assignment_id
3714 ,p_person_id => p_person_id
3715 ,p_chunk_number => p_chunk_number
3716 ,p_start_date => p_start_date
3717 ,p_end_date => p_end_date
3718 ,p_business_group_id => p_business_group_id
3719 ,p_gre_id => p_gre_id
3720 ,p_eff_start_date => ld_eff_start_date
3721 ,p_eff_end_date => ld_eff_end_date
3722 ,p_exc_start_date => lv_exclude_start_date_from_cur
3723 ,p_exc_end_date => lv_exclude_end_date_from_cur
3724 );
3725
3726 hr_utility.set_location(gv_package || lv_procedure_name, 90);
3727 ln_step := 9;
3728
3729 EXCEPTION
3730 WHEN others THEN
3731 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3732 gv_package || lv_procedure_name;
3733
3734 hr_utility_trace(lv_error_message || '-' || sqlerrm);
3735
3736 lv_error_message :=
3737 pay_emp_action_arch.set_error_message(lv_error_message);
3738
3739 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3740 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3741 hr_utility.raise_error;
3742
3743 END get_transactions;
3744
3745 PROCEDURE get_person_information( p_payroll_action_id IN NUMBER
3746 ,p_asg_action_id IN NUMBER
3747 ,p_effective_date IN DATE
3748 ,p_assignment_id IN NUMBER
3749 ,p_person_id IN NUMBER
3750 ,p_chunk_number IN NUMBER
3751 ,p_start_date IN DATE
3752 ,p_end_date IN DATE
3753 ,p_business_group_id IN NUMBER
3754 ,p_gre_id IN NUMBER ) IS
3755
3756 CURSOR c_person_detail (cp_person_id IN NUMBER
3757 , cp_effective_date IN DATE ) IS
3758 SELECT ppf.person_id person_id
3759 ,replace(ppf.per_information3,'-','') emp_ssnumber
3760 ,ppf.last_name paternal_last_name
3761 ,ppf.per_information1 maternal_last_name
3762 ,rtrim(ppf.first_name || ' ' || ppf.middle_names) emp_name
3763 ,ppf.per_information4 medical_center
3764 ,ppf.employee_number worker_id
3765 ,ppf.national_identifier curp
3766 ,ppf.per_information2 tax_rfc_id
3767 ,fnd_date.date_to_canonical(ppf.effective_start_date) hire_date
3768 FROM per_all_people_f ppf
3769 WHERE ppf.person_id = cp_person_id
3770 AND ppf.effective_start_date =
3771 ( SELECT max(ppf_in.effective_start_date)
3772 FROM per_all_people_f ppf_in
3773 WHERE ppf_in.person_id = ppf.person_id
3774 AND ppf_in.effective_start_date <= cp_effective_date);
3775
3776 CURSOR c_asg_detail ( cp_assignment_id IN NUMBER
3777 , cp_effective_date IN DATE/*
3778 , cp_start_date IN DATE
3779 , cp_end_date IN DATE*/ ) IS
3780 SELECT paf.location_id
3781 ,paf.soft_coding_keyflex_id
3782 ,substr(paf.employment_category,3,1) worker_type
3783 FROM per_all_assignments_f paf
3784 WHERE paf.assignment_id = cp_assignment_id
3785 AND cp_effective_date BETWEEN paf.effective_start_date
3786 AND paf.effective_end_date;
3787 /*AND paf.effective_start_date =
3788 ( SELECT max(paf_in.effective_start_date)
3789 FROM per_all_assignments_f paf_in
3790 WHERE paf_in.assignment_id = paf.assignment_id
3791 AND trunc(cp_end_date) >= paf_in.effective_start_date
3792 AND trunc(cp_start_date) <= paf_in.effective_end_date);*/
3793
3794 CURSOR c_work_schdl ( cp_soft_cod_kflx_id IN NUMBER ) IS
3795 SELECT hsc.segment6 salary_type
3796 ,puc.user_column_name work_schedule
3797 FROM hr_soft_coding_keyflex hsc,
3798 pay_user_columns puc
3799 WHERE hsc.soft_coding_keyflex_id = cp_soft_cod_kflx_id
3800 AND hsc.segment4 = puc.user_column_id(+);
3801
3802 CURSOR c_location ( cp_location_id IN NUMBER ) IS
3803 SELECT location_code
3804 FROM hr_locations_all
3805 WHERE location_id = cp_location_id;
3806
3807 CURSOR csr_asg_dates IS
3808 SELECT paf.effective_start_date,
3809 paf.effective_end_date
3810 FROM per_assignments_f paf
3811 WHERE paf.assignment_id = p_assignment_id
3812 AND paf.effective_start_date = (SELECT max(paf_in.effective_start_date)
3813 FROM per_assignments_f paf_in
3814 WHERE paf_in.assignment_id =
3815 paf.assignment_id
3816 -- Bug 5908010
3817 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
3818 paf_in.location_id,
3819 paf_in.business_group_id,
3820 paf_in.soft_coding_keyflex_id,
3821 paf_in.effective_start_date) = p_gre_id);
3822
3823 CURSOR c_ee_for_infonavit( cp_person_id NUMBER
3824 ,cp_effective_date DATE ) IS
3825 SELECT pee.element_entry_id
3826 ,pee.element_type_id
3827 FROM pay_element_entries_f pee
3828 ,per_all_assignments_f paf
3829 WHERE paf.person_id = cp_person_id
3830 AND cp_effective_date BETWEEN paf.effective_start_date
3831 AND paf.effective_end_date
3832 AND pee.assignment_id = paf.assignment_id + 0
3833 AND cp_effective_date BETWEEN pee.effective_start_date
3834 AND pee.effective_end_date
3835 AND EXISTS ( SELECT 1
3836 FROM pay_element_type_extra_info petei
3837 WHERE petei.information_type = 'MX_DEDUCTION_PROCESSING'
3838 AND petei.eei_information_category =
3839 'MX_DEDUCTION_PROCESSING'
3840 AND petei.eei_information1 = 'INFONAVIT'
3841 AND petei.element_type_id = pee.element_type_id )
3842 ORDER BY pee.effective_start_date desc;
3843
3844 CURSOR c_infonavit( cp_element_type_id NUMBER
3845 ,cp_element_entry_id NUMBER
3846 ,cp_effective_date DATE ) IS
3847 SELECT piv.name, peev.screen_entry_value
3848 FROM pay_element_entry_values_f peev
3849 ,pay_input_values_f piv
3850 WHERE piv.element_type_id = cp_element_type_id
3851 AND peev.element_entry_id = cp_element_entry_id
3852 AND piv.input_value_id = peev.input_value_id
3853 AND cp_effective_date BETWEEN piv.effective_start_date
3854 AND piv.effective_end_date;
3855
3856 ln_person_id NUMBER;
3857 lv_end_date VARCHAR2(30);
3858 lv_start_date VARCHAR2(30);
3859 ln_bus_grp_id NUMBER;
3860 ln_gre_id NUMBER;
3861 lv_emp_ssn VARCHAR2(240);
3862 lv_tax_rfc_id VARCHAR2(240);
3863 lv_curp VARCHAR2(240);
3864 lv_paternal_last_name VARCHAR2(240);
3865 lv_maternal_last_name VARCHAR2(240);
3866 lv_emp_name VARCHAR2(240);
3867 lv_worker_type VARCHAR2(240);
3868 lv_red_work_week_ind VARCHAR2(240);
3869 lv_hire_date VARCHAR2(240);
3870 lv_location_code VARCHAR2(240);
3871 ln_infonavit_crdt_no NUMBER;
3872 ln_infonavit_strt_dt DATE;
3873 ld_asg_start_date DATE;
3874 ld_asg_end_date DATE;
3875 ld_effective_date DATE;
3876 ln_infonavit_disc_type VARCHAR2(240);
3877 ln_infonavit_disc_val NUMBER;
3878 lv_daily_base_wage VARCHAR2(240);
3879 lv_salary_type VARCHAR2(240);
3880 lv_medical_center VARCHAR2(240);
3881 lv_worker_id VARCHAR2(240);
3882
3883 ln_location_id NUMBER;
3884 ln_soft_cod_kflx_id NUMBER;
3885 lv_work_schedule VARCHAR2(240);
3886
3887 ln_min_wage NUMBER;
3888
3889 ln_index NUMBER;
3890 ln_idw NUMBER;
3891 ln_fixed_idw NUMBER;
3892 ln_variable_idw NUMBER;
3893
3894 ln_element_entry_id NUMBER;
3895 ln_element_type_id NUMBER;
3896
3897 lv_procedure_name VARCHAR2(100);
3898 lv_error_message VARCHAR2(2000);
3899 ln_step NUMBER;
3900 ln_check_person_info_exist NUMBER;
3901
3902 BEGIN
3903
3904 lv_procedure_name := 'get_person_information';
3905
3906 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3907 ln_step := 1;
3908
3909 hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3910 hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3911 hr_utility_trace('p_person_id : ' ||p_person_id );
3912
3913 OPEN csr_asg_dates;
3914 FETCH csr_asg_dates INTO ld_asg_start_date,
3915 ld_asg_end_date;
3916 CLOSE csr_asg_dates;
3917
3918 -- Bug 5875096
3919 IF ld_asg_start_date <= p_end_date THEN
3920 ld_effective_date := LEAST (ld_asg_end_date, p_end_date);
3921 ELSE
3922 ld_effective_date := ld_asg_start_date;
3923 END IF;
3924 /*ld_effective_date := max (ld_effective_date, p_end_date);
3925 hr_utility_trace('p_end_date = '||
3926 fnd_date.date_to_canonical(p_end_date));*/
3927 hr_utility_trace('ld_asg_start_date = '||
3928 fnd_date.date_to_canonical(ld_asg_start_date));
3929 hr_utility_trace('ld_asg_end_date = '||
3930 fnd_date.date_to_canonical(ld_asg_end_date));
3931 hr_utility_trace('p_end_date = '||
3932 fnd_date.date_to_canonical(p_end_date));
3933 hr_utility_trace('ld_effective_date = '||
3934 fnd_date.date_to_canonical(ld_effective_date));
3935
3936 OPEN c_person_detail (p_person_id
3937 ,ld_effective_date);
3938 --,p_effective_date);
3939
3940 FETCH c_person_detail INTO ln_person_id
3941 ,lv_emp_ssn
3942 ,lv_paternal_last_name
3943 ,lv_maternal_last_name
3944 ,lv_emp_name
3945 ,lv_medical_center
3946 ,lv_worker_id
3947 ,lv_curp
3948 ,lv_tax_rfc_id
3949 ,lv_hire_date;
3950 CLOSE c_person_detail;
3951
3952 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3953 ln_step := 2;
3954
3955 OPEN c_asg_detail( p_assignment_id
3956 ,ld_effective_date/*
3957 ,p_start_date
3958 ,p_end_date */);
3959 FETCH c_asg_detail INTO ln_location_id
3960 ,ln_soft_cod_kflx_id
3961 ,lv_worker_type;
3962 CLOSE c_asg_detail;
3963
3964 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3965 ln_step := 3;
3966
3967 OPEN c_work_schdl( ln_soft_cod_kflx_id );
3968 FETCH c_work_schdl INTO lv_salary_type
3969 ,lv_work_schedule;
3970 CLOSE c_work_schdl;
3971
3972 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3973 ln_step := 4;
3974
3975 OPEN c_location (ln_location_id);
3976 FETCH c_location INTO lv_location_code;
3977 CLOSE c_location;
3978
3979 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3980 ln_step := 5;
3981
3982 -- derive Reduced Working-week indicator from workschedule
3983
3984 IF lv_work_schedule IS NOT NULL THEN
3985
3986 get_rww_ind( p_business_group_id
3987 ,lv_work_schedule
3988 ,lv_red_work_week_ind );
3989
3990 ELSE
3991
3992 lv_red_work_week_ind := NULL ;
3993
3994 END IF;
3995
3996 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3997 ln_step := 6;
3998
3999 -- Bug 5146225
4000 get_payroll_action_info (p_payroll_action_id,
4001 lv_end_date,
4002 lv_start_date,
4003 ln_bus_grp_id,
4004 ln_gre_id);
4005
4006 -- Calculate IDW on LEAST(assignment's end date, process end date)
4007 /*OPEN csr_asg_end_date;
4008 FETCH csr_asg_end_date INTO ld_asg_end_date;
4009 CLOSE csr_asg_end_date;
4010
4011 lv_end_date := fnd_date.date_to_canonical(
4012 LEAST(ld_asg_end_date,
4013 fnd_date.canonical_to_date(lv_end_date)));*/
4014
4015 SELECT count(*) into ln_check_person_info_exist
4016 FROM pay_action_information
4017 WHERE action_context_type='AAP'
4018 AND assignment_id = p_assignment_id
4019 AND tax_unit_id = p_gre_id
4020 AND action_information_category = 'MX SS PERSON INFORMATION'
4021 AND ld_asg_start_date >=fnd_date.canonical_to_date(action_information10);
4022
4023 hr_utility.trace('ln_check_person_info_exist '|| ln_check_person_info_exist);
4024 IF ln_check_person_info_exist > 0 THEN
4025
4026 /*bug9128410: If the person is processing first time along with new hire(08) transaction
4027 then the effective date for the idw calculation is assignment start date otherwise it
4028 should be the effective date of current process. This change has done to make the
4029 person info idw sinc with new hire transaction*/
4030
4031 ln_idw := get_idw( p_assignment_id => p_assignment_id /*bug9128410*/
4032 ,p_tax_unit_id => p_gre_id
4033 ,p_effective_date => ld_effective_date
4034 ,p_fixed_idw => ln_fixed_idw
4035 ,p_variable_idw => ln_variable_idw );
4036 ELSE
4037 ln_idw := get_idw( p_assignment_id => p_assignment_id /*bug9128410*/
4038 ,p_tax_unit_id => p_gre_id
4039 ,p_effective_date => ld_asg_start_date
4040 ,p_fixed_idw => ln_fixed_idw
4041 ,p_variable_idw => ln_variable_idw );
4042 END IF;
4043
4044 hr_utility.trace('SS_ARCH PERSON_INFO ln_idw: '|| ln_idw);
4045 hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_fixed_idw: '||
4046 ln_fixed_idw);
4047 hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
4048 ln_variable_idw);
4049
4050 hr_utility.set_location(gv_package || lv_procedure_name, 70);
4051 ln_step := 7;
4052
4053 IF ( gv_credit_no IS NULL AND gv_credit_start_date IS NULL ) THEN
4054
4055 ln_element_entry_id := NULL;
4056 ln_element_type_id := NULL;
4057
4058 OPEN c_ee_for_infonavit( p_person_id
4059 ,p_end_date );
4060 FETCH c_ee_for_infonavit INTO ln_element_entry_id
4061 ,ln_element_type_id;
4062 CLOSE c_ee_for_infonavit;
4063
4064 IF ln_element_entry_id IS NOT NULL THEN
4065
4066 FOR infonavit IN c_infonavit (ln_element_type_id
4067 ,ln_element_entry_id
4068 ,p_end_date)
4069 LOOP
4070
4071 hr_utility_trace('name : '|| infonavit.name);
4072 hr_utility_trace('screen_entry_value : '||
4073 infonavit.screen_entry_value);
4074
4075 IF infonavit.name = 'Credit Number' THEN
4076
4077 gv_credit_no := infonavit.screen_entry_value;
4078
4079 ELSIF infonavit.name = 'Credit Start Date' THEN
4080
4081 gv_credit_start_date := infonavit.screen_entry_value;
4082
4083 ELSIF infonavit.name = 'Discount Type' THEN
4084
4085 gv_discount_type := infonavit.screen_entry_value;
4086
4087 ELSIF infonavit.name = 'Discount Value' THEN
4088
4089 gv_discount_value := infonavit.screen_entry_value;
4090
4091 END IF;
4092
4093 END LOOP; -- infonavit
4094
4095 END IF;
4096
4097 END IF;
4098
4099 hr_utility.set_location(gv_package || lv_procedure_name, 80);
4100 ln_step := 8;
4101
4102 ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
4103
4104 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category
4105 := 'MX SS PERSON INFORMATION';
4106 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code := NULL;
4107 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1 := ln_person_id;
4108 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2 := lv_emp_ssn;
4109 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info3 := lv_tax_rfc_id;
4110 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4 := lv_curp;
4111 /*Bug:9820914: Handling Spcial Chars in Employee Name */
4112 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info5
4113 := replace(pay_mx_rules.strip_spl_chars(lv_paternal_last_name),'/','N');
4114 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info6
4115 := replace(pay_mx_rules.strip_spl_chars(lv_maternal_last_name),'/','N');
4116 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info7
4117 := replace(pay_mx_rules.strip_spl_chars(lv_emp_name),'/','N');
4118 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info8 := lv_worker_type;
4119 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info9
4120 := lv_red_work_week_ind;
4121 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info10 := lv_hire_date;
4122 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info11
4123 := to_char(ln_idw,'99999.99');
4124 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info12 := lv_location_code;
4125 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info13 := gv_credit_no;
4126 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info14
4127 := gv_credit_start_date;
4128 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info15 := gv_discount_type;
4129 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info16
4130 := gv_discount_value;
4131 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info17 := NULL;
4132 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info18 := lv_salary_type;
4133 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info19
4134 := lv_medical_center;
4135 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info20 := lv_worker_id;
4136 -- Segment 21 is the 'Do Not Report on Magtape' flag
4137 pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info22 := gv_crdt_grant_dt;
4138
4139 hr_utility.set_location(gv_package || lv_procedure_name, 90);
4140
4141 EXCEPTION
4142 WHEN others THEN
4143 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4144 gv_package || lv_procedure_name;
4145
4146 hr_utility_trace(lv_error_message || '-' || sqlerrm);
4147
4148 lv_error_message :=
4149 pay_emp_action_arch.set_error_message(lv_error_message);
4150
4151 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4152 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4153 hr_utility.raise_error;
4154
4155 END get_person_information;
4156
4157
4158 /*****************************************************************************
4159 Name : get_IDW_calc_method
4160 Purpose : This returns the IDW calculation method captured at GRE EIT.
4161 *****************************************************************************/
4162 FUNCTION get_IDW_calc_method(p_org_id IN NUMBER
4163 ,p_effective_date IN DATE) RETURN VARCHAR2 IS
4164 CURSOR c_get_idw_calc_method IS
4165 SELECT hoi.org_information10
4166 FROM hr_organization_units hou,
4167 hr_organization_information hoi
4168 WHERE hou.organization_id = p_org_id
4169 AND hoi.org_information_context ='MX_SOC_SEC_DETAILS'
4170 AND hou.organization_id = hoi.organization_id
4171 AND p_effective_date BETWEEN hou.date_from
4172 AND nvl(hou.date_to,p_effective_date);
4173
4174 lv_idw_calc_method hr_organization_information.org_information10%type;
4175 BEGIN
4176 OPEN c_get_idw_calc_method;
4177 FETCH c_get_idw_calc_method INTO lv_idw_calc_method;
4178 CLOSE c_get_idw_calc_method;
4179
4180 hr_utility_trace ('IDW calculation method = '||lv_idw_calc_method);
4181 RETURN (lv_idw_calc_method);
4182 END get_IDW_calc_method;
4183
4184 /*****************************************************************************
4185 Name : seniority_changed
4186 Purpose : This returns 'Y' if passed person crossed anniversary date since
4187 last archiver run.
4188 *****************************************************************************/
4189 FUNCTION seniority_changed(p_person_id IN NUMBER
4190 ,p_curr_date IN DATE
4191 ,p_prev_date IN DATE) RETURN VARCHAR2 IS
4192 ld_hire_anniversary DATE;
4193 BEGIN
4194 ld_hire_anniversary := hr_mx_utility.get_hire_anniversary(p_person_id,
4195 p_curr_date);
4196 -- Bug 6005922
4197 IF CEIL((GREATEST(p_prev_date,ld_hire_anniversary+1)-ld_hire_anniversary)/365) =
4198 CEIL((GREATEST(p_curr_date,ld_hire_anniversary+1)-ld_hire_anniversary)/365) THEN
4199 RETURN ('N');
4200 ELSE
4201 RETURN ('Y');
4202 END IF;
4203 END seniority_changed;
4204
4205 /*****************************************************************************
4206 Name : validate_person_id
4207 Purpose : This returns 'Y' if the if the person is included from the process
4208 *****************************************************************************/
4209 FUNCTION validate_person_id (p_person_id IN NUMBER,
4210 p_assignment_id IN NUMBER,
4211 p_start_date IN DATE,
4212 p_end_date IN DATE,
4213 p_periodic_start_date IN DATE,
4214 p_periodic_end_date IN DATE,
4215 p_payroll_action_id IN NUMBER,
4216 p_gre_id IN NUMBER,
4217 p_business_group_id IN NUMBER,
4218 p_vidw_check IN VARCHAR2)
4219 RETURN VARCHAR2 IS
4220 l_count NUMBER;
4221 l_char VARCHAR2(1);
4222 lv_exists VARCHAR2(1);
4223 lv_prev_action_exists VARCHAR2(1);
4224 lv_procedure_name VARCHAR2(100);
4225 ln_step NUMBER;
4226 lv_error_message VARCHAR2(2000);
4227
4228 CURSOR c_chk_pay_process_events IS
4229 SELECT 'Y'
4230 FROM pay_process_events ppe
4231 WHERE ppe.assignment_id = p_assignment_id
4232 AND ppe.creation_date BETWEEN p_start_date AND p_end_date
4233 AND rownum = 1;
4234
4235 CURSOR c_chk_elements IS
4236 select 'Y'
4237 from pay_element_entries_f pee
4238 ,pay_sub_classification_rules_f psc
4239 ,pay_element_classifications pec
4240 ,pay_assignment_actions paa
4241 ,pay_payroll_actions ppa2
4242 WHERE pee.assignment_id = p_assignment_id
4243 AND pee.effective_start_date <= p_periodic_end_date
4244 AND pee.effective_end_date >= p_periodic_start_date
4245 AND psc.business_group_id = p_business_group_id
4246 AND psc.element_type_id = pee.element_type_id
4247 AND psc.effective_start_date <= p_periodic_end_date
4248 AND psc.effective_end_date >= p_periodic_start_date
4249 AND pec.classification_id = psc.classification_id
4250 AND pec.classification_name LIKE '%Eligible Compensation for IDW (Variable Basis)'
4251 AND paa.assignment_id = p_assignment_id
4252 AND ppa2.payroll_action_id =paa.payroll_action_id
4253 AND ppa2.effective_date BETWEEN p_periodic_start_date AND p_periodic_end_date
4254 AND ppa2.action_type in ('R', 'Q', 'B', 'V' )
4255 AND EXISTS ( SELECT 1
4256 FROM pay_run_results prr
4257 WHERE prr.assignment_action_id = paa.assignment_action_id
4258 AND prr.element_type_id = pee.element_type_id)
4259 AND ROWNUM = 1;
4260
4261
4262 CURSOR chk_prev_action IS
4263 SELECT 'Y'
4264 FROM pay_payroll_actions ppa_prev
4265 ,pay_assignment_actions paa_prev
4266 WHERE ppa_prev.report_type = 'SS_ARCHIVE'
4267 AND ppa_prev.report_qualifier = 'SS_ARCHIVE'
4268 AND ppa_prev.report_category = 'RT'
4269 AND pay_mx_utility.get_legi_param_val('GRE',
4270 ppa_prev.legislative_parameters) = p_gre_id
4271 AND TRUNC( fnd_date.canonical_to_date (
4272 pay_mx_utility.get_legi_param_val(
4273 'PERIOD_ENDING_DATE',
4274 ppa_prev.legislative_parameters) ) ) =
4275 TRUNC(p_periodic_end_date)
4276 AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
4277 AND paa_prev.assignment_id = p_assignment_id
4278 AND pay_mx_utility.get_legi_param_val('MX_IDWV',
4279 paa_prev.serial_number) = 'Y';
4280
4281
4282 BEGIN
4283 lv_procedure_name := 'validate_person_id';
4284 ln_step := 1;
4285 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4286
4287 IF (p_vidw_check = 'N') THEN
4288 OPEN c_chk_pay_process_events;
4289 FETCH c_chk_pay_process_events INTO lv_exists;
4290 IF c_chk_pay_process_events%FOUND THEN
4291 CLOSE c_chk_pay_process_events;
4292 RETURN lv_exists;
4293 END IF;
4294 CLOSE c_chk_pay_process_events;
4295 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4296
4297 IF gv_IDW_calc_method = 'B' THEN
4298 lv_exists := pay_mx_soc_sec_archive.seniority_changed (p_person_id,
4299 p_end_date,
4300 p_start_date);
4301 IF lv_exists = 'Y' THEN
4302 RETURN lv_exists;
4303 ELSE
4304 lv_exists :='N';
4305 END IF;
4306 END IF;
4307 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4308 END IF;
4309
4310 IF (p_vidw_check = 'Y') THEN
4311 hr_utility.set_location(gv_package || lv_procedure_name, 40);
4312 OPEN chk_prev_action;
4313 FETCH chk_prev_action INTO lv_prev_action_exists;
4314 IF chk_prev_action%FOUND THEN
4315 lv_exists:= 'N';
4316 CLOSE chk_prev_action;
4317 RETURN lv_exists;
4318 ELSE
4319 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4320 CLOSE chk_prev_action;
4321 OPEN c_chk_elements;
4322 FETCH c_chk_elements INTO lv_exists;
4323 IF c_chk_elements%FOUND THEN --
4324 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4325 lv_exists:= 'Y';
4326 CLOSE c_chk_elements;
4327 RETURN lv_exists;
4328 ELSE
4329 hr_utility.set_location(gv_package || lv_procedure_name, 70);
4330 CLOSE c_chk_elements;
4331 lv_exists := 'N';
4332 RETURN lv_exists;
4333 END IF; --c_chk_elements
4334 END IF; --chk_prev_action
4335 ELSE
4336 lv_exists := 'Y';
4337 END IF; --p_report_mode
4338 hr_utility.trace('lv_exists :'||lv_exists);
4339 RETURN lv_exists;
4340 EXCEPTION
4341 WHEN others THEN
4342 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4343 gv_package || lv_procedure_name;
4344
4345 hr_utility_trace(lv_error_message || '-' || sqlerrm);
4346
4347 lv_error_message :=
4348 pay_emp_action_arch.set_error_message(lv_error_message);
4349
4350 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4351 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4352 hr_utility.raise_error;
4353 END validate_person_id;
4354
4355 PROCEDURE range_cursor( p_payroll_action_id IN NUMBER
4356 ,p_sqlstr OUT NOCOPY VARCHAR2) IS
4357
4358
4359 lv_procedure_name VARCHAR2(200);
4360 lv_end_date VARCHAR2(19);
4361 lv_start_date VARCHAR2(19);
4362 ln_business_group_id hr_organization_units.organization_id%TYPE;
4363 ln_gre_id hr_organization_units.organization_id%TYPE;
4364 ln_pactid NUMBER;
4365 lv_msg_txt VARCHAR2(250); -- Bug 13357684
4366 lv_gre_name VARCHAR2(200); -- Bug 13357684
4367
4368 BEGIN
4369
4370 lv_procedure_name := 'range_cursor';
4371
4372 hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
4373
4374 hr_utility_trace('Starting range_cursor ');
4375 hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4376 hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
4377 hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4378
4379
4380 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
4381 ,p_end_date => lv_end_date
4382 ,p_start_date => lv_start_date
4383 ,p_business_group_id => ln_business_group_id
4384 ,p_gre_id => ln_gre_id
4385 );
4386
4387 hr_utility_trace('lv_start_date :' || lv_start_date);
4388 hr_utility_trace('lv_end_date :' || lv_end_date);
4389 hr_utility_trace('ln_business_group_id :' || ln_business_group_id);
4390
4391 IF pay_mx_soc_sec_archive.arch_exists_without_upgrade(ln_business_group_id)
4392 = 'B' THEN
4393 pay_generic_upgrade.new_business_group (
4394 p_bus_grp_id => ln_business_group_id,
4395 p_leg_code => NULL);
4396 END IF;
4397
4398 gv_IDW_calc_method := get_IDW_calc_method (
4399 ln_gre_id,
4400 fnd_date.canonical_to_date (lv_end_date));
4401
4402 hr_utility_trace('--> gv_IDW_calc_method : ' || gv_IDW_calc_method );
4403 /*
4404 IF gv_mode = 'P' THEN
4405
4406 gv_periodic_start_date :=
4407 fnd_date.date_to_canonical(
4408 TRUNC(add_months(fnd_date.canonical_to_date(lv_end_date),-2)+1));
4409 -- ||' 00:00:00';
4410
4411 ELSE
4412
4413 gv_periodic_start_date := lv_start_date;
4414
4415 END IF;
4416
4417 hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
4418 hr_utility_trace('gv_periodic_end_date :' || gv_periodic_end_date);
4419 */
4420
4421 ln_pactid := p_payroll_action_id;
4422
4423 SELECT COUNT(*)
4424 INTO gn_implementation
4425 FROM pay_payroll_actions
4426 WHERE report_type = 'SS_ARCHIVE'
4427 AND report_qualifier = 'SS_ARCHIVE'
4428 AND report_category = 'RT'
4429 AND pay_mx_utility.get_legi_param_val('GRE', legislative_parameters )
4430 = ln_gre_id
4431 AND payroll_action_id + 0 < p_payroll_action_id;
4432
4433 /****************************************************************
4434 ** gn_implementation is used to check whether archiver
4435 ** has already been run before or not.
4436 ** IF not (gn_implementation = 0) then it should archive person
4437 ** information for all employees and transaction it there is any.
4438 ** IF yes (gn_implementation > 0), it should arrchive only
4439 ** transaction if there is any event occured.
4440 **************************************************************/
4441 hr_utility_trace('--> gn_implementation : ' || gn_implementation );
4442
4443 IF gn_implementation = 0 THEN
4444
4445 p_sqlstr :=
4446 'SELECT DISTINCT paf.person_id
4447 FROM per_assignments_f paf,
4448 pay_payroll_actions ppa
4449 WHERE ppa.payroll_action_id = :p_payroll_action_id
4450 AND paf.business_group_id = ppa.business_group_id
4451 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4452 paf.location_id
4453 ,paf.business_group_id
4454 ,paf.soft_coding_keyflex_id
4455 ,ppa.effective_date) = '||ln_gre_id|| ' '||
4456 'AND ppa.effective_date BETWEEN paf.effective_start_date
4457 AND paf.effective_end_date
4458 ORDER BY paf.person_id';
4459
4460 ELSE
4461 /*bug:15839415*/
4462
4463 p_sqlstr :=
4464 'SELECT DISTINCT person_id FROM ( '||
4465 'SELECT paf.person_id
4466 FROM per_assignments_f paf
4467 ,pay_payroll_actions ppa
4468 WHERE ppa.payroll_action_id = :p_payroll_action_id
4469 AND paf.business_group_id = ppa.business_group_id
4470 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4471 paf.location_id
4472 ,paf.business_group_id
4473 ,paf.soft_coding_keyflex_id
4474 ,ppa.effective_date) = '||ln_gre_id|| ') '||
4475 /*'AND '''|| gv_mode || ''' = ''P'' ) ' ||*/
4476 'ORDER BY person_id';
4477
4478 END IF; -- gn_implementation = 0
4479 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4480
4481 hr_utility_trace('--> Query formed p_sqlstr : ' || p_sqlstr );
4482
4483 update pay_payroll_actions
4484 set effective_date = fnd_date.canonical_to_date('4712/12/31')
4485 where payroll_action_id = p_payroll_action_id;
4486
4487 /*Begin - Bug 13357684*/
4488 select name
4489 into lv_gre_name
4490 from hr_all_organization_units
4491 where organization_id = ln_gre_id;
4492
4493 lv_msg_txt := 'This Social Security Archiver was run for GRE '|| lv_gre_name || ' and period from ' || lv_start_date ||' to '|| lv_end_date;
4494
4495 pay_core_utils.push_message(p_applid => 800
4496 ,p_msg_name => NULL
4497 ,p_msg_txt => lv_msg_txt
4498 ,p_level => 'F');
4499
4500 /*End - Bug 13357684*/
4501
4502 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4503
4504 END range_cursor;
4505
4506 PROCEDURE action_creation( p_payroll_action_id IN NUMBER
4507 ,p_start_person_id IN NUMBER
4508 ,p_end_person_id IN NUMBER
4509 ,p_chunk IN NUMBER) IS
4510
4511 /* CURSOR c_get_emp( cp_payroll_action_id IN NUMBER
4512 ,cp_start_person_id IN NUMBER
4513 ,cp_end_person_id IN NUMBER
4514 ,cp_business_group_id IN NUMBER
4515 ,cp_gre_id IN NUMBER
4516 ,cp_start_date IN DATE
4517 ,cp_end_date IN DATE
4518 ,cp_periodic_start_date IN DATE
4519 ,cp_periodic_end_date IN DATE) IS
4520 SELECT paf.person_id
4521 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4522 ,paf.assignment_id
4523 ,'N' variable_idw
4524 FROM pay_payroll_actions ppa
4525 ,per_assignments_f paf
4526 WHERE ppa.payroll_action_id = cp_payroll_action_id
4527 AND ppa.business_group_id = cp_business_group_id
4528 AND paf.business_group_id = ppa.business_group_id
4529 AND paf.person_id BETWEEN cp_start_person_id
4530 AND cp_end_person_id
4531 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4532 paf.location_id
4533 ,paf.business_group_id
4534 ,paf.soft_coding_keyflex_id
4535 ,ppa.effective_date) = cp_gre_id
4536 UNION ALL
4537 SELECT paf.person_id
4538 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4539 ,paf.assignment_id
4540 ,'Y' variable_idw
4541 FROM per_assignments_f paf
4542 ,pay_payroll_actions ppa
4543 ,pay_element_entries_f pee
4544 ,pay_sub_classification_rules_f psc
4545 ,pay_element_classifications pec
4546 ,pay_assignment_actions paa
4547 ,pay_payroll_actions ppa2
4548 WHERE ppa.payroll_action_id = cp_payroll_action_id
4549 AND paf.business_group_id = ppa.business_group_id
4550 AND paf.person_id BETWEEN cp_start_person_id
4551 AND cp_end_person_id
4552 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4553 paf.location_id
4554 ,paf.business_group_id
4555 ,paf.soft_coding_keyflex_id
4556 ,ppa.effective_date) = cp_gre_id
4557 AND pee.assignment_id = paf.assignment_id
4558 AND pee.effective_start_date <= cp_periodic_end_date
4559 AND pee.effective_end_date >= cp_periodic_start_date
4560 AND psc.business_group_id = ppa.business_group_id
4561 AND psc.element_type_id = pee.element_type_id
4562 AND psc.effective_start_date <= cp_periodic_end_date
4563 AND psc.effective_end_date >= cp_periodic_start_date
4564 AND pec.classification_id = psc.classification_id
4565 AND pec.classification_name LIKE
4566 '%Eligible Compensation for IDW (Variable Basis)'
4567 AND paa.assignment_id = paf.assignment_id
4568 AND ppa2.payroll_action_id = paa.payroll_action_id
4569 AND ppa2.effective_date BETWEEN cp_periodic_start_date
4570 AND cp_periodic_end_date
4571 AND ppa2.action_type in ( 'R', 'Q', 'B', 'V' )
4572 AND EXISTS (SELECT 1 FROM pay_run_results prr
4573 WHERE prr.assignment_action_id = paa.assignment_action_id
4574 AND prr.element_type_id = pee.element_type_id )
4575 AND NOT EXISTS (
4576 SELECT 1
4577 FROM pay_payroll_actions ppa_prev
4578 ,pay_assignment_actions paa_prev
4579 WHERE ppa_prev.report_type = 'SS_ARCHIVE'
4580 AND ppa_prev.report_qualifier = 'SS_ARCHIVE'
4581 AND ppa_prev.report_category = 'RT'
4582 AND pay_mx_utility.get_legi_param_val('GRE',
4583 ppa_prev.legislative_parameters) = cp_gre_id
4584 AND TRUNC( fnd_date.canonical_to_date (
4585 pay_mx_utility.get_legi_param_val( 'PERIOD_ENDING_DATE',
4586 ppa_prev.legislative_parameters) ) ) =
4587 TRUNC(fnd_date.canonical_to_date(cp_periodic_end_date))
4588 AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
4589 AND paa_prev.assignment_id = paf.assignment_id
4590 AND pay_mx_utility.get_legi_param_val('MX_IDWV',
4591 paa_prev.serial_number) = 'Y' )
4592 AND gv_mode = 'P'
4593 ORDER BY 1, 2, 3, 4 desc;
4594
4595 CURSOR c_get_range_emp(
4596 cp_payroll_action_id IN NUMBER
4597 ,cp_chunk_number IN NUMBER
4598 ,cp_business_group_id IN NUMBER
4599 ,cp_gre_id IN NUMBER
4600 ,cp_start_date IN DATE
4601 ,cp_end_date IN DATE
4602 ,cp_periodic_start_date IN DATE
4603 ,cp_periodic_end_date IN DATE) IS
4604 SELECT paf.person_id
4605 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4606 ,paf.assignment_id
4607 ,'N' variable_idw
4608 FROM pay_payroll_actions ppa
4609 ,per_assignments_f paf
4610 ,pay_population_ranges ppr
4611 WHERE ppa.payroll_action_id = cp_payroll_action_id
4612 AND ppr.payroll_action_id = ppa.payroll_action_id
4613 AND ppr.chunk_number = cp_chunk_number
4614 AND paf.person_id = ppr.person_id
4615 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4616 paf.location_id
4617 ,paf.business_group_id
4618 ,paf.soft_coding_keyflex_id
4619 ,ppa.effective_date) = cp_gre_id
4620 UNION ALL
4621 SELECT paf.person_id
4622 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4623 ,paf.assignment_id
4624 ,'Y' variable_idw
4625 FROM pay_payroll_actions ppa
4626 ,per_assignments_f paf
4627 ,pay_population_ranges ppr
4628 ,pay_element_entries_f pee
4629 ,pay_sub_classification_rules_f psc
4630 ,pay_element_classifications pec
4631 ,pay_assignment_actions paa
4632 ,pay_payroll_actions ppa2
4633 WHERE ppa.payroll_action_id = cp_payroll_action_id
4634 AND ppr.payroll_action_id = ppa.payroll_action_id
4635 AND ppr.chunk_number = cp_chunk_number
4636 AND paf.person_id = ppr.person_id
4637 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4638 paf.location_id
4639 ,paf.business_group_id
4640 ,paf.soft_coding_keyflex_id
4641 ,ppa.effective_date) = cp_gre_id
4642 AND pee.assignment_id = paf.assignment_id
4643 AND pee.effective_start_date <= cp_periodic_end_date
4644 AND pee.effective_end_date >= cp_periodic_start_date
4645 AND psc.business_group_id = ppa.business_group_id
4646 AND psc.element_type_id = pee.element_type_id
4647 AND psc.effective_start_date <= cp_periodic_end_date
4648 AND psc.effective_end_date >= cp_periodic_start_date
4649 AND pec.classification_id = psc.classification_id
4650 AND pec.classification_name LIKE
4651 '%Eligible Compensation for IDW (Variable Basis)'
4652 AND paa.assignment_id = paf.assignment_id
4653 AND ppa2.payroll_action_id = paa.payroll_action_id
4654 AND ppa2.effective_date BETWEEN cp_periodic_start_date
4655 AND cp_periodic_end_date
4656 AND ppa2.action_type in ( 'R', 'Q', 'B', 'V' )
4657 AND EXISTS (SELECT 1 FROM pay_run_results prr
4658 WHERE prr.assignment_action_id = paa.assignment_action_id
4659 AND prr.element_type_id = pee.element_type_id )
4660 AND NOT EXISTS (
4661 SELECT 1
4662 FROM pay_payroll_actions ppa_prev
4663 ,pay_assignment_actions paa_prev
4664 WHERE ppa_prev.report_type = 'SS_ARCHIVE'
4665 AND ppa_prev.report_qualifier = 'SS_ARCHIVE'
4666 AND ppa_prev.report_category = 'RT'
4667 AND pay_mx_utility.get_legi_param_val('GRE',
4668 ppa_prev.legislative_parameters) = cp_gre_id
4669 AND TRUNC( fnd_date.canonical_to_date (
4670 pay_mx_utility.get_legi_param_val( 'PERIOD_ENDING_DATE',
4671 ppa_prev.legislative_parameters) ) ) =
4672 TRUNC(fnd_date.canonical_to_date(cp_periodic_end_date))
4673 AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
4674 AND paa_prev.assignment_id = paf.assignment_id
4675 AND pay_mx_utility.get_legi_param_val('MX_IDWV',
4676 paa_prev.serial_number) = 'Y' )
4677 AND gv_mode = 'P'
4678 ORDER BY 1, 2, 3, 4 desc;*/
4679 -- bug:15839415 start
4680 CURSOR c_get_emp( cp_payroll_action_id IN NUMBER
4681 ,cp_start_person_id IN NUMBER
4682 ,cp_end_person_id IN NUMBER
4683 ,cp_business_group_id IN NUMBER
4684 ,cp_gre_id IN NUMBER
4685 ,cp_start_date IN DATE
4686 ,cp_end_date IN DATE
4687 ,cp_periodic_start_date IN DATE
4688 ,cp_periodic_end_date IN DATE) IS
4689 SELECT paf.person_id
4690 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4691 ,paf.assignment_id
4692 ,'N' variable_idw
4693 FROM pay_payroll_actions ppa
4694 ,per_assignments_f paf
4695 WHERE ppa.payroll_action_id = cp_payroll_action_id
4696 AND ppa.business_group_id = cp_business_group_id
4697 AND paf.business_group_id = ppa.business_group_id
4698 AND paf.person_id BETWEEN cp_start_person_id
4699 AND cp_end_person_id
4700 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4701 paf.location_id
4702 ,paf.business_group_id
4703 ,paf.soft_coding_keyflex_id
4704 ,ppa.effective_date) = cp_gre_id
4705 AND validate_person_id(paf.person_id
4706 ,paf.assignment_id
4707 ,cp_start_date
4708 ,cp_end_date
4709 ,cp_periodic_start_date
4710 ,cp_periodic_end_date
4711 ,cp_payroll_action_id
4712 ,cp_gre_id
4713 ,cp_business_group_id
4714 ,'N') = 'Y'
4715 UNION ALL
4716 SELECT paf.person_id
4717 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4718 ,paf.assignment_id
4719 ,'Y' variable_idw
4720 FROM per_assignments_f paf
4721 ,pay_payroll_actions ppa
4722 WHERE ppa.payroll_action_id = cp_payroll_action_id
4723 AND ppa.business_group_id = cp_business_group_id
4724 AND paf.business_group_id = ppa.business_group_id
4725 AND paf.person_id BETWEEN cp_start_person_id
4726 AND cp_end_person_id
4727 AND gv_mode = 'P'
4728 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4729 paf.location_id
4730 ,paf.business_group_id
4731 ,paf.soft_coding_keyflex_id
4732 ,ppa.effective_date) = cp_gre_id
4733 AND validate_person_id(
4734 paf.person_id
4735 ,paf.assignment_id
4736 ,cp_start_date
4737 ,cp_end_date
4738 ,cp_periodic_start_date
4739 ,cp_periodic_end_date
4740 ,cp_payroll_action_id
4741 ,cp_gre_id
4742 ,cp_business_group_id
4743 ,'Y') = 'Y'
4744 ORDER BY 1, 2, 3, 4 desc;
4745
4746 CURSOR c_get_range_emp(
4747 cp_payroll_action_id IN NUMBER
4748 ,cp_chunk_number IN NUMBER
4749 ,cp_business_group_id IN NUMBER
4750 ,cp_gre_id IN NUMBER
4751 ,cp_start_date IN DATE
4752 ,cp_end_date IN DATE
4753 ,cp_periodic_start_date IN DATE
4754 ,cp_periodic_end_date IN DATE) IS
4755 SELECT paf.person_id
4756 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4757 ,paf.assignment_id
4758 ,'N' variable_idw
4759 FROM pay_payroll_actions ppa
4760 ,per_assignments_f paf
4761 ,pay_population_ranges ppr
4762 WHERE ppa.payroll_action_id = cp_payroll_action_id
4763 AND ppr.payroll_action_id = ppa.payroll_action_id
4764 AND ppr.chunk_number = cp_chunk_number
4765 AND paf.person_id = ppr.person_id
4766 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4767 paf.location_id
4768 ,paf.business_group_id
4769 ,paf.soft_coding_keyflex_id
4770 ,ppa.effective_date) = cp_gre_id
4771 AND validate_person_id(
4772 paf.person_id
4773 ,paf.assignment_id
4774 ,cp_start_date
4775 ,cp_end_date
4776 ,cp_periodic_start_date
4777 ,cp_periodic_end_date
4778 ,cp_payroll_action_id
4779 ,cp_gre_id
4780 ,cp_business_group_id
4781 ,'N') = 'Y'
4782 UNION ALL
4783 SELECT paf.person_id
4784 ,decode(paf.primary_flag, 'Y', 'Y', 'Z')
4785 ,paf.assignment_id
4786 ,'Y' variable_idw
4787 FROM pay_payroll_actions ppa
4788 ,per_assignments_f paf
4789 ,pay_population_ranges ppr
4790 WHERE ppa.payroll_action_id = cp_payroll_action_id
4791 AND ppr.payroll_action_id = ppa.payroll_action_id
4792 AND ppr.chunk_number = cp_chunk_number
4793 AND paf.person_id = ppr.person_id
4794 AND gv_mode = 'P'
4795 AND per_mx_ssaffl_archive.derive_gre_from_loc_scl(
4796 paf.location_id
4797 ,paf.business_group_id
4798 ,paf.soft_coding_keyflex_id
4799 ,ppa.effective_date) = cp_gre_id
4800 AND validate_person_id(
4801 paf.person_id
4802 ,paf.assignment_id
4803 ,cp_start_date
4804 ,cp_end_date
4805 ,cp_periodic_start_date
4806 ,cp_periodic_end_date
4807 ,cp_payroll_action_id
4808 ,cp_gre_id
4809 ,cp_business_group_id
4810 ,'Y') = 'Y'
4811 ORDER BY 1, 2, 3, 4 desc;
4812 -- bug:15839415 end
4813
4814 ln_person_id NUMBER;
4815 ln_assignment_id NUMBER;
4816 ln_person_id_prev NUMBER;
4817 ln_assignment_id_prev NUMBER;
4818 ln_primary_flag VARCHAR2(100);
4819 lv_report_mode VARCHAR2(10);
4820
4821 lv_end_date VARCHAR2(50);
4822 lv_start_date VARCHAR2(50);
4823 ln_business_group_id NUMBER;
4824 ln_gre_id NUMBER;
4825
4826 ln_asg_act_id NUMBER;
4827 ln_events_found NUMBER;
4828 lb_range_person BOOLEAN;
4829
4830 lv_procedure_name VARCHAR2(100);
4831 lv_error_message VARCHAR2(2000);
4832 ln_step NUMBER;
4833
4834 BEGIN
4835
4836 lv_procedure_name := 'action_creation';
4837
4838 ln_step := 1;
4839 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4840
4841
4842 hr_utility_trace('--> Entering Action Creation ');
4843
4844 get_payroll_action_info(
4845 p_payroll_action_id => p_payroll_action_id
4846 ,p_start_date => lv_start_date
4847 ,p_end_date => lv_end_date
4848 ,p_business_group_id => ln_business_group_id
4849 ,p_gre_id => ln_gre_id);
4850
4851 hr_utility_trace('lv_start_date ' || lv_start_date);
4852 hr_utility_trace('lv_end_date ' || lv_end_date);
4853 hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4854 hr_utility_trace('ln_gre_id ' || ln_gre_id);
4855
4856 IF gv_IDW_calc_method IS NULL THEN
4857 gv_IDW_calc_method := get_IDW_calc_method (
4858 ln_gre_id,
4859 fnd_date.canonical_to_date (lv_end_date));
4860 END IF;
4861
4862 hr_utility_trace('--> gv_IDW_calc_method ' || gv_IDW_calc_method);
4863
4864 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4865 ln_step := 2;
4866
4867 lb_range_person := pay_ac_utility.range_person_on(
4868 p_report_type => 'SS_ARCHIVE'
4869 ,p_report_format => 'SS_ARCHIVE'
4870 ,p_report_qualifier => 'SS_ARCHIVE'
4871 ,p_report_category => 'RT');
4872
4873 hr_utility_trace('--> Step ' || ln_step);
4874 IF lb_range_person THEN
4875 OPEN c_get_range_emp(p_payroll_action_id
4876 ,p_chunk
4877 ,ln_business_group_id
4878 ,ln_gre_id
4879 ,fnd_date.canonical_to_date(lv_start_date)
4880 ,fnd_date.canonical_to_date(lv_end_date)
4881 ,fnd_date.canonical_to_date(gv_periodic_start_date)
4882 ,fnd_date.canonical_to_date(gv_periodic_end_date)
4883 );
4884
4885 hr_utility_trace('c_get_range_emp');
4886
4887 ELSE
4888 OPEN c_get_emp( p_payroll_action_id
4889 , p_start_person_id
4890 , p_end_person_id
4891 , ln_business_group_id
4892 , ln_gre_id
4893 , fnd_date.canonical_to_date(lv_start_date)
4894 , fnd_date.canonical_to_date(lv_end_date)
4895 , fnd_date.canonical_to_date(gv_periodic_start_date)
4896 , fnd_date.canonical_to_date(gv_periodic_end_date) );
4897
4898 hr_utility_trace('c_get_emp');
4899
4900 END IF;
4901
4902 --Loop for all rows returned for SQL statement.
4903 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4904
4905 ln_person_id_prev := 0;
4906 ln_assignment_id_prev := 0;
4907
4908 LOOP
4909
4910 IF lb_range_person THEN
4911
4912 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4913 ln_step := 3;
4914 hr_utility_trace('--> Fetching from c_get_range_emp ' );
4915 FETCH c_get_range_emp INTO ln_person_id
4916 ,ln_primary_flag
4917 ,ln_assignment_id
4918 ,lv_report_mode;
4919
4920 EXIT WHEN c_get_range_emp%NOTFOUND;
4921
4922 ELSE
4923
4924 hr_utility.set_location(gv_package || lv_procedure_name, 40);
4925 ln_step := 4;
4926 hr_utility_trace('--> Fetching from c_get_emp ' );
4927 FETCH c_get_emp INTO ln_person_id
4928 ,ln_primary_flag
4929 ,ln_assignment_id
4930 ,lv_report_mode;
4931
4932 EXIT WHEN c_get_emp%NOTFOUND;
4933
4934 END IF;
4935
4936 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4937 ln_step := 5;
4938
4939 hr_utility_trace('ln_person_id = ' || ln_person_id);
4940 hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4941 hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4942 hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4943 hr_utility_trace('lv_start_date = ' || lv_start_date);
4944 hr_utility_trace('lv_end_date = ' || lv_end_date);
4945
4946 IF (ln_person_id <> ln_person_id_prev OR
4947 ln_assignment_id <> ln_assignment_id_prev) THEN
4948
4949 IF gn_implementation = 0 THEN
4950
4951
4952 ln_step := 6;
4953 hr_utility_trace('--> Step 6' );
4954 hr_utility_trace('IMPLEMENTATION TRUE');
4955
4956 SELECT pay_assignment_actions_s.nextval
4957 INTO ln_asg_act_id
4958 FROM dual;
4959
4960 hr_utility_trace('--> Inserting into pay_assignment_actions' );
4961 hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4962 -- insert into pay_assignment_actions.
4963 hr_nonrun_asact.insact(ln_asg_act_id,
4964 ln_assignment_id,
4965 p_payroll_action_id,
4966 p_chunk,
4967 ln_gre_id,
4968 null,
4969 'U',
4970 null);
4971
4972 hr_utility.set_location(gv_package || lv_procedure_name, 70);
4973 hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4974 hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4975 hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4976
4977 hr_utility.set_location(gv_package || lv_procedure_name, 80);
4978
4979 hr_utility_trace('--> Updating into pay_assignment_actions' );
4980 hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4981 UPDATE pay_assignment_actions
4982 SET serial_number = to_char(ln_person_id) || '|' ||
4983 'MX_IDWV=' || lv_report_mode || '|'
4984 WHERE assignment_action_id = ln_asg_act_id;
4985
4986 ELSE
4987
4988 ln_step := 7;
4989
4990 hr_utility_trace('--> In STep 7');
4991
4992 IF ( pay_mx_soc_sec_archive.per_asg.EXISTS(ln_person_id) = FALSE )
4993 THEN
4994 hr_utility_trace('--> Adding in pay_mx_soc_sec_archive.per_asg ');
4995 pay_mx_soc_sec_archive.per_asg(ln_person_id).person_id :=
4996 ln_person_id;
4997 pay_mx_soc_sec_archive.per_asg(ln_person_id).assignment_id :=
4998 ln_assignment_id;
4999 END IF;
5000
5001 hr_utility_trace('--> lv_report_mode '||lv_report_mode);
5002 IF lv_report_mode = 'Y' THEN
5003
5004 ln_events_found := 1;
5005
5006 ELSE
5007
5008 SELECT count(*)
5009 INTO ln_events_found
5010 FROM pay_process_events
5011 WHERE assignment_id = ln_assignment_id
5012 AND last_update_date
5013 BETWEEN fnd_date.canonical_to_date(lv_start_date)
5014 AND fnd_date.canonical_to_date(lv_end_date);
5015 END IF;
5016
5017 ln_step := 8;
5018 hr_utility_trace('--> In Step 8 ');
5019 hr_utility_trace('--> ln_events_found ' || ln_events_found);
5020
5021 IF ln_events_found > 0
5022 OR (gv_IDW_calc_method = 'B' AND
5023 seniority_changed (
5024 ln_person_id,
5025 fnd_date.canonical_to_date(lv_end_date),
5026 fnd_date.canonical_to_date(lv_start_date)) = 'Y') THEN
5027 hr_utility_trace('--> Inside If ' );
5028 SELECT pay_assignment_actions_s.nextval
5029 INTO ln_asg_act_id
5030 FROM dual;
5031
5032 hr_utility_trace('--> insert into pay_assignment_actions.' );
5033 -- insert into pay_assignment_actions.
5034 hr_nonrun_asact.insact(ln_asg_act_id,
5035 ln_assignment_id,
5036 p_payroll_action_id,
5037 p_chunk,
5038 ln_gre_id,
5039 null,
5040 'U',
5041 null);
5042
5043 hr_utility.set_location(gv_package || lv_procedure_name, 90);
5044 hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
5045 hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
5046
5047 hr_utility.set_location(gv_package || lv_procedure_name, 100);
5048
5049 UPDATE pay_assignment_actions
5050 SET serial_number = to_char(ln_person_id) || '|' ||
5051 'MX_IDWV=' || lv_report_mode || '|'
5052 WHERE assignment_action_id = ln_asg_act_id;
5053
5054
5055 pay_mx_soc_sec_archive.per_asg.delete;
5056
5057 END IF; -- ln_events_found
5058
5059 END IF; -- gn_implementation
5060
5061 END IF; -- prev_person
5062
5063 ln_person_id_prev := ln_person_id;
5064 ln_assignment_id_prev := ln_assignment_id;
5065
5066 END LOOP;
5067
5068 IF lb_range_person THEN
5069
5070 CLOSE c_get_range_emp;
5071
5072 ELSE
5073
5074 CLOSE c_get_emp;
5075
5076 END IF;
5077
5078 ln_step := 9;
5079
5080 hr_utility_trace('--> Step 9 ' );
5081 hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
5082 hr_utility_trace(' p_chunk : '|| p_chunk );
5083
5084 hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
5085
5086 EXCEPTION
5087 WHEN others THEN
5088 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
5089 gv_package || lv_procedure_name;
5090
5091 hr_utility_trace(lv_error_message || '-' || sqlerrm);
5092
5093 lv_error_message :=
5094 pay_emp_action_arch.set_error_message(lv_error_message);
5095
5096 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5097 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5098 hr_utility.raise_error;
5099
5100
5101
5102 END action_creation;
5103
5104 PROCEDURE archive_data(p_asg_action_id IN NUMBER,
5105 p_effective_date IN DATE) IS
5106
5107 CURSOR c_asgact_info (cp_assignment_action IN NUMBER) IS
5108 SELECT paa.payroll_action_id
5109 ,paa.assignment_id
5110 ,paa.tax_unit_id
5111 ,paa.chunk_number
5112 ,paa.serial_number
5113 FROM pay_assignment_actions paa
5114 WHERE paa.assignment_action_id = cp_assignment_action;
5115
5116
5117 CURSOR c_check_pay_action( cp_payroll_action_id IN NUMBER) IS
5118 SELECT count(*)
5119 FROM pay_action_information
5120 WHERE action_context_id = cp_payroll_action_id
5121 AND action_context_type = 'PA';
5122
5123 CURSOR c_check_active_employee (p_person_id IN VARCHAR2 ,
5124 p_effective_date IN VARCHAR2) IS
5125
5126 SELECT 'Y'
5127 FROM per_all_people_f per
5128 WHERE per.person_id = p_person_id
5129 AND fnd_date.canonical_to_date(p_effective_date)
5130 BETWEEN per.effective_start_date AND per.effective_end_date
5131 AND per.current_employee_flag = 'Y';
5132
5133
5134 lv_end_date VARCHAR2(50);
5135 lv_start_date VARCHAR2(50);
5136 ld_end_date DATE;
5137 ld_start_date DATE;
5138 ln_business_group_id NUMBER;
5139 ln_gre_id NUMBER;
5140
5141 ln_payroll_action_id NUMBER;
5142 ln_assignment_id NUMBER;
5143 ln_tax_unit_id NUMBER;
5144 ln_chunk_number NUMBER;
5145 lv_serial_number VARCHAR2(240);
5146 ln_person_id VARCHAR2(24);
5147
5148 ln_pay_action_count NUMBER;
5149
5150 lv_procedure_name VARCHAR2(100);
5151 lv_error_message VARCHAR2(2000);
5152 ln_step NUMBER;
5153
5154 l_act_tab pay_emp_action_arch.action_info_table;
5155 j NUMBER;
5156 lv_is_employee VARCHAR2(2) := 'N';
5157
5158 BEGIN
5159
5160 hr_utility_trace('--> Archive_data' );
5161 hr_utility_trace('--> p_asg_action_id ' || p_asg_action_id );
5162 hr_utility_trace('--> p_effective_date' || p_effective_date );
5163
5164 pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
5165
5166 lv_procedure_name := 'archive_data';
5167 gn_person_rec_chng := 0;
5168 gn_gre_found := 0;
5169 gv_credit_no := NULL;
5170 gv_credit_start_date := NULL;
5171 gv_crdt_grant_dt := NULL;
5172 gv_discount_type := NULL;
5173 gv_discount_value := NULL;
5174
5175
5176
5177 IF gn_implementation = 0 THEN
5178 gn_person_rec_chng := 1;
5179 END IF;
5180
5181 hr_utility.set_location(gv_package || lv_procedure_name, 10);
5182 ln_step := 1;
5183
5184 OPEN c_asgact_info(p_asg_action_id);
5185 FETCH c_asgact_info INTO ln_payroll_action_id
5186 ,ln_assignment_id
5187 ,ln_tax_unit_id
5188 ,ln_chunk_number
5189 ,lv_serial_number;
5190 CLOSE c_asgact_info;
5191
5192 ln_person_id := SUBSTR( lv_serial_number, 1,
5193 NVL(INSTR(lv_serial_number,'|'),50) -1);
5194
5195 hr_utility_trace('--> ln_person_id'|| ln_person_id);
5196 /***********************************************************
5197 ** gv_variable_idw is used to get information about whether
5198 ** whether this assignment has variable IDW or not.
5199 ***********************************************************/
5200
5201 gv_variable_idw :=
5202 NVL(pay_mx_utility.get_legi_param_val('MX_IDWV',lv_serial_number), 'N');
5203 hr_utility_trace('--> gv_variable_idw' || gv_variable_idw );
5204
5205 hr_utility.set_location(gv_package || lv_procedure_name, 20);
5206 ln_step := 2;
5207
5208 get_payroll_action_info(
5209 p_payroll_action_id => ln_payroll_action_id
5210 ,p_start_date => lv_start_date
5211 ,p_end_date => lv_end_date
5212 ,p_business_group_id => ln_business_group_id
5213 ,p_gre_id => ln_gre_id);
5214
5215 hr_utility.set_location(gv_package || lv_procedure_name, 30);
5216 ln_step := 3;
5217
5218 ld_start_date := fnd_date.canonical_to_date(lv_start_date);
5219 ld_end_date := fnd_date.canonical_to_date(lv_end_date);
5220
5221 hr_utility_trace('--> lv_start_date' || lv_start_date );
5222 hr_utility_trace('--> lv_end_date' || lv_end_date );
5223 hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
5224 hr_utility_trace('--> ln_gre_id' || ln_gre_id );
5225
5226
5227 IF gv_IDW_calc_method IS NULL THEN
5228 gv_IDW_calc_method := get_IDW_calc_method (
5229 ln_gre_id,
5230 fnd_date.canonical_to_date (lv_end_date));
5231 END IF;
5232 hr_utility_trace('--> gv_IDW_calc_method' || gv_IDW_calc_method );
5233 /****************************************************************
5234 ** Archive all the payroll action level data once only when
5235 ** chunk number is 1. Also check if this has not been archived
5236 ** earlier
5237 *****************************************************************/
5238
5239 hr_utility.set_location(gv_package || lv_procedure_name, 40);
5240 ln_step := 4;
5241
5242 OPEN c_check_pay_action(ln_payroll_action_id);
5243 FETCH c_check_pay_action INTO ln_pay_action_count;
5244 CLOSE c_check_pay_action;
5245
5246 hr_utility_trace('--> ln_pay_action_count' || ln_pay_action_count );
5247 IF ln_pay_action_count = 0 THEN
5248
5249 hr_utility.set_location(gv_package || lv_procedure_name, 50);
5250 ln_step := 5;
5251
5252 IF ln_chunk_number = 1 THEN
5253
5254 ln_step := 25;
5255
5256 hr_utility.set_location(gv_package || lv_procedure_name, 60);
5257 ln_step := 6;
5258 hr_utility_trace('--> arch_pay_action_level_data' );
5259 arch_pay_action_level_data(
5260 p_payroll_action_id => ln_payroll_action_id
5261 ,p_assignment_id => ln_assignment_id
5262 ,p_effective_Date => ld_end_date
5263 ,p_tax_unit_id => ln_gre_id
5264 );
5265 END IF;
5266
5267 END IF;
5268
5269 hr_utility.set_location(gv_package || lv_procedure_name, 70);
5270 ln_step := 7;
5271
5272 hr_utility_trace('--> get_transactions' );
5273 get_transactions( p_payroll_action_id => ln_payroll_action_id
5274 ,p_asg_action_id => p_asg_action_id
5275 ,p_effective_Date => p_effective_Date
5276 ,p_assignment_id => ln_assignment_id
5277 ,p_person_id => ln_person_id
5278 ,p_chunk_number => ln_chunk_number
5279 ,p_start_date => ld_start_date
5280 ,p_end_date => ld_end_date
5281 ,p_business_group_id => ln_business_group_id
5282 ,p_gre_id => ln_gre_id
5283 );
5284
5285 IF ( gn_person_rec_chng = 1 ) THEN
5286
5287 hr_utility.set_location(gv_package || lv_procedure_name, 80);
5288 ln_step := 8;
5289 hr_utility_trace('--> 8 get_person_information' );
5290 get_person_information(
5291 p_payroll_action_id => ln_payroll_action_id
5292 ,p_asg_action_id => p_asg_action_id
5293 ,p_effective_Date => p_effective_Date
5294 ,p_assignment_id => ln_assignment_id
5295 ,p_person_id => ln_person_id
5296 ,p_chunk_number => ln_chunk_number
5297 ,p_start_date => ld_start_date
5298 ,p_end_date => ld_end_date
5299 ,p_business_group_id => ln_business_group_id
5300 ,p_gre_id => ln_gre_id
5301 );
5302
5303 END IF;
5304
5305 hr_utility.set_location(gv_package || lv_procedure_name, 90);
5306 ln_step := 9;
5307
5308 l_act_tab.DELETE;
5309 j := 0;
5310
5311
5312 IF pay_mx_soc_sec_archive.lrr_act_tab.COUNT > 0 THEN
5313 FOR i IN pay_mx_soc_sec_archive.lrr_act_tab.FIRST..
5314 pay_mx_soc_sec_archive.lrr_act_tab.LAST
5315 LOOP
5316
5317 IF pay_mx_soc_sec_archive.lrr_act_tab(i).action_info_category
5318 = 'MX SS TRANSACTIONS' THEN
5319 /*Added code for bug 6862116*/
5320 hr_utility_trace('Checking for active employe ..for MX SS TRANSACTIONS' );
5321 OPEN c_check_active_employee(pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1,
5322 pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
5323 FETCH c_check_active_employee INTO lv_is_employee;
5324 CLOSE c_check_active_employee;
5325
5326 hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5327 hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
5328 hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5329
5330 IF (TRUNC(fnd_date.canonical_to_date(
5331 pay_mx_soc_sec_archive.lrr_act_tab(i).act_info2)) =
5332 TRUNC(fnd_date.canonical_to_date('4712/12/31'))) OR lv_is_employee = 'N' THEN
5333
5334 NULL;
5335 hr_utility_trace('--> NULL' );
5336 ELSE
5337 l_act_tab(j) := pay_mx_soc_sec_archive.lrr_act_tab(i);
5338
5339 j := j + 1;
5340
5341 END if;
5342
5343 ELSIF pay_mx_soc_sec_archive.lrr_act_tab(i).action_info_category
5344 = 'MX SS PERSON INFORMATION' THEN
5345 hr_utility_trace('Checking for active employe ..for MX SS PERSON Information' );
5346 OPEN c_check_active_employee (pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1,
5347 pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
5348 FETCH c_check_active_employee INTO lv_is_employee;
5349 CLOSE c_check_active_employee;
5350
5351 hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5352 hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
5353
5354 hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5355
5356
5357 IF lv_is_employee = 'N' THEN
5358
5359 NULL ;
5360
5361 ELSE
5362 l_act_tab(j) := pay_mx_soc_sec_archive.lrr_act_tab(i);
5363
5364 j := j + 1;
5365
5366 END IF ;
5367
5368 ELSE
5369 l_act_tab(j) := pay_mx_soc_sec_archive.lrr_act_tab(i);
5370
5371 j := j + 1;
5372
5373 END IF;
5374
5375 END LOOP;
5376
5377 pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
5378
5379 IF l_act_tab.COUNT > 0 THEN
5380 pay_mx_soc_sec_archive.lrr_act_tab := l_act_tab;
5381 END IF;
5382
5383 l_act_tab.DELETE;
5384
5385 END IF;
5386
5387 IF pay_mx_soc_sec_archive.lrr_act_tab.COUNT > 0 THEN
5388
5389 hr_utility_trace('--> pay_mx_soc_sec_archive.lrr_act_tab.COUNT ' || pay_mx_soc_sec_archive.lrr_act_tab.COUNT);
5390 pay_emp_action_arch.insert_rows_thro_api_process(
5391 p_action_context_id => p_asg_action_id
5392 ,p_action_context_type => 'AAP'
5393 ,p_assignment_id => ln_assignment_id
5394 ,p_tax_unit_id => ln_gre_id
5395 ,p_curr_pymt_eff_date => ld_end_date
5396 ,p_tab_rec_data => pay_mx_soc_sec_archive.lrr_act_tab
5397 );
5398
5399 END IF;
5400
5401 pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
5402
5403 hr_utility.set_location(gv_package || lv_procedure_name, 100);
5404 ln_step := 10;
5405
5406 EXCEPTION
5407 WHEN others THEN
5408 hr_utility_trace('--> Exception in Archive_data ' );
5409 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
5410 gv_package || lv_procedure_name;
5411
5412 hr_utility_trace(lv_error_message || '-' || sqlerrm);
5413
5414 lv_error_message :=
5415 pay_emp_action_arch.set_error_message(lv_error_message);
5416
5417 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5418 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5419 hr_utility.raise_error;
5420
5421 END archive_data;
5422
5423 PROCEDURE archinit(p_payroll_action_id in number) IS
5424
5425 CURSOR c_dated_tables IS
5426 SELECT dated_table_id
5427 ,table_name
5428 FROM pay_dated_tables;
5429
5430 CURSOR c_payroll_Action_info (cp_payroll_action_id IN NUMBER) IS
5431 SELECT pay_mx_utility.get_legi_param_val('GRE',
5432 LEGISLATIVE_PARAMETERS) GRE,
5433 fnd_date.canonical_to_date(
5434 pay_mx_utility.get_legi_param_val(
5435 'END_DATE',
5436 LEGISLATIVE_PARAMETERS)) END_DATE
5437 FROM pay_payroll_actions
5438 WHERE payroll_action_id = cp_payroll_action_id;
5439
5440 ln_gre_id NUMBER;
5441 ld_start_date DATE;
5442 ld_end_date DATE;
5443 ld_old_recorded_date DATE;
5444
5445 BEGIN
5446
5447 FOR pdt IN c_dated_tables
5448 LOOP
5449
5450 dated_tbls(pdt.dated_table_id).table_name := pdt.table_name;
5451
5452 END LOOP;
5453
5454 OPEN c_payroll_action_info(p_payroll_action_id);
5455 FETCH c_payroll_action_info INTO ln_gre_id,
5456 ld_end_date;
5457 CLOSE c_payroll_action_info;
5458
5459 SELECT COUNT(*)
5460 INTO gn_implementation
5461 FROM pay_payroll_actions
5462 WHERE report_type = 'SS_ARCHIVE'
5463 AND report_qualifier = 'SS_ARCHIVE'
5464 AND report_category = 'RT'
5465 AND pay_mx_utility.get_legi_param_val('GRE', legislative_parameters )
5466 = ln_gre_id
5467 AND payroll_action_id + 0 < p_payroll_action_id;
5468
5469 pay_recorded_requests_pkg.get_recorded_date_no_ins(
5470 p_process => 'MX_SOC_SEC_ARCH',
5471 p_recorded_date => ld_start_date,
5472 p_attribute1 => ln_gre_id,
5473 p_attribute2 => NULL,
5474 p_attribute3 => NULL,
5475 p_attribute4 => NULL,
5476 p_attribute5 => NULL,
5477 p_attribute6 => NULL,
5478 p_attribute7 => NULL,
5479 p_attribute8 => NULL,
5480 p_attribute9 => NULL,
5481 p_attribute10 => NULL,
5482 p_attribute11 => NULL,
5483 p_attribute12 => NULL,
5484 p_attribute13 => NULL,
5485 p_attribute14 => NULL,
5486 p_attribute15 => NULL,
5487 p_attribute16 => NULL,
5488 p_attribute17 => NULL,
5489 p_attribute18 => NULL,
5490 p_attribute19 => NULL,
5491 p_attribute20 => NULL);
5492
5493 hr_utility_trace ('end_date in legislative_parameters = '||
5494 fnd_date.date_to_canonical(ld_end_date));
5495 hr_utility_trace ('Date stamped in pay_recorded_requests = '||
5496 fnd_date.date_to_canonical(ld_start_date));
5497
5498 /* We need to stamp the end date in pay_recorded_requests only if
5499 existing date is less than end date stamped on payroll action. This
5500 is done to prevent invalid stamping of date in pay_recorded_requests
5501 when archiver is run in retry mode.
5502
5503 In other words, date stamped in pay_recorded_requests can only be
5504 advanced further; it cannot be updated by a potential retry attempt. */
5505 IF ld_end_date > ld_start_date THEN
5506 hr_utility_trace ('Advancing date in pay_recorded_requests to '||
5507 fnd_date.date_to_canonical(ld_end_date));
5508 pay_recorded_requests_pkg.set_recorded_date(
5509 p_process => 'MX_SOC_SEC_ARCH',
5510 p_recorded_date => ld_end_date,
5511 p_recorded_date_o => ld_old_recorded_date,
5512 p_attribute1 => ln_gre_id,
5513 p_attribute2 => NULL,
5514 p_attribute3 => NULL,
5515 p_attribute4 => NULL,
5516 p_attribute5 => NULL,
5517 p_attribute6 => NULL,
5518 p_attribute7 => NULL,
5519 p_attribute8 => NULL,
5520 p_attribute9 => NULL,
5521 p_attribute10 => NULL,
5522 p_attribute11 => NULL,
5523 p_attribute12 => NULL,
5524 p_attribute13 => NULL,
5525 p_attribute14 => NULL,
5526 p_attribute15 => NULL,
5527 p_attribute16 => NULL,
5528 p_attribute17 => NULL,
5529 p_attribute18 => NULL,
5530 p_attribute19 => NULL,
5531 p_attribute20 => NULL);
5532 ELSE
5533 hr_utility_trace ('pay_recorded_requests not updated.');
5534 END IF;
5535
5536 gt_gre_cache.delete();
5537
5538 END archinit;
5539
5540 FUNCTION arch_exists_without_upgrade
5541 (
5542 p_business_group_id NUMBER
5543 ) RETURN VARCHAR2 AS
5544
5545 CURSOR csr_upgrade_exists IS
5546 SELECT 'Y'
5547 FROM pay_upgrade_status pus,
5548 pay_upgrade_definitions pud
5549 WHERE pud.upgrade_definition_id = pus.upgrade_definition_id
5550 AND pus.business_group_id = p_business_group_id
5551 AND pud.short_name = 'MX_SS_ARCH_TRAN_DATE'
5552 AND pus.status = 'C';
5553
5554 CURSOR csr_arch_exists IS
5555 SELECT 'Y'
5556 FROM pay_payroll_actions ppa
5557 WHERE ppa.report_type = 'SS_ARCHIVE'
5558 AND ppa.report_qualifier = 'SS_ARCHIVE'
5559 AND ppa.report_category = 'RT'
5560 AND ppa.business_group_id = p_business_group_id;
5561
5562 l_proc_name varchar2(100);
5563 lv_exists varchar2(1);
5564 BEGIN
5565 /*
5566 This function returns following values: -
5567 B - Current BG is brand new. No SS archiver runs exist in the past.
5568 C - Completed upgrade process exists for BG passed as parameter.
5569 E - Error. Archiver runs exist without successful upgrade runs.
5570 */
5571
5572 l_proc_name := gv_package || 'arch_exists_without_upgrade';
5573 hr_utility_trace ('Entering '||l_proc_name);
5574 lv_exists := 'N';
5575
5576 OPEN csr_upgrade_exists;
5577 FETCH csr_upgrade_exists INTO lv_exists;
5578 CLOSE csr_upgrade_exists;
5579
5580 IF lv_exists = 'Y' THEN
5581 hr_utility_trace ('Completed upgrade process exists.');
5582 hr_utility_trace ('Leaving '||l_proc_name);
5583 RETURN ('C');
5584 END IF;
5585
5586 OPEN csr_arch_exists;
5587 FETCH csr_arch_exists INTO lv_exists;
5588 CLOSE csr_arch_exists;
5589
5590 IF lv_exists = 'N' THEN
5591 hr_utility_trace ('Brand new business group.');
5592 hr_utility_trace ('Leaving '||l_proc_name);
5593 RETURN ('B');
5594 ELSE
5595 hr_utility_trace ('Archiver runs exist without successful upgrade.');
5596 hr_utility_trace ('Leaving '||l_proc_name);
5597 RETURN ('E');
5598 END IF;
5599
5600 END arch_exists_without_upgrade;
5601
5602 BEGIN
5603
5604 --hr_utility.trace_on(null,'SUA');
5605 gv_package := 'pay_mx_soc_sec_archive.';
5606 gv_debug := hr_utility.debug_enabled;
5607
5608 END PAY_MX_SOC_SEC_ARCHIVE;