[Home] [Help]
340: gv_discount_value VARCHAR2(240);
341: gv_variable_idw VARCHAR2(10);
342: gv_IDW_calc_method hr_organization_information.org_information10%type;
343:
344: PROCEDURE hr_utility_trace (p_data IN VARCHAR2) IS
345: BEGIN
346: IF gv_debug THEN
347: hr_utility.trace (p_data);
348: END IF;
343:
344: PROCEDURE hr_utility_trace (p_data IN VARCHAR2) IS
345: BEGIN
346: IF gv_debug THEN
347: hr_utility.trace (p_data);
348: END IF;
349: END;
350:
351: FUNCTION event_qualified(p_person_id NUMBER,
381: lv_chk_emp_status VARCHAR2(1);
382:
383: BEGIN
384: l_proc_name := gv_package || 'event_qualified';
385: hr_utility_trace ('Entering '||l_proc_name);
386: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
387: hr_utility_trace ('p_effective_date = '||
388: fnd_date.date_to_canonical(p_effective_date));
389: hr_utility_trace ('p_gre_id = '||p_gre_id);
382:
383: BEGIN
384: l_proc_name := gv_package || 'event_qualified';
385: hr_utility_trace ('Entering '||l_proc_name);
386: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
387: hr_utility_trace ('p_effective_date = '||
388: fnd_date.date_to_canonical(p_effective_date));
389: hr_utility_trace ('p_gre_id = '||p_gre_id);
390:
383: BEGIN
384: l_proc_name := gv_package || 'event_qualified';
385: hr_utility_trace ('Entering '||l_proc_name);
386: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
387: hr_utility_trace ('p_effective_date = '||
388: fnd_date.date_to_canonical(p_effective_date));
389: hr_utility_trace ('p_gre_id = '||p_gre_id);
390:
391: l_return := FALSE;
385: hr_utility_trace ('Entering '||l_proc_name);
386: hr_utility_trace ('p_assignment_id = '||p_assignment_id);
387: hr_utility_trace ('p_effective_date = '||
388: fnd_date.date_to_canonical(p_effective_date));
389: hr_utility_trace ('p_gre_id = '||p_gre_id);
390:
391: l_return := FALSE;
392: -- Check if assignment is cached.
393: IF gt_gre_cache.count() > 0 THEN
392: -- Check if assignment is cached.
393: IF gt_gre_cache.count() > 0 THEN
394: FOR ln_cntr IN gt_gre_cache.first()..gt_gre_cache.last() LOOP
395: IF p_assignment_id = gt_gre_cache(ln_cntr).assignment_id THEN
396: hr_utility_trace('Assignment '||p_assignment_id||
397: ' found in cache.');
398: l_return := TRUE;
399: EXIT;
400: END IF;
415: END IF;
416: END LOOP;
417: ELSE
418: -- Assignment is not cached. Load cache. Check if event is qualified.
419: hr_utility_trace('Assignment '||p_assignment_id||
420: ' not found in cache. Hitting database now.');
421: l_return := FALSE;
422: FOR csr_asg_rec IN csr_asg LOOP
423: ln_cntr := gt_gre_cache.count();
436: l_return := TRUE;
437: END IF;
438: END LOOP;
439: END IF;
440: hr_utility_trace('Checking for applicant record');
441: OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_effective_date));
442: FETCH c_check_per_status INTO lv_chk_emp_status;
443: CLOSE c_check_per_status;
444:
447: END IF;
448:
449:
450: IF l_return THEN
451: hr_utility_trace ('Event qualified.');
452: ELSE
453: hr_utility_trace ('Event not qualified.');
454: END IF;
455: hr_utility_trace ('Leaving '||l_proc_name);
449:
450: IF l_return THEN
451: hr_utility_trace ('Event qualified.');
452: ELSE
453: hr_utility_trace ('Event not qualified.');
454: END IF;
455: hr_utility_trace ('Leaving '||l_proc_name);
456: RETURN (l_return);
457: END event_qualified;
451: hr_utility_trace ('Event qualified.');
452: ELSE
453: hr_utility_trace ('Event not qualified.');
454: END IF;
455: hr_utility_trace ('Leaving '||l_proc_name);
456: RETURN (l_return);
457: END event_qualified;
458:
459: /*8438074 Begin*/
522: ln_count NUMBER;
523:
524: BEGIN
525:
526: hr_utility_trace('p_gre_id '||nvl( p_gre_id, -999));
527:
528: pay_recorded_requests_pkg.get_recorded_date_no_ins(
529: p_process => 'MX_SOC_SEC_ARCH',
530: p_recorded_date => ld_start_date,
566: OPEN c_get_bus_grp_id(p_gre_id);
567: FETCH c_get_bus_grp_id INTO ln_bus_grp_id;
568: CLOSE c_get_bus_grp_id;
569:
570: hr_utility_trace('ln_bus_grp_id '||ln_bus_grp_id);
571:
572: SELECT count(*)
573: INTO ln_count
574: FROM fnd_sessions
573: INTO ln_count
574: FROM fnd_sessions
575: WHERE session_id = USERENV('sessionid');
576:
577: hr_utility_trace('ln_count '||ln_count);
578:
579: ln_legal_employer_id :=
580: hr_mx_utility.get_legal_employer(ln_bus_grp_id, p_gre_id);
581:
578:
579: ln_legal_employer_id :=
580: hr_mx_utility.get_legal_employer(ln_bus_grp_id, p_gre_id);
581:
582: hr_utility_trace('ln_legal_employer_id '||ln_legal_employer_id);
583:
584: -- get the report Implementation Date from p_legal_emp_id
585:
586: OPEN c_get_imp_date(ln_legal_employer_id);
596: END IF;
597:
598: CLOSE c_get_imp_date;
599:
600: hr_utility_trace('ld_report_imp_date '||ld_report_imp_date);
601:
602: ln_tax_unit_id := to_number(p_gre_id) ;
603:
604: OPEN c_get_start_date(ln_tax_unit_id);
612: END IF;
613:
614: CLOSE c_get_start_date;
615:
616: hr_utility_trace('lv_start_date '||lv_start_date);
617:
618: ld_start_date := fnd_date.canonical_to_date(lv_start_date) ;
619:
620: hr_utility_trace('ld_start_date '||ld_start_date);
616: hr_utility_trace('lv_start_date '||lv_start_date);
617:
618: ld_start_date := fnd_date.canonical_to_date(lv_start_date) ;
619:
620: hr_utility_trace('ld_start_date '||ld_start_date);
621:
622: ELSE
623:
624: SELECT fnd_date.date_to_canonical(sysdate)
628: END IF; -- p_gre_id IS NOT NULL
629:
630: END IF; -- ld_start_date <> hr_api.g_sot
631:
632: hr_utility_trace('lv_start_date '||lv_start_date);
633:
634: RETURN lv_start_date ;
635:
636: END get_start_date;
700: BEGIN
701:
702: lv_procedure_name := 'get_payroll_action_info';
703:
704: hr_utility.set_location(gv_package || lv_procedure_name, 10);
705: ln_step := 1;
706:
707: OPEN c_payroll_action_info(p_payroll_action_id);
708: FETCH c_payroll_action_info INTO ln_business_group_id
712: ,lv_mode
713: ,lv_periodic_end_date;
714: CLOSE c_payroll_action_info;
715:
716: hr_utility.set_location(gv_package || lv_procedure_name, 30);
717:
718: IF lv_periodic_end_date IS NOT NULL THEN
719:
720: IF TRUNC(fnd_date.canonical_to_date(lv_end_date)) -
745: p_business_group_id := ln_business_group_id;
746: p_gre_id := ln_gre_id;
747: gv_mode := lv_mode;
748:
749: hr_utility.set_location(gv_package || lv_procedure_name, 50);
750: ln_step := 2;
751:
752: IF gv_mode = 'P' THEN
753:
761: gv_periodic_start_date := lv_start_date;
762:
763: END IF;
764:
765: hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
766:
767: END get_payroll_action_info;
768:
769: /********************************************************************
870: BEGIN
871:
872: lv_procedure_name := 'get_idw';
873:
874: hr_utility.set_location(gv_package || lv_procedure_name, 10);
875: ln_step := 1;
876:
877: ln_min_wage := 0;
878: ln_idw := 0;
878: ln_idw := 0;
879: ln_fixed_idw := 0;
880: ln_variable_idw := 0;
881:
882: hr_utility.set_location(gv_package || lv_procedure_name, 20);
883:
884: -- get the minimum wage for Zone A ( Mexico City )
885:
886: OPEN c_minimum_wage_zonea(p_effective_date);
886: OPEN c_minimum_wage_zonea(p_effective_date);
887: FETCH c_minimum_wage_zonea INTO ln_min_wage;
888: CLOSE c_minimum_wage_zonea;
889:
890: hr_utility.set_location(gv_package || lv_procedure_name, 30);
891:
892:
893: BEGIN
894:
891:
892:
893: BEGIN
894:
895: hr_utility.set_location(gv_package || lv_procedure_name, 40);
896:
897: ln_step := 2;
898:
899: lv_mode := 'REPORT';
908: ,p_variable_idw => ln_variable_idw
909: ,p_execute_old_idw_code => 'Y'
910: );
911:
912: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
913: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
914: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
915:
916: hr_utility.set_location(gv_package || lv_procedure_name, 50);
909: ,p_execute_old_idw_code => 'Y'
910: );
911:
912: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
913: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
914: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
915:
916: hr_utility.set_location(gv_package || lv_procedure_name, 50);
917:
910: );
911:
912: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
913: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
914: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
915:
916: hr_utility.set_location(gv_package || lv_procedure_name, 50);
917:
918: EXCEPTION WHEN others THEN
912: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
913: hr_utility.trace('SS_ARCH get_idw ln_fixed_idw: '|| ln_fixed_idw);
914: hr_utility.trace('SS_ARCH get_idw ln_variable_idw: '|| ln_variable_idw);
915:
916: hr_utility.set_location(gv_package || lv_procedure_name, 50);
917:
918: EXCEPTION WHEN others THEN
919: hr_utility.set_location(gv_package || lv_procedure_name, 60);
920: NULL;
915:
916: hr_utility.set_location(gv_package || lv_procedure_name, 50);
917:
918: EXCEPTION WHEN others THEN
919: hr_utility.set_location(gv_package || lv_procedure_name, 60);
920: NULL;
921:
922: END;
923:
931: -- end if
932:
933: IF ln_idw > ( 25 * ln_min_wage ) THEN
934: ln_idw := 25 * ln_min_wage;
935: hr_utility.trace('SS_ARCH get_idw ln_idw > 25 * ln_min_wage');
936: hr_utility.trace('25 times of zone A minimum wage');
937: END IF;
938:
939: hr_utility.set_location(gv_package || lv_procedure_name, 70);
932:
933: IF ln_idw > ( 25 * ln_min_wage ) THEN
934: ln_idw := 25 * ln_min_wage;
935: hr_utility.trace('SS_ARCH get_idw ln_idw > 25 * ln_min_wage');
936: hr_utility.trace('25 times of zone A minimum wage');
937: END IF;
938:
939: hr_utility.set_location(gv_package || lv_procedure_name, 70);
940:
935: hr_utility.trace('SS_ARCH get_idw ln_idw > 25 * ln_min_wage');
936: hr_utility.trace('25 times of zone A minimum wage');
937: END IF;
938:
939: hr_utility.set_location(gv_package || lv_procedure_name, 70);
940:
941: -- round to 2 decimal and archive
942:
943: p_fixed_idw := ROUND(LEAST(ln_fixed_idw, 25 * ln_min_wage), 2);
942:
943: p_fixed_idw := ROUND(LEAST(ln_fixed_idw, 25 * ln_min_wage), 2);
944: p_variable_idw := ROUND(LEAST(ln_variable_idw, 25 * ln_min_wage), 2);
945:
946: hr_utility.trace('SS_ARCH get_idw p_fixed_idw: '|| p_fixed_idw);
947: hr_utility.trace('SS_ARCH get_idw p_variable_idw: '|| p_variable_idw);
948:
949: ln_idw := round(ln_idw,2);
950:
943: p_fixed_idw := ROUND(LEAST(ln_fixed_idw, 25 * ln_min_wage), 2);
944: p_variable_idw := ROUND(LEAST(ln_variable_idw, 25 * ln_min_wage), 2);
945:
946: hr_utility.trace('SS_ARCH get_idw p_fixed_idw: '|| p_fixed_idw);
947: hr_utility.trace('SS_ARCH get_idw p_variable_idw: '|| p_variable_idw);
948:
949: ln_idw := round(ln_idw,2);
950:
951: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
947: hr_utility.trace('SS_ARCH get_idw p_variable_idw: '|| p_variable_idw);
948:
949: ln_idw := round(ln_idw,2);
950:
951: hr_utility.trace('SS_ARCH get_idw ln_idw: '|| ln_idw);
952:
953: RETURN ln_idw;
954:
955: EXCEPTION
956: WHEN others THEN
957: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
958: gv_package || lv_procedure_name;
959:
960: hr_utility_trace(lv_error_message || '-' || sqlerrm);
961:
962: lv_error_message :=
963: pay_emp_action_arch.set_error_message(lv_error_message);
964:
961:
962: lv_error_message :=
963: pay_emp_action_arch.set_error_message(lv_error_message);
964:
965: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
966: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
967: hr_utility.raise_error;
968:
969: END get_idw;
962: lv_error_message :=
963: pay_emp_action_arch.set_error_message(lv_error_message);
964:
965: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
966: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
967: hr_utility.raise_error;
968:
969: END get_idw;
970:
963: pay_emp_action_arch.set_error_message(lv_error_message);
964:
965: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
966: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
967: hr_utility.raise_error;
968:
969: END get_idw;
970:
971: PROCEDURE arch_pay_action_level_data(
1012: BEGIN
1013:
1014: lv_procedure_name := 'arch_pay_action_level_data';
1015:
1016: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1017: ln_step := 1;
1018:
1019: OPEN c_get_org_information(p_tax_unit_id);
1020: FETCH c_get_org_information INTO lv_soc_sec_id
1022: ,lv_waybill_no
1023: ,lv_transmitter_gre_id;
1024: CLOSE c_get_org_information;
1025:
1026: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1027: ln_step := 2;
1028:
1029: OPEN c_org_name(p_tax_unit_id);
1030: FETCH c_org_name INTO lv_gre_name;
1029: OPEN c_org_name(p_tax_unit_id);
1030: FETCH c_org_name INTO lv_gre_name;
1031: CLOSE c_org_name;
1032:
1033: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1034: ln_step := 3;
1035:
1036: IF lv_transmitter = 'Y' THEN
1037:
1041: ELSE
1042:
1043: IF lv_transmitter IS NOT NULL THEN
1044:
1045: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1046: ln_step := 4;
1047:
1048: OPEN c_org_name(lv_transmitter_gre_id);
1049: FETCH c_org_name INTO lv_transmitter_gre_name;
1048: OPEN c_org_name(lv_transmitter_gre_id);
1049: FETCH c_org_name INTO lv_transmitter_gre_name;
1050: CLOSE c_org_name;
1051:
1052: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1053: ln_step := 5;
1054:
1055: OPEN c_waybill_of_trnsmtr(lv_transmitter_gre_id);
1056: FETCH c_waybill_of_trnsmtr INTO lv_waybill_no;
1059: END IF; -- lv_transmitter
1060:
1061: END IF;
1062:
1063: hr_utility.set_location(gv_package || lv_procedure_name, 60);
1064: ln_step := 6;
1065:
1066: ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
1067:
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 arch_pay_action_level_data;
1099: lv_error_message :=
1100: pay_emp_action_arch.set_error_message(lv_error_message);
1101:
1102: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1103: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1104: hr_utility.raise_error;
1105:
1106: END arch_pay_action_level_data;
1107:
1100: pay_emp_action_arch.set_error_message(lv_error_message);
1101:
1102: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1103: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1104: hr_utility.raise_error;
1105:
1106: END arch_pay_action_level_data;
1107:
1108: PROCEDURE chk_person_rec_chng (
1120: BEGIN
1121:
1122: lv_procedure_name := 'chk_person_rec_chng';
1123:
1124: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1125: ln_step := 1;
1126:
1127:
1128: FOR i IN p_per_events.FIRST..p_per_events.LAST LOOP
1126:
1127:
1128: FOR i IN p_per_events.FIRST..p_per_events.LAST LOOP
1129:
1130: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1131: ln_step := 2;
1132:
1133: lv_change_values := p_per_events(i).change_values ;
1134:
1137:
1138: lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,
1139: INSTR(lv_change_values,'->')+3)));
1140:
1141: hr_utility_trace('lv_change_values : '||lv_change_values);
1142: hr_utility_trace('lv_old_value : '||lv_old_value);
1143: hr_utility_trace('lv_new_value : '||lv_new_value);
1144: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1145:
1138: lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,
1139: INSTR(lv_change_values,'->')+3)));
1140:
1141: hr_utility_trace('lv_change_values : '||lv_change_values);
1142: hr_utility_trace('lv_old_value : '||lv_old_value);
1143: hr_utility_trace('lv_new_value : '||lv_new_value);
1144: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1145:
1146: IF p_per_events(i).column_name in ( 'LAST_NAME', 'FIRST_NAME',
1139: INSTR(lv_change_values,'->')+3)));
1140:
1141: hr_utility_trace('lv_change_values : '||lv_change_values);
1142: hr_utility_trace('lv_old_value : '||lv_old_value);
1143: hr_utility_trace('lv_new_value : '||lv_new_value);
1144: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1145:
1146: IF p_per_events(i).column_name in ( 'LAST_NAME', 'FIRST_NAME',
1147: 'MIDDLE_NAMES', 'PER_INFORMATION1',
1140:
1141: hr_utility_trace('lv_change_values : '||lv_change_values);
1142: hr_utility_trace('lv_old_value : '||lv_old_value);
1143: hr_utility_trace('lv_new_value : '||lv_new_value);
1144: hr_utility_trace('column_name : '||p_per_events(i).column_name);
1145:
1146: IF p_per_events(i).column_name in ( 'LAST_NAME', 'FIRST_NAME',
1147: 'MIDDLE_NAMES', 'PER_INFORMATION1',
1148: 'PER_INFORMATION4') -- Bug 5885473
1158: END IF;
1159:
1160: END LOOP;
1161:
1162: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1163: ln_step := 3;
1164:
1165: EXCEPTION
1166: WHEN others THEN
1166: WHEN others THEN
1167: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1168: gv_package || lv_procedure_name;
1169:
1170: hr_utility_trace(lv_error_message || '-' || sqlerrm);
1171:
1172: lv_error_message :=
1173: pay_emp_action_arch.set_error_message(lv_error_message);
1174:
1171:
1172: lv_error_message :=
1173: pay_emp_action_arch.set_error_message(lv_error_message);
1174:
1175: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1176: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1177: hr_utility.raise_error;
1178:
1179: END chk_person_rec_chng;
1172: lv_error_message :=
1173: pay_emp_action_arch.set_error_message(lv_error_message);
1174:
1175: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1176: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1177: hr_utility.raise_error;
1178:
1179: END chk_person_rec_chng;
1180:
1173: pay_emp_action_arch.set_error_message(lv_error_message);
1174:
1175: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1176: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1177: hr_utility.raise_error;
1178:
1179: END chk_person_rec_chng;
1180:
1181: PROCEDURE arch_other_transactions (
1544: p_gre_id) THEN
1545: -- Archive a 07 only if current person is not a new hire
1546: -- (Bug 6021768)
1547: lb_new_hire := FALSE;
1548: hr_utility_trace('Checking for hire ');
1549: hr_utility_trace('Archived SS transaxtions ' ||pay_mx_soc_sec_archive.lrr_act_tab.COUNT());
1550: hr_utility_trace('p_effective_date ' || p_effective_date);
1551:
1552: OPEN c_first_sal_date;
1545: -- Archive a 07 only if current person is not a new hire
1546: -- (Bug 6021768)
1547: lb_new_hire := FALSE;
1548: hr_utility_trace('Checking for hire ');
1549: hr_utility_trace('Archived SS transaxtions ' ||pay_mx_soc_sec_archive.lrr_act_tab.COUNT());
1550: hr_utility_trace('p_effective_date ' || p_effective_date);
1551:
1552: OPEN c_first_sal_date;
1553: FETCH c_first_sal_date into ld_effective_date;
1546: -- (Bug 6021768)
1547: lb_new_hire := FALSE;
1548: hr_utility_trace('Checking for hire ');
1549: hr_utility_trace('Archived SS transaxtions ' ||pay_mx_soc_sec_archive.lrr_act_tab.COUNT());
1550: hr_utility_trace('p_effective_date ' || p_effective_date);
1551:
1552: OPEN c_first_sal_date;
1553: FETCH c_first_sal_date into ld_effective_date;
1554: CLOSE c_first_sal_date;
1555:
1556: IF pay_mx_soc_sec_archive.lrr_act_tab.COUNT() > 0 THEN --Bug 6060052
1557: FOR cntr IN pay_mx_soc_sec_archive.lrr_act_tab.FIRST()..
1558: pay_mx_soc_sec_archive.lrr_act_tab.LAST() LOOP
1559: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category ' ||
1560: pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category);
1561: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) ' ||
1562: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2);
1563: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 ' ||
1557: FOR cntr IN pay_mx_soc_sec_archive.lrr_act_tab.FIRST()..
1558: pay_mx_soc_sec_archive.lrr_act_tab.LAST() LOOP
1559: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category ' ||
1560: pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category);
1561: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) ' ||
1562: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2);
1563: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 ' ||
1564: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4);
1565:
1559: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category ' ||
1560: pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category);
1561: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2) ' ||
1562: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info2);
1563: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 ' ||
1564: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4);
1565:
1566: IF pay_mx_soc_sec_archive.lrr_act_tab(cntr).action_info_category
1567: = 'MX SS TRANSACTIONS' AND
1578: = 'MX SS TRANSACTIONS' AND
1579: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info4 = '08'
1580: THEN
1581: lb_new_hire := TRUE;
1582: hr_utility_trace('Going to run the get_idw to get the correct IDW for the first salary.');
1583: ln_idw := get_idw( p_assignment_id => p_assignment_id
1584: ,p_tax_unit_id => p_gre_id
1585: ,p_effective_date => ld_effective_date
1586: ,p_fixed_idw => ln_fix_idw
1585: ,p_effective_date => ld_effective_date
1586: ,p_fixed_idw => ln_fix_idw
1587: ,p_variable_idw => ln_var_idw );
1588: pay_mx_soc_sec_archive.lrr_act_tab(cntr).act_info8:= ln_idw;
1589: hr_utility_trace('Exiting the ELSIF part to prevent 07 transaction');
1590: EXIT;
1591: END IF;
1592: END LOOP;
1593: END IF;
1601: fix_var_idw(ln_count).idw_type := 'VARIABLE';
1602: fix_var_idw(ln_count).idw_date := p_effective_date;
1603: END IF;
1604: ELSE
1605: hr_utility_trace('Current person is a new hire. 07 effective '||
1606: 'on '||fnd_date.date_to_canonical(p_effective_date)
1607: ||' will not be archived.');
1608: END IF;
1609: END IF;
1621: OPEN c_all_ele_entries (p_effective_date);
1622: LOOP
1623: FETCH c_all_ele_entries INTO ln_element_entry_id;
1624: EXIT WHEN c_all_ele_entries%NOTFOUND;
1625: hr_utility_trace('Element Entry ID = '||
1626: ln_element_entry_id);
1627:
1628: OPEN c_ele_type_id (ln_element_entry_id,
1629: p_effective_date);
1639: FETCH c_ele_extra_info INTO lv_idw_type;
1640: CLOSE c_ele_extra_info;
1641:
1642: IF lv_idw_type IS NOT NULL THEN
1643: hr_utility_trace('element entry id '||
1644: ln_element_entry_id||' has following IDW info: -');
1645: hr_utility_trace('IDW type = '||lv_idw_type);
1646: hr_utility_trace('IDW effective date = '||
1647: fnd_date.date_to_canonical(p_effective_date));
1641:
1642: IF lv_idw_type IS NOT NULL THEN
1643: hr_utility_trace('element entry id '||
1644: ln_element_entry_id||' has following IDW info: -');
1645: hr_utility_trace('IDW type = '||lv_idw_type);
1646: hr_utility_trace('IDW effective date = '||
1647: fnd_date.date_to_canonical(p_effective_date));
1648: ELSE
1649: hr_utility_trace('element entry id '||
1642: IF lv_idw_type IS NOT NULL THEN
1643: hr_utility_trace('element entry id '||
1644: ln_element_entry_id||' has following IDW info: -');
1645: hr_utility_trace('IDW type = '||lv_idw_type);
1646: hr_utility_trace('IDW effective date = '||
1647: fnd_date.date_to_canonical(p_effective_date));
1648: ELSE
1649: hr_utility_trace('element entry id '||
1650: ln_element_entry_id||' has no IDW information.');
1645: hr_utility_trace('IDW type = '||lv_idw_type);
1646: hr_utility_trace('IDW effective date = '||
1647: fnd_date.date_to_canonical(p_effective_date));
1648: ELSE
1649: hr_utility_trace('element entry id '||
1650: ln_element_entry_id||' has no IDW information.');
1651: END IF;
1652: cache_IDW_date (lv_idw_type,
1653: p_effective_date);
1777: THEN
1778: trn(ln_trn_cnt).type := p_tran_type;
1779: trn(ln_trn_cnt).date := fnd_date.date_to_canonical(
1780: p_effective_end_date + 1);
1781: hr_utility_trace('Transaction '|| p_tran_type ||' found.');
1782: lb_tran_found := TRUE;
1783: EXIT;
1784: END IF;
1785: END LOOP;
1814: BEGIN -- Main
1815:
1816: lv_procedure_name := 'arch_other_transactions';
1817:
1818: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1819: ln_step := 1;
1820:
1821: fix_var_idw.DELETE;
1822: fix_var_idw_uniq.DELETE;
1821: fix_var_idw.DELETE;
1822: fix_var_idw_uniq.DELETE;
1823: prev_eff_date := fnd_date.canonical_to_date('0001/01/01');
1824:
1825: hr_utility.set_location(gv_package || lv_procedure_name, 910);
1826: ln_step := 91;
1827:
1828: OPEN c_get_org_information(p_gre_id);
1829: FETCH c_get_org_information INTO lv_employer_ss_id;
1828: OPEN c_get_org_information(p_gre_id);
1829: FETCH c_get_org_information INTO lv_employer_ss_id;
1830: CLOSE c_get_org_information;
1831:
1832: hr_utility.set_location(gv_package || lv_procedure_name, 920);
1833: ln_step := 92;
1834:
1835: hr_utility.set_location(gv_package || lv_procedure_name, 930);
1836: ln_step := 93;
1831:
1832: hr_utility.set_location(gv_package || lv_procedure_name, 920);
1833: ln_step := 92;
1834:
1835: hr_utility.set_location(gv_package || lv_procedure_name, 930);
1836: ln_step := 93;
1837:
1838: IF gv_IDW_calc_method = 'B' THEN
1839: /*-- IDW factor table support --*/
1840: OPEN c_IDW_events;
1841: LOOP
1842: FETCH c_IDW_events INTO ld_effective_date;
1843: EXIT WHEN c_IDW_events%NOTFOUND;
1844: hr_utility_trace('Timestamp of IDW table update event = '||
1845: fnd_date.date_to_canonical(ld_effective_date));
1846: parse_all_ele_entries (ld_effective_date);
1847: END LOOP;
1848: CLOSE c_IDW_events;
1850: /*-- Support for change in seniority --*/
1851: IF pay_mx_soc_sec_archive.seniority_changed (p_person_id,
1852: p_end_date,
1853: p_start_date) = 'Y' THEN
1854: hr_utility_trace ('Seniority of person '||p_person_id||
1855: ' has changed since last archiver run. IDW will'||
1856: ' be recomputed for this person.');
1857: -- Get hire anniversary date
1858: ld_hire_anniversary := hr_mx_utility.get_hire_anniversary(
1857: -- Get hire anniversary date
1858: ld_hire_anniversary := hr_mx_utility.get_hire_anniversary(
1859: p_person_id,
1860: p_end_date);
1861: hr_utility_trace ('Hire anniversary date of person '||p_person_id||
1862: ' = '||fnd_date.date_to_canonical(ld_hire_anniversary));
1863:
1864: -- Calculate anniversary date in current year
1865: SELECT ADD_MONTHS (TRUNC (p_end_date, 'Y'),
1869: (ld_hire_anniversary - TRUNC (ld_hire_anniversary, 'MM'))
1870: INTO ld_anniversary_date
1871: FROM dual;
1872:
1873: hr_utility_trace ('Anniversary date of person '||p_person_id||
1874: ' in the year of archiver run = '||
1875: fnd_date.date_to_canonical(ld_anniversary_date));
1876:
1877: parse_all_ele_entries (ld_anniversary_date);
1894: EXIT WHEN c_ele_entries%NOTFOUND;
1895: /* Adding event qualification mechanism so that only those events that
1896: belong to current GRE are picked for archival. (Bug 5921945)*/
1897:
1898: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1899: ln_step := 2;
1900:
1901: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1902: hr_utility_trace('lv_change_values :' || lv_change_values);
1897:
1898: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1899: ln_step := 2;
1900:
1901: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1902: hr_utility_trace('lv_change_values :' || lv_change_values);
1903: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1904: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1905: hr_utility_trace('lv_event_type :' || lv_event_type);
1898: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1899: ln_step := 2;
1900:
1901: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1902: hr_utility_trace('lv_change_values :' || lv_change_values);
1903: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1904: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1905: hr_utility_trace('lv_event_type :' || lv_event_type);
1906:
1899: ln_step := 2;
1900:
1901: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1902: hr_utility_trace('lv_change_values :' || lv_change_values);
1903: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1904: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1905: hr_utility_trace('lv_event_type :' || lv_event_type);
1906:
1907: OPEN c_ele_type_id ( ln_element_entry_id
1900:
1901: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1902: hr_utility_trace('lv_change_values :' || lv_change_values);
1903: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1904: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1905: hr_utility_trace('lv_event_type :' || lv_event_type);
1906:
1907: OPEN c_ele_type_id ( ln_element_entry_id
1908: ,ld_effective_date );
1901: hr_utility_trace('ld_effective_date :' || ld_effective_date);
1902: hr_utility_trace('lv_change_values :' || lv_change_values);
1903: hr_utility_trace('ln_element_entry_id :' || ln_element_entry_id);
1904: hr_utility_trace('ld_calculation_date :' || ld_calculation_date);
1905: hr_utility_trace('lv_event_type :' || lv_event_type);
1906:
1907: OPEN c_ele_type_id ( ln_element_entry_id
1908: ,ld_effective_date );
1909: FETCH c_ele_type_id INTO ln_element_type_id
1911: ,ld_ee_eff_start_date
1912: ,ld_ee_eff_end_date;
1913: CLOSE c_ele_type_id;
1914:
1915: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1916: ln_step := 3;
1917:
1918: hr_utility_trace('ln_element_type_id :' || ln_element_type_id);
1919:
1914:
1915: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1916: ln_step := 3;
1917:
1918: hr_utility_trace('ln_element_type_id :' || ln_element_type_id);
1919:
1920: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1921: ln_step := 4;
1922:
1916: ln_step := 3;
1917:
1918: hr_utility_trace('ln_element_type_id :' || ln_element_type_id);
1919:
1920: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1921: ln_step := 4;
1922:
1923: lv_idw_type := NULL;
1924: OPEN c_ele_extra_info (ln_element_type_id, ld_effective_date);
1927: INTO lv_idw_type;
1928:
1929: CLOSE c_ele_extra_info;
1930:
1931: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1932: ln_step := 5;
1933:
1934: hr_utility_trace('IDW_TYPE :' || lv_idw_type);
1935:
1930:
1931: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1932: ln_step := 5;
1933:
1934: hr_utility_trace('IDW_TYPE :' || lv_idw_type);
1935:
1936: IF lv_idw_type IS NOT NULL THEN
1937: cache_IDW_date (lv_idw_type,
1938: ld_effective_date);
1939: END IF;
1940:
1941: IF lv_creator_type = 'A' THEN
1942:
1943: hr_utility.set_location(gv_package || lv_procedure_name, 60);
1944: ln_step := 6;
1945:
1946: ln_abs_attend_type_id := 0;
1947:
1954: ,ld_ee_eff_end_date );
1955: --,p_start_date
1956: --,p_end_date );
1957:
1958: hr_utility.set_location(gv_package || lv_procedure_name, 70);
1959: ln_step := 7;
1960:
1961: LOOP
1962:
1970: ,lv_disability_id;
1971:
1972: EXIT WHEN c_abs_info%NOTFOUND;
1973:
1974: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1975: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1976: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1971:
1972: EXIT WHEN c_abs_info%NOTFOUND;
1973:
1974: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1975: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1976: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1979: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1972: EXIT WHEN c_abs_info%NOTFOUND;
1973:
1974: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1975: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1976: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1979: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1980: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1973:
1974: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1975: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1976: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1979: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1980: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1981: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1974: hr_utility_trace('ln_abs_attend_type_id : '||ln_abs_attend_type_id);
1975: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1976: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1979: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1980: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1981: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1982:
1975: hr_utility_trace('ln_abs_attendance_id : '||ln_abs_attendance_id);
1976: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1979: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1980: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1981: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1982:
1983: IF event_qualified (p_person_id,
1976: hr_utility_trace('ln_absence_days : '|| ln_absence_days);
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1979: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1980: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1981: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1982:
1983: IF event_qualified (p_person_id,
1984: p_assignment_id,
1977: hr_utility_trace('ld_date_start : '|| ld_date_start);
1978: hr_utility_trace('ld_date_end : '|| ld_date_end);
1979: hr_utility_trace('lv_abs_info_category: '|| lv_abs_info_category);
1980: --hr_utility_trace('lv_disability_type : '|| lv_disability_type);
1981: hr_utility_trace('lv_disability_id : '|| lv_disability_id);
1982:
1983: IF event_qualified (p_person_id,
1984: p_assignment_id,
1985: ld_date_start,
2043: OPEN c_get_infonavit(ln_element_type_id);
2044: FETCH c_get_infonavit INTO lv_infonavit;
2045: CLOSE c_get_infonavit;
2046:
2047: hr_utility_trace('lv_infonavit : '|| nvl(lv_infonavit, 'NULL'));
2048:
2049: IF lv_infonavit = 'INFONAVIT' THEN
2050:
2051: lb_tran_16_found := FALSE;
2055: ,ld_ee_eff_start_date
2056: ,ld_ee_eff_end_date)
2057: LOOP
2058:
2059: hr_utility_trace('----------------------');
2060: hr_utility_trace('name : '|| infonavit.name);
2061: hr_utility_trace('screen_entry_value : '||
2062: infonavit.screen_entry_value);
2063: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2056: ,ld_ee_eff_end_date)
2057: LOOP
2058:
2059: hr_utility_trace('----------------------');
2060: hr_utility_trace('name : '|| infonavit.name);
2061: hr_utility_trace('screen_entry_value : '||
2062: infonavit.screen_entry_value);
2063: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2064: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2057: LOOP
2058:
2059: hr_utility_trace('----------------------');
2060: hr_utility_trace('name : '|| infonavit.name);
2061: hr_utility_trace('screen_entry_value : '||
2062: infonavit.screen_entry_value);
2063: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2064: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2065: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
2059: hr_utility_trace('----------------------');
2060: hr_utility_trace('name : '|| infonavit.name);
2061: hr_utility_trace('screen_entry_value : '||
2062: infonavit.screen_entry_value);
2063: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2064: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2065: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
2066: hr_utility_trace('effective_start_date : '||
2067: infonavit.effective_start_date);
2060: hr_utility_trace('name : '|| infonavit.name);
2061: hr_utility_trace('screen_entry_value : '||
2062: infonavit.screen_entry_value);
2063: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2064: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2065: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
2066: hr_utility_trace('effective_start_date : '||
2067: infonavit.effective_start_date);
2068: hr_utility_trace('effective_end_date : '||
2061: hr_utility_trace('screen_entry_value : '||
2062: infonavit.screen_entry_value);
2063: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2064: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2065: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
2066: hr_utility_trace('effective_start_date : '||
2067: infonavit.effective_start_date);
2068: hr_utility_trace('effective_end_date : '||
2069: infonavit.effective_end_date);
2062: infonavit.screen_entry_value);
2063: hr_utility_trace('input_value_id : '|| infonavit.input_value_id);
2064: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2065: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
2066: hr_utility_trace('effective_start_date : '||
2067: infonavit.effective_start_date);
2068: hr_utility_trace('effective_end_date : '||
2069: infonavit.effective_end_date);
2070:
2064: hr_utility_trace('element_entry_id:'||infonavit.element_entry_id);
2065: hr_utility_trace('assignment_id : '|| infonavit.assignment_id);
2066: hr_utility_trace('effective_start_date : '||
2067: infonavit.effective_start_date);
2068: hr_utility_trace('effective_end_date : '||
2069: infonavit.effective_end_date);
2070:
2071: IF infonavit.name = 'Credit Number' THEN
2072:
2138: ,ld_ee_eff_start_date
2139: ,ld_ee_eff_end_date
2140: ,ln_trn_cnt);
2141: lb_tran_16_found := TRUE;
2142: hr_utility_trace ('Transaction 16 found.');
2143:
2144: END IF;
2145:
2146: ELSIF infonavit.name = 'Transaction Type' AND
2151:
2152: IF infonavit.screen_entry_value = 'CREDIT_BEGIN' THEN
2153:
2154: trn(ln_trn_cnt).type := '15';
2155: hr_utility_trace ('Transaction 15 found.');
2156:
2157: ELSIF infonavit.screen_entry_value = 'DISC_RESUME' THEN
2158:
2159: trn(ln_trn_cnt).type := '17';
2156:
2157: ELSIF infonavit.screen_entry_value = 'DISC_RESUME' THEN
2158:
2159: trn(ln_trn_cnt).type := '17';
2160: hr_utility_trace ('Transaction 17 found.');
2161:
2162: END IF;
2163:
2164: -- gv_credit_start_date is already in canonical date format.
2197: FOR i in fix_var_idw.FIRST..fix_var_idw.LAST
2198: LOOP
2199:
2200: lv_fix_var_idw_found := 'N';
2201: hr_utility_trace('fix_var_idw(i).idw_type '||i||': '||
2202: fix_var_idw(i).idw_type );
2203: hr_utility_trace('fix_var_idw(i).idw_date '||i||': '||
2204: fix_var_idw(i).idw_date );
2205: hr_utility_trace('---------------------------------------');
2199:
2200: lv_fix_var_idw_found := 'N';
2201: hr_utility_trace('fix_var_idw(i).idw_type '||i||': '||
2202: fix_var_idw(i).idw_type );
2203: hr_utility_trace('fix_var_idw(i).idw_date '||i||': '||
2204: fix_var_idw(i).idw_date );
2205: hr_utility_trace('---------------------------------------');
2206:
2207: IF fix_var_idw_uniq.COUNT > 0 THEN
2201: hr_utility_trace('fix_var_idw(i).idw_type '||i||': '||
2202: fix_var_idw(i).idw_type );
2203: hr_utility_trace('fix_var_idw(i).idw_date '||i||': '||
2204: fix_var_idw(i).idw_date );
2205: hr_utility_trace('---------------------------------------');
2206:
2207: IF fix_var_idw_uniq.COUNT > 0 THEN
2208:
2209: FOR j in fix_var_idw_uniq.FIRST..fix_var_idw_uniq.LAST
2207: IF fix_var_idw_uniq.COUNT > 0 THEN
2208:
2209: FOR j in fix_var_idw_uniq.FIRST..fix_var_idw_uniq.LAST
2210: LOOP
2211: hr_utility_trace('fix_var_idw_uniq(j).idw_type '||j||': '||
2212: fix_var_idw_uniq(j).idw_type );
2213: hr_utility_trace('fix_var_idw_uniq(j).idw_date '||j||': '||
2214: fix_var_idw_uniq(j).idw_date );
2215:
2209: FOR j in fix_var_idw_uniq.FIRST..fix_var_idw_uniq.LAST
2210: LOOP
2211: hr_utility_trace('fix_var_idw_uniq(j).idw_type '||j||': '||
2212: fix_var_idw_uniq(j).idw_type );
2213: hr_utility_trace('fix_var_idw_uniq(j).idw_date '||j||': '||
2214: fix_var_idw_uniq(j).idw_date );
2215:
2216: IF fix_var_idw(i).idw_type = fix_var_idw_uniq(j).idw_type AND
2217: fix_var_idw(i).idw_date = fix_var_idw_uniq(j).idw_date THEN
2216: IF fix_var_idw(i).idw_type = fix_var_idw_uniq(j).idw_type AND
2217: fix_var_idw(i).idw_date = fix_var_idw_uniq(j).idw_date THEN
2218:
2219: lv_fix_var_idw_found := 'Y';
2220: hr_utility_trace('FOUND');
2221:
2222: END IF;
2223:
2224: END LOOP;
2224: END LOOP;
2225:
2226: END IF;
2227:
2228: hr_utility_trace('---------------------------------------');
2229:
2230: IF lv_fix_var_idw_found = 'N' AND
2231: fix_var_idw(i).idw_type = 'FIXED' THEN
2232:
2229:
2230: IF lv_fix_var_idw_found = 'N' AND
2231: fix_var_idw(i).idw_type = 'FIXED' THEN
2232:
2233: hr_utility_trace('NOT FOUND');
2234: hr_utility_trace(' ');
2235: ln_count := fix_var_idw_uniq.COUNT;
2236: fix_var_idw_uniq(ln_count).idw_type := fix_var_idw(i).idw_type;
2237: fix_var_idw_uniq(ln_count).idw_date := fix_var_idw(i).idw_date;
2230: IF lv_fix_var_idw_found = 'N' AND
2231: fix_var_idw(i).idw_type = 'FIXED' THEN
2232:
2233: hr_utility_trace('NOT FOUND');
2234: hr_utility_trace(' ');
2235: ln_count := fix_var_idw_uniq.COUNT;
2236: fix_var_idw_uniq(ln_count).idw_type := fix_var_idw(i).idw_type;
2237: fix_var_idw_uniq(ln_count).idw_date := fix_var_idw(i).idw_date;
2238:
2258: ,p_effective_date => fix_var_idw(i).idw_date
2259: ,p_fixed_idw => ln_fixed_idw
2260: ,p_variable_idw => ln_variable_idw );
2261:
2262: hr_utility.trace('SS_ARCH other TRN ln_idw: '||ln_idw);
2263: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2264: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2265: ln_variable_idw);
2266:
2259: ,p_fixed_idw => ln_fixed_idw
2260: ,p_variable_idw => ln_variable_idw );
2261:
2262: hr_utility.trace('SS_ARCH other TRN ln_idw: '||ln_idw);
2263: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2264: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2265: ln_variable_idw);
2266:
2267: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
2260: ,p_variable_idw => ln_variable_idw );
2261:
2262: hr_utility.trace('SS_ARCH other TRN ln_idw: '||ln_idw);
2263: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2264: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2265: ln_variable_idw);
2266:
2267: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
2268: ln_step := 203;
2263: hr_utility.trace('SS_ARCH other TRN ln_fixed_idw: '||ln_fixed_idw);
2264: hr_utility.trace('SS_ARCH other TRN ln_variable_idw: '||
2265: ln_variable_idw);
2266:
2267: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
2268: ln_step := 203;
2269:
2270: END IF;
2271:
2303: fnd_date.canonical_to_date(gv_periodic_end_date) + 1
2304: ,p_fixed_idw => ln_fixed_idw
2305: ,p_variable_idw => ln_variable_idw );
2306:
2307: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_idw: '||ln_idw);
2308: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_fixed_idw: '||
2309: ln_fixed_idw);
2310: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2311: ln_variable_idw);
2304: ,p_fixed_idw => ln_fixed_idw
2305: ,p_variable_idw => ln_variable_idw );
2306:
2307: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_idw: '||ln_idw);
2308: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_fixed_idw: '||
2309: ln_fixed_idw);
2310: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2311: ln_variable_idw);
2312:
2306:
2307: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_idw: '||ln_idw);
2308: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_fixed_idw: '||
2309: ln_fixed_idw);
2310: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2311: ln_variable_idw);
2312:
2313: hr_utility.set_location(gv_package || lv_procedure_name, 2040);
2314: ln_step := 204;
2309: ln_fixed_idw);
2310: hr_utility.trace('SS_ARCH other TRN VARIABLE ln_variable_idw: '||
2311: ln_variable_idw);
2312:
2313: hr_utility.set_location(gv_package || lv_procedure_name, 2040);
2314: ln_step := 204;
2315:
2316: ln_trn_cnt := trn.COUNT;
2317:
2323: trn(ln_trn_cnt).abs_days := NULL;
2324: trn(ln_trn_cnt).idw_vol_contr := ln_idw;
2325: trn(ln_trn_cnt).salary_type := 'VARIABLE';
2326:
2327: hr_utility.set_location(gv_package || lv_procedure_name, 2050);
2328:
2329: END IF; -- gv_variable_idw = 'Y'
2330:
2331: IF trn.COUNT > 0 THEN
2435: WHEN others THEN
2436: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
2437: gv_package || lv_procedure_name;
2438:
2439: hr_utility_trace(lv_error_message || '-' || sqlerrm);
2440:
2441: lv_error_message :=
2442: pay_emp_action_arch.set_error_message(lv_error_message);
2443:
2440:
2441: lv_error_message :=
2442: pay_emp_action_arch.set_error_message(lv_error_message);
2443:
2444: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2445: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2446: hr_utility.raise_error;
2447:
2448: END arch_other_transactions;
2441: lv_error_message :=
2442: pay_emp_action_arch.set_error_message(lv_error_message);
2443:
2444: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2445: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2446: hr_utility.raise_error;
2447:
2448: END arch_other_transactions;
2449:
2442: pay_emp_action_arch.set_error_message(lv_error_message);
2443:
2444: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2445: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2446: hr_utility.raise_error;
2447:
2448: END arch_other_transactions;
2449:
2450: PROCEDURE arch_hire_separation (
2672: BEGIN
2673:
2674: lv_procedure_name := 'arch_hire_separation';
2675:
2676: hr_utility.set_location(gv_package || lv_procedure_name, 10);
2677: ln_step := 1;
2678:
2679: lv_leaving_reason := NULL;
2680: lv_idw := NULL;
2686: lv_hire := 'N';
2687:
2688: hire_sep.DELETE;
2689:
2690: hr_utility.set_location(gv_package || lv_procedure_name, 20);
2691: ln_step := 2;
2692:
2693: FOR i IN 1..p_asg_events.COUNT
2694: LOOP
2695:
2696: lv_table_name :=
2697: dated_tbls(p_asg_events(i).dated_table_id).table_name;
2698:
2699: hr_utility_trace('-----------------------------------------------');
2700: hr_utility_trace('Result row :' ||to_char(i));
2701: hr_utility_trace('lv_table_name :' ||lv_table_name );
2702: hr_utility_trace('Datetracked_event: '||
2703: p_asg_events(i).datetracked_event );
2696: lv_table_name :=
2697: dated_tbls(p_asg_events(i).dated_table_id).table_name;
2698:
2699: hr_utility_trace('-----------------------------------------------');
2700: hr_utility_trace('Result row :' ||to_char(i));
2701: hr_utility_trace('lv_table_name :' ||lv_table_name );
2702: hr_utility_trace('Datetracked_event: '||
2703: p_asg_events(i).datetracked_event );
2704: hr_utility_trace('Change_mode : '||
2697: dated_tbls(p_asg_events(i).dated_table_id).table_name;
2698:
2699: hr_utility_trace('-----------------------------------------------');
2700: hr_utility_trace('Result row :' ||to_char(i));
2701: hr_utility_trace('lv_table_name :' ||lv_table_name );
2702: hr_utility_trace('Datetracked_event: '||
2703: p_asg_events(i).datetracked_event );
2704: hr_utility_trace('Change_mode : '||
2705: p_asg_events(i).change_mode );
2698:
2699: hr_utility_trace('-----------------------------------------------');
2700: hr_utility_trace('Result row :' ||to_char(i));
2701: hr_utility_trace('lv_table_name :' ||lv_table_name );
2702: hr_utility_trace('Datetracked_event: '||
2703: p_asg_events(i).datetracked_event );
2704: hr_utility_trace('Change_mode : '||
2705: p_asg_events(i).change_mode );
2706: hr_utility_trace('Effective_date : '||
2700: hr_utility_trace('Result row :' ||to_char(i));
2701: hr_utility_trace('lv_table_name :' ||lv_table_name );
2702: hr_utility_trace('Datetracked_event: '||
2703: p_asg_events(i).datetracked_event );
2704: hr_utility_trace('Change_mode : '||
2705: p_asg_events(i).change_mode );
2706: hr_utility_trace('Effective_date : '||
2707: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2708: hr_utility_trace('dated_table_id : '||
2702: hr_utility_trace('Datetracked_event: '||
2703: p_asg_events(i).datetracked_event );
2704: hr_utility_trace('Change_mode : '||
2705: p_asg_events(i).change_mode );
2706: hr_utility_trace('Effective_date : '||
2707: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2708: hr_utility_trace('dated_table_id : '||
2709: TO_CHAR(p_asg_events(i).dated_table_id));
2710: hr_utility_trace('column_name : '||
2704: hr_utility_trace('Change_mode : '||
2705: p_asg_events(i).change_mode );
2706: hr_utility_trace('Effective_date : '||
2707: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2708: hr_utility_trace('dated_table_id : '||
2709: TO_CHAR(p_asg_events(i).dated_table_id));
2710: hr_utility_trace('column_name : '||
2711: p_asg_events(i).column_name );
2712: hr_utility_trace('Update_type : '||
2706: hr_utility_trace('Effective_date : '||
2707: to_char(p_asg_events(i).effective_date,'DD-MON-YYYY'));
2708: hr_utility_trace('dated_table_id : '||
2709: TO_CHAR(p_asg_events(i).dated_table_id));
2710: hr_utility_trace('column_name : '||
2711: p_asg_events(i).column_name );
2712: hr_utility_trace('Update_type : '||
2713: p_asg_events(i).update_type );
2714: hr_utility_trace('old_value : '||
2708: hr_utility_trace('dated_table_id : '||
2709: TO_CHAR(p_asg_events(i).dated_table_id));
2710: hr_utility_trace('column_name : '||
2711: p_asg_events(i).column_name );
2712: hr_utility_trace('Update_type : '||
2713: p_asg_events(i).update_type );
2714: hr_utility_trace('old_value : '||
2715: p_asg_events(i).old_value );
2716: hr_utility_trace('new_value : '||
2710: hr_utility_trace('column_name : '||
2711: p_asg_events(i).column_name );
2712: hr_utility_trace('Update_type : '||
2713: p_asg_events(i).update_type );
2714: hr_utility_trace('old_value : '||
2715: p_asg_events(i).old_value );
2716: hr_utility_trace('new_value : '||
2717: p_asg_events(i).new_value );
2718: hr_utility_trace('change_values : '||
2712: hr_utility_trace('Update_type : '||
2713: p_asg_events(i).update_type );
2714: hr_utility_trace('old_value : '||
2715: p_asg_events(i).old_value );
2716: hr_utility_trace('new_value : '||
2717: p_asg_events(i).new_value );
2718: hr_utility_trace('change_values : '||
2719: p_asg_events(i).change_values );
2720: hr_utility_trace('-----------------------------------------------');
2714: hr_utility_trace('old_value : '||
2715: p_asg_events(i).old_value );
2716: hr_utility_trace('new_value : '||
2717: p_asg_events(i).new_value );
2718: hr_utility_trace('change_values : '||
2719: p_asg_events(i).change_values );
2720: hr_utility_trace('-----------------------------------------------');
2721:
2722: ln_old_gre_id := -9;
2716: hr_utility_trace('new_value : '||
2717: p_asg_events(i).new_value );
2718: hr_utility_trace('change_values : '||
2719: p_asg_events(i).change_values );
2720: hr_utility_trace('-----------------------------------------------');
2721:
2722: ln_old_gre_id := -9;
2723: ln_new_gre_id := -9;
2724:
2739: END IF;
2740:
2741: IF p_asg_events(i).update_type = 'I' THEN
2742:
2743: hr_utility.set_location(gv_package || lv_procedure_name, 30);
2744: ln_step := 3;
2745:
2746: lv_hire := 'Y';
2747: ln_count := hire_sep.COUNT;
2749: hire_sep(ln_count).trn_date := p_asg_events(i).effective_date;
2750:
2751: ELSE
2752:
2753: hr_utility.set_location(gv_package || lv_procedure_name, 40);
2754: ln_step := 4;
2755: hr_utility_trace ('column_name = PER_ALL_ASSIGNMENTS_F.'||
2756: p_asg_events(i).column_name);
2757: IF p_asg_events(i).column_name = 'LOCATION_ID' THEN
2751: ELSE
2752:
2753: hr_utility.set_location(gv_package || lv_procedure_name, 40);
2754: ln_step := 4;
2755: hr_utility_trace ('column_name = PER_ALL_ASSIGNMENTS_F.'||
2756: p_asg_events(i).column_name);
2757: IF p_asg_events(i).column_name = 'LOCATION_ID' THEN
2758:
2759: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2755: hr_utility_trace ('column_name = PER_ALL_ASSIGNMENTS_F.'||
2756: p_asg_events(i).column_name);
2757: IF p_asg_events(i).column_name = 'LOCATION_ID' THEN
2758:
2759: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2760:
2761: /*
2762: * Retrieve the gre off the soft coding keyflex (scl). If a GRE has
2763: * been specified at the scl, location changes are moot.
2779: ,lv_asg_number;
2780:
2781: CLOSE c_get_asg_scl;
2782:
2783: hr_utility.set_location(gv_package || lv_procedure_name, 60);
2784: ln_step := 6;
2785:
2786: ln_old_gre_id :=
2787: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2789: ,p_business_group_id
2790: ,ln_asg_scl_old
2791: ,p_asg_events(i).effective_date - 1);
2792:
2793: hr_utility.set_location(gv_package || lv_procedure_name, 70);
2794:
2795: IF ln_old_gre_id < 0 THEN
2796: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2797: lv_asg_number ||'], Effective Date ['||
2802: ,p_msg_txt => lv_msg_txt
2803: ,p_level => 'I');
2804: END IF;
2805:
2806: hr_utility_trace('Checking wherther the person is applicant');
2807: OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2808: FETCH c_check_per_status INTO lv_check_applicant;
2809:
2810: CLOSE c_check_per_status;
2809:
2810: CLOSE c_check_per_status;
2811:
2812: IF lv_check_applicant = 'Y' THEN
2813: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2814: hr_utility_trace('making -9 for the gre that got by location');
2815: ln_old_gre_id := -9;
2816: END IF;
2817:
2810: CLOSE c_check_per_status;
2811:
2812: IF lv_check_applicant = 'Y' THEN
2813: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2814: hr_utility_trace('making -9 for the gre that got by location');
2815: ln_old_gre_id := -9;
2816: END IF;
2817:
2818: ln_step := 7;
2840:
2841: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2842: THEN
2843:
2844: hr_utility.set_location(gv_package||lv_procedure_name,100);
2845: ln_step := 10;
2846:
2847: ln_count := hire_sep.COUNT;
2848: hire_sep(ln_count).trn_type := 'SEPARATION';
2853:
2854: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2855: THEN
2856:
2857: hr_utility.set_location(gv_package||lv_procedure_name,110);
2858: ln_step := 11;
2859:
2860: ln_count := hire_sep.COUNT;
2861: hire_sep(ln_count).trn_type := 'HIRE';
2868:
2869:
2870: ELSIF p_asg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' THEN
2871:
2872: hr_utility.set_location(gv_package||lv_procedure_name,120);
2873: ln_step := 12;
2874: hr_utility_trace('Inside get_transaction and sof_coding :');
2875: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2876: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2870: ELSIF p_asg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' THEN
2871:
2872: hr_utility.set_location(gv_package||lv_procedure_name,120);
2873: ln_step := 12;
2874: hr_utility_trace('Inside get_transaction and sof_coding :');
2875: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2876: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2877: OPEN c_get_asg_loc(p_assignment_id
2878: ,p_asg_events(i).effective_date - 1);
2871:
2872: hr_utility.set_location(gv_package||lv_procedure_name,120);
2873: ln_step := 12;
2874: hr_utility_trace('Inside get_transaction and sof_coding :');
2875: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2876: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2877: OPEN c_get_asg_loc(p_assignment_id
2878: ,p_asg_events(i).effective_date - 1);
2879:
2872: hr_utility.set_location(gv_package||lv_procedure_name,120);
2873: ln_step := 12;
2874: hr_utility_trace('Inside get_transaction and sof_coding :');
2875: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2876: hr_utility_trace('p_asg_events(i).effective_date - 1 :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2877: OPEN c_get_asg_loc(p_assignment_id
2878: ,p_asg_events(i).effective_date - 1);
2879:
2880: FETCH c_get_asg_loc
2881: INTO ln_asg_loc_old
2882: ,lv_asg_number;
2883:
2884: CLOSE c_get_asg_loc;
2885: hr_utility_trace('Value from the cursor c_get_asg_loc');
2886: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2887: hr_utility_trace('lv_asg_number '||lv_asg_number);
2888: hr_utility.set_location(gv_package||lv_procedure_name,130);
2889: ln_step := 13;
2882: ,lv_asg_number;
2883:
2884: CLOSE c_get_asg_loc;
2885: hr_utility_trace('Value from the cursor c_get_asg_loc');
2886: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2887: hr_utility_trace('lv_asg_number '||lv_asg_number);
2888: hr_utility.set_location(gv_package||lv_procedure_name,130);
2889: ln_step := 13;
2890:
2883:
2884: CLOSE c_get_asg_loc;
2885: hr_utility_trace('Value from the cursor c_get_asg_loc');
2886: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2887: hr_utility_trace('lv_asg_number '||lv_asg_number);
2888: hr_utility.set_location(gv_package||lv_procedure_name,130);
2889: ln_step := 13;
2890:
2891: ln_old_gre_id := NVL(
2884: CLOSE c_get_asg_loc;
2885: hr_utility_trace('Value from the cursor c_get_asg_loc');
2886: hr_utility_trace('ln_asg_loc_old '||ln_asg_loc_old);
2887: hr_utility_trace('lv_asg_number '||lv_asg_number);
2888: hr_utility.set_location(gv_package||lv_procedure_name,130);
2889: ln_step := 13;
2890:
2891: ln_old_gre_id := NVL(
2892: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2893: ln_asg_loc_old
2894: ,p_business_group_id
2895: ,lv_old_value
2896: ,p_asg_events(i).effective_date - 1), -9);
2897: hr_utility_trace('Checking wherther the person is applicant');
2898: OPEN c_check_per_status(p_person_id, fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2899: FETCH c_check_per_status INTO lv_check_applicant;
2900:
2901: CLOSE c_check_per_status;
2900:
2901: CLOSE c_check_per_status;
2902:
2903: IF lv_check_applicant = 'Y' THEN
2904: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2905: hr_utility_trace('making -9 for the gre that got by location');
2906: ln_old_gre_id := -9;
2907: END IF;
2908:
2901: CLOSE c_check_per_status;
2902:
2903: IF lv_check_applicant = 'Y' THEN
2904: hr_utility_trace('The person is applicant on effective_date ' || fnd_date.date_to_canonical(p_asg_events(i).effective_date - 1));
2905: hr_utility_trace('making -9 for the gre that got by location');
2906: ln_old_gre_id := -9;
2907: END IF;
2908:
2909: hr_utility.set_location(gv_package||lv_procedure_name,135);
2905: hr_utility_trace('making -9 for the gre that got by location');
2906: ln_old_gre_id := -9;
2907: END IF;
2908:
2909: hr_utility.set_location(gv_package||lv_procedure_name,135);
2910: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2911:
2912: IF ln_old_gre_id < 0 THEN
2913: hr_utility_trace('ln_old_gre_id <0');
2906: ln_old_gre_id := -9;
2907: END IF;
2908:
2909: hr_utility.set_location(gv_package||lv_procedure_name,135);
2910: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2911:
2912: IF ln_old_gre_id < 0 THEN
2913: hr_utility_trace('ln_old_gre_id <0');
2914: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2909: hr_utility.set_location(gv_package||lv_procedure_name,135);
2910: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2911:
2912: IF ln_old_gre_id < 0 THEN
2913: hr_utility_trace('ln_old_gre_id <0');
2914: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2915: lv_asg_number ||'], Effective Date ['||
2916: TO_CHAR(p_asg_events(i).effective_date - 1)||']';
2917:
2920: ,p_msg_txt => lv_msg_txt
2921: ,p_level => 'I');
2922: END IF;
2923:
2924: hr_utility.set_location(gv_package||lv_procedure_name,140);
2925: ln_step := 14;
2926: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2927: hr_utility_trace('p_asg_events(i).effective_date :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date ));
2928: OPEN c_get_asg_loc(p_assignment_id
2922: END IF;
2923:
2924: hr_utility.set_location(gv_package||lv_procedure_name,140);
2925: ln_step := 14;
2926: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2927: hr_utility_trace('p_asg_events(i).effective_date :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date ));
2928: OPEN c_get_asg_loc(p_assignment_id
2929: ,p_asg_events(i).effective_date);
2930:
2923:
2924: hr_utility.set_location(gv_package||lv_procedure_name,140);
2925: ln_step := 14;
2926: hr_utility_trace('p_assignment_id :' || p_assignment_id);
2927: hr_utility_trace('p_asg_events(i).effective_date :' || fnd_date.date_to_canonical(p_asg_events(i).effective_date ));
2928: OPEN c_get_asg_loc(p_assignment_id
2929: ,p_asg_events(i).effective_date);
2930:
2931: FETCH c_get_asg_loc
2932: INTO ln_asg_loc_new
2933: ,lv_asg_number;
2934:
2935: CLOSE c_get_asg_loc;
2936: hr_utility_trace('Value from the cursor c_get_asg_loc for new loc');
2937: hr_utility_trace('ln_asg_loc_new '||ln_asg_loc_new);
2938: hr_utility_trace('lv_asg_number '||lv_asg_number);
2939: ln_new_gre_id := NVL(
2940: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2933: ,lv_asg_number;
2934:
2935: CLOSE c_get_asg_loc;
2936: hr_utility_trace('Value from the cursor c_get_asg_loc for new loc');
2937: hr_utility_trace('ln_asg_loc_new '||ln_asg_loc_new);
2938: hr_utility_trace('lv_asg_number '||lv_asg_number);
2939: ln_new_gre_id := NVL(
2940: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2941: ln_asg_loc_new
2934:
2935: CLOSE c_get_asg_loc;
2936: hr_utility_trace('Value from the cursor c_get_asg_loc for new loc');
2937: hr_utility_trace('ln_asg_loc_new '||ln_asg_loc_new);
2938: hr_utility_trace('lv_asg_number '||lv_asg_number);
2939: ln_new_gre_id := NVL(
2940: per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2941: ln_asg_loc_new
2942: ,p_business_group_id
2943: ,lv_new_value
2944: ,p_asg_events(i).effective_date), -9);
2945:
2946: IF ln_new_gre_id < 0 THEN
2947: hr_utility_trace('ln_old_gre_id <0');
2948: lv_msg_txt := 'Unable to determine GRE: Assignment Number ['||
2949: lv_asg_number ||'], Effective Date ['||
2950: TO_CHAR(p_asg_events(i).effective_date - 1)||']';
2951:
2953: ,p_msg_name => NULL
2954: ,p_msg_txt => lv_msg_txt
2955: ,p_level => 'I');
2956: END IF;
2957: hr_utility_trace('p_gre_id '||p_gre_id);
2958: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2959: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2960: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2961: THEN
2954: ,p_msg_txt => lv_msg_txt
2955: ,p_level => 'I');
2956: END IF;
2957: hr_utility_trace('p_gre_id '||p_gre_id);
2958: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2959: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2960: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2961: THEN
2962: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2955: ,p_level => 'I');
2956: END IF;
2957: hr_utility_trace('p_gre_id '||p_gre_id);
2958: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2959: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2960: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2961: THEN
2962: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2963: hr_utility.set_location(gv_package||lv_procedure_name,100);
2958: hr_utility_trace('ln_old_gre_id '||ln_old_gre_id);
2959: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2960: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2961: THEN
2962: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2963: hr_utility.set_location(gv_package||lv_procedure_name,100);
2964: ln_step := 10;
2965:
2966: ln_count := hire_sep.COUNT;
2959: hr_utility_trace('ln_new_gre_id '||ln_new_gre_id);
2960: IF ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id
2961: THEN
2962: hr_utility_trace('ln_old_gre_id = p_gre_id AND ln_new_gre_id <> p_gre_id');
2963: hr_utility.set_location(gv_package||lv_procedure_name,100);
2964: ln_step := 10;
2965:
2966: ln_count := hire_sep.COUNT;
2967: hire_sep(ln_count).trn_type := 'SEPARATION';
2966: ln_count := hire_sep.COUNT;
2967: hire_sep(ln_count).trn_type := 'SEPARATION';
2968: hire_sep(ln_count).trn_date :=
2969: p_asg_events(i).effective_date - 1;
2970: hr_utility_trace('Separation');
2971: END IF;
2972:
2973: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2974: THEN
2971: END IF;
2972:
2973: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2974: THEN
2975: hr_utility_trace('ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id');
2976: hr_utility.set_location(gv_package||lv_procedure_name,110);
2977: ln_step := 11;
2978:
2979: ln_count := hire_sep.COUNT;
2972:
2973: IF ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id
2974: THEN
2975: hr_utility_trace('ln_old_gre_id <> p_gre_id AND ln_new_gre_id = p_gre_id');
2976: hr_utility.set_location(gv_package||lv_procedure_name,110);
2977: ln_step := 11;
2978:
2979: ln_count := hire_sep.COUNT;
2980: hire_sep(ln_count).trn_type := 'HIRE';
2979: ln_count := hire_sep.COUNT;
2980: hire_sep(ln_count).trn_type := 'HIRE';
2981: hire_sep(ln_count).trn_date :=
2982: p_asg_events(i).effective_date;
2983: hr_utility_trace('Hire');
2984: END IF;
2985:
2986: ELSIF p_asg_events(i).column_name = 'ASSIGNMENT_STATUS_TYPE_ID' THEN
2987:
3002: FETCH c_asg_status_type INTO lv_new_asg_status ;
3003: CLOSE c_asg_status_type ;
3004: END IF;
3005:
3006: hr_utility_trace( 'old assignment status :'||lv_old_asg_status );
3007: hr_utility_trace( 'new assignment status :'||lv_new_asg_status );
3008:
3009: IF lv_old_asg_status = 'ACTIVE_ASSIGN' AND
3010: lv_new_asg_status = 'TERM_ASSIGN' THEN
3003: CLOSE c_asg_status_type ;
3004: END IF;
3005:
3006: hr_utility_trace( 'old assignment status :'||lv_old_asg_status );
3007: hr_utility_trace( 'new assignment status :'||lv_new_asg_status );
3008:
3009: IF lv_old_asg_status = 'ACTIVE_ASSIGN' AND
3010: lv_new_asg_status = 'TERM_ASSIGN' THEN
3011:
3074: END IF;
3075:
3076: END IF;
3077:
3078: hr_utility_trace('---------------------------------------');
3079: hr_utility_trace('Row in Location :' ||i);
3080: hr_utility_trace('lv_change_values :' ||lv_change_values);
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3075:
3076: END IF;
3077:
3078: hr_utility_trace('---------------------------------------');
3079: hr_utility_trace('Row in Location :' ||i);
3080: hr_utility_trace('lv_change_values :' ||lv_change_values);
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3083: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3076: END IF;
3077:
3078: hr_utility_trace('---------------------------------------');
3079: hr_utility_trace('Row in Location :' ||i);
3080: hr_utility_trace('lv_change_values :' ||lv_change_values);
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3083: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3084: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
3077:
3078: hr_utility_trace('---------------------------------------');
3079: hr_utility_trace('Row in Location :' ||i);
3080: hr_utility_trace('lv_change_values :' ||lv_change_values);
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3083: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3084: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
3085: hr_utility_trace('p_gre_id :' ||p_gre_id );
3078: hr_utility_trace('---------------------------------------');
3079: hr_utility_trace('Row in Location :' ||i);
3080: hr_utility_trace('lv_change_values :' ||lv_change_values);
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3083: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3084: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
3085: hr_utility_trace('p_gre_id :' ||p_gre_id );
3086:
3079: hr_utility_trace('Row in Location :' ||i);
3080: hr_utility_trace('lv_change_values :' ||lv_change_values);
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3083: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3084: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
3085: hr_utility_trace('p_gre_id :' ||p_gre_id );
3086:
3087: IF hire_sep.COUNT > 0 THEN
3080: hr_utility_trace('lv_change_values :' ||lv_change_values);
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3083: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3084: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
3085: hr_utility_trace('p_gre_id :' ||p_gre_id );
3086:
3087: IF hire_sep.COUNT > 0 THEN
3088:
3081: hr_utility_trace('lv_old_value :' ||lv_old_value );
3082: hr_utility_trace('lv_new_value :' ||lv_new_value );
3083: hr_utility_trace('ln_old_gre_id :' ||ln_old_gre_id );
3084: hr_utility_trace('ln_new_gre_id :' ||ln_new_gre_id );
3085: hr_utility_trace('p_gre_id :' ||p_gre_id );
3086:
3087: IF hire_sep.COUNT > 0 THEN
3088:
3089: hr_utility_trace('ln_count :' ||ln_count);
3085: hr_utility_trace('p_gre_id :' ||p_gre_id );
3086:
3087: IF hire_sep.COUNT > 0 THEN
3088:
3089: hr_utility_trace('ln_count :' ||ln_count);
3090: hr_utility_trace('TRN_TYPE :' ||hire_sep(ln_count).trn_type);
3091: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
3092:
3093: ELSE
3086:
3087: IF hire_sep.COUNT > 0 THEN
3088:
3089: hr_utility_trace('ln_count :' ||ln_count);
3090: hr_utility_trace('TRN_TYPE :' ||hire_sep(ln_count).trn_type);
3091: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
3092:
3093: ELSE
3094:
3087: IF hire_sep.COUNT > 0 THEN
3088:
3089: hr_utility_trace('ln_count :' ||ln_count);
3090: hr_utility_trace('TRN_TYPE :' ||hire_sep(ln_count).trn_type);
3091: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
3092:
3093: ELSE
3094:
3095: hr_utility_trace('NO RECORD FOUND in hire_sep table');
3091: hr_utility_trace('TRN_DATE :' ||hire_sep(ln_count).trn_date);
3092:
3093: ELSE
3094:
3095: hr_utility_trace('NO RECORD FOUND in hire_sep table');
3096: END IF;
3097:
3098: hr_utility_trace('---------------------------------------');
3099:
3094:
3095: hr_utility_trace('NO RECORD FOUND in hire_sep table');
3096: END IF;
3097:
3098: hr_utility_trace('---------------------------------------');
3099:
3100: END LOOP;
3101:
3102: IF hire_sep.COUNT > 0 THEN
3110: LOOP
3111:
3112: lv_hire_sep_found := 'N';
3113:
3114: hr_utility_trace('hire_sep(i).trn_type '||i||': '||
3115: hire_sep(i).trn_type );
3116: hr_utility_trace('hire_sep(i).trn_date '||i||': '||
3117: hire_sep(i).trn_date );
3118: hr_utility_trace('---------------------------------------');
3112: lv_hire_sep_found := 'N';
3113:
3114: hr_utility_trace('hire_sep(i).trn_type '||i||': '||
3115: hire_sep(i).trn_type );
3116: hr_utility_trace('hire_sep(i).trn_date '||i||': '||
3117: hire_sep(i).trn_date );
3118: hr_utility_trace('---------------------------------------');
3119:
3120: IF hire_sep_uniq.COUNT > 0 THEN
3114: hr_utility_trace('hire_sep(i).trn_type '||i||': '||
3115: hire_sep(i).trn_type );
3116: hr_utility_trace('hire_sep(i).trn_date '||i||': '||
3117: hire_sep(i).trn_date );
3118: hr_utility_trace('---------------------------------------');
3119:
3120: IF hire_sep_uniq.COUNT > 0 THEN
3121:
3122: FOR j in hire_sep_uniq.FIRST..hire_sep_uniq.LAST
3120: IF hire_sep_uniq.COUNT > 0 THEN
3121:
3122: FOR j in hire_sep_uniq.FIRST..hire_sep_uniq.LAST
3123: LOOP
3124: hr_utility_trace('hire_sep_uniq(j).trn_type '||j||': '||
3125: hire_sep_uniq(j).trn_type );
3126: hr_utility_trace('hire_sep_uniq(j).trn_date '||j||': '||
3127: hire_sep_uniq(j).trn_date );
3128:
3122: FOR j in hire_sep_uniq.FIRST..hire_sep_uniq.LAST
3123: LOOP
3124: hr_utility_trace('hire_sep_uniq(j).trn_type '||j||': '||
3125: hire_sep_uniq(j).trn_type );
3126: hr_utility_trace('hire_sep_uniq(j).trn_date '||j||': '||
3127: hire_sep_uniq(j).trn_date );
3128:
3129: IF hire_sep(i).trn_type = hire_sep_uniq(j).trn_type AND
3130: hire_sep(i).trn_date = hire_sep_uniq(j).trn_date THEN
3129: IF hire_sep(i).trn_type = hire_sep_uniq(j).trn_type AND
3130: hire_sep(i).trn_date = hire_sep_uniq(j).trn_date THEN
3131:
3132: lv_hire_sep_found := 'Y';
3133: hr_utility_trace('FOUND');
3134:
3135: END IF;
3136:
3137: END LOOP;
3137: END LOOP;
3138:
3139: END IF;
3140:
3141: hr_utility_trace('---------------------------------------');
3142:
3143: /**************************************************************
3144: ** There should not be a hire/separation record followed by
3145: ** another hire/separation record.
3157:
3158: lv_hire_already_in := 'Y';
3159: lv_sep_already_in := 'N';
3160:
3161: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3162: hr_utility_trace(' ');
3163: ln_count := hire_sep_uniq.COUNT;
3164: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3165: hire_sep_uniq(ln_count).trn_date := hire_sep(i).trn_date;
3158: lv_hire_already_in := 'Y';
3159: lv_sep_already_in := 'N';
3160:
3161: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3162: hr_utility_trace(' ');
3163: ln_count := hire_sep_uniq.COUNT;
3164: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3165: hire_sep_uniq(ln_count).trn_date := hire_sep(i).trn_date;
3166:
3172:
3173: lv_hire_already_in := 'N';
3174: lv_sep_already_in := 'Y';
3175:
3176: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3177: hr_utility_trace(' ');
3178:
3179: ln_count := hire_sep_uniq.COUNT;
3180: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3173: lv_hire_already_in := 'N';
3174: lv_sep_already_in := 'Y';
3175:
3176: hr_utility_trace('NOT FOUND'||hire_sep(i).trn_type);
3177: hr_utility_trace(' ');
3178:
3179: ln_count := hire_sep_uniq.COUNT;
3180: hire_sep_uniq(ln_count).trn_type := hire_sep(i).trn_type;
3181: -- Bug 5005254
3192: hire_sep.DELETE;
3193: hire_sep := hire_sep_uniq;
3194: hire_sep_uniq.DELETE;
3195:
3196: hr_utility.set_location(gv_package || lv_procedure_name, 210);
3197: ln_step := 21;
3198:
3199: OPEN c_get_org_information(p_gre_id);
3200: FETCH c_get_org_information INTO lv_employer_ss_id;
3199: OPEN c_get_org_information(p_gre_id);
3200: FETCH c_get_org_information INTO lv_employer_ss_id;
3201: CLOSE c_get_org_information;
3202:
3203: hr_utility.set_location(gv_package || lv_procedure_name, 220);
3204: ln_step := 22;
3205:
3206: /*
3207: lv_exclude_start_date := NULL;
3225: lv_exclude_start_date := p_exc_start_date;
3226: lv_exclude_end_date := p_exc_end_date;
3227: /*8438074 End*/
3228:
3229: hr_utility.trace('Count hire_sep array :'|| i);
3230: lv_relation_exists := 'N';
3231: IF hire_sep(i).trn_type = 'HIRE' THEN
3232:
3233: hr_utility.set_location(gv_package || lv_procedure_name, 240);
3229: hr_utility.trace('Count hire_sep array :'|| i);
3230: lv_relation_exists := 'N';
3231: IF hire_sep(i).trn_type = 'HIRE' THEN
3232:
3233: hr_utility.set_location(gv_package || lv_procedure_name, 240);
3234: ln_step := 24;
3235:
3236: lv_transaction_type := '08';
3237: lv_transaction_date :=
3236: lv_transaction_type := '08';
3237: lv_transaction_date :=
3238: fnd_date.date_to_canonical(hire_sep(i).trn_date);
3239: lv_leaving_reason := NULL;
3240: hr_utility.trace('lv_transaction_date is: '||lv_transaction_date);
3241: /* Do not archive this transaction if person-GRE relation
3242: already exists prior to transaction date. */
3243: OPEN csr_per_gre(hire_sep(i).trn_date,
3244: lv_transaction_type);
3243: OPEN csr_per_gre(hire_sep(i).trn_date,
3244: lv_transaction_type);
3245: FETCH csr_per_gre INTO lv_relation_exists;
3246: CLOSE csr_per_gre;
3247: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3248: hr_utility.set_location(gv_package || lv_procedure_name, 230);
3249: ln_step := 23;
3250:
3251: ln_idw := get_idw( p_assignment_id => p_assignment_id
3244: lv_transaction_type);
3245: FETCH csr_per_gre INTO lv_relation_exists;
3246: CLOSE csr_per_gre;
3247: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3248: hr_utility.set_location(gv_package || lv_procedure_name, 230);
3249: ln_step := 23;
3250:
3251: ln_idw := get_idw( p_assignment_id => p_assignment_id
3252: ,p_tax_unit_id => p_gre_id
3253: ,p_effective_date => hire_sep(i).trn_date
3254: ,p_fixed_idw => ln_fixed_idw
3255: ,p_variable_idw => ln_variable_idw );
3256:
3257: hr_utility.trace('SS_ARCH hire_sep ln_idw: '||ln_idw);
3258: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3259: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3260: ln_variable_idw);
3261:
3254: ,p_fixed_idw => ln_fixed_idw
3255: ,p_variable_idw => ln_variable_idw );
3256:
3257: hr_utility.trace('SS_ARCH hire_sep ln_idw: '||ln_idw);
3258: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3259: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3260: ln_variable_idw);
3261:
3262: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
3255: ,p_variable_idw => ln_variable_idw );
3256:
3257: hr_utility.trace('SS_ARCH hire_sep ln_idw: '||ln_idw);
3258: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3259: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3260: ln_variable_idw);
3261:
3262: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
3263: ln_step := 203;
3258: hr_utility.trace('SS_ARCH hire_sep ln_fixed_idw: '||ln_fixed_idw);
3259: hr_utility.trace('SS_ARCH hire_sep ln_variable_idw: '||
3260: ln_variable_idw);
3261:
3262: hr_utility.set_location(gv_package || lv_procedure_name, 2030);
3263: ln_step := 203;
3264:
3265: lv_idw := to_char(ln_idw, '99999.99'); --Bug 8988585
3266:
3263: ln_step := 203;
3264:
3265: lv_idw := to_char(ln_idw, '99999.99'); --Bug 8988585
3266:
3267: hr_utility.trace('SS_ARCH hire_sep lv_idw: '||lv_idw);
3268:
3269: IF gn_person_rec_chng = 0 THEN
3270: gn_person_rec_chng := 1;
3271: END IF;
3271: END IF;
3272:
3273: ELSIF hire_sep(i).trn_type = 'SEPARATION' THEN
3274:
3275: hr_utility.set_location(gv_package || lv_procedure_name, 250);
3276: ln_step := 25;
3277:
3278: lv_transaction_type := '02';
3279: lv_transaction_date :=
3287: OPEN csr_per_gre(hire_sep(i).trn_date,
3288: lv_transaction_type);
3289: FETCH csr_per_gre INTO lv_relation_exists;
3290: CLOSE csr_per_gre;
3291: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3292:
3293: hr_utility.set_location(gv_package || lv_procedure_name, 260);
3294: ln_step := 26;
3295:
3289: FETCH csr_per_gre INTO lv_relation_exists;
3290: CLOSE csr_per_gre;
3291: hr_utility.trace('lv_relation_exists is: '||lv_relation_exists);
3292:
3293: hr_utility.set_location(gv_package || lv_procedure_name, 260);
3294: ln_step := 26;
3295:
3296: OPEN c_get_leaving_reason( p_assignment_id
3297: ,ld_eff_date
3301: CLOSE c_get_leaving_reason;
3302:
3303: IF lv_leaving_reason IS NULL THEN
3304:
3305: hr_utility.set_location(gv_package || lv_procedure_name, 270);
3306: ln_step := 27;
3307:
3308: -- get it from periods of service
3309: -- also the effective date passed is not correct
3319:
3320: END IF;
3321:
3322: IF lv_relation_exists = 'N' THEN
3323: hr_utility.set_location(gv_package || lv_procedure_name, 280);
3324: ln_step := 28;
3325: hr_utility_trace('lv_transaction_date :'|| lv_transaction_date);
3326: OPEN c_person_detail (p_person_id
3327: ,fnd_date.canonical_to_date
3321:
3322: IF lv_relation_exists = 'N' THEN
3323: hr_utility.set_location(gv_package || lv_procedure_name, 280);
3324: ln_step := 28;
3325: hr_utility_trace('lv_transaction_date :'|| lv_transaction_date);
3326: OPEN c_person_detail (p_person_id
3327: ,fnd_date.canonical_to_date
3328: (lv_transaction_date));
3329: FETCH c_person_detail INTO lv_employee_ssn;
3342: OPEN c_salary_type( p_assignment_id
3343: ,hire_sep(i).trn_date);
3344: FETCH c_salary_type INTO ln_salary_type;
3345: CLOSE c_salary_type;
3346: hr_utility.set_location(gv_package || lv_procedure_name, 300);
3347: ln_step := 28;
3348: IF ln_salary_type ='1' THEN
3349: ln_salary_type :='VARIABLE';
3350: ELSIF ln_salary_type ='2' THEN
3401: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info25
3402: := lv_exclude_start_date; /*8438074*/
3403: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info26
3404: := lv_exclude_end_date; /*8438074*/
3405: hr_utility_trace('Archived MX SS Transaction for hire');
3406: hr_utility_trace('-----------------------------------');
3407: hr_utility_trace('index ' || ln_index);
3408: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3409: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3402: := lv_exclude_start_date; /*8438074*/
3403: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info26
3404: := lv_exclude_end_date; /*8438074*/
3405: hr_utility_trace('Archived MX SS Transaction for hire');
3406: hr_utility_trace('-----------------------------------');
3407: hr_utility_trace('index ' || ln_index);
3408: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3409: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3410: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3403: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info26
3404: := lv_exclude_end_date; /*8438074*/
3405: hr_utility_trace('Archived MX SS Transaction for hire');
3406: hr_utility_trace('-----------------------------------');
3407: hr_utility_trace('index ' || ln_index);
3408: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3409: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3410: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3411: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3404: := lv_exclude_end_date; /*8438074*/
3405: hr_utility_trace('Archived MX SS Transaction for hire');
3406: hr_utility_trace('-----------------------------------');
3407: hr_utility_trace('index ' || ln_index);
3408: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3409: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3410: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3411: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3412: hr_utility_trace('Person ID ' ||
3406: hr_utility_trace('-----------------------------------');
3407: hr_utility_trace('index ' || ln_index);
3408: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3409: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3410: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3411: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3412: hr_utility_trace('Person ID ' ||
3413: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3414: hr_utility_trace('Transaction date ' ||
3408: hr_utility_trace('pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category ' ||
3409: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).action_info_category);
3410: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3411: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3412: hr_utility_trace('Person ID ' ||
3413: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3414: hr_utility_trace('Transaction date ' ||
3415: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3416: hr_utility_trace('transaction type ' ||
3410: hr_utility_trace(' pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code ' ||
3411: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).jurisdiction_code);
3412: hr_utility_trace('Person ID ' ||
3413: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3414: hr_utility_trace('Transaction date ' ||
3415: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3416: hr_utility_trace('transaction type ' ||
3417: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4);
3418: hr_utility_trace('-----------------------------------');
3412: hr_utility_trace('Person ID ' ||
3413: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info1);
3414: hr_utility_trace('Transaction date ' ||
3415: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3416: hr_utility_trace('transaction type ' ||
3417: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4);
3418: hr_utility_trace('-----------------------------------');
3419:
3420:
3414: hr_utility_trace('Transaction date ' ||
3415: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info2);
3416: hr_utility_trace('transaction type ' ||
3417: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info4);
3418: hr_utility_trace('-----------------------------------');
3419:
3420:
3421: ELSE
3422: hr_utility_trace('Person-GRE association exits. Transaction '||
3418: hr_utility_trace('-----------------------------------');
3419:
3420:
3421: ELSE
3422: hr_utility_trace('Person-GRE association exits. Transaction '||
3423: lv_transaction_type||' ('||lv_transaction_date||
3424: ') will not be archived.');
3425: END IF;
3426: END LOOP;
3432: WHEN others THEN
3433: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3434: gv_package || lv_procedure_name;
3435:
3436: hr_utility_trace(lv_error_message || '-' || sqlerrm);
3437:
3438: lv_error_message :=
3439: pay_emp_action_arch.set_error_message(lv_error_message);
3440:
3437:
3438: lv_error_message :=
3439: pay_emp_action_arch.set_error_message(lv_error_message);
3440:
3441: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3442: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3443: hr_utility.raise_error;
3444:
3445: END arch_hire_separation;
3438: lv_error_message :=
3439: pay_emp_action_arch.set_error_message(lv_error_message);
3440:
3441: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3442: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3443: hr_utility.raise_error;
3444:
3445: END arch_hire_separation;
3446:
3439: pay_emp_action_arch.set_error_message(lv_error_message);
3440:
3441: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3442: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3443: hr_utility.raise_error;
3444:
3445: END arch_hire_separation;
3446:
3447: PROCEDURE get_transactions( p_payroll_action_id IN NUMBER
3538: BEGIN
3539:
3540: lv_procedure_name := 'get_transactions';
3541:
3542: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3543: ln_step := 1;
3544:
3545: ld_eff_start_date := p_start_date;
3546: ld_eff_end_date := p_end_date;
3553: per_events.DELETE;
3554: ele_events.DELETE;
3555: eev_events.DELETE;
3556:
3557: hr_utility.set_location(gv_package || lv_procedure_name, 20);
3558: ln_step := 2;
3559:
3560: OPEN c_get_event_group ('Mexico Social Security Reports');
3561: FETCH c_get_event_group INTO ln_event_group_id;
3560: OPEN c_get_event_group ('Mexico Social Security Reports');
3561: FETCH c_get_event_group INTO ln_event_group_id;
3562: CLOSE c_get_event_group;
3563:
3564: hr_utility_trace('p_person_id : ' || p_person_id);
3565: hr_utility_trace('p_start_date : ' || p_start_date);
3566: hr_utility_trace('p_end_date : ' || p_end_date);
3567: hr_utility_trace('p_gre_id : ' || p_gre_id);
3568:
3561: FETCH c_get_event_group INTO ln_event_group_id;
3562: CLOSE c_get_event_group;
3563:
3564: hr_utility_trace('p_person_id : ' || p_person_id);
3565: hr_utility_trace('p_start_date : ' || p_start_date);
3566: hr_utility_trace('p_end_date : ' || p_end_date);
3567: hr_utility_trace('p_gre_id : ' || p_gre_id);
3568:
3569: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3562: CLOSE c_get_event_group;
3563:
3564: hr_utility_trace('p_person_id : ' || p_person_id);
3565: hr_utility_trace('p_start_date : ' || p_start_date);
3566: hr_utility_trace('p_end_date : ' || p_end_date);
3567: hr_utility_trace('p_gre_id : ' || p_gre_id);
3568:
3569: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3570: ln_step := 3;
3563:
3564: hr_utility_trace('p_person_id : ' || p_person_id);
3565: hr_utility_trace('p_start_date : ' || p_start_date);
3566: hr_utility_trace('p_end_date : ' || p_end_date);
3567: hr_utility_trace('p_gre_id : ' || p_gre_id);
3568:
3569: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3570: ln_step := 3;
3571:
3565: hr_utility_trace('p_start_date : ' || p_start_date);
3566: hr_utility_trace('p_end_date : ' || p_end_date);
3567: hr_utility_trace('p_gre_id : ' || p_gre_id);
3568:
3569: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3570: ln_step := 3;
3571:
3572: OPEN c_assignments ( p_assignment_id
3573: ,p_start_date
3579: ,ld_eff_start_date
3580: ,ld_eff_end_date;
3581: CLOSE c_assignments;
3582:
3583: hr_utility.set_location(gv_package || lv_procedure_name, 40);
3584: ln_step := 4;
3585:
3586: pay_interpreter_pkg.entry_affected(
3587: p_element_entry_id => NULL
3599: ,t_proration_dates => l_proration_dates
3600: ,t_proration_change_type => l_proration_changes
3601: ,t_proration_type => l_pro_type_tab);
3602:
3603: hr_utility.set_location(gv_package || lv_procedure_name, 50);
3604: ln_step := 5;
3605: hr_utility_trace('pay_interpreter_pkg.entry_affected Returned Rows');
3606:
3607: IF int_pkg_events.COUNT > 0 THEN
3601: ,t_proration_type => l_pro_type_tab);
3602:
3603: hr_utility.set_location(gv_package || lv_procedure_name, 50);
3604: ln_step := 5;
3605: hr_utility_trace('pay_interpreter_pkg.entry_affected Returned Rows');
3606:
3607: IF int_pkg_events.COUNT > 0 THEN
3608:
3609: FOR i IN 1..int_pkg_events.COUNT
3616: END IF;*/
3617:
3618: /* Adding event qualification mechanism so that only those events that
3619: belong to current GRE are picked for archival. (Bug 5921945)*/
3620: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3621: ld_effective_date := int_pkg_events(i).effective_date;
3622: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3623: -- Bug 6005853
3624: IF int_pkg_events(i).column_name IN ('LOCATION_ID',
3618: /* Adding event qualification mechanism so that only those events that
3619: belong to current GRE are picked for archival. (Bug 5921945)*/
3620: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3621: ld_effective_date := int_pkg_events(i).effective_date;
3622: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3623: -- Bug 6005853
3624: IF int_pkg_events(i).column_name IN ('LOCATION_ID',
3625: 'SOFT_CODING_KEYFLEX_ID') THEN
3626: hr_utility_trace('Inside the event on location or soft key flex');
3622: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3623: -- Bug 6005853
3624: IF int_pkg_events(i).column_name IN ('LOCATION_ID',
3625: 'SOFT_CODING_KEYFLEX_ID') THEN
3626: hr_utility_trace('Inside the event on location or soft key flex');
3627: OPEN csr_get_asg_end_date (int_pkg_events(i).effective_date-1);
3628: FETCH csr_get_asg_end_date INTO ld_effective_date;
3629: CLOSE csr_get_asg_end_date;
3630: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3626: hr_utility_trace('Inside the event on location or soft key flex');
3627: OPEN csr_get_asg_end_date (int_pkg_events(i).effective_date-1);
3628: FETCH csr_get_asg_end_date INTO ld_effective_date;
3629: CLOSE csr_get_asg_end_date;
3630: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3631: END IF;
3632: hr_utility_trace('final ld_effective_date :' || ld_effective_date);
3633: IF event_qualified(p_person_id,
3634: p_assignment_id,
3628: FETCH csr_get_asg_end_date INTO ld_effective_date;
3629: CLOSE csr_get_asg_end_date;
3630: hr_utility_trace('ld_effective_date :' ||ld_effective_date);
3631: END IF;
3632: hr_utility_trace('final ld_effective_date :' || ld_effective_date);
3633: IF event_qualified(p_person_id,
3634: p_assignment_id,
3635: ld_effective_date,
3636: p_gre_id) THEN
3636: p_gre_id) THEN
3637: lv_table_name :=
3638: dated_tbls(int_pkg_events(i).dated_table_id).table_name;
3639:
3640: hr_utility_trace('-----------------------------------------------');
3641: hr_utility_trace('lv_table_name :' ||lv_table_name );
3642:
3643:
3644: IF lv_table_name = 'PER_ALL_ASSIGNMENTS_F' THEN
3637: lv_table_name :=
3638: dated_tbls(int_pkg_events(i).dated_table_id).table_name;
3639:
3640: hr_utility_trace('-----------------------------------------------');
3641: hr_utility_trace('lv_table_name :' ||lv_table_name );
3642:
3643:
3644: IF lv_table_name = 'PER_ALL_ASSIGNMENTS_F' THEN
3645:
3672: ,lv_exclude_end_date_from_cur);
3673:
3674: IF asg_events.COUNT > 0 THEN
3675:
3676: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3677: ln_step := 6;
3678:
3679: arch_hire_separation ( p_payroll_action_id => p_payroll_action_id
3680: ,p_asg_action_id => p_asg_action_id
3696: END IF;
3697:
3698: IF gn_person_rec_chng = 0 AND per_events.COUNT > 0 THEN
3699:
3700: hr_utility.set_location(gv_package || lv_procedure_name, 70);
3701: ln_step := 7;
3702:
3703: chk_person_rec_chng( p_per_events => per_events );
3704:
3703: chk_person_rec_chng( p_per_events => per_events );
3704:
3705: END IF;
3706:
3707: hr_utility.set_location(gv_package || lv_procedure_name, 80);
3708: ln_step := 8;
3709:
3710: arch_other_transactions ( p_payroll_action_id => p_payroll_action_id
3711: ,p_asg_action_id => p_asg_action_id
3722: ,p_exc_start_date => lv_exclude_start_date_from_cur
3723: ,p_exc_end_date => lv_exclude_end_date_from_cur
3724: );
3725:
3726: hr_utility.set_location(gv_package || lv_procedure_name, 90);
3727: ln_step := 9;
3728:
3729: EXCEPTION
3730: WHEN others THEN
3730: WHEN others THEN
3731: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3732: gv_package || lv_procedure_name;
3733:
3734: hr_utility_trace(lv_error_message || '-' || sqlerrm);
3735:
3736: lv_error_message :=
3737: pay_emp_action_arch.set_error_message(lv_error_message);
3738:
3735:
3736: lv_error_message :=
3737: pay_emp_action_arch.set_error_message(lv_error_message);
3738:
3739: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3740: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3741: hr_utility.raise_error;
3742:
3743: END get_transactions;
3736: lv_error_message :=
3737: pay_emp_action_arch.set_error_message(lv_error_message);
3738:
3739: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3740: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3741: hr_utility.raise_error;
3742:
3743: END get_transactions;
3744:
3737: pay_emp_action_arch.set_error_message(lv_error_message);
3738:
3739: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3740: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3741: hr_utility.raise_error;
3742:
3743: END get_transactions;
3744:
3745: PROCEDURE get_person_information( p_payroll_action_id IN NUMBER
3902: BEGIN
3903:
3904: lv_procedure_name := 'get_person_information';
3905:
3906: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3907: ln_step := 1;
3908:
3909: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3910: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3905:
3906: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3907: ln_step := 1;
3908:
3909: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3910: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3911: hr_utility_trace('p_person_id : ' ||p_person_id );
3912:
3913: OPEN csr_asg_dates;
3906: hr_utility.set_location(gv_package || lv_procedure_name, 10);
3907: ln_step := 1;
3908:
3909: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3910: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3911: hr_utility_trace('p_person_id : ' ||p_person_id );
3912:
3913: OPEN csr_asg_dates;
3914: FETCH csr_asg_dates INTO ld_asg_start_date,
3907: ln_step := 1;
3908:
3909: hr_utility_trace('p_asg_action_id : ' ||p_asg_action_id);
3910: hr_utility_trace('p_assignment_id : ' ||p_assignment_id);
3911: hr_utility_trace('p_person_id : ' ||p_person_id );
3912:
3913: OPEN csr_asg_dates;
3914: FETCH csr_asg_dates INTO ld_asg_start_date,
3915: ld_asg_end_date;
3921: ELSE
3922: ld_effective_date := ld_asg_start_date;
3923: END IF;
3924: /*ld_effective_date := max (ld_effective_date, p_end_date);
3925: hr_utility_trace('p_end_date = '||
3926: fnd_date.date_to_canonical(p_end_date));*/
3927: hr_utility_trace('ld_asg_start_date = '||
3928: fnd_date.date_to_canonical(ld_asg_start_date));
3929: hr_utility_trace('ld_asg_end_date = '||
3923: END IF;
3924: /*ld_effective_date := max (ld_effective_date, p_end_date);
3925: hr_utility_trace('p_end_date = '||
3926: fnd_date.date_to_canonical(p_end_date));*/
3927: hr_utility_trace('ld_asg_start_date = '||
3928: fnd_date.date_to_canonical(ld_asg_start_date));
3929: hr_utility_trace('ld_asg_end_date = '||
3930: fnd_date.date_to_canonical(ld_asg_end_date));
3931: hr_utility_trace('p_end_date = '||
3925: hr_utility_trace('p_end_date = '||
3926: fnd_date.date_to_canonical(p_end_date));*/
3927: hr_utility_trace('ld_asg_start_date = '||
3928: fnd_date.date_to_canonical(ld_asg_start_date));
3929: hr_utility_trace('ld_asg_end_date = '||
3930: fnd_date.date_to_canonical(ld_asg_end_date));
3931: hr_utility_trace('p_end_date = '||
3932: fnd_date.date_to_canonical(p_end_date));
3933: hr_utility_trace('ld_effective_date = '||
3927: hr_utility_trace('ld_asg_start_date = '||
3928: fnd_date.date_to_canonical(ld_asg_start_date));
3929: hr_utility_trace('ld_asg_end_date = '||
3930: fnd_date.date_to_canonical(ld_asg_end_date));
3931: hr_utility_trace('p_end_date = '||
3932: fnd_date.date_to_canonical(p_end_date));
3933: hr_utility_trace('ld_effective_date = '||
3934: fnd_date.date_to_canonical(ld_effective_date));
3935:
3929: hr_utility_trace('ld_asg_end_date = '||
3930: fnd_date.date_to_canonical(ld_asg_end_date));
3931: hr_utility_trace('p_end_date = '||
3932: fnd_date.date_to_canonical(p_end_date));
3933: hr_utility_trace('ld_effective_date = '||
3934: fnd_date.date_to_canonical(ld_effective_date));
3935:
3936: OPEN c_person_detail (p_person_id
3937: ,ld_effective_date);
3948: ,lv_tax_rfc_id
3949: ,lv_hire_date;
3950: CLOSE c_person_detail;
3951:
3952: hr_utility.set_location(gv_package || lv_procedure_name, 20);
3953: ln_step := 2;
3954:
3955: OPEN c_asg_detail( p_assignment_id
3956: ,ld_effective_date/*
3960: ,ln_soft_cod_kflx_id
3961: ,lv_worker_type;
3962: CLOSE c_asg_detail;
3963:
3964: hr_utility.set_location(gv_package || lv_procedure_name, 30);
3965: ln_step := 3;
3966:
3967: OPEN c_work_schdl( ln_soft_cod_kflx_id );
3968: FETCH c_work_schdl INTO lv_salary_type
3968: FETCH c_work_schdl INTO lv_salary_type
3969: ,lv_work_schedule;
3970: CLOSE c_work_schdl;
3971:
3972: hr_utility.set_location(gv_package || lv_procedure_name, 40);
3973: ln_step := 4;
3974:
3975: OPEN c_location (ln_location_id);
3976: FETCH c_location INTO lv_location_code;
3975: OPEN c_location (ln_location_id);
3976: FETCH c_location INTO lv_location_code;
3977: CLOSE c_location;
3978:
3979: hr_utility.set_location(gv_package || lv_procedure_name, 50);
3980: ln_step := 5;
3981:
3982: -- derive Reduced Working-week indicator from workschedule
3983:
3992: lv_red_work_week_ind := NULL ;
3993:
3994: END IF;
3995:
3996: hr_utility.set_location(gv_package || lv_procedure_name, 60);
3997: ln_step := 6;
3998:
3999: -- Bug 5146225
4000: get_payroll_action_info (p_payroll_action_id,
4019: AND tax_unit_id = p_gre_id
4020: AND action_information_category = 'MX SS PERSON INFORMATION'
4021: AND ld_asg_start_date >=fnd_date.canonical_to_date(action_information10);
4022:
4023: hr_utility.trace('ln_check_person_info_exist '|| ln_check_person_info_exist);
4024: IF ln_check_person_info_exist > 0 THEN
4025:
4026: /*bug9128410: If the person is processing first time along with new hire(08) transaction
4027: then the effective date for the idw calculation is assignment start date otherwise it
4040: ,p_fixed_idw => ln_fixed_idw
4041: ,p_variable_idw => ln_variable_idw );
4042: END IF;
4043:
4044: hr_utility.trace('SS_ARCH PERSON_INFO ln_idw: '|| ln_idw);
4045: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_fixed_idw: '||
4046: ln_fixed_idw);
4047: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
4048: ln_variable_idw);
4041: ,p_variable_idw => ln_variable_idw );
4042: END IF;
4043:
4044: hr_utility.trace('SS_ARCH PERSON_INFO ln_idw: '|| ln_idw);
4045: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_fixed_idw: '||
4046: ln_fixed_idw);
4047: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
4048: ln_variable_idw);
4049:
4043:
4044: hr_utility.trace('SS_ARCH PERSON_INFO ln_idw: '|| ln_idw);
4045: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_fixed_idw: '||
4046: ln_fixed_idw);
4047: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
4048: ln_variable_idw);
4049:
4050: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4051: ln_step := 7;
4046: ln_fixed_idw);
4047: hr_utility.trace('SS_ARCH PERSON_INFO get_idw ln_variable_idw: '||
4048: ln_variable_idw);
4049:
4050: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4051: ln_step := 7;
4052:
4053: IF ( gv_credit_no IS NULL AND gv_credit_start_date IS NULL ) THEN
4054:
4067: ,ln_element_entry_id
4068: ,p_end_date)
4069: LOOP
4070:
4071: hr_utility_trace('name : '|| infonavit.name);
4072: hr_utility_trace('screen_entry_value : '||
4073: infonavit.screen_entry_value);
4074:
4075: IF infonavit.name = 'Credit Number' THEN
4068: ,p_end_date)
4069: LOOP
4070:
4071: hr_utility_trace('name : '|| infonavit.name);
4072: hr_utility_trace('screen_entry_value : '||
4073: infonavit.screen_entry_value);
4074:
4075: IF infonavit.name = 'Credit Number' THEN
4076:
4095: END IF;
4096:
4097: END IF;
4098:
4099: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4100: ln_step := 8;
4101:
4102: ln_index := pay_mx_soc_sec_archive.lrr_act_tab.COUNT;
4103:
4135: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info20 := lv_worker_id;
4136: -- Segment 21 is the 'Do Not Report on Magtape' flag
4137: pay_mx_soc_sec_archive.lrr_act_tab(ln_index).act_info22 := gv_crdt_grant_dt;
4138:
4139: hr_utility.set_location(gv_package || lv_procedure_name, 90);
4140:
4141: EXCEPTION
4142: WHEN others THEN
4143: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4142: WHEN others THEN
4143: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4144: gv_package || lv_procedure_name;
4145:
4146: hr_utility_trace(lv_error_message || '-' || sqlerrm);
4147:
4148: lv_error_message :=
4149: pay_emp_action_arch.set_error_message(lv_error_message);
4150:
4147:
4148: lv_error_message :=
4149: pay_emp_action_arch.set_error_message(lv_error_message);
4150:
4151: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4152: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4153: hr_utility.raise_error;
4154:
4155: END get_person_information;
4148: lv_error_message :=
4149: pay_emp_action_arch.set_error_message(lv_error_message);
4150:
4151: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4152: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4153: hr_utility.raise_error;
4154:
4155: END get_person_information;
4156:
4149: pay_emp_action_arch.set_error_message(lv_error_message);
4150:
4151: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4152: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4153: hr_utility.raise_error;
4154:
4155: END get_person_information;
4156:
4157:
4176: OPEN c_get_idw_calc_method;
4177: FETCH c_get_idw_calc_method INTO lv_idw_calc_method;
4178: CLOSE c_get_idw_calc_method;
4179:
4180: hr_utility_trace ('IDW calculation method = '||lv_idw_calc_method);
4181: RETURN (lv_idw_calc_method);
4182: END get_IDW_calc_method;
4183:
4184: /*****************************************************************************
4281:
4282: BEGIN
4283: lv_procedure_name := 'validate_person_id';
4284: ln_step := 1;
4285: hr_utility.set_location(gv_package || lv_procedure_name, 10);
4286:
4287: IF (p_vidw_check = 'N') THEN
4288: OPEN c_chk_pay_process_events;
4289: FETCH c_chk_pay_process_events INTO lv_exists;
4291: CLOSE c_chk_pay_process_events;
4292: RETURN lv_exists;
4293: END IF;
4294: CLOSE c_chk_pay_process_events;
4295: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4296:
4297: IF gv_IDW_calc_method = 'B' THEN
4298: lv_exists := pay_mx_soc_sec_archive.seniority_changed (p_person_id,
4299: p_end_date,
4303: ELSE
4304: lv_exists :='N';
4305: END IF;
4306: END IF;
4307: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4308: END IF;
4309:
4310: IF (p_vidw_check = 'Y') THEN
4311: hr_utility.set_location(gv_package || lv_procedure_name, 40);
4307: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4308: END IF;
4309:
4310: IF (p_vidw_check = 'Y') THEN
4311: hr_utility.set_location(gv_package || lv_procedure_name, 40);
4312: OPEN chk_prev_action;
4313: FETCH chk_prev_action INTO lv_prev_action_exists;
4314: IF chk_prev_action%FOUND THEN
4315: lv_exists:= 'N';
4315: lv_exists:= 'N';
4316: CLOSE chk_prev_action;
4317: RETURN lv_exists;
4318: ELSE
4319: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4320: CLOSE chk_prev_action;
4321: OPEN c_chk_elements;
4322: FETCH c_chk_elements INTO lv_exists;
4323: IF c_chk_elements%FOUND THEN --
4320: CLOSE chk_prev_action;
4321: OPEN c_chk_elements;
4322: FETCH c_chk_elements INTO lv_exists;
4323: IF c_chk_elements%FOUND THEN --
4324: hr_utility.set_location(gv_package || lv_procedure_name, 60);
4325: lv_exists:= 'Y';
4326: CLOSE c_chk_elements;
4327: RETURN lv_exists;
4328: ELSE
4325: lv_exists:= 'Y';
4326: CLOSE c_chk_elements;
4327: RETURN lv_exists;
4328: ELSE
4329: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4330: CLOSE c_chk_elements;
4331: lv_exists := 'N';
4332: RETURN lv_exists;
4333: END IF; --c_chk_elements
4334: END IF; --chk_prev_action
4335: ELSE
4336: lv_exists := 'Y';
4337: END IF; --p_report_mode
4338: hr_utility.trace('lv_exists :'||lv_exists);
4339: RETURN lv_exists;
4340: EXCEPTION
4341: WHEN others THEN
4342: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4341: WHEN others THEN
4342: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4343: gv_package || lv_procedure_name;
4344:
4345: hr_utility_trace(lv_error_message || '-' || sqlerrm);
4346:
4347: lv_error_message :=
4348: pay_emp_action_arch.set_error_message(lv_error_message);
4349:
4346:
4347: lv_error_message :=
4348: pay_emp_action_arch.set_error_message(lv_error_message);
4349:
4350: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4351: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4352: hr_utility.raise_error;
4353: END validate_person_id;
4354:
4347: lv_error_message :=
4348: pay_emp_action_arch.set_error_message(lv_error_message);
4349:
4350: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4351: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4352: hr_utility.raise_error;
4353: END validate_person_id;
4354:
4355: PROCEDURE range_cursor( p_payroll_action_id IN NUMBER
4348: pay_emp_action_arch.set_error_message(lv_error_message);
4349:
4350: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4351: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4352: hr_utility.raise_error;
4353: END validate_person_id;
4354:
4355: PROCEDURE range_cursor( p_payroll_action_id IN NUMBER
4356: ,p_sqlstr OUT NOCOPY VARCHAR2) IS
4368: BEGIN
4369:
4370: lv_procedure_name := 'range_cursor';
4371:
4372: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
4373:
4374: hr_utility_trace('Starting range_cursor ');
4375: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4376: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
4370: lv_procedure_name := 'range_cursor';
4371:
4372: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
4373:
4374: hr_utility_trace('Starting range_cursor ');
4375: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4376: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
4377: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4378:
4371:
4372: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
4373:
4374: hr_utility_trace('Starting range_cursor ');
4375: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4376: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
4377: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4378:
4379:
4372: hr_utility.set_location('Entering: '||gv_package || lv_procedure_name, 10);
4373:
4374: hr_utility_trace('Starting range_cursor ');
4375: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4376: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
4377: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4378:
4379:
4380: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
4373:
4374: hr_utility_trace('Starting range_cursor ');
4375: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4376: hr_utility_trace('p_payroll_action_id : '||p_payroll_action_id );
4377: hr_utility_trace('ln_gre_id : ' || ln_gre_id );
4378:
4379:
4380: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
4381: ,p_end_date => lv_end_date
4383: ,p_business_group_id => ln_business_group_id
4384: ,p_gre_id => ln_gre_id
4385: );
4386:
4387: hr_utility_trace('lv_start_date :' || lv_start_date);
4388: hr_utility_trace('lv_end_date :' || lv_end_date);
4389: hr_utility_trace('ln_business_group_id :' || ln_business_group_id);
4390:
4391: IF pay_mx_soc_sec_archive.arch_exists_without_upgrade(ln_business_group_id)
4384: ,p_gre_id => ln_gre_id
4385: );
4386:
4387: hr_utility_trace('lv_start_date :' || lv_start_date);
4388: hr_utility_trace('lv_end_date :' || lv_end_date);
4389: hr_utility_trace('ln_business_group_id :' || ln_business_group_id);
4390:
4391: IF pay_mx_soc_sec_archive.arch_exists_without_upgrade(ln_business_group_id)
4392: = 'B' THEN
4385: );
4386:
4387: hr_utility_trace('lv_start_date :' || lv_start_date);
4388: hr_utility_trace('lv_end_date :' || lv_end_date);
4389: hr_utility_trace('ln_business_group_id :' || ln_business_group_id);
4390:
4391: IF pay_mx_soc_sec_archive.arch_exists_without_upgrade(ln_business_group_id)
4392: = 'B' THEN
4393: pay_generic_upgrade.new_business_group (
4398: gv_IDW_calc_method := get_IDW_calc_method (
4399: ln_gre_id,
4400: fnd_date.canonical_to_date (lv_end_date));
4401:
4402: hr_utility_trace('--> gv_IDW_calc_method : ' || gv_IDW_calc_method );
4403: /*
4404: IF gv_mode = 'P' THEN
4405:
4406: gv_periodic_start_date :=
4413: gv_periodic_start_date := lv_start_date;
4414:
4415: END IF;
4416:
4417: hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
4418: hr_utility_trace('gv_periodic_end_date :' || gv_periodic_end_date);
4419: */
4420:
4421: ln_pactid := p_payroll_action_id;
4414:
4415: END IF;
4416:
4417: hr_utility_trace('gv_periodic_start_date :' || gv_periodic_start_date);
4418: hr_utility_trace('gv_periodic_end_date :' || gv_periodic_end_date);
4419: */
4420:
4421: ln_pactid := p_payroll_action_id;
4422:
4437: ** information for all employees and transaction it there is any.
4438: ** IF yes (gn_implementation > 0), it should arrchive only
4439: ** transaction if there is any event occured.
4440: **************************************************************/
4441: hr_utility_trace('--> gn_implementation : ' || gn_implementation );
4442:
4443: IF gn_implementation = 0 THEN
4444:
4445: p_sqlstr :=
4475: /*'AND '''|| gv_mode || ''' = ''P'' ) ' ||*/
4476: 'ORDER BY person_id';
4477:
4478: END IF; -- gn_implementation = 0
4479: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4480:
4481: hr_utility_trace('--> Query formed p_sqlstr : ' || p_sqlstr );
4482:
4483: update pay_payroll_actions
4477:
4478: END IF; -- gn_implementation = 0
4479: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4480:
4481: hr_utility_trace('--> Query formed p_sqlstr : ' || p_sqlstr );
4482:
4483: update pay_payroll_actions
4484: set effective_date = fnd_date.canonical_to_date('4712/12/31')
4485: where payroll_action_id = p_payroll_action_id;
4498: ,p_level => 'F');
4499:
4500: /*End - Bug 13357684*/
4501:
4502: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4503:
4504: END range_cursor;
4505:
4506: PROCEDURE action_creation( p_payroll_action_id IN NUMBER
4835:
4836: lv_procedure_name := 'action_creation';
4837:
4838: ln_step := 1;
4839: hr_utility.set_location(gv_package || lv_procedure_name, 10);
4840:
4841:
4842: hr_utility_trace('--> Entering Action Creation ');
4843:
4838: ln_step := 1;
4839: hr_utility.set_location(gv_package || lv_procedure_name, 10);
4840:
4841:
4842: hr_utility_trace('--> Entering Action Creation ');
4843:
4844: get_payroll_action_info(
4845: p_payroll_action_id => p_payroll_action_id
4846: ,p_start_date => lv_start_date
4847: ,p_end_date => lv_end_date
4848: ,p_business_group_id => ln_business_group_id
4849: ,p_gre_id => ln_gre_id);
4850:
4851: hr_utility_trace('lv_start_date ' || lv_start_date);
4852: hr_utility_trace('lv_end_date ' || lv_end_date);
4853: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4854: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4855:
4848: ,p_business_group_id => ln_business_group_id
4849: ,p_gre_id => ln_gre_id);
4850:
4851: hr_utility_trace('lv_start_date ' || lv_start_date);
4852: hr_utility_trace('lv_end_date ' || lv_end_date);
4853: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4854: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4855:
4856: IF gv_IDW_calc_method IS NULL THEN
4849: ,p_gre_id => ln_gre_id);
4850:
4851: hr_utility_trace('lv_start_date ' || lv_start_date);
4852: hr_utility_trace('lv_end_date ' || lv_end_date);
4853: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4854: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4855:
4856: IF gv_IDW_calc_method IS NULL THEN
4857: gv_IDW_calc_method := get_IDW_calc_method (
4850:
4851: hr_utility_trace('lv_start_date ' || lv_start_date);
4852: hr_utility_trace('lv_end_date ' || lv_end_date);
4853: hr_utility_trace('ln_business_group_id ' || ln_business_group_id);
4854: hr_utility_trace('ln_gre_id ' || ln_gre_id);
4855:
4856: IF gv_IDW_calc_method IS NULL THEN
4857: gv_IDW_calc_method := get_IDW_calc_method (
4858: ln_gre_id,
4858: ln_gre_id,
4859: fnd_date.canonical_to_date (lv_end_date));
4860: END IF;
4861:
4862: hr_utility_trace('--> gv_IDW_calc_method ' || gv_IDW_calc_method);
4863:
4864: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4865: ln_step := 2;
4866:
4860: END IF;
4861:
4862: hr_utility_trace('--> gv_IDW_calc_method ' || gv_IDW_calc_method);
4863:
4864: hr_utility.set_location(gv_package || lv_procedure_name, 20);
4865: ln_step := 2;
4866:
4867: lb_range_person := pay_ac_utility.range_person_on(
4868: p_report_type => 'SS_ARCHIVE'
4869: ,p_report_format => 'SS_ARCHIVE'
4870: ,p_report_qualifier => 'SS_ARCHIVE'
4871: ,p_report_category => 'RT');
4872:
4873: hr_utility_trace('--> Step ' || ln_step);
4874: IF lb_range_person THEN
4875: OPEN c_get_range_emp(p_payroll_action_id
4876: ,p_chunk
4877: ,ln_business_group_id
4881: ,fnd_date.canonical_to_date(gv_periodic_start_date)
4882: ,fnd_date.canonical_to_date(gv_periodic_end_date)
4883: );
4884:
4885: hr_utility_trace('c_get_range_emp');
4886:
4887: ELSE
4888: OPEN c_get_emp( p_payroll_action_id
4889: , p_start_person_id
4894: , fnd_date.canonical_to_date(lv_end_date)
4895: , fnd_date.canonical_to_date(gv_periodic_start_date)
4896: , fnd_date.canonical_to_date(gv_periodic_end_date) );
4897:
4898: hr_utility_trace('c_get_emp');
4899:
4900: END IF;
4901:
4902: --Loop for all rows returned for SQL statement.
4899:
4900: END IF;
4901:
4902: --Loop for all rows returned for SQL statement.
4903: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4904:
4905: ln_person_id_prev := 0;
4906: ln_assignment_id_prev := 0;
4907:
4908: LOOP
4909:
4910: IF lb_range_person THEN
4911:
4912: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4913: ln_step := 3;
4914: hr_utility_trace('--> Fetching from c_get_range_emp ' );
4915: FETCH c_get_range_emp INTO ln_person_id
4916: ,ln_primary_flag
4910: IF lb_range_person THEN
4911:
4912: hr_utility.set_location(gv_package || lv_procedure_name, 30);
4913: ln_step := 3;
4914: hr_utility_trace('--> Fetching from c_get_range_emp ' );
4915: FETCH c_get_range_emp INTO ln_person_id
4916: ,ln_primary_flag
4917: ,ln_assignment_id
4918: ,lv_report_mode;
4920: EXIT WHEN c_get_range_emp%NOTFOUND;
4921:
4922: ELSE
4923:
4924: hr_utility.set_location(gv_package || lv_procedure_name, 40);
4925: ln_step := 4;
4926: hr_utility_trace('--> Fetching from c_get_emp ' );
4927: FETCH c_get_emp INTO ln_person_id
4928: ,ln_primary_flag
4922: ELSE
4923:
4924: hr_utility.set_location(gv_package || lv_procedure_name, 40);
4925: ln_step := 4;
4926: hr_utility_trace('--> Fetching from c_get_emp ' );
4927: FETCH c_get_emp INTO ln_person_id
4928: ,ln_primary_flag
4929: ,ln_assignment_id
4930: ,lv_report_mode;
4932: EXIT WHEN c_get_emp%NOTFOUND;
4933:
4934: END IF;
4935:
4936: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4937: ln_step := 5;
4938:
4939: hr_utility_trace('ln_person_id = ' || ln_person_id);
4940: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4935:
4936: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4937: ln_step := 5;
4938:
4939: hr_utility_trace('ln_person_id = ' || ln_person_id);
4940: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4941: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4942: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4943: hr_utility_trace('lv_start_date = ' || lv_start_date);
4936: hr_utility.set_location(gv_package || lv_procedure_name, 50);
4937: ln_step := 5;
4938:
4939: hr_utility_trace('ln_person_id = ' || ln_person_id);
4940: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4941: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4942: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4943: hr_utility_trace('lv_start_date = ' || lv_start_date);
4944: hr_utility_trace('lv_end_date = ' || lv_end_date);
4937: ln_step := 5;
4938:
4939: hr_utility_trace('ln_person_id = ' || ln_person_id);
4940: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4941: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4942: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4943: hr_utility_trace('lv_start_date = ' || lv_start_date);
4944: hr_utility_trace('lv_end_date = ' || lv_end_date);
4945:
4938:
4939: hr_utility_trace('ln_person_id = ' || ln_person_id);
4940: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4941: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4942: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4943: hr_utility_trace('lv_start_date = ' || lv_start_date);
4944: hr_utility_trace('lv_end_date = ' || lv_end_date);
4945:
4946: IF (ln_person_id <> ln_person_id_prev OR
4939: hr_utility_trace('ln_person_id = ' || ln_person_id);
4940: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4941: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4942: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4943: hr_utility_trace('lv_start_date = ' || lv_start_date);
4944: hr_utility_trace('lv_end_date = ' || lv_end_date);
4945:
4946: IF (ln_person_id <> ln_person_id_prev OR
4947: ln_assignment_id <> ln_assignment_id_prev) THEN
4940: hr_utility_trace('ln_assignment_id = ' || ln_assignment_id);
4941: hr_utility_trace('ln_primary_flag = ' || ln_primary_flag);
4942: hr_utility_trace('lv_report_mode = ' || lv_report_mode);
4943: hr_utility_trace('lv_start_date = ' || lv_start_date);
4944: hr_utility_trace('lv_end_date = ' || lv_end_date);
4945:
4946: IF (ln_person_id <> ln_person_id_prev OR
4947: ln_assignment_id <> ln_assignment_id_prev) THEN
4948:
4949: IF gn_implementation = 0 THEN
4950:
4951:
4952: ln_step := 6;
4953: hr_utility_trace('--> Step 6' );
4954: hr_utility_trace('IMPLEMENTATION TRUE');
4955:
4956: SELECT pay_assignment_actions_s.nextval
4957: INTO ln_asg_act_id
4950:
4951:
4952: ln_step := 6;
4953: hr_utility_trace('--> Step 6' );
4954: hr_utility_trace('IMPLEMENTATION TRUE');
4955:
4956: SELECT pay_assignment_actions_s.nextval
4957: INTO ln_asg_act_id
4958: FROM dual;
4956: SELECT pay_assignment_actions_s.nextval
4957: INTO ln_asg_act_id
4958: FROM dual;
4959:
4960: hr_utility_trace('--> Inserting into pay_assignment_actions' );
4961: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4962: -- insert into pay_assignment_actions.
4963: hr_nonrun_asact.insact(ln_asg_act_id,
4964: ln_assignment_id,
4957: INTO ln_asg_act_id
4958: FROM dual;
4959:
4960: hr_utility_trace('--> Inserting into pay_assignment_actions' );
4961: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4962: -- insert into pay_assignment_actions.
4963: hr_nonrun_asact.insact(ln_asg_act_id,
4964: ln_assignment_id,
4965: p_payroll_action_id,
4968: null,
4969: 'U',
4970: null);
4971:
4972: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4973: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4974: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4975: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4976:
4969: 'U',
4970: null);
4971:
4972: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4973: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4974: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4975: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4976:
4977: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4970: null);
4971:
4972: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4973: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4974: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4975: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4976:
4977: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4978:
4971:
4972: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4973: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4974: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4975: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4976:
4977: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4978:
4979: hr_utility_trace('--> Updating into pay_assignment_actions' );
4973: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
4974: hr_utility_trace('p_payroll_action_id= ' || p_payroll_action_id);
4975: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4976:
4977: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4978:
4979: hr_utility_trace('--> Updating into pay_assignment_actions' );
4980: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4981: UPDATE pay_assignment_actions
4975: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
4976:
4977: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4978:
4979: hr_utility_trace('--> Updating into pay_assignment_actions' );
4980: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4981: UPDATE pay_assignment_actions
4982: SET serial_number = to_char(ln_person_id) || '|' ||
4983: 'MX_IDWV=' || lv_report_mode || '|'
4976:
4977: hr_utility.set_location(gv_package || lv_procedure_name, 80);
4978:
4979: hr_utility_trace('--> Updating into pay_assignment_actions' );
4980: hr_utility_trace('--> ln_asg_act_id' || ln_asg_act_id );
4981: UPDATE pay_assignment_actions
4982: SET serial_number = to_char(ln_person_id) || '|' ||
4983: 'MX_IDWV=' || lv_report_mode || '|'
4984: WHERE assignment_action_id = ln_asg_act_id;
4986: ELSE
4987:
4988: ln_step := 7;
4989:
4990: hr_utility_trace('--> In STep 7');
4991:
4992: IF ( pay_mx_soc_sec_archive.per_asg.EXISTS(ln_person_id) = FALSE )
4993: THEN
4994: hr_utility_trace('--> Adding in pay_mx_soc_sec_archive.per_asg ');
4990: hr_utility_trace('--> In STep 7');
4991:
4992: IF ( pay_mx_soc_sec_archive.per_asg.EXISTS(ln_person_id) = FALSE )
4993: THEN
4994: hr_utility_trace('--> Adding in pay_mx_soc_sec_archive.per_asg ');
4995: pay_mx_soc_sec_archive.per_asg(ln_person_id).person_id :=
4996: ln_person_id;
4997: pay_mx_soc_sec_archive.per_asg(ln_person_id).assignment_id :=
4998: ln_assignment_id;
4997: pay_mx_soc_sec_archive.per_asg(ln_person_id).assignment_id :=
4998: ln_assignment_id;
4999: END IF;
5000:
5001: hr_utility_trace('--> lv_report_mode '||lv_report_mode);
5002: IF lv_report_mode = 'Y' THEN
5003:
5004: ln_events_found := 1;
5005:
5014: AND fnd_date.canonical_to_date(lv_end_date);
5015: END IF;
5016:
5017: ln_step := 8;
5018: hr_utility_trace('--> In Step 8 ');
5019: hr_utility_trace('--> ln_events_found ' || ln_events_found);
5020:
5021: IF ln_events_found > 0
5022: OR (gv_IDW_calc_method = 'B' AND
5015: END IF;
5016:
5017: ln_step := 8;
5018: hr_utility_trace('--> In Step 8 ');
5019: hr_utility_trace('--> ln_events_found ' || ln_events_found);
5020:
5021: IF ln_events_found > 0
5022: OR (gv_IDW_calc_method = 'B' AND
5023: seniority_changed (
5023: seniority_changed (
5024: ln_person_id,
5025: fnd_date.canonical_to_date(lv_end_date),
5026: fnd_date.canonical_to_date(lv_start_date)) = 'Y') THEN
5027: hr_utility_trace('--> Inside If ' );
5028: SELECT pay_assignment_actions_s.nextval
5029: INTO ln_asg_act_id
5030: FROM dual;
5031:
5028: SELECT pay_assignment_actions_s.nextval
5029: INTO ln_asg_act_id
5030: FROM dual;
5031:
5032: hr_utility_trace('--> insert into pay_assignment_actions.' );
5033: -- insert into pay_assignment_actions.
5034: hr_nonrun_asact.insact(ln_asg_act_id,
5035: ln_assignment_id,
5036: p_payroll_action_id,
5039: null,
5040: 'U',
5041: null);
5042:
5043: hr_utility.set_location(gv_package || lv_procedure_name, 90);
5044: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
5045: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
5046:
5047: hr_utility.set_location(gv_package || lv_procedure_name, 100);
5040: 'U',
5041: null);
5042:
5043: hr_utility.set_location(gv_package || lv_procedure_name, 90);
5044: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
5045: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
5046:
5047: hr_utility.set_location(gv_package || lv_procedure_name, 100);
5048:
5041: null);
5042:
5043: hr_utility.set_location(gv_package || lv_procedure_name, 90);
5044: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
5045: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
5046:
5047: hr_utility.set_location(gv_package || lv_procedure_name, 100);
5048:
5049: UPDATE pay_assignment_actions
5043: hr_utility.set_location(gv_package || lv_procedure_name, 90);
5044: hr_utility_trace('ln_asg_act_id = ' || ln_asg_act_id);
5045: hr_utility_trace('ln_tax_unit_id = ' || ln_gre_id);
5046:
5047: hr_utility.set_location(gv_package || lv_procedure_name, 100);
5048:
5049: UPDATE pay_assignment_actions
5050: SET serial_number = to_char(ln_person_id) || '|' ||
5051: 'MX_IDWV=' || lv_report_mode || '|'
5076: END IF;
5077:
5078: ln_step := 9;
5079:
5080: hr_utility_trace('--> Step 9 ' );
5081: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
5082: hr_utility_trace(' p_chunk : '|| p_chunk );
5083:
5084: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
5077:
5078: ln_step := 9;
5079:
5080: hr_utility_trace('--> Step 9 ' );
5081: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
5082: hr_utility_trace(' p_chunk : '|| p_chunk );
5083:
5084: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
5085:
5078: ln_step := 9;
5079:
5080: hr_utility_trace('--> Step 9 ' );
5081: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
5082: hr_utility_trace(' p_chunk : '|| p_chunk );
5083:
5084: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
5085:
5086: EXCEPTION
5080: hr_utility_trace('--> Step 9 ' );
5081: hr_utility_trace(' p_payroll_action_id : '|| p_payroll_action_id );
5082: hr_utility_trace(' p_chunk : '|| p_chunk );
5083:
5084: hr_utility.set_location('Leaving: '||gv_package || lv_procedure_name, 100);
5085:
5086: EXCEPTION
5087: WHEN others THEN
5088: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
5087: WHEN others THEN
5088: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
5089: gv_package || lv_procedure_name;
5090:
5091: hr_utility_trace(lv_error_message || '-' || sqlerrm);
5092:
5093: lv_error_message :=
5094: pay_emp_action_arch.set_error_message(lv_error_message);
5095:
5092:
5093: lv_error_message :=
5094: pay_emp_action_arch.set_error_message(lv_error_message);
5095:
5096: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5097: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5098: hr_utility.raise_error;
5099:
5100:
5093: lv_error_message :=
5094: pay_emp_action_arch.set_error_message(lv_error_message);
5095:
5096: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5097: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5098: hr_utility.raise_error;
5099:
5100:
5101:
5094: pay_emp_action_arch.set_error_message(lv_error_message);
5095:
5096: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5097: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5098: hr_utility.raise_error;
5099:
5100:
5101:
5102: END action_creation;
5156: lv_is_employee VARCHAR2(2) := 'N';
5157:
5158: BEGIN
5159:
5160: hr_utility_trace('--> Archive_data' );
5161: hr_utility_trace('--> p_asg_action_id ' || p_asg_action_id );
5162: hr_utility_trace('--> p_effective_date' || p_effective_date );
5163:
5164: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
5157:
5158: BEGIN
5159:
5160: hr_utility_trace('--> Archive_data' );
5161: hr_utility_trace('--> p_asg_action_id ' || p_asg_action_id );
5162: hr_utility_trace('--> p_effective_date' || p_effective_date );
5163:
5164: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
5165:
5158: BEGIN
5159:
5160: hr_utility_trace('--> Archive_data' );
5161: hr_utility_trace('--> p_asg_action_id ' || p_asg_action_id );
5162: hr_utility_trace('--> p_effective_date' || p_effective_date );
5163:
5164: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
5165:
5166: lv_procedure_name := 'archive_data';
5177: IF gn_implementation = 0 THEN
5178: gn_person_rec_chng := 1;
5179: END IF;
5180:
5181: hr_utility.set_location(gv_package || lv_procedure_name, 10);
5182: ln_step := 1;
5183:
5184: OPEN c_asgact_info(p_asg_action_id);
5185: FETCH c_asgact_info INTO ln_payroll_action_id
5191:
5192: ln_person_id := SUBSTR( lv_serial_number, 1,
5193: NVL(INSTR(lv_serial_number,'|'),50) -1);
5194:
5195: hr_utility_trace('--> ln_person_id'|| ln_person_id);
5196: /***********************************************************
5197: ** gv_variable_idw is used to get information about whether
5198: ** whether this assignment has variable IDW or not.
5199: ***********************************************************/
5199: ***********************************************************/
5200:
5201: gv_variable_idw :=
5202: NVL(pay_mx_utility.get_legi_param_val('MX_IDWV',lv_serial_number), 'N');
5203: hr_utility_trace('--> gv_variable_idw' || gv_variable_idw );
5204:
5205: hr_utility.set_location(gv_package || lv_procedure_name, 20);
5206: ln_step := 2;
5207:
5201: gv_variable_idw :=
5202: NVL(pay_mx_utility.get_legi_param_val('MX_IDWV',lv_serial_number), 'N');
5203: hr_utility_trace('--> gv_variable_idw' || gv_variable_idw );
5204:
5205: hr_utility.set_location(gv_package || lv_procedure_name, 20);
5206: ln_step := 2;
5207:
5208: get_payroll_action_info(
5209: p_payroll_action_id => ln_payroll_action_id
5211: ,p_end_date => lv_end_date
5212: ,p_business_group_id => ln_business_group_id
5213: ,p_gre_id => ln_gre_id);
5214:
5215: hr_utility.set_location(gv_package || lv_procedure_name, 30);
5216: ln_step := 3;
5217:
5218: ld_start_date := fnd_date.canonical_to_date(lv_start_date);
5219: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
5217:
5218: ld_start_date := fnd_date.canonical_to_date(lv_start_date);
5219: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
5220:
5221: hr_utility_trace('--> lv_start_date' || lv_start_date );
5222: hr_utility_trace('--> lv_end_date' || lv_end_date );
5223: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
5224: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
5225:
5218: ld_start_date := fnd_date.canonical_to_date(lv_start_date);
5219: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
5220:
5221: hr_utility_trace('--> lv_start_date' || lv_start_date );
5222: hr_utility_trace('--> lv_end_date' || lv_end_date );
5223: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
5224: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
5225:
5226:
5219: ld_end_date := fnd_date.canonical_to_date(lv_end_date);
5220:
5221: hr_utility_trace('--> lv_start_date' || lv_start_date );
5222: hr_utility_trace('--> lv_end_date' || lv_end_date );
5223: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
5224: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
5225:
5226:
5227: IF gv_IDW_calc_method IS NULL THEN
5220:
5221: hr_utility_trace('--> lv_start_date' || lv_start_date );
5222: hr_utility_trace('--> lv_end_date' || lv_end_date );
5223: hr_utility_trace('--> ln_business_group_id' || ln_business_group_id );
5224: hr_utility_trace('--> ln_gre_id' || ln_gre_id );
5225:
5226:
5227: IF gv_IDW_calc_method IS NULL THEN
5228: gv_IDW_calc_method := get_IDW_calc_method (
5228: gv_IDW_calc_method := get_IDW_calc_method (
5229: ln_gre_id,
5230: fnd_date.canonical_to_date (lv_end_date));
5231: END IF;
5232: hr_utility_trace('--> gv_IDW_calc_method' || gv_IDW_calc_method );
5233: /****************************************************************
5234: ** Archive all the payroll action level data once only when
5235: ** chunk number is 1. Also check if this has not been archived
5236: ** earlier
5235: ** chunk number is 1. Also check if this has not been archived
5236: ** earlier
5237: *****************************************************************/
5238:
5239: hr_utility.set_location(gv_package || lv_procedure_name, 40);
5240: ln_step := 4;
5241:
5242: OPEN c_check_pay_action(ln_payroll_action_id);
5243: FETCH c_check_pay_action INTO ln_pay_action_count;
5242: OPEN c_check_pay_action(ln_payroll_action_id);
5243: FETCH c_check_pay_action INTO ln_pay_action_count;
5244: CLOSE c_check_pay_action;
5245:
5246: hr_utility_trace('--> ln_pay_action_count' || ln_pay_action_count );
5247: IF ln_pay_action_count = 0 THEN
5248:
5249: hr_utility.set_location(gv_package || lv_procedure_name, 50);
5250: ln_step := 5;
5245:
5246: hr_utility_trace('--> ln_pay_action_count' || ln_pay_action_count );
5247: IF ln_pay_action_count = 0 THEN
5248:
5249: hr_utility.set_location(gv_package || lv_procedure_name, 50);
5250: ln_step := 5;
5251:
5252: IF ln_chunk_number = 1 THEN
5253:
5252: IF ln_chunk_number = 1 THEN
5253:
5254: ln_step := 25;
5255:
5256: hr_utility.set_location(gv_package || lv_procedure_name, 60);
5257: ln_step := 6;
5258: hr_utility_trace('--> arch_pay_action_level_data' );
5259: arch_pay_action_level_data(
5260: p_payroll_action_id => ln_payroll_action_id
5254: ln_step := 25;
5255:
5256: hr_utility.set_location(gv_package || lv_procedure_name, 60);
5257: ln_step := 6;
5258: hr_utility_trace('--> arch_pay_action_level_data' );
5259: arch_pay_action_level_data(
5260: p_payroll_action_id => ln_payroll_action_id
5261: ,p_assignment_id => ln_assignment_id
5262: ,p_effective_Date => ld_end_date
5265: END IF;
5266:
5267: END IF;
5268:
5269: hr_utility.set_location(gv_package || lv_procedure_name, 70);
5270: ln_step := 7;
5271:
5272: hr_utility_trace('--> get_transactions' );
5273: get_transactions( p_payroll_action_id => ln_payroll_action_id
5268:
5269: hr_utility.set_location(gv_package || lv_procedure_name, 70);
5270: ln_step := 7;
5271:
5272: hr_utility_trace('--> get_transactions' );
5273: get_transactions( p_payroll_action_id => ln_payroll_action_id
5274: ,p_asg_action_id => p_asg_action_id
5275: ,p_effective_Date => p_effective_Date
5276: ,p_assignment_id => ln_assignment_id
5283: );
5284:
5285: IF ( gn_person_rec_chng = 1 ) THEN
5286:
5287: hr_utility.set_location(gv_package || lv_procedure_name, 80);
5288: ln_step := 8;
5289: hr_utility_trace('--> 8 get_person_information' );
5290: get_person_information(
5291: p_payroll_action_id => ln_payroll_action_id
5285: IF ( gn_person_rec_chng = 1 ) THEN
5286:
5287: hr_utility.set_location(gv_package || lv_procedure_name, 80);
5288: ln_step := 8;
5289: hr_utility_trace('--> 8 get_person_information' );
5290: get_person_information(
5291: p_payroll_action_id => ln_payroll_action_id
5292: ,p_asg_action_id => p_asg_action_id
5293: ,p_effective_Date => p_effective_Date
5301: );
5302:
5303: END IF;
5304:
5305: hr_utility.set_location(gv_package || lv_procedure_name, 90);
5306: ln_step := 9;
5307:
5308: l_act_tab.DELETE;
5309: j := 0;
5316:
5317: IF pay_mx_soc_sec_archive.lrr_act_tab(i).action_info_category
5318: = 'MX SS TRANSACTIONS' THEN
5319: /*Added code for bug 6862116*/
5320: hr_utility_trace('Checking for active employe ..for MX SS TRANSACTIONS' );
5321: OPEN c_check_active_employee(pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1,
5322: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
5323: FETCH c_check_active_employee INTO lv_is_employee;
5324: CLOSE c_check_active_employee;
5322: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
5323: FETCH c_check_active_employee INTO lv_is_employee;
5324: CLOSE c_check_active_employee;
5325:
5326: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5327: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
5328: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5329:
5330: IF (TRUNC(fnd_date.canonical_to_date(
5323: FETCH c_check_active_employee INTO lv_is_employee;
5324: CLOSE c_check_active_employee;
5325:
5326: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5327: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
5328: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5329:
5330: IF (TRUNC(fnd_date.canonical_to_date(
5331: pay_mx_soc_sec_archive.lrr_act_tab(i).act_info2)) =
5324: CLOSE c_check_active_employee;
5325:
5326: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5327: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO2);
5328: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5329:
5330: IF (TRUNC(fnd_date.canonical_to_date(
5331: pay_mx_soc_sec_archive.lrr_act_tab(i).act_info2)) =
5332: TRUNC(fnd_date.canonical_to_date('4712/12/31'))) OR lv_is_employee = 'N' THEN
5331: pay_mx_soc_sec_archive.lrr_act_tab(i).act_info2)) =
5332: TRUNC(fnd_date.canonical_to_date('4712/12/31'))) OR lv_is_employee = 'N' THEN
5333:
5334: NULL;
5335: hr_utility_trace('--> NULL' );
5336: ELSE
5337: l_act_tab(j) := pay_mx_soc_sec_archive.lrr_act_tab(i);
5338:
5339: j := j + 1;
5341: END if;
5342:
5343: ELSIF pay_mx_soc_sec_archive.lrr_act_tab(i).action_info_category
5344: = 'MX SS PERSON INFORMATION' THEN
5345: hr_utility_trace('Checking for active employe ..for MX SS PERSON Information' );
5346: OPEN c_check_active_employee (pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1,
5347: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
5348: FETCH c_check_active_employee INTO lv_is_employee;
5349: CLOSE c_check_active_employee;
5347: pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
5348: FETCH c_check_active_employee INTO lv_is_employee;
5349: CLOSE c_check_active_employee;
5350:
5351: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5352: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
5353:
5354: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5355:
5348: FETCH c_check_active_employee INTO lv_is_employee;
5349: CLOSE c_check_active_employee;
5350:
5351: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5352: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
5353:
5354: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5355:
5356:
5350:
5351: hr_utility_trace('Person ID for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO1);
5352: hr_utility_trace('Transaction Date for MX SS Transcations .. ' || pay_mx_soc_sec_archive.lrr_act_tab(i).ACT_INFO10);
5353:
5354: hr_utility_trace('IS Employee active .. ' || lv_is_employee);
5355:
5356:
5357: IF lv_is_employee = 'N' THEN
5358:
5385: END IF;
5386:
5387: IF pay_mx_soc_sec_archive.lrr_act_tab.COUNT > 0 THEN
5388:
5389: hr_utility_trace('--> pay_mx_soc_sec_archive.lrr_act_tab.COUNT ' || pay_mx_soc_sec_archive.lrr_act_tab.COUNT);
5390: pay_emp_action_arch.insert_rows_thro_api_process(
5391: p_action_context_id => p_asg_action_id
5392: ,p_action_context_type => 'AAP'
5393: ,p_assignment_id => ln_assignment_id
5399: END IF;
5400:
5401: pay_mx_soc_sec_archive.lrr_act_tab.DELETE;
5402:
5403: hr_utility.set_location(gv_package || lv_procedure_name, 100);
5404: ln_step := 10;
5405:
5406: EXCEPTION
5407: WHEN others THEN
5404: ln_step := 10;
5405:
5406: EXCEPTION
5407: WHEN others THEN
5408: hr_utility_trace('--> Exception in Archive_data ' );
5409: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
5410: gv_package || lv_procedure_name;
5411:
5412: hr_utility_trace(lv_error_message || '-' || sqlerrm);
5408: hr_utility_trace('--> Exception in Archive_data ' );
5409: lv_error_message := 'Error at step ' || ln_step || ' in ' ||
5410: gv_package || lv_procedure_name;
5411:
5412: hr_utility_trace(lv_error_message || '-' || sqlerrm);
5413:
5414: lv_error_message :=
5415: pay_emp_action_arch.set_error_message(lv_error_message);
5416:
5413:
5414: lv_error_message :=
5415: pay_emp_action_arch.set_error_message(lv_error_message);
5416:
5417: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5418: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5419: hr_utility.raise_error;
5420:
5421: END archive_data;
5414: lv_error_message :=
5415: pay_emp_action_arch.set_error_message(lv_error_message);
5416:
5417: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5418: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5419: hr_utility.raise_error;
5420:
5421: END archive_data;
5422:
5415: pay_emp_action_arch.set_error_message(lv_error_message);
5416:
5417: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5418: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5419: hr_utility.raise_error;
5420:
5421: END archive_data;
5422:
5423: PROCEDURE archinit(p_payroll_action_id in number) IS
5489: p_attribute18 => NULL,
5490: p_attribute19 => NULL,
5491: p_attribute20 => NULL);
5492:
5493: hr_utility_trace ('end_date in legislative_parameters = '||
5494: fnd_date.date_to_canonical(ld_end_date));
5495: hr_utility_trace ('Date stamped in pay_recorded_requests = '||
5496: fnd_date.date_to_canonical(ld_start_date));
5497:
5491: p_attribute20 => NULL);
5492:
5493: hr_utility_trace ('end_date in legislative_parameters = '||
5494: fnd_date.date_to_canonical(ld_end_date));
5495: hr_utility_trace ('Date stamped in pay_recorded_requests = '||
5496: fnd_date.date_to_canonical(ld_start_date));
5497:
5498: /* We need to stamp the end date in pay_recorded_requests only if
5499: existing date is less than end date stamped on payroll action. This
5502:
5503: In other words, date stamped in pay_recorded_requests can only be
5504: advanced further; it cannot be updated by a potential retry attempt. */
5505: IF ld_end_date > ld_start_date THEN
5506: hr_utility_trace ('Advancing date in pay_recorded_requests to '||
5507: fnd_date.date_to_canonical(ld_end_date));
5508: pay_recorded_requests_pkg.set_recorded_date(
5509: p_process => 'MX_SOC_SEC_ARCH',
5510: p_recorded_date => ld_end_date,
5529: p_attribute18 => NULL,
5530: p_attribute19 => NULL,
5531: p_attribute20 => NULL);
5532: ELSE
5533: hr_utility_trace ('pay_recorded_requests not updated.');
5534: END IF;
5535:
5536: gt_gre_cache.delete();
5537:
5569: E - Error. Archiver runs exist without successful upgrade runs.
5570: */
5571:
5572: l_proc_name := gv_package || 'arch_exists_without_upgrade';
5573: hr_utility_trace ('Entering '||l_proc_name);
5574: lv_exists := 'N';
5575:
5576: OPEN csr_upgrade_exists;
5577: FETCH csr_upgrade_exists INTO lv_exists;
5577: FETCH csr_upgrade_exists INTO lv_exists;
5578: CLOSE csr_upgrade_exists;
5579:
5580: IF lv_exists = 'Y' THEN
5581: hr_utility_trace ('Completed upgrade process exists.');
5582: hr_utility_trace ('Leaving '||l_proc_name);
5583: RETURN ('C');
5584: END IF;
5585:
5578: CLOSE csr_upgrade_exists;
5579:
5580: IF lv_exists = 'Y' THEN
5581: hr_utility_trace ('Completed upgrade process exists.');
5582: hr_utility_trace ('Leaving '||l_proc_name);
5583: RETURN ('C');
5584: END IF;
5585:
5586: OPEN csr_arch_exists;
5587: FETCH csr_arch_exists INTO lv_exists;
5588: CLOSE csr_arch_exists;
5589:
5590: IF lv_exists = 'N' THEN
5591: hr_utility_trace ('Brand new business group.');
5592: hr_utility_trace ('Leaving '||l_proc_name);
5593: RETURN ('B');
5594: ELSE
5595: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5588: CLOSE csr_arch_exists;
5589:
5590: IF lv_exists = 'N' THEN
5591: hr_utility_trace ('Brand new business group.');
5592: hr_utility_trace ('Leaving '||l_proc_name);
5593: RETURN ('B');
5594: ELSE
5595: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5596: hr_utility_trace ('Leaving '||l_proc_name);
5591: hr_utility_trace ('Brand new business group.');
5592: hr_utility_trace ('Leaving '||l_proc_name);
5593: RETURN ('B');
5594: ELSE
5595: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5596: hr_utility_trace ('Leaving '||l_proc_name);
5597: RETURN ('E');
5598: END IF;
5599:
5592: hr_utility_trace ('Leaving '||l_proc_name);
5593: RETURN ('B');
5594: ELSE
5595: hr_utility_trace ('Archiver runs exist without successful upgrade.');
5596: hr_utility_trace ('Leaving '||l_proc_name);
5597: RETURN ('E');
5598: END IF;
5599:
5600: END arch_exists_without_upgrade;
5600: END arch_exists_without_upgrade;
5601:
5602: BEGIN
5603:
5604: --hr_utility.trace_on(null,'SUA');
5605: gv_package := 'pay_mx_soc_sec_archive.';
5606: gv_debug := hr_utility.debug_enabled;
5607:
5608: END PAY_MX_SOC_SEC_ARCHIVE;
5602: BEGIN
5603:
5604: --hr_utility.trace_on(null,'SUA');
5605: gv_package := 'pay_mx_soc_sec_archive.';
5606: gv_debug := hr_utility.debug_enabled;
5607:
5608: END PAY_MX_SOC_SEC_ARCHIVE;