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