[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_PERSON_UPLOAD
Source
1 PACKAGE BODY hr_h2pi_person_upload AS
2 /* $Header: hrh2pipe.pkb 120.0 2005/05/31 00:41:20 appldev noship $ */
3
4 g_eot DATE := TRUNC(TO_DATE('4712/12/31', 'YYYY/MM/DD'));
5 g_package VARCHAR2(33) := ' hr_h2pi_person_upload.';
6 MAPPING_ID_MISSING EXCEPTION;
7 PRAGMA EXCEPTION_INIT (MAPPING_ID_MISSING, -20010);
8 MAPPING_ID_INVALID EXCEPTION;
9 PERSON_ERROR EXCEPTION;
10
11 PROCEDURE calculate_datetrack_mode(p_ud_start_date DATE,
12 p_ud_end_date DATE,
13 p_ed_start_date DATE,
14 p_ed_end_date DATE,
15 p_records_same BOOLEAN,
16 p_future_records BOOLEAN,
17 p_update_mode OUT NOCOPY VARCHAR2,
18 p_delete_mode OUT NOCOPY VARCHAR2) IS
19
20 l_proc VARCHAR2(72) := g_package||'calculate_datetrack_mode';
21
22 BEGIN
23 hr_utility.set_location('Entering:'|| l_proc, 10);
24 p_update_mode := NULL;
25 p_delete_mode := 'X';
26
27 IF p_ud_end_date > p_ed_end_date THEN
28 hr_utility.set_location(l_proc, 20);
29 p_delete_mode := 'DELETE_NEXT_CHANGE';
30 ELSE
31 IF p_ud_start_date = p_ed_start_date THEN
32 hr_utility.set_location(l_proc, 30);
33 IF NOT p_records_same THEN
34 hr_utility.set_location(l_proc, 40);
35 p_update_mode := 'CORRECTION';
36 END IF;
37 ELSE
38 IF p_future_records THEN
39 hr_utility.set_location(l_proc, 50);
40 p_update_mode := 'UPDATE_CHANGE_INSERT';
41 ELSE
42 hr_utility.set_location(l_proc, 60);
43 p_update_mode := 'UPDATE';
44 END IF;
45 END IF;
46 END IF;
47
48 hr_utility.set_location('Leaving:'|| l_proc, 70);
49
50 END calculate_datetrack_mode;
51
52 FUNCTION get_costing_id_flex_num RETURN NUMBER IS
53
54 l_proc VARCHAR2(72) := g_package||'.get_costing_id_flex_num';
55 l_costing_id_flex_num varchar2(150);
56
57 CURSOR csr_costing IS
58 SELECT cost_allocation_structure
59 FROM per_business_groups
60 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
61
62 BEGIN
63 hr_utility.set_location('Entering:'|| l_proc, 10);
64 OPEN csr_costing;
65 FETCH csr_costing INTO l_costing_id_flex_num;
66 IF csr_costing%notfound then
67 hr_utility.set_location(l_proc, 20);
68 END IF;
69 CLOSE csr_costing;
70 hr_utility.set_location('Leaving:'|| l_proc, 30);
71 RETURN to_number(l_costing_id_flex_num);
72 END;
73
74 PROCEDURE create_end_date_records(p_from_client_id NUMBER ) IS
75
76 l_proc VARCHAR2(72) := g_package||'create_end_date_records';
77
78 BEGIN
79 hr_utility.set_location('Entering:'|| l_proc, 10);
80
81 INSERT INTO hr_h2pi_assignments
82 (assignment_id,
83 effective_start_date,
84 effective_end_date,
85 last_upd_date,
86 business_group_id,
87 client_id,
88 person_id,
89 organization_id,
90 primary_flag)
91 SELECT asg1.assignment_id,
92 asg1.end_date+1,
93 g_eot,
94 g_eot,
95 asg.business_group_id,
96 p_from_client_id,
97 asg.person_id,
98 -1,
99 asg.primary_flag
100 FROM (SELECT asg2.assignment_id,
101 MAX(asg2.effective_end_date) end_date
102 FROM hr_h2pi_assignments asg2
103 WHERE asg2.client_id = p_from_client_id
104 GROUP BY asg2.person_id, asg2.assignment_id
105 HAVING MAX(asg2.effective_end_date) <> g_eot) asg1,
106 hr_h2pi_assignments asg
107 WHERE asg.assignment_id = asg1.assignment_id
108 AND asg.client_id = p_from_client_id
109 AND asg.effective_end_date = asg1.end_date
110 AND asg.primary_flag = 'Y';
111 INSERT INTO hr_h2pi_assignments
112 (assignment_id,
113 effective_start_date,
114 effective_end_date,
115 last_upd_date,
116 business_group_id,
117 client_id,
118 person_id,
119 organization_id,
120 primary_flag)
121 SELECT asg1.assignment_id,
122 asg1.end_date+1,
123 g_eot,
124 g_eot,
125 asg.business_group_id,
126 p_from_client_id,
127 asg.person_id,
128 -1,
129 asg.primary_flag
130 FROM (SELECT asg2.assignment_id,
131 MAX(asg2.effective_end_date) end_date
132 FROM hr_h2pi_assignments asg2
133 WHERE asg2.client_id = p_from_client_id
134 GROUP BY asg2.person_id, asg2.assignment_id
135 HAVING MAX(asg2.effective_end_date) <> g_eot) asg1,
136 hr_h2pi_assignments asg
137 WHERE asg.assignment_id = asg1.assignment_id
138 AND asg.client_id = p_from_client_id
139 AND asg.effective_end_date = asg1.end_date
140 AND asg.primary_flag = 'N'
141 AND NOT EXISTS (SELECT 1
142 FROM (SELECT sasg2.assignment_id,
143 MAX(sasg2.effective_end_date) end_date
144 FROM hr_h2pi_assignments sasg2
145 WHERE sasg2.client_id = p_from_client_id
146 GROUP BY sasg2.person_id, sasg2.assignment_id
147 HAVING MAX(sasg2.effective_end_date) = g_eot) sasg1,
148 hr_h2pi_assignments sasg
149 WHERE sasg.assignment_id = sasg1.assignment_id
150 AND sasg.client_id = p_from_client_id
151 AND sasg.effective_end_date = sasg1.end_date
152 AND sasg.last_upd_date = g_eot
153 AND sasg.primary_flag = 'Y'
154 AND sasg.effective_start_date = asg.effective_end_date + 1
155 AND sasg.person_id = asg.person_id);
156
157 hr_utility.set_location(l_proc, 40);
158 INSERT INTO hr_h2pi_payment_methods
159 (personal_payment_method_id,
160 effective_start_date,
161 effective_end_date,
162 last_upd_date,
163 business_group_id,
164 client_id,
165 person_id,
166 assignment_id,
167 org_payment_method_id)
168 SELECT personal_payment_method_id,
169 MAX(effective_end_date)+1,
170 g_eot,
171 g_eot,
172 business_group_id,
173 p_from_client_id,
174 person_id,
175 -1,
176 -1
177 FROM hr_h2pi_payment_methods
178 WHERE client_id = p_from_client_id
179 GROUP BY person_id, personal_payment_method_id,business_group_id
180 HAVING MAX(effective_end_date) <> g_eot;
181
182 hr_utility.set_location(l_proc, 50);
183 INSERT INTO hr_h2pi_cost_allocations
184 (cost_allocation_id,
185 effective_start_date,
186 effective_end_date,
187 last_upd_date,
188 business_group_id,
189 client_id,
190 person_id,
191 assignment_id,
192 proportion,
193 id_flex_num,
194 summary_flag,
195 enabled_flag)
196 SELECT cost_allocation_id,
197 MAX(effective_end_date)+1,
198 g_eot,
199 g_eot,
200 business_group_id,
201 p_from_client_id,
202 person_id,
203 -1,
204 -1,
205 id_flex_num,
206 summary_flag,
207 enabled_flag
208 FROM hr_h2pi_cost_allocations
209 WHERE client_id = p_from_client_id
210 GROUP BY person_id, cost_allocation_id,business_group_id,
211 id_flex_num,summary_flag,enabled_flag
212 HAVING MAX(effective_end_date) <> g_eot;
213
214 hr_utility.set_location(l_proc, 60);
215 INSERT INTO hr_h2pi_element_entries
216 (element_entry_id,
217 effective_start_date,
218 effective_end_date,
219 last_upd_date,
220 business_group_id,
221 client_id,
222 person_id,
223 element_link_id,
224 assignment_id,
225 creator_type,
226 entry_type)
227 SELECT element_entry_id,
228 MAX(effective_end_date)+1,
229 g_eot,
230 g_eot,
231 business_group_id,
232 p_from_client_id,
233 person_id,
234 -1,
235 -1,
236 'x',
237 'x'
238 FROM hr_h2pi_element_entries
239 WHERE client_id = p_from_client_id
240 AND creator_type <> 'UT'
241 GROUP BY person_id, element_entry_id,business_group_id
242 HAVING MAX(effective_end_date) <> g_eot;
243
244 hr_utility.set_location('Leaving:'|| l_proc, 100);
245
246 END create_end_date_records;
247
248
249 PROCEDURE remove_staging_table_data (p_from_client_id NUMBER) IS
250
251 BEGIN
252
253 DELETE FROM hr_h2pi_employees
254 WHERE client_id = p_from_client_id
255 AND status = 'C';
256
257 DELETE FROM hr_h2pi_addresses
258 WHERE client_id = p_from_client_id
259 AND status = 'C';
260
261 DELETE FROM hr_h2pi_assignments
262 WHERE client_id = p_from_client_id
263 AND status = 'C';
264
265 DELETE FROM hr_h2pi_periods_of_service
266 WHERE client_id = p_from_client_id
267 AND status = 'C';
268
269 DELETE FROM hr_h2pi_locations
270 WHERE client_id = p_from_client_id
271 AND status = 'C';
272
273 DELETE FROM hr_h2pi_pay_bases
274 WHERE client_id = p_from_client_id
275 AND status = 'C';
276
277 DELETE FROM hr_h2pi_hr_organizations
278 WHERE client_id = p_from_client_id
279 AND status = 'C';
280
281 DELETE FROM hr_h2pi_organization_class
282 WHERE client_id = p_from_client_id
283 AND status = 'C';
284
285 DELETE FROM hr_h2pi_organization_info
286 WHERE client_id = p_from_client_id
287 AND status = 'C';
288
289 DELETE FROM hr_h2pi_payrolls
290 WHERE client_id = p_from_client_id
291 AND status = 'C';
292
293 DELETE FROM hr_h2pi_element_types
294 WHERE client_id = p_from_client_id
295 AND status = 'C';
296
297 DELETE FROM hr_h2pi_input_values
298 WHERE client_id = p_from_client_id
299 AND status = 'C';
300
301 DELETE FROM hr_h2pi_element_links
302 WHERE client_id = p_from_client_id
303 AND status = 'C';
304
305 DELETE FROM hr_h2pi_bg_and_gre
306 WHERE client_id = p_from_client_id
307 AND status = 'C';
308
309 DELETE FROM hr_h2pi_org_payment_methods
310 WHERE client_id = p_from_client_id
311 AND status = 'C';
312
313 DELETE FROM hr_h2pi_federal_tax_rules
314 WHERE client_id = p_from_client_id
315 AND status = 'C';
316
317 DELETE FROM hr_h2pi_state_tax_rules
318 WHERE client_id = p_from_client_id
319 AND status = 'C';
320
321 DELETE FROM hr_h2pi_county_tax_rules
322 WHERE client_id = p_from_client_id
323 AND status = 'C';
324
325 DELETE FROM hr_h2pi_city_tax_rules
326 WHERE client_id = p_from_client_id
327 AND status = 'C';
328
329 DELETE FROM hr_h2pi_salaries
330 WHERE client_id = p_from_client_id
331 AND status = 'C';
332
333 DELETE FROM hr_h2pi_cost_allocations
334 WHERE client_id = p_from_client_id
335 AND status = 'C';
336
337 DELETE FROM hr_h2pi_payment_methods
338 WHERE client_id = p_from_client_id
339 AND status = 'C';
340
341 DELETE FROM hr_h2pi_element_names
342 WHERE client_id = p_from_client_id
343 AND status = 'C';
344
345 DELETE FROM hr_h2pi_element_entries
346 WHERE client_id = p_from_client_id
347 AND status = 'C';
348
349 DELETE FROM hr_h2pi_element_entry_values
350 WHERE client_id = p_from_client_id
351 AND status = 'C';
352
353
354 DELETE FROM hr_h2pi_bg_and_gre
355 WHERE client_id = p_from_client_id;
356
357 DELETE FROM hr_h2pi_payrolls
358 WHERE client_id = p_from_client_id;
359
360 DELETE FROM hr_h2pi_pay_bases
361 WHERE client_id = p_from_client_id;
362
363 DELETE FROM hr_h2pi_org_payment_methods
364 WHERE client_id = p_from_client_id;
365
366 DELETE FROM hr_h2pi_element_types
367 WHERE client_id = p_from_client_id;
368
369 DELETE FROM hr_h2pi_input_values
370 WHERE client_id = p_from_client_id;
371
372 DELETE FROM hr_h2pi_element_links
373 WHERE client_id = p_from_client_id;
374
375 DELETE FROM hr_h2pi_us_modified_geocodes
376 WHERE client_id = p_from_client_id;
377
378 DELETE FROM hr_h2pi_us_city_names
379 WHERE client_id = p_from_client_id;
380
381 DELETE FROM hr_h2pi_patch_status
382 WHERE client_id = p_from_client_id;
383
384 END;
385
386
387 PROCEDURE upload_person_level (p_from_client_id NUMBER) IS
388
389 CURSOR csr_people (p_bg_id NUMBER) IS
390 SELECT DISTINCT person_id
391 FROM hr_h2pi_employees
392 WHERE (status IS NULL OR status <> 'C')
393 AND client_id = p_bg_id
394 UNION
395 SELECT DISTINCT person_id
396 FROM hr_h2pi_addresses
397 WHERE (status IS NULL OR status <> 'C')
398 AND client_id = p_bg_id
399 UNION
400 SELECT DISTINCT person_id
401 FROM hr_h2pi_assignments
402 WHERE (status IS NULL OR status <> 'C')
403 AND client_id = p_bg_id
404 UNION
405 SELECT DISTINCT person_id
406 FROM hr_h2pi_periods_of_service
407 WHERE (status IS NULL OR status <> 'C')
408 AND client_id = p_bg_id
409 UNION
410 SELECT DISTINCT person_id
411 FROM hr_h2pi_salaries
412 WHERE (status IS NULL OR status <> 'C')
413 AND client_id = p_bg_id
414 UNION
415 SELECT DISTINCT person_id
416 FROM hr_h2pi_payment_methods
417 WHERE (status IS NULL OR status <> 'C')
418 AND client_id = p_bg_id
419 UNION
420 SELECT DISTINCT person_id
421 FROM hr_h2pi_cost_allocations
422 WHERE (status IS NULL OR status <> 'C')
423 AND client_id = p_bg_id
424 UNION
425 SELECT DISTINCT person_id
426 FROM hr_h2pi_element_entries
427 WHERE (status IS NULL OR status <> 'C')
428 AND client_id = p_bg_id
429 UNION
430 SELECT DISTINCT person_id
431 FROM hr_h2pi_federal_tax_rules
432 WHERE (status IS NULL OR status <> 'C')
433 AND client_id = p_bg_id
434 UNION
435 SELECT DISTINCT person_id
436 FROM hr_h2pi_state_tax_rules
437 WHERE (status IS NULL OR status <> 'C')
438 AND client_id = p_bg_id
439 UNION
440 SELECT DISTINCT person_id
441 FROM hr_h2pi_county_tax_rules
442 WHERE (status IS NULL OR status <> 'C')
443 AND client_id = p_bg_id
444 UNION
445 SELECT DISTINCT person_id
446 FROM hr_h2pi_city_tax_rules
447 WHERE (status IS NULL OR status <> 'C')
448 AND client_id = p_bg_id
449 ORDER BY person_id;
450
451 CURSOR csr_person_detail (p_per_id NUMBER) IS
452 SELECT person_id id,
453 effective_start_date eff_date,
454 1 ord,
455 'upload_person' fn_name,
456 DECODE(last_upd_date, g_eot, 1, 2) sub_order
457 FROM HR_H2PI_EMPLOYEES
458 WHERE (status IS NULL OR status <> 'C')
459 AND person_id = p_per_id
460 AND client_id = p_from_client_id
461 UNION
462 SELECT address_id id,
463 date_from eff_date,
464 2 ord,
465 'upload_address' fn_name,
466 DECODE(last_upd_date, g_eot, 1, 2) sub_order
467 FROM HR_H2PI_ADDRESSES
468 WHERE (status IS NULL OR status <> 'C')
469 AND person_id = p_per_id
470 AND client_id = p_from_client_id
471 UNION
472 SELECT assignment_id id,
473 effective_start_date eff_date,
474 3 ord,
475 'upload_assignment' fn_name,
476 DECODE(last_upd_date, g_eot, 1, 2) sub_order
477 FROM HR_H2PI_ASSIGNMENTS
478 WHERE (status IS NULL OR status <> 'C')
479 AND primary_flag = 'Y'
480 AND person_id = p_per_id
481 AND client_id = p_from_client_id
482 UNION
483 SELECT assignment_id id,
484 effective_start_date eff_date,
485 4 ord,
486 'upload_assignment' fn_name,
487 DECODE(last_upd_date, g_eot, 1, 2) sub_order
488 FROM HR_H2PI_ASSIGNMENTS
489 WHERE (status IS NULL OR status <> 'C')
490 AND primary_flag = 'N'
491 AND person_id = p_per_id
492 AND client_id = p_from_client_id
493 UNION
494 SELECT period_of_service_id id,
495 date_start eff_date,
496 5 ord,
497 'upload_period_of_service' fn_name,
498 DECODE(last_upd_date, g_eot, 1, 2) sub_order
499 FROM HR_H2PI_PERIODS_OF_SERVICE
500 WHERE (status IS NULL OR status <> 'C')
501 AND person_id = p_per_id
502 AND client_id = p_from_client_id
503 UNION
504 SELECT pay_proposal_id id,
505 change_date eff_date,
506 6 ord,
507 'upload_salary' fn_name,
508 DECODE(last_upd_date, g_eot, 1, 2) sub_order
509 FROM HR_H2PI_SALARIES
510 WHERE (status IS NULL OR status <> 'C')
511 AND person_id = p_per_id
512 AND client_id = p_from_client_id
513 UNION
514 SELECT personal_payment_method_id id,
515 effective_start_date eff_date,
516 7 ord,
517 'upload_payment_method' fn_name,
518 DECODE(last_upd_date, g_eot, 1, 2) sub_order
519 FROM HR_H2PI_PAYMENT_METHODS
520 WHERE (status IS NULL OR status <> 'C')
521 AND person_id = p_per_id
522 AND client_id = p_from_client_id
523 UNION
524 SELECT cost_allocation_id id,
525 effective_start_date eff_date,
526 8 ord,
527 'upload_cost_allocation' fn_name,
528 DECODE(last_upd_date, g_eot, 1, 2) sub_order
529 FROM HR_H2PI_COST_ALLOCATIONS
530 WHERE (status IS NULL OR status <> 'C')
531 AND person_id = p_per_id
532 AND client_id = p_from_client_id
533 UNION
534 SELECT element_entry_id id,
535 effective_start_date eff_date,
536 9 ord,
537 'upload_element_entry' fn_name,
538 DECODE(last_upd_date, g_eot, 1, 2) sub_order
539 FROM HR_H2PI_ELEMENT_ENTRIES
540 WHERE (status IS NULL OR status <> 'C')
541 AND creator_type <> 'UT'
542 AND person_id = p_per_id
543 AND client_id = p_from_client_id
544 UNION
545 SELECT emp_fed_tax_rule_id id,
546 effective_start_date eff_date,
547 10 ord,
548 'upload_federal_tax_record' fn_name,
549 DECODE(last_upd_date, g_eot, 1, 2) sub_order
550 FROM HR_H2PI_FEDERAL_TAX_RULES
551 WHERE (status IS NULL OR status <> 'C')
552 AND person_id = p_per_id
553 AND client_id = p_from_client_id
554 UNION
555 SELECT emp_state_tax_rule_id id,
556 effective_start_date eff_date,
557 11 ord,
558 'upload_state_tax_record' fn_name,
559 DECODE(last_upd_date, g_eot, 1, 2) sub_order
560 FROM HR_H2PI_STATE_TAX_RULES
561 WHERE (status IS NULL OR status <> 'C')
562 AND person_id = p_per_id
563 AND client_id = p_from_client_id
564 UNION
565 SELECT emp_county_tax_rule_id id,
566 effective_start_date eff_date,
567 12 ord,
568 'upload_county_tax_record' fn_name,
569 DECODE(last_upd_date, g_eot, 1, 2) sub_order
570 FROM HR_H2PI_COUNTY_TAX_RULES
571 WHERE (status IS NULL OR status <> 'C')
572 AND person_id = p_per_id
573 AND client_id = p_from_client_id
574 UNION
575 SELECT emp_city_tax_rule_id id,
576 effective_start_date eff_date,
577 13 ord,
578 'upload_city_tax_record' fn_name,
579 DECODE(last_upd_date, g_eot, 1, 2) sub_order
580 FROM HR_H2PI_CITY_TAX_RULES
581 WHERE (status IS NULL OR status <> 'C')
582 AND person_id = p_per_id
583 AND client_id = p_from_client_id
584 ORDER BY eff_date,
585 ord,
586 sub_order;
587
588 l_proc VARCHAR2(72) := g_package||'upload_person_level';
589
590 l_from_client_id NUMBER;
591
592 l_csr_handle INTEGER;
593 l_sql_parse VARCHAR2(2000);
594 l_csr_rows INTEGER;
595 l_date_char VARCHAR2(11);
596
597
598 BEGIN
599 l_from_client_id := p_from_client_id;
600
601 hr_utility.set_location('Entering:'|| l_proc, 10);
602 hr_h2pi_person_upload.create_end_date_records(l_from_client_id);
603
604 FOR v_per IN csr_people(l_from_client_id) LOOP
605
606 hr_utility.set_location(l_proc, 20);
607 SAVEPOINT person_start;
608
609 BEGIN
610 delete_address(l_from_client_id,
611 v_per.person_id);
612
613 hr_utility.set_location(l_proc, 30);
614 FOR v_per_det IN csr_person_detail(v_per.person_id) LOOP
615
616 BEGIN
617
618 hr_utility.set_location(l_proc, 40);
619 l_date_char := TO_CHAR(v_per_det.eff_date, 'YYYY/MM/DD');
620 l_sql_parse :=
621 'BEGIN '||fnd_global.local_chr(10)||
622 'hr_h2pi_person_upload.'||
623 v_per_det.fn_name||'('||l_from_client_id||','||fnd_global.local_chr(10)||
624 v_per_det.id||','||fnd_global.local_chr(10)||
625 'TO_DATE('||''''||l_date_char||''''||
626 ','||''''||'YYYY/MM/DD'||''''||'));'||fnd_global.local_chr(10)||
627 'END;';
628
629 hr_utility.trace(l_sql_parse);
630 l_csr_handle := dbms_sql.open_cursor;
631 dbms_sql.parse(l_csr_handle,
632 l_sql_parse,
633 dbms_sql.native);
634 l_csr_rows := dbms_sql.execute(l_csr_handle);
635 dbms_sql.close_cursor(l_csr_handle);
636
637 EXCEPTION
638 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
639 hr_utility.set_location(l_proc, 50);
640 RAISE PERSON_ERROR;
641 WHEN MAPPING_ID_MISSING THEN
642 hr_utility.set_location(l_proc, 60);
643 RAISE PERSON_ERROR;
644 WHEN MAPPING_ID_INVALID THEN
645 hr_utility.set_location(l_proc, 70);
646 RAISE PERSON_ERROR;
647 END;
648
649 END LOOP;
650
651 upload_tax_percentage(p_from_client_id => p_from_client_id,
652 p_person_id => v_per.person_id);
653
654
655 EXCEPTION
656 WHEN PERSON_ERROR THEN
657 hr_utility.set_location(l_proc, 60);
658 COMMIT;
659 END;
660
661 END LOOP;
662
663 IF NOT hr_h2pi_error.check_for_errors THEN
664 hr_utility.set_location(l_proc, 70);
665 remove_staging_table_data(l_from_client_id);
666 END IF;
667
668 hr_utility.set_location('Leaving:'|| l_proc, 100);
669
670 COMMIT;
671
672 END;
673
674
675 PROCEDURE upload_person (p_from_client_id NUMBER, --
676 p_person_id NUMBER,
677 p_effective_start_date DATE) IS
678
679 CURSOR csr_ud_person (p_per_id NUMBER,
680 p_esd DATE) IS
681 SELECT *
682 FROM hr_h2pi_employees per
683 WHERE per.person_id = p_per_id
684 AND per.client_id = p_from_client_id
685 AND per.effective_start_date = p_esd;
686
687 CURSOR csr_ed_person (p_per_id NUMBER,
688 p_esd DATE) IS
689 SELECT per.person_type,
690 per.effective_start_date,
691 per.effective_end_date
692 FROM hr_h2pi_employees_v per
693 WHERE per.person_id = p_per_id
694 AND p_esd BETWEEN per.effective_start_date
695 AND per.effective_end_date;
696
697 CURSOR csr_ed_person_ovn (p_per_id NUMBER,
698 p_esd DATE) IS
699 SELECT per.object_version_number
700 FROM per_all_people_f per
701 WHERE per.person_id = p_per_id
702 AND p_esd BETWEEN per.effective_start_date
703 AND per.effective_end_date;
704
705
706 l_encoded_message VARCHAR2(200);
707
708 l_proc VARCHAR2(72) := g_package||'upload_person';
709
710 v_ud_per hr_h2pi_employees%ROWTYPE;
711
712 l_ud_person_id per_all_people_f.person_id%TYPE;
713 l_ud_assignment_id per_all_assignments_f.assignment_id%TYPE;
714 l_ud_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
715
716 l_person_id per_all_people_f.person_id%TYPE;
717 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
718 l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
719 l_person_type per_person_types.system_person_type%TYPE;
720 l_ovn per_all_people_f.object_version_number%TYPE;
721 l_asg_ovn per_all_people_f.object_version_number%TYPE;
722 l_esd per_all_people_f.effective_start_date%TYPE;
723 l_eed per_all_people_f.effective_end_date%TYPE;
724 l_comment_id per_all_people_f.comment_id%TYPE;
725 l_full_name per_all_people_f.full_name%TYPE;
726 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
727 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
728 l_name_combination_warning BOOLEAN;
729 l_assign_payroll_warning BOOLEAN;
730 l_orig_hire_warning BOOLEAN;
731
732 l_max_eed per_all_people_f.effective_end_date%TYPE;
733 l_del_ovn per_all_people_f.object_version_number%TYPE;
734 l_del_esd per_all_people_f.effective_start_date%TYPE;
735 l_del_eed per_all_people_f.effective_end_date%TYPE;
736
737 l_records_same BOOLEAN;
738 l_future_records BOOLEAN;
739 l_update_mode VARCHAR2(30);
740 l_delete_mode VARCHAR2(30);
741
742 BEGIN
743 hr_utility.set_location('Entering:'|| l_proc, 10);
744
745 OPEN csr_ud_person(p_person_id,
746 p_effective_start_date);
747 FETCH csr_ud_person INTO v_ud_per;
748
749 l_person_id := hr_h2pi_map.get_to_id
750 (p_table_name => 'PER_ALL_PEOPLE_F',
751 p_from_id => p_person_id);
752
753 IF l_person_id = -1 THEN
754 hr_utility.set_location(l_proc, 20);
755
756 hr_employee_api.create_employee (
757 p_hire_date => v_ud_per.effective_start_date
758 ,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
759 ,p_last_name => v_ud_per.last_name
760 ,p_sex => v_ud_per.sex
761 ,p_date_of_birth => v_ud_per.date_of_birth
762 ,p_email_address => v_ud_per.email_address
763 ,p_employee_number => v_ud_per.employee_number
764 ,p_expense_check_send_to_addres
765 => v_ud_per.expense_check_send_to_address
766 ,p_first_name => v_ud_per.first_name
767 ,p_marital_status => v_ud_per.marital_status
768 ,p_middle_names => v_ud_per.middle_names
769 -- ,p_nationality => v_ud_per.nationality
770 ,p_national_identifier => v_ud_per.national_identifier
771 ,p_registered_disabled_flag => v_ud_per.registered_disabled_flag
772 ,p_title => v_ud_per.title
773 ,p_attribute_category => v_ud_per.attribute_category
774 ,p_attribute1 => v_ud_per.attribute1
775 ,p_attribute2 => v_ud_per.attribute2
776 ,p_attribute3 => v_ud_per.attribute3
777 ,p_attribute4 => v_ud_per.attribute4
778 ,p_attribute5 => v_ud_per.attribute5
779 ,p_attribute6 => v_ud_per.attribute6
780 ,p_attribute7 => v_ud_per.attribute7
781 ,p_attribute8 => v_ud_per.attribute8
782 ,p_attribute9 => v_ud_per.attribute9
783 ,p_attribute10 => v_ud_per.attribute10
784 ,p_attribute11 => v_ud_per.attribute11
785 ,p_attribute12 => v_ud_per.attribute12
786 ,p_attribute13 => v_ud_per.attribute13
787 ,p_attribute14 => v_ud_per.attribute14
788 ,p_attribute15 => v_ud_per.attribute15
789 ,p_attribute16 => v_ud_per.attribute16
790 ,p_attribute17 => v_ud_per.attribute17
791 ,p_attribute18 => v_ud_per.attribute18
792 ,p_attribute19 => v_ud_per.attribute19
793 ,p_attribute20 => v_ud_per.attribute20
794 ,p_attribute21 => v_ud_per.attribute21
795 ,p_attribute22 => v_ud_per.attribute22
796 ,p_attribute23 => v_ud_per.attribute23
797 ,p_attribute24 => v_ud_per.attribute24
798 ,p_attribute25 => v_ud_per.attribute25
799 ,p_attribute26 => v_ud_per.attribute26
800 ,p_attribute27 => v_ud_per.attribute27
801 ,p_attribute28 => v_ud_per.attribute28
802 ,p_attribute29 => v_ud_per.attribute29
803 ,p_attribute30 => v_ud_per.attribute30
804 ,p_per_information_category => v_ud_per.per_information_category
805 ,p_per_information1 => v_ud_per.per_information1
806 ,p_per_information2 => v_ud_per.per_information2
807 ,p_per_information3 => v_ud_per.per_information3
808 ,p_per_information4 => v_ud_per.per_information4
809 ,p_per_information5 => v_ud_per.per_information5
810 ,p_per_information6 => v_ud_per.per_information6
811 ,p_per_information7 => v_ud_per.per_information7
812 ,p_per_information8 => v_ud_per.per_information8
813 ,p_per_information9 => v_ud_per.per_information9
814 ,p_per_information10 => v_ud_per.per_information10
815 ,p_per_information11 => v_ud_per.per_information11
816 ,p_per_information12 => v_ud_per.per_information12
817 ,p_per_information13 => v_ud_per.per_information13
818 ,p_per_information14 => v_ud_per.per_information14
819 ,p_per_information15 => v_ud_per.per_information15
820 ,p_per_information16 => v_ud_per.per_information16
821 ,p_per_information17 => v_ud_per.per_information17
822 ,p_per_information18 => v_ud_per.per_information18
823 ,p_per_information19 => v_ud_per.per_information19
824 ,p_per_information20 => v_ud_per.per_information20
825 ,p_per_information21 => v_ud_per.per_information21
826 ,p_per_information22 => v_ud_per.per_information22
827 ,p_per_information23 => v_ud_per.per_information23
828 ,p_per_information24 => v_ud_per.per_information24
829 ,p_per_information25 => v_ud_per.per_information25
830 ,p_per_information26 => v_ud_per.per_information26
831 ,p_per_information27 => v_ud_per.per_information27
832 ,p_per_information28 => v_ud_per.per_information28
833 ,p_per_information29 => v_ud_per.per_information29
834 ,p_per_information30 => v_ud_per.per_information30
835 ,p_date_of_death => v_ud_per.date_of_death
836 ,p_correspondence_language => v_ud_per.correspondence_language
837 ,p_office_number => v_ud_per.office_number
838 ,p_pre_name_adjunct => v_ud_per.pre_name_adjunct
839 ,p_suffix => v_ud_per.suffix
840 ,p_person_id => l_person_id
841 ,p_assignment_id => l_assignment_id
842 ,p_per_object_version_number => l_ovn
843 ,p_asg_object_version_number => l_asg_ovn
844 ,p_per_effective_start_date => l_esd
845 ,p_per_effective_end_date => l_eed
846 ,p_full_name => l_full_name
847 ,p_per_comment_id => l_comment_id
848 ,p_assignment_sequence => l_assignment_sequence
849 ,p_assignment_number => l_assignment_number
850 ,p_name_combination_warning => l_name_combination_warning
851 ,p_assign_payroll_warning => l_assign_payroll_warning
852 ,p_orig_hire_warning => l_orig_hire_warning
853 );
854
855 hr_utility.set_location(l_proc, 30);
856 hr_h2pi_map.create_id_mapping
857 (p_table_name => 'PER_ALL_PEOPLE_F',
858 p_from_id => p_person_id,
859 p_to_id => l_person_id);
860
861 SELECT asg.assignment_id
862 INTO l_ud_assignment_id
863 FROM hr_h2pi_assignments asg
864 WHERE asg.person_id = v_ud_per.person_id
865 AND asg.client_id = p_from_client_id
866 AND asg.effective_start_date = v_ud_per.effective_start_date
867 AND asg.primary_flag = 'Y';
868
869 hr_h2pi_map.create_id_mapping
870 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
871 p_from_id => l_ud_assignment_id,
872 p_to_id => l_assignment_id);
873 BEGIN
874 SELECT pos.period_of_service_id
875 INTO l_ud_period_of_service_id
876 FROM hr_h2pi_periods_of_service pos
877 WHERE pos.person_id = v_ud_per.person_id
878 AND pos.client_id = p_from_client_id
879 AND pos.date_start = v_ud_per.effective_start_date;
880
881 SELECT pos.period_of_service_id
882 INTO l_period_of_service_id
883 FROM hr_h2pi_periods_of_service_v pos
884 WHERE pos.person_id = l_person_id
885 AND pos.date_start = l_esd;
886
887 hr_h2pi_map.create_id_mapping
888 (p_table_name => 'PER_PERIODS_OF_SERVICE',
889 p_from_id => l_ud_period_of_service_id,
890 p_to_id => l_period_of_service_id);
891 EXCEPTION
892 WHEN NO_DATA_FOUND THEN NULL;
893 END;
894
895 ELSE
896
897 BEGIN
898 hr_utility.set_location(l_proc, 50);
899 OPEN csr_ed_person(l_person_id,
900 v_ud_per.effective_start_date);
901 FETCH csr_ed_person
902 INTO l_person_type,
903 l_esd,
904 l_eed;
905 IF csr_ed_person%NOTFOUND THEN
906 hr_utility.set_location(l_proc, 60);
907 CLOSE csr_ed_person;
908 ROLLBACK;
909 hr_h2pi_error.data_error
910 (p_from_id => l_person_id,
911 p_table_name => 'HR_H2PI_EMPLOYEES',
912 p_message_level => 'FATAL',
913 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
914 COMMIT;
915 RAISE MAPPING_ID_INVALID;
916 ELSE
917 hr_utility.set_location(l_proc, 70);
918 CLOSE csr_ed_person;
919 END IF;
920
921 OPEN csr_ed_person_ovn(l_person_id,
922 v_ud_per.effective_start_date);
923 FETCH csr_ed_person_ovn
924 INTO l_ovn;
925 CLOSE csr_ed_person_ovn;
926 END;
927
928 IF v_ud_per.person_type = l_person_type THEN
929
930 hr_utility.set_location(l_proc, 80);
931 l_delete_mode := 'DELETE_NEXT_CHANGE';
932 LOOP
933 hr_utility.set_location(l_proc, 90);
934 l_records_same := FALSE;
935
936 SELECT MAX(per.effective_end_date)
937 INTO l_max_eed
938 FROM per_all_people_f per
939 WHERE per.person_id = l_person_id;
940
941 IF l_max_eed > l_eed THEN
942 hr_utility.set_location(l_proc, 100);
943 l_future_records := TRUE;
944 END IF;
945
946 calculate_datetrack_mode
947 (p_ud_start_date => v_ud_per.effective_start_date
948 ,p_ud_end_date => v_ud_per.effective_end_date
949 ,p_ed_start_date => l_esd
950 ,p_ed_end_date => l_eed
951 ,p_records_same => l_records_same
952 ,p_future_records => l_future_records
953 ,p_update_mode => l_update_mode
954 ,p_delete_mode => l_delete_mode);
955
956 EXIT WHEN l_delete_mode = 'X';
957
958 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
959
960 hr_utility.set_location(l_proc, 110);
961 per_per_del.del(p_person_id => l_person_id
962 ,p_effective_start_date => l_del_esd
963 ,p_effective_end_date => l_del_eed
964 ,p_object_version_number => l_ovn
965 ,p_effective_date => l_eed
966 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE');
967
968 OPEN csr_ed_person(l_person_id,
969 v_ud_per.effective_start_date);
970 FETCH csr_ed_person
971 INTO l_person_type,
972 l_esd,
973 l_eed;
974 CLOSE csr_ed_person;
975
976 END IF;
977
978 END LOOP;
979
980 hr_utility.set_location(l_proc, 120);
981 hr_person_api.update_person(
982 p_effective_date => v_ud_per.effective_start_date
983 ,p_datetrack_update_mode => l_update_mode
984 ,p_person_id => l_person_id
985 ,p_object_version_number => l_ovn
986 ,p_last_name => v_ud_per.last_name
987 ,p_date_of_birth => v_ud_per.date_of_birth
988 ,p_email_address => v_ud_per.email_address
989 ,p_employee_number => v_ud_per.employee_number
990 ,p_expense_check_send_to_addres
991 => v_ud_per.expense_check_send_to_address
992 ,p_first_name => v_ud_per.first_name
993 ,p_marital_status => v_ud_per.marital_status
994 ,p_middle_names => v_ud_per.middle_names
995 -- ,p_nationality => v_ud_per.nationality
996 ,p_national_identifier => v_ud_per.national_identifier
997 ,p_registered_disabled_flag => v_ud_per.registered_disabled_flag
998 ,p_sex => v_ud_per.sex
999 ,p_title => v_ud_per.title
1000 ,p_attribute_category => v_ud_per.attribute_category
1001 ,p_attribute1 => v_ud_per.attribute1
1002 ,p_attribute2 => v_ud_per.attribute2
1003 ,p_attribute3 => v_ud_per.attribute3
1004 ,p_attribute4 => v_ud_per.attribute4
1005 ,p_attribute5 => v_ud_per.attribute5
1006 ,p_attribute6 => v_ud_per.attribute6
1007 ,p_attribute7 => v_ud_per.attribute7
1008 ,p_attribute8 => v_ud_per.attribute8
1009 ,p_attribute9 => v_ud_per.attribute9
1010 ,p_attribute10 => v_ud_per.attribute10
1011 ,p_attribute11 => v_ud_per.attribute11
1012 ,p_attribute12 => v_ud_per.attribute12
1013 ,p_attribute13 => v_ud_per.attribute13
1014 ,p_attribute14 => v_ud_per.attribute14
1015 ,p_attribute15 => v_ud_per.attribute15
1016 ,p_attribute16 => v_ud_per.attribute16
1017 ,p_attribute17 => v_ud_per.attribute17
1018 ,p_attribute18 => v_ud_per.attribute18
1019 ,p_attribute19 => v_ud_per.attribute19
1020 ,p_attribute20 => v_ud_per.attribute20
1021 ,p_attribute21 => v_ud_per.attribute21
1022 ,p_attribute22 => v_ud_per.attribute22
1023 ,p_attribute23 => v_ud_per.attribute23
1024 ,p_attribute24 => v_ud_per.attribute24
1025 ,p_attribute25 => v_ud_per.attribute25
1026 ,p_attribute26 => v_ud_per.attribute26
1027 ,p_attribute27 => v_ud_per.attribute27
1028 ,p_attribute28 => v_ud_per.attribute28
1029 ,p_attribute29 => v_ud_per.attribute29
1030 ,p_attribute30 => v_ud_per.attribute30
1031 ,p_per_information_category => v_ud_per.per_information_category
1032 ,p_per_information1 => v_ud_per.per_information1
1033 ,p_per_information2 => v_ud_per.per_information2
1034 ,p_per_information3 => v_ud_per.per_information3
1035 ,p_per_information4 => v_ud_per.per_information4
1036 ,p_per_information5 => v_ud_per.per_information5
1037 ,p_per_information6 => v_ud_per.per_information6
1038 ,p_per_information7 => v_ud_per.per_information7
1039 ,p_per_information8 => v_ud_per.per_information8
1040 ,p_per_information9 => v_ud_per.per_information9
1041 ,p_per_information10 => v_ud_per.per_information10
1042 ,p_per_information11 => v_ud_per.per_information11
1043 ,p_per_information12 => v_ud_per.per_information12
1044 ,p_per_information13 => v_ud_per.per_information13
1045 ,p_per_information14 => v_ud_per.per_information14
1046 ,p_per_information15 => v_ud_per.per_information15
1047 ,p_per_information16 => v_ud_per.per_information16
1048 ,p_per_information17 => v_ud_per.per_information17
1049 ,p_per_information18 => v_ud_per.per_information18
1050 ,p_per_information19 => v_ud_per.per_information19
1051 ,p_per_information20 => v_ud_per.per_information20
1052 ,p_per_information21 => v_ud_per.per_information21
1053 ,p_per_information22 => v_ud_per.per_information22
1054 ,p_per_information23 => v_ud_per.per_information23
1055 ,p_per_information24 => v_ud_per.per_information24
1056 ,p_per_information25 => v_ud_per.per_information25
1057 ,p_per_information26 => v_ud_per.per_information26
1058 ,p_per_information27 => v_ud_per.per_information27
1059 ,p_per_information28 => v_ud_per.per_information28
1060 ,p_per_information29 => v_ud_per.per_information29
1061 ,p_per_information30 => v_ud_per.per_information30
1062 ,p_date_of_death => v_ud_per.date_of_death
1063 ,p_correspondence_language => v_ud_per.correspondence_language
1064 ,p_office_number => v_ud_per.office_number
1065 ,p_pre_name_adjunct => v_ud_per.pre_name_adjunct
1066 ,p_suffix => v_ud_per.suffix
1067 ,p_effective_start_date => l_esd
1068 ,p_effective_end_date => l_eed
1069 ,p_full_name => l_full_name
1070 ,p_comment_id => l_comment_id
1071 ,p_name_combination_warning => l_name_combination_warning
1072 ,p_assign_payroll_warning => l_assign_payroll_warning
1073 ,p_orig_hire_warning => l_orig_hire_warning
1074 );
1075
1076 ELSE
1077
1078 IF v_ud_per.person_type = 'EMP' THEN
1079
1080 hr_utility.set_location(l_proc, 130);
1081 hr_employee_api.re_hire_ex_employee(
1082 p_hire_date => v_ud_per.effective_start_date
1083 ,p_person_id => l_person_id
1084 ,p_per_object_version_number => l_ovn
1085 ,p_rehire_reason => v_ud_per.rehire_reason
1086 ,p_assignment_id => l_assignment_id
1087 ,p_asg_object_version_number => l_asg_ovn
1088 ,p_per_effective_start_date => l_esd
1089 ,p_per_effective_end_date => l_eed
1090 ,p_assignment_sequence => l_assignment_sequence
1091 ,p_assignment_number => l_assignment_number
1092 ,p_assign_payroll_warning => l_assign_payroll_warning
1093 );
1094
1095 hr_utility.set_location(l_proc, 140);
1096 SELECT asg.assignment_id
1097 INTO l_ud_assignment_id
1098 FROM hr_h2pi_assignments asg
1099 WHERE asg.person_id = v_ud_per.person_id
1100 AND asg.client_id = p_from_client_id
1101 AND asg.effective_start_date = v_ud_per.effective_start_date
1102 AND asg.primary_flag = 'Y';
1103
1104 hr_h2pi_map.create_id_mapping
1105 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
1106 p_from_id => l_ud_assignment_id,
1107 p_to_id => l_assignment_id);
1108
1109 BEGIN
1110 hr_utility.set_location(l_proc, 150);
1111 SELECT pos.period_of_service_id
1112 INTO l_ud_period_of_service_id
1113 FROM hr_h2pi_periods_of_service pos
1114 WHERE pos.person_id = v_ud_per.person_id
1115 AND pos.client_id = p_from_client_id
1116 AND pos.date_start = v_ud_per.effective_start_date ;
1117
1118 SELECT pos.period_of_service_id
1119 INTO l_period_of_service_id
1120 FROM hr_h2pi_periods_of_service_v pos
1121 WHERE pos.person_id = l_person_id
1122 AND pos.date_start = l_esd ;
1123
1124 hr_h2pi_map.create_id_mapping
1125 (p_table_name => 'PER_PERIODS_OF_SERVICE',
1126 p_from_id => l_ud_period_of_service_id,
1127 p_to_id => l_period_of_service_id);
1128 EXCEPTION
1129 WHEN NO_DATA_FOUND THEN NULL;
1130 END;
1131
1132 ELSE
1133
1134 hr_utility.set_location(l_proc, 160);
1135 terminate_person
1136 (p_from_client_id => p_from_client_id,
1137 p_person_id => v_ud_per.person_id,
1138 p_effective_start_date => v_ud_per.effective_start_date);
1139
1140 END IF;
1141
1142 END IF;
1143 END IF;
1144
1145 hr_utility.set_location(l_proc, 170);
1146 UPDATE hr_h2pi_employees per
1147 SET status = 'C'
1148 WHERE per.person_id = v_ud_per.person_id
1149 AND per.client_id = p_from_client_id
1150 AND per.effective_start_date = v_ud_per.effective_start_date
1151 AND per.effective_end_date = v_ud_per.effective_end_date;
1152
1153 CLOSE csr_ud_person;
1154 hr_utility.set_location('Leaving:'|| l_proc, 180);
1155 COMMIT;
1156
1157 EXCEPTION
1158 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
1159 ROLLBACK;
1160 hr_utility.set_location(l_proc, 200);
1161 l_encoded_message := fnd_message.get_encoded;
1162 hr_utility.set_location(l_encoded_message, 200);
1163 hr_h2pi_error.data_error
1164 (p_from_id => v_ud_per.person_id,
1165 p_table_name => 'HR_H2PI_EMPLOYEES',
1166 p_message_level => 'FATAL',
1167 p_message_text => l_encoded_message);
1168 COMMIT;
1169 RAISE;
1170
1171 END;
1172
1173
1174 PROCEDURE terminate_person(p_from_client_id NUMBER,
1175 p_person_id NUMBER,
1176 p_effective_start_date DATE) IS
1177
1178 CURSOR csr_ud_periods_of_service(p_per_id NUMBER,
1179 p_esd DATE) IS
1180 SELECT *
1181 FROM hr_h2pi_periods_of_service pos
1182 WHERE pos.person_id = p_per_id
1183 AND pos.client_id = p_from_client_id
1184 AND pos.actual_termination_date = p_esd - 1;
1185
1186 CURSOR csr_ed_periods_of_service(p_pos_id NUMBER) IS
1187 SELECT pos.object_version_number,
1188 pos.actual_termination_date,
1189 pos.final_process_date
1190 FROM per_periods_of_service pos
1191 WHERE pos.period_of_service_id = p_pos_id;
1192
1193 l_proc VARCHAR2(72) := g_package||'terminate_person';
1194
1195 l_encoded_message VARCHAR2(200);
1196
1197 v_ud_pos hr_h2pi_periods_of_service%ROWTYPE;
1198
1199 l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
1200 l_ovn per_periods_of_service.object_version_number%TYPE;
1201 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
1202 l_final_process_date per_periods_of_service.final_process_date%TYPE;
1203
1204 l_supervisor_warning BOOLEAN;
1205 l_event_warning BOOLEAN;
1206 l_interview_warning BOOLEAN;
1207 l_review_warning BOOLEAN;
1208 l_recruiter_warning BOOLEAN;
1209 l_asg_future_changes_warning BOOLEAN;
1210 l_entries_changed_warning VARCHAR2(1);
1211 l_pay_proposal_warning BOOLEAN;
1212 l_dod_warning BOOLEAN;
1213 l_org_now_no_manager_warning BOOLEAN;
1214
1215 BEGIN
1216 hr_utility.set_location('Entering:'|| l_proc, 10);
1217
1218 OPEN csr_ud_periods_of_service(p_person_id,
1219 p_effective_start_date);
1220 FETCH csr_ud_periods_of_service INTO v_ud_pos;
1221 CLOSE csr_ud_periods_of_service;
1222
1223 l_period_of_service_id := hr_h2pi_map.get_to_id
1224 (p_table_name => 'PER_PERIODS_OF_SERVICE',
1225 p_from_id => v_ud_pos.period_of_service_id,
1226 p_report_error => TRUE);
1227
1228 OPEN csr_ed_periods_of_service(l_period_of_service_id);
1229 FETCH csr_ed_periods_of_service
1230 INTO l_ovn,
1231 l_actual_termination_date,
1232 l_final_process_date;
1233 CLOSE csr_ed_periods_of_service;
1234
1235 IF (l_actual_termination_date IS NULL) AND
1236 (v_ud_pos.actual_termination_date IS NOT NULL) THEN
1237
1238 hr_utility.set_location(l_proc, 30);
1239 hr_ex_employee_api.actual_termination_emp(
1240 p_effective_date => v_ud_pos.actual_termination_date
1241 ,p_period_of_service_id => l_period_of_service_id
1242 ,p_object_version_number => l_ovn
1243 ,p_actual_termination_date => v_ud_pos.actual_termination_date
1244 ,p_last_standard_process_date => v_ud_pos.last_standard_process_date
1245 ,p_leaving_reason => v_ud_pos.leaving_reason
1246 ,p_supervisor_warning => l_supervisor_warning
1247 ,p_event_warning => l_event_warning
1248 ,p_interview_warning => l_interview_warning
1249 ,p_review_warning => l_review_warning
1250 ,p_recruiter_warning => l_recruiter_warning
1251 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1252 ,p_entries_changed_warning => l_entries_changed_warning
1253 ,p_pay_proposal_warning => l_pay_proposal_warning
1254 ,p_dod_warning => l_dod_warning
1255 );
1256
1257 END IF;
1258
1259 IF (l_final_process_date IS NULL) AND
1260 (v_ud_pos.actual_termination_date = v_ud_pos.final_process_date) THEN
1261
1262 hr_utility.set_location(l_proc, 40);
1263 hr_ex_employee_api.final_process_emp(
1264 p_period_of_service_id => l_period_of_service_id
1265 ,p_object_version_number => l_ovn
1266 ,p_final_process_date => v_ud_pos.final_process_date
1267 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1268 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1269 ,p_entries_changed_warning => l_entries_changed_warning
1270 );
1271
1272 END IF;
1273
1274 hr_utility.set_location('Leaving:'|| l_proc, 50);
1275
1276 END;
1277
1278
1279 PROCEDURE delete_address (p_from_client_id NUMBER,
1280 p_person_id NUMBER) IS
1281
1282 CURSOR csr_ud_address (p_adr_id NUMBER) IS
1283 SELECT address_id,
1284 date_from,
1285 date_to
1286 FROM hr_h2pi_addresses adr
1287 WHERE adr.address_id = p_adr_id
1288 AND adr.client_id = p_from_client_id
1289 AND (adr.status IS NULL OR adr.status <> 'C');
1290
1291 CURSOR csr_ed_addresses (p_per_id NUMBER) IS
1292 SELECT address_id,
1293 object_version_number,
1294 date_from,
1295 date_to
1296 FROM per_addresses adr
1297 WHERE adr.person_id = p_per_id;
1298
1299 l_proc VARCHAR2(72) := g_package||'delete_address';
1300
1301 l_encoded_message VARCHAR2(200);
1302
1303 l_person_id per_addresses.person_id%TYPE;
1304 l_ud_address_id per_addresses.address_id%TYPE;
1305 l_ud_date_from per_addresses.date_from%TYPE;
1306 l_ud_date_to per_addresses.date_to%TYPE;
1307 v_ed_adr hr_h2pi_addresses%ROWTYPE;
1308
1309 BEGIN
1310 hr_utility.set_location('Entering:'|| l_proc, 10);
1311
1312 l_person_id := hr_h2pi_map.get_to_id
1313 (p_table_name => 'PER_ALL_PEOPLE_F',
1314 p_from_id => p_person_id);
1315
1316 IF l_person_id <> -1 THEN
1317
1318 FOR v_ed_adr IN csr_ed_addresses(l_person_id) LOOP
1319 hr_utility.set_location(l_proc, 20);
1320 l_ud_address_id := hr_h2pi_map.get_from_id
1321 (p_table_name => 'PER_ADDRESSES',
1322 p_to_id => v_ed_adr.address_id);
1323
1324 IF l_ud_address_id <> -1 THEN
1325 hr_utility.set_location(l_proc, 30);
1326 OPEN csr_ud_address(l_ud_address_id);
1327 FETCH csr_ud_address INTO l_ud_address_id,
1328 l_ud_date_from,
1329 l_ud_date_to;
1330 IF (csr_ud_address%FOUND AND
1331 (v_ed_adr.date_from <> l_ud_date_from OR
1332 v_ed_adr.date_to <> l_ud_date_to)) THEN
1333
1334 hr_utility.set_location(l_proc, 40);
1335 per_add_del.del
1336 (p_address_id => v_ed_adr.address_id,
1337 p_object_version_number => v_ed_adr.object_version_number);
1338
1339 DELETE FROM hr_h2pi_id_mapping
1340 WHERE table_name = 'PER_ADDRESSES'
1341 AND to_id = v_ed_adr.address_id
1342 AND to_business_group_id = hr_h2pi_upload.g_to_business_group_id;
1343
1344 END IF;
1345 CLOSE csr_ud_address;
1346 END IF;
1347 END LOOP;
1348 END IF;
1349 hr_utility.set_location('Leaving:'|| l_proc, 50);
1350 COMMIT;
1351
1352 EXCEPTION
1353 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
1354 ROLLBACK;
1355 hr_utility.set_location(l_proc, 60);
1356 l_encoded_message := fnd_message.get_encoded;
1357 hr_h2pi_error.data_error
1358 (p_from_id => l_ud_address_id,
1359 p_table_name => 'HR_H2PI_ADDRESSES',
1360 p_message_level => 'FATAL',
1361 p_message_text => l_encoded_message);
1362 COMMIT;
1363 RAISE;
1364
1365 END;
1366
1367
1368 PROCEDURE upload_address (p_from_client_id NUMBER,
1369 p_address_id NUMBER,
1370 p_effective_start_date DATE) IS
1371
1372 CURSOR csr_ud_address (p_adr_id NUMBER) IS
1373 SELECT *
1374 FROM hr_h2pi_addresses adr
1375 WHERE adr.address_id = p_adr_id
1376 AND adr.client_id = p_from_client_id;
1377
1378 CURSOR csr_ed_address (p_adr_id NUMBER) IS
1379 SELECT object_version_number
1380 FROM per_addresses adr
1381 WHERE adr.address_id = p_adr_id;
1382
1383 l_proc VARCHAR2(72) := g_package||'upload_address';
1384
1385 l_encoded_message VARCHAR2(200);
1386
1387 l_person_id per_addresses.person_id%TYPE;
1388 l_address_id per_addresses.address_id%TYPE;
1389 l_ovn per_addresses.object_version_number%TYPE;
1390 v_ud_adr hr_h2pi_addresses%ROWTYPE;
1391 l_per_start_date per_all_people_f.effective_start_date%TYPE;
1392 l_date_from per_addresses.date_from%TYPE;
1393
1394 BEGIN
1395 hr_utility.set_location('Entering:'|| l_proc, 10);
1396
1397 OPEN csr_ud_address(p_address_id);
1398 FETCH csr_ud_address INTO v_ud_adr;
1399
1400 l_person_id := hr_h2pi_map.get_to_id
1401 (p_table_name => 'PER_ALL_PEOPLE_F',
1402 p_from_id => v_ud_adr.person_id,
1403 p_report_error => TRUE);
1404
1405 hr_utility.set_location(l_proc, 20);
1406 l_address_id := hr_h2pi_map.get_to_id
1407 (p_table_name => 'PER_ADDRESSES',
1408 p_from_id => v_ud_adr.address_id);
1409
1410 IF l_address_id = -1 THEN
1411 hr_utility.set_location(l_proc, 30);
1412 /*
1413 * Check that the employee exists for the date range
1414 */
1415 SELECT MIN(per.effective_start_date)
1416 INTO l_per_start_date
1417 FROM per_all_people_f per
1418 WHERE per.person_id = l_person_id;
1419 IF NVL(v_ud_adr.date_to, l_per_start_date) >= l_per_start_date THEN
1420 IF v_ud_adr.date_from < l_per_start_date THEN
1421 l_date_from := l_per_start_date;
1422 ELSE
1423 l_date_from := v_ud_adr.date_from;
1424 END IF;
1425 hr_person_address_api.create_person_address(
1426 p_effective_date => v_ud_adr.date_from
1427 ,p_pradd_ovlapval_override => FALSE
1428 ,p_person_id => l_person_id
1429 ,p_primary_flag => 'Y'
1430 ,p_style => v_ud_adr.style
1431 ,p_date_from => l_date_from
1432 ,p_date_to => v_ud_adr.date_to
1433 -- ,p_address_type => v_ud_adr.address_type
1434 ,p_address_line1 => v_ud_adr.address_line1
1435 ,p_address_line2 => v_ud_adr.address_line2
1436 ,p_address_line3 => v_ud_adr.address_line3
1437 ,p_town_or_city => v_ud_adr.town_or_city
1438 ,p_region_1 => v_ud_adr.region_1
1439 ,p_region_2 => v_ud_adr.region_2
1440 ,p_region_3 => v_ud_adr.region_3
1441 ,p_postal_code => v_ud_adr.postal_code
1442 ,p_country => v_ud_adr.country
1443 ,p_telephone_number_1 => v_ud_adr.telephone_number_1
1444 ,p_telephone_number_2 => v_ud_adr.telephone_number_2
1445 ,p_telephone_number_3 => v_ud_adr.telephone_number_3
1446 ,p_add_information17 => v_ud_adr.add_information17
1447 ,p_add_information18 => v_ud_adr.add_information18
1448 ,p_add_information19 => v_ud_adr.add_information19
1449 ,p_add_information20 => v_ud_adr.add_information20
1450 ,p_addr_attribute_category => v_ud_adr.addr_attribute_category
1451 ,p_addr_attribute1 => v_ud_adr.addr_attribute1
1452 ,p_addr_attribute2 => v_ud_adr.addr_attribute2
1453 ,p_addr_attribute3 => v_ud_adr.addr_attribute3
1454 ,p_addr_attribute4 => v_ud_adr.addr_attribute4
1455 ,p_addr_attribute5 => v_ud_adr.addr_attribute5
1456 ,p_addr_attribute6 => v_ud_adr.addr_attribute6
1457 ,p_addr_attribute7 => v_ud_adr.addr_attribute7
1458 ,p_addr_attribute8 => v_ud_adr.addr_attribute8
1459 ,p_addr_attribute9 => v_ud_adr.addr_attribute9
1460 ,p_addr_attribute10 => v_ud_adr.addr_attribute10
1461 ,p_addr_attribute11 => v_ud_adr.addr_attribute11
1462 ,p_addr_attribute12 => v_ud_adr.addr_attribute12
1463 ,p_addr_attribute13 => v_ud_adr.addr_attribute13
1464 ,p_addr_attribute14 => v_ud_adr.addr_attribute14
1465 ,p_addr_attribute15 => v_ud_adr.addr_attribute15
1466 ,p_addr_attribute16 => v_ud_adr.addr_attribute16
1467 ,p_addr_attribute17 => v_ud_adr.addr_attribute17
1468 ,p_addr_attribute18 => v_ud_adr.addr_attribute18
1469 ,p_addr_attribute19 => v_ud_adr.addr_attribute19
1470 ,p_addr_attribute20 => v_ud_adr.addr_attribute20
1471 ,p_address_id => l_address_id
1472 ,p_object_version_number => l_ovn
1473 );
1474
1475 hr_utility.set_location(l_proc, 40);
1476 hr_h2pi_map.create_id_mapping
1477 (p_table_name => 'PER_ADDRESSES',
1478 p_from_id => v_ud_adr.address_id,
1479 p_to_id => l_address_id);
1480 END IF;
1481 ELSE
1482 hr_utility.set_location(l_proc, 50);
1483 OPEN csr_ed_address(l_address_id);
1484 FETCH csr_ed_address INTO l_ovn;
1485 IF csr_ed_address%NOTFOUND THEN
1486 hr_utility.set_location(l_proc, 60);
1487 CLOSE csr_ed_address;
1488 ROLLBACK;
1489 hr_utility.set_location(l_proc, 70);
1490 hr_h2pi_error.data_error
1491 (p_from_id => l_address_id,
1492 p_table_name => 'HR_H2PI_ADDRESSES',
1493 p_message_level => 'FATAL',
1494 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
1495 COMMIT;
1496 RAISE MAPPING_ID_INVALID;
1497 ELSE
1498 CLOSE csr_ed_address;
1499 END IF;
1500
1501 hr_utility.set_location(l_proc, 80);
1502 hr_person_address_api.update_person_address(
1503 p_effective_date => v_ud_adr.date_from
1504 ,p_address_id => l_address_id
1505 ,p_object_version_number => l_ovn
1506 ,p_date_from => v_ud_adr.date_from
1507 ,p_date_to => v_ud_adr.date_to
1508 -- ,p_address_type => v_ud_adr.address_type
1509 ,p_address_line1 => v_ud_adr.address_line1
1510 ,p_address_line2 => v_ud_adr.address_line2
1511 ,p_address_line3 => v_ud_adr.address_line3
1512 ,p_town_or_city => v_ud_adr.town_or_city
1513 ,p_region_1 => v_ud_adr.region_1
1514 ,p_region_2 => v_ud_adr.region_2
1515 ,p_region_3 => v_ud_adr.region_3
1516 ,p_postal_code => v_ud_adr.postal_code
1517 ,p_country => v_ud_adr.country
1518 ,p_telephone_number_1 => v_ud_adr.telephone_number_1
1519 ,p_telephone_number_2 => v_ud_adr.telephone_number_2
1520 ,p_telephone_number_3 => v_ud_adr.telephone_number_3
1521 ,p_add_information17 => v_ud_adr.add_information17
1522 ,p_add_information18 => v_ud_adr.add_information18
1523 ,p_add_information19 => v_ud_adr.add_information19
1524 ,p_add_information20 => v_ud_adr.add_information20
1525 ,p_addr_attribute_category => v_ud_adr.addr_attribute_category
1526 ,p_addr_attribute1 => v_ud_adr.addr_attribute1
1527 ,p_addr_attribute2 => v_ud_adr.addr_attribute2
1528 ,p_addr_attribute3 => v_ud_adr.addr_attribute3
1529 ,p_addr_attribute4 => v_ud_adr.addr_attribute4
1530 ,p_addr_attribute5 => v_ud_adr.addr_attribute5
1531 ,p_addr_attribute6 => v_ud_adr.addr_attribute6
1532 ,p_addr_attribute7 => v_ud_adr.addr_attribute7
1533 ,p_addr_attribute8 => v_ud_adr.addr_attribute8
1534 ,p_addr_attribute9 => v_ud_adr.addr_attribute9
1535 ,p_addr_attribute10 => v_ud_adr.addr_attribute10
1536 ,p_addr_attribute11 => v_ud_adr.addr_attribute11
1537 ,p_addr_attribute12 => v_ud_adr.addr_attribute12
1538 ,p_addr_attribute13 => v_ud_adr.addr_attribute13
1539 ,p_addr_attribute14 => v_ud_adr.addr_attribute14
1540 ,p_addr_attribute15 => v_ud_adr.addr_attribute15
1541 ,p_addr_attribute16 => v_ud_adr.addr_attribute16
1542 ,p_addr_attribute17 => v_ud_adr.addr_attribute17
1543 ,p_addr_attribute18 => v_ud_adr.addr_attribute18
1544 ,p_addr_attribute19 => v_ud_adr.addr_attribute19
1545 ,p_addr_attribute20 => v_ud_adr.addr_attribute20
1546 );
1547 END IF;
1548
1549 hr_utility.set_location(l_proc, 90);
1550 UPDATE hr_h2pi_addresses adr
1551 SET status = 'C'
1552 WHERE adr.address_id = v_ud_adr.address_id
1553 AND adr.client_id = p_from_client_id;
1554 CLOSE csr_ud_address;
1555
1556 hr_utility.set_location('Leaving:'|| l_proc, 100);
1557 COMMIT;
1558
1559 EXCEPTION
1560 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
1561 ROLLBACK;
1562 hr_utility.set_location(l_proc, 110);
1563 l_encoded_message := fnd_message.get_encoded;
1564 hr_h2pi_error.data_error
1565 (p_from_id => v_ud_adr.address_id,
1566 p_table_name => 'HR_H2PI_ADDRESSES',
1567 p_message_level => 'FATAL',
1568 p_message_text => l_encoded_message);
1569 COMMIT;
1570 RAISE;
1571
1572 END;
1573
1574
1575 PROCEDURE upload_assignment (p_from_client_id NUMBER,
1576 p_assignment_id NUMBER,
1577 p_effective_start_date DATE) IS
1578
1579 CURSOR csr_ud_assignment (p_asg_id NUMBER,
1580 p_esd DATE) IS
1581 SELECT *
1582 FROM hr_h2pi_assignments asg
1583 WHERE asg.assignment_id = p_asg_id
1584 AND asg.client_id = p_from_client_id
1585 AND asg.effective_start_date = p_esd;
1586
1587 CURSOR csr_ed_assignment (p_asg_id NUMBER,
1588 p_esd DATE) IS
1589 SELECT *
1590 FROM hr_h2pi_assignments_v asg
1591 WHERE asg.assignment_id = p_asg_id
1592 AND p_esd BETWEEN asg.effective_start_date
1593 AND asg.effective_end_date;
1594
1595 CURSOR csr_ed_assignment_ovn (p_asg_id NUMBER,
1596 p_esd DATE) IS
1597 SELECT asg.object_version_number
1598 FROM per_all_assignments_f asg
1599 WHERE asg.assignment_id = p_asg_id
1600 AND p_esd BETWEEN asg.effective_start_date
1601 AND asg.effective_end_date;
1602
1603 CURSOR csr_ed_periods_of_service(p_asg_id NUMBER,
1604 p_esd DATE) IS
1605 SELECT pos.object_version_number,
1606 pos.period_of_service_id
1607 FROM per_all_assignments_f asg,
1608 per_all_people_f per,
1609 per_periods_of_service pos
1610 WHERE asg.assignment_id = p_asg_id
1611 AND p_esd BETWEEN asg.effective_start_date
1612 AND asg.effective_end_date
1613 AND asg.person_id = per.person_id
1614 AND p_esd BETWEEN per.effective_start_date
1615 AND per.effective_end_date
1616 AND per.person_id = pos.person_id
1617 AND pos.actual_termination_date = per.effective_start_date - 1;
1618
1619 CURSOR csr_state_tax_rule (p_asg_id NUMBER,
1620 p_date DATE) IS
1621 SELECT emp_state_tax_rule_id,
1622 jurisdiction_code
1623 FROM hr_h2pi_state_tax_rules_v
1624 WHERE assignment_id = p_asg_id
1625 AND p_date BETWEEN effective_start_date
1626 AND effective_end_date;
1627
1628 CURSOR csr_county_tax_rule (p_asg_id NUMBER,
1629 p_date DATE) IS
1630 SELECT emp_county_tax_rule_id,
1631 jurisdiction_code
1632 FROM hr_h2pi_county_tax_rules_v
1633 WHERE assignment_id = p_asg_id
1634 AND p_date BETWEEN effective_start_date
1635 AND effective_end_date;
1636
1637 CURSOR csr_city_tax_rule (p_asg_id NUMBER,
1638 p_date DATE) IS
1639 SELECT emp_city_tax_rule_id,
1640 jurisdiction_code
1641 FROM hr_h2pi_city_tax_rules_v
1642 WHERE assignment_id = p_asg_id
1643 AND p_date BETWEEN effective_start_date
1644 AND effective_end_date;
1645
1646 l_encoded_message VARCHAR2(200);
1647
1648 l_proc VARCHAR2(72) := g_package||'upload_assignment';
1649
1650 v_ud_asg hr_h2pi_assignments%ROWTYPE;
1651 v_ed_asg hr_h2pi_assignments_v%ROWTYPE;
1652 v_ud_pos hr_h2pi_periods_of_service%ROWTYPE;
1653
1654 l_person_id per_all_people_f.person_id%TYPE;
1655 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1656 l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
1657 l_ovn per_all_people_f.object_version_number%TYPE;
1658 l_pos_ovn per_all_people_f.object_version_number%TYPE;
1659 l_esd per_all_people_f.effective_start_date%TYPE;
1660 l_eed per_all_people_f.effective_end_date%TYPE;
1661 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
1662 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
1663
1664 l_max_eed per_all_assignments_f.effective_end_date%TYPE;
1665 l_del_ovn per_all_assignments_f.object_version_number%TYPE;
1666 l_del_esd per_all_assignments_f.effective_start_date%TYPE;
1667 l_del_eed per_all_assignments_f.effective_end_date%TYPE;
1668 l_val_esd per_all_assignments_f.effective_start_date%TYPE;
1669 l_val_eed per_all_assignments_f.effective_end_date%TYPE;
1670 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1671
1672 l_records_same BOOLEAN;
1673 l_future_records BOOLEAN;
1674 l_update_mode VARCHAR2(30);
1675 l_delete_mode VARCHAR2(30);
1676
1677 l_org_now_no_manager_warning BOOLEAN;
1678 l_asg_future_changes_warning BOOLEAN;
1679 l_entries_changed_warning VARCHAR2(1);
1680 l_pay_proposal_warning BOOLEAN;
1681 l_group_name VARCHAR2(100);
1682 l_concatenated_segments VARCHAR2(2000);
1683 l_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%TYPE;
1684 l_people_group_id per_all_assignments_f.people_group_id%TYPE;
1685 l_comment_id per_all_assignments_f.comment_id%TYPE;
1686 l_other_manager_warning BOOLEAN;
1687 l_no_manager_warning BOOLEAN;
1688 l_concat_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
1689 l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%TYPE;
1690 l_spp_delete_warning BOOLEAN;
1691 l_tax_district_changed_warning BOOLEAN;
1692
1693 l_organization_id per_all_assignments_f.organization_id%TYPE;
1694 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
1695 l_location_id per_all_assignments_f.location_id%TYPE;
1696 l_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
1697 l_gre_id NUMBER(15);
1698
1699 l_final_process_date per_periods_of_service.final_process_date%TYPE;
1700
1701 l_temp_id NUMBER(15);
1702 l_ud_emp_fed_tax_rule_id hr_h2pi_federal_tax_rules.emp_fed_tax_rule_id%TYPE;
1703 l_ud_emp_state_tax_rule_id hr_h2pi_state_tax_rules.emp_state_tax_rule_id%TYPE;
1704 l_ud_emp_county_tax_rule_id hr_h2pi_county_tax_rules.emp_county_tax_rule_id%TYPE;
1705 l_ud_emp_city_tax_rule_id hr_h2pi_city_tax_rules.emp_city_tax_rule_id%TYPE;
1706 l_emp_fed_tax_rule_id hr_h2pi_federal_tax_rules.emp_fed_tax_rule_id%TYPE;
1707 l_emp_state_tax_rule_id hr_h2pi_state_tax_rules.emp_state_tax_rule_id%TYPE;
1708 l_emp_county_tax_rule_id hr_h2pi_county_tax_rules.emp_county_tax_rule_id%TYPE;
1709 l_emp_city_tax_rule_id hr_h2pi_city_tax_rules.emp_city_tax_rule_id%TYPE;
1710
1711 l_dummy_person_type hr_h2pi_employees_v.person_type%TYPE;
1712 l_future_term_flag BOOLEAN := FALSE;
1713
1714 BEGIN
1715 hr_utility.set_location('Entering:'|| l_proc, 10);
1716
1717 OPEN csr_ud_assignment(p_assignment_id,
1718 p_effective_start_date);
1719 FETCH csr_ud_assignment INTO v_ud_asg;
1720
1721 IF v_ud_asg.last_upd_date = g_eot THEN
1722
1723 hr_utility.set_location(l_proc, 20);
1724 l_assignment_id := hr_h2pi_map.get_to_id
1725 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
1726 p_from_id => v_ud_asg.assignment_id,
1727 p_report_error => TRUE);
1728
1729 IF v_ud_asg.primary_flag = 'Y' THEN
1730
1731 hr_utility.set_location(l_proc, 30);
1732 OPEN csr_ed_periods_of_service(l_assignment_id,
1733 v_ud_asg.effective_start_date);
1734 FETCH csr_ed_periods_of_service
1735 INTO l_ovn,
1736 l_period_of_service_id;
1737 CLOSE csr_ed_periods_of_service;
1738
1739 hr_utility.set_location(l_proc, 40);
1740 l_final_process_date := v_ud_asg.effective_start_date - 1;
1741 hr_ex_employee_api.final_process_emp(
1742 p_period_of_service_id => l_period_of_service_id
1743 ,p_object_version_number => l_ovn
1744 ,p_final_process_date => l_final_process_date
1745 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1746 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1747 ,p_entries_changed_warning => l_entries_changed_warning
1748 );
1749 ELSE
1750 OPEN csr_ed_assignment_ovn(l_assignment_id,
1751 v_ud_asg.effective_start_date);
1752 FETCH csr_ed_assignment_ovn
1753 INTO l_ovn;
1754 CLOSE csr_ed_assignment_ovn;
1755
1756 hr_utility.set_location(l_proc, 50);
1757 hr_assignment_api.actual_termination_emp_asg(
1758 p_assignment_id => l_assignment_id
1759 ,p_object_version_number => l_ovn
1760 ,p_actual_termination_date =>(v_ud_asg.effective_start_date - 1)
1761 ,p_effective_start_date => l_esd
1762 ,p_effective_end_date => l_esd
1763 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1764 ,p_entries_changed_warning => l_entries_changed_warning
1765 ,p_pay_proposal_warning => l_pay_proposal_warning
1766 );
1767
1768 hr_utility.set_location(l_proc, 60);
1769 hr_assignment_api.final_process_emp_asg(
1770 p_assignment_id => l_assignment_id
1771 ,p_object_version_number => l_ovn
1772 ,p_final_process_date =>(v_ud_asg.effective_start_date - 1)
1773 ,p_effective_start_date => l_esd
1774 ,p_effective_end_date => l_eed
1775 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1776 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1777 ,p_entries_changed_warning => l_entries_changed_warning
1778 );
1779 END IF;
1780
1781 ELSE
1782
1783 hr_utility.set_location(l_proc, 70);
1784 l_person_id := hr_h2pi_map.get_to_id
1785 (p_table_name => 'PER_ALL_PEOPLE_F',
1786 p_from_id => v_ud_asg.person_id,
1787 p_report_error => TRUE);
1788
1789 l_organization_id := hr_h2pi_map.get_to_id
1790 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
1791 p_from_id => v_ud_asg.organization_id,
1792 p_report_error => TRUE);
1793
1794 l_payroll_id := hr_h2pi_map.get_to_id
1795 (p_table_name => 'PAY_ALL_PAYROLLS_F',
1796 p_from_id => v_ud_asg.payroll_id,
1797 p_report_error => TRUE);
1798
1799 l_location_id := hr_h2pi_map.get_to_id
1800 (p_table_name => 'HR_LOCATIONS_ALL',
1801 p_from_id => v_ud_asg.location_id,
1802 p_report_error => TRUE);
1803
1804 l_pay_basis_id := hr_h2pi_map.get_to_id
1805 (p_table_name => 'PER_PAY_BASES',
1806 p_from_id => v_ud_asg.pay_basis_id,
1807 p_report_error => TRUE);
1808
1809 /*****************************************************
1810 * US SPECIFIC - Ideally have generic flexfield mapper
1811 *****************************************************/
1812 l_gre_id := hr_h2pi_map.get_to_id
1813 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
1814 p_from_id => v_ud_asg.segment1,
1815 p_report_error => TRUE);
1816 v_ud_asg.segment1 := TO_CHAR(l_gre_id);
1817
1818 l_assignment_id := hr_h2pi_map.get_to_id
1819 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
1820 p_from_id => v_ud_asg.assignment_id);
1821
1822 IF l_assignment_id = -1 THEN
1823 hr_utility.set_location(l_proc, 80);
1824
1825 hr_assignment_api.create_secondary_emp_asg(
1826 p_effective_date => v_ud_asg.effective_start_date
1827 ,p_person_id => l_person_id
1828 ,p_organization_id => l_organization_id
1829 ,p_payroll_id => l_payroll_id
1830 ,p_location_id => l_location_id
1831 ,p_pay_basis_id => l_pay_basis_id
1832 ,p_assignment_number => v_ud_asg.assignment_number
1833 ,p_frequency => v_ud_asg.frequency
1834 ,p_normal_hours => v_ud_asg.normal_hours
1835 ,p_hourly_salaried_code => v_ud_asg.hourly_salaried_code
1836 ,p_time_normal_finish => v_ud_asg.time_normal_finish
1837 ,p_time_normal_start => v_ud_asg.time_normal_start
1838 ,p_employment_category => v_ud_asg.employment_category
1839 ,p_title => v_ud_asg.title
1840 ,p_ass_attribute_category => v_ud_asg.ass_attribute_category
1841 ,p_ass_attribute1 => v_ud_asg.ass_attribute1
1842 ,p_ass_attribute2 => v_ud_asg.ass_attribute2
1843 ,p_ass_attribute3 => v_ud_asg.ass_attribute3
1844 ,p_ass_attribute4 => v_ud_asg.ass_attribute4
1845 ,p_ass_attribute5 => v_ud_asg.ass_attribute5
1846 ,p_ass_attribute6 => v_ud_asg.ass_attribute6
1847 ,p_ass_attribute7 => v_ud_asg.ass_attribute7
1848 ,p_ass_attribute8 => v_ud_asg.ass_attribute8
1849 ,p_ass_attribute9 => v_ud_asg.ass_attribute9
1850 ,p_ass_attribute10 => v_ud_asg.ass_attribute10
1851 ,p_ass_attribute11 => v_ud_asg.ass_attribute11
1852 ,p_ass_attribute12 => v_ud_asg.ass_attribute12
1853 ,p_ass_attribute13 => v_ud_asg.ass_attribute13
1854 ,p_ass_attribute14 => v_ud_asg.ass_attribute14
1855 ,p_ass_attribute15 => v_ud_asg.ass_attribute15
1856 ,p_ass_attribute16 => v_ud_asg.ass_attribute16
1857 ,p_ass_attribute17 => v_ud_asg.ass_attribute17
1858 ,p_ass_attribute18 => v_ud_asg.ass_attribute18
1859 ,p_ass_attribute19 => v_ud_asg.ass_attribute19
1860 ,p_ass_attribute20 => v_ud_asg.ass_attribute20
1861 ,p_ass_attribute21 => v_ud_asg.ass_attribute21
1862 ,p_ass_attribute22 => v_ud_asg.ass_attribute22
1863 ,p_ass_attribute23 => v_ud_asg.ass_attribute23
1864 ,p_ass_attribute24 => v_ud_asg.ass_attribute24
1865 ,p_ass_attribute25 => v_ud_asg.ass_attribute25
1866 ,p_ass_attribute26 => v_ud_asg.ass_attribute26
1867 ,p_ass_attribute27 => v_ud_asg.ass_attribute27
1868 ,p_ass_attribute28 => v_ud_asg.ass_attribute28
1869 ,p_ass_attribute29 => v_ud_asg.ass_attribute29
1870 ,p_ass_attribute30 => v_ud_asg.ass_attribute30
1871 ,p_scl_segment1 => v_ud_asg.segment1
1872 -- ,p_scl_segment2 => v_ud_asg.segment2
1873 ,p_scl_segment3 => v_ud_asg.segment3
1874 -- ,p_scl_segment4 => v_ud_asg.segment4
1875 ,p_scl_segment5 => v_ud_asg.segment5
1876 ,p_scl_segment6 => v_ud_asg.segment6
1877 ,p_scl_segment7 => v_ud_asg.segment7
1878 ,p_scl_segment8 => v_ud_asg.segment8
1879 ,p_scl_segment9 => v_ud_asg.segment9
1880 ,p_scl_segment10 => v_ud_asg.segment10
1881 ,p_scl_segment11 => v_ud_asg.segment11
1882 ,p_scl_segment12 => v_ud_asg.segment12
1883 ,p_scl_segment13 => v_ud_asg.segment13
1884 ,p_scl_segment14 => v_ud_asg.segment14
1885 ,p_scl_segment15 => v_ud_asg.segment15
1886 ,p_scl_segment16 => v_ud_asg.segment16
1887 ,p_scl_segment17 => v_ud_asg.segment17
1888 ,p_scl_segment18 => v_ud_asg.segment18
1889 ,p_scl_segment19 => v_ud_asg.segment19
1890 ,p_scl_segment20 => v_ud_asg.segment20
1891 ,p_scl_segment21 => v_ud_asg.segment21
1892 ,p_scl_segment22 => v_ud_asg.segment22
1893 ,p_scl_segment23 => v_ud_asg.segment23
1894 ,p_scl_segment24 => v_ud_asg.segment24
1895 ,p_scl_segment25 => v_ud_asg.segment25
1896 ,p_scl_segment26 => v_ud_asg.segment26
1897 ,p_scl_segment27 => v_ud_asg.segment27
1898 ,p_scl_segment28 => v_ud_asg.segment28
1899 ,p_scl_segment29 => v_ud_asg.segment29
1900 ,p_scl_segment30 => v_ud_asg.segment30
1901 -- added for the enhancement
1902 ,p_pgp_segment1 => v_ud_asg.ppg_segment1
1903 ,p_pgp_segment2 => v_ud_asg.ppg_segment2
1904 ,p_pgp_segment3 => v_ud_asg.ppg_segment3
1905 ,p_pgp_segment4 => v_ud_asg.ppg_segment4
1906 ,p_pgp_segment5 => v_ud_asg.ppg_segment5
1907 ,p_pgp_segment6 => v_ud_asg.ppg_segment6
1908 ,p_pgp_segment7 => v_ud_asg.ppg_segment7
1909 ,p_pgp_segment8 => v_ud_asg.ppg_segment8
1910 ,p_pgp_segment9 => v_ud_asg.ppg_segment9
1911 ,p_pgp_segment10 => v_ud_asg.ppg_segment10
1912 ,p_pgp_segment11 => v_ud_asg.ppg_segment11
1913 ,p_pgp_segment12 => v_ud_asg.ppg_segment12
1914 ,p_pgp_segment13 => v_ud_asg.ppg_segment13
1915 ,p_pgp_segment14 => v_ud_asg.ppg_segment14
1916 ,p_pgp_segment15 => v_ud_asg.ppg_segment15
1917 ,p_pgp_segment16 => v_ud_asg.ppg_segment16
1918 ,p_pgp_segment17 => v_ud_asg.ppg_segment17
1919 ,p_pgp_segment18 => v_ud_asg.ppg_segment18
1920 ,p_pgp_segment19 => v_ud_asg.ppg_segment19
1921 ,p_pgp_segment20 => v_ud_asg.ppg_segment20
1922 ,p_pgp_segment21 => v_ud_asg.ppg_segment21
1923 ,p_pgp_segment22 => v_ud_asg.ppg_segment22
1924 ,p_pgp_segment23 => v_ud_asg.ppg_segment23
1925 ,p_pgp_segment24 => v_ud_asg.ppg_segment24
1926 ,p_pgp_segment25 => v_ud_asg.ppg_segment25
1927 ,p_pgp_segment26 => v_ud_asg.ppg_segment26
1928 ,p_pgp_segment27 => v_ud_asg.ppg_segment27
1929 ,p_pgp_segment28 => v_ud_asg.ppg_segment28
1930 ,p_pgp_segment29 => v_ud_asg.ppg_segment29
1931 ,p_pgp_segment30 => v_ud_asg.ppg_segment30
1932 --
1933 ,p_group_name => l_group_name
1934 ,p_concatenated_segments => l_concatenated_segments
1935 ,p_assignment_id => l_assignment_id
1936 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1937 ,p_people_group_id => l_people_group_id
1938 ,p_object_version_number => l_ovn
1939 ,p_effective_start_date => l_esd
1940 ,p_effective_end_date => l_eed
1941 ,p_assignment_sequence => l_assignment_sequence
1942 ,p_comment_id => l_comment_id
1943 ,p_other_manager_warning => l_other_manager_warning
1944 );
1945
1946 hr_utility.set_location(l_proc, 90);
1947 hr_h2pi_map.create_id_mapping
1948 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
1949 p_from_id => v_ud_asg.assignment_id,
1950 p_to_id => l_assignment_id);
1951 ELSE
1952
1953 BEGIN
1954 hr_utility.set_location(l_proc, 100);
1955 OPEN csr_ed_assignment(l_assignment_id,
1956 v_ud_asg.effective_start_date);
1957 FETCH csr_ed_assignment
1958 INTO v_ed_asg;
1959 IF csr_ed_assignment%NOTFOUND THEN
1960 hr_utility.set_location(l_proc, 110);
1961 CLOSE csr_ed_assignment;
1962 ROLLBACK;
1963 hr_utility.set_location(l_proc, 220);
1964 hr_h2pi_error.data_error
1965 (p_from_id => l_assignment_id,
1966 p_table_name => 'HR_H2PI_ASSIGNMENTS',
1967 p_message_level => 'FATAL',
1968 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
1969 COMMIT;
1970 RAISE MAPPING_ID_INVALID;
1971 ELSE
1972 CLOSE csr_ed_assignment;
1973 END IF;
1974
1975 OPEN csr_ed_assignment_ovn(l_assignment_id,
1976 v_ud_asg.effective_start_date);
1977 FETCH csr_ed_assignment_ovn
1978 INTO l_ovn;
1979 CLOSE csr_ed_assignment_ovn;
1980 END;
1981
1982 l_delete_mode := 'DELETE_NEXT_CHANGE';
1983 LOOP
1984 hr_utility.set_location(l_proc, 120);
1985 l_records_same := FALSE;
1986
1987 SELECT MAX(asg.effective_end_date)
1988 INTO l_max_eed
1989 FROM per_all_assignments_f asg
1990 WHERE asg.assignment_id = l_assignment_id;
1991
1992 IF l_max_eed > v_ed_asg.effective_end_date THEN
1993 hr_utility.set_location(l_proc, 130);
1994 l_future_records := TRUE;
1995 END IF;
1996
1997 calculate_datetrack_mode
1998 (p_ud_start_date => v_ud_asg.effective_start_date
1999 ,p_ud_end_date => v_ud_asg.effective_end_date
2000 ,p_ed_start_date => v_ed_asg.effective_start_date
2001 ,p_ed_end_date => v_ed_asg.effective_end_date
2002 ,p_records_same => l_records_same
2003 ,p_future_records => l_future_records
2004 ,p_update_mode => l_update_mode
2005 ,p_delete_mode => l_delete_mode);
2006
2007 EXIT WHEN l_delete_mode = 'X';
2008
2009 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
2010 hr_utility.set_location(l_proc, 134);
2011
2012 BEGIN
2013 SELECT DISTINCT person_type
2014 INTO l_dummy_person_type
2015 FROM hr_h2pi_employees_v
2016 WHERE person_id = l_person_id
2017 AND effective_start_date < v_ud_asg.effective_end_date
2018 AND effective_end_date > v_ud_asg.effective_start_date;
2019 EXCEPTION
2020 WHEN TOO_MANY_ROWS THEN
2021 hr_utility.set_location(l_proc, 136);
2022 l_future_term_flag := TRUE;
2023 calculate_datetrack_mode
2024 (p_ud_start_date => v_ud_asg.effective_start_date
2025 ,p_ud_end_date => v_ud_asg.effective_end_date
2026 ,p_ed_start_date => v_ed_asg.effective_start_date
2027 ,p_ed_end_date => v_ud_asg.effective_end_date
2028 ,p_records_same => l_records_same
2029 ,p_future_records => l_future_records
2030 ,p_update_mode => l_update_mode
2031 ,p_delete_mode => l_delete_mode);
2032 EXIT;
2033 END;
2034
2035
2036 hr_utility.set_location(l_proc, 140);
2037 per_asg_del.del(p_assignment_id => l_assignment_id
2038 ,p_effective_start_date => l_del_esd
2039 ,p_effective_end_date => l_del_eed
2040 ,p_validation_start_date => l_val_esd
2041 ,p_validation_end_date => l_val_eed
2042 ,p_business_group_id => l_business_group_id
2043 ,p_org_now_no_manager_warning
2044 => l_org_now_no_manager_warning
2045 ,p_object_version_number => l_ovn
2046 ,p_effective_date => v_ed_asg.effective_end_date
2047 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE');
2048
2049 hr_utility.set_location(l_proc, 150);
2050 OPEN csr_ed_assignment(l_assignment_id,
2051 v_ud_asg.effective_start_date);
2052 FETCH csr_ed_assignment
2053 INTO v_ed_asg;
2054 CLOSE csr_ed_assignment;
2055
2056 END IF;
2057
2058 END LOOP;
2059
2060 IF v_ud_asg.primary_flag = 'Y' AND
2061 v_ed_asg.primary_flag = 'N' THEN
2062 hr_utility.set_location(l_proc, 160);
2063 l_person_id := hr_h2pi_map.get_to_id
2064 (p_table_name => 'PER_ALL_PEOPLE_F',
2065 p_from_id => v_ud_asg.person_id,
2066 p_report_error => TRUE);
2067
2068 hr_utility.set_location(l_proc, 170);
2069 hr_assignment_api.set_new_primary_asg(
2070 p_effective_date => v_ud_asg.effective_start_date
2071 ,p_person_id => l_person_id
2072 ,p_assignment_id => l_assignment_id
2073 ,p_object_version_number => l_ovn
2074 ,p_effective_start_date => l_esd
2075 ,p_effective_end_date => l_eed
2076 );
2077
2078 IF l_future_term_flag THEN
2079 calculate_datetrack_mode
2080 (p_ud_start_date => v_ud_asg.effective_start_date
2081 ,p_ud_end_date => v_ud_asg.effective_end_date
2082 ,p_ed_start_date => l_esd
2083 ,p_ed_end_date => v_ud_asg.effective_end_date
2084 ,p_records_same => l_records_same
2085 ,p_future_records => l_future_records
2086 ,p_update_mode => l_update_mode
2087 ,p_delete_mode => l_delete_mode);
2088 ELSE
2089 calculate_datetrack_mode
2090 (p_ud_start_date => v_ud_asg.effective_start_date
2091 ,p_ud_end_date => v_ud_asg.effective_end_date
2092 ,p_ed_start_date => l_esd
2093 ,p_ed_end_date => l_eed
2094 ,p_records_same => l_records_same
2095 ,p_future_records => l_future_records
2096 ,p_update_mode => l_update_mode
2097 ,p_delete_mode => l_delete_mode);
2098 END IF;
2099 END IF;
2100
2101 hr_utility.set_location(l_proc, 180);
2102 hr_assignment_api.update_emp_asg(
2103 p_effective_date => v_ud_asg.effective_start_date
2104 ,p_datetrack_update_mode => l_update_mode
2105 ,p_assignment_id => l_assignment_id
2106 ,p_object_version_number => l_ovn
2107 ,p_assignment_number => v_ud_asg.assignment_number
2108 ,p_frequency => v_ud_asg.frequency
2109 ,p_normal_hours => v_ud_asg.normal_hours
2110 ,p_hourly_salaried_code => v_ud_asg.hourly_salaried_code
2111 -- ,p_source_type => v_ud_asg.source_type
2112 ,p_time_normal_finish => v_ud_asg.time_normal_finish
2113 ,p_time_normal_start => v_ud_asg.time_normal_start
2114 ,p_ass_attribute_category => v_ud_asg.ass_attribute_category
2115 ,p_ass_attribute1 => v_ud_asg.ass_attribute1
2116 ,p_ass_attribute2 => v_ud_asg.ass_attribute2
2117 ,p_ass_attribute3 => v_ud_asg.ass_attribute3
2118 ,p_ass_attribute4 => v_ud_asg.ass_attribute4
2119 ,p_ass_attribute5 => v_ud_asg.ass_attribute5
2120 ,p_ass_attribute6 => v_ud_asg.ass_attribute6
2121 ,p_ass_attribute7 => v_ud_asg.ass_attribute7
2122 ,p_ass_attribute8 => v_ud_asg.ass_attribute8
2123 ,p_ass_attribute9 => v_ud_asg.ass_attribute9
2124 ,p_ass_attribute10 => v_ud_asg.ass_attribute10
2125 ,p_ass_attribute11 => v_ud_asg.ass_attribute11
2126 ,p_ass_attribute12 => v_ud_asg.ass_attribute12
2127 ,p_ass_attribute13 => v_ud_asg.ass_attribute13
2128 ,p_ass_attribute14 => v_ud_asg.ass_attribute14
2129 ,p_ass_attribute15 => v_ud_asg.ass_attribute15
2130 ,p_ass_attribute16 => v_ud_asg.ass_attribute16
2131 ,p_ass_attribute17 => v_ud_asg.ass_attribute17
2132 ,p_ass_attribute18 => v_ud_asg.ass_attribute18
2133 ,p_ass_attribute19 => v_ud_asg.ass_attribute19
2134 ,p_ass_attribute20 => v_ud_asg.ass_attribute20
2135 ,p_ass_attribute21 => v_ud_asg.ass_attribute21
2136 ,p_ass_attribute22 => v_ud_asg.ass_attribute22
2137 ,p_ass_attribute23 => v_ud_asg.ass_attribute23
2138 ,p_ass_attribute24 => v_ud_asg.ass_attribute24
2139 ,p_ass_attribute25 => v_ud_asg.ass_attribute25
2140 ,p_ass_attribute26 => v_ud_asg.ass_attribute26
2141 ,p_ass_attribute27 => v_ud_asg.ass_attribute27
2142 ,p_ass_attribute28 => v_ud_asg.ass_attribute28
2143 ,p_ass_attribute29 => v_ud_asg.ass_attribute29
2144 ,p_ass_attribute30 => v_ud_asg.ass_attribute30
2145 ,p_title => v_ud_asg.title
2146 ,p_segment1 => v_ud_asg.segment1
2147 -- ,p_segment2 => v_ud_asg.segment2
2148 ,p_segment3 => v_ud_asg.segment3
2149 -- ,p_segment4 => v_ud_asg.segment4
2150 ,p_segment5 => v_ud_asg.segment5
2151 ,p_segment6 => v_ud_asg.segment6
2152 ,p_segment7 => v_ud_asg.segment7
2153 ,p_segment8 => v_ud_asg.segment8
2154 ,p_segment9 => v_ud_asg.segment9
2155 ,p_segment10 => v_ud_asg.segment10
2156 ,p_segment11 => v_ud_asg.segment11
2157 ,p_segment12 => v_ud_asg.segment12
2158 ,p_segment13 => v_ud_asg.segment13
2159 ,p_segment14 => v_ud_asg.segment14
2160 ,p_segment15 => v_ud_asg.segment15
2161 ,p_segment16 => v_ud_asg.segment16
2162 ,p_segment17 => v_ud_asg.segment17
2163 ,p_segment18 => v_ud_asg.segment18
2164 ,p_segment19 => v_ud_asg.segment19
2165 ,p_segment20 => v_ud_asg.segment20
2166 ,p_segment21 => v_ud_asg.segment21
2167 ,p_segment22 => v_ud_asg.segment22
2168 ,p_segment23 => v_ud_asg.segment23
2169 ,p_segment24 => v_ud_asg.segment24
2170 ,p_segment25 => v_ud_asg.segment25
2171 ,p_segment26 => v_ud_asg.segment26
2172 ,p_segment27 => v_ud_asg.segment27
2173 ,p_segment28 => v_ud_asg.segment28
2174 ,p_segment29 => v_ud_asg.segment29
2175 ,p_segment30 => v_ud_asg.segment30
2176 ,p_concatenated_segments => l_concat_segments
2177 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
2178 ,p_comment_id => l_comment_id
2179 ,p_effective_start_date => l_esd
2180 ,p_effective_end_date => l_eed
2181 ,p_no_managers_warning => l_no_manager_warning
2182 ,p_other_manager_warning => l_other_manager_warning
2183 );
2184
2185 hr_utility.set_location(l_proc, 190);
2186 IF l_future_term_flag THEN
2187 calculate_datetrack_mode
2188 (p_ud_start_date => v_ud_asg.effective_start_date
2189 ,p_ud_end_date => v_ud_asg.effective_end_date
2190 ,p_ed_start_date => l_esd
2191 ,p_ed_end_date => v_ud_asg.effective_end_date
2192 ,p_records_same => l_records_same
2193 ,p_future_records => l_future_records
2194 ,p_update_mode => l_update_mode
2195 ,p_delete_mode => l_delete_mode);
2196 ELSE
2197 calculate_datetrack_mode
2198 (p_ud_start_date => v_ud_asg.effective_start_date
2199 ,p_ud_end_date => v_ud_asg.effective_end_date
2200 ,p_ed_start_date => l_esd
2201 ,p_ed_end_date => l_eed
2202 ,p_records_same => l_records_same
2203 ,p_future_records => l_future_records
2204 ,p_update_mode => l_update_mode
2205 ,p_delete_mode => l_delete_mode);
2206 END IF;
2207
2208 hr_assignment_api.update_emp_asg_criteria(
2209 p_effective_date => v_ud_asg.effective_start_date
2210 ,p_datetrack_update_mode => l_update_mode
2211 ,p_assignment_id => l_assignment_id
2212 ,p_object_version_number => l_ovn
2213 ,p_payroll_id => l_payroll_id
2214 ,p_location_id => l_location_id
2215 ,p_organization_id => l_organization_id
2216 ,p_pay_basis_id => l_pay_basis_id
2217 ,p_employment_category => v_ud_asg.employment_category
2218 -- added for the enhancement
2219 ,p_segment1 => v_ud_asg.ppg_segment1
2220 ,p_segment2 => v_ud_asg.ppg_segment2
2221 ,p_segment3 => v_ud_asg.ppg_segment3
2222 ,p_segment4 => v_ud_asg.ppg_segment4
2223 ,p_segment5 => v_ud_asg.ppg_segment5
2224 ,p_segment6 => v_ud_asg.ppg_segment6
2225 ,p_segment7 => v_ud_asg.ppg_segment7
2226 ,p_segment8 => v_ud_asg.ppg_segment8
2227 ,p_segment9 => v_ud_asg.ppg_segment9
2228 ,p_segment10 => v_ud_asg.ppg_segment10
2229 ,p_segment11 => v_ud_asg.ppg_segment11
2230 ,p_segment12 => v_ud_asg.ppg_segment12
2231 ,p_segment13 => v_ud_asg.ppg_segment13
2232 ,p_segment14 => v_ud_asg.ppg_segment14
2233 ,p_segment15 => v_ud_asg.ppg_segment15
2234 ,p_segment16 => v_ud_asg.ppg_segment16
2235 ,p_segment17 => v_ud_asg.ppg_segment17
2236 ,p_segment18 => v_ud_asg.ppg_segment18
2237 ,p_segment19 => v_ud_asg.ppg_segment19
2238 ,p_segment20 => v_ud_asg.ppg_segment20
2239 ,p_segment21 => v_ud_asg.ppg_segment21
2240 ,p_segment22 => v_ud_asg.ppg_segment22
2241 ,p_segment23 => v_ud_asg.ppg_segment23
2242 ,p_segment24 => v_ud_asg.ppg_segment24
2243 ,p_segment25 => v_ud_asg.ppg_segment25
2244 ,p_segment26 => v_ud_asg.ppg_segment26
2245 ,p_segment27 => v_ud_asg.ppg_segment27
2246 ,p_segment28 => v_ud_asg.ppg_segment28
2247 ,p_segment29 => v_ud_asg.ppg_segment29
2248 ,p_segment30 => v_ud_asg.ppg_segment30
2249 --
2250 ,p_effective_start_date => l_esd
2251 ,p_effective_end_date => l_eed
2252 ,p_group_name => l_group_name
2253 ,p_people_group_id => l_people_group_id
2254 ,p_special_ceiling_step_id => l_special_ceiling_step_id
2255 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2256 ,p_other_manager_warning => l_other_manager_warning
2257 ,p_spp_delete_warning => l_spp_delete_warning
2258 ,p_entries_changed_warning => l_entries_changed_warning
2259 ,p_tax_district_changed_warning => l_tax_district_changed_warning
2260 );
2261
2262 END IF;
2263
2264 hr_utility.set_location(l_proc, 200);
2265 BEGIN
2266 SELECT emp_fed_tax_rule_id
2267 INTO l_emp_fed_tax_rule_id
2268 FROM hr_h2pi_federal_tax_rules_v
2269 WHERE assignment_id = l_assignment_id
2270 AND v_ud_asg.effective_start_date BETWEEN effective_start_date
2271 AND effective_end_date;
2272
2273 l_temp_id := hr_h2pi_map.get_from_id(
2274 p_table_name => 'PAY_US_EMP_FED_TAX_RULES_F',
2275 p_to_id => l_emp_fed_tax_rule_id);
2276 IF l_temp_id = -1 THEN
2277 SELECT emp_fed_tax_rule_id
2278 INTO l_ud_emp_fed_tax_rule_id
2279 FROM hr_h2pi_federal_tax_rules
2280 WHERE assignment_id = v_ud_asg.assignment_id
2281 AND client_id = p_from_client_id
2282 AND v_ud_asg.effective_start_date BETWEEN effective_start_date
2283 AND effective_end_date;
2284
2285 hr_h2pi_map.create_id_mapping
2286 (p_table_name => 'PAY_US_EMP_FED_TAX_RULES_F',
2287 p_from_id => l_ud_emp_fed_tax_rule_id,
2288 p_to_id => l_emp_fed_tax_rule_id);
2289 END IF;
2290 EXCEPTION
2291 WHEN NO_DATA_FOUND THEN
2292 hr_utility.set_location(l_proc, 205);
2293 END;
2294
2295 hr_utility.set_location(l_proc, 210);
2296 FOR v_sta IN csr_state_tax_rule(l_assignment_id,
2297 v_ud_asg.effective_start_date) LOOP
2298 l_temp_id := hr_h2pi_map.get_from_id(
2299 p_table_name => 'PAY_US_EMP_STATE_TAX_RULES_F',
2300 p_to_id => v_sta.emp_state_tax_rule_id);
2301 IF l_temp_id = -1 THEN
2302
2303 SELECT emp_state_tax_rule_id
2304 INTO l_ud_emp_state_tax_rule_id
2305 FROM hr_h2pi_state_tax_rules
2306 WHERE assignment_id = v_ud_asg.assignment_id
2307 AND client_id = p_from_client_id
2308 AND jurisdiction_code = v_sta.jurisdiction_code
2309 AND v_ud_asg.effective_start_date BETWEEN effective_start_date
2310 AND effective_end_date;
2311
2312 hr_h2pi_map.create_id_mapping
2313 (p_table_name => 'PAY_US_EMP_STATE_TAX_RULES_F',
2314 p_from_id => l_ud_emp_state_tax_rule_id,
2315 p_to_id => v_sta.emp_state_tax_rule_id);
2316
2317 END IF;
2318 END LOOP;
2319
2320 hr_utility.set_location(l_proc, 220);
2321 FOR v_cnt IN csr_county_tax_rule(l_assignment_id,
2322 v_ud_asg.effective_start_date) LOOP
2323 l_temp_id := hr_h2pi_map.get_from_id(
2324 p_table_name=> 'PAY_US_EMP_COUNTY_TAX_RULES_F',
2325 p_to_id => v_cnt.emp_county_tax_rule_id);
2326 IF l_temp_id = -1 THEN
2327
2328 SELECT emp_county_tax_rule_id
2329 INTO l_ud_emp_county_tax_rule_id
2330 FROM hr_h2pi_county_tax_rules
2331 WHERE assignment_id = v_ud_asg.assignment_id
2332 AND client_id = p_from_client_id
2333 AND jurisdiction_code = v_cnt.jurisdiction_code
2334 AND v_ud_asg.effective_start_date BETWEEN effective_start_date
2335 AND effective_end_date;
2336
2337 hr_h2pi_map.create_id_mapping
2338 (p_table_name => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
2339 p_from_id => l_ud_emp_county_tax_rule_id,
2340 p_to_id => v_cnt.emp_county_tax_rule_id);
2341
2342 END IF;
2343 END LOOP;
2344
2345 hr_utility.set_location(l_proc, 230);
2346 FOR v_cty IN csr_city_tax_rule(l_assignment_id,
2347 v_ud_asg.effective_start_date) LOOP
2348 l_temp_id := hr_h2pi_map.get_from_id(
2349 p_table_name => 'PAY_US_EMP_CITY_TAX_RULES_F',
2350 p_to_id => v_cty.emp_city_tax_rule_id);
2351 IF l_temp_id = -1 THEN
2352
2353 SELECT emp_city_tax_rule_id
2354 INTO l_ud_emp_city_tax_rule_id
2355 FROM hr_h2pi_city_tax_rules
2356 WHERE assignment_id = v_ud_asg.assignment_id
2357 AND client_id = p_from_client_id
2358 AND jurisdiction_code = v_cty.jurisdiction_code
2359 AND v_ud_asg.effective_start_date BETWEEN effective_start_date
2360 AND effective_end_date;
2361
2362 hr_h2pi_map.create_id_mapping
2363 (p_table_name => 'PAY_US_EMP_CITY_TAX_RULES_F',
2364 p_from_id => l_ud_emp_city_tax_rule_id,
2365 p_to_id => v_cty.emp_city_tax_rule_id);
2366
2367 END IF;
2368 END LOOP;
2369 END IF;
2370
2371 hr_utility.set_location(l_proc, 240);
2372 UPDATE hr_h2pi_assignments asg
2373 SET status = 'C'
2374 WHERE asg.assignment_id = v_ud_asg.assignment_id
2375 AND asg.client_id = p_from_client_id
2376 AND asg.effective_start_date = v_ud_asg.effective_start_date
2377 AND asg.effective_end_date = v_ud_asg.effective_end_date;
2378
2379 CLOSE csr_ud_assignment;
2380 hr_utility.set_location('Leaving:'|| l_proc, 250);
2381 COMMIT;
2382
2383 EXCEPTION
2384 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
2385 ROLLBACK;
2386 hr_utility.set_location(l_proc, 260);
2387 l_encoded_message := fnd_message.get_encoded;
2388 hr_h2pi_error.data_error
2389 (p_from_id => v_ud_asg.assignment_id,
2390 p_table_name => 'HR_H2PI_ASSIGNMENTS',
2391 p_message_level => 'FATAL',
2392 p_message_text => l_encoded_message);
2393 COMMIT;
2394 RAISE;
2395 END;
2396
2397
2398
2399 PROCEDURE upload_period_of_service (p_from_client_id NUMBER,
2400 p_period_of_service_id NUMBER,
2401 p_effective_start_date DATE) IS
2402
2403 CURSOR csr_ud_periods_of_service(p_pos_id NUMBER) IS
2404 SELECT *
2405 FROM hr_h2pi_periods_of_service pos
2406 WHERE pos.period_of_service_id = p_pos_id
2407 AND pos.client_id = p_from_client_id;
2408
2409 CURSOR csr_ed_periods_of_service(p_pos_id NUMBER) IS
2410 SELECT pos.object_version_number
2411 FROM per_periods_of_service pos
2412 WHERE pos.period_of_service_id = p_pos_id;
2413
2414 l_proc VARCHAR2(72) := g_package||'upload_period_of_service';
2415
2416 l_encoded_message VARCHAR2(200);
2417
2418 v_ud_pos hr_h2pi_periods_of_service%ROWTYPE;
2419 l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
2420 l_ovn per_periods_of_service.object_version_number%TYPE;
2421 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
2422 l_final_process_date per_periods_of_service.final_process_date%TYPE;
2423 BEGIN
2424 hr_utility.set_location('Entering:'|| l_proc, 10);
2425
2426 OPEN csr_ud_periods_of_service(p_period_of_service_id);
2427 FETCH csr_ud_periods_of_service INTO v_ud_pos;
2428
2429 l_period_of_service_id := hr_h2pi_map.get_to_id
2430 (p_table_name => 'PER_PERIODS_OF_SERVICE',
2431 p_from_id => v_ud_pos.period_of_service_id,
2432 p_report_error => TRUE);
2433
2434 OPEN csr_ed_periods_of_service(l_period_of_service_id);
2435 FETCH csr_ed_periods_of_service INTO l_ovn;
2436 CLOSE csr_ed_periods_of_service;
2437
2438 hr_utility.set_location(l_proc, 30);
2439 hr_ex_employee_api.update_term_details_emp(
2440 p_effective_date => v_ud_pos.date_start
2441 ,p_period_of_service_id => l_period_of_service_id
2442 ,p_object_version_number => l_ovn
2443 -- ,p_accepted_termination_date => v_ud_pos.accepted_termination_date
2444 ,p_leaving_reason => v_ud_pos.leaving_reason
2445 -- ,p_notified_termination_date => v_ud_pos.notified_termination_date
2446 -- ,p_projected_termination_date => v_ud_pos.projected_termination_date
2447 );
2448
2449 hr_utility.set_location(l_proc, 40);
2450 UPDATE hr_h2pi_periods_of_service pos
2451 SET status = 'C'
2452 WHERE pos.period_of_service_id = v_ud_pos.period_of_service_id
2453 AND pos.client_id = p_from_client_id;
2454
2455 CLOSE csr_ud_periods_of_service;
2456 hr_utility.set_location('Leaving:'|| l_proc, 50);
2457 COMMIT;
2458
2459 EXCEPTION
2460 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
2461 ROLLBACK;
2462 hr_utility.set_location(l_proc, 70);
2463 l_encoded_message := fnd_message.get_encoded;
2464 hr_h2pi_error.data_error
2465 (p_from_id => v_ud_pos.period_of_service_id,
2466 p_table_name => 'HR_H2PI_PERIODS_OF_SERVICE',
2467 p_message_level => 'FATAL',
2468 p_message_text => l_encoded_message);
2469 COMMIT;
2470 RAISE;
2471
2472 END;
2473
2474
2475
2476 PROCEDURE upload_salary (p_from_client_id NUMBER,
2477 p_pay_proposal_id NUMBER,
2478 p_effective_start_date DATE) IS
2479
2480 CURSOR csr_ud_salary (p_ppp_id NUMBER) IS
2481 SELECT *
2482 FROM hr_h2pi_salaries ppp
2483 WHERE ppp.pay_proposal_id = p_ppp_id
2484 AND ppp.client_id = p_from_client_id;
2485
2486 CURSOR csr_ed_salary (p_ppp_id NUMBER) IS
2487 SELECT object_version_number
2488 FROM per_pay_proposals ppp
2489 WHERE ppp.pay_proposal_id = p_ppp_id;
2490
2491 CURSOR csr_sal_ee (p_asg_id NUMBER,
2492 p_date DATE) IS
2493 SELECT element_entry_id
2494 FROM pay_element_entries_f
2495 WHERE creator_type = 'SP'
2496 AND p_date BETWEEN effective_start_date and effective_end_date
2497 AND assignment_id = p_asg_id;
2498
2499 l_proc VARCHAR2(72) := g_package||'upload_salary';
2500
2501 l_encoded_message VARCHAR2(200);
2502
2503 l_assignment_id per_pay_proposals.assignment_id%TYPE;
2504 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
2505 l_ovn per_pay_proposals.object_version_number%TYPE;
2506 v_ud_ppp hr_h2pi_salaries%ROWTYPE;
2507
2508 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2509 l_inv_next_sal_date_warning BOOLEAN;
2510 l_proposed_salary_warning BOOLEAN;
2511 l_approved_warning BOOLEAN;
2512 l_payroll_warning BOOLEAN;
2513
2514 BEGIN
2515 hr_utility.set_location('Entering:'|| l_proc, 10);
2516
2517 OPEN csr_ud_salary(p_pay_proposal_id);
2518 FETCH csr_ud_salary INTO v_ud_ppp;
2519
2520 l_assignment_id := hr_h2pi_map.get_to_id
2521 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
2522 p_from_id => v_ud_ppp.assignment_id,
2523 p_report_error => TRUE);
2524
2525 hr_utility.set_location(l_proc, 20);
2526 l_pay_proposal_id := hr_h2pi_map.get_to_id
2527 (p_table_name => 'PER_PAY_PROPOSALS',
2528 p_from_id => v_ud_ppp.pay_proposal_id);
2529
2530 OPEN csr_sal_ee(l_assignment_id,
2531 v_ud_ppp.change_date);
2532 FETCH csr_sal_ee INTO l_element_entry_id;
2533 hr_utility.set_location(l_proc, 25);
2534 CLOSE csr_sal_ee;
2535
2536 IF l_pay_proposal_id = -1 THEN
2537 hr_utility.set_location(l_proc, 30);
2538 hr_maintain_proposal_api.insert_salary_proposal(
2539 p_assignment_id => l_assignment_id
2540 ,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
2541 ,p_change_date => v_ud_ppp.change_date
2542 ,p_proposed_salary_n => v_ud_ppp.proposed_salary_n
2543 ,p_attribute_category => v_ud_ppp.attribute_category
2544 ,p_attribute1 => v_ud_ppp.attribute1
2545 ,p_attribute2 => v_ud_ppp.attribute2
2546 ,p_attribute3 => v_ud_ppp.attribute3
2547 ,p_attribute4 => v_ud_ppp.attribute4
2548 ,p_attribute5 => v_ud_ppp.attribute5
2549 ,p_attribute6 => v_ud_ppp.attribute6
2550 ,p_attribute7 => v_ud_ppp.attribute7
2551 ,p_attribute8 => v_ud_ppp.attribute8
2552 ,p_attribute9 => v_ud_ppp.attribute9
2553 ,p_attribute10 => v_ud_ppp.attribute10
2554 ,p_attribute11 => v_ud_ppp.attribute11
2555 ,p_attribute12 => v_ud_ppp.attribute12
2556 ,p_attribute13 => v_ud_ppp.attribute13
2557 ,p_attribute14 => v_ud_ppp.attribute14
2558 ,p_attribute15 => v_ud_ppp.attribute15
2559 ,p_attribute16 => v_ud_ppp.attribute16
2560 ,p_attribute17 => v_ud_ppp.attribute17
2561 ,p_attribute18 => v_ud_ppp.attribute18
2562 ,p_attribute19 => v_ud_ppp.attribute19
2563 ,p_attribute20 => v_ud_ppp.attribute20
2564 ,p_object_version_number => l_ovn
2565 ,p_multiple_components => 'N'
2566 ,p_approved => 'Y'
2567 ,p_element_entry_id => l_element_entry_id
2568 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
2569 ,p_proposed_salary_warning => l_proposed_salary_warning
2570 ,p_approved_warning => l_approved_warning
2571 ,p_payroll_warning => l_payroll_warning
2572 ,p_pay_proposal_id => l_pay_proposal_id
2573 );
2574
2575 hr_utility.set_location(l_proc || ' 2. Assignment_id..' || l_assignment_id, 101);
2576 hr_utility.set_location(l_proc || ' 2. Element_Entry_ID..' || l_element_entry_ID, 102);
2577 hr_utility.set_location(l_proc || ' 2. Proposed_salary_n..' || v_ud_ppp.proposed_salary_n, 103);
2578 hr_utility.set_location(l_proc || ' 2. Change_Date..' || v_ud_ppp.change_date, 104);
2579 hr_utility.set_location(l_proc, 40);
2580 hr_h2pi_map.create_id_mapping
2581 (p_table_name => 'PER_PAY_PROPOSALS',
2582 p_from_id => v_ud_ppp.pay_proposal_id,
2583 p_to_id => l_pay_proposal_id);
2584
2585 ELSE
2586 hr_utility.set_location(l_proc, 50);
2587 OPEN csr_ed_salary(l_pay_proposal_id);
2588 FETCH csr_ed_salary INTO l_ovn;
2589 IF csr_ed_salary%NOTFOUND THEN
2590 hr_utility.set_location(l_proc, 60);
2591 CLOSE csr_ed_salary;
2592 ROLLBACK;
2593 hr_utility.set_location(l_proc, 70);
2594 hr_h2pi_error.data_error
2595 (p_from_id => l_pay_proposal_id,
2596 p_table_name => 'HR_H2PI_SALARIES',
2597 p_message_level => 'FATAL',
2598 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
2599 COMMIT;
2600 RAISE MAPPING_ID_INVALID;
2601 ELSE
2602 CLOSE csr_ed_salary;
2603 END IF;
2604
2605 hr_utility.set_location(l_proc, 80);
2606 hr_maintain_proposal_api.update_salary_proposal(
2607 p_pay_proposal_id => l_pay_proposal_id
2608 ,p_object_version_number => l_ovn
2609 ,p_change_date => v_ud_ppp.change_date
2610 ,p_proposed_salary_n => v_ud_ppp.proposed_salary_n
2611 ,p_attribute_category => v_ud_ppp.attribute_category
2612 ,p_attribute1 => v_ud_ppp.attribute1
2613 ,p_attribute2 => v_ud_ppp.attribute2
2614 ,p_attribute3 => v_ud_ppp.attribute3
2615 ,p_attribute4 => v_ud_ppp.attribute4
2616 ,p_attribute5 => v_ud_ppp.attribute5
2617 ,p_attribute6 => v_ud_ppp.attribute6
2618 ,p_attribute7 => v_ud_ppp.attribute7
2619 ,p_attribute8 => v_ud_ppp.attribute8
2620 ,p_attribute9 => v_ud_ppp.attribute9
2621 ,p_attribute10 => v_ud_ppp.attribute10
2622 ,p_attribute11 => v_ud_ppp.attribute11
2623 ,p_attribute12 => v_ud_ppp.attribute12
2624 ,p_attribute13 => v_ud_ppp.attribute13
2625 ,p_attribute14 => v_ud_ppp.attribute14
2626 ,p_attribute15 => v_ud_ppp.attribute15
2627 ,p_attribute16 => v_ud_ppp.attribute16
2628 ,p_attribute17 => v_ud_ppp.attribute17
2629 ,p_attribute18 => v_ud_ppp.attribute18
2630 ,p_attribute19 => v_ud_ppp.attribute19
2631 ,p_attribute20 => v_ud_ppp.attribute20
2632 ,p_approved => 'Y'
2633 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
2634 ,p_proposed_salary_warning => l_proposed_salary_warning
2635 ,p_approved_warning => l_approved_warning
2636 ,p_payroll_warning => l_payroll_warning
2637 );
2638 END IF;
2639
2640 hr_utility.set_location(l_proc, 90);
2641 UPDATE hr_h2pi_salaries ppp
2642 SET status = 'C'
2643 WHERE ppp.pay_proposal_id = v_ud_ppp.pay_proposal_id
2644 AND ppp.client_id = p_from_client_id;
2645 CLOSE csr_ud_salary;
2646
2647 hr_utility.set_location('Leaving:'|| l_proc, 100);
2648 COMMIT;
2649
2650 EXCEPTION
2651 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
2652 ROLLBACK;
2653 hr_utility.set_location(l_proc, 110);
2654 l_encoded_message := fnd_message.get_encoded;
2655 hr_h2pi_error.data_error
2656 (p_from_id => v_ud_ppp.pay_proposal_id,
2657 p_table_name => 'HR_H2PI_SALARIES',
2658 p_message_level => 'FATAL',
2659 p_message_text => l_encoded_message);
2660 COMMIT;
2661 RAISE;
2662
2663 END;
2664
2665
2666 PROCEDURE upload_payment_method (p_from_client_id NUMBER,
2667 p_personal_payment_method_id NUMBER,
2668 p_effective_start_date DATE) IS
2669
2670 CURSOR csr_ud_payment_method (p_ppm_id NUMBER,
2671 p_esd DATE) IS
2672 SELECT *
2673 FROM hr_h2pi_payment_methods ppm
2674 WHERE ppm.personal_payment_method_id = p_ppm_id
2675 AND ppm.client_id = p_from_client_id
2676 AND ppm.effective_start_date = p_esd;
2677
2678 CURSOR csr_ed_payment_method (p_ppm_id NUMBER,
2679 p_esd DATE) IS
2680 SELECT *
2681 FROM hr_h2pi_payment_methods_v ppm
2682 WHERE ppm.personal_payment_method_id = p_ppm_id
2683 AND p_esd BETWEEN ppm.effective_start_date
2684 AND ppm.effective_end_date;
2685
2686 CURSOR csr_ed_payment_method_ovn (p_ppm_id NUMBER,
2687 p_esd DATE) IS
2688 SELECT ppm.object_version_number
2689 FROM pay_personal_payment_methods_f ppm
2690 WHERE ppm.personal_payment_method_id = p_ppm_id
2691 AND p_esd BETWEEN ppm.effective_start_date
2692 AND ppm.effective_end_date;
2693
2694
2695 l_encoded_message VARCHAR2(200);
2696
2697 l_proc VARCHAR2(72) := g_package||'upload_payment_method';
2698
2699 v_ud_ppm hr_h2pi_payment_methods%ROWTYPE;
2700 v_ed_ppm hr_h2pi_payment_methods_v%ROWTYPE;
2701
2702 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2703 l_personal_pay_method_id
2704 pay_personal_payment_methods_f.personal_payment_method_id%TYPE;
2705 l_org_pay_method_id pay_personal_payment_methods_f.org_payment_method_id%TYPE;
2706 l_ovn pay_personal_payment_methods_f.object_version_number%TYPE;
2707 l_esd pay_personal_payment_methods_f.effective_start_date%TYPE;
2708 l_eed pay_personal_payment_methods_f.effective_end_date%TYPE;
2709
2710 l_max_eed pay_personal_payment_methods_f.effective_end_date%TYPE;
2711 l_del_ovn pay_personal_payment_methods_f.object_version_number%TYPE;
2712 l_del_esd pay_personal_payment_methods_f.effective_start_date%TYPE;
2713 l_del_eed pay_personal_payment_methods_f.effective_end_date%TYPE;
2714 l_val_esd pay_personal_payment_methods_f.effective_start_date%TYPE;
2715 l_val_eed pay_personal_payment_methods_f.effective_end_date%TYPE;
2716 l_business_group_id pay_personal_payment_methods_f.business_group_id%TYPE;
2717
2718 l_records_same BOOLEAN;
2719 l_future_records BOOLEAN;
2720 l_update_mode VARCHAR2(30);
2721 l_delete_mode VARCHAR2(30);
2722
2723 l_external_account_id pay_personal_payment_methods_f.external_account_id%TYPE;
2724 l_comment_id pay_personal_payment_methods_f.comment_id%TYPE;
2725
2726 --
2727 l_payee_id pay_personal_payment_methods_f.payee_id%TYPE;
2728 l_payee_type pay_personal_payment_methods_f.payee_type%TYPE;
2729 --
2730
2731 BEGIN
2732 hr_utility.set_location('Entering:'|| l_proc, 10);
2733
2734 OPEN csr_ud_payment_method(p_personal_payment_method_id,
2735 p_effective_start_date);
2736 FETCH csr_ud_payment_method INTO v_ud_ppm;
2737
2738 IF v_ud_ppm.last_upd_date = g_eot THEN
2739
2740 hr_utility.set_location(l_proc, 20);
2741 l_personal_pay_method_id := hr_h2pi_map.get_to_id
2742 (p_table_name => 'PAY_PERSONAL_PAYMENT_METHODS_F',
2743 p_from_id => v_ud_ppm.personal_payment_method_id,
2744 p_report_error => TRUE);
2745
2746 hr_utility.set_location(l_proc, 30);
2747 OPEN csr_ed_payment_method_ovn(l_personal_pay_method_id,
2748 v_ud_ppm.effective_start_date);
2749 FETCH csr_ed_payment_method_ovn
2750 INTO l_ovn;
2751
2752 IF csr_ed_payment_method_ovn%FOUND THEN
2753
2754 l_delete_mode := 'DELETE';
2755 hr_personal_pay_method_api.delete_personal_pay_method(
2756 p_effective_date => v_ud_ppm.effective_start_date-1
2757 ,p_datetrack_delete_mode => l_delete_mode
2758 ,p_personal_payment_method_id => l_personal_pay_method_id
2759 ,p_object_version_number => l_ovn
2760 ,p_effective_start_date => l_esd
2761 ,p_effective_end_date => l_eed
2762 );
2763 END IF;
2764
2765 CLOSE csr_ed_payment_method_ovn;
2766
2767 ELSE
2768
2769 hr_utility.set_location(l_proc, 70);
2770 l_assignment_id := hr_h2pi_map.get_to_id
2771 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
2772 p_from_id => v_ud_ppm.assignment_id,
2773 p_report_error => TRUE);
2774
2775 l_org_pay_method_id := hr_h2pi_map.get_to_id
2776 (p_table_name => 'PAY_ORG_PAYMENT_METHODS_F',
2777 p_from_id => v_ud_ppm.org_payment_method_id,
2778 p_report_error => TRUE);
2779
2780 l_personal_pay_method_id := hr_h2pi_map.get_to_id
2781 (p_table_name => 'PAY_PERSONAL_PAYMENT_METHODS_F',
2782 p_from_id => v_ud_ppm.personal_payment_method_id);
2783
2784 --
2785 IF v_ud_ppm.payee_type = 'O' THEN
2786 hr_utility.set_location(l_proc, 71);
2787 l_payee_type := v_ud_ppm.payee_type;
2788 l_payee_id := hr_h2pi_map.get_to_id
2789 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
2790 p_from_id => v_ud_ppm.payee_id);
2791
2792 ELSIF v_ud_ppm.payee_type = 'P' THEN
2793 hr_utility.set_location(l_proc, 72);
2794 l_payee_type := null;
2795 l_payee_id := null;
2796 hr_h2pi_error.data_error
2797 (p_from_id => v_ud_ppm.payee_id,
2798 p_table_name => 'HR_H2PI_EMPLOYEES',
2799 p_message_level => 'FATAL',
2800 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
2801 ELSE
2802 l_payee_type := v_ud_ppm.payee_type;
2803 l_payee_id := v_ud_ppm.payee_id;
2804 END IF;
2805 --
2806
2807 IF l_personal_pay_method_id = -1 THEN
2808 hr_utility.set_location(l_proc, 80);
2809 hr_personal_pay_method_api.create_personal_pay_method(
2810 p_effective_date => v_ud_ppm.effective_start_date
2811 ,p_assignment_id => l_assignment_id
2812 ,p_org_payment_method_id => l_org_pay_method_id
2813 ,p_amount => v_ud_ppm.amount
2814 ,p_percentage => v_ud_ppm.percentage
2815 ,p_priority => v_ud_ppm.priority
2816 ,p_attribute_category => v_ud_ppm.attribute_category
2817 ,p_attribute1 => v_ud_ppm.attribute1
2818 ,p_attribute2 => v_ud_ppm.attribute2
2819 ,p_attribute3 => v_ud_ppm.attribute3
2820 ,p_attribute4 => v_ud_ppm.attribute4
2821 ,p_attribute5 => v_ud_ppm.attribute5
2822 ,p_attribute6 => v_ud_ppm.attribute6
2823 ,p_attribute7 => v_ud_ppm.attribute7
2824 ,p_attribute8 => v_ud_ppm.attribute8
2825 ,p_attribute9 => v_ud_ppm.attribute9
2826 ,p_attribute10 => v_ud_ppm.attribute10
2827 ,p_attribute11 => v_ud_ppm.attribute11
2828 ,p_attribute12 => v_ud_ppm.attribute12
2829 ,p_attribute13 => v_ud_ppm.attribute13
2830 ,p_attribute14 => v_ud_ppm.attribute14
2831 ,p_attribute15 => v_ud_ppm.attribute15
2832 ,p_attribute16 => v_ud_ppm.attribute16
2833 ,p_attribute17 => v_ud_ppm.attribute17
2834 ,p_attribute18 => v_ud_ppm.attribute18
2835 ,p_attribute19 => v_ud_ppm.attribute19
2836 ,p_attribute20 => v_ud_ppm.attribute20
2837 ,p_territory_code => v_ud_ppm.territory_code
2838 ,p_segment1 => v_ud_ppm.segment1
2839 ,p_segment2 => v_ud_ppm.segment2
2840 ,p_segment3 => v_ud_ppm.segment3
2841 ,p_segment4 => v_ud_ppm.segment4
2842 ,p_segment5 => v_ud_ppm.segment5
2843 ,p_segment6 => v_ud_ppm.segment6
2844 ,p_segment7 => v_ud_ppm.segment7
2845 ,p_segment8 => v_ud_ppm.segment8
2846 ,p_segment9 => v_ud_ppm.segment9
2847 ,p_segment10 => v_ud_ppm.segment10
2848 ,p_segment11 => v_ud_ppm.segment11
2849 ,p_segment12 => v_ud_ppm.segment12
2850 ,p_segment13 => v_ud_ppm.segment13
2851 ,p_segment14 => v_ud_ppm.segment14
2852 ,p_segment15 => v_ud_ppm.segment15
2853 ,p_segment16 => v_ud_ppm.segment16
2854 ,p_segment17 => v_ud_ppm.segment17
2855 ,p_segment18 => v_ud_ppm.segment18
2856 ,p_segment19 => v_ud_ppm.segment19
2857 ,p_segment20 => v_ud_ppm.segment20
2858 ,p_segment21 => v_ud_ppm.segment21
2859 ,p_segment22 => v_ud_ppm.segment22
2860 ,p_segment23 => v_ud_ppm.segment23
2861 ,p_segment24 => v_ud_ppm.segment24
2862 ,p_segment25 => v_ud_ppm.segment25
2863 ,p_segment26 => v_ud_ppm.segment26
2864 ,p_segment27 => v_ud_ppm.segment27
2865 ,p_segment28 => v_ud_ppm.segment28
2866 ,p_segment29 => v_ud_ppm.segment29
2867 ,p_segment30 => v_ud_ppm.segment30
2868 --
2869 ,p_payee_type => l_payee_type
2870 ,p_payee_id => l_payee_id
2871 --
2872 ,p_personal_payment_method_id => l_personal_pay_method_id
2873 ,p_external_account_id => l_external_account_id
2874 ,p_object_version_number => l_ovn
2875 ,p_effective_start_date => l_esd
2876 ,p_effective_end_date => l_eed
2877 ,p_comment_id => l_comment_id
2878 );
2879
2880 hr_utility.set_location(l_proc, 90);
2881 hr_h2pi_map.create_id_mapping
2882 (p_table_name => 'PAY_PERSONAL_PAYMENT_METHODS_F',
2883 p_from_id => v_ud_ppm.personal_payment_method_id,
2884 p_to_id => l_personal_pay_method_id);
2885 ELSE
2886
2887 BEGIN
2888 hr_utility.set_location(l_proc, 100);
2889 OPEN csr_ed_payment_method(l_personal_pay_method_id,
2890 v_ud_ppm.effective_start_date);
2891 FETCH csr_ed_payment_method
2892 INTO v_ed_ppm;
2893 IF csr_ed_payment_method%NOTFOUND THEN
2894 hr_utility.set_location(l_proc, 110);
2895 CLOSE csr_ed_payment_method;
2896 ROLLBACK;
2897 hr_utility.set_location(l_proc, 120);
2898 hr_h2pi_error.data_error
2899 (p_from_id => l_personal_pay_method_id,
2900 p_table_name => 'HR_H2PI_PAYMENT_METHODS',
2901 p_message_level => 'FATAL',
2902 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
2903 COMMIT;
2904 RAISE MAPPING_ID_INVALID;
2905 ELSE
2906 CLOSE csr_ed_payment_method;
2907 END IF;
2908
2909 OPEN csr_ed_payment_method_ovn(l_personal_pay_method_id,
2910 v_ud_ppm.effective_start_date);
2911 FETCH csr_ed_payment_method_ovn
2912 INTO l_ovn;
2913 CLOSE csr_ed_payment_method_ovn;
2914 END;
2915
2916 l_delete_mode := 'DELETE_NEXT_CHANGE';
2917 LOOP
2918 hr_utility.set_location(l_proc, 120);
2919 l_records_same := FALSE;
2920
2921 SELECT MAX(ppm.effective_end_date)
2922 INTO l_max_eed
2923 FROM pay_personal_payment_methods_f ppm
2924 WHERE ppm.personal_payment_method_id = l_personal_pay_method_id;
2925
2926 IF l_max_eed > v_ed_ppm.effective_end_date THEN
2927 hr_utility.set_location(l_proc, 130);
2928 l_future_records := TRUE;
2929 ELSE
2930 hr_utility.set_location(l_proc, 135);
2931 l_future_records := FALSE;
2932 END IF;
2933
2934 calculate_datetrack_mode
2935 (p_ud_start_date => v_ud_ppm.effective_start_date
2936 ,p_ud_end_date => v_ud_ppm.effective_end_date
2937 ,p_ed_start_date => v_ed_ppm.effective_start_date
2938 ,p_ed_end_date => v_ed_ppm.effective_end_date
2939 ,p_records_same => l_records_same
2940 ,p_future_records => l_future_records
2941 ,p_update_mode => l_update_mode
2942 ,p_delete_mode => l_delete_mode);
2943
2944 EXIT WHEN l_delete_mode = 'X';
2945
2946 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
2947
2948 hr_utility.set_location(l_proc, 140);
2949 hr_personal_pay_method_api.delete_personal_pay_method(
2950 p_effective_date => v_ud_ppm.effective_start_date
2951 ,p_datetrack_delete_mode => l_delete_mode
2952 ,p_personal_payment_method_id => l_personal_pay_method_id
2953 ,p_object_version_number => l_ovn
2954 ,p_effective_start_date => l_esd
2955 ,p_effective_end_date => l_eed
2956 );
2957
2958 hr_utility.set_location(l_proc, 150);
2959 OPEN csr_ed_payment_method(l_personal_pay_method_id,
2960 v_ud_ppm.effective_start_date);
2961 FETCH csr_ed_payment_method
2962 INTO v_ed_ppm;
2963 CLOSE csr_ed_payment_method;
2964
2965 END IF;
2966
2967 END LOOP;
2968
2969 hr_personal_pay_method_api.update_personal_pay_method(
2970 p_effective_date => v_ud_ppm.effective_start_date
2971 ,p_datetrack_update_mode => l_update_mode
2972 ,p_amount => v_ud_ppm.amount
2973 ,p_percentage => v_ud_ppm.percentage
2974 ,p_priority => v_ud_ppm.priority
2975 ,p_attribute_category => v_ud_ppm.attribute_category
2976 ,p_attribute1 => v_ud_ppm.attribute1
2977 ,p_attribute2 => v_ud_ppm.attribute2
2978 ,p_attribute3 => v_ud_ppm.attribute3
2979 ,p_attribute4 => v_ud_ppm.attribute4
2980 ,p_attribute5 => v_ud_ppm.attribute5
2981 ,p_attribute6 => v_ud_ppm.attribute6
2982 ,p_attribute7 => v_ud_ppm.attribute7
2983 ,p_attribute8 => v_ud_ppm.attribute8
2984 ,p_attribute9 => v_ud_ppm.attribute9
2985 ,p_attribute10 => v_ud_ppm.attribute10
2986 ,p_attribute11 => v_ud_ppm.attribute11
2987 ,p_attribute12 => v_ud_ppm.attribute12
2988 ,p_attribute13 => v_ud_ppm.attribute13
2989 ,p_attribute14 => v_ud_ppm.attribute14
2990 ,p_attribute15 => v_ud_ppm.attribute15
2991 ,p_attribute16 => v_ud_ppm.attribute16
2992 ,p_attribute17 => v_ud_ppm.attribute17
2993 ,p_attribute18 => v_ud_ppm.attribute18
2994 ,p_attribute19 => v_ud_ppm.attribute19
2995 ,p_attribute20 => v_ud_ppm.attribute20
2996 ,p_territory_code => v_ud_ppm.territory_code
2997 ,p_segment1 => v_ud_ppm.segment1
2998 ,p_segment2 => v_ud_ppm.segment2
2999 ,p_segment3 => v_ud_ppm.segment3
3000 ,p_segment4 => v_ud_ppm.segment4
3001 ,p_segment5 => v_ud_ppm.segment5
3002 ,p_segment6 => v_ud_ppm.segment6
3003 ,p_segment7 => v_ud_ppm.segment7
3004 ,p_segment8 => v_ud_ppm.segment8
3005 ,p_segment9 => v_ud_ppm.segment9
3006 ,p_segment10 => v_ud_ppm.segment10
3007 ,p_segment11 => v_ud_ppm.segment11
3008 ,p_segment12 => v_ud_ppm.segment12
3009 ,p_segment13 => v_ud_ppm.segment13
3010 ,p_segment14 => v_ud_ppm.segment14
3011 ,p_segment15 => v_ud_ppm.segment15
3012 ,p_segment16 => v_ud_ppm.segment16
3013 ,p_segment17 => v_ud_ppm.segment17
3014 ,p_segment18 => v_ud_ppm.segment18
3015 ,p_segment19 => v_ud_ppm.segment19
3016 ,p_segment20 => v_ud_ppm.segment20
3017 ,p_segment21 => v_ud_ppm.segment21
3018 ,p_segment22 => v_ud_ppm.segment22
3019 ,p_segment23 => v_ud_ppm.segment23
3020 ,p_segment24 => v_ud_ppm.segment24
3021 ,p_segment25 => v_ud_ppm.segment25
3022 ,p_segment26 => v_ud_ppm.segment26
3023 ,p_segment27 => v_ud_ppm.segment27
3024 ,p_segment28 => v_ud_ppm.segment28
3025 ,p_segment29 => v_ud_ppm.segment29
3026 ,p_segment30 => v_ud_ppm.segment30
3027 ,p_personal_payment_method_id => l_personal_pay_method_id
3028 ,p_object_version_number => l_ovn
3029 ,p_external_account_id => l_external_account_id
3030 ,p_effective_start_date => l_esd
3031 ,p_effective_end_date => l_eed
3032 ,p_comment_id => l_comment_id
3033 );
3034
3035 END IF;
3036 END IF;
3037
3038 hr_utility.set_location(l_proc, 200);
3039 UPDATE hr_h2pi_payment_methods ppm
3040 SET status = 'C'
3041 WHERE ppm.personal_payment_method_id = v_ud_ppm.personal_payment_method_id
3042 AND ppm.client_id = p_from_client_id
3043 AND ppm.effective_start_date = v_ud_ppm.effective_start_date
3044 AND ppm.effective_end_date = v_ud_ppm.effective_end_date;
3045
3046 CLOSE csr_ud_payment_method;
3047 hr_utility.set_location('Leaving:'|| l_proc, 210);
3048 COMMIT;
3049
3050 EXCEPTION
3051 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
3052 ROLLBACK;
3053 hr_utility.set_location(l_proc, 230);
3054 l_encoded_message := fnd_message.get_encoded;
3055 hr_h2pi_error.data_error
3056 (p_from_id => v_ud_ppm.personal_payment_method_id,
3057 p_table_name => 'HR_H2PI_PAYMENT_METHODS',
3058 p_message_level => 'FATAL',
3059 p_message_text => l_encoded_message);
3060 COMMIT;
3061 RAISE;
3062 END;
3063
3064
3065 PROCEDURE upload_cost_allocation (p_from_client_id NUMBER,
3066 p_cost_allocation_id NUMBER,
3067 p_effective_start_date DATE) IS
3068
3069 CURSOR csr_ud_cost_allocation (p_cost_allocation_id NUMBER,
3070 p_esd DATE) IS
3071 SELECT *
3072 FROM hr_h2pi_cost_allocations hca
3073 WHERE hca.cost_allocation_id = p_cost_allocation_id
3074 AND hca.client_id = p_from_client_id
3075 AND hca.effective_start_date = p_esd;
3076
3077 CURSOR csr_ed_cost_allocation (p_cost_allocation_id NUMBER,
3078 p_esd DATE) IS
3079 SELECT *
3080 FROM hr_h2pi_cost_allocations_v hca
3081 WHERE hca.cost_allocation_id = p_cost_allocation_id
3082 AND p_esd BETWEEN hca.effective_start_date
3083 AND hca.effective_end_date;
3084
3085 CURSOR csr_ed_cost_allocation_ovn (p_cost_allocation_id NUMBER,
3086 p_esd DATE) IS
3087 SELECT pca.object_version_number
3088 FROM pay_cost_allocations_f pca
3089 WHERE pca.cost_allocation_id = p_cost_allocation_id
3090 AND p_esd BETWEEN pca.effective_start_date
3091 AND pca.effective_end_date;
3092
3093 l_encoded_message VARCHAR2(200);
3094 l_proc VARCHAR2(72) := g_package||'upload_cost_allocation';
3095
3096 v_ud_hca hr_h2pi_cost_allocations%ROWTYPE;
3097 v_ed_hca hr_h2pi_cost_allocations_v%ROWTYPE;
3098
3099 l_assignment_id pay_cost_allocations_f.assignment_id%TYPE;
3100 l_cost_allocation_id pay_cost_allocations_f.cost_allocation_id%TYPE;
3101 l_combination_name VARCHAR2(240);
3102 l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3103 l_ovn pay_cost_allocations_f.object_version_number%TYPE;
3104 l_esd pay_cost_allocations_f.effective_start_date%TYPE;
3105 l_eed pay_cost_allocations_f.effective_end_date%TYPE;
3106
3107 l_max_eed pay_cost_allocations_f.effective_end_date%TYPE;
3108 l_del_ovn pay_cost_allocations_f.object_version_number%TYPE;
3109 l_del_esd pay_cost_allocations_f.effective_start_date%TYPE;
3110 l_del_eed pay_cost_allocations_f.effective_end_date%TYPE;
3111 l_val_esd pay_cost_allocations_f.effective_start_date%TYPE;
3112 l_val_eed pay_cost_allocations_f.effective_end_date%TYPE;
3113 l_business_group_id pay_cost_allocations_f.business_group_id%TYPE;
3114
3115 l_records_same BOOLEAN;
3116 l_future_records BOOLEAN;
3117 l_update_mode VARCHAR2(30);
3118 l_delete_mode VARCHAR2(30);
3119
3120
3121 BEGIN
3122 hr_utility.set_location('Entering:'|| l_proc, 10);
3123
3124 OPEN csr_ud_cost_allocation(p_cost_allocation_id,
3125 p_effective_start_date);
3126 FETCH csr_ud_cost_allocation INTO v_ud_hca;
3127
3128 IF v_ud_hca.last_upd_date = g_eot THEN
3129
3130 hr_utility.set_location(l_proc, 20);
3131 l_cost_allocation_id := hr_h2pi_map.get_to_id
3132 (p_table_name => 'PAY_COST_ALLOCATIONS_F',
3133 p_from_id => v_ud_hca.cost_allocation_id,
3134 p_report_error => TRUE);
3135
3136 hr_utility.set_location(l_proc, 30);
3137 OPEN csr_ed_cost_allocation_ovn(l_cost_allocation_id,
3138 v_ud_hca.effective_start_date);
3139 FETCH csr_ed_cost_allocation_ovn
3140 INTO l_ovn;
3141
3142 IF csr_ed_cost_allocation_ovn%FOUND THEN
3143
3144 l_delete_mode := 'DELETE';
3145 pay_cost_allocation_api.delete_cost_allocation(
3146 p_validate => FALSE
3147 ,p_effective_date => v_ud_hca.effective_start_date - 1
3148 ,p_datetrack_delete_mode => l_delete_mode
3149 ,p_cost_allocation_id => l_cost_allocation_id
3150 ,p_object_version_number => l_ovn
3151 ,p_effective_start_date => l_esd
3152 ,p_effective_end_date => l_eed);
3153
3154 END IF;
3155
3156 CLOSE csr_ed_cost_allocation_ovn;
3157 ELSE
3158
3159 hr_utility.set_location(l_proc, 40);
3160 l_assignment_id := hr_h2pi_map.get_to_id
3161 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
3162 p_from_id => v_ud_hca.assignment_id,
3163 p_report_error => TRUE);
3164
3165 l_cost_allocation_id := hr_h2pi_map.get_to_id
3166 (p_table_name => 'PAY_COST_ALLOCATIONS_F',
3167 p_from_id => v_ud_hca.cost_allocation_id);
3168
3169 IF l_cost_allocation_id = -1 THEN
3170 hr_utility.set_location(l_proc, 50);
3171 pay_cost_allocation_api.create_cost_allocation(
3172 p_effective_date => v_ud_hca.effective_start_date
3173 ,p_assignment_id => l_assignment_id
3174 ,p_proportion => v_ud_hca.proportion
3175 ,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
3176 ,p_segment1 => v_ud_hca.segment1
3177 ,p_segment2 => v_ud_hca.segment2
3178 ,p_segment3 => v_ud_hca.segment3
3179 ,p_segment4 => v_ud_hca.segment4
3180 ,p_segment5 => v_ud_hca.segment5
3181 ,p_segment6 => v_ud_hca.segment6
3182 ,p_segment7 => v_ud_hca.segment7
3183 ,p_segment8 => v_ud_hca.segment8
3184 ,p_segment9 => v_ud_hca.segment9
3185 ,p_segment10 => v_ud_hca.segment10
3186 ,p_segment11 => v_ud_hca.segment11
3187 ,p_segment12 => v_ud_hca.segment12
3188 ,p_segment13 => v_ud_hca.segment13
3189 ,p_segment14 => v_ud_hca.segment14
3190 ,p_segment15 => v_ud_hca.segment15
3191 ,p_segment16 => v_ud_hca.segment16
3192 ,p_segment17 => v_ud_hca.segment17
3193 ,p_segment18 => v_ud_hca.segment18
3194 ,p_segment19 => v_ud_hca.segment19
3195 ,p_segment20 => v_ud_hca.segment20
3196 ,p_segment21 => v_ud_hca.segment21
3197 ,p_segment22 => v_ud_hca.segment22
3198 ,p_segment23 => v_ud_hca.segment23
3199 ,p_segment24 => v_ud_hca.segment24
3200 ,p_segment25 => v_ud_hca.segment25
3201 ,p_segment26 => v_ud_hca.segment26
3202 ,p_segment27 => v_ud_hca.segment27
3203 ,p_segment28 => v_ud_hca.segment28
3204 ,p_segment29 => v_ud_hca.segment29
3205 ,p_segment30 => v_ud_hca.segment30
3206 ,p_concat_segments => v_ud_hca.concatenated_segments
3207 ,p_combination_name => l_combination_name
3208 ,p_cost_allocation_id => l_cost_allocation_id
3209 ,p_effective_start_date => l_esd
3210 ,p_effective_end_date => l_eed
3211 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
3212 ,p_object_version_number => l_ovn );
3213
3214 hr_utility.set_location(l_proc, 60);
3215 hr_h2pi_map.create_id_mapping
3216 (p_table_name => 'PAY_COST_ALLOCATIONS_F',
3217 p_from_id => v_ud_hca.cost_allocation_id,
3218 p_to_id => l_cost_allocation_id);
3219 ELSE
3220
3221 BEGIN
3222 hr_utility.set_location(l_proc, 70);
3223 OPEN csr_ed_cost_allocation(l_cost_allocation_id,
3224 v_ud_hca.effective_start_date);
3225 FETCH csr_ed_cost_allocation
3226 INTO v_ed_hca;
3227 IF csr_ed_cost_allocation%NOTFOUND THEN
3228 hr_utility.set_location(l_proc, 80);
3229 CLOSE csr_ed_cost_allocation;
3230 ROLLBACK;
3231 hr_utility.set_location(l_proc, 90);
3232 hr_h2pi_error.data_error
3233 (p_from_id => l_cost_allocation_id,
3234 p_table_name => 'HR_H2PI_COST_ALLOCATIONS',
3235 p_message_level => 'FATAL',
3236 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
3237 COMMIT;
3238 RAISE MAPPING_ID_INVALID;
3239 ELSE
3240 CLOSE csr_ed_cost_allocation;
3241 END IF;
3242
3243 OPEN csr_ed_cost_allocation_ovn(l_cost_allocation_id,
3244 v_ud_hca.effective_start_date);
3245 FETCH csr_ed_cost_allocation_ovn
3246 INTO l_ovn;
3247 CLOSE csr_ed_cost_allocation_ovn;
3248 END;
3249
3250 l_delete_mode := 'DELETE_NEXT_CHANGE';
3251 LOOP
3252 hr_utility.set_location(l_proc, 100);
3253 l_records_same := FALSE;
3254
3255 SELECT MAX(caf.effective_end_date)
3256 INTO l_max_eed
3257 FROM pay_cost_allocations_f caf
3258 WHERE caf.cost_allocation_id = l_cost_allocation_id;
3259
3260 IF l_max_eed > v_ed_hca.effective_end_date THEN
3261 hr_utility.set_location(l_proc, 110);
3262 l_future_records := TRUE;
3263 ELSE
3264 hr_utility.set_location(l_proc, 120);
3265 l_future_records := FALSE;
3266 END IF;
3267
3268 calculate_datetrack_mode
3269 (p_ud_start_date => v_ud_hca.effective_start_date
3270 ,p_ud_end_date => v_ud_hca.effective_end_date
3271 ,p_ed_start_date => v_ed_hca.effective_start_date
3272 ,p_ed_end_date => v_ed_hca.effective_end_date
3273 ,p_records_same => l_records_same
3274 ,p_future_records => l_future_records
3275 ,p_update_mode => l_update_mode
3276 ,p_delete_mode => l_delete_mode);
3277
3278 EXIT WHEN l_delete_mode = 'X';
3279 hr_utility.set_location(l_proc, 130);
3280
3281 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
3282
3283 hr_utility.set_location(l_proc, 140);
3284 pay_cost_allocation_api.delete_cost_allocation(
3285 p_effective_date => v_ud_hca.effective_start_date
3286 ,p_datetrack_delete_mode => l_delete_mode
3287 ,p_cost_allocation_id => l_cost_allocation_id
3288 ,p_object_version_number => l_ovn
3289 ,p_effective_start_date => l_esd
3290 ,p_effective_end_date => l_eed );
3291
3292 hr_utility.set_location(l_proc, 150);
3293 OPEN csr_ed_cost_allocation(l_cost_allocation_id,
3294 v_ud_hca.effective_start_date);
3295 FETCH csr_ed_cost_allocation
3296 INTO v_ed_hca;
3297 CLOSE csr_ed_cost_allocation;
3298
3299 END IF;
3300
3301 END LOOP;
3302
3303 hr_utility.set_location(l_proc, 160);
3304 pay_cost_allocation_api.update_cost_allocation(
3305 p_effective_date => v_ud_hca.effective_start_date
3306 ,p_datetrack_update_mode => l_update_mode
3307 ,p_cost_allocation_id => l_cost_allocation_id
3308 ,p_object_version_number => l_ovn
3309 ,p_proportion => v_ud_hca.proportion
3310 ,p_segment1 => v_ud_hca.segment1
3311 ,p_segment2 => v_ud_hca.segment2
3312 ,p_segment3 => v_ud_hca.segment3
3313 ,p_segment4 => v_ud_hca.segment4
3314 ,p_segment5 => v_ud_hca.segment5
3315 ,p_segment6 => v_ud_hca.segment6
3316 ,p_segment7 => v_ud_hca.segment7
3317 ,p_segment8 => v_ud_hca.segment8
3318 ,p_segment9 => v_ud_hca.segment9
3319 ,p_segment10 => v_ud_hca.segment10
3320 ,p_segment11 => v_ud_hca.segment11
3321 ,p_segment12 => v_ud_hca.segment12
3322 ,p_segment13 => v_ud_hca.segment13
3323 ,p_segment14 => v_ud_hca.segment14
3324 ,p_segment15 => v_ud_hca.segment15
3325 ,p_segment16 => v_ud_hca.segment16
3326 ,p_segment17 => v_ud_hca.segment17
3327 ,p_segment18 => v_ud_hca.segment18
3328 ,p_segment19 => v_ud_hca.segment19
3329 ,p_segment20 => v_ud_hca.segment20
3330 ,p_segment21 => v_ud_hca.segment21
3331 ,p_segment22 => v_ud_hca.segment22
3332 ,p_segment23 => v_ud_hca.segment23
3333 ,p_segment24 => v_ud_hca.segment24
3334 ,p_segment25 => v_ud_hca.segment25
3335 ,p_segment26 => v_ud_hca.segment26
3336 ,p_segment27 => v_ud_hca.segment27
3337 ,p_segment28 => v_ud_hca.segment28
3338 ,p_segment29 => v_ud_hca.segment29
3339 ,p_segment30 => v_ud_hca.segment30
3340 ,p_concat_segments => v_ud_hca.concatenated_segments
3341 ,p_combination_name => l_combination_name
3342 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
3343 ,p_effective_start_date => l_esd
3344 ,p_effective_end_date => l_eed );
3345
3346 END IF;
3347 END IF;
3348
3349 hr_utility.set_location(l_proc, 170);
3350 UPDATE hr_h2pi_cost_allocations hca
3351 SET status = 'C'
3352 WHERE hca.cost_allocation_id = v_ud_hca.cost_allocation_id
3353 AND hca.client_id = p_from_client_id
3354 AND hca.effective_start_date = v_ud_hca.effective_start_date
3355 AND hca.effective_end_date = v_ud_hca.effective_end_date;
3356
3357 CLOSE csr_ud_cost_allocation;
3358 hr_utility.set_location('Leaving:'|| l_proc, 180);
3359 COMMIT;
3360
3361 EXCEPTION
3362 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
3363 ROLLBACK;
3364 hr_utility.set_location(l_proc, 190);
3365 l_encoded_message := fnd_message.get_encoded;
3366 hr_h2pi_error.data_error
3367 (p_from_id => v_ud_hca.cost_allocation_id,
3368 p_table_name => 'HR_H2PI_COST_ALLOCATIONS',
3369 p_message_level => 'FATAL',
3370 p_message_text => l_encoded_message);
3371 COMMIT;
3372 RAISE;
3373
3374 END;
3375
3376
3377 PROCEDURE upload_element_entry (p_from_client_id NUMBER,
3378 p_element_entry_id NUMBER,
3379 p_effective_start_date DATE) IS
3380
3381 CURSOR csr_ud_element_entry (p_ele_id NUMBER,
3382 p_esd DATE) IS
3383 SELECT *
3384 FROM hr_h2pi_element_entries ele
3385 WHERE ele.element_entry_id = p_ele_id
3386 AND ele.client_id = p_from_client_id
3387 AND ele.effective_start_date = p_esd;
3388
3389 CURSOR csr_ud_element_entry_value (p_ele_id NUMBER,
3390 p_esd DATE) IS
3391 SELECT *
3392 FROM hr_h2pi_element_entry_values eev
3393 WHERE eev.element_entry_id = p_ele_id
3394 AND eev.screen_entry_value IS NOT NULL
3395 AND eev.client_id = p_from_client_id
3396 AND p_esd BETWEEN eev.effective_start_date
3397 AND eev.effective_end_date;
3398
3399 CURSOR csr_ed_element_entry (p_ele_id NUMBER,
3400 p_esd DATE) IS
3401 SELECT *
3402 FROM hr_h2pi_element_entries_v ele
3403 WHERE ele.element_entry_id = p_ele_id
3404 AND p_esd BETWEEN ele.effective_start_date
3405 AND ele.effective_end_date;
3406
3407 CURSOR csr_ed_element_entry_ovn (p_ele_id NUMBER,
3408 p_esd DATE) IS
3409 SELECT ele.object_version_number
3410 FROM pay_element_entries_f ele
3411 WHERE ele.element_entry_id = p_ele_id
3412 AND p_esd BETWEEN ele.effective_start_date
3413 AND ele.effective_end_date;
3414
3415 l_encoded_message VARCHAR2(200);
3416
3417 l_proc VARCHAR2(72) := g_package||'upload_element_entry';
3418
3419
3420 TYPE eev_array IS VARRAY(15) OF hr_h2pi_element_entry_values.screen_entry_value%TYPE;
3421 TYPE iv_array IS VARRAY(15) OF hr_h2pi_element_entry_values.input_value_id%TYPE;
3422
3423 v_ud_ele hr_h2pi_element_entries%ROWTYPE;
3424 a_ud_sev eev_array := eev_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3425 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3426 v_ed_ele hr_h2pi_element_entries_v%ROWTYPE;
3427
3428 l_index NUMBER;
3429 a_input_value_id iv_array := iv_array(NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3430 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3431 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3432 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3433 l_element_link_id pay_element_entries_f.element_link_id%TYPE;
3434 l_cost_allocation_keyflex_id
3435 pay_element_entries_f.cost_allocation_keyflex_id%TYPE;
3436 l_id_flex_num pay_cost_allocation_keyflex.id_flex_num%TYPE;
3437 l_ovn pay_element_entries_f.object_version_number%TYPE;
3438 l_esd pay_element_entries_f.effective_start_date%TYPE;
3439 l_eed pay_element_entries_f.effective_end_date%TYPE;
3440 l_uom pay_input_values_f.uom%TYPE;
3441
3442 l_max_eed pay_element_entries_f.effective_end_date%TYPE;
3443 l_del_ovn pay_element_entries_f.object_version_number%TYPE;
3444 l_del_esd pay_element_entries_f.effective_start_date%TYPE;
3445 l_del_eed pay_element_entries_f.effective_end_date%TYPE;
3446 l_val_esd pay_element_entries_f.effective_start_date%TYPE;
3447 l_val_eed pay_element_entries_f.effective_end_date%TYPE;
3448
3449 l_records_same BOOLEAN;
3450 l_future_records BOOLEAN;
3451 l_update_mode VARCHAR2(30);
3452 l_delete_mode VARCHAR2(30);
3453
3454 l_create_warning BOOLEAN;
3455 l_delete_warning BOOLEAN;
3456 l_update_warning BOOLEAN;
3457 l_ee_personal_pay_method_id pay_element_entries_f.personal_payment_method_id%TYPE;
3458
3459 BEGIN
3460 hr_utility.set_location('Entering:'|| l_proc, 10);
3461
3462 OPEN csr_ud_element_entry(p_element_entry_id,
3463 p_effective_start_date);
3464 FETCH csr_ud_element_entry INTO v_ud_ele;
3465
3466 IF v_ud_ele.last_upd_date = g_eot THEN
3467
3468 hr_utility.set_location(l_proc, 20);
3469 l_element_entry_id := hr_h2pi_map.get_to_id
3470 (p_table_name => 'PAY_ELEMENT_ENTRIES_F',
3471 p_from_id => v_ud_ele.element_entry_id,
3472 p_report_error => TRUE);
3473
3474 hr_utility.set_location(l_proc, 30);
3475 OPEN csr_ed_element_entry_ovn(l_element_entry_id,
3476 v_ud_ele.effective_start_date);
3477 FETCH csr_ed_element_entry_ovn
3478 INTO l_ovn;
3479
3480 IF csr_ed_element_entry_ovn%FOUND THEN
3481
3482 l_delete_mode := 'DELETE';
3483 py_element_entry_api.delete_element_entry(
3484 p_effective_date => v_ud_ele.effective_start_date-1
3485 ,p_datetrack_delete_mode => l_delete_mode
3486 ,p_element_entry_id => l_element_entry_id
3487 ,p_object_version_number => l_ovn
3488 ,p_effective_start_date => l_esd
3489 ,p_effective_end_date => l_eed
3490 ,p_delete_warning => l_delete_warning
3491 );
3492 END IF;
3493
3494 CLOSE csr_ed_element_entry_ovn;
3495
3496 ELSE
3497 l_index := 1;
3498 FOR v_ud_eev IN csr_ud_element_entry_value
3499 (v_ud_ele.element_entry_id,
3500 v_ud_ele.effective_start_date) LOOP
3501 a_input_value_id(l_index) := hr_h2pi_map.get_to_id
3502 (p_table_name => 'PAY_INPUT_VALUES_F',
3503 p_from_id => v_ud_eev.input_value_id,
3504 p_report_error => TRUE);
3505 BEGIN
3506 SELECT uom
3507 INTO l_uom
3508 FROM pay_input_values_f
3509 WHERE input_value_id = a_input_value_id(l_index)
3510 AND v_ud_ele.effective_start_date BETWEEN effective_start_date
3511 AND effective_end_date;
3512 EXCEPTION
3513 WHEN NO_DATA_FOUND THEN
3514 ROLLBACK;
3515 hr_utility.set_location(l_proc, 35);
3516 hr_h2pi_error.data_error
3517 (p_from_id => a_input_value_id(l_index),
3518 p_table_name => 'HR_H2PI_ELEMENT_ENTRY_VALUES',
3519 p_message_level => 'FATAL',
3520 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
3521 COMMIT;
3522 RAISE MAPPING_ID_INVALID;
3523 END;
3524
3525 IF l_uom = 'D' THEN
3526 a_ud_sev(l_index) := TO_CHAR(TRUNC(TO_DATE(v_ud_eev.screen_entry_value,
3527 'YYYY/MM/DD HH24:MI:SS')), 'DD-MON-YYYY');
3528 ELSE
3529 a_ud_sev(l_index) := v_ud_eev.screen_entry_value;
3530 END IF;
3531 l_index := l_index + 1;
3532 END LOOP;
3533
3534 hr_utility.set_location(l_proc, 40);
3535 l_assignment_id := hr_h2pi_map.get_to_id
3536 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
3537 p_from_id => v_ud_ele.assignment_id,
3538 p_report_error => TRUE);
3539
3540 l_element_entry_id := hr_h2pi_map.get_to_id
3541 (p_table_name => 'PAY_ELEMENT_ENTRIES_F',
3542 p_from_id => v_ud_ele.element_entry_id);
3543
3544 IF v_ud_ele.personal_payment_method_id IS NOT NULL THEN
3545 l_ee_personal_pay_method_id := hr_h2pi_map.get_to_id
3546 (p_table_name => 'PAY_PERSONAL_PAYMENT_METHODS_F',
3547 p_from_id => v_ud_ele.personal_payment_method_id);
3548 ELSE
3549 l_ee_personal_pay_method_id := NULL;
3550 END IF;
3551
3552 -- If no mapping found then set personal_payment_method_id to null
3553 IF l_ee_personal_pay_method_id = -1 THEN
3554 l_ee_personal_pay_method_id := NULL;
3555 END IF;
3556
3557 hr_utility.set_location('l_ee_personal_pay_method_id = '||
3558 to_char(l_ee_personal_pay_method_id),1010);
3559
3560 hr_utility.set_location('Getting cost_allocation_keyflex 1',1011);
3561
3562 -- Get id_flex_num using function
3563 l_id_flex_num := get_costing_id_flex_num;
3564
3565 /* l_id_flex_num := hr_h2pi_map.get_to_id
3566 (p_table_name => 'COST_ALLOCATION_KEYFLEX',
3567 p_from_id => v_ud_ele.id_flex_num);
3568 */
3569
3570 hr_utility.set_location('cost_allocation_keyflex 1' || l_id_flex_num, 1020);
3571
3572 IF l_element_entry_id = -1 THEN
3573 hr_utility.set_location(l_proc, 50);
3574 l_element_link_id := hr_h2pi_map.get_to_id
3575 (p_table_name => 'PAY_ELEMENT_LINKS_F',
3576 p_from_id => v_ud_ele.element_link_id,
3577 p_report_error => TRUE);
3578
3579 l_cost_allocation_keyflex_id := hr_entry.maintain_cost_keyflex(
3580 p_cost_keyflex_structure => l_id_flex_num
3581 ,p_cost_allocation_keyflex_id => -1
3582 ,p_concatenated_segments => v_ud_ele.concatenated_segments
3583 ,p_summary_flag => v_ud_ele.summary_flag
3584 ,p_start_date_active => v_ud_ele.start_date_active
3585 ,p_end_date_active => v_ud_ele.end_date_active
3586 ,p_segment1 => v_ud_ele.segment1
3587 ,p_segment2 => v_ud_ele.segment2
3588 ,p_segment3 => v_ud_ele.segment3
3589 ,p_segment4 => v_ud_ele.segment4
3590 ,p_segment5 => v_ud_ele.segment5
3591 ,p_segment6 => v_ud_ele.segment6
3592 ,p_segment7 => v_ud_ele.segment7
3593 ,p_segment8 => v_ud_ele.segment8
3594 ,p_segment9 => v_ud_ele.segment9
3595 ,p_segment10 => v_ud_ele.segment10
3596 ,p_segment11 => v_ud_ele.segment11
3597 ,p_segment12 => v_ud_ele.segment12
3598 ,p_segment13 => v_ud_ele.segment13
3599 ,p_segment14 => v_ud_ele.segment14
3600 ,p_segment15 => v_ud_ele.segment15
3601 ,p_segment16 => v_ud_ele.segment16
3602 ,p_segment17 => v_ud_ele.segment17
3603 ,p_segment18 => v_ud_ele.segment18
3604 ,p_segment19 => v_ud_ele.segment19
3605 ,p_segment20 => v_ud_ele.segment20
3606 ,p_segment21 => v_ud_ele.segment21
3607 ,p_segment22 => v_ud_ele.segment22
3608 ,p_segment23 => v_ud_ele.segment23
3609 ,p_segment24 => v_ud_ele.segment24
3610 ,p_segment25 => v_ud_ele.segment25
3611 ,p_segment26 => v_ud_ele.segment26
3612 ,p_segment27 => v_ud_ele.segment27
3613 ,p_segment28 => v_ud_ele.segment28
3614 ,p_segment29 => v_ud_ele.segment29
3615 ,p_segment30 => v_ud_ele.segment30
3616 );
3617
3618 py_element_entry_api.create_element_entry(
3619 p_effective_date => v_ud_ele.effective_start_date
3620 ,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
3621 ,p_assignment_id => l_assignment_id
3622 ,p_element_link_id => l_element_link_id
3623 ,p_entry_type => v_ud_ele.entry_type
3624 -- ,p_subpriority => v_ud_ele.subpriority
3625 ,p_date_earned => v_ud_ele.date_earned
3626 ,p_personal_payment_method_id => l_ee_personal_pay_method_id
3627 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
3628 ,p_attribute_category => v_ud_ele.attribute_category
3629 ,p_attribute1 => v_ud_ele.attribute1
3630 ,p_attribute2 => v_ud_ele.attribute2
3631 ,p_attribute3 => v_ud_ele.attribute3
3632 ,p_attribute4 => v_ud_ele.attribute4
3633 ,p_attribute5 => v_ud_ele.attribute5
3634 ,p_attribute6 => v_ud_ele.attribute6
3635 ,p_attribute7 => v_ud_ele.attribute7
3636 ,p_attribute8 => v_ud_ele.attribute8
3637 ,p_attribute9 => v_ud_ele.attribute9
3638 ,p_attribute10 => v_ud_ele.attribute10
3639 ,p_attribute11 => v_ud_ele.attribute11
3640 ,p_attribute12 => v_ud_ele.attribute12
3641 ,p_attribute13 => v_ud_ele.attribute13
3642 ,p_attribute14 => v_ud_ele.attribute14
3643 ,p_attribute15 => v_ud_ele.attribute15
3644 ,p_attribute16 => v_ud_ele.attribute16
3645 ,p_attribute17 => v_ud_ele.attribute17
3646 ,p_attribute18 => v_ud_ele.attribute18
3647 ,p_attribute19 => v_ud_ele.attribute19
3648 ,p_attribute20 => v_ud_ele.attribute20
3649 ,p_input_value_id1 => a_input_value_id(1)
3650 ,p_input_value_id2 => a_input_value_id(2)
3651 ,p_input_value_id3 => a_input_value_id(3)
3652 ,p_input_value_id4 => a_input_value_id(4)
3653 ,p_input_value_id5 => a_input_value_id(5)
3654 ,p_input_value_id6 => a_input_value_id(6)
3655 ,p_input_value_id7 => a_input_value_id(7)
3656 ,p_input_value_id8 => a_input_value_id(8)
3657 ,p_input_value_id9 => a_input_value_id(9)
3658 ,p_input_value_id10 => a_input_value_id(10)
3659 ,p_input_value_id11 => a_input_value_id(11)
3660 ,p_input_value_id12 => a_input_value_id(12)
3661 ,p_input_value_id13 => a_input_value_id(13)
3662 ,p_input_value_id14 => a_input_value_id(14)
3663 ,p_input_value_id15 => a_input_value_id(15)
3664 ,p_entry_value1 => a_ud_sev(1)
3665 ,p_entry_value2 => a_ud_sev(2)
3666 ,p_entry_value3 => a_ud_sev(3)
3667 ,p_entry_value4 => a_ud_sev(4)
3668 ,p_entry_value5 => a_ud_sev(5)
3669 ,p_entry_value6 => a_ud_sev(6)
3670 ,p_entry_value7 => a_ud_sev(7)
3671 ,p_entry_value8 => a_ud_sev(8)
3672 ,p_entry_value9 => a_ud_sev(9)
3673 ,p_entry_value10 => a_ud_sev(10)
3674 ,p_entry_value11 => a_ud_sev(11)
3675 ,p_entry_value12 => a_ud_sev(12)
3676 ,p_entry_value13 => a_ud_sev(13)
3677 ,p_entry_value14 => a_ud_sev(14)
3678 ,p_entry_value15 => a_ud_sev(15)
3679 ,p_effective_start_date => l_esd
3680 ,p_effective_end_date => l_eed
3681 ,p_element_entry_id => l_element_entry_id
3682 ,p_object_version_number => l_ovn
3683 ,p_create_warning => l_create_warning
3684 );
3685
3686 hr_utility.set_location(l_proc, 60);
3687 hr_h2pi_map.create_id_mapping
3688 (p_table_name => 'PAY_ELEMENT_ENTRIES_F',
3689 p_from_id => v_ud_ele.element_entry_id,
3690 p_to_id => l_element_entry_id);
3691
3692 ELSE
3693
3694 BEGIN
3695 hr_utility.set_location(l_proc, 70);
3696 OPEN csr_ed_element_entry(l_element_entry_id,
3697 v_ud_ele.effective_start_date);
3698 FETCH csr_ed_element_entry
3699 INTO v_ed_ele;
3700 IF csr_ed_element_entry%NOTFOUND THEN
3701 hr_utility.set_location(l_proc, 80);
3702 CLOSE csr_ed_element_entry;
3703 ROLLBACK;
3704 hr_utility.set_location(l_proc, 90);
3705 hr_h2pi_error.data_error
3706 (p_from_id => l_element_entry_id,
3707 p_table_name => 'HR_H2PI_ELEMENT_ENTRIES',
3708 p_message_level => 'FATAL',
3709 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
3710 COMMIT;
3711 RAISE MAPPING_ID_INVALID;
3712 ELSE
3713 CLOSE csr_ed_element_entry;
3714 END IF;
3715
3716 OPEN csr_ed_element_entry_ovn(l_element_entry_id,
3717 v_ud_ele.effective_start_date);
3718 FETCH csr_ed_element_entry_ovn
3719 INTO l_ovn;
3720 CLOSE csr_ed_element_entry_ovn;
3721 END;
3722
3723 l_delete_mode := 'DELETE_NEXT_CHANGE';
3724 LOOP
3725 hr_utility.set_location(l_proc, 100);
3726 l_records_same := FALSE;
3727
3728 SELECT MAX(ele.effective_end_date)
3729 INTO l_max_eed
3730 FROM pay_element_entries_f ele
3731 WHERE ele.element_entry_id = l_element_entry_id;
3732
3733 IF l_max_eed > v_ed_ele.effective_end_date THEN
3734 hr_utility.set_location(l_proc, 110);
3735 l_future_records := TRUE;
3736 ELSE
3737 hr_utility.set_location(l_proc, 120);
3738 l_future_records := FALSE;
3739 END IF;
3740
3741 calculate_datetrack_mode
3742 (p_ud_start_date => v_ud_ele.effective_start_date
3743 ,p_ud_end_date => v_ud_ele.effective_end_date
3744 ,p_ed_start_date => v_ed_ele.effective_start_date
3745 ,p_ed_end_date => v_ed_ele.effective_end_date
3746 ,p_records_same => l_records_same
3747 ,p_future_records => l_future_records
3748 ,p_update_mode => l_update_mode
3749 ,p_delete_mode => l_delete_mode);
3750
3751 EXIT WHEN l_delete_mode = 'X';
3752 hr_utility.set_location(l_proc, 130);
3753
3754 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
3755
3756 hr_utility.set_location(l_proc, 140);
3757 py_element_entry_api.delete_element_entry(
3758 p_effective_date => v_ud_ele.effective_start_date
3759 ,p_datetrack_delete_mode => l_delete_mode
3760 ,p_element_entry_id => l_element_entry_id
3761 ,p_object_version_number => l_ovn
3762 ,p_effective_start_date => l_esd
3763 ,p_effective_end_date => l_eed
3764 ,p_delete_warning => l_delete_warning
3765 );
3766
3767 hr_utility.set_location(l_proc, 150);
3768 OPEN csr_ed_element_entry(l_element_entry_id,
3769 v_ud_ele.effective_start_date);
3770 FETCH csr_ed_element_entry
3771 INTO v_ed_ele;
3772 CLOSE csr_ed_element_entry;
3773
3774 END IF;
3775
3776 END LOOP;
3777
3778 hr_utility.set_location(l_proc, 160);
3779 l_cost_allocation_keyflex_id := hr_entry.maintain_cost_keyflex(
3780 p_cost_keyflex_structure => l_id_flex_num
3781 ,p_cost_allocation_keyflex_id => -1
3782 ,p_concatenated_segments => v_ud_ele.concatenated_segments
3783 ,p_summary_flag => v_ud_ele.summary_flag
3784 ,p_start_date_active => v_ud_ele.start_date_active
3785 ,p_end_date_active => v_ud_ele.end_date_active
3786 ,p_segment1 => v_ud_ele.segment1
3787 ,p_segment2 => v_ud_ele.segment2
3788 ,p_segment3 => v_ud_ele.segment3
3789 ,p_segment4 => v_ud_ele.segment4
3790 ,p_segment5 => v_ud_ele.segment5
3791 ,p_segment6 => v_ud_ele.segment6
3792 ,p_segment7 => v_ud_ele.segment7
3793 ,p_segment8 => v_ud_ele.segment8
3794 ,p_segment9 => v_ud_ele.segment9
3795 ,p_segment10 => v_ud_ele.segment10
3796 ,p_segment11 => v_ud_ele.segment11
3797 ,p_segment12 => v_ud_ele.segment12
3798 ,p_segment13 => v_ud_ele.segment13
3799 ,p_segment14 => v_ud_ele.segment14
3800 ,p_segment15 => v_ud_ele.segment15
3801 ,p_segment16 => v_ud_ele.segment16
3802 ,p_segment17 => v_ud_ele.segment17
3803 ,p_segment18 => v_ud_ele.segment18
3804 ,p_segment19 => v_ud_ele.segment19
3805 ,p_segment20 => v_ud_ele.segment20
3806 ,p_segment21 => v_ud_ele.segment21
3807 ,p_segment22 => v_ud_ele.segment22
3808 ,p_segment23 => v_ud_ele.segment23
3809 ,p_segment24 => v_ud_ele.segment24
3810 ,p_segment25 => v_ud_ele.segment25
3811 ,p_segment26 => v_ud_ele.segment26
3812 ,p_segment27 => v_ud_ele.segment27
3813 ,p_segment28 => v_ud_ele.segment28
3814 ,p_segment29 => v_ud_ele.segment29
3815 ,p_segment30 => v_ud_ele.segment30
3816 );
3817
3818 py_element_entry_api.update_element_entry(
3819 p_datetrack_update_mode => l_update_mode
3820 ,p_effective_date => v_ud_ele.effective_start_date
3821 ,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
3822 ,p_element_entry_id => l_element_entry_id
3823 ,p_object_version_number => l_ovn
3824 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
3825 -- ,p_subpriority => v_ud_ele.subpriority
3826 ,p_date_earned => v_ud_ele.date_earned
3827 ,p_personal_payment_method_id => l_ee_personal_pay_method_id
3828 ,p_attribute_category => v_ud_ele.attribute_category
3829 ,p_attribute1 => v_ud_ele.attribute1
3830 ,p_attribute2 => v_ud_ele.attribute2
3831 ,p_attribute3 => v_ud_ele.attribute3
3832 ,p_attribute4 => v_ud_ele.attribute4
3833 ,p_attribute5 => v_ud_ele.attribute5
3834 ,p_attribute6 => v_ud_ele.attribute6
3835 ,p_attribute7 => v_ud_ele.attribute7
3836 ,p_attribute8 => v_ud_ele.attribute8
3837 ,p_attribute9 => v_ud_ele.attribute9
3838 ,p_attribute10 => v_ud_ele.attribute10
3839 ,p_attribute11 => v_ud_ele.attribute11
3840 ,p_attribute12 => v_ud_ele.attribute12
3841 ,p_attribute13 => v_ud_ele.attribute13
3842 ,p_attribute14 => v_ud_ele.attribute14
3843 ,p_attribute15 => v_ud_ele.attribute15
3844 ,p_attribute16 => v_ud_ele.attribute16
3845 ,p_attribute17 => v_ud_ele.attribute17
3846 ,p_attribute18 => v_ud_ele.attribute18
3847 ,p_attribute19 => v_ud_ele.attribute19
3848 ,p_attribute20 => v_ud_ele.attribute20
3849 ,p_input_value_id1 => a_input_value_id(1)
3850 ,p_input_value_id2 => a_input_value_id(2)
3851 ,p_input_value_id3 => a_input_value_id(3)
3852 ,p_input_value_id4 => a_input_value_id(4)
3853 ,p_input_value_id5 => a_input_value_id(5)
3854 ,p_input_value_id6 => a_input_value_id(6)
3855 ,p_input_value_id7 => a_input_value_id(7)
3856 ,p_input_value_id8 => a_input_value_id(8)
3857 ,p_input_value_id9 => a_input_value_id(9)
3858 ,p_input_value_id10 => a_input_value_id(10)
3859 ,p_input_value_id11 => a_input_value_id(11)
3860 ,p_input_value_id12 => a_input_value_id(12)
3861 ,p_input_value_id13 => a_input_value_id(13)
3862 ,p_input_value_id14 => a_input_value_id(14)
3863 ,p_input_value_id15 => a_input_value_id(15)
3864 ,p_entry_value1 => a_ud_sev(1)
3865 ,p_entry_value2 => a_ud_sev(2)
3866 ,p_entry_value3 => a_ud_sev(3)
3867 ,p_entry_value4 => a_ud_sev(4)
3868 ,p_entry_value5 => a_ud_sev(5)
3869 ,p_entry_value6 => a_ud_sev(6)
3870 ,p_entry_value7 => a_ud_sev(7)
3871 ,p_entry_value8 => a_ud_sev(8)
3872 ,p_entry_value9 => a_ud_sev(9)
3873 ,p_entry_value10 => a_ud_sev(10)
3874 ,p_entry_value11 => a_ud_sev(11)
3875 ,p_entry_value12 => a_ud_sev(12)
3876 ,p_entry_value13 => a_ud_sev(13)
3877 ,p_entry_value14 => a_ud_sev(14)
3878 ,p_entry_value15 => a_ud_sev(15)
3879 ,p_effective_start_date => l_esd
3880 ,p_effective_end_date => l_eed
3881 ,p_update_warning => l_update_warning
3882 );
3883
3884
3885 END IF;
3886 END IF;
3887
3888 hr_utility.set_location(l_proc, 170);
3889 UPDATE hr_h2pi_element_entries ele
3890 SET status = 'C'
3891 WHERE ele.element_entry_id = v_ud_ele.element_entry_id
3892 AND ele.client_id = p_from_client_id
3893 AND ele.effective_start_date = v_ud_ele.effective_start_date
3894 AND ele.effective_end_date = v_ud_ele.effective_end_date;
3895
3896 UPDATE hr_h2pi_element_entry_values eev
3897 SET status = 'C'
3898 WHERE eev.element_entry_id = v_ud_ele.element_entry_id
3899 AND eev.client_id = p_from_client_id
3900 AND eev.effective_start_date = v_ud_ele.effective_start_date
3901 AND eev.effective_end_date = v_ud_ele.effective_end_date;
3902
3903 CLOSE csr_ud_element_entry;
3904 hr_utility.set_location('Leaving:'|| l_proc, 180);
3905 COMMIT;
3906
3907 EXCEPTION
3908 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
3909 ROLLBACK;
3910 hr_utility.set_location(l_proc, 190);
3911 l_encoded_message := fnd_message.get_encoded;
3912 hr_h2pi_error.data_error
3913 (p_from_id => v_ud_ele.element_entry_id,
3914 p_table_name => 'HR_H2PI_ELEMENT_ENTRIES',
3915 p_message_level => 'FATAL',
3916 p_message_text => l_encoded_message);
3917 COMMIT;
3918 RAISE;
3919 END;
3920
3921
3922
3923 PROCEDURE upload_federal_tax_record (p_from_client_id NUMBER,
3924 p_emp_fed_tax_rule_id NUMBER,
3925 p_effective_start_date DATE) IS
3926
3927 CURSOR csr_ud_federal_tax_rule (p_fed_id NUMBER,
3928 p_esd DATE) IS
3929 SELECT *
3930 FROM hr_h2pi_federal_tax_rules fed
3931 WHERE fed.emp_fed_tax_rule_id = p_fed_id
3932 AND fed.client_id = p_from_client_id
3933 AND fed.effective_start_date = p_esd;
3934
3935 CURSOR csr_ed_federal_tax_rule (p_fed_id NUMBER,
3936 p_esd DATE) IS
3937 SELECT *
3938 FROM hr_h2pi_federal_tax_rules_v fed
3939 WHERE fed.emp_fed_tax_rule_id = p_fed_id
3940 AND p_esd BETWEEN fed.effective_start_date
3941 AND fed.effective_end_date;
3942
3943 CURSOR csr_ed_federal_tax_rule_ovn (p_fed_id NUMBER,
3944 p_esd DATE) IS
3945 SELECT fed.object_version_number
3946 FROM pay_us_emp_fed_tax_rules_f fed
3947 WHERE fed.emp_fed_tax_rule_id = p_fed_id
3948 AND p_esd BETWEEN fed.effective_start_date
3949 AND fed.effective_end_date;
3950
3951 l_encoded_message VARCHAR2(200);
3952
3953 l_proc VARCHAR2(72) := g_package||'upload_federal_tax_rule';
3954
3955 v_ud_fed hr_h2pi_federal_tax_rules%ROWTYPE;
3956 v_ed_fed hr_h2pi_federal_tax_rules_v%ROWTYPE;
3957
3958 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3959 l_emp_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
3960 l_ovn pay_us_emp_fed_tax_rules_f.object_version_number%TYPE;
3961 l_esd pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
3962 l_eed pay_us_emp_fed_tax_rules_f.effective_end_date%TYPE;
3963
3964 l_max_eed pay_us_emp_fed_tax_rules_f.effective_end_date%TYPE;
3965 l_del_ovn pay_us_emp_fed_tax_rules_f.object_version_number%TYPE;
3966 l_del_esd pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
3967 l_del_eed pay_us_emp_fed_tax_rules_f.effective_end_date%TYPE;
3968 l_val_esd pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
3969 l_val_eed pay_us_emp_fed_tax_rules_f.effective_end_date%TYPE;
3970 l_business_group_id pay_us_emp_fed_tax_rules_f.business_group_id%TYPE;
3971
3972 l_records_same BOOLEAN;
3973 l_future_records BOOLEAN;
3974 l_update_mode VARCHAR2(30);
3975 l_delete_mode VARCHAR2(30);
3976
3977 BEGIN
3978 hr_utility.set_location('Entering:'|| l_proc, 10);
3979
3980 OPEN csr_ud_federal_tax_rule(p_emp_fed_tax_rule_id,
3981 p_effective_start_date);
3982 FETCH csr_ud_federal_tax_rule INTO v_ud_fed;
3983
3984 hr_utility.set_location(l_proc, 40);
3985 l_emp_fed_tax_rule_id := hr_h2pi_map.get_to_id
3986 (p_table_name => 'PAY_US_EMP_FED_TAX_RULES_F',
3987 p_from_id => v_ud_fed.emp_fed_tax_rule_id,
3988 p_report_error => TRUE);
3989
3990
3991 BEGIN
3992 hr_utility.set_location(l_proc, 70);
3993 OPEN csr_ed_federal_tax_rule(l_emp_fed_tax_rule_id,
3994 v_ud_fed.effective_start_date);
3995 FETCH csr_ed_federal_tax_rule
3996 INTO v_ed_fed;
3997 IF csr_ed_federal_tax_rule%NOTFOUND THEN
3998 hr_utility.set_location(l_proc, 80);
3999 CLOSE csr_ed_federal_tax_rule;
4000 ROLLBACK;
4001 hr_utility.set_location(l_proc, 90);
4002 hr_h2pi_error.data_error
4003 (p_from_id => l_emp_fed_tax_rule_id,
4004 p_table_name => 'HR_H2PI_FEDERAL_TAX_RULES',
4005 p_message_level => 'FATAL',
4006 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
4007 COMMIT;
4008 RAISE MAPPING_ID_INVALID;
4009 ELSE
4010 CLOSE csr_ed_federal_tax_rule;
4011 END IF;
4012
4013 OPEN csr_ed_federal_tax_rule_ovn(l_emp_fed_tax_rule_id,
4014 v_ud_fed.effective_start_date);
4015 FETCH csr_ed_federal_tax_rule_ovn
4016 INTO l_ovn;
4017 CLOSE csr_ed_federal_tax_rule_ovn;
4018 END;
4019
4020 l_delete_mode := 'DELETE_NEXT_CHANGE';
4021 LOOP
4022 hr_utility.set_location(l_proc, 100);
4023 l_records_same := FALSE;
4024
4025 SELECT MAX(fed.effective_end_date)
4026 INTO l_max_eed
4027 FROM pay_us_emp_fed_tax_rules_f fed
4028 WHERE fed.emp_fed_tax_rule_id = l_emp_fed_tax_rule_id;
4029
4030 IF l_max_eed > v_ed_fed.effective_end_date THEN
4031 hr_utility.set_location(l_proc, 110);
4032 l_future_records := TRUE;
4033 ELSE
4034 hr_utility.set_location(l_proc, 120);
4035 l_future_records := FALSE;
4036 END IF;
4037
4038 calculate_datetrack_mode
4039 (p_ud_start_date => v_ud_fed.effective_start_date
4040 ,p_ud_end_date => v_ud_fed.effective_end_date
4041 ,p_ed_start_date => v_ed_fed.effective_start_date
4042 ,p_ed_end_date => v_ed_fed.effective_end_date
4043 ,p_records_same => l_records_same
4044 ,p_future_records => l_future_records
4045 ,p_update_mode => l_update_mode
4046 ,p_delete_mode => l_delete_mode);
4047
4048 EXIT WHEN l_delete_mode = 'X';
4049 hr_utility.set_location(l_proc, 130);
4050
4051 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
4052
4053 hr_utility.set_location(l_proc, 140);
4054 pay_federal_tax_rule_api.update_fed_tax_rule(
4055 p_effective_date => v_ud_fed.effective_start_date
4056 ,p_datetrack_update_mode => 'UPDATE_OVERRIDE'
4057 ,p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id
4058 ,p_object_version_number => l_ovn
4059 ,p_sui_state_code => v_ud_fed.sui_state_code
4060 ,p_additional_wa_amount => v_ud_fed.additional_wa_amount
4061 ,p_filing_status_code => v_ud_fed.filing_status_code
4062 ,p_fit_override_amount => v_ud_fed.fit_override_amount
4063 ,p_fit_override_rate => v_ud_fed.fit_override_rate
4064 ,p_withholding_allowances => v_ud_fed.withholding_allowances
4065 ,p_cumulative_taxation => v_ud_fed.cumulative_taxation
4066 ,p_eic_filing_status_code => v_ud_fed.eic_filing_status_code
4067 ,p_fit_additional_tax => v_ud_fed.fit_additional_tax
4068 ,p_fit_exempt => v_ud_fed.fit_exempt
4069 ,p_futa_tax_exempt => v_ud_fed.futa_tax_exempt
4070 ,p_medicare_tax_exempt => v_ud_fed.medicare_tax_exempt
4071 ,p_ss_tax_exempt => v_ud_fed.ss_tax_exempt
4072 ,p_statutory_employee => v_ud_fed.statutory_employee
4073 ,p_w2_filed_year => v_ud_fed.w2_filed_year
4074 ,p_supp_tax_override_rate => v_ud_fed.supp_tax_override_rate
4075 ,p_excessive_wa_reject_date => v_ud_fed.excessive_wa_reject_date
4076 ,p_effective_start_date => l_esd
4077 ,p_effective_end_date => l_eed
4078 );
4079 /*
4080 pay_fed_del.del(
4081 p_effective_date => v_ud_fed.effective_start_date
4082 ,p_datetrack_mode => l_delete_mode
4083 ,p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id
4084 ,p_object_version_number => l_ovn
4085 ,p_effective_start_date => l_esd
4086 ,p_effective_end_date => l_eed
4087 ,p_delete_routine => NULL
4088 );
4089 */
4090 hr_utility.set_location(l_proc, 150);
4091 OPEN csr_ed_federal_tax_rule(l_emp_fed_tax_rule_id,
4092 v_ud_fed.effective_start_date);
4093 FETCH csr_ed_federal_tax_rule
4094 INTO v_ed_fed;
4095 CLOSE csr_ed_federal_tax_rule;
4096
4097 END IF;
4098
4099 END LOOP;
4100
4101 hr_utility.set_location(l_proc, 160);
4102 pay_federal_tax_rule_api.update_fed_tax_rule(
4103 p_effective_date => v_ud_fed.effective_start_date
4104 ,p_datetrack_update_mode => l_update_mode
4105 ,p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id
4106 ,p_object_version_number => l_ovn
4107 ,p_sui_state_code => v_ud_fed.sui_state_code
4108 ,p_additional_wa_amount => v_ud_fed.additional_wa_amount
4109 ,p_filing_status_code => v_ud_fed.filing_status_code
4110 ,p_fit_override_amount => v_ud_fed.fit_override_amount
4111 ,p_fit_override_rate => v_ud_fed.fit_override_rate
4112 ,p_withholding_allowances => v_ud_fed.withholding_allowances
4113 ,p_cumulative_taxation => v_ud_fed.cumulative_taxation
4114 ,p_eic_filing_status_code => v_ud_fed.eic_filing_status_code
4115 ,p_fit_additional_tax => v_ud_fed.fit_additional_tax
4116 ,p_fit_exempt => v_ud_fed.fit_exempt
4117 ,p_futa_tax_exempt => v_ud_fed.futa_tax_exempt
4118 ,p_medicare_tax_exempt => v_ud_fed.medicare_tax_exempt
4119 ,p_ss_tax_exempt => v_ud_fed.ss_tax_exempt
4120 ,p_statutory_employee => v_ud_fed.statutory_employee
4121 ,p_w2_filed_year => v_ud_fed.w2_filed_year
4122 ,p_supp_tax_override_rate => v_ud_fed.supp_tax_override_rate
4123 ,p_excessive_wa_reject_date => v_ud_fed.excessive_wa_reject_date
4124 ,p_effective_start_date => l_esd
4125 ,p_effective_end_date => l_eed
4126 );
4127
4128 hr_utility.set_location(l_proc, 170);
4129 UPDATE hr_h2pi_federal_tax_rules fed
4130 SET status = 'C'
4131 WHERE fed.emp_fed_tax_rule_id = v_ud_fed.emp_fed_tax_rule_id
4132 AND fed.client_id = p_from_client_id
4133 AND fed.effective_start_date = v_ud_fed.effective_start_date
4134 AND fed.effective_end_date = v_ud_fed.effective_end_date;
4135
4136 CLOSE csr_ud_federal_tax_rule;
4137 hr_utility.set_location('Leaving:'|| l_proc, 180);
4138 COMMIT;
4139
4140 EXCEPTION
4141 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
4142 ROLLBACK;
4143 hr_utility.set_location(l_proc, 190);
4144 l_encoded_message := fnd_message.get_encoded;
4145 hr_h2pi_error.data_error
4146 (p_from_id => v_ud_fed.emp_fed_tax_rule_id,
4147 p_table_name => 'HR_H2PI_FEDERAL_TAX_RULES',
4148 p_message_level => 'FATAL',
4149 p_message_text => l_encoded_message);
4150 COMMIT;
4151 RAISE;
4152
4153 END;
4154
4155
4156
4157 PROCEDURE upload_state_tax_record (p_from_client_id NUMBER,
4158 p_emp_state_tax_rule_id NUMBER,
4159 p_effective_start_date DATE) IS
4160
4161 CURSOR csr_ud_state_tax_rule (p_sta_id NUMBER,
4162 p_esd DATE) IS
4163 SELECT *
4164 FROM hr_h2pi_state_tax_rules sta
4165 WHERE sta.emp_state_tax_rule_id = p_sta_id
4166 AND sta.client_id = p_from_client_id
4167 AND sta.effective_start_date = p_esd;
4168
4169 CURSOR csr_ed_state_tax_rule (p_sta_id NUMBER,
4170 p_esd DATE) IS
4171 SELECT *
4172 FROM hr_h2pi_state_tax_rules_v sta
4173 WHERE sta.emp_state_tax_rule_id = p_sta_id
4174 AND p_esd BETWEEN sta.effective_start_date
4175 AND sta.effective_end_date;
4176
4177 CURSOR csr_ed_state_tax_rule_ovn (p_sta_id NUMBER,
4178 p_esd DATE) IS
4179 SELECT sta.object_version_number
4180 FROM pay_us_emp_state_tax_rules_f sta
4181 WHERE sta.emp_state_tax_rule_id = p_sta_id
4182 AND p_esd BETWEEN sta.effective_start_date
4183 AND sta.effective_end_date;
4184
4185 l_encoded_message VARCHAR2(200);
4186
4187 l_proc VARCHAR2(72) := g_package||'upload_state_tax_rule';
4188
4189 v_ud_sta hr_h2pi_state_tax_rules%ROWTYPE;
4190 v_ed_sta hr_h2pi_state_tax_rules_v%ROWTYPE;
4191
4192 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
4193 l_emp_state_tax_rule_id
4194 pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
4195 l_ovn pay_us_emp_state_tax_rules_f.object_version_number%TYPE;
4196 l_esd pay_us_emp_state_tax_rules_f.effective_start_date%TYPE;
4197 l_eed pay_us_emp_state_tax_rules_f.effective_end_date%TYPE;
4198
4199 l_max_eed pay_us_emp_state_tax_rules_f.effective_end_date%TYPE;
4200 l_del_ovn pay_us_emp_state_tax_rules_f.object_version_number%TYPE;
4201 l_del_esd pay_us_emp_state_tax_rules_f.effective_start_date%TYPE;
4202 l_del_eed pay_us_emp_state_tax_rules_f.effective_end_date%TYPE;
4203 l_val_esd pay_us_emp_state_tax_rules_f.effective_start_date%TYPE;
4204 l_val_eed pay_us_emp_state_tax_rules_f.effective_end_date%TYPE;
4205 l_business_group_id pay_us_emp_state_tax_rules_f.business_group_id%TYPE;
4206
4207 l_records_same BOOLEAN;
4208 l_future_records BOOLEAN;
4209 l_update_mode VARCHAR2(30);
4210 l_delete_mode VARCHAR2(30);
4211
4212 BEGIN
4213 hr_utility.set_location('Entering:'|| l_proc, 10);
4214
4215 OPEN csr_ud_state_tax_rule(p_emp_state_tax_rule_id,
4216 p_effective_start_date);
4217 FETCH csr_ud_state_tax_rule INTO v_ud_sta;
4218
4219 hr_utility.set_location(l_proc, 20);
4220 l_assignment_id := hr_h2pi_map.get_to_id
4221 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
4222 p_from_id => v_ud_sta.assignment_id,
4223 p_report_error => TRUE);
4224
4225 l_emp_state_tax_rule_id := hr_h2pi_map.get_to_id
4226 (p_table_name => 'PAY_US_EMP_STATE_TAX_RULES_F',
4227 p_from_id => v_ud_sta.emp_state_tax_rule_id);
4228
4229 IF l_emp_state_tax_rule_id = -1 THEN
4230 hr_utility.set_location(l_proc, 30);
4231
4232 pay_state_tax_rule_api.create_state_tax_rule(
4233 p_effective_date => v_ud_sta.effective_start_date
4234 ,p_assignment_id => l_assignment_id
4235 ,p_state_code => v_ud_sta.state_code
4236 ,p_additional_wa_amount => v_ud_sta.additional_wa_amount
4237 ,p_filing_status_code => v_ud_sta.filing_status_code
4238 ,p_remainder_percent => v_ud_sta.remainder_percent
4239 ,p_secondary_wa => v_ud_sta.secondary_wa
4240 ,p_sit_additional_tax => v_ud_sta.sit_additional_tax
4241 ,p_sit_override_amount => v_ud_sta.sit_override_amount
4242 ,p_sit_override_rate => v_ud_sta.sit_override_rate
4243 ,p_withholding_allowances => v_ud_sta.withholding_allowances
4244 ,p_excessive_wa_reject_date => v_ud_sta.excessive_wa_reject_date
4245 ,p_sdi_exempt => v_ud_sta.sdi_exempt
4246 ,p_sit_exempt => v_ud_sta.sit_exempt
4247 ,p_sit_optional_calc_ind => v_ud_sta.sit_optional_calc_ind
4248 ,p_state_non_resident_cert => v_ud_sta.state_non_resident_cert
4249 ,p_sui_exempt => v_ud_sta.sui_exempt
4250 ,p_wc_exempt => v_ud_sta.wc_exempt
4251 ,p_sui_wage_base_override_amoun =>
4252 v_ud_sta.sui_wage_base_override_amount
4253 ,p_supp_tax_override_rate => v_ud_sta.supp_tax_override_rate
4254 ,p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
4255 ,p_object_version_number => l_ovn
4256 ,p_effective_start_date => l_esd
4257 ,p_effective_end_date => l_eed
4258 );
4259
4260 hr_utility.set_location(l_proc, 40);
4261 hr_h2pi_map.create_id_mapping
4262 (p_table_name => 'PAY_US_EMP_STATE_TAX_RULES_F',
4263 p_from_id => v_ud_sta.emp_state_tax_rule_id,
4264 p_to_id => l_emp_state_tax_rule_id);
4265 ELSE
4266
4267 BEGIN
4268 hr_utility.set_location(l_proc, 50);
4269 OPEN csr_ed_state_tax_rule(l_emp_state_tax_rule_id,
4270 v_ud_sta.effective_start_date);
4271 FETCH csr_ed_state_tax_rule
4272 INTO v_ed_sta;
4273 IF csr_ed_state_tax_rule%NOTFOUND THEN
4274 hr_utility.set_location(l_proc, 60);
4275 CLOSE csr_ed_state_tax_rule;
4276 ROLLBACK;
4277 hr_utility.set_location(l_proc, 70);
4278 hr_h2pi_error.data_error
4279 (p_from_id => l_emp_state_tax_rule_id,
4280 p_table_name => 'HR_H2PI_STATE_TAX_RULES',
4281 p_message_level => 'FATAL',
4282 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
4283 COMMIT;
4284 RAISE MAPPING_ID_INVALID;
4285 ELSE
4286 CLOSE csr_ed_state_tax_rule;
4287 END IF;
4288
4289 OPEN csr_ed_state_tax_rule_ovn(l_emp_state_tax_rule_id,
4290 v_ud_sta.effective_start_date);
4291 FETCH csr_ed_state_tax_rule_ovn
4292 INTO l_ovn;
4293 CLOSE csr_ed_state_tax_rule_ovn;
4294 END;
4295
4296 l_delete_mode := 'DELETE_NEXT_CHANGE';
4297 LOOP
4298 hr_utility.set_location(l_proc, 80);
4299 l_records_same := FALSE;
4300
4301 SELECT MAX(sta.effective_end_date)
4302 INTO l_max_eed
4303 FROM pay_us_emp_state_tax_rules_f sta
4304 WHERE sta.emp_state_tax_rule_id = l_emp_state_tax_rule_id;
4305
4306 IF l_max_eed > v_ed_sta.effective_end_date THEN
4307 hr_utility.set_location(l_proc, 90);
4308 l_future_records := TRUE;
4309 ELSE
4310 hr_utility.set_location(l_proc, 100);
4311 l_future_records := FALSE;
4312 END IF;
4313
4314 calculate_datetrack_mode
4315 (p_ud_start_date => v_ud_sta.effective_start_date
4316 ,p_ud_end_date => v_ud_sta.effective_end_date
4317 ,p_ed_start_date => v_ed_sta.effective_start_date
4318 ,p_ed_end_date => v_ed_sta.effective_end_date
4319 ,p_records_same => l_records_same
4320 ,p_future_records => l_future_records
4321 ,p_update_mode => l_update_mode
4322 ,p_delete_mode => l_delete_mode);
4323
4324 EXIT WHEN l_delete_mode = 'X';
4325 hr_utility.set_location(l_proc, 110);
4326
4327 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
4328
4329 hr_utility.set_location(l_proc, 120);
4330 pay_state_tax_rule_api.update_state_tax_rule(
4331 p_effective_date => v_ud_sta.effective_start_date
4332 ,p_datetrack_update_mode => 'UPDATE_OVERRIDE'
4333 ,p_object_version_number => l_ovn
4334 ,p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
4335 ,p_additional_wa_amount => v_ud_sta.additional_wa_amount
4336 ,p_filing_status_code => v_ud_sta.filing_status_code
4337 ,p_remainder_percent => v_ud_sta.remainder_percent
4338 ,p_secondary_wa => v_ud_sta.secondary_wa
4339 ,p_sit_additional_tax => v_ud_sta.sit_additional_tax
4340 ,p_sit_override_amount => v_ud_sta.sit_override_amount
4341 ,p_sit_override_rate => v_ud_sta.sit_override_rate
4342 ,p_withholding_allowances => v_ud_sta.withholding_allowances
4343 ,p_excessive_wa_reject_date => v_ud_sta.excessive_wa_reject_date
4344 ,p_sdi_exempt => v_ud_sta.sdi_exempt
4345 ,p_sit_exempt => v_ud_sta.sit_exempt
4346 ,p_sit_optional_calc_ind => v_ud_sta.sit_optional_calc_ind
4347 ,p_state_non_resident_cert => v_ud_sta.state_non_resident_cert
4348 ,p_sui_exempt => v_ud_sta.sui_exempt
4349 ,p_wc_exempt => v_ud_sta.wc_exempt
4350 ,p_sui_wage_base_override_amoun
4351 => v_ud_sta.sui_wage_base_override_amount
4352 ,p_supp_tax_override_rate => v_ud_sta.supp_tax_override_rate
4353 ,p_effective_start_date => l_esd
4354 ,p_effective_end_date => l_eed
4355 );
4356 /*
4357 pay_sta_del.del(
4358 p_effective_date => v_ud_sta.effective_start_date
4359 ,p_datetrack_mode => l_delete_mode
4360 ,p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
4361 ,p_object_version_number => l_ovn
4362 ,p_effective_start_date => l_esd
4363 ,p_effective_end_date => l_eed
4364 ,p_delete_routine => NULL
4365 );
4366 */
4367
4368 hr_utility.set_location(l_proc, 130);
4369 OPEN csr_ed_state_tax_rule(l_emp_state_tax_rule_id,
4370 v_ud_sta.effective_start_date);
4371 FETCH csr_ed_state_tax_rule
4372 INTO v_ed_sta;
4373 CLOSE csr_ed_state_tax_rule;
4374
4375 END IF;
4376
4377 END LOOP;
4378
4379 hr_utility.set_location(l_proc, 140);
4380 pay_state_tax_rule_api.update_state_tax_rule(
4381 p_effective_date => v_ud_sta.effective_start_date
4382 ,p_datetrack_update_mode => l_update_mode
4383 ,p_object_version_number => l_ovn
4384 ,p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
4385 ,p_additional_wa_amount => v_ud_sta.additional_wa_amount
4386 ,p_filing_status_code => v_ud_sta.filing_status_code
4387 ,p_remainder_percent => v_ud_sta.remainder_percent
4388 ,p_secondary_wa => v_ud_sta.secondary_wa
4389 ,p_sit_additional_tax => v_ud_sta.sit_additional_tax
4390 ,p_sit_override_amount => v_ud_sta.sit_override_amount
4391 ,p_sit_override_rate => v_ud_sta.sit_override_rate
4392 ,p_withholding_allowances => v_ud_sta.withholding_allowances
4393 ,p_excessive_wa_reject_date => v_ud_sta.excessive_wa_reject_date
4394 ,p_sdi_exempt => v_ud_sta.sdi_exempt
4395 ,p_sit_exempt => v_ud_sta.sit_exempt
4396 ,p_sit_optional_calc_ind => v_ud_sta.sit_optional_calc_ind
4397 ,p_state_non_resident_cert => v_ud_sta.state_non_resident_cert
4398 ,p_sui_exempt => v_ud_sta.sui_exempt
4399 ,p_wc_exempt => v_ud_sta.wc_exempt
4400 ,p_sui_wage_base_override_amoun
4401 => v_ud_sta.sui_wage_base_override_amount
4402 ,p_supp_tax_override_rate => v_ud_sta.supp_tax_override_rate
4403 ,p_effective_start_date => l_esd
4404 ,p_effective_end_date => l_eed
4405 );
4406
4407 END IF;
4408
4409 hr_utility.set_location(l_proc, 150);
4410 UPDATE hr_h2pi_state_tax_rules sta
4411 SET status = 'C'
4412 WHERE sta.emp_state_tax_rule_id = v_ud_sta.emp_state_tax_rule_id
4413 AND sta.client_id = p_from_client_id
4414 AND sta.effective_start_date = v_ud_sta.effective_start_date
4415 AND sta.effective_end_date = v_ud_sta.effective_end_date;
4416
4417 CLOSE csr_ud_state_tax_rule;
4418 hr_utility.set_location('Leaving:'|| l_proc, 160);
4419 COMMIT;
4420
4421 EXCEPTION
4422 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
4423 ROLLBACK;
4424 hr_utility.set_location(l_proc, 170);
4425 l_encoded_message := fnd_message.get_encoded;
4426 hr_h2pi_error.data_error
4427 (p_from_id => v_ud_sta.emp_state_tax_rule_id,
4428 p_table_name => 'HR_H2PI_STATE_TAX_RULES',
4429 p_message_level => 'FATAL',
4430 p_message_text => l_encoded_message);
4431 COMMIT;
4432 RAISE;
4433
4434 END;
4435
4436
4437
4438 PROCEDURE upload_county_tax_record (p_from_client_id NUMBER,
4439 p_emp_county_tax_rule_id NUMBER,
4440 p_effective_start_date DATE) IS
4441
4442 CURSOR csr_ud_county_tax_rule (p_cnt_id NUMBER,
4443 p_esd DATE) IS
4444 SELECT *
4445 FROM hr_h2pi_county_tax_rules cnt
4446 WHERE cnt.emp_county_tax_rule_id = p_cnt_id
4447 AND cnt.client_id = p_from_client_id
4448 AND cnt.effective_start_date = p_esd;
4449
4450 CURSOR csr_ed_county_tax_rule (p_cnt_id NUMBER,
4451 p_esd DATE) IS
4452 SELECT *
4453 FROM hr_h2pi_county_tax_rules_v cnt
4454 WHERE cnt.emp_county_tax_rule_id = p_cnt_id
4455 AND p_esd BETWEEN cnt.effective_start_date
4456 AND cnt.effective_end_date;
4457
4458 CURSOR csr_ed_county_tax_rule_ovn (p_cnt_id NUMBER,
4459 p_esd DATE) IS
4460 SELECT cnt.object_version_number
4461 FROM pay_us_emp_county_tax_rules_f cnt
4462 WHERE cnt.emp_county_tax_rule_id = p_cnt_id
4463 AND p_esd BETWEEN cnt.effective_start_date
4464 AND cnt.effective_end_date;
4465
4466 l_encoded_message VARCHAR2(200);
4467
4468 l_proc VARCHAR2(72) := g_package||'upload_county_tax_rule';
4469
4470 v_ud_cnt hr_h2pi_county_tax_rules%ROWTYPE;
4471 v_ed_cnt hr_h2pi_county_tax_rules_v%ROWTYPE;
4472
4473 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
4474 l_emp_county_tax_rule_id
4475 pay_us_emp_county_tax_rules_f.emp_county_tax_rule_id%TYPE;
4476 l_ovn pay_us_emp_county_tax_rules_f.object_version_number%TYPE;
4477 l_esd pay_us_emp_county_tax_rules_f.effective_start_date%TYPE;
4478 l_eed pay_us_emp_county_tax_rules_f.effective_end_date%TYPE;
4479
4480 l_max_eed pay_us_emp_county_tax_rules_f.effective_end_date%TYPE;
4481 l_del_ovn pay_us_emp_county_tax_rules_f.object_version_number%TYPE;
4482 l_del_esd pay_us_emp_county_tax_rules_f.effective_start_date%TYPE;
4483 l_del_eed pay_us_emp_county_tax_rules_f.effective_end_date%TYPE;
4484 l_val_esd pay_us_emp_county_tax_rules_f.effective_start_date%TYPE;
4485 l_val_eed pay_us_emp_county_tax_rules_f.effective_end_date%TYPE;
4486 l_business_group_id pay_us_emp_county_tax_rules_f.business_group_id%TYPE;
4487
4488 l_records_same BOOLEAN;
4489 l_future_records BOOLEAN;
4490 l_update_mode VARCHAR2(30);
4491 l_delete_mode VARCHAR2(30);
4492
4493 BEGIN
4494 hr_utility.set_location('Entering:'|| l_proc, 10);
4495
4496 OPEN csr_ud_county_tax_rule(p_emp_county_tax_rule_id,
4497 p_effective_start_date);
4498 FETCH csr_ud_county_tax_rule INTO v_ud_cnt;
4499
4500 hr_utility.set_location(l_proc, 20);
4501 l_assignment_id := hr_h2pi_map.get_to_id
4502 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
4503 p_from_id => v_ud_cnt.assignment_id,
4504 p_report_error => TRUE);
4505
4506 l_emp_county_tax_rule_id := hr_h2pi_map.get_to_id
4507 (p_table_name => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
4508 p_from_id => v_ud_cnt.emp_county_tax_rule_id);
4509
4510 IF l_emp_county_tax_rule_id = -1 THEN
4511 hr_utility.set_location(l_proc, 30);
4512 pay_county_tax_rule_api.create_county_tax_rule(
4513 p_effective_date => v_ud_cnt.effective_start_date
4514 ,p_assignment_id => l_assignment_id
4515 ,p_state_code => v_ud_cnt.state_code
4516 ,p_county_code => v_ud_cnt.county_code
4517 ,p_additional_wa_rate => v_ud_cnt.additional_wa_rate
4518 ,p_filing_status_code => v_ud_cnt.filing_status_code
4519 ,p_lit_additional_tax => v_ud_cnt.lit_additional_tax
4520 ,p_lit_override_amount => v_ud_cnt.lit_override_amount
4521 ,p_lit_override_rate => v_ud_cnt.lit_override_rate
4522 ,p_withholding_allowances => v_ud_cnt.withholding_allowances
4523 ,p_lit_exempt => v_ud_cnt.lit_exempt
4524 ,p_sd_exempt => v_ud_cnt.sd_exempt
4525 ,p_ht_exempt => v_ud_cnt.ht_exempt
4526 ,p_school_district_code => v_ud_cnt.school_district_code
4527 ,p_object_version_number => l_ovn
4528 ,p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
4529 ,p_effective_start_date => l_esd
4530 ,p_effective_end_date => l_eed
4531 );
4532
4533 hr_utility.set_location(l_proc, 40);
4534 hr_h2pi_map.create_id_mapping
4535 (p_table_name => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
4536 p_from_id => v_ud_cnt.emp_county_tax_rule_id,
4537 p_to_id => l_emp_county_tax_rule_id);
4538 ELSE
4539
4540 BEGIN
4541 hr_utility.set_location(l_proc, 50);
4542 OPEN csr_ed_county_tax_rule(l_emp_county_tax_rule_id,
4543 v_ud_cnt.effective_start_date);
4544 FETCH csr_ed_county_tax_rule
4545 INTO v_ed_cnt;
4546 IF csr_ed_county_tax_rule%NOTFOUND THEN
4547 hr_utility.set_location(l_proc, 60);
4548 CLOSE csr_ed_county_tax_rule;
4549 ROLLBACK;
4550 hr_utility.set_location(l_proc, 70);
4551 hr_h2pi_error.data_error
4552 (p_from_id => l_emp_county_tax_rule_id,
4553 p_table_name => 'HR_H2PI_COUNTY_TAX_RULES',
4554 p_message_level => 'FATAL',
4555 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
4556 COMMIT;
4557 RAISE MAPPING_ID_INVALID;
4558 ELSE
4559 CLOSE csr_ed_county_tax_rule;
4560 END IF;
4561
4562 OPEN csr_ed_county_tax_rule_ovn(l_emp_county_tax_rule_id,
4563 v_ud_cnt.effective_start_date);
4564 FETCH csr_ed_county_tax_rule_ovn
4565 INTO l_ovn;
4566 CLOSE csr_ed_county_tax_rule_ovn;
4567 END;
4568
4569 l_delete_mode := 'DELETE_NEXT_CHANGE';
4570 LOOP
4571 hr_utility.set_location(l_proc, 80);
4572 l_records_same := FALSE;
4573
4574 SELECT MAX(cnt.effective_end_date)
4575 INTO l_max_eed
4576 FROM pay_us_emp_county_tax_rules_f cnt
4577 WHERE cnt.emp_county_tax_rule_id = l_emp_county_tax_rule_id;
4578
4579 IF l_max_eed > v_ed_cnt.effective_end_date THEN
4580 hr_utility.set_location(l_proc, 90);
4581 l_future_records := TRUE;
4582 ELSE
4583 hr_utility.set_location(l_proc, 100);
4584 l_future_records := FALSE;
4585 END IF;
4586
4587 calculate_datetrack_mode
4588 (p_ud_start_date => v_ud_cnt.effective_start_date
4589 ,p_ud_end_date => v_ud_cnt.effective_end_date
4590 ,p_ed_start_date => v_ed_cnt.effective_start_date
4591 ,p_ed_end_date => v_ed_cnt.effective_end_date
4592 ,p_records_same => l_records_same
4593 ,p_future_records => l_future_records
4594 ,p_update_mode => l_update_mode
4595 ,p_delete_mode => l_delete_mode);
4596
4597 EXIT WHEN l_delete_mode = 'X';
4598 hr_utility.set_location(l_proc, 110);
4599
4600 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
4601
4602 hr_utility.set_location(l_proc, 120);
4603 pay_county_tax_rule_api.update_county_tax_rule(
4604 p_effective_date => v_ud_cnt.effective_start_date
4605 ,p_datetrack_mode => 'UPDATE_OVERRIDE'
4606 ,p_object_version_number => l_ovn
4607 ,p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
4608 ,p_additional_wa_rate => v_ud_cnt.additional_wa_rate
4609 ,p_filing_status_code => v_ud_cnt.filing_status_code
4610 ,p_lit_additional_tax => v_ud_cnt.lit_additional_tax
4611 ,p_lit_override_amount => v_ud_cnt.lit_override_amount
4612 ,p_lit_override_rate => v_ud_cnt.lit_override_rate
4613 ,p_withholding_allowances => v_ud_cnt.withholding_allowances
4614 ,p_lit_exempt => v_ud_cnt.lit_exempt
4615 ,p_sd_exempt => v_ud_cnt.sd_exempt
4616 ,p_ht_exempt => v_ud_cnt.ht_exempt
4617 ,p_school_district_code => v_ud_cnt.school_district_code
4618 ,p_effective_start_date => l_esd
4619 ,p_effective_end_date => l_eed
4620 );
4621 /*
4622 pay_cnt_del.del(
4623 p_effective_date => v_ud_cnt.effective_start_date
4624 ,p_datetrack_mode => l_delete_mode
4625 ,p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
4626 ,p_object_version_number => l_ovn
4627 ,p_effective_start_date => l_esd
4628 ,p_effective_end_date => l_eed
4629 ,p_delete_routine => NULL
4630 );
4631 */
4632
4633 hr_utility.set_location(l_proc, 130);
4634 OPEN csr_ed_county_tax_rule(l_emp_county_tax_rule_id,
4635 v_ud_cnt.effective_start_date);
4636 FETCH csr_ed_county_tax_rule
4637 INTO v_ed_cnt;
4638 CLOSE csr_ed_county_tax_rule;
4639
4640 END IF;
4641
4642 END LOOP;
4643
4644 hr_utility.set_location(l_proc, 140);
4645 pay_county_tax_rule_api.update_county_tax_rule(
4646 p_effective_date => v_ud_cnt.effective_start_date
4647 ,p_datetrack_mode => l_update_mode
4648 ,p_object_version_number => l_ovn
4649 ,p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
4650 ,p_additional_wa_rate => v_ud_cnt.additional_wa_rate
4651 ,p_filing_status_code => v_ud_cnt.filing_status_code
4652 ,p_lit_additional_tax => v_ud_cnt.lit_additional_tax
4653 ,p_lit_override_amount => v_ud_cnt.lit_override_amount
4654 ,p_lit_override_rate => v_ud_cnt.lit_override_rate
4655 ,p_withholding_allowances => v_ud_cnt.withholding_allowances
4656 ,p_lit_exempt => v_ud_cnt.lit_exempt
4657 ,p_sd_exempt => v_ud_cnt.sd_exempt
4658 ,p_ht_exempt => v_ud_cnt.ht_exempt
4659 ,p_school_district_code => v_ud_cnt.school_district_code
4660 ,p_effective_start_date => l_esd
4661 ,p_effective_end_date => l_eed
4662 );
4663 END IF;
4664
4665 hr_utility.set_location(l_proc, 150);
4666 UPDATE hr_h2pi_county_tax_rules cnt
4667 SET status = 'C'
4668 WHERE cnt.emp_county_tax_rule_id = v_ud_cnt.emp_county_tax_rule_id
4669 AND cnt.client_id = p_from_client_id
4670 AND cnt.effective_start_date = v_ud_cnt.effective_start_date
4671 AND cnt.effective_end_date = v_ud_cnt.effective_end_date;
4672
4673 CLOSE csr_ud_county_tax_rule;
4674 hr_utility.set_location('Leaving:'|| l_proc, 160);
4675 COMMIT;
4676
4677 EXCEPTION
4678 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
4679 ROLLBACK;
4680 hr_utility.set_location(l_proc, 170);
4681 l_encoded_message := fnd_message.get_encoded;
4682 hr_h2pi_error.data_error
4683 (p_from_id => v_ud_cnt.emp_county_tax_rule_id,
4684 p_table_name => 'HR_H2PI_COUNTY_TAX_RULES',
4685 p_message_level => 'FATAL',
4686 p_message_text => l_encoded_message);
4687 COMMIT;
4688 RAISE;
4689
4690 END;
4691
4692
4693 PROCEDURE upload_city_tax_record (p_from_client_id NUMBER,
4694 p_emp_city_tax_rule_id NUMBER,
4695 p_effective_start_date DATE) IS
4696
4697 CURSOR csr_ud_city_tax_rule (p_cty_id NUMBER,
4698 p_esd DATE) IS
4699 SELECT *
4700 FROM hr_h2pi_city_tax_rules cty
4701 WHERE cty.emp_city_tax_rule_id = p_cty_id
4702 AND cty.client_Id = p_from_client_id
4703 AND cty.effective_start_date = p_esd;
4704
4705 CURSOR csr_ed_city_tax_rule (p_cty_id NUMBER,
4706 p_esd DATE) IS
4707 SELECT *
4708 FROM hr_h2pi_city_tax_rules_v cty
4709 WHERE cty.emp_city_tax_rule_id = p_cty_id
4710 AND p_esd BETWEEN cty.effective_start_date
4711 AND cty.effective_end_date;
4712
4713 CURSOR csr_ed_city_tax_rule_ovn (p_cty_id NUMBER,
4714 p_esd DATE) IS
4715 SELECT cty.object_version_number
4716 FROM pay_us_emp_city_tax_rules_f cty
4717 WHERE cty.emp_city_tax_rule_id = p_cty_id
4718 AND p_esd BETWEEN cty.effective_start_date
4719 AND cty.effective_end_date;
4720
4721 l_encoded_message VARCHAR2(200);
4722
4723 l_proc VARCHAR2(72) := g_package||'upload_city_tax_rule';
4724
4725 v_ud_cty hr_h2pi_city_tax_rules%ROWTYPE;
4726 v_ed_cty hr_h2pi_city_tax_rules_v%ROWTYPE;
4727
4728 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
4729 l_emp_city_tax_rule_id
4730 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE;
4731 l_ovn pay_us_emp_city_tax_rules_f.object_version_number%TYPE;
4732 l_esd pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
4733 l_eed pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
4734
4735 l_max_eed pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
4736 l_del_ovn pay_us_emp_city_tax_rules_f.object_version_number%TYPE;
4737 l_del_esd pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
4738 l_del_eed pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
4739 l_val_esd pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
4740 l_val_eed pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
4741 l_business_group_id pay_us_emp_city_tax_rules_f.business_group_id%TYPE;
4742
4743 l_records_same BOOLEAN;
4744 l_future_records BOOLEAN;
4745 l_update_mode VARCHAR2(30);
4746 l_delete_mode VARCHAR2(30);
4747
4748 BEGIN
4749 hr_utility.set_location('Entering:'|| l_proc, 10);
4750
4751 OPEN csr_ud_city_tax_rule(p_emp_city_tax_rule_id,
4752 p_effective_start_date);
4753 FETCH csr_ud_city_tax_rule INTO v_ud_cty;
4754
4755 hr_utility.set_location(l_proc, 20);
4756 l_assignment_id := hr_h2pi_map.get_to_id
4757 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
4758 p_from_id => v_ud_cty.assignment_id,
4759 p_report_error => TRUE);
4760
4761 l_emp_city_tax_rule_id := hr_h2pi_map.get_to_id
4762 (p_table_name => 'PAY_US_EMP_CITY_TAX_RULES_F',
4763 p_from_id => v_ud_cty.emp_city_tax_rule_id);
4764
4765 IF l_emp_city_tax_rule_id = -1 THEN
4766 hr_utility.set_location(l_proc, 30);
4767 pay_city_tax_rule_api.create_city_tax_rule(
4768 p_effective_date => v_ud_cty.effective_start_date
4769 ,p_assignment_id => l_assignment_id
4770 ,p_state_code => v_ud_cty.state_code
4771 ,p_county_code => v_ud_cty.county_code
4772 ,p_city_code => v_ud_cty.city_code
4773 ,p_additional_wa_rate => v_ud_cty.additional_wa_rate
4774 ,p_filing_status_code => v_ud_cty.filing_status_code
4775 ,p_lit_additional_tax => v_ud_cty.lit_additional_tax
4776 ,p_lit_override_amount => v_ud_cty.lit_override_amount
4777 ,p_lit_override_rate => v_ud_cty.lit_override_rate
4778 ,p_withholding_allowances => v_ud_cty.withholding_allowances
4779 ,p_lit_exempt => v_ud_cty.lit_exempt
4780 ,p_sd_exempt => v_ud_cty.sd_exempt
4781 ,p_ht_exempt => v_ud_cty.ht_exempt
4782 ,p_school_district_code => v_ud_cty.school_district_code
4783 ,p_object_version_number => l_ovn
4784 ,p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
4785 ,p_effective_start_date => l_esd
4786 ,p_effective_end_date => l_eed
4787 );
4788
4789 hr_utility.set_location(l_proc, 40);
4790 hr_h2pi_map.create_id_mapping
4791 (p_table_name => 'PAY_US_EMP_CITY_TAX_RULES_F',
4792 p_from_id => v_ud_cty.emp_city_tax_rule_id,
4793 p_to_id => l_emp_city_tax_rule_id);
4794 ELSE
4795
4796 BEGIN
4797 hr_utility.set_location(l_proc, 50);
4798 OPEN csr_ed_city_tax_rule(l_emp_city_tax_rule_id,
4799 v_ud_cty.effective_start_date);
4800 FETCH csr_ed_city_tax_rule
4801 INTO v_ed_cty;
4802 IF csr_ed_city_tax_rule%NOTFOUND THEN
4803 hr_utility.set_location(l_proc, 60);
4804 CLOSE csr_ed_city_tax_rule;
4805 ROLLBACK;
4806 hr_utility.set_location(l_proc, 70);
4807 hr_h2pi_error.data_error
4808 (p_from_id => l_emp_city_tax_rule_id,
4809 p_table_name => 'HR_H2PI_CITY_TAX_RULES',
4810 p_message_level => 'FATAL',
4811 p_message_name => 'HR_289240_MAPPING_ID_INVALID');
4812 COMMIT;
4813 RAISE MAPPING_ID_INVALID;
4814 ELSE
4815 CLOSE csr_ed_city_tax_rule;
4816 END IF;
4817
4818 OPEN csr_ed_city_tax_rule_ovn(l_emp_city_tax_rule_id,
4819 v_ud_cty.effective_start_date);
4820 FETCH csr_ed_city_tax_rule_ovn
4821 INTO l_ovn;
4822 CLOSE csr_ed_city_tax_rule_ovn;
4823 END;
4824
4825 l_delete_mode := 'DELETE_NEXT_CHANGE';
4826 LOOP
4827 hr_utility.set_location(l_proc, 80);
4828 l_records_same := FALSE;
4829
4830 SELECT MAX(sta.effective_end_date)
4831 INTO l_max_eed
4832 FROM pay_us_emp_city_tax_rules_f sta
4833 WHERE sta.emp_city_tax_rule_id = l_emp_city_tax_rule_id;
4834
4835 IF l_max_eed > v_ed_cty.effective_end_date THEN
4836 hr_utility.set_location(l_proc, 90);
4837 l_future_records := TRUE;
4838 ELSE
4839 hr_utility.set_location(l_proc, 100);
4840 l_future_records := FALSE;
4841 END IF;
4842
4843 calculate_datetrack_mode
4844 (p_ud_start_date => v_ud_cty.effective_start_date
4845 ,p_ud_end_date => v_ud_cty.effective_end_date
4846 ,p_ed_start_date => v_ed_cty.effective_start_date
4847 ,p_ed_end_date => v_ed_cty.effective_end_date
4848 ,p_records_same => l_records_same
4849 ,p_future_records => l_future_records
4850 ,p_update_mode => l_update_mode
4851 ,p_delete_mode => l_delete_mode);
4852
4853 EXIT WHEN l_delete_mode = 'X';
4854 hr_utility.set_location(l_proc, 110);
4855
4856 IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
4857
4858 hr_utility.set_location(l_proc, 120);
4859 pay_city_tax_rule_api.update_city_tax_rule(
4860 p_effective_date => v_ud_cty.effective_start_date
4861 ,p_datetrack_mode => 'UPDATE_OVERRIDE'
4862 ,p_object_version_number => l_ovn
4863 ,p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
4864 ,p_additional_wa_rate => v_ud_cty.additional_wa_rate
4865 ,p_filing_status_code => v_ud_cty.filing_status_code
4866 ,p_lit_additional_tax => v_ud_cty.lit_additional_tax
4867 ,p_lit_override_amount => v_ud_cty.lit_override_amount
4868 ,p_lit_override_rate => v_ud_cty.lit_override_rate
4869 ,p_withholding_allowances => v_ud_cty.withholding_allowances
4870 ,p_lit_exempt => v_ud_cty.lit_exempt
4871 ,p_sd_exempt => v_ud_cty.sd_exempt
4872 ,p_ht_exempt => v_ud_cty.ht_exempt
4873 ,p_school_district_code => v_ud_cty.school_district_code
4874 ,p_effective_start_date => l_esd
4875 ,p_effective_end_date => l_eed
4876 );
4877 pay_cty_del.del(
4878 p_effective_date => v_ud_cty.effective_start_date
4879 ,p_datetrack_mode => l_delete_mode
4880 ,p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
4881 ,p_object_version_number => l_ovn
4882 ,p_effective_start_date => l_esd
4883 ,p_effective_end_date => l_eed
4884 ,p_delete_routine => NULL
4885 );
4886
4887 hr_utility.set_location(l_proc, 130);
4888 OPEN csr_ed_city_tax_rule(l_emp_city_tax_rule_id,
4889 v_ud_cty.effective_start_date);
4890 FETCH csr_ed_city_tax_rule
4891 INTO v_ed_cty;
4892 CLOSE csr_ed_city_tax_rule;
4893
4894 END IF;
4895
4896 END LOOP;
4897
4898 hr_utility.set_location(l_proc, 140);
4899 pay_city_tax_rule_api.update_city_tax_rule(
4900 p_effective_date => v_ud_cty.effective_start_date
4901 ,p_datetrack_mode => l_update_mode
4902 ,p_object_version_number => l_ovn
4903 ,p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
4904 ,p_additional_wa_rate => v_ud_cty.additional_wa_rate
4905 ,p_filing_status_code => v_ud_cty.filing_status_code
4906 ,p_lit_additional_tax => v_ud_cty.lit_additional_tax
4907 ,p_lit_override_amount => v_ud_cty.lit_override_amount
4908 ,p_lit_override_rate => v_ud_cty.lit_override_rate
4909 ,p_withholding_allowances => v_ud_cty.withholding_allowances
4910 ,p_lit_exempt => v_ud_cty.lit_exempt
4911 ,p_sd_exempt => v_ud_cty.sd_exempt
4912 ,p_ht_exempt => v_ud_cty.ht_exempt
4913 ,p_school_district_code => v_ud_cty.school_district_code
4914 ,p_effective_start_date => l_esd
4915 ,p_effective_end_date => l_eed
4916 );
4917 END IF;
4918
4919 hr_utility.set_location(l_proc, 150);
4920 UPDATE hr_h2pi_city_tax_rules sta
4921 SET status = 'C'
4922 WHERE sta.emp_city_tax_rule_id = v_ud_cty.emp_city_tax_rule_id
4923 AND sta.client_id = p_from_client_id
4924 AND sta.effective_start_date = v_ud_cty.effective_start_date
4925 AND sta.effective_end_date = v_ud_cty.effective_end_date;
4926
4927 CLOSE csr_ud_city_tax_rule;
4928 hr_utility.set_location('Leaving:'|| l_proc, 160);
4929 COMMIT;
4930
4931 EXCEPTION
4932 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
4933 ROLLBACK;
4934 hr_utility.set_location(l_proc, 170);
4935 l_encoded_message := fnd_message.get_encoded;
4936 hr_h2pi_error.data_error
4937 (p_from_id => v_ud_cty.emp_city_tax_rule_id,
4938 p_table_name => 'HR_H2PI_CITY_TAX_RULES',
4939 p_message_level => 'FATAL',
4940 p_message_text => l_encoded_message);
4941 COMMIT;
4942 RAISE;
4943
4944 END;
4945
4946
4947 /*********************************************************************
4948 * US SPECIFIC
4949 ********************************************************************/
4950 PROCEDURE upload_tax_percentage (p_from_client_id NUMBER,
4951 p_person_id NUMBER) IS
4952
4953 CURSOR csr_ed_assignment (p_per_id NUMBER) IS
4954 SELECT DISTINCT asg.assignment_id
4955 FROM hr_h2pi_assignments_v asg
4956 WHERE asg.person_id = p_per_id;
4957
4958 CURSOR csr_city_pct (p_ud_iv_id1 NUMBER,
4959 p_ud_iv_id2 NUMBER,
4960 p_ud_asg_id NUMBER,
4961 p_ed_iv_id1 NUMBER,
4962 p_ed_iv_id2 NUMBER,
4963 p_ed_asg_id NUMBER,
4964 p_county VARCHAR2) IS
4965 SELECT SUBSTR(fr_eev.screen_entry_value, 8, 4) city_code,
4966 SUM (fr_eev2.screen_entry_value) percentage,
4967 SUM (fr_eev2.screen_entry_value) - SUM (to_eev2.screen_entry_value) pct_diff
4968 FROM hr_h2pi_element_entry_values_v to_eev,
4969 hr_h2pi_element_entry_values_v to_eev2,
4970 hr_h2pi_element_entries_v to_ele,
4971 hr_h2pi_element_entry_values fr_eev,
4972 hr_h2pi_element_entry_values fr_eev2,
4973 hr_h2pi_element_entries fr_ele
4974 WHERE to_ele.element_entry_id = to_eev.element_entry_id
4975 AND to_eev.input_value_id = p_ed_iv_id1
4976 AND to_eev2.input_value_id = p_ed_iv_id2
4977 AND fr_eev.input_value_id = p_ud_iv_id1
4978 AND fr_eev.client_id = p_from_client_id --
4979 AND fr_eev2.input_value_id = p_ud_iv_id2
4980 AND fr_eev2.client_id = p_from_client_id --
4981 AND to_ele.effective_start_date = (SELECT MAX(to_ele_t.effective_start_date)
4982 from hr_h2pi_element_entries_v to_ele_t
4983 WHERE to_ele_t.element_entry_id = to_ele.element_entry_id)
4984 AND to_eev.effective_start_date BETWEEN to_ele.effective_start_date
4985 AND to_ele.effective_end_date
4986 AND to_ele.element_entry_id = to_eev2.element_entry_id
4987 AND to_eev2.effective_start_date BETWEEN to_ele.effective_start_date
4988 AND to_ele.effective_end_date
4989 AND to_ele.creator_type = 'UT'
4990 AND fr_ele.element_entry_id = fr_eev.element_entry_id
4991 AND fr_ele.client_id = p_from_client_id --
4992 AND fr_ele.effective_start_date = (SELECT MAX(fr_ele_t.effective_start_date)
4993 from hr_h2pi_element_entries fr_ele_t
4994 WHERE fr_ele_t.element_entry_id = fr_ele.element_entry_id
4995 AND fr_ele_t.client_id = p_from_client_id)
4996 AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
4997 AND fr_ele.effective_end_date
4998 AND fr_ele.element_entry_id = fr_eev2.element_entry_id
4999 --AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
5000 -- AND fr_ele.effective_end_date
5001 AND fr_eev2.effective_start_date BETWEEN fr_ele.effective_start_date
5002 AND fr_ele.effective_end_date
5003 AND fr_ele.creator_type = 'UT'
5004 and fr_ele.assignment_id = p_ud_asg_id
5005 and to_ele.assignment_id = p_ed_asg_id
5006 and to_eev.screen_entry_value = fr_eev.screen_entry_value
5007 and SUBSTR(to_eev.screen_entry_value,8,4) <> '0000'
5008 and SUBSTR(fr_eev.screen_entry_value,1,6) = p_county
5009 group by SUBSTR(fr_eev.screen_entry_value, 8, 4)
5010
5011 order by 3;
5012
5013 CURSOR csr_county_pct (p_ud_iv_id1 NUMBER,
5014 p_ud_iv_id2 NUMBER,
5015 p_ud_asg_id NUMBER,
5016 p_ed_iv_id1 NUMBER,
5017 p_ed_iv_id2 NUMBER,
5018 p_ed_asg_id NUMBER,
5019 p_state VARCHAR2) IS
5020 SELECT SUBSTR(fr_eev.screen_entry_value,4,3) county_code,
5021 SUM (fr_eev2.screen_entry_value) percentage,
5022 SUM (fr_eev2.screen_entry_value) - SUM (to_eev2.screen_entry_value) pct_diff
5023 FROM hr_h2pi_element_entry_values_v to_eev,
5024 hr_h2pi_element_entry_values_v to_eev2,
5025 hr_h2pi_element_entries_v to_ele,
5026 hr_h2pi_element_entry_values fr_eev,
5027 hr_h2pi_element_entry_values fr_eev2,
5028 hr_h2pi_element_entries fr_ele
5029 WHERE to_ele.element_entry_id = to_eev.element_entry_id
5030 AND to_eev.input_value_id = p_ed_iv_id1
5031 AND to_eev2.input_value_id = p_ed_iv_id2
5032 AND fr_eev.input_value_id = p_ud_iv_id1
5033 AND fr_eev.client_id = p_from_client_id --
5034 AND fr_eev2.input_value_id = p_ud_iv_id2
5035 AND fr_eev2.client_id = p_from_client_id --
5036 AND to_ele.effective_start_date = (SELECT MAX(to_ele_t.effective_start_date)
5037 from hr_h2pi_element_entries_v to_ele_t
5038 WHERE to_ele_t.element_entry_id = to_ele.element_entry_id)
5039 AND to_eev.effective_start_date BETWEEN to_ele.effective_start_date
5040 AND to_ele.effective_end_date
5041 AND to_ele.element_entry_id = to_eev2.element_entry_id
5042 AND to_eev2.effective_start_date BETWEEN to_ele.effective_start_date
5043 AND to_ele.effective_end_date
5044 AND to_ele.creator_type = 'UT'
5045 AND fr_ele.element_entry_id = fr_eev.element_entry_id
5046 AND fr_ele.client_id = p_from_client_id --
5047 AND fr_ele.effective_start_date = (SELECT MAX(fr_ele_t.effective_start_date)
5048 from hr_h2pi_element_entries fr_ele_t
5049 WHERE fr_ele_t.element_entry_id = fr_ele.element_entry_id
5050 AND fr_ele_t.client_id = p_from_client_id)
5051 AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
5052 AND fr_ele.effective_end_date
5053 AND fr_ele.element_entry_id = fr_eev2.element_entry_id
5054 --AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
5055 -- AND fr_ele.effective_end_date
5056 AND fr_eev2.effective_start_date BETWEEN fr_ele.effective_start_date
5057 AND fr_ele.effective_end_date
5058 AND fr_ele.creator_type = 'UT'
5059 and fr_ele.assignment_id = p_ud_asg_id
5060 and to_ele.assignment_id = p_ed_asg_id
5061 and to_eev.screen_entry_value = fr_eev.screen_entry_value
5062 and SUBSTR(to_eev.screen_entry_value,4,8) <> '000-0000'
5063 and SUBSTR(fr_eev.screen_entry_value,1,2) = p_state
5064 group by SUBSTR(fr_eev.screen_entry_value,4,3)
5065 order by 3;
5066
5067 CURSOR csr_state_pct (p_ud_iv_id1 NUMBER,
5068 p_ud_iv_id2 NUMBER,
5069 p_ud_asg_id NUMBER,
5070 p_ed_iv_id1 NUMBER,
5071 p_ed_iv_id2 NUMBER,
5072 p_ed_asg_id NUMBER) IS
5073 SELECT SUBSTR(fr_eev.screen_entry_value,1,2) state_code,
5074 SUM (fr_eev2.screen_entry_value) percentage,
5075 SUM (fr_eev2.screen_entry_value) - SUM (to_eev2.screen_entry_value) pct_diff
5076 FROM hr_h2pi_element_entry_values_v to_eev,
5077 hr_h2pi_element_entry_values_v to_eev2,
5078 hr_h2pi_element_entries_v to_ele,
5079 hr_h2pi_element_entry_values fr_eev,
5080 hr_h2pi_element_entry_values fr_eev2,
5081 hr_h2pi_element_entries fr_ele
5082 WHERE to_ele.element_entry_id = to_eev.element_entry_id
5083 AND to_eev.input_value_id = p_ed_iv_id1
5084 AND to_eev2.input_value_id = p_ed_iv_id2
5085 AND fr_eev.input_value_id = p_ud_iv_id1
5086 AND fr_eev.client_id = p_from_client_id --
5087 AND fr_eev2.input_value_id = p_ud_iv_id2
5088 AND fr_eev2.client_id = p_from_client_id --
5089 AND to_ele.effective_start_date = (SELECT MAX(to_ele_t.effective_start_date)
5090 from hr_h2pi_element_entries_v to_ele_t
5091 WHERE to_ele_t.element_entry_id = to_ele.element_entry_id)
5092 AND to_eev.effective_start_date BETWEEN to_ele.effective_start_date
5093 AND to_ele.effective_end_date
5094 AND to_ele.element_entry_id = to_eev2.element_entry_id
5095 AND to_eev2.effective_start_date BETWEEN to_ele.effective_start_date
5096 AND to_ele.effective_end_date
5097 AND to_ele.creator_type = 'UT'
5098 AND fr_ele.element_entry_id = fr_eev.element_entry_id
5099 AND fr_ele.client_id = p_from_client_id --
5100 AND fr_ele.effective_start_date = (SELECT MAX(fr_ele_t.effective_start_date)
5101 from hr_h2pi_element_entries fr_ele_t
5102 WHERE fr_ele_t.element_entry_id = fr_ele.element_entry_id
5103 AND fr_ele_t.client_id = p_from_client_id)
5104 AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
5105 AND fr_ele.effective_end_date
5106 AND fr_ele.element_entry_id = fr_eev2.element_entry_id
5107 --AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
5108 -- AND fr_ele.effective_end_date
5109 AND fr_eev2.effective_start_date BETWEEN fr_ele.effective_start_date
5110 AND fr_ele.effective_end_date
5111 AND fr_ele.creator_type = 'UT'
5112 and fr_ele.assignment_id = p_ud_asg_id
5113 and to_ele.assignment_id = p_ed_asg_id
5114 and to_eev.screen_entry_value = fr_eev.screen_entry_value
5115 group by SUBSTR(fr_eev.screen_entry_value,1,2)
5116 order by 3;
5117
5118 CURSOR csr_ed_input_values IS
5119 SELECT ipv1.input_value_id,
5120 ipv2.input_value_id
5121 FROM pay_input_values_f ipv1,
5122 pay_input_values_f ipv2,
5123 pay_element_types_f elt
5124 WHERE element_name = 'VERTEX'
5125 AND elt.element_type_id = ipv1.element_type_id
5126 AND elt.element_type_id = ipv2.element_type_id
5127 AND ipv1.name = 'Jurisdiction'
5128 AND ipv2.name = 'Percentage';
5129
5130 CURSOR csr_element_entry (p_asg_id NUMBER) IS
5131 SELECT emp_state_tax_rule_id,
5132 jurisdiction_code
5133 FROM hr_h2pi_state_tax_rules_v
5134 WHERE assignment_id = p_asg_id;
5135
5136 l_proc VARCHAR2(72) := g_package||'upload_tax_percentage';
5137
5138 l_encoded_message VARCHAR2(200);
5139
5140 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
5141 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
5142 l_ud_input_value_id1 pay_input_values_f.input_value_id%TYPE;
5143 l_ud_input_value_id2 pay_input_values_f.input_value_id%TYPE;
5144 l_ud_assignment_id per_all_assignments_f.assignment_id%TYPE;
5145 l_person_id per_all_people_f.person_id%TYPE;
5146
5147 l_effective_date DATE;
5148
5149 begin
5150 hr_utility.set_location('Entering:'|| l_proc, 10);
5151 l_person_id := hr_h2pi_map.get_to_id
5152 (p_table_name => 'PER_ALL_PEOPLE_F',
5153 p_from_id => p_person_id,
5154 p_report_error => TRUE);
5155
5156 FOR v_asg IN csr_ed_assignment(l_person_id) LOOP
5157
5158 hr_utility.set_location(l_proc, 20);
5159 l_ud_assignment_id := hr_h2pi_map.get_from_id
5160 (p_table_name => 'PER_ALL_ASSIGNMENTS_F',
5161 p_to_id => v_asg.assignment_id,
5162 p_report_error => TRUE);
5163
5164 SELECT MAX(effective_end_date)
5165 INTO l_effective_date
5166 FROM hr_h2pi_element_entries
5167 WHERE assignment_id = l_ud_assignment_id;
5168
5169 OPEN csr_ed_input_values;
5170 FETCH csr_ed_input_values INTO l_input_value_id1,
5171 l_input_value_id2;
5172 CLOSE csr_ed_input_values;
5173
5174 l_ud_input_value_id1 := hr_h2pi_map.get_from_id
5175 (p_table_name => 'PAY_INPUT_VALUES_F',
5176 p_to_id => l_input_value_id1,
5177 p_report_error => TRUE);
5178 l_ud_input_value_id2 := hr_h2pi_map.get_from_id
5179 (p_table_name => 'PAY_INPUT_VALUES_F',
5180 p_to_id => l_input_value_id2,
5181 p_report_error => TRUE);
5182
5183
5184 FOR v_sta IN csr_state_pct(l_ud_input_value_id1,
5185 l_ud_input_value_id2,
5186 l_ud_assignment_id,
5187 l_input_value_id1,
5188 l_input_value_id2,
5189 v_asg.assignment_id) LOOP
5190 hr_utility.set_location(l_proc, 30);
5191 IF v_sta.pct_diff > 0 THEN
5192 hr_utility.set_location(l_proc, 40);
5193 pay_us_tax_api.correct_tax_percentage(
5194 p_assignment_id => v_asg.assignment_id
5195 ,p_effective_date => l_effective_date
5196 ,p_state_code => v_sta.state_code
5197 ,p_county_code => '000'
5198 ,p_city_code => '0000'
5199 ,p_percentage => v_sta.percentage);
5200 END IF;
5201
5202 FOR v_cnt IN csr_county_pct(l_ud_input_value_id1,
5203 l_ud_input_value_id2,
5204 l_ud_assignment_id,
5205 l_input_value_id1,
5206 l_input_value_id2,
5207 v_asg.assignment_id,
5208 v_sta.state_code) LOOP
5209 hr_utility.set_location(l_proc, 50);
5210 IF v_cnt.pct_diff > 0 THEN
5211 hr_utility.set_location(l_proc, 60);
5212 pay_us_tax_api.correct_tax_percentage(
5213 p_assignment_id => v_asg.assignment_id
5214 ,p_effective_date => l_effective_date
5215 ,p_state_code => v_sta.state_code
5216 ,p_county_code => v_cnt.county_code
5217 ,p_city_code => '0000'
5218 ,p_percentage => v_cnt.percentage);
5219 END IF;
5220
5221 FOR v_cty IN csr_city_pct(l_ud_input_value_id1,
5222 l_ud_input_value_id2,
5223 l_ud_assignment_id,
5224 l_input_value_id1,
5225 l_input_value_id2,
5226 v_asg.assignment_id,
5227 v_sta.state_code||'-'||v_cnt.county_code) LOOP
5228 hr_utility.set_location(l_proc, 70);
5229 IF v_cty.pct_diff <> 0 THEN
5230 hr_utility.set_location(l_proc, 80);
5231 pay_us_tax_api.correct_tax_percentage(
5232 p_assignment_id => v_asg.assignment_id
5233 ,p_effective_date => l_effective_date
5234 ,p_state_code => v_sta.state_code
5235 ,p_county_code => v_cnt.county_code
5236 ,p_city_code => v_cty.city_code
5237 ,p_percentage => v_cty.percentage);
5238 END IF;
5239 END LOOP;
5240
5241 hr_utility.set_location(l_proc, 90);
5242 IF v_cnt.pct_diff < 0 THEN
5243 hr_utility.set_location(l_proc, 100);
5244 pay_us_tax_api.correct_tax_percentage(
5245 p_assignment_id => v_asg.assignment_id
5246 ,p_effective_date => l_effective_date
5247 ,p_state_code => v_sta.state_code
5248 ,p_county_code => v_cnt.county_code
5249 ,p_city_code => '0000'
5250 ,p_percentage => v_cnt.percentage);
5251 END IF;
5252
5253 END LOOP;
5254
5255 hr_utility.set_location(l_proc, 110);
5256 IF v_sta.pct_diff < 0 THEN
5257 hr_utility.set_location(l_proc, 120);
5258 pay_us_tax_api.correct_tax_percentage(
5259 p_assignment_id => v_asg.assignment_id
5260 ,p_effective_date => l_effective_date
5261 ,p_state_code => v_sta.state_code
5262 ,p_county_code => '000'
5263 ,p_city_code => '0000'
5264 ,p_percentage => v_sta.percentage);
5265 END IF;
5266
5267 END LOOP;
5268
5269 END LOOP;
5270
5271 hr_utility.set_location(l_proc, 125);
5272 UPDATE hr_h2pi_element_entry_values eev
5273 SET eev.status = 'C'
5274 WHERE eev.element_entry_id IN (SELECT ee.element_entry_id
5275 FROM hr_h2pi_element_entries ee
5276 WHERE ee.person_id = p_person_id
5277 AND ee.creator_type = 'UT'
5278 AND ee.client_id = p_from_client_id);
5279
5280 UPDATE hr_h2pi_element_entries ee
5281 SET ee.status = 'C'
5282 WHERE ee.person_id = p_person_id
5283 AND ee.creator_type = 'UT'
5284 AND ee.client_id = p_from_client_id;
5285
5286 hr_utility.set_location('Entering:'|| l_proc, 130);
5287
5288 EXCEPTION
5289 WHEN APP_EXCEPTIONS.APPLICATION_EXCEPTION THEN
5290 ROLLBACK;
5291 hr_utility.set_location(l_proc, 140);
5292 l_encoded_message := fnd_message.get_encoded;
5293 hr_h2pi_error.data_error
5294 (p_from_id => l_ud_assignment_id,
5295 p_table_name => 'HR_H2PI_ASSIGNMENTS',
5296 p_message_level => 'FATAL',
5297 p_message_text => l_encoded_message);
5298 COMMIT;
5299 RAISE;
5300 WHEN MAPPING_ID_MISSING THEN
5301 hr_utility.set_location(l_proc, 150);
5302 RAISE PERSON_ERROR;
5303
5304 END;
5305
5306 END hr_h2pi_person_upload;