[Home] [Help]
304: gv_discount_value VARCHAR2(240);
305: gv_variable_idw VARCHAR2(10);
306: gv_IDW_calc_method hr_organization_information.org_information10%type;
307:
308: PROCEDURE hr_utility_trace (p_data IN VARCHAR2) IS
309: BEGIN
310: IF gv_debug THEN
311: hr_utility.trace (p_data);
312: END IF;
307:
308: PROCEDURE hr_utility_trace (p_data IN VARCHAR2) IS
309: BEGIN
310: IF gv_debug THEN
311: hr_utility.trace (p_data);
312: END IF;
313: END;
314:
315: FUNCTION event_qualified(p_person_id NUMBER,
344: lv_chk_emp_status VARCHAR2(1);
345:
346: BEGIN
347: l_proc_name := gv_package || 'event_qualified';
348: hr_utility_trace ('Entering '||l_proc_name);
349: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
350: hr_utility_trace ('p_effective_date = '||
351: fnd_date.date_to_canonical(p_effective_date));
352: hr_utility_trace ('p_gre_id = '||p_gre_id);
345:
346: BEGIN
347: l_proc_name := gv_package || 'event_qualified';
348: hr_utility_trace ('Entering '||l_proc_name);
349: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
350: hr_utility_trace ('p_effective_date = '||
351: fnd_date.date_to_canonical(p_effective_date));
352: hr_utility_trace ('p_gre_id = '||p_gre_id);
353:
346: BEGIN
347: l_proc_name := gv_package || 'event_qualified';
348: hr_utility_trace ('Entering '||l_proc_name);
349: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
350: hr_utility_trace ('p_effective_date = '||
351: fnd_date.date_to_canonical(p_effective_date));
352: hr_utility_trace ('p_gre_id = '||p_gre_id);
353:
354: l_return := FALSE;
348: hr_utility_trace ('Entering '||l_proc_name);
349: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
350: hr_utility_trace ('p_effective_date = '||
351: fnd_date.date_to_canonical(p_effective_date));
352: hr_utility_trace ('p_gre_id = '||p_gre_id);
353:
354: l_return := FALSE;
355: -- Check if assignment is cached.
356: IF gt_gre_cache.count() > 0 THEN
355: -- Check if assignment is cached.
356: IF gt_gre_cache.count() > 0 THEN
357: FOR ln_cntr IN gt_gre_cache.first()..gt_gre_cache.last() LOOP
358: IF p_assignment_id = gt_gre_cache(ln_cntr).assignment_id THEN
359: hr_utility_trace('Assignment '||p_assignment_id||
360: ' found in cache.');
361: l_return := TRUE;
362: EXIT;
363: END IF;
378: END IF;
379: END LOOP;
380: ELSE
381: -- Assignment is not cached. Load cache. Check if event is qualified.
382: hr_utility_trace('Assignment '||p_assignment_id||
383: ' not found in cache. Hitting database now.');
384: l_return := FALSE;
385: FOR csr_asg_rec IN csr_asg LOOP
386: ln_cntr := gt_gre_cache.count();
399: l_return := TRUE;
400: END IF;
401: END LOOP;
402: END IF;
403: hr_utility_trace('Checking for applicant record');
404: OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_effective_date));
405: FETCH c_check_per_status INTO lv_chk_emp_status;
406: CLOSE c_check_per_status;
407:
410: END IF;
411:
412:
413: IF l_return THEN
414: hr_utility_trace ('Event qualified.');
415: ELSE
416: hr_utility_trace ('Event not qualified.');
417: END IF;
418: hr_utility_trace ('Leaving '||l_proc_name);
412:
413: IF l_return THEN
414: hr_utility_trace ('Event qualified.');
415: ELSE
416: hr_utility_trace ('Event not qualified.');
417: END IF;
418: hr_utility_trace ('Leaving '||l_proc_name);
419: RETURN (l_return);
420: END event_qualified;
414: hr_utility_trace ('Event qualified.');
415: ELSE
416: hr_utility_trace ('Event not qualified.');
417: END IF;
418: hr_utility_trace ('Leaving '||l_proc_name);
419: RETURN (l_return);
420: END event_qualified;
421:
422: FUNCTION get_start_date( p_gre_id IN VARCHAR2 )
453: ln_count NUMBER;
454:
455: BEGIN
456:
457: hr_utility_trace('p_gre_id '||nvl( p_gre_id, -999));
458:
459: pay_recorded_requests_pkg.get_recorded_date_no_ins(
460: p_process => 'MX_SOC_SEC_ARCH',
461: p_recorded_date => ld_start_date,
497: OPEN c_get_bus_grp_id(p_gre_id);
498: FETCH c_get_bus_grp_id INTO ln_bus_grp_id;
499: CLOSE c_get_bus_grp_id;
500:
501: hr_utility_trace('ln_bus_grp_id '||ln_bus_grp_id);
502:
503: SELECT count(*)
504: INTO ln_count
505: FROM fnd_sessions
504: INTO ln_count
505: FROM fnd_sessions
506: WHERE session_id = USERENV('sessionid');
507:
508: hr_utility_trace('ln_count '||ln_count);
509:
510: ln_legal_employer_id :=
511: hr_mx_utility.get_legal_employer(ln_bus_grp_id, p_gre_id);
512:
509:
510: ln_legal_employer_id :=
511: hr_mx_utility.get_legal_employer(ln_bus_grp_id, p_gre_id);
512:
513: hr_utility_trace('ln_legal_employer_id '||ln_legal_employer_id);
514:
515: -- get the report Implementation Date from p_legal_emp_id
516:
517: OPEN c_get_imp_date(ln_legal_employer_id);
527: END IF;
528:
529: CLOSE c_get_imp_date;
530:
531: hr_utility_trace('ld_report_imp_date '||ld_report_imp_date);
532:
533: ln_tax_unit_id := to_number(p_gre_id) ;
534:
535: OPEN c_get_start_date(ln_tax_unit_id);
543: END IF;
544:
545: CLOSE c_get_start_date;
546:
547: hr_utility_trace('lv_start_date '||lv_start_date);
548:
549: ld_start_date := fnd_date.canonical_to_date(lv_start_date) ;
550:
551: hr_utility_trace('ld_start_date '||ld_start_date);
547: hr_utility_trace('lv_start_date '||lv_start_date);
548:
549: ld_start_date := fnd_date.canonical_to_date(lv_start_date) ;
550:
551: hr_utility_trace('ld_start_date '||ld_start_date);
552:
553: ELSE
554:
555: SELECT fnd_date.date_to_canonical(sysdate)
559: END IF; -- p_gre_id IS NOT NULL
560:
561: END IF; -- ld_start_date <> hr_api.g_sot
562:
563: hr_utility_trace('lv_start_date '||lv_start_date);
564:
565: RETURN lv_start_date ;
566:
567: END get_start_date;
631: BEGIN
632:
633: lv_procedure_name := 'get_payroll_action_info';
634:
635: hr_utility.set_location(gv_package || lv_procedure_name, 10);
636: ln_step := 1;
637:
638: OPEN c_payroll_action_info(p_payroll_action_id);
639: FETCH c_payroll_action_info INTO ln_business_group_id
643: ,lv_mode
644: ,lv_periodic_end_date;
645: CLOSE c_payroll_action_info;
646:
647: hr_utility.set_location(gv_package || lv_procedure_name, 30);
648:
649: IF lv_periodic_end_date IS NOT NULL THEN
650:
651: IF TRUNC(fnd_date.canonical_to_date(lv_end_date)) -
676: p_business_group_id := ln_business_group_id;
677: p_gre_id := ln_gre_id;
678: gv_mode := lv_mode;
679:
680: hr_utility.set_location(gv_package || lv_procedure_name, 50);
681: ln_step := 2;
682:
683: IF gv_mode = 'P' THEN
684:
692: gv_periodic_start_date := lv_start_date;
693:
694: END IF;
695:
696: hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
697:
698: END get_payroll_action_info;
699:
700: /********************************************************************
801: BEGIN
802:
803: lv_procedure_name := 'get_idw';
804:
805: hr_utility.set_location(gv_package || lv_procedure_name, 10);
806: ln_step := 1;
807:
808: ln_min_wage := 0;
809: ln_idw := 0;
809: ln_idw := 0;
810: ln_fixed_idw := 0;
811: ln_variable_idw := 0;
812:
813: hr_utility.set_location(gv_package || lv_procedure_name, 20);
814:
815: -- get the minimum wage for Zone A ( Mexico City )
816:
817: OPEN c_minimum_wage_zonea(p_effective_date);
817: OPEN c_minimum_wage_zonea(p_effective_date);
818: FETCH c_minimum_wage_zonea INTO ln_min_wage;
819: CLOSE c_minimum_wage_zonea;
820:
821: hr_utility.set_location(gv_package || lv_procedure_name, 30);
822:
823:
824: BEGIN
825:
822:
823:
824: BEGIN
825:
826: hr_utility.set_location(gv_package || lv_procedure_name, 40);
827:
828: ln_step := 2;
829:
830: lv_mode := 'REPORT';
837: ,p_fixed_idw => ln_fixed_idw
838: ,p_variable_idw => ln_variable_idw
839: );
840:
841: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
842: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
843: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
844:
845: hr_utility.set_location(gv_package || lv_procedure_name, 50);
838: ,p_variable_idw => ln_variable_idw
839: );
840:
841: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
842: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
843: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
844:
845: hr_utility.set_location(gv_package || lv_procedure_name, 50);
846:
839: );
840:
841: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
842: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
843: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
844:
845: hr_utility.set_location(gv_package || lv_procedure_name, 50);
846:
847: EXCEPTION WHEN others THEN
841: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
842: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
843: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
844:
845: hr_utility.set_location(gv_package || lv_procedure_name, 50);
846:
847: EXCEPTION WHEN others THEN
848: hr_utility.set_location(gv_package || lv_procedure_name, 60);
849: NULL;
844:
845: hr_utility.set_location(gv_package || lv_procedure_name, 50);
846:
847: EXCEPTION WHEN others THEN
848: hr_utility.set_location(gv_package || lv_procedure_name, 60);
849: NULL;
850:
851: END;
852:
860: -- end if
861:
862: IF ln_idw > ( 25 * ln_min_wage ) THEN
863: ln_idw := 25 * ln_min_wage;
864: hr_utility.trace('SS_ARCH get_idw ln_idw > 25 * ln_min_wage');
865: hr_utility.trace('25 times of zone A minimum wage');
866: END IF;
867:
868: hr_utility.set_location(gv_package || lv_procedure_name, 70);
861:
862: IF ln_idw > ( 25 * ln_min_wage ) THEN
863: ln_idw := 25 * ln_min_wage;
864: hr_utility.trace('SS_ARCH get_idw ln_idw > 25 * ln_min_wage');
865: hr_utility.trace('25 times of zone A minimum wage');
866: END IF;
867:
868: hr_utility.set_location(gv_package || lv_procedure_name, 70);
869:
864: hr_utility.trace('SS_ARCH get_idw ln_idw > 25 * ln_min_wage');
865: hr_utility.trace('25 times of zone A minimum wage');
866: END IF;
867:
868: hr_utility.set_location(gv_package || lv_procedure_name, 70);
869:
870: -- round to 2 decimal and archive
871:
872: p_fixed_idw := ROUND(LEAST(ln_fixed_idw, 25 * ln_min_wage), 2);
871:
872: p_fixed_idw := ROUND(LEAST(ln_fixed_idw, 25 * ln_min_wage), 2);
873: p_variable_idw := ROUND(LEAST(ln_variable_idw, 25 * ln_min_wage), 2);
874:
875: hr_utility.trace('SS_ARCH get_idw p_fixed_idw: '|| p_fixed_idw);
876: hr_utility.trace('SS_ARCH get_idw p_variable_idw: '|| p_variable_idw);
877:
878: ln_idw := round(ln_idw,2);
879:
872: p_fixed_idw := ROUND(LEAST(ln_fixed_idw, 25 * ln_min_wage), 2);
873: p_variable_idw := ROUND(LEAST(ln_variable_idw, 25 * ln_min_wage), 2);
874:
875: hr_utility.trace('SS_ARCH get_idw p_fixed_idw: '|| p_fixed_idw);
876: hr_utility.trace('SS_ARCH get_idw p_variable_idw: '|| p_variable_idw);
877:
878: ln_idw := round(ln_idw,2);
879:
880: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
876: hr_utility.trace('SS_ARCH get_idw p_variable_idw: '|| p_variable_idw);
877:
878: ln_idw := round(ln_idw,2);
879:
880: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
881:
882: RETURN ln_idw;
883:
884: EXCEPTION
885: WHEN others THEN
886: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
887: gv_package || lv_procedure_name;
888:
889: hr_utility_trace(lv_error_message || '-' || sqlerrm);
890:
891: lv_error_message :=
892: pay_emp_action_arch.set_error_message(lv_error_message);
893:
890:
891: lv_error_message :=
892: pay_emp_action_arch.set_error_message(lv_error_message);
893:
894: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
895: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
896: hr_utility.raise_error;
897:
898: END get_idw;
891: lv_error_message :=
892: pay_emp_action_arch.set_error_message(lv_error_message);
893:
894: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
895: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
896: hr_utility.raise_error;
897:
898: END get_idw;
899:
892: pay_emp_action_arch.set_error_message(lv_error_message);
893:
894: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
895: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
896: hr_utility.raise_error;
897:
898: END get_idw;
899:
900: PROCEDURE arch_pay_action_level_data(
941: BEGIN
942:
943: lv_procedure_name := 'arch_pay_action_level_data';
944:
945: hr_utility.set_location(gv_package || lv_procedure_name, 10);
946: ln_step := 1;
947:
948: OPEN c_get_org_information(p_tax_unit_id);
949: FETCH c_get_org_information INTO lv_soc_sec_id
951: ,lv_waybill_no
952: ,lv_transmitter_gre_id;
953: CLOSE c_get_org_information;
954:
955: hr_utility.set_location(gv_package || lv_procedure_name, 20);
956: ln_step := 2;
957:
958: OPEN c_org_name(p_tax_unit_id);
959: FETCH c_org_name INTO lv_gre_name;
958: OPEN c_org_name(p_tax_unit_id);
959: FETCH c_org_name INTO lv_gre_name;
960: CLOSE c_org_name;
961:
962: hr_utility.set_location(gv_package || lv_procedure_name, 30);
963: ln_step := 3;
964:
965: IF lv_transmitter = 'Y' THEN
966:
970: ELSE
971:
972: IF lv_transmitter IS NOT NULL THEN
973:
974: hr_utility.set_location(gv_package || lv_procedure_name, 40);
975: ln_step := 4;
976:
977: OPEN c_org_name(lv_transmitter_gre_id);
978: FETCH c_org_name INTO lv_transmitter_gre_name;
977: OPEN c_org_name(lv_transmitter_gre_id);
978: FETCH c_org_name INTO lv_transmitter_gre_name;
979: CLOSE c_org_name;
980:
981: hr_utility.set_location(gv_package || lv_procedure_name, 50);
982: ln_step := 5;
983:
984: OPEN c_waybill_of_trnsmtr(lv_transmitter_gre_id);
985: FETCH c_waybill_of_trnsmtr INTO lv_waybill_no;
988: END IF; -- lv_transmitter
989:
990: END IF;
991:
992: hr_utility.set_location(gv_package || lv_procedure_name, 60);
993: ln_step := 6;
994:
995: ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
996:
1020: WHEN others THEN
1021: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1022: gv_package || lv_procedure_name;
1023:
1024: hr_utility_trace(lv_error_message || '-' || sqlerrm);
1025:
1026: lv_error_message :=
1027: pay_emp_action_arch.set_error_message(lv_error_message);
1028:
1025:
1026: lv_error_message :=
1027: pay_emp_action_arch.set_error_message(lv_error_message);
1028:
1029: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1030: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1031: hr_utility.raise_error;
1032:
1033: END arch_pay_action_level_data;
1026: lv_error_message :=
1027: pay_emp_action_arch.set_error_message(lv_error_message);
1028:
1029: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1030: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1031: hr_utility.raise_error;
1032:
1033: END arch_pay_action_level_data;
1034:
1027: pay_emp_action_arch.set_error_message(lv_error_message);
1028:
1029: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1030: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1031: hr_utility.raise_error;
1032:
1033: END arch_pay_action_level_data;
1034:
1035: PROCEDURE chk_person_rec_chng (
1047: BEGIN
1048:
1049: lv_procedure_name := 'chk_person_rec_chng';
1050:
1051: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1052: ln_step := 1;
1053:
1054:
1055: FOR i IN p_per_events.FIRST..p_per_events.LAST LOOP
1053:
1054:
1055: FOR i IN p_per_events.FIRST..p_per_events.LAST LOOP
1056:
1057: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1058: ln_step := 2;
1059:
1060: lv_change_values := p_per_events(i).change_values ;
1061:
1064:
1065: lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,
1066: INSTR(lv_change_values,'->')+3)));
1067:
1068: hr_utility_trace('lv_change_values : '||lv_change_values);
1069: hr_utility_trace('lv_old_value : '||lv_old_value);
1070: hr_utility_trace('lv_new_value : '||lv_new_value);
1071: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1072:
1065: lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,
1066: INSTR(lv_change_values,'->')+3)));
1067:
1068: hr_utility_trace('lv_change_values : '||lv_change_values);
1069: hr_utility_trace('lv_old_value : '||lv_old_value);
1070: hr_utility_trace('lv_new_value : '||lv_new_value);
1071: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1072:
1073: IF p_per_events(i).column_name in ( 'LAST_NAME', 'FIRST_NAME',
1066: INSTR(lv_change_values,'->')+3)));
1067:
1068: hr_utility_trace('lv_change_values : '||lv_change_values);
1069: hr_utility_trace('lv_old_value : '||lv_old_value);
1070: hr_utility_trace('lv_new_value : '||lv_new_value);
1071: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1072:
1073: IF p_per_events(i).column_name in ( 'LAST_NAME', 'FIRST_NAME',
1074: 'MIDDLE_NAMES', 'PER_INFORMATION1',
1067:
1068: hr_utility_trace('lv_change_values : '||lv_change_values);
1069: hr_utility_trace('lv_old_value : '||lv_old_value);
1070: hr_utility_trace('lv_new_value : '||lv_new_value);
1071: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1072:
1073: IF p_per_events(i).column_name in ( 'LAST_NAME', 'FIRST_NAME',
1074: 'MIDDLE_NAMES', 'PER_INFORMATION1',
1075: 'PER_INFORMATION4') -- Bug 5885473
1085: END IF;
1086:
1087: END LOOP;
1088:
1089: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1090: ln_step := 3;
1091:
1092: EXCEPTION
1093: WHEN others THEN
1093: WHEN others THEN
1094: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1095: gv_package || lv_procedure_name;
1096:
1097: hr_utility_trace(lv_error_message || '-' || sqlerrm);
1098:
1099: lv_error_message :=
1100: pay_emp_action_arch.set_error_message(lv_error_message);
1101:
1098:
1099: lv_error_message :=
1100: pay_emp_action_arch.set_error_message(lv_error_message);
1101:
1102: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1103: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1104: hr_utility.raise_error;
1105:
1106: END chk_person_rec_chng;
1099: lv_error_message :=
1100: pay_emp_action_arch.set_error_message(lv_error_message);
1101:
1102: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1103: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1104: hr_utility.raise_error;
1105:
1106: END chk_person_rec_chng;
1107:
1100: pay_emp_action_arch.set_error_message(lv_error_message);
1101:
1102: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1103: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1104: hr_utility.raise_error;
1105:
1106: END chk_person_rec_chng;
1107:
1108: PROCEDURE arch_other_transactions (
1459: p_gre_id) THEN
1460: -- Archive a 07 only if current person is not a new hire
1461: -- (Bug 6021768)
1462: lb_new_hire := FALSE;
1463: hr_utility_trace('Checking for hire ');
1464: hr_utility_trace('Archived SS transaxtions ' ||pay_mx_soc_sec_archive.lrr_act_tab.COUNT());
1465: hr_utility_trace('p_effective_date ' || p_effective_date);
1466:
1467: OPEN c_first_sal_date;
1460: -- Archive a 07 only if current person is not a new hire
1461: -- (Bug 6021768)
1462: lb_new_hire := FALSE;
1463: hr_utility_trace('Checking for hire ');
1464: hr_utility_trace('Archived SS transaxtions ' ||pay_mx_soc_sec_archive.lrr_act_tab.COUNT());
1465: hr_utility_trace('p_effective_date ' || p_effective_date);
1466:
1467: OPEN c_first_sal_date;
1468: FETCH c_first_sal_date into ld_effective_date;
1461: -- (Bug 6021768)
1462: lb_new_hire := FALSE;
1463: hr_utility_trace('Checking for hire ');
1464: hr_utility_trace('Archived SS transaxtions ' ||pay_mx_soc_sec_archive.lrr_act_tab.COUNT());
1465: hr_utility_trace('p_effective_date ' || p_effective_date);
1466:
1467: OPEN c_first_sal_date;
1468: FETCH c_first_sal_date into ld_effective_date;
1469: CLOSE c_first_sal_date;
1470:
1471: IF pay_mx_soc_sec_archive.lrr_act_tab.COUNT() > 0 THEN --Bug 6060052
1472: FOR cntr IN pay_mx_soc_sec_archive.lrr_act_tab.FIRST()..
1473: pay_mx_soc_sec_archive.lrr_act_tab.LAST() LOOP
1474: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category ' ||
1475: pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category);
1476: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) ' ||
1477: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2);
1478: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 ' ||
1472: FOR cntr IN pay_mx_soc_sec_archive.lrr_act_tab.FIRST()..
1473: pay_mx_soc_sec_archive.lrr_act_tab.LAST() LOOP
1474: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category ' ||
1475: pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category);
1476: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) ' ||
1477: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2);
1478: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 ' ||
1479: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4);
1480:
1474: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category ' ||
1475: pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category);
1476: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) ' ||
1477: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2);
1478: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 ' ||
1479: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4);
1480:
1481: IF pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category
1482: = 'MX SS TRANSACTIONS' AND
1493: = 'MX SS TRANSACTIONS' AND
1494: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 = '08'
1495: THEN
1496: lb_new_hire := TRUE;
1497: hr_utility_trace('Going to run the get_idw to get the correct IDW for the first salary.');
1498: ln_idw := get_idw( p_assignment_id => p_assignment_id
1499: ,p_tax_unit_id => p_gre_id
1500: ,p_effective_date => ld_effective_date
1501: ,p_fixed_idw => ln_fix_idw
1500: ,p_effective_date => ld_effective_date
1501: ,p_fixed_idw => ln_fix_idw
1502: ,p_variable_idw => ln_var_idw );
1503: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info8:= ln_idw;
1504: hr_utility_trace('Exiting the ELSIF part to prevent 07 transaction');
1505: EXIT;
1506: END IF;
1507: END LOOP;
1508: END IF;
1516: fix_var_idw(ln_count).idw_type := 'VARIABLE';
1517: fix_var_idw(ln_count).idw_date := p_effective_date;
1518: END IF;
1519: ELSE
1520: hr_utility_trace('Current person is a new hire. 07 effective '||
1521: 'on '||fnd_date.date_to_canonical(p_effective_date)
1522: ||' will not be archived.');
1523: END IF;
1524: END IF;
1536: OPEN c_all_ele_entries (p_effective_date);
1537: LOOP
1538: FETCH c_all_ele_entries INTO ln_element_entry_id;
1539: EXIT WHEN c_all_ele_entries%NOTFOUND;
1540: hr_utility_trace('Element Entry ID = '||
1541: ln_element_entry_id);
1542:
1543: OPEN c_ele_type_id (ln_element_entry_id,
1544: p_effective_date);
1554: FETCH c_ele_extra_info INTO lv_idw_type;
1555: CLOSE c_ele_extra_info;
1556:
1557: IF lv_idw_type IS NOT NULL THEN
1558: hr_utility_trace('element entry id '||
1559: ln_element_entry_id||' has following IDW info: -');
1560: hr_utility_trace('IDW type = '||lv_idw_type);
1561: hr_utility_trace('IDW effective date = '||
1562: fnd_date.date_to_canonical(p_effective_date));
1556:
1557: IF lv_idw_type IS NOT NULL THEN
1558: hr_utility_trace('element entry id '||
1559: ln_element_entry_id||' has following IDW info: -');
1560: hr_utility_trace('IDW type = '||lv_idw_type);
1561: hr_utility_trace('IDW effective date = '||
1562: fnd_date.date_to_canonical(p_effective_date));
1563: ELSE
1564: hr_utility_trace('element entry id '||
1557: IF lv_idw_type IS NOT NULL THEN
1558: hr_utility_trace('element entry id '||
1559: ln_element_entry_id||' has following IDW info: -');
1560: hr_utility_trace('IDW type = '||lv_idw_type);
1561: hr_utility_trace('IDW effective date = '||
1562: fnd_date.date_to_canonical(p_effective_date));
1563: ELSE
1564: hr_utility_trace('element entry id '||
1565: ln_element_entry_id||' has no IDW information.');
1560: hr_utility_trace('IDW type = '||lv_idw_type);
1561: hr_utility_trace('IDW effective date = '||
1562: fnd_date.date_to_canonical(p_effective_date));
1563: ELSE
1564: hr_utility_trace('element entry id '||
1565: ln_element_entry_id||' has no IDW information.');
1566: END IF;
1567: cache_IDW_date (lv_idw_type,
1568: p_effective_date);
1692: THEN
1693: trn(ln_trn_cnt).type := p_tran_type;
1694: trn(ln_trn_cnt).date := fnd_date.date_to_canonical(
1695: p_effective_end_date + 1);
1696: hr_utility_trace('Transaction '|| p_tran_type ||' found.');
1697: lb_tran_found := TRUE;
1698: EXIT;
1699: END IF;
1700: END LOOP;
1729: BEGIN -- Main
1730:
1731: lv_procedure_name := 'arch_other_transactions';
1732:
1733: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1734: ln_step := 1;
1735:
1736: fix_var_idw.DELETE;
1737: fix_var_idw_uniq.DELETE;
1736: fix_var_idw.DELETE;
1737: fix_var_idw_uniq.DELETE;
1738: prev_eff_date := fnd_date.canonical_to_date('0001/01/01');
1739:
1740: hr_utility.set_location(gv_package || lv_procedure_name, 910);
1741: ln_step := 91;
1742:
1743: OPEN c_get_org_information(p_gre_id);
1744: FETCH c_get_org_information INTO lv_employer_ss_id;
1743: OPEN c_get_org_information(p_gre_id);
1744: FETCH c_get_org_information INTO lv_employer_ss_id;
1745: CLOSE c_get_org_information;
1746:
1747: hr_utility.set_location(gv_package || lv_procedure_name, 920);
1748: ln_step := 92;
1749:
1750: hr_utility.set_location(gv_package || lv_procedure_name, 930);
1751: ln_step := 93;
1746:
1747: hr_utility.set_location(gv_package || lv_procedure_name, 920);
1748: ln_step := 92;
1749:
1750: hr_utility.set_location(gv_package || lv_procedure_name, 930);
1751: ln_step := 93;
1752:
1753: IF gv_IDW_calc_method = 'B' THEN
1754: /*-- IDW factor table support --*/
1755: OPEN c_IDW_events;
1756: LOOP
1757: FETCH c_IDW_events INTO ld_effective_date;
1758: EXIT WHEN c_IDW_events%NOTFOUND;
1759: hr_utility_trace('Timestamp of IDW table update event = '||
1760: fnd_date.date_to_canonical(ld_effective_date));
1761: parse_all_ele_entries (ld_effective_date);
1762: END LOOP;
1763: CLOSE c_IDW_events;
1765: /*-- Support for change in seniority --*/
1766: IF pay_mx_soc_sec_archive.seniority_changed (p_person_id,
1767: p_end_date,
1768: p_start_date) = 'Y' THEN
1769: hr_utility_trace ('Seniority of person '||p_person_id||
1770: ' has changed since last archiver run. IDW will'||
1771: ' be recomputed for this person.');
1772: -- Get hire anniversary date
1773: ld_hire_anniversary := hr_mx_utility.get_hire_anniversary(
1772: -- Get hire anniversary date
1773: ld_hire_anniversary := hr_mx_utility.get_hire_anniversary(
1774: p_person_id,
1775: p_end_date);
1776: hr_utility_trace ('Hire anniversary date of person '||p_person_id||
1777: ' = '||fnd_date.date_to_canonical(ld_hire_anniversary));
1778:
1779: -- Calculate anniversary date in current year
1780: SELECT ADD_MONTHS (TRUNC (p_end_date, 'Y'),
1784: (ld_hire_anniversary - TRUNC (ld_hire_anniversary, 'MM'))
1785: INTO ld_anniversary_date
1786: FROM dual;
1787:
1788: hr_utility_trace ('Anniversary date of person '||p_person_id||
1789: ' in the year of archiver run = '||
1790: fnd_date.date_to_canonical(ld_anniversary_date));
1791:
1792: parse_all_ele_entries (ld_anniversary_date);
1809: EXIT WHEN c_ele_entries%NOTFOUND;
1810: /* Adding event qualification mechanism so that only those events that
1811: belong to current GRE are picked for archival. (Bug 5921945)*/
1812:
1813: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1814: ln_step := 2;
1815:
1816: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1817: hr_utility_trace('lv_change_values :' || lv_change_values);
1812:
1813: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1814: ln_step := 2;
1815:
1816: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1817: hr_utility_trace('lv_change_values :' || lv_change_values);
1818: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1819: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1820: hr_utility_trace('lv_event_type :' || lv_event_type);
1813: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1814: ln_step := 2;
1815:
1816: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1817: hr_utility_trace('lv_change_values :' || lv_change_values);
1818: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1819: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1820: hr_utility_trace('lv_event_type :' || lv_event_type);
1821:
1814: ln_step := 2;
1815:
1816: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1817: hr_utility_trace('lv_change_values :' || lv_change_values);
1818: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1819: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1820: hr_utility_trace('lv_event_type :' || lv_event_type);
1821:
1822: OPEN c_ele_type_id ( ln_element_entry_id
1815:
1816: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1817: hr_utility_trace('lv_change_values :' || lv_change_values);
1818: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1819: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1820: hr_utility_trace('lv_event_type :' || lv_event_type);
1821:
1822: OPEN c_ele_type_id ( ln_element_entry_id
1823: ,ld_effective_date );
1816: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1817: hr_utility_trace('lv_change_values :' || lv_change_values);
1818: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1819: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1820: hr_utility_trace('lv_event_type :' || lv_event_type);
1821:
1822: OPEN c_ele_type_id ( ln_element_entry_id
1823: ,ld_effective_date );
1824: FETCH c_ele_type_id INTO ln_element_type_id
1826: ,ld_ee_eff_start_date
1827: ,ld_ee_eff_end_date;
1828: CLOSE c_ele_type_id;
1829:
1830: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1831: ln_step := 3;
1832:
1833: hr_utility_trace('ln_element_type_id :' || ln_element_type_id);
1834:
1829:
1830: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1831: ln_step := 3;
1832:
1833: hr_utility_trace('ln_element_type_id :' || ln_element_type_id);
1834:
1835: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1836: ln_step := 4;
1837:
1831: ln_step := 3;
1832:
1833: hr_utility_trace('ln_element_type_id :' || ln_element_type_id);
1834:
1835: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1836: ln_step := 4;
1837:
1838: lv_idw_type := NULL;
1839: OPEN c_ele_extra_info (ln_element_type_id, ld_effective_date);
1842: INTO lv_idw_type;
1843:
1844: CLOSE c_ele_extra_info;
1845:
1846: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1847: ln_step := 5;
1848:
1849: hr_utility_trace('IDW_TYPE :' || lv_idw_type);
1850:
1845:
1846: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1847: ln_step := 5;
1848:
1849: hr_utility_trace('IDW_TYPE :' || lv_idw_type);
1850:
1851: IF lv_idw_type IS NOT NULL THEN
1852: cache_IDW_date (lv_idw_type,
1853: ld_effective_date);
1854: END IF;
1855:
1856: IF lv_creator_type = 'A' THEN
1857:
1858: hr_utility.set_location(gv_package || lv_procedure_name, 60);
1859: ln_step := 6;
1860:
1861: ln_abs_attend_type_id := 0;
1862:
1869: ,ld_ee_eff_end_date );
1870: --,p_start_date
1871: --,p_end_date );
1872:
1873: hr_utility.set_location(gv_package || lv_procedure_name, 70);
1874: ln_step := 7;
1875:
1876: LOOP
1877:
1885: ,lv_disability_id;
1886:
1887: EXIT WHEN c_abs_info%NOTFOUND;
1888:
1889: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1890: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1891: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1886:
1887: EXIT WHEN c_abs_info%NOTFOUND;
1888:
1889: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1890: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1891: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1894: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1887: EXIT WHEN c_abs_info%NOTFOUND;
1888:
1889: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1890: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1891: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1894: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1895: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1888:
1889: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1890: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1891: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1894: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1895: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1896: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1889: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1890: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1891: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1894: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1895: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1896: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1897:
1890: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1891: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1894: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1895: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1896: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1897:
1898: IF event_qualified (p_person_id,
1891: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1894: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1895: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1896: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1897:
1898: IF event_qualified (p_person_id,
1899: p_assignment_id,
1892: hr_utility_trace('ld_date_start : '|| ld_date_start);
1893: hr_utility_trace('ld_date_end : '|| ld_date_end);
1894: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1895: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1896: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1897:
1898: IF event_qualified (p_person_id,
1899: p_assignment_id,
1900: ld_date_start,
1958: OPEN c_get_infonavit(ln_element_type_id);
1959: FETCH c_get_infonavit INTO lv_infonavit;
1960: CLOSE c_get_infonavit;
1961:
1962: hr_utility_trace('lv_infonavit : '|| nvl(lv_infonavit, 'NULL'));
1963:
1964: IF lv_infonavit = 'INFONAVIT' THEN
1965:
1966: lb_tran_16_found := FALSE;
1970: ,ld_ee_eff_start_date
1971: ,ld_ee_eff_end_date)
1972: LOOP
1973:
1974: hr_utility_trace('----------------------');
1975: hr_utility_trace('name : '|| infonavit.name);
1976: hr_utility_trace('screen_entry_value : '||
1977: infonavit.screen_entry_value);
1978: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
1971: ,ld_ee_eff_end_date)
1972: LOOP
1973:
1974: hr_utility_trace('----------------------');
1975: hr_utility_trace('name : '|| infonavit.name);
1976: hr_utility_trace('screen_entry_value : '||
1977: infonavit.screen_entry_value);
1978: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
1979: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
1972: LOOP
1973:
1974: hr_utility_trace('----------------------');
1975: hr_utility_trace('name : '|| infonavit.name);
1976: hr_utility_trace('screen_entry_value : '||
1977: infonavit.screen_entry_value);
1978: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
1979: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
1980: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
1974: hr_utility_trace('----------------------');
1975: hr_utility_trace('name : '|| infonavit.name);
1976: hr_utility_trace('screen_entry_value : '||
1977: infonavit.screen_entry_value);
1978: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
1979: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
1980: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
1981: hr_utility_trace('effective_start_date : '||
1982: infonavit.effective_start_date);
1975: hr_utility_trace('name : '|| infonavit.name);
1976: hr_utility_trace('screen_entry_value : '||
1977: infonavit.screen_entry_value);
1978: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
1979: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
1980: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
1981: hr_utility_trace('effective_start_date : '||
1982: infonavit.effective_start_date);
1983: hr_utility_trace('effective_end_date : '||
1976: hr_utility_trace('screen_entry_value : '||
1977: infonavit.screen_entry_value);
1978: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
1979: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
1980: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
1981: hr_utility_trace('effective_start_date : '||
1982: infonavit.effective_start_date);
1983: hr_utility_trace('effective_end_date : '||
1984: infonavit.effective_end_date);
1977: infonavit.screen_entry_value);
1978: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
1979: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
1980: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
1981: hr_utility_trace('effective_start_date : '||
1982: infonavit.effective_start_date);
1983: hr_utility_trace('effective_end_date : '||
1984: infonavit.effective_end_date);
1985:
1979: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
1980: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
1981: hr_utility_trace('effective_start_date : '||
1982: infonavit.effective_start_date);
1983: hr_utility_trace('effective_end_date : '||
1984: infonavit.effective_end_date);
1985:
1986: IF infonavit.name = 'Credit Number' THEN
1987:
2053: ,ld_ee_eff_start_date
2054: ,ld_ee_eff_end_date
2055: ,ln_trn_cnt);
2056: lb_tran_16_found := TRUE;
2057: hr_utility_trace ('Transaction 16 found.');
2058:
2059: END IF;
2060:
2061: ELSIF infonavit.name = 'Transaction Type' AND
2066:
2067: IF infonavit.screen_entry_value = 'CREDIT_BEGIN' THEN
2068:
2069: trn(ln_trn_cnt).type := '15';
2070: hr_utility_trace ('Transaction 15 found.');
2071:
2072: ELSIF infonavit.screen_entry_value = 'DISC_RESUME' THEN
2073:
2074: trn(ln_trn_cnt).type := '17';
2071:
2072: ELSIF infonavit.screen_entry_value = 'DISC_RESUME' THEN
2073:
2074: trn(ln_trn_cnt).type := '17';
2075: hr_utility_trace ('Transaction 17 found.');
2076:
2077: END IF;
2078:
2079: -- gv_credit_start_date is already in canonical date format.
2112: FOR i in fix_var_idw.FIRST..fix_var_idw.LAST
2113: LOOP
2114:
2115: lv_fix_var_idw_found := 'N';
2116: hr_utility_trace('fix_var_idw(i).idw_type '||i||': '||
2117: fix_var_idw(i).idw_type );
2118: hr_utility_trace('fix_var_idw(i).idw_date '||i||': '||
2119: fix_var_idw(i).idw_date );
2120: hr_utility_trace('---------------------------------------');
2114:
2115: lv_fix_var_idw_found := 'N';
2116: hr_utility_trace('fix_var_idw(i).idw_type '||i||': '||
2117: fix_var_idw(i).idw_type );
2118: hr_utility_trace('fix_var_idw(i).idw_date '||i||': '||
2119: fix_var_idw(i).idw_date );
2120: hr_utility_trace('---------------------------------------');
2121:
2122: IF fix_var_idw_uniq.COUNT > 0 THEN
2116: hr_utility_trace('fix_var_idw(i).idw_type '||i||': '||
2117: fix_var_idw(i).idw_type );
2118: hr_utility_trace('fix_var_idw(i).idw_date '||i||': '||
2119: fix_var_idw(i).idw_date );
2120: hr_utility_trace('---------------------------------------');
2121:
2122: IF fix_var_idw_uniq.COUNT > 0 THEN
2123:
2124: FOR j in fix_var_idw_uniq.FIRST..fix_var_idw_uniq.LAST
2122: IF fix_var_idw_uniq.COUNT > 0 THEN
2123:
2124: FOR j in fix_var_idw_uniq.FIRST..fix_var_idw_uniq.LAST
2125: LOOP
2126: hr_utility_trace('fix_var_idw_uniq(j).idw_type '||j||': '||
2127: fix_var_idw_uniq(j).idw_type );
2128: hr_utility_trace('fix_var_idw_uniq(j).idw_date '||j||': '||
2129: fix_var_idw_uniq(j).idw_date );
2130:
2124: FOR j in fix_var_idw_uniq.FIRST..fix_var_idw_uniq.LAST
2125: LOOP
2126: hr_utility_trace('fix_var_idw_uniq(j).idw_type '||j||': '||
2127: fix_var_idw_uniq(j).idw_type );
2128: hr_utility_trace('fix_var_idw_uniq(j).idw_date '||j||': '||
2129: fix_var_idw_uniq(j).idw_date );
2130:
2131: IF fix_var_idw(i).idw_type = fix_var_idw_uniq(j).idw_type AND
2132: fix_var_idw(i).idw_date = fix_var_idw_uniq(j).idw_date THEN
2131: IF fix_var_idw(i).idw_type = fix_var_idw_uniq(j).idw_type AND
2132: fix_var_idw(i).idw_date = fix_var_idw_uniq(j).idw_date THEN
2133:
2134: lv_fix_var_idw_found := 'Y';
2135: hr_utility_trace('FOUND');
2136:
2137: END IF;
2138:
2139: END LOOP;
2139: END LOOP;
2140:
2141: END IF;
2142:
2143: hr_utility_trace('---------------------------------------');
2144:
2145: IF lv_fix_var_idw_found = 'N' AND
2146: fix_var_idw(i).idw_type = 'FIXED' THEN
2147:
2144:
2145: IF lv_fix_var_idw_found = 'N' AND
2146: fix_var_idw(i).idw_type = 'FIXED' THEN
2147:
2148: hr_utility_trace('NOT FOUND');
2149: hr_utility_trace(' ');
2150: ln_count := fix_var_idw_uniq.COUNT;
2151: fix_var_idw_uniq(ln_count).idw_type := fix_var_idw(i).idw_type;
2152: fix_var_idw_uniq(ln_count).idw_date := fix_var_idw(i).idw_date;
2145: IF lv_fix_var_idw_found = 'N' AND
2146: fix_var_idw(i).idw_type = 'FIXED' THEN
2147:
2148: hr_utility_trace('NOT FOUND');
2149: hr_utility_trace(' ');
2150: ln_count := fix_var_idw_uniq.COUNT;
2151: fix_var_idw_uniq(ln_count).idw_type := fix_var_idw(i).idw_type;
2152: fix_var_idw_uniq(ln_count).idw_date := fix_var_idw(i).idw_date;
2153:
2173: ,p_effective_date => fix_var_idw(i).idw_date
2174: ,p_fixed_idw => ln_fixed_idw
2175: ,p_variable_idw => ln_variable_idw );
2176:
2177: hr_utility.trace('SS_ARCH other TRN ln_idw: '||ln_idw);
2178: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2179: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2180: ln_variable_idw);
2181:
2174: ,p_fixed_idw => ln_fixed_idw
2175: ,p_variable_idw => ln_variable_idw );
2176:
2177: hr_utility.trace('SS_ARCH other TRN ln_idw: '||ln_idw);
2178: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2179: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2180: ln_variable_idw);
2181:
2182: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
2175: ,p_variable_idw => ln_variable_idw );
2176:
2177: hr_utility.trace('SS_ARCH other TRN ln_idw: '||ln_idw);
2178: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2179: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2180: ln_variable_idw);
2181:
2182: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
2183: ln_step := 203;
2178: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2179: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2180: ln_variable_idw);
2181:
2182: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
2183: ln_step := 203;
2184:
2185: END IF;
2186:
2218: fnd_date.canonical_to_date(gv_periodic_end_date) + 1
2219: ,p_fixed_idw => ln_fixed_idw
2220: ,p_variable_idw => ln_variable_idw );
2221:
2222: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_idw: '||ln_idw);
2223: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_fixed_idw: '||
2224: ln_fixed_idw);
2225: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2226: ln_variable_idw);
2219: ,p_fixed_idw => ln_fixed_idw
2220: ,p_variable_idw => ln_variable_idw );
2221:
2222: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_idw: '||ln_idw);
2223: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_fixed_idw: '||
2224: ln_fixed_idw);
2225: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2226: ln_variable_idw);
2227:
2221:
2222: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_idw: '||ln_idw);
2223: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_fixed_idw: '||
2224: ln_fixed_idw);
2225: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2226: ln_variable_idw);
2227:
2228: hr_utility.set_location(gv_package || lv_procedure_name, 2040);
2229: ln_step := 204;
2224: ln_fixed_idw);
2225: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2226: ln_variable_idw);
2227:
2228: hr_utility.set_location(gv_package || lv_procedure_name, 2040);
2229: ln_step := 204;
2230:
2231: ln_trn_cnt := trn.COUNT;
2232:
2238: trn(ln_trn_cnt).abs_days := NULL;
2239: trn(ln_trn_cnt).idw_vol_contr := ln_idw;
2240: trn(ln_trn_cnt).salary_type := 'VARIABLE';
2241:
2242: hr_utility.set_location(gv_package || lv_procedure_name, 2050);
2243:
2244: END IF; -- gv_variable_idw = 'Y'
2245:
2246: IF trn.COUNT > 0 THEN
2313: WHEN others THEN
2314: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
2315: gv_package || lv_procedure_name;
2316:
2317: hr_utility_trace(lv_error_message || '-' || sqlerrm);
2318:
2319: lv_error_message :=
2320: pay_emp_action_arch.set_error_message(lv_error_message);
2321:
2318:
2319: lv_error_message :=
2320: pay_emp_action_arch.set_error_message(lv_error_message);
2321:
2322: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2323: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2324: hr_utility.raise_error;
2325:
2326: END arch_other_transactions;
2319: lv_error_message :=
2320: pay_emp_action_arch.set_error_message(lv_error_message);
2321:
2322: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2323: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2324: hr_utility.raise_error;
2325:
2326: END arch_other_transactions;
2327:
2320: pay_emp_action_arch.set_error_message(lv_error_message);
2321:
2322: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2323: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2324: hr_utility.raise_error;
2325:
2326: END arch_other_transactions;
2327:
2328: PROCEDURE arch_hire_separation (
2526: BEGIN
2527:
2528: lv_procedure_name := 'arch_hire_separation';
2529:
2530: hr_utility.set_location(gv_package || lv_procedure_name, 10);
2531: ln_step := 1;
2532:
2533: lv_leaving_reason := NULL;
2534: lv_idw := NULL;
2540: lv_hire := 'N';
2541:
2542: hire_sep.DELETE;
2543:
2544: hr_utility.set_location(gv_package || lv_procedure_name, 20);
2545: ln_step := 2;
2546:
2547: FOR i IN 1..p_asg_events.COUNT
2548: LOOP
2549:
2550: lv_table_name :=
2551: dated_tbls(p_asg_events(i).dated_table_id).table_name;
2552:
2553: hr_utility_trace('-----------------------------------------------');
2554: hr_utility_trace('Result row :' ||to_char(i));
2555: hr_utility_trace('lv_table_name :' ||lv_table_name );
2556: hr_utility_trace('Datetracked_event: '||
2557: p_asg_events(i).datetracked_event );
2550: lv_table_name :=
2551: dated_tbls(p_asg_events(i).dated_table_id).table_name;
2552:
2553: hr_utility_trace('-----------------------------------------------');
2554: hr_utility_trace('Result row :' ||to_char(i));
2555: hr_utility_trace('lv_table_name :' ||lv_table_name );
2556: hr_utility_trace('Datetracked_event: '||
2557: p_asg_events(i).datetracked_event );
2558: hr_utility_trace('Change_mode : '||
2551: dated_tbls(p_asg_events(i).dated_table_id).table_name;
2552:
2553: hr_utility_trace('-----------------------------------------------');
2554: hr_utility_trace('Result row :' ||to_char(i));
2555: hr_utility_trace('lv_table_name :' ||lv_table_name );
2556: hr_utility_trace('Datetracked_event: '||
2557: p_asg_events(i).datetracked_event );
2558: hr_utility_trace('Change_mode : '||
2559: p_asg_events(i).change_mode );
2552:
2553: hr_utility_trace('-----------------------------------------------');
2554: hr_utility_trace('Result row :' ||to_char(i));
2555: hr_utility_trace('lv_table_name :' ||lv_table_name );
2556: hr_utility_trace('Datetracked_event: '||
2557: p_asg_events(i).datetracked_event );
2558: hr_utility_trace('Change_mode : '||
2559: p_asg_events(i).change_mode );
2560: hr_utility_trace('Effective_date : '||
2554: hr_utility_trace('Result row :' ||to_char(i));
2555: hr_utility_trace('lv_table_name :' ||lv_table_name );
2556: hr_utility_trace('Datetracked_event: '||
2557: p_asg_events(i).datetracked_event );
2558: hr_utility_trace('Change_mode : '||
2559: p_asg_events(i).change_mode );
2560: hr_utility_trace('Effective_date : '||
2561: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2562: hr_utility_trace('dated_table_id : '||
2556: hr_utility_trace('Datetracked_event: '||
2557: p_asg_events(i).datetracked_event );
2558: hr_utility_trace('Change_mode : '||
2559: p_asg_events(i).change_mode );
2560: hr_utility_trace('Effective_date : '||
2561: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2562: hr_utility_trace('dated_table_id : '||
2563: TO_CHAR(p_asg_events(i).dated_table_id));
2564: hr_utility_trace('column_name : '||
2558: hr_utility_trace('Change_mode : '||
2559: p_asg_events(i).change_mode );
2560: hr_utility_trace('Effective_date : '||
2561: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2562: hr_utility_trace('dated_table_id : '||
2563: TO_CHAR(p_asg_events(i).dated_table_id));
2564: hr_utility_trace('column_name : '||
2565: p_asg_events(i).column_name );
2566: hr_utility_trace('Update_type : '||
2560: hr_utility_trace('Effective_date : '||
2561: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2562: hr_utility_trace('dated_table_id : '||
2563: TO_CHAR(p_asg_events(i).dated_table_id));
2564: hr_utility_trace('column_name : '||
2565: p_asg_events(i).column_name );
2566: hr_utility_trace('Update_type : '||
2567: p_asg_events(i).update_type );
2568: hr_utility_trace('old_value : '||
2562: hr_utility_trace('dated_table_id : '||
2563: TO_CHAR(p_asg_events(i).dated_table_id));
2564: hr_utility_trace('column_name : '||
2565: p_asg_events(i).column_name );
2566: hr_utility_trace('Update_type : '||
2567: p_asg_events(i).update_type );
2568: hr_utility_trace('old_value : '||
2569: p_asg_events(i).old_value );
2570: hr_utility_trace('new_value : '||
2564: hr_utility_trace('column_name : '||
2565: p_asg_events(i).column_name );
2566: hr_utility_trace('Update_type : '||
2567: p_asg_events(i).update_type );
2568: hr_utility_trace('old_value : '||
2569: p_asg_events(i).old_value );
2570: hr_utility_trace('new_value : '||
2571: p_asg_events(i).new_value );
2572: hr_utility_trace('change_values : '||
2566: hr_utility_trace('Update_type : '||
2567: p_asg_events(i).update_type );
2568: hr_utility_trace('old_value : '||
2569: p_asg_events(i).old_value );
2570: hr_utility_trace('new_value : '||
2571: p_asg_events(i).new_value );
2572: hr_utility_trace('change_values : '||
2573: p_asg_events(i).change_values );
2574: hr_utility_trace('-----------------------------------------------');
2568: hr_utility_trace('old_value : '||
2569: p_asg_events(i).old_value );
2570: hr_utility_trace('new_value : '||
2571: p_asg_events(i).new_value );
2572: hr_utility_trace('change_values : '||
2573: p_asg_events(i).change_values );
2574: hr_utility_trace('-----------------------------------------------');
2575:
2576: ln_old_gre_id := -9;
2570: hr_utility_trace('new_value : '||
2571: p_asg_events(i).new_value );
2572: hr_utility_trace('change_values : '||
2573: p_asg_events(i).change_values );
2574: hr_utility_trace('-----------------------------------------------');
2575:
2576: ln_old_gre_id := -9;
2577: ln_new_gre_id := -9;
2578:
2593: END IF;
2594:
2595: IF p_asg_events(i).update_type = 'I' THEN
2596:
2597: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2598: ln_step := 3;
2599:
2600: lv_hire := 'Y';
2601: ln_count := hire_sep.COUNT;
2603: hire_sep(ln_count).trn_date := p_asg_events(i).effective_date;
2604:
2605: ELSE
2606:
2607: hr_utility.set_location(gv_package || lv_procedure_name, 40);
2608: ln_step := 4;
2609: hr_utility_trace ('column_name = PER_ALL_ASSIGNMENTS_F.'||
2610: p_asg_events(i).column_name);
2611: IF p_asg_events(i).column_name = 'LOCATION_ID' THEN
2605: ELSE
2606:
2607: hr_utility.set_location(gv_package || lv_procedure_name, 40);
2608: ln_step := 4;
2609: hr_utility_trace ('column_name = PER_ALL_ASSIGNMENTS_F.'||
2610: p_asg_events(i).column_name);
2611: IF p_asg_events(i).column_name = 'LOCATION_ID' THEN
2612:
2613: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2609: hr_utility_trace ('column_name = PER_ALL_ASSIGNMENTS_F.'||
2610: p_asg_events(i).column_name);
2611: IF p_asg_events(i).column_name = 'LOCATION_ID' THEN
2612:
2613: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2614:
2615: /*
2616: * Retrieve the gre off the soft coding keyflex (scl). If a GRE has
2617: * been specified at the scl, location changes are moot.
2633: ,lv_asg_number;
2634:
2635: CLOSE c_get_asg_scl;
2636:
2637: hr_utility.set_location(gv_package || lv_procedure_name, 60);
2638: ln_step := 6;
2639:
2640: ln_old_gre_id :=
2641: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2643: ,p_business_group_id
2644: ,ln_asg_scl_old
2645: ,p_asg_events(i).effective_date - 1);
2646:
2647: hr_utility.set_location(gv_package || lv_procedure_name, 70);
2648:
2649: IF ln_old_gre_id < 0 THEN
2650: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2651: lv_asg_number ||'], Effective Date ['||
2656: ,p_msg_txt => lv_msg_txt
2657: ,p_level => 'I');
2658: END IF;
2659:
2660: hr_utility_trace('Checking wherther the person is applicant');
2661: OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2662: FETCH c_check_per_status INTO lv_check_applicant;
2663:
2664: CLOSE c_check_per_status;
2663:
2664: CLOSE c_check_per_status;
2665:
2666: IF lv_check_applicant = 'Y' THEN
2667: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2668: hr_utility_trace('making -9 for the gre that got by location');
2669: ln_old_gre_id := -9;
2670: END IF;
2671:
2664: CLOSE c_check_per_status;
2665:
2666: IF lv_check_applicant = 'Y' THEN
2667: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2668: hr_utility_trace('making -9 for the gre that got by location');
2669: ln_old_gre_id := -9;
2670: END IF;
2671:
2672: ln_step := 7;
2694:
2695: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2696: THEN
2697:
2698: hr_utility.set_location(gv_package||lv_procedure_name,100);
2699: ln_step := 10;
2700:
2701: ln_count := hire_sep.COUNT;
2702: hire_sep(ln_count).trn_type := 'SEPARATION';
2707:
2708: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2709: THEN
2710:
2711: hr_utility.set_location(gv_package||lv_procedure_name,110);
2712: ln_step := 11;
2713:
2714: ln_count := hire_sep.COUNT;
2715: hire_sep(ln_count).trn_type := 'HIRE';
2722:
2723:
2724: ELSIF p_asg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' THEN
2725:
2726: hr_utility.set_location(gv_package||lv_procedure_name,120);
2727: ln_step := 12;
2728: hr_utility_trace('Inside get_transaction and sof_coding :');
2729: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2730: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2724: ELSIF p_asg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' THEN
2725:
2726: hr_utility.set_location(gv_package||lv_procedure_name,120);
2727: ln_step := 12;
2728: hr_utility_trace('Inside get_transaction and sof_coding :');
2729: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2730: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2731: OPEN c_get_asg_loc(p_assignment_id
2732: ,p_asg_events(i).effective_date - 1);
2725:
2726: hr_utility.set_location(gv_package||lv_procedure_name,120);
2727: ln_step := 12;
2728: hr_utility_trace('Inside get_transaction and sof_coding :');
2729: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2730: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2731: OPEN c_get_asg_loc(p_assignment_id
2732: ,p_asg_events(i).effective_date - 1);
2733:
2726: hr_utility.set_location(gv_package||lv_procedure_name,120);
2727: ln_step := 12;
2728: hr_utility_trace('Inside get_transaction and sof_coding :');
2729: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2730: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2731: OPEN c_get_asg_loc(p_assignment_id
2732: ,p_asg_events(i).effective_date - 1);
2733:
2734: FETCH c_get_asg_loc
2735: INTO ln_asg_loc_old
2736: ,lv_asg_number;
2737:
2738: CLOSE c_get_asg_loc;
2739: hr_utility_trace('Value from the cursor c_get_asg_loc');
2740: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2741: hr_utility_trace('lv_asg_number '||lv_asg_number);
2742: hr_utility.set_location(gv_package||lv_procedure_name,130);
2743: ln_step := 13;
2736: ,lv_asg_number;
2737:
2738: CLOSE c_get_asg_loc;
2739: hr_utility_trace('Value from the cursor c_get_asg_loc');
2740: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2741: hr_utility_trace('lv_asg_number '||lv_asg_number);
2742: hr_utility.set_location(gv_package||lv_procedure_name,130);
2743: ln_step := 13;
2744:
2737:
2738: CLOSE c_get_asg_loc;
2739: hr_utility_trace('Value from the cursor c_get_asg_loc');
2740: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2741: hr_utility_trace('lv_asg_number '||lv_asg_number);
2742: hr_utility.set_location(gv_package||lv_procedure_name,130);
2743: ln_step := 13;
2744:
2745: ln_old_gre_id := NVL(
2738: CLOSE c_get_asg_loc;
2739: hr_utility_trace('Value from the cursor c_get_asg_loc');
2740: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2741: hr_utility_trace('lv_asg_number '||lv_asg_number);
2742: hr_utility.set_location(gv_package||lv_procedure_name,130);
2743: ln_step := 13;
2744:
2745: ln_old_gre_id := NVL(
2746: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2747: ln_asg_loc_old
2748: ,p_business_group_id
2749: ,lv_old_value
2750: ,p_asg_events(i).effective_date - 1), -9);
2751: hr_utility_trace('Checking wherther the person is applicant');
2752: OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2753: FETCH c_check_per_status INTO lv_check_applicant;
2754:
2755: CLOSE c_check_per_status;
2754:
2755: CLOSE c_check_per_status;
2756:
2757: IF lv_check_applicant = 'Y' THEN
2758: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2759: hr_utility_trace('making -9 for the gre that got by location');
2760: ln_old_gre_id := -9;
2761: END IF;
2762:
2755: CLOSE c_check_per_status;
2756:
2757: IF lv_check_applicant = 'Y' THEN
2758: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2759: hr_utility_trace('making -9 for the gre that got by location');
2760: ln_old_gre_id := -9;
2761: END IF;
2762:
2763: hr_utility.set_location(gv_package||lv_procedure_name,135);
2759: hr_utility_trace('making -9 for the gre that got by location');
2760: ln_old_gre_id := -9;
2761: END IF;
2762:
2763: hr_utility.set_location(gv_package||lv_procedure_name,135);
2764: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2765:
2766: IF ln_old_gre_id < 0 THEN
2767: hr_utility_trace('ln_old_gre_id <0');
2760: ln_old_gre_id := -9;
2761: END IF;
2762:
2763: hr_utility.set_location(gv_package||lv_procedure_name,135);
2764: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2765:
2766: IF ln_old_gre_id < 0 THEN
2767: hr_utility_trace('ln_old_gre_id <0');
2768: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2763: hr_utility.set_location(gv_package||lv_procedure_name,135);
2764: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2765:
2766: IF ln_old_gre_id < 0 THEN
2767: hr_utility_trace('ln_old_gre_id <0');
2768: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2769: lv_asg_number ||'], Effective Date ['||
2770: TO_CHAR(p_asg_events(i).effective_date - 1)||']';
2771:
2774: ,p_msg_txt => lv_msg_txt
2775: ,p_level => 'I');
2776: END IF;
2777:
2778: hr_utility.set_location(gv_package||lv_procedure_name,140);
2779: ln_step := 14;
2780: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2781: hr_utility_trace('p_asg_events(i).effective_date :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date ));
2782: OPEN c_get_asg_loc(p_assignment_id
2776: END IF;
2777:
2778: hr_utility.set_location(gv_package||lv_procedure_name,140);
2779: ln_step := 14;
2780: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2781: hr_utility_trace('p_asg_events(i).effective_date :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date ));
2782: OPEN c_get_asg_loc(p_assignment_id
2783: ,p_asg_events(i).effective_date);
2784:
2777:
2778: hr_utility.set_location(gv_package||lv_procedure_name,140);
2779: ln_step := 14;
2780: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2781: hr_utility_trace('p_asg_events(i).effective_date :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date ));
2782: OPEN c_get_asg_loc(p_assignment_id
2783: ,p_asg_events(i).effective_date);
2784:
2785: FETCH c_get_asg_loc
2786: INTO ln_asg_loc_new
2787: ,lv_asg_number;
2788:
2789: CLOSE c_get_asg_loc;
2790: hr_utility_trace('Value from the cursor c_get_asg_loc for new loc');
2791: hr_utility_trace('ln_asg_loc_new '||ln_asg_loc_new);
2792: hr_utility_trace('lv_asg_number '||lv_asg_number);
2793: ln_new_gre_id := NVL(
2794: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2787: ,lv_asg_number;
2788:
2789: CLOSE c_get_asg_loc;
2790: hr_utility_trace('Value from the cursor c_get_asg_loc for new loc');
2791: hr_utility_trace('ln_asg_loc_new '||ln_asg_loc_new);
2792: hr_utility_trace('lv_asg_number '||lv_asg_number);
2793: ln_new_gre_id := NVL(
2794: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2795: ln_asg_loc_new
2788:
2789: CLOSE c_get_asg_loc;
2790: hr_utility_trace('Value from the cursor c_get_asg_loc for new loc');
2791: hr_utility_trace('ln_asg_loc_new '||ln_asg_loc_new);
2792: hr_utility_trace('lv_asg_number '||lv_asg_number);
2793: ln_new_gre_id := NVL(
2794: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2795: ln_asg_loc_new
2796: ,p_business_group_id
2797: ,lv_new_value
2798: ,p_asg_events(i).effective_date), -9);
2799:
2800: IF ln_new_gre_id < 0 THEN
2801: hr_utility_trace('ln_old_gre_id <0');
2802: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2803: lv_asg_number ||'], Effective Date ['||
2804: TO_CHAR(p_asg_events(i).effective_date - 1)||']';
2805:
2807: ,p_msg_name => NULL
2808: ,p_msg_txt => lv_msg_txt
2809: ,p_level => 'I');
2810: END IF;
2811: hr_utility_trace('p_gre_id '||p_gre_id);
2812: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2813: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2814: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2815: THEN
2808: ,p_msg_txt => lv_msg_txt
2809: ,p_level => 'I');
2810: END IF;
2811: hr_utility_trace('p_gre_id '||p_gre_id);
2812: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2813: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2814: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2815: THEN
2816: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2809: ,p_level => 'I');
2810: END IF;
2811: hr_utility_trace('p_gre_id '||p_gre_id);
2812: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2813: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2814: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2815: THEN
2816: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2817: hr_utility.set_location(gv_package||lv_procedure_name,100);
2812: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2813: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2814: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2815: THEN
2816: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2817: hr_utility.set_location(gv_package||lv_procedure_name,100);
2818: ln_step := 10;
2819:
2820: ln_count := hire_sep.COUNT;
2813: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2814: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2815: THEN
2816: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2817: hr_utility.set_location(gv_package||lv_procedure_name,100);
2818: ln_step := 10;
2819:
2820: ln_count := hire_sep.COUNT;
2821: hire_sep(ln_count).trn_type := 'SEPARATION';
2820: ln_count := hire_sep.COUNT;
2821: hire_sep(ln_count).trn_type := 'SEPARATION';
2822: hire_sep(ln_count).trn_date :=
2823: p_asg_events(i).effective_date - 1;
2824: hr_utility_trace('Separation');
2825: END IF;
2826:
2827: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2828: THEN
2825: END IF;
2826:
2827: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2828: THEN
2829: hr_utility_trace('ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id');
2830: hr_utility.set_location(gv_package||lv_procedure_name,110);
2831: ln_step := 11;
2832:
2833: ln_count := hire_sep.COUNT;
2826:
2827: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2828: THEN
2829: hr_utility_trace('ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id');
2830: hr_utility.set_location(gv_package||lv_procedure_name,110);
2831: ln_step := 11;
2832:
2833: ln_count := hire_sep.COUNT;
2834: hire_sep(ln_count).trn_type := 'HIRE';
2833: ln_count := hire_sep.COUNT;
2834: hire_sep(ln_count).trn_type := 'HIRE';
2835: hire_sep(ln_count).trn_date :=
2836: p_asg_events(i).effective_date;
2837: hr_utility_trace('Hire');
2838: END IF;
2839:
2840: ELSIF p_asg_events(i).column_name = 'ASSIGNMENT_STATUS_TYPE_ID' THEN
2841:
2856: FETCH c_asg_status_type INTO lv_new_asg_status ;
2857: CLOSE c_asg_status_type ;
2858: END IF;
2859:
2860: hr_utility_trace( 'old assignment status :'||lv_old_asg_status );
2861: hr_utility_trace( 'new assignment status :'||lv_new_asg_status );
2862:
2863: IF lv_old_asg_status = 'ACTIVE_ASSIGN' AND
2864: lv_new_asg_status = 'TERM_ASSIGN' THEN
2857: CLOSE c_asg_status_type ;
2858: END IF;
2859:
2860: hr_utility_trace( 'old assignment status :'||lv_old_asg_status );
2861: hr_utility_trace( 'new assignment status :'||lv_new_asg_status );
2862:
2863: IF lv_old_asg_status = 'ACTIVE_ASSIGN' AND
2864: lv_new_asg_status = 'TERM_ASSIGN' THEN
2865:
2928: END IF;
2929:
2930: END IF;
2931:
2932: hr_utility_trace('---------------------------------------');
2933: hr_utility_trace('Row in Location :' ||i);
2934: hr_utility_trace('lv_change_values :' ||lv_change_values);
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2929:
2930: END IF;
2931:
2932: hr_utility_trace('---------------------------------------');
2933: hr_utility_trace('Row in Location :' ||i);
2934: hr_utility_trace('lv_change_values :' ||lv_change_values);
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2937: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
2930: END IF;
2931:
2932: hr_utility_trace('---------------------------------------');
2933: hr_utility_trace('Row in Location :' ||i);
2934: hr_utility_trace('lv_change_values :' ||lv_change_values);
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2937: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
2938: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
2931:
2932: hr_utility_trace('---------------------------------------');
2933: hr_utility_trace('Row in Location :' ||i);
2934: hr_utility_trace('lv_change_values :' ||lv_change_values);
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2937: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
2938: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
2939: hr_utility_trace('p_gre_id :' ||p_gre_id );
2932: hr_utility_trace('---------------------------------------');
2933: hr_utility_trace('Row in Location :' ||i);
2934: hr_utility_trace('lv_change_values :' ||lv_change_values);
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2937: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
2938: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
2939: hr_utility_trace('p_gre_id :' ||p_gre_id );
2940:
2933: hr_utility_trace('Row in Location :' ||i);
2934: hr_utility_trace('lv_change_values :' ||lv_change_values);
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2937: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
2938: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
2939: hr_utility_trace('p_gre_id :' ||p_gre_id );
2940:
2941: IF hire_sep.COUNT > 0 THEN
2934: hr_utility_trace('lv_change_values :' ||lv_change_values);
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2937: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
2938: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
2939: hr_utility_trace('p_gre_id :' ||p_gre_id );
2940:
2941: IF hire_sep.COUNT > 0 THEN
2942:
2935: hr_utility_trace('lv_old_value :' ||lv_old_value );
2936: hr_utility_trace('lv_new_value :' ||lv_new_value );
2937: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
2938: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
2939: hr_utility_trace('p_gre_id :' ||p_gre_id );
2940:
2941: IF hire_sep.COUNT > 0 THEN
2942:
2943: hr_utility_trace('ln_count :' ||ln_count);
2939: hr_utility_trace('p_gre_id :' ||p_gre_id );
2940:
2941: IF hire_sep.COUNT > 0 THEN
2942:
2943: hr_utility_trace('ln_count :' ||ln_count);
2944: hr_utility_trace('TRN_TYPE :' ||hire_sep(ln_count).trn_type);
2945: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
2946:
2947: ELSE
2940:
2941: IF hire_sep.COUNT > 0 THEN
2942:
2943: hr_utility_trace('ln_count :' ||ln_count);
2944: hr_utility_trace('TRN_TYPE :' ||hire_sep(ln_count).trn_type);
2945: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
2946:
2947: ELSE
2948:
2941: IF hire_sep.COUNT > 0 THEN
2942:
2943: hr_utility_trace('ln_count :' ||ln_count);
2944: hr_utility_trace('TRN_TYPE :' ||hire_sep(ln_count).trn_type);
2945: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
2946:
2947: ELSE
2948:
2949: hr_utility_trace('NO RECORD FOUND in hire_sep table');
2945: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
2946:
2947: ELSE
2948:
2949: hr_utility_trace('NO RECORD FOUND in hire_sep table');
2950: END IF;
2951:
2952: hr_utility_trace('---------------------------------------');
2953:
2948:
2949: hr_utility_trace('NO RECORD FOUND in hire_sep table');
2950: END IF;
2951:
2952: hr_utility_trace('---------------------------------------');
2953:
2954: END LOOP;
2955:
2956: IF hire_sep.COUNT > 0 THEN
2964: LOOP
2965:
2966: lv_hire_sep_found := 'N';
2967:
2968: hr_utility_trace('hire_sep(i).trn_type '||i||': '||
2969: hire_sep(i).trn_type );
2970: hr_utility_trace('hire_sep(i).trn_date '||i||': '||
2971: hire_sep(i).trn_date );
2972: hr_utility_trace('---------------------------------------');
2966: lv_hire_sep_found := 'N';
2967:
2968: hr_utility_trace('hire_sep(i).trn_type '||i||': '||
2969: hire_sep(i).trn_type );
2970: hr_utility_trace('hire_sep(i).trn_date '||i||': '||
2971: hire_sep(i).trn_date );
2972: hr_utility_trace('---------------------------------------');
2973:
2974: IF hire_sep_uniq.COUNT > 0 THEN
2968: hr_utility_trace('hire_sep(i).trn_type '||i||': '||
2969: hire_sep(i).trn_type );
2970: hr_utility_trace('hire_sep(i).trn_date '||i||': '||
2971: hire_sep(i).trn_date );
2972: hr_utility_trace('---------------------------------------');
2973:
2974: IF hire_sep_uniq.COUNT > 0 THEN
2975:
2976: FOR j in hire_sep_uniq.FIRST..hire_sep_uniq.LAST
2974: IF hire_sep_uniq.COUNT > 0 THEN
2975:
2976: FOR j in hire_sep_uniq.FIRST..hire_sep_uniq.LAST
2977: LOOP
2978: hr_utility_trace('hire_sep_uniq(j).trn_type '||j||': '||
2979: hire_sep_uniq(j).trn_type );
2980: hr_utility_trace('hire_sep_uniq(j).trn_date '||j||': '||
2981: hire_sep_uniq(j).trn_date );
2982:
2976: FOR j in hire_sep_uniq.FIRST..hire_sep_uniq.LAST
2977: LOOP
2978: hr_utility_trace('hire_sep_uniq(j).trn_type '||j||': '||
2979: hire_sep_uniq(j).trn_type );
2980: hr_utility_trace('hire_sep_uniq(j).trn_date '||j||': '||
2981: hire_sep_uniq(j).trn_date );
2982:
2983: IF hire_sep(i).trn_type = hire_sep_uniq(j).trn_type AND
2984: hire_sep(i).trn_date = hire_sep_uniq(j).trn_date THEN
2983: IF hire_sep(i).trn_type = hire_sep_uniq(j).trn_type AND
2984: hire_sep(i).trn_date = hire_sep_uniq(j).trn_date THEN
2985:
2986: lv_hire_sep_found := 'Y';
2987: hr_utility_trace('FOUND');
2988:
2989: END IF;
2990:
2991: END LOOP;
2991: END LOOP;
2992:
2993: END IF;
2994:
2995: hr_utility_trace('---------------------------------------');
2996:
2997: /**************************************************************
2998: ** There should not be a hire/separation record followed by
2999: ** another hire/separation record.
3011:
3012: lv_hire_already_in := 'Y';
3013: lv_sep_already_in := 'N';
3014:
3015: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3016: hr_utility_trace(' ');
3017: ln_count := hire_sep_uniq.COUNT;
3018: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3019: hire_sep_uniq(ln_count).trn_date := hire_sep(i).trn_date;
3012: lv_hire_already_in := 'Y';
3013: lv_sep_already_in := 'N';
3014:
3015: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3016: hr_utility_trace(' ');
3017: ln_count := hire_sep_uniq.COUNT;
3018: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3019: hire_sep_uniq(ln_count).trn_date := hire_sep(i).trn_date;
3020:
3026:
3027: lv_hire_already_in := 'N';
3028: lv_sep_already_in := 'Y';
3029:
3030: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3031: hr_utility_trace(' ');
3032:
3033: ln_count := hire_sep_uniq.COUNT;
3034: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3027: lv_hire_already_in := 'N';
3028: lv_sep_already_in := 'Y';
3029:
3030: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3031: hr_utility_trace(' ');
3032:
3033: ln_count := hire_sep_uniq.COUNT;
3034: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3035: -- Bug 5005254
3046: hire_sep.DELETE;
3047: hire_sep := hire_sep_uniq;
3048: hire_sep_uniq.DELETE;
3049:
3050: hr_utility.set_location(gv_package || lv_procedure_name, 210);
3051: ln_step := 21;
3052:
3053: OPEN c_get_org_information(p_gre_id);
3054: FETCH c_get_org_information INTO lv_employer_ss_id;
3053: OPEN c_get_org_information(p_gre_id);
3054: FETCH c_get_org_information INTO lv_employer_ss_id;
3055: CLOSE c_get_org_information;
3056:
3057: hr_utility.set_location(gv_package || lv_procedure_name, 220);
3058: ln_step := 22;
3059:
3060: FOR i in hire_sep.FIRST..hire_sep.LAST
3061: LOOP
3059:
3060: FOR i in hire_sep.FIRST..hire_sep.LAST
3061: LOOP
3062:
3063: hr_utility.trace('Count hire_sep array :'|| i);
3064: lv_relation_exists := 'N';
3065: IF hire_sep(i).trn_type = 'HIRE' THEN
3066:
3067: hr_utility.set_location(gv_package || lv_procedure_name, 240);
3063: hr_utility.trace('Count hire_sep array :'|| i);
3064: lv_relation_exists := 'N';
3065: IF hire_sep(i).trn_type = 'HIRE' THEN
3066:
3067: hr_utility.set_location(gv_package || lv_procedure_name, 240);
3068: ln_step := 24;
3069:
3070: lv_transaction_type := '08';
3071: lv_transaction_date :=
3070: lv_transaction_type := '08';
3071: lv_transaction_date :=
3072: fnd_date.date_to_canonical(hire_sep(i).trn_date);
3073: lv_leaving_reason := NULL;
3074: hr_utility.trace('lv_transaction_date is: '||lv_transaction_date);
3075: /* Do not archive this transaction if person-GRE relation
3076: already exists prior to transaction date. */
3077: OPEN csr_per_gre(hire_sep(i).trn_date,
3078: lv_transaction_type);
3077: OPEN csr_per_gre(hire_sep(i).trn_date,
3078: lv_transaction_type);
3079: FETCH csr_per_gre INTO lv_relation_exists;
3080: CLOSE csr_per_gre;
3081: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3082: hr_utility.set_location(gv_package || lv_procedure_name, 230);
3083: ln_step := 23;
3084:
3085: ln_idw := get_idw( p_assignment_id => p_assignment_id
3078: lv_transaction_type);
3079: FETCH csr_per_gre INTO lv_relation_exists;
3080: CLOSE csr_per_gre;
3081: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3082: hr_utility.set_location(gv_package || lv_procedure_name, 230);
3083: ln_step := 23;
3084:
3085: ln_idw := get_idw( p_assignment_id => p_assignment_id
3086: ,p_tax_unit_id => p_gre_id
3087: ,p_effective_date => hire_sep(i).trn_date
3088: ,p_fixed_idw => ln_fixed_idw
3089: ,p_variable_idw => ln_variable_idw );
3090:
3091: hr_utility.trace('SS_ARCH hire_sep ln_idw: '||ln_idw);
3092: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3093: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3094: ln_variable_idw);
3095:
3088: ,p_fixed_idw => ln_fixed_idw
3089: ,p_variable_idw => ln_variable_idw );
3090:
3091: hr_utility.trace('SS_ARCH hire_sep ln_idw: '||ln_idw);
3092: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3093: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3094: ln_variable_idw);
3095:
3096: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
3089: ,p_variable_idw => ln_variable_idw );
3090:
3091: hr_utility.trace('SS_ARCH hire_sep ln_idw: '||ln_idw);
3092: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3093: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3094: ln_variable_idw);
3095:
3096: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
3097: ln_step := 203;
3092: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3093: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3094: ln_variable_idw);
3095:
3096: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
3097: ln_step := 203;
3098:
3099: lv_idw := to_char(ln_idw, 99999.99);
3100:
3097: ln_step := 203;
3098:
3099: lv_idw := to_char(ln_idw, 99999.99);
3100:
3101: hr_utility.trace('SS_ARCH hire_sep lv_idw: '||lv_idw);
3102:
3103: IF gn_person_rec_chng = 0 THEN
3104: gn_person_rec_chng := 1;
3105: END IF;
3105: END IF;
3106:
3107: ELSIF hire_sep(i).trn_type = 'SEPARATION' THEN
3108:
3109: hr_utility.set_location(gv_package || lv_procedure_name, 250);
3110: ln_step := 25;
3111:
3112: lv_transaction_type := '02';
3113: lv_transaction_date :=
3121: OPEN csr_per_gre(hire_sep(i).trn_date,
3122: lv_transaction_type);
3123: FETCH csr_per_gre INTO lv_relation_exists;
3124: CLOSE csr_per_gre;
3125: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3126:
3127: hr_utility.set_location(gv_package || lv_procedure_name, 260);
3128: ln_step := 26;
3129:
3123: FETCH csr_per_gre INTO lv_relation_exists;
3124: CLOSE csr_per_gre;
3125: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3126:
3127: hr_utility.set_location(gv_package || lv_procedure_name, 260);
3128: ln_step := 26;
3129:
3130: OPEN c_get_leaving_reason( p_assignment_id
3131: ,ld_eff_date
3135: CLOSE c_get_leaving_reason;
3136:
3137: IF lv_leaving_reason IS NULL THEN
3138:
3139: hr_utility.set_location(gv_package || lv_procedure_name, 270);
3140: ln_step := 27;
3141:
3142: -- get it from periods of service
3143: -- also the effective date passed is not correct
3153:
3154: END IF;
3155:
3156: IF lv_relation_exists = 'N' THEN
3157: hr_utility.set_location(gv_package || lv_procedure_name, 280);
3158: ln_step := 28;
3159: hr_utility_trace('lv_transaction_date :'|| lv_transaction_date);
3160: OPEN c_person_detail (p_person_id
3161: ,fnd_date.canonical_to_date
3155:
3156: IF lv_relation_exists = 'N' THEN
3157: hr_utility.set_location(gv_package || lv_procedure_name, 280);
3158: ln_step := 28;
3159: hr_utility_trace('lv_transaction_date :'|| lv_transaction_date);
3160: OPEN c_person_detail (p_person_id
3161: ,fnd_date.canonical_to_date
3162: (lv_transaction_date));
3163: FETCH c_person_detail INTO lv_employee_ssn;
3199: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info10
3200: := NULL;
3201: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info24
3202: := lv_report_yes_no;
3203: hr_utility_trace('Archived MX SS Transaction for hire');
3204: hr_utility_trace('-----------------------------------');
3205: hr_utility_trace('index ' || ln_index);
3206: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3207: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3200: := NULL;
3201: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info24
3202: := lv_report_yes_no;
3203: hr_utility_trace('Archived MX SS Transaction for hire');
3204: hr_utility_trace('-----------------------------------');
3205: hr_utility_trace('index ' || ln_index);
3206: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3207: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3208: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3201: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info24
3202: := lv_report_yes_no;
3203: hr_utility_trace('Archived MX SS Transaction for hire');
3204: hr_utility_trace('-----------------------------------');
3205: hr_utility_trace('index ' || ln_index);
3206: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3207: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3208: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3209: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3202: := lv_report_yes_no;
3203: hr_utility_trace('Archived MX SS Transaction for hire');
3204: hr_utility_trace('-----------------------------------');
3205: hr_utility_trace('index ' || ln_index);
3206: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3207: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3208: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3209: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3210: hr_utility_trace('Person ID ' ||
3204: hr_utility_trace('-----------------------------------');
3205: hr_utility_trace('index ' || ln_index);
3206: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3207: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3208: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3209: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3210: hr_utility_trace('Person ID ' ||
3211: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3212: hr_utility_trace('Transaction date ' ||
3206: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3207: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3208: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3209: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3210: hr_utility_trace('Person ID ' ||
3211: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3212: hr_utility_trace('Transaction date ' ||
3213: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3214: hr_utility_trace('transaction type ' ||
3208: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3209: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3210: hr_utility_trace('Person ID ' ||
3211: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3212: hr_utility_trace('Transaction date ' ||
3213: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3214: hr_utility_trace('transaction type ' ||
3215: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4);
3216: hr_utility_trace('-----------------------------------');
3210: hr_utility_trace('Person ID ' ||
3211: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3212: hr_utility_trace('Transaction date ' ||
3213: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3214: hr_utility_trace('transaction type ' ||
3215: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4);
3216: hr_utility_trace('-----------------------------------');
3217:
3218:
3212: hr_utility_trace('Transaction date ' ||
3213: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3214: hr_utility_trace('transaction type ' ||
3215: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4);
3216: hr_utility_trace('-----------------------------------');
3217:
3218:
3219: ELSE
3220: hr_utility_trace('Person-GRE association exits. Transaction '||
3216: hr_utility_trace('-----------------------------------');
3217:
3218:
3219: ELSE
3220: hr_utility_trace('Person-GRE association exits. Transaction '||
3221: lv_transaction_type||' ('||lv_transaction_date||
3222: ') will not be archived.');
3223: END IF;
3224: END LOOP;
3230: WHEN others THEN
3231: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3232: gv_package || lv_procedure_name;
3233:
3234: hr_utility_trace(lv_error_message || '-' || sqlerrm);
3235:
3236: lv_error_message :=
3237: pay_emp_action_arch.set_error_message(lv_error_message);
3238:
3235:
3236: lv_error_message :=
3237: pay_emp_action_arch.set_error_message(lv_error_message);
3238:
3239: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3240: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3241: hr_utility.raise_error;
3242:
3243: END arch_hire_separation;
3236: lv_error_message :=
3237: pay_emp_action_arch.set_error_message(lv_error_message);
3238:
3239: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3240: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3241: hr_utility.raise_error;
3242:
3243: END arch_hire_separation;
3244:
3237: pay_emp_action_arch.set_error_message(lv_error_message);
3238:
3239: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3240: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3241: hr_utility.raise_error;
3242:
3243: END arch_hire_separation;
3244:
3245: PROCEDURE get_transactions( p_payroll_action_id IN NUMBER
3333: BEGIN
3334:
3335: lv_procedure_name := 'get_transactions';
3336:
3337: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3338: ln_step := 1;
3339:
3340: ld_eff_start_date := p_start_date;
3341: ld_eff_end_date := p_end_date;
3348: per_events.DELETE;
3349: ele_events.DELETE;
3350: eev_events.DELETE;
3351:
3352: hr_utility.set_location(gv_package || lv_procedure_name, 20);
3353: ln_step := 2;
3354:
3355: OPEN c_get_event_group ('Mexico Social Security Reports');
3356: FETCH c_get_event_group INTO ln_event_group_id;
3355: OPEN c_get_event_group ('Mexico Social Security Reports');
3356: FETCH c_get_event_group INTO ln_event_group_id;
3357: CLOSE c_get_event_group;
3358:
3359: hr_utility_trace('p_person_id : ' || p_person_id);
3360: hr_utility_trace('p_start_date : ' || p_start_date);
3361: hr_utility_trace('p_end_date : ' || p_end_date);
3362: hr_utility_trace('p_gre_id : ' || p_gre_id);
3363:
3356: FETCH c_get_event_group INTO ln_event_group_id;
3357: CLOSE c_get_event_group;
3358:
3359: hr_utility_trace('p_person_id : ' || p_person_id);
3360: hr_utility_trace('p_start_date : ' || p_start_date);
3361: hr_utility_trace('p_end_date : ' || p_end_date);
3362: hr_utility_trace('p_gre_id : ' || p_gre_id);
3363:
3364: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3357: CLOSE c_get_event_group;
3358:
3359: hr_utility_trace('p_person_id : ' || p_person_id);
3360: hr_utility_trace('p_start_date : ' || p_start_date);
3361: hr_utility_trace('p_end_date : ' || p_end_date);
3362: hr_utility_trace('p_gre_id : ' || p_gre_id);
3363:
3364: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3365: ln_step := 3;
3358:
3359: hr_utility_trace('p_person_id : ' || p_person_id);
3360: hr_utility_trace('p_start_date : ' || p_start_date);
3361: hr_utility_trace('p_end_date : ' || p_end_date);
3362: hr_utility_trace('p_gre_id : ' || p_gre_id);
3363:
3364: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3365: ln_step := 3;
3366:
3360: hr_utility_trace('p_start_date : ' || p_start_date);
3361: hr_utility_trace('p_end_date : ' || p_end_date);
3362: hr_utility_trace('p_gre_id : ' || p_gre_id);
3363:
3364: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3365: ln_step := 3;
3366:
3367: OPEN c_assignments ( p_assignment_id
3368: ,p_start_date
3374: ,ld_eff_start_date
3375: ,ld_eff_end_date;
3376: CLOSE c_assignments;
3377:
3378: hr_utility.set_location(gv_package || lv_procedure_name, 40);
3379: ln_step := 4;
3380:
3381: pay_interpreter_pkg.entry_affected(
3382: p_element_entry_id => NULL
3394: ,t_proration_dates => l_proration_dates
3395: ,t_proration_change_type => l_proration_changes
3396: ,t_proration_type => l_pro_type_tab);
3397:
3398: hr_utility.set_location(gv_package || lv_procedure_name, 50);
3399: ln_step := 5;
3400: hr_utility_trace('pay_interpreter_pkg.entry_affected Returned Rows');
3401:
3402: IF int_pkg_events.COUNT > 0 THEN
3396: ,t_proration_type => l_pro_type_tab);
3397:
3398: hr_utility.set_location(gv_package || lv_procedure_name, 50);
3399: ln_step := 5;
3400: hr_utility_trace('pay_interpreter_pkg.entry_affected Returned Rows');
3401:
3402: IF int_pkg_events.COUNT > 0 THEN
3403:
3404: FOR i IN 1..int_pkg_events.COUNT
3411: END IF;*/
3412:
3413: /* Adding event qualification mechanism so that only those events that
3414: belong to current GRE are picked for archival. (Bug 5921945)*/
3415: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3416: ld_effective_date := int_pkg_events(i).effective_date;
3417: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3418: -- Bug 6005853
3419: IF int_pkg_events(i).column_name IN ('LOCATION_ID',
3413: /* Adding event qualification mechanism so that only those events that
3414: belong to current GRE are picked for archival. (Bug 5921945)*/
3415: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3416: ld_effective_date := int_pkg_events(i).effective_date;
3417: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3418: -- Bug 6005853
3419: IF int_pkg_events(i).column_name IN ('LOCATION_ID',
3420: 'SOFT_CODING_KEYFLEX_ID') THEN
3421: hr_utility_trace('Inside the event on location or soft key flex');
3417: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3418: -- Bug 6005853
3419: IF int_pkg_events(i).column_name IN ('LOCATION_ID',
3420: 'SOFT_CODING_KEYFLEX_ID') THEN
3421: hr_utility_trace('Inside the event on location or soft key flex');
3422: OPEN csr_get_asg_end_date (int_pkg_events(i).effective_date-1);
3423: FETCH csr_get_asg_end_date INTO ld_effective_date;
3424: CLOSE csr_get_asg_end_date;
3425: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3421: hr_utility_trace('Inside the event on location or soft key flex');
3422: OPEN csr_get_asg_end_date (int_pkg_events(i).effective_date-1);
3423: FETCH csr_get_asg_end_date INTO ld_effective_date;
3424: CLOSE csr_get_asg_end_date;
3425: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3426: END IF;
3427: hr_utility_trace('final ld_effective_date :' || ld_effective_date);
3428: IF event_qualified(p_person_id,
3429: p_assignment_id,
3423: FETCH csr_get_asg_end_date INTO ld_effective_date;
3424: CLOSE csr_get_asg_end_date;
3425: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3426: END IF;
3427: hr_utility_trace('final ld_effective_date :' || ld_effective_date);
3428: IF event_qualified(p_person_id,
3429: p_assignment_id,
3430: ld_effective_date,
3431: p_gre_id) THEN
3431: p_gre_id) THEN
3432: lv_table_name :=
3433: dated_tbls(int_pkg_events(i).dated_table_id).table_name;
3434:
3435: hr_utility_trace('-----------------------------------------------');
3436: hr_utility_trace('lv_table_name :' ||lv_table_name );
3437:
3438:
3439: IF lv_table_name = 'PER_ALL_ASSIGNMENTS_F' THEN
3432: lv_table_name :=
3433: dated_tbls(int_pkg_events(i).dated_table_id).table_name;
3434:
3435: hr_utility_trace('-----------------------------------------------');
3436: hr_utility_trace('lv_table_name :' ||lv_table_name );
3437:
3438:
3439: IF lv_table_name = 'PER_ALL_ASSIGNMENTS_F' THEN
3440:
3463: END IF;
3464:
3465: IF asg_events.COUNT > 0 THEN
3466:
3467: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3468: ln_step := 6;
3469:
3470: arch_hire_separation ( p_payroll_action_id => p_payroll_action_id
3471: ,p_asg_action_id => p_asg_action_id
3485: END IF;
3486:
3487: IF gn_person_rec_chng = 0 AND per_events.COUNT > 0 THEN
3488:
3489: hr_utility.set_location(gv_package || lv_procedure_name, 70);
3490: ln_step := 7;
3491:
3492: chk_person_rec_chng( p_per_events => per_events );
3493:
3492: chk_person_rec_chng( p_per_events => per_events );
3493:
3494: END IF;
3495:
3496: hr_utility.set_location(gv_package || lv_procedure_name, 80);
3497: ln_step := 8;
3498:
3499: arch_other_transactions ( p_payroll_action_id => p_payroll_action_id
3500: ,p_asg_action_id => p_asg_action_id
3509: ,p_eff_start_date => ld_eff_start_date
3510: ,p_eff_end_date => ld_eff_end_date
3511: );
3512:
3513: hr_utility.set_location(gv_package || lv_procedure_name, 90);
3514: ln_step := 9;
3515:
3516: EXCEPTION
3517: WHEN others THEN
3517: WHEN others THEN
3518: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3519: gv_package || lv_procedure_name;
3520:
3521: hr_utility_trace(lv_error_message || '-' || sqlerrm);
3522:
3523: lv_error_message :=
3524: pay_emp_action_arch.set_error_message(lv_error_message);
3525:
3522:
3523: lv_error_message :=
3524: pay_emp_action_arch.set_error_message(lv_error_message);
3525:
3526: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3527: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3528: hr_utility.raise_error;
3529:
3530: END get_transactions;
3523: lv_error_message :=
3524: pay_emp_action_arch.set_error_message(lv_error_message);
3525:
3526: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3527: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3528: hr_utility.raise_error;
3529:
3530: END get_transactions;
3531:
3524: pay_emp_action_arch.set_error_message(lv_error_message);
3525:
3526: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3527: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3528: hr_utility.raise_error;
3529:
3530: END get_transactions;
3531:
3532: PROCEDURE get_person_information( p_payroll_action_id IN NUMBER
3688: BEGIN
3689:
3690: lv_procedure_name := 'get_person_information';
3691:
3692: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3693: ln_step := 1;
3694:
3695: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3696: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3691:
3692: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3693: ln_step := 1;
3694:
3695: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3696: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3697: hr_utility_trace('p_person_id : ' ||p_person_id );
3698:
3699: OPEN csr_asg_dates;
3692: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3693: ln_step := 1;
3694:
3695: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3696: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3697: hr_utility_trace('p_person_id : ' ||p_person_id );
3698:
3699: OPEN csr_asg_dates;
3700: FETCH csr_asg_dates INTO ld_asg_start_date,
3693: ln_step := 1;
3694:
3695: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3696: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3697: hr_utility_trace('p_person_id : ' ||p_person_id );
3698:
3699: OPEN csr_asg_dates;
3700: FETCH csr_asg_dates INTO ld_asg_start_date,
3701: ld_asg_end_date;
3707: ELSE
3708: ld_effective_date := ld_asg_start_date;
3709: END IF;
3710: /*ld_effective_date := max (ld_effective_date, p_end_date);
3711: hr_utility_trace('p_end_date = '||
3712: fnd_date.date_to_canonical(p_end_date));*/
3713: hr_utility_trace('ld_asg_start_date = '||
3714: fnd_date.date_to_canonical(ld_asg_start_date));
3715: hr_utility_trace('ld_asg_end_date = '||
3709: END IF;
3710: /*ld_effective_date := max (ld_effective_date, p_end_date);
3711: hr_utility_trace('p_end_date = '||
3712: fnd_date.date_to_canonical(p_end_date));*/
3713: hr_utility_trace('ld_asg_start_date = '||
3714: fnd_date.date_to_canonical(ld_asg_start_date));
3715: hr_utility_trace('ld_asg_end_date = '||
3716: fnd_date.date_to_canonical(ld_asg_end_date));
3717: hr_utility_trace('p_end_date = '||
3711: hr_utility_trace('p_end_date = '||
3712: fnd_date.date_to_canonical(p_end_date));*/
3713: hr_utility_trace('ld_asg_start_date = '||
3714: fnd_date.date_to_canonical(ld_asg_start_date));
3715: hr_utility_trace('ld_asg_end_date = '||
3716: fnd_date.date_to_canonical(ld_asg_end_date));
3717: hr_utility_trace('p_end_date = '||
3718: fnd_date.date_to_canonical(p_end_date));
3719: hr_utility_trace('ld_effective_date = '||
3713: hr_utility_trace('ld_asg_start_date = '||
3714: fnd_date.date_to_canonical(ld_asg_start_date));
3715: hr_utility_trace('ld_asg_end_date = '||
3716: fnd_date.date_to_canonical(ld_asg_end_date));
3717: hr_utility_trace('p_end_date = '||
3718: fnd_date.date_to_canonical(p_end_date));
3719: hr_utility_trace('ld_effective_date = '||
3720: fnd_date.date_to_canonical(ld_effective_date));
3721:
3715: hr_utility_trace('ld_asg_end_date = '||
3716: fnd_date.date_to_canonical(ld_asg_end_date));
3717: hr_utility_trace('p_end_date = '||
3718: fnd_date.date_to_canonical(p_end_date));
3719: hr_utility_trace('ld_effective_date = '||
3720: fnd_date.date_to_canonical(ld_effective_date));
3721:
3722: OPEN c_person_detail (p_person_id
3723: ,ld_effective_date);
3734: ,lv_tax_rfc_id
3735: ,lv_hire_date;
3736: CLOSE c_person_detail;
3737:
3738: hr_utility.set_location(gv_package || lv_procedure_name, 20);
3739: ln_step := 2;
3740:
3741: OPEN c_asg_detail( p_assignment_id
3742: ,ld_effective_date/*
3746: ,ln_soft_cod_kflx_id
3747: ,lv_worker_type;
3748: CLOSE c_asg_detail;
3749:
3750: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3751: ln_step := 3;
3752:
3753: OPEN c_work_schdl( ln_soft_cod_kflx_id );
3754: FETCH c_work_schdl INTO lv_salary_type
3754: FETCH c_work_schdl INTO lv_salary_type
3755: ,lv_work_schedule;
3756: CLOSE c_work_schdl;
3757:
3758: hr_utility.set_location(gv_package || lv_procedure_name, 40);
3759: ln_step := 4;
3760:
3761: OPEN c_location (ln_location_id);
3762: FETCH c_location INTO lv_location_code;
3761: OPEN c_location (ln_location_id);
3762: FETCH c_location INTO lv_location_code;
3763: CLOSE c_location;
3764:
3765: hr_utility.set_location(gv_package || lv_procedure_name, 50);
3766: ln_step := 5;
3767:
3768: -- derive Reduced Working-week indicator from workschedule
3769:
3778: lv_red_work_week_ind := NULL ;
3779:
3780: END IF;
3781:
3782: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3783: ln_step := 6;
3784:
3785: -- Bug 5146225
3786: get_payroll_action_info (p_payroll_action_id,
3805: ,p_effective_date => ld_effective_date
3806: ,p_fixed_idw => ln_fixed_idw
3807: ,p_variable_idw => ln_variable_idw );
3808:
3809: hr_utility.trace('SS_ARCH PERSON_INFO ln_idw: '|| ln_idw);
3810: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_fixed_idw: '||
3811: ln_fixed_idw);
3812: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
3813: ln_variable_idw);
3806: ,p_fixed_idw => ln_fixed_idw
3807: ,p_variable_idw => ln_variable_idw );
3808:
3809: hr_utility.trace('SS_ARCH PERSON_INFO ln_idw: '|| ln_idw);
3810: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_fixed_idw: '||
3811: ln_fixed_idw);
3812: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
3813: ln_variable_idw);
3814:
3808:
3809: hr_utility.trace('SS_ARCH PERSON_INFO ln_idw: '|| ln_idw);
3810: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_fixed_idw: '||
3811: ln_fixed_idw);
3812: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
3813: ln_variable_idw);
3814:
3815: hr_utility.set_location(gv_package || lv_procedure_name, 70);
3816: ln_step := 7;
3811: ln_fixed_idw);
3812: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
3813: ln_variable_idw);
3814:
3815: hr_utility.set_location(gv_package || lv_procedure_name, 70);
3816: ln_step := 7;
3817:
3818: IF ( gv_credit_no IS NULL AND gv_credit_start_date IS NULL ) THEN
3819:
3832: ,ln_element_entry_id
3833: ,p_end_date)
3834: LOOP
3835:
3836: hr_utility_trace('name : '|| infonavit.name);
3837: hr_utility_trace('screen_entry_value : '||
3838: infonavit.screen_entry_value);
3839:
3840: IF infonavit.name = 'Credit Number' THEN
3833: ,p_end_date)
3834: LOOP
3835:
3836: hr_utility_trace('name : '|| infonavit.name);
3837: hr_utility_trace('screen_entry_value : '||
3838: infonavit.screen_entry_value);
3839:
3840: IF infonavit.name = 'Credit Number' THEN
3841:
3860: END IF;
3861:
3862: END IF;
3863:
3864: hr_utility.set_location(gv_package || lv_procedure_name, 80);
3865: ln_step := 8;
3866:
3867: ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
3868:
3898: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info20 := lv_worker_id;
3899: -- Segment 21 is the 'Do Not Report on Magtape' flag
3900: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info22 := gv_crdt_grant_dt;
3901:
3902: hr_utility.set_location(gv_package || lv_procedure_name, 90);
3903:
3904: EXCEPTION
3905: WHEN others THEN
3906: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3905: WHEN others THEN
3906: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3907: gv_package || lv_procedure_name;
3908:
3909: hr_utility_trace(lv_error_message || '-' || sqlerrm);
3910:
3911: lv_error_message :=
3912: pay_emp_action_arch.set_error_message(lv_error_message);
3913:
3910:
3911: lv_error_message :=
3912: pay_emp_action_arch.set_error_message(lv_error_message);
3913:
3914: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3915: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3916: hr_utility.raise_error;
3917:
3918: END get_person_information;
3911: lv_error_message :=
3912: pay_emp_action_arch.set_error_message(lv_error_message);
3913:
3914: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3915: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3916: hr_utility.raise_error;
3917:
3918: END get_person_information;
3919:
3912: pay_emp_action_arch.set_error_message(lv_error_message);
3913:
3914: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3915: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3916: hr_utility.raise_error;
3917:
3918: END get_person_information;
3919:
3920:
3939: OPEN c_get_idw_calc_method;
3940: FETCH c_get_idw_calc_method INTO lv_idw_calc_method;
3941: CLOSE c_get_idw_calc_method;
3942:
3943: hr_utility_trace ('IDW calculation method = '||lv_idw_calc_method);
3944: RETURN (lv_idw_calc_method);
3945: END get_IDW_calc_method;
3946:
3947: /*****************************************************************************
3979: BEGIN
3980:
3981: lv_procedure_name := 'range_cursor';
3982:
3983: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
3984:
3985: hr_utility_trace('Starting range_cursor ');
3986: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3987: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
3981: lv_procedure_name := 'range_cursor';
3982:
3983: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
3984:
3985: hr_utility_trace('Starting range_cursor ');
3986: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3987: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
3988: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3989:
3982:
3983: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
3984:
3985: hr_utility_trace('Starting range_cursor ');
3986: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3987: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
3988: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3989:
3990:
3983: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
3984:
3985: hr_utility_trace('Starting range_cursor ');
3986: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3987: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
3988: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3989:
3990:
3991: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
3984:
3985: hr_utility_trace('Starting range_cursor ');
3986: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3987: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
3988: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
3989:
3990:
3991: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
3992: ,p_end_date => lv_end_date
3994: ,p_business_group_id => ln_business_group_id
3995: ,p_gre_id => ln_gre_id
3996: );
3997:
3998: hr_utility_trace('lv_start_date :' || lv_start_date);
3999: hr_utility_trace('lv_end_date :' || lv_end_date);
4000: hr_utility_trace('ln_business_group_id :' || ln_business_group_id);
4001:
4002: IF pay_mx_soc_sec_archive.arch_exists_without_upgrade(ln_business_group_id)
3995: ,p_gre_id => ln_gre_id
3996: );
3997:
3998: hr_utility_trace('lv_start_date :' || lv_start_date);
3999: hr_utility_trace('lv_end_date :' || lv_end_date);
4000: hr_utility_trace('ln_business_group_id :' || ln_business_group_id);
4001:
4002: IF pay_mx_soc_sec_archive.arch_exists_without_upgrade(ln_business_group_id)
4003: = 'B' THEN
3996: );
3997:
3998: hr_utility_trace('lv_start_date :' || lv_start_date);
3999: hr_utility_trace('lv_end_date :' || lv_end_date);
4000: hr_utility_trace('ln_business_group_id :' || ln_business_group_id);
4001:
4002: IF pay_mx_soc_sec_archive.arch_exists_without_upgrade(ln_business_group_id)
4003: = 'B' THEN
4004: pay_generic_upgrade.new_business_group (
4009: gv_IDW_calc_method := get_IDW_calc_method (
4010: ln_gre_id,
4011: fnd_date.canonical_to_date (lv_end_date));
4012:
4013: hr_utility_trace('--> gv_IDW_calc_method : ' || gv_IDW_calc_method );
4014: /*
4015: IF gv_mode = 'P' THEN
4016:
4017: gv_periodic_start_date :=
4024: gv_periodic_start_date := lv_start_date;
4025:
4026: END IF;
4027:
4028: hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
4029: hr_utility_trace('gv_periodic_end_date :' || gv_periodic_end_date);
4030: */
4031:
4032: ln_pactid := p_payroll_action_id;
4025:
4026: END IF;
4027:
4028: hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
4029: hr_utility_trace('gv_periodic_end_date :' || gv_periodic_end_date);
4030: */
4031:
4032: ln_pactid := p_payroll_action_id;
4033:
4048: ** information for all employees and transaction it there is any.
4049: ** IF yes (gn_implementation > 0), it should arrchive only
4050: ** transaction if there is any event occured.
4051: **************************************************************/
4052: hr_utility_trace('--> gn_implementation : ' || gn_implementation );
4053:
4054: IF gn_implementation = 0 THEN
4055:
4056: p_sqlstr :=
4159: 'AND '''|| gv_mode || ''' = ''P'' ) ' ||
4160: 'ORDER BY person_id';
4161:
4162: END IF; -- gn_implementation = 0
4163: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4164:
4165: hr_utility_trace('--> Query formed p_sqlstr : ' || p_sqlstr );
4166:
4167: update pay_payroll_actions
4161:
4162: END IF; -- gn_implementation = 0
4163: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4164:
4165: hr_utility_trace('--> Query formed p_sqlstr : ' || p_sqlstr );
4166:
4167: update pay_payroll_actions
4168: set effective_date = fnd_date.canonical_to_date('4712/12/31')
4169: where payroll_action_id = p_payroll_action_id;
4167: update pay_payroll_actions
4168: set effective_date = fnd_date.canonical_to_date('4712/12/31')
4169: where payroll_action_id = p_payroll_action_id;
4170:
4171: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4172:
4173: END range_cursor;
4174:
4175: PROCEDURE action_creation( p_payroll_action_id IN NUMBER
4370:
4371: lv_procedure_name := 'action_creation';
4372:
4373: ln_step := 1;
4374: hr_utility.set_location(gv_package || lv_procedure_name, 10);
4375:
4376:
4377: hr_utility_trace('--> Entering Action Creation ');
4378:
4373: ln_step := 1;
4374: hr_utility.set_location(gv_package || lv_procedure_name, 10);
4375:
4376:
4377: hr_utility_trace('--> Entering Action Creation ');
4378:
4379: get_payroll_action_info(
4380: p_payroll_action_id => p_payroll_action_id
4381: ,p_start_date => lv_start_date
4382: ,p_end_date => lv_end_date
4383: ,p_business_group_id => ln_business_group_id
4384: ,p_gre_id => ln_gre_id);
4385:
4386: hr_utility_trace('lv_start_date ' || lv_start_date);
4387: hr_utility_trace('lv_end_date ' || lv_end_date);
4388: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4389: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4390:
4383: ,p_business_group_id => ln_business_group_id
4384: ,p_gre_id => ln_gre_id);
4385:
4386: hr_utility_trace('lv_start_date ' || lv_start_date);
4387: hr_utility_trace('lv_end_date ' || lv_end_date);
4388: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4389: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4390:
4391: IF gv_IDW_calc_method IS NULL THEN
4384: ,p_gre_id => ln_gre_id);
4385:
4386: hr_utility_trace('lv_start_date ' || lv_start_date);
4387: hr_utility_trace('lv_end_date ' || lv_end_date);
4388: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4389: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4390:
4391: IF gv_IDW_calc_method IS NULL THEN
4392: gv_IDW_calc_method := get_IDW_calc_method (
4385:
4386: hr_utility_trace('lv_start_date ' || lv_start_date);
4387: hr_utility_trace('lv_end_date ' || lv_end_date);
4388: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4389: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4390:
4391: IF gv_IDW_calc_method IS NULL THEN
4392: gv_IDW_calc_method := get_IDW_calc_method (
4393: ln_gre_id,
4393: ln_gre_id,
4394: fnd_date.canonical_to_date (lv_end_date));
4395: END IF;
4396:
4397: hr_utility_trace('--> gv_IDW_calc_method ' || gv_IDW_calc_method);
4398:
4399: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4400: ln_step := 2;
4401:
4395: END IF;
4396:
4397: hr_utility_trace('--> gv_IDW_calc_method ' || gv_IDW_calc_method);
4398:
4399: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4400: ln_step := 2;
4401:
4402: lb_range_person := pay_ac_utility.range_person_on(
4403: p_report_type => 'SS_ARCHIVE'
4404: ,p_report_format => 'SS_ARCHIVE'
4405: ,p_report_qualifier => 'SS_ARCHIVE'
4406: ,p_report_category => 'RT');
4407:
4408: hr_utility_trace('--> Step ' || ln_step);
4409: IF lb_range_person THEN
4410: OPEN c_get_range_emp(p_payroll_action_id
4411: ,p_chunk
4412: ,ln_business_group_id
4416: ,fnd_date.canonical_to_date(gv_periodic_start_date)
4417: ,fnd_date.canonical_to_date(gv_periodic_end_date)
4418: );
4419:
4420: hr_utility_trace('c_get_range_emp');
4421:
4422: ELSE
4423: OPEN c_get_emp( p_payroll_action_id
4424: , p_start_person_id
4429: , fnd_date.canonical_to_date(lv_end_date)
4430: , fnd_date.canonical_to_date(gv_periodic_start_date)
4431: , fnd_date.canonical_to_date(gv_periodic_end_date) );
4432:
4433: hr_utility_trace('c_get_emp');
4434:
4435: END IF;
4436:
4437: --Loop for all rows returned for SQL statement.
4434:
4435: END IF;
4436:
4437: --Loop for all rows returned for SQL statement.
4438: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4439:
4440: ln_person_id_prev := 0;
4441: ln_assignment_id_prev := 0;
4442:
4443: LOOP
4444:
4445: IF lb_range_person THEN
4446:
4447: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4448: ln_step := 3;
4449: hr_utility_trace('--> Fetching from c_get_range_emp ' );
4450: FETCH c_get_range_emp INTO ln_person_id
4451: ,ln_primary_flag
4445: IF lb_range_person THEN
4446:
4447: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4448: ln_step := 3;
4449: hr_utility_trace('--> Fetching from c_get_range_emp ' );
4450: FETCH c_get_range_emp INTO ln_person_id
4451: ,ln_primary_flag
4452: ,ln_assignment_id
4453: ,lv_report_mode;
4455: EXIT WHEN c_get_range_emp%NOTFOUND;
4456:
4457: ELSE
4458:
4459: hr_utility.set_location(gv_package || lv_procedure_name, 40);
4460: ln_step := 4;
4461: hr_utility_trace('--> Fetching from c_get_emp ' );
4462: FETCH c_get_emp INTO ln_person_id
4463: ,ln_primary_flag
4457: ELSE
4458:
4459: hr_utility.set_location(gv_package || lv_procedure_name, 40);
4460: ln_step := 4;
4461: hr_utility_trace('--> Fetching from c_get_emp ' );
4462: FETCH c_get_emp INTO ln_person_id
4463: ,ln_primary_flag
4464: ,ln_assignment_id
4465: ,lv_report_mode;
4467: EXIT WHEN c_get_emp%NOTFOUND;
4468:
4469: END IF;
4470:
4471: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4472: ln_step := 5;
4473:
4474: hr_utility_trace('ln_person_id = ' || ln_person_id);
4475: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4470:
4471: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4472: ln_step := 5;
4473:
4474: hr_utility_trace('ln_person_id = ' || ln_person_id);
4475: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4476: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4477: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4478:
4471: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4472: ln_step := 5;
4473:
4474: hr_utility_trace('ln_person_id = ' || ln_person_id);
4475: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4476: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4477: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4478:
4479: IF (ln_person_id <> ln_person_id_prev OR
4472: ln_step := 5;
4473:
4474: hr_utility_trace('ln_person_id = ' || ln_person_id);
4475: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4476: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4477: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4478:
4479: IF (ln_person_id <> ln_person_id_prev OR
4480: ln_assignment_id <> ln_assignment_id_prev) THEN
4473:
4474: hr_utility_trace('ln_person_id = ' || ln_person_id);
4475: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4476: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4477: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4478:
4479: IF (ln_person_id <> ln_person_id_prev OR
4480: ln_assignment_id <> ln_assignment_id_prev) THEN
4481:
4482: IF gn_implementation = 0 THEN
4483:
4484:
4485: ln_step := 6;
4486: hr_utility_trace('--> Step 6' );
4487: hr_utility_trace('IMPLEMENTATION TRUE');
4488:
4489: SELECT pay_assignment_actions_s.nextval
4490: INTO ln_asg_act_id
4483:
4484:
4485: ln_step := 6;
4486: hr_utility_trace('--> Step 6' );
4487: hr_utility_trace('IMPLEMENTATION TRUE');
4488:
4489: SELECT pay_assignment_actions_s.nextval
4490: INTO ln_asg_act_id
4491: FROM dual;
4489: SELECT pay_assignment_actions_s.nextval
4490: INTO ln_asg_act_id
4491: FROM dual;
4492:
4493: hr_utility_trace('--> Inserting into pay_assignment_actions' );
4494: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4495: -- insert into pay_assignment_actions.
4496: hr_nonrun_asact.insact(ln_asg_act_id,
4497: ln_assignment_id,
4490: INTO ln_asg_act_id
4491: FROM dual;
4492:
4493: hr_utility_trace('--> Inserting into pay_assignment_actions' );
4494: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4495: -- insert into pay_assignment_actions.
4496: hr_nonrun_asact.insact(ln_asg_act_id,
4497: ln_assignment_id,
4498: p_payroll_action_id,
4501: null,
4502: 'U',
4503: null);
4504:
4505: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4506: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4507: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4508: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4509:
4502: 'U',
4503: null);
4504:
4505: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4506: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4507: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4508: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4509:
4510: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4503: null);
4504:
4505: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4506: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4507: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4508: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4509:
4510: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4511:
4504:
4505: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4506: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4507: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4508: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4509:
4510: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4511:
4512: hr_utility_trace('--> Updating into pay_assignment_actions' );
4506: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4507: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4508: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4509:
4510: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4511:
4512: hr_utility_trace('--> Updating into pay_assignment_actions' );
4513: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4514: UPDATE pay_assignment_actions
4508: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4509:
4510: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4511:
4512: hr_utility_trace('--> Updating into pay_assignment_actions' );
4513: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4514: UPDATE pay_assignment_actions
4515: SET serial_number = to_char(ln_person_id) || '|' ||
4516: 'MX_IDWV=' || lv_report_mode || '|'
4509:
4510: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4511:
4512: hr_utility_trace('--> Updating into pay_assignment_actions' );
4513: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4514: UPDATE pay_assignment_actions
4515: SET serial_number = to_char(ln_person_id) || '|' ||
4516: 'MX_IDWV=' || lv_report_mode || '|'
4517: WHERE assignment_action_id = ln_asg_act_id;
4519: ELSE
4520:
4521: ln_step := 7;
4522:
4523: hr_utility_trace('--> In STep 7');
4524:
4525: IF ( pay_mx_soc_sec_archive.per_asg.EXISTS(ln_person_id) = FALSE )
4526: THEN
4527: hr_utility_trace('--> Adding in pay_mx_soc_sec_archive.per_asg ');
4523: hr_utility_trace('--> In STep 7');
4524:
4525: IF ( pay_mx_soc_sec_archive.per_asg.EXISTS(ln_person_id) = FALSE )
4526: THEN
4527: hr_utility_trace('--> Adding in pay_mx_soc_sec_archive.per_asg ');
4528: pay_mx_soc_sec_archive.per_asg(ln_person_id).person_id :=
4529: ln_person_id;
4530: pay_mx_soc_sec_archive.per_asg(ln_person_id).assignment_id :=
4531: ln_assignment_id;
4530: pay_mx_soc_sec_archive.per_asg(ln_person_id).assignment_id :=
4531: ln_assignment_id;
4532: END IF;
4533:
4534: hr_utility_trace('--> lv_report_mode '||lv_report_mode);
4535: IF lv_report_mode = 'Y' THEN
4536:
4537: ln_events_found := 1;
4538:
4547: AND fnd_date.canonical_to_date(lv_end_date);
4548: END IF;
4549:
4550: ln_step := 8;
4551: hr_utility_trace('--> In Step 8 ');
4552: hr_utility_trace('--> ln_events_found ' || ln_events_found);
4553:
4554: IF ln_events_found > 0
4555: OR (gv_IDW_calc_method = 'B' AND
4548: END IF;
4549:
4550: ln_step := 8;
4551: hr_utility_trace('--> In Step 8 ');
4552: hr_utility_trace('--> ln_events_found ' || ln_events_found);
4553:
4554: IF ln_events_found > 0
4555: OR (gv_IDW_calc_method = 'B' AND
4556: seniority_changed (
4556: seniority_changed (
4557: ln_person_id,
4558: fnd_date.canonical_to_date(lv_end_date),
4559: fnd_date.canonical_to_date(lv_start_date)) = 'Y') THEN
4560: hr_utility_trace('--> Inside If ' );
4561: SELECT pay_assignment_actions_s.nextval
4562: INTO ln_asg_act_id
4563: FROM dual;
4564:
4561: SELECT pay_assignment_actions_s.nextval
4562: INTO ln_asg_act_id
4563: FROM dual;
4564:
4565: hr_utility_trace('--> insert into pay_assignment_actions.' );
4566: -- insert into pay_assignment_actions.
4567: hr_nonrun_asact.insact(ln_asg_act_id,
4568: ln_assignment_id,
4569: p_payroll_action_id,
4572: null,
4573: 'U',
4574: null);
4575:
4576: hr_utility.set_location(gv_package || lv_procedure_name, 90);
4577: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4578: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4579:
4580: hr_utility.set_location(gv_package || lv_procedure_name, 100);
4573: 'U',
4574: null);
4575:
4576: hr_utility.set_location(gv_package || lv_procedure_name, 90);
4577: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4578: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4579:
4580: hr_utility.set_location(gv_package || lv_procedure_name, 100);
4581:
4574: null);
4575:
4576: hr_utility.set_location(gv_package || lv_procedure_name, 90);
4577: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4578: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4579:
4580: hr_utility.set_location(gv_package || lv_procedure_name, 100);
4581:
4582: UPDATE pay_assignment_actions
4576: hr_utility.set_location(gv_package || lv_procedure_name, 90);
4577: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4578: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4579:
4580: hr_utility.set_location(gv_package || lv_procedure_name, 100);
4581:
4582: UPDATE pay_assignment_actions
4583: SET serial_number = to_char(ln_person_id) || '|' ||
4584: 'MX_IDWV=' || lv_report_mode || '|'
4609: END IF;
4610:
4611: ln_step := 9;
4612:
4613: hr_utility_trace('--> Step 9 ' );
4614: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
4615: hr_utility_trace(' p_chunk : '|| p_chunk );
4616:
4617: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
4610:
4611: ln_step := 9;
4612:
4613: hr_utility_trace('--> Step 9 ' );
4614: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
4615: hr_utility_trace(' p_chunk : '|| p_chunk );
4616:
4617: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
4618:
4611: ln_step := 9;
4612:
4613: hr_utility_trace('--> Step 9 ' );
4614: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
4615: hr_utility_trace(' p_chunk : '|| p_chunk );
4616:
4617: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
4618:
4619: EXCEPTION
4613: hr_utility_trace('--> Step 9 ' );
4614: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
4615: hr_utility_trace(' p_chunk : '|| p_chunk );
4616:
4617: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
4618:
4619: EXCEPTION
4620: WHEN others THEN
4621: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4620: WHEN others THEN
4621: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4622: gv_package || lv_procedure_name;
4623:
4624: hr_utility_trace(lv_error_message || '-' || sqlerrm);
4625:
4626: lv_error_message :=
4627: pay_emp_action_arch.set_error_message(lv_error_message);
4628:
4625:
4626: lv_error_message :=
4627: pay_emp_action_arch.set_error_message(lv_error_message);
4628:
4629: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4630: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4631: hr_utility.raise_error;
4632:
4633:
4626: lv_error_message :=
4627: pay_emp_action_arch.set_error_message(lv_error_message);
4628:
4629: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4630: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4631: hr_utility.raise_error;
4632:
4633:
4634:
4627: pay_emp_action_arch.set_error_message(lv_error_message);
4628:
4629: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4630: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4631: hr_utility.raise_error;
4632:
4633:
4634:
4635: END action_creation;
4689: lv_is_employee VARCHAR2(2) := 'N';
4690:
4691: BEGIN
4692:
4693: hr_utility_trace('--> Archive_data' );
4694: hr_utility_trace('--> p_asg_action_id ' || p_asg_action_id );
4695: hr_utility_trace('--> p_effective_date' || p_effective_date );
4696:
4697: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
4690:
4691: BEGIN
4692:
4693: hr_utility_trace('--> Archive_data' );
4694: hr_utility_trace('--> p_asg_action_id ' || p_asg_action_id );
4695: hr_utility_trace('--> p_effective_date' || p_effective_date );
4696:
4697: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
4698:
4691: BEGIN
4692:
4693: hr_utility_trace('--> Archive_data' );
4694: hr_utility_trace('--> p_asg_action_id ' || p_asg_action_id );
4695: hr_utility_trace('--> p_effective_date' || p_effective_date );
4696:
4697: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
4698:
4699: lv_procedure_name := 'archive_data';
4710: IF gn_implementation = 0 THEN
4711: gn_person_rec_chng := 1;
4712: END IF;
4713:
4714: hr_utility.set_location(gv_package || lv_procedure_name, 10);
4715: ln_step := 1;
4716:
4717: OPEN c_asgact_info(p_asg_action_id);
4718: FETCH c_asgact_info INTO ln_payroll_action_id
4724:
4725: ln_person_id := SUBSTR( lv_serial_number, 1,
4726: NVL(INSTR(lv_serial_number,'|'),50) -1);
4727:
4728: hr_utility_trace('--> ln_person_id'|| ln_person_id);
4729: /***********************************************************
4730: ** gv_variable_idw is used to get information about whether
4731: ** whether this assignment has variable IDW or not.
4732: ***********************************************************/
4732: ***********************************************************/
4733:
4734: gv_variable_idw :=
4735: NVL(pay_mx_utility.get_legi_param_val('MX_IDWV',lv_serial_number), 'N');
4736: hr_utility_trace('--> gv_variable_idw' || gv_variable_idw );
4737:
4738: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4739: ln_step := 2;
4740:
4734: gv_variable_idw :=
4735: NVL(pay_mx_utility.get_legi_param_val('MX_IDWV',lv_serial_number), 'N');
4736: hr_utility_trace('--> gv_variable_idw' || gv_variable_idw );
4737:
4738: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4739: ln_step := 2;
4740:
4741: get_payroll_action_info(
4742: p_payroll_action_id => ln_payroll_action_id
4744: ,p_end_date => lv_end_date
4745: ,p_business_group_id => ln_business_group_id
4746: ,p_gre_id => ln_gre_id);
4747:
4748: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4749: ln_step := 3;
4750:
4751: ld_start_date := fnd_date.canonical_to_date(lv_start_date);
4752: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
4750:
4751: ld_start_date := fnd_date.canonical_to_date(lv_start_date);
4752: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
4753:
4754: hr_utility_trace('--> lv_start_date' || lv_start_date );
4755: hr_utility_trace('--> lv_end_date' || lv_end_date );
4756: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
4757: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
4758:
4751: ld_start_date := fnd_date.canonical_to_date(lv_start_date);
4752: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
4753:
4754: hr_utility_trace('--> lv_start_date' || lv_start_date );
4755: hr_utility_trace('--> lv_end_date' || lv_end_date );
4756: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
4757: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
4758:
4759:
4752: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
4753:
4754: hr_utility_trace('--> lv_start_date' || lv_start_date );
4755: hr_utility_trace('--> lv_end_date' || lv_end_date );
4756: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
4757: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
4758:
4759:
4760: IF gv_IDW_calc_method IS NULL THEN
4753:
4754: hr_utility_trace('--> lv_start_date' || lv_start_date );
4755: hr_utility_trace('--> lv_end_date' || lv_end_date );
4756: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
4757: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
4758:
4759:
4760: IF gv_IDW_calc_method IS NULL THEN
4761: gv_IDW_calc_method := get_IDW_calc_method (
4761: gv_IDW_calc_method := get_IDW_calc_method (
4762: ln_gre_id,
4763: fnd_date.canonical_to_date (lv_end_date));
4764: END IF;
4765: hr_utility_trace('--> gv_IDW_calc_method' || gv_IDW_calc_method );
4766: /****************************************************************
4767: ** Archive all the payroll action level data once only when
4768: ** chunk number is 1. Also check if this has not been archived
4769: ** earlier
4768: ** chunk number is 1. Also check if this has not been archived
4769: ** earlier
4770: *****************************************************************/
4771:
4772: hr_utility.set_location(gv_package || lv_procedure_name, 40);
4773: ln_step := 4;
4774:
4775: OPEN c_check_pay_action(ln_payroll_action_id);
4776: FETCH c_check_pay_action INTO ln_pay_action_count;
4775: OPEN c_check_pay_action(ln_payroll_action_id);
4776: FETCH c_check_pay_action INTO ln_pay_action_count;
4777: CLOSE c_check_pay_action;
4778:
4779: hr_utility_trace('--> ln_pay_action_count' || ln_pay_action_count );
4780: IF ln_pay_action_count = 0 THEN
4781:
4782: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4783: ln_step := 5;
4778:
4779: hr_utility_trace('--> ln_pay_action_count' || ln_pay_action_count );
4780: IF ln_pay_action_count = 0 THEN
4781:
4782: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4783: ln_step := 5;
4784:
4785: IF ln_chunk_number = 1 THEN
4786:
4785: IF ln_chunk_number = 1 THEN
4786:
4787: ln_step := 25;
4788:
4789: hr_utility.set_location(gv_package || lv_procedure_name, 60);
4790: ln_step := 6;
4791: hr_utility_trace('--> arch_pay_action_level_data' );
4792: arch_pay_action_level_data(
4793: p_payroll_action_id => ln_payroll_action_id
4787: ln_step := 25;
4788:
4789: hr_utility.set_location(gv_package || lv_procedure_name, 60);
4790: ln_step := 6;
4791: hr_utility_trace('--> arch_pay_action_level_data' );
4792: arch_pay_action_level_data(
4793: p_payroll_action_id => ln_payroll_action_id
4794: ,p_assignment_id => ln_assignment_id
4795: ,p_effective_Date => ld_end_date
4798: END IF;
4799:
4800: END IF;
4801:
4802: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4803: ln_step := 7;
4804:
4805: hr_utility_trace('--> get_transactions' );
4806: get_transactions( p_payroll_action_id => ln_payroll_action_id
4801:
4802: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4803: ln_step := 7;
4804:
4805: hr_utility_trace('--> get_transactions' );
4806: get_transactions( p_payroll_action_id => ln_payroll_action_id
4807: ,p_asg_action_id => p_asg_action_id
4808: ,p_effective_Date => p_effective_Date
4809: ,p_assignment_id => ln_assignment_id
4816: );
4817:
4818: IF ( gn_person_rec_chng = 1 ) THEN
4819:
4820: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4821: ln_step := 8;
4822: hr_utility_trace('--> 8 get_person_information' );
4823: get_person_information(
4824: p_payroll_action_id => ln_payroll_action_id
4818: IF ( gn_person_rec_chng = 1 ) THEN
4819:
4820: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4821: ln_step := 8;
4822: hr_utility_trace('--> 8 get_person_information' );
4823: get_person_information(
4824: p_payroll_action_id => ln_payroll_action_id
4825: ,p_asg_action_id => p_asg_action_id
4826: ,p_effective_Date => p_effective_Date
4834: );
4835:
4836: END IF;
4837:
4838: hr_utility.set_location(gv_package || lv_procedure_name, 90);
4839: ln_step := 9;
4840:
4841: l_act_tab.DELETE;
4842: j := 0;
4849:
4850: IF pay_mx_soc_sec_archive.lrr_act_tab(i).action_info_category
4851: = 'MX SS TRANSACTIONS' THEN
4852: /*Added code for bug 6862116*/
4853: hr_utility_trace('Checking for active employe ..for MX SS TRANSACTIONS' );
4854: OPEN c_check_active_employee(pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1,
4855: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
4856: FETCH c_check_active_employee INTO lv_is_employee;
4857: CLOSE c_check_active_employee;
4855: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
4856: FETCH c_check_active_employee INTO lv_is_employee;
4857: CLOSE c_check_active_employee;
4858:
4859: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
4860: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
4861: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
4862:
4863: IF (TRUNC(fnd_date.canonical_to_date(
4856: FETCH c_check_active_employee INTO lv_is_employee;
4857: CLOSE c_check_active_employee;
4858:
4859: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
4860: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
4861: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
4862:
4863: IF (TRUNC(fnd_date.canonical_to_date(
4864: pay_mx_soc_sec_archive.lrr_act_tab(i).act_info2)) =
4857: CLOSE c_check_active_employee;
4858:
4859: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
4860: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
4861: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
4862:
4863: IF (TRUNC(fnd_date.canonical_to_date(
4864: pay_mx_soc_sec_archive.lrr_act_tab(i).act_info2)) =
4865: TRUNC(fnd_date.canonical_to_date('4712/12/31'))) OR lv_is_employee = 'N' THEN
4864: pay_mx_soc_sec_archive.lrr_act_tab(i).act_info2)) =
4865: TRUNC(fnd_date.canonical_to_date('4712/12/31'))) OR lv_is_employee = 'N' THEN
4866:
4867: NULL;
4868: hr_utility_trace('--> NULL' );
4869: ELSE
4870: l_act_tab(j) := pay_mx_soc_sec_archive.lrr_act_tab(i);
4871:
4872: j := j + 1;
4874: END if;
4875:
4876: ELSIF pay_mx_soc_sec_archive.lrr_act_tab(i).action_info_category
4877: = 'MX SS PERSON INFORMATION' THEN
4878: hr_utility_trace('Checking for active employe ..for MX SS PERSON Information' );
4879: OPEN c_check_active_employee (pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1,
4880: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
4881: FETCH c_check_active_employee INTO lv_is_employee;
4882: CLOSE c_check_active_employee;
4880: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
4881: FETCH c_check_active_employee INTO lv_is_employee;
4882: CLOSE c_check_active_employee;
4883:
4884: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
4885: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
4886:
4887: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
4888:
4881: FETCH c_check_active_employee INTO lv_is_employee;
4882: CLOSE c_check_active_employee;
4883:
4884: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
4885: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
4886:
4887: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
4888:
4889:
4883:
4884: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
4885: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
4886:
4887: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
4888:
4889:
4890: IF lv_is_employee = 'N' THEN
4891:
4918: END IF;
4919:
4920: IF pay_mx_soc_sec_archive.lrr_act_tab.COUNT > 0 THEN
4921:
4922: hr_utility_trace('--> pay_mx_soc_sec_archive.lrr_act_tab.COUNT ' || pay_mx_soc_sec_archive.lrr_act_tab.COUNT);
4923: pay_emp_action_arch.insert_rows_thro_api_process(
4924: p_action_context_id => p_asg_action_id
4925: ,p_action_context_type => 'AAP'
4926: ,p_assignment_id => ln_assignment_id
4932: END IF;
4933:
4934: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
4935:
4936: hr_utility.set_location(gv_package || lv_procedure_name, 100);
4937: ln_step := 10;
4938:
4939: EXCEPTION
4940: WHEN others THEN
4937: ln_step := 10;
4938:
4939: EXCEPTION
4940: WHEN others THEN
4941: hr_utility_trace('--> Exception in Archive_data ' );
4942: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4943: gv_package || lv_procedure_name;
4944:
4945: hr_utility_trace(lv_error_message || '-' || sqlerrm);
4941: hr_utility_trace('--> Exception in Archive_data ' );
4942: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4943: gv_package || lv_procedure_name;
4944:
4945: hr_utility_trace(lv_error_message || '-' || sqlerrm);
4946:
4947: lv_error_message :=
4948: pay_emp_action_arch.set_error_message(lv_error_message);
4949:
4946:
4947: lv_error_message :=
4948: pay_emp_action_arch.set_error_message(lv_error_message);
4949:
4950: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4951: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4952: hr_utility.raise_error;
4953:
4954: END archive_data;
4947: lv_error_message :=
4948: pay_emp_action_arch.set_error_message(lv_error_message);
4949:
4950: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4951: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4952: hr_utility.raise_error;
4953:
4954: END archive_data;
4955:
4948: pay_emp_action_arch.set_error_message(lv_error_message);
4949:
4950: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4951: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4952: hr_utility.raise_error;
4953:
4954: END archive_data;
4955:
4956: PROCEDURE archinit(p_payroll_action_id in number) IS
5022: p_attribute18 => NULL,
5023: p_attribute19 => NULL,
5024: p_attribute20 => NULL);
5025:
5026: hr_utility_trace ('end_date in legislative_parameters = '||
5027: fnd_date.date_to_canonical(ld_end_date));
5028: hr_utility_trace ('Date stamped in pay_recorded_requests = '||
5029: fnd_date.date_to_canonical(ld_start_date));
5030:
5024: p_attribute20 => NULL);
5025:
5026: hr_utility_trace ('end_date in legislative_parameters = '||
5027: fnd_date.date_to_canonical(ld_end_date));
5028: hr_utility_trace ('Date stamped in pay_recorded_requests = '||
5029: fnd_date.date_to_canonical(ld_start_date));
5030:
5031: /* We need to stamp the end date in pay_recorded_requests only if
5032: existing date is less than end date stamped on payroll action. This
5035:
5036: In other words, date stamped in pay_recorded_requests can only be
5037: advanced further; it cannot be updated by a potential retry attempt. */
5038: IF ld_end_date > ld_start_date THEN
5039: hr_utility_trace ('Advancing date in pay_recorded_requests to '||
5040: fnd_date.date_to_canonical(ld_end_date));
5041: pay_recorded_requests_pkg.set_recorded_date(
5042: p_process => 'MX_SOC_SEC_ARCH',
5043: p_recorded_date => ld_end_date,
5062: p_attribute18 => NULL,
5063: p_attribute19 => NULL,
5064: p_attribute20 => NULL);
5065: ELSE
5066: hr_utility_trace ('pay_recorded_requests not updated.');
5067: END IF;
5068:
5069: gt_gre_cache.delete();
5070:
5102: E - Error. Archiver runs exist without successful upgrade runs.
5103: */
5104:
5105: l_proc_name := gv_package || 'arch_exists_without_upgrade';
5106: hr_utility_trace ('Entering '||l_proc_name);
5107: lv_exists := 'N';
5108:
5109: OPEN csr_upgrade_exists;
5110: FETCH csr_upgrade_exists INTO lv_exists;
5110: FETCH csr_upgrade_exists INTO lv_exists;
5111: CLOSE csr_upgrade_exists;
5112:
5113: IF lv_exists = 'Y' THEN
5114: hr_utility_trace ('Completed upgrade process exists.');
5115: hr_utility_trace ('Leaving '||l_proc_name);
5116: RETURN ('C');
5117: END IF;
5118:
5111: CLOSE csr_upgrade_exists;
5112:
5113: IF lv_exists = 'Y' THEN
5114: hr_utility_trace ('Completed upgrade process exists.');
5115: hr_utility_trace ('Leaving '||l_proc_name);
5116: RETURN ('C');
5117: END IF;
5118:
5119: OPEN csr_arch_exists;
5120: FETCH csr_arch_exists INTO lv_exists;
5121: CLOSE csr_arch_exists;
5122:
5123: IF lv_exists = 'N' THEN
5124: hr_utility_trace ('Brand new business group.');
5125: hr_utility_trace ('Leaving '||l_proc_name);
5126: RETURN ('B');
5127: ELSE
5128: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5121: CLOSE csr_arch_exists;
5122:
5123: IF lv_exists = 'N' THEN
5124: hr_utility_trace ('Brand new business group.');
5125: hr_utility_trace ('Leaving '||l_proc_name);
5126: RETURN ('B');
5127: ELSE
5128: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5129: hr_utility_trace ('Leaving '||l_proc_name);
5124: hr_utility_trace ('Brand new business group.');
5125: hr_utility_trace ('Leaving '||l_proc_name);
5126: RETURN ('B');
5127: ELSE
5128: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5129: hr_utility_trace ('Leaving '||l_proc_name);
5130: RETURN ('E');
5131: END IF;
5132:
5125: hr_utility_trace ('Leaving '||l_proc_name);
5126: RETURN ('B');
5127: ELSE
5128: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5129: hr_utility_trace ('Leaving '||l_proc_name);
5130: RETURN ('E');
5131: END IF;
5132:
5133: END arch_exists_without_upgrade;
5133: END arch_exists_without_upgrade;
5134:
5135: BEGIN
5136:
5137: --hr_utility.trace_on(null,'SUA');
5138: gv_package := 'pay_mx_soc_sec_archive.';
5139: gv_debug := hr_utility.debug_enabled;
5140:
5141: END PAY_MX_SOC_SEC_ARCHIVE;
5135: BEGIN
5136:
5137: --hr_utility.trace_on(null,'SUA');
5138: gv_package := 'pay_mx_soc_sec_archive.';
5139: gv_debug := hr_utility.debug_enabled;
5140:
5141: END PAY_MX_SOC_SEC_ARCHIVE;