[Home] [Help]
548: l_current_percentage number;
549: l_proc varchar2(72)
550: := g_package||'get_curr_jurisdiction_db_value';
551: begin
552: hr_utility.set_location('Entering:'||l_proc, 10);
553: open csr_ele_entry
554: (
555: p_element_link_id,
556: p_input_value_id_tbl(2),
557: p_jurisdiction,
558: p_assignment_id
559: );
560: fetch csr_ele_entry into l_element_entry_id;
561: hr_utility.set_location(l_proc, 20);
562: if csr_ele_entry%FOUND then
563: close csr_ele_entry;
564: open csr_get_curr_percnt
565: (
576: close csr_get_curr_percnt;
577: end if;
578: end if;
579: close csr_ele_entry;
580: hr_utility.set_location('Leaving:'||l_proc, 30);
581: return 0;
582: end get_curr_jurisdiction_db_value;
583: --
584: -- ----------------------------------------------------------------------------
594: l_jurisdiction varchar2(11);
595: l_proc varchar2(72)
596: := g_package||'chk_work_location';
597: begin
598: hr_utility.set_location('Entering:'||l_proc, 10);
599: open csr_get_jurisdiction
600: (
601: p_assignment_id,
602: p_effective_date
609: else
610: close csr_get_jurisdiction;
611: return FALSE;
612: end if;
613: hr_utility.set_location('Leaving:'||l_proc, 20);
614: end chk_work_location;
615: --
616: -- ----------------------------------------------------------------------------
617: -- |----------------------------< asg_loc_comp >------------------------------|
629: l_inc number;
630: l_proc varchar2(72)
631: := g_package||'asg_loc_comp';
632: Begin
633: hr_utility.set_location('Entering:'||l_proc, 10);
634: if t_asg_tbl.first is not null then
635: l_inc := t_asg_tbl.first;
636: while t_asg_tbl.exists(l_inc) loop
637: if l_effective_date >= t_asg_tbl(l_inc).start_date
648: end if;
649: l_inc := t_asg_tbl.next(l_inc);
650: end loop;
651: end if;
652: hr_utility.set_location('Leaving:'||l_proc, 20);
653: return FALSE;
654: end asg_loc_comp;
655: --
656: -- ----------------------------------------------------------------------------
674: := g_package||'set_and_correct_jurisdiction';
675: l_new_vertex_value_tbl hr_entry.varchar2_table;
676: l_element_entry_id number;
677: begin
678: hr_utility.set_location('Entering:'||l_proc, 10);
679: open csr_ele_entry
680: (
681: p_element_link_id,
682: p_input_value_id_tbl(2),
688: close csr_ele_entry;
689: l_new_vertex_value_tbl(1) := null;
690: l_new_vertex_value_tbl(2) := p_jurisdiction;
691: if p_percentage is not null then
692: hr_utility.set_location(l_proc, 20);
693: l_new_vertex_value_tbl(3) := fnd_number.canonical_to_number(p_percentage);
694: hr_entry_api.update_element_entry
695: (
696: p_dt_update_mode => 'CORRECTION',
700: p_input_value_id_tbl => p_input_value_id_tbl,
701: p_entry_value_tbl => l_new_vertex_value_tbl
702: );
703: else
704: hr_utility.set_location(l_proc, 30);
705: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
706: hr_utility.set_message_token('1',l_proc);
707: hr_utility.raise_error;
708: end if;
701: p_entry_value_tbl => l_new_vertex_value_tbl
702: );
703: else
704: hr_utility.set_location(l_proc, 30);
705: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
706: hr_utility.set_message_token('1',l_proc);
707: hr_utility.raise_error;
708: end if;
709: else
702: );
703: else
704: hr_utility.set_location(l_proc, 30);
705: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
706: hr_utility.set_message_token('1',l_proc);
707: hr_utility.raise_error;
708: end if;
709: else
710: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
703: else
704: hr_utility.set_location(l_proc, 30);
705: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
706: hr_utility.set_message_token('1',l_proc);
707: hr_utility.raise_error;
708: end if;
709: else
710: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
711: hr_utility.set_message_token('2','VERTEX');
706: hr_utility.set_message_token('1',l_proc);
707: hr_utility.raise_error;
708: end if;
709: else
710: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
711: hr_utility.set_message_token('2','VERTEX');
712: hr_utility.raise_error;
713: end if;
714: hr_utility.set_location('Leaving:'||l_proc, 40);
707: hr_utility.raise_error;
708: end if;
709: else
710: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
711: hr_utility.set_message_token('2','VERTEX');
712: hr_utility.raise_error;
713: end if;
714: hr_utility.set_location('Leaving:'||l_proc, 40);
715: end set_and_correct_jurisdiction;
708: end if;
709: else
710: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
711: hr_utility.set_message_token('2','VERTEX');
712: hr_utility.raise_error;
713: end if;
714: hr_utility.set_location('Leaving:'||l_proc, 40);
715: end set_and_correct_jurisdiction;
716: --
710: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
711: hr_utility.set_message_token('2','VERTEX');
712: hr_utility.raise_error;
713: end if;
714: hr_utility.set_location('Leaving:'||l_proc, 40);
715: end set_and_correct_jurisdiction;
716: --
717: -- ----------------------------------------------------------------------------
718: -- |------------------< get_sub_jurisdiction_sum >----------------------------|
736: l_sum number := 0;
737: l_proc varchar2(72)
738: := g_package||'get_sub_jurisdiction_sum';
739: begin
740: hr_utility.set_location('Entering:'||l_proc, 10);
741: l_state := substr(p_jurisdiction,1,2);
742: l_county := nvl(substr(p_jurisdiction,4,3),'000');
743: l_city := nvl(substr(p_jurisdiction,8,4),'0000');
744: l_exclude_jurisdiction := l_state||'-'||l_county||'-'||l_city;
757: p_element_link_id,
758: p_input_value_id_tbl
759: );
760: end loop;
761: hr_utility.set_location('Leaving:'||l_proc, 20);
762: return l_sum;
763: end get_sub_jurisdiction_sum;
764: --
765: -- ----------------------------------------------------------------------------
790: l_cty_sum number :=0;
791: l_cty_cnt_sum number :=0;
792: l_other_jurisdiction_sum number :=0;
793: begin
794: hr_utility.set_location('Entering:'||l_proc, 10);
795: l_jurisdiction := p_state_code||'-'||
796: p_county_code||'-'||
797: p_city_code;
798: if p_city_code <> '0000' then
795: l_jurisdiction := p_state_code||'-'||
796: p_county_code||'-'||
797: p_city_code;
798: if p_city_code <> '0000' then
799: hr_utility.set_location(l_proc, 15);
800: l_curr_cty_db_value := get_curr_jurisdiction_db_value
801: (
802: p_assignment_id,
803: l_jurisdiction,
804: p_effective_date,
805: p_element_link_id,
806: p_input_value_id_tbl
807: );
808: hr_utility.set_location(l_proc, 20);
809: l_curr_cnt_db_value := get_curr_jurisdiction_db_value
810: (
811: p_assignment_id,
812: p_state_code||'-'||
817: );
818: l_new_cnt_db_value := l_curr_cty_db_value - p_scn_percent +
819: l_curr_cnt_db_value;
820: if l_new_cnt_db_value < 0 then
821: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
822: hr_utility.set_message_token('PROCEDURE', l_proc);
823: hr_utility.set_message_token('STEP','25');
824: hr_utility.raise_error;
825: else
818: l_new_cnt_db_value := l_curr_cty_db_value - p_scn_percent +
819: l_curr_cnt_db_value;
820: if l_new_cnt_db_value < 0 then
821: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
822: hr_utility.set_message_token('PROCEDURE', l_proc);
823: hr_utility.set_message_token('STEP','25');
824: hr_utility.raise_error;
825: else
826: hr_utility.set_location(l_proc, 30);
819: l_curr_cnt_db_value;
820: if l_new_cnt_db_value < 0 then
821: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
822: hr_utility.set_message_token('PROCEDURE', l_proc);
823: hr_utility.set_message_token('STEP','25');
824: hr_utility.raise_error;
825: else
826: hr_utility.set_location(l_proc, 30);
827: set_and_correct_jurisdiction
820: if l_new_cnt_db_value < 0 then
821: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
822: hr_utility.set_message_token('PROCEDURE', l_proc);
823: hr_utility.set_message_token('STEP','25');
824: hr_utility.raise_error;
825: else
826: hr_utility.set_location(l_proc, 30);
827: set_and_correct_jurisdiction
828: (
822: hr_utility.set_message_token('PROCEDURE', l_proc);
823: hr_utility.set_message_token('STEP','25');
824: hr_utility.raise_error;
825: else
826: hr_utility.set_location(l_proc, 30);
827: set_and_correct_jurisdiction
828: (
829: p_assignment_id,
830: l_jurisdiction,
832: p_element_link_id,
833: p_input_value_id_tbl,
834: p_effective_date
835: );
836: hr_utility.set_location(l_proc, 35);
837: set_and_correct_jurisdiction
838: (
839: p_assignment_id,
840: p_state_code||'-'||p_county_code||'-0000',
844: p_effective_date
845: );
846: end if;
847: elsif p_county_code <> '000' and p_city_code = '0000' then
848: hr_utility.set_location(l_proc, 40);
849: l_curr_cnt_db_value := get_curr_jurisdiction_db_value
850: (
851: p_assignment_id,
852: l_jurisdiction,
853: p_effective_date,
854: p_element_link_id,
855: p_input_value_id_tbl
856: );
857: hr_utility.set_location(l_proc, 45);
858: l_cty_sum := get_sub_jurisdiction_sum
859: (
860: p_state_code||'-'||p_county_code,
861: p_assignment_id,
862: p_element_link_id,
863: p_input_value_id_tbl,
864: p_effective_date
865: );
866: hr_utility.set_location(l_proc, 50);
867: l_curr_sta_db_value := get_curr_jurisdiction_db_value
868: (
869: p_assignment_id,
870: p_state_code||'-'||'000-0000',
873: p_input_value_id_tbl
874: );
875: l_new_cnt_db_value := p_scn_percent - l_cty_sum;
876: if l_new_cnt_db_value < 0 then
877: hr_utility.set_message(801, 'PAY_72831_TAX_MIN_CNT_PCT');
878: hr_utility.raise_error;
879: else
880: hr_utility.set_location(l_proc, 60);
881: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
874: );
875: l_new_cnt_db_value := p_scn_percent - l_cty_sum;
876: if l_new_cnt_db_value < 0 then
877: hr_utility.set_message(801, 'PAY_72831_TAX_MIN_CNT_PCT');
878: hr_utility.raise_error;
879: else
880: hr_utility.set_location(l_proc, 60);
881: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
882: + l_curr_sta_db_value;
876: if l_new_cnt_db_value < 0 then
877: hr_utility.set_message(801, 'PAY_72831_TAX_MIN_CNT_PCT');
878: hr_utility.raise_error;
879: else
880: hr_utility.set_location(l_proc, 60);
881: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
882: + l_curr_sta_db_value;
883: if l_new_sta_db_value < 0 then
884: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
880: hr_utility.set_location(l_proc, 60);
881: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
882: + l_curr_sta_db_value;
883: if l_new_sta_db_value < 0 then
884: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
885: hr_utility.raise_error;
886: else
887: hr_utility.set_location(l_proc, 70);
888: set_and_correct_jurisdiction
881: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
882: + l_curr_sta_db_value;
883: if l_new_sta_db_value < 0 then
884: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
885: hr_utility.raise_error;
886: else
887: hr_utility.set_location(l_proc, 70);
888: set_and_correct_jurisdiction
889: (
883: if l_new_sta_db_value < 0 then
884: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
885: hr_utility.raise_error;
886: else
887: hr_utility.set_location(l_proc, 70);
888: set_and_correct_jurisdiction
889: (
890: p_assignment_id,
891: l_jurisdiction,
893: p_element_link_id,
894: p_input_value_id_tbl,
895: p_effective_date
896: );
897: hr_utility.set_location(l_proc, 75);
898: set_and_correct_jurisdiction
899: (
900: p_assignment_id,
901: p_state_code||'-'||'000-0000',
907: end if;
908: end if;
909: elsif p_state_code <> '00' and p_county_code = '000' and
910: p_city_code = '0000' then
911: hr_utility.set_location(l_proc, 80);
912: l_cty_cnt_sum := get_sub_jurisdiction_sum
913: (
914: p_state_code,
915: p_assignment_id,
918: p_effective_date
919: );
920: l_new_sta_db_value := p_scn_percent - l_cty_cnt_sum;
921: if l_new_sta_db_value < 0 then
922: hr_utility.set_message(801, 'PAY_72832_TAX_MIN_STA_PCT');
923: hr_utility.raise_error;
924: else
925: hr_utility.set_location(l_proc, 90);
926: for asg_other_jurisdiction in csr_get_sub_jurisdictions
919: );
920: l_new_sta_db_value := p_scn_percent - l_cty_cnt_sum;
921: if l_new_sta_db_value < 0 then
922: hr_utility.set_message(801, 'PAY_72832_TAX_MIN_STA_PCT');
923: hr_utility.raise_error;
924: else
925: hr_utility.set_location(l_proc, 90);
926: for asg_other_jurisdiction in csr_get_sub_jurisdictions
927: (
921: if l_new_sta_db_value < 0 then
922: hr_utility.set_message(801, 'PAY_72832_TAX_MIN_STA_PCT');
923: hr_utility.raise_error;
924: else
925: hr_utility.set_location(l_proc, 90);
926: for asg_other_jurisdiction in csr_get_sub_jurisdictions
927: (
928: p_assignment_id,
929: '',
929: '',
930: p_state_code||'-',
931: p_effective_date
932: ) loop
933: hr_utility.set_location(l_proc, 95);
934: l_other_jurisdiction_sum := l_other_jurisdiction_sum +
935: get_curr_jurisdiction_db_value
936: (
937: p_assignment_id,
942: );
943: end loop;
944: if l_cty_cnt_sum + l_new_sta_db_value +
945: l_other_jurisdiction_sum > 100 then
946: hr_utility.set_message(801, 'PAY_72833_TAX_MAX_STA_PCT');
947: hr_utility.raise_error;
948: else
949: hr_utility.set_location(l_proc, 105);
950: set_and_correct_jurisdiction
943: end loop;
944: if l_cty_cnt_sum + l_new_sta_db_value +
945: l_other_jurisdiction_sum > 100 then
946: hr_utility.set_message(801, 'PAY_72833_TAX_MAX_STA_PCT');
947: hr_utility.raise_error;
948: else
949: hr_utility.set_location(l_proc, 105);
950: set_and_correct_jurisdiction
951: (
945: l_other_jurisdiction_sum > 100 then
946: hr_utility.set_message(801, 'PAY_72833_TAX_MAX_STA_PCT');
947: hr_utility.raise_error;
948: else
949: hr_utility.set_location(l_proc, 105);
950: set_and_correct_jurisdiction
951: (
952: p_assignment_id,
953: l_jurisdiction,
958: );
959: end if;
960: end if;
961: end if;
962: hr_utility.set_location('Leaving:'||l_proc, 110);
963: end calculate_db_percentage;
964:
965: Begin
966: --
963: end calculate_db_percentage;
964:
965: Begin
966: --
967: hr_utility.set_location('Entering:'|| l_proc, 5);
968: --
969: --
970: -- Process Logic
971: --
971: --
972: --
973: -- Check to see if the PAYROLL product is installed.
974: --
975: l_payroll_installed := hr_utility.chk_product_install
976: (p_product =>'Oracle Payroll',
977: p_legislation =>'US');
978: if l_payroll_installed then
979:
976: (p_product =>'Oracle Payroll',
977: p_legislation =>'US');
978: if l_payroll_installed then
979:
980: hr_utility.set_location(l_proc, 15);
981: --
982: -- Find the element entry type id, input_value, and
983: -- input name for and element type with a screen value
984: -- of 'VERTEX'.
984: -- of 'VERTEX'.
985: --
986: Open csr_vertex_element;
987:
988: hr_utility.set_location(l_proc, 20);
989:
990: loop
991: Fetch csr_vertex_element into l_element_type_id,
992: l_input_value_id,
1003: end loop;
1004:
1005: Close csr_vertex_element;
1006:
1007: hr_utility.set_location(l_proc, 25);
1008:
1009: --
1010: -- Check that all of the input value id(s)
1011: -- for the vertex element exist.
1012: --
1013: for i in 1..3 loop
1014: if l_input_value_id_tbl(i) = null or
1015: l_input_value_id_tbl(i) = 0 then
1016: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1017: hr_utility.set_message_token('26','VERTEX');
1018: hr_utility.raise_error;
1019: end if;
1020: end loop;
1013: for i in 1..3 loop
1014: if l_input_value_id_tbl(i) = null or
1015: l_input_value_id_tbl(i) = 0 then
1016: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1017: hr_utility.set_message_token('26','VERTEX');
1018: hr_utility.raise_error;
1019: end if;
1020: end loop;
1021: --
1014: if l_input_value_id_tbl(i) = null or
1015: l_input_value_id_tbl(i) = 0 then
1016: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1017: hr_utility.set_message_token('26','VERTEX');
1018: hr_utility.raise_error;
1019: end if;
1020: end loop;
1021: --
1022: hr_utility.set_location(l_proc, 30);
1018: hr_utility.raise_error;
1019: end if;
1020: end loop;
1021: --
1022: hr_utility.set_location(l_proc, 30);
1023: --
1024: -- assign the parameters to local variables because
1025: -- the element entry procedures expect them to be in
1026: -- out parameters
1043: p_session_date => l_effective_start_date
1044: );
1045: if l_element_link_id is null or
1046: l_element_link_id = 0 then
1047: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1048: hr_utility.set_message_token('31','VERTEX');
1049: hr_utility.raise_error;
1050: end if;
1051: else
1044: );
1045: if l_element_link_id is null or
1046: l_element_link_id = 0 then
1047: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1048: hr_utility.set_message_token('31','VERTEX');
1049: hr_utility.raise_error;
1050: end if;
1051: else
1052: open csr_get_ele_entry_id(
1045: if l_element_link_id is null or
1046: l_element_link_id = 0 then
1047: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1048: hr_utility.set_message_token('31','VERTEX');
1049: hr_utility.raise_error;
1050: end if;
1051: else
1052: open csr_get_ele_entry_id(
1053: p_assignment_id,
1057: fetch csr_get_ele_entry_id into l_element_entry_id;
1058: close csr_get_ele_entry_id;
1059: end if;
1060: --
1061: hr_utility.set_location(l_proc, 35);
1062: --
1063: if p_percentage < 0 or p_percentage > 100 then
1064: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1065: hr_utility.set_message_token('PROCEDURE', l_proc);
1060: --
1061: hr_utility.set_location(l_proc, 35);
1062: --
1063: if p_percentage < 0 or p_percentage > 100 then
1064: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1065: hr_utility.set_message_token('PROCEDURE', l_proc);
1066: hr_utility.set_message_token('STEP','36');
1067: hr_utility.raise_error;
1068: end if;
1061: hr_utility.set_location(l_proc, 35);
1062: --
1063: if p_percentage < 0 or p_percentage > 100 then
1064: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1065: hr_utility.set_message_token('PROCEDURE', l_proc);
1066: hr_utility.set_message_token('STEP','36');
1067: hr_utility.raise_error;
1068: end if;
1069: --
1062: --
1063: if p_percentage < 0 or p_percentage > 100 then
1064: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1065: hr_utility.set_message_token('PROCEDURE', l_proc);
1066: hr_utility.set_message_token('STEP','36');
1067: hr_utility.raise_error;
1068: end if;
1069: --
1070: -- Store screen entry value in the table
1063: if p_percentage < 0 or p_percentage > 100 then
1064: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1065: hr_utility.set_message_token('PROCEDURE', l_proc);
1066: hr_utility.set_message_token('STEP','36');
1067: hr_utility.raise_error;
1068: end if;
1069: --
1070: -- Store screen entry value in the table
1071: --
1082: 'UPDATE_OVERRIDE',
1083: 'INSERT_OVERRIDE',
1084: 'INSERT_OLD') then
1085: --
1086: hr_utility.set_location(l_proc, 40);
1087: --
1088: -- Find the element link id for the current assignment
1089: -- with the derived element entry type id.
1090: --
1106: Fetch csr_ele_entry into l_element_entry_id;
1107: Close csr_ele_entry;
1108: end if;
1109: --
1110: hr_utility.set_location(l_proc, 45);
1111: --
1112: if p_datetrack_mode not in ('UPDATE','INSERT_OVERRIDE', 'UPDATE_OVERRIDE') then
1113: open csr_get_curr_percnt(l_element_entry_id,
1114: l_input_value_id_tbl(3),
1115: p_effective_date);
1116: fetch csr_get_curr_percnt into l_new_vertex_value_tbl(3);
1117: if csr_get_curr_percnt%NOTFOUND then
1118: close csr_get_curr_percnt;
1119: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1120: hr_utility.set_message_token('PROCEDURE', l_proc);
1121: hr_utility.set_message_token('STEP','46');
1122: hr_utility.raise_error;
1123: end if;
1116: fetch csr_get_curr_percnt into l_new_vertex_value_tbl(3);
1117: if csr_get_curr_percnt%NOTFOUND then
1118: close csr_get_curr_percnt;
1119: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1120: hr_utility.set_message_token('PROCEDURE', l_proc);
1121: hr_utility.set_message_token('STEP','46');
1122: hr_utility.raise_error;
1123: end if;
1124: close csr_get_curr_percnt;
1117: if csr_get_curr_percnt%NOTFOUND then
1118: close csr_get_curr_percnt;
1119: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1120: hr_utility.set_message_token('PROCEDURE', l_proc);
1121: hr_utility.set_message_token('STEP','46');
1122: hr_utility.raise_error;
1123: end if;
1124: close csr_get_curr_percnt;
1125: end if;
1118: close csr_get_curr_percnt;
1119: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1120: hr_utility.set_message_token('PROCEDURE', l_proc);
1121: hr_utility.set_message_token('STEP','46');
1122: hr_utility.raise_error;
1123: end if;
1124: close csr_get_curr_percnt;
1125: end if;
1126: End if;
1125: end if;
1126: End if;
1127:
1128: --
1129: hr_utility.set_location(l_proc, 47);
1130: --
1131: --
1132: -- Find defaulting date
1133: --
1145: cur_end_date;
1146:
1147: if csr_assignment_locations%FOUND then
1148: --
1149: hr_utility.set_location(l_proc, 50);
1150: --
1151:
1152: l_location_id := cur_location_id;
1153: l_start_date := cur_start_date;
1155: l_inc := 0;
1156: l_asg_inc := 0;
1157: While csr_assignment_locations%FOUND loop
1158: --
1159: hr_utility.set_location(l_proc, 55);
1160: --
1161: l_asg_inc := l_asg_inc + 1;
1162:
1163: --
1193: l_location_chg_tbl(l_inc).end_date := l_end_date;
1194: end if;
1195: End loop;
1196: --
1197: hr_utility.set_location(l_proc, 60);
1198: --
1199: elsif p_datetrack_mode <> 'ZAP' then
1200: close csr_assignment_locations;
1201:
1198: --
1199: elsif p_datetrack_mode <> 'ZAP' then
1200: close csr_assignment_locations;
1201:
1202: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1203: hr_utility.set_message_token('PROCEDURE', l_proc);
1204: hr_utility.raise_error;
1205: end if;
1206: if csr_assignment_locations%ISOPEN then
1199: elsif p_datetrack_mode <> 'ZAP' then
1200: close csr_assignment_locations;
1201:
1202: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1203: hr_utility.set_message_token('PROCEDURE', l_proc);
1204: hr_utility.raise_error;
1205: end if;
1206: if csr_assignment_locations%ISOPEN then
1207: close csr_assignment_locations;
1200: close csr_assignment_locations;
1201:
1202: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1203: hr_utility.set_message_token('PROCEDURE', l_proc);
1204: hr_utility.raise_error;
1205: end if;
1206: if csr_assignment_locations%ISOPEN then
1207: close csr_assignment_locations;
1208: end if;
1213: --
1214: l_inc := 0;
1215: l_io := FALSE;
1216: --
1217: hr_utility.set_location(l_proc, 65);
1218: --
1219: for l_insert_old_recs in csr_vertex_in_jurisdiction
1220: (
1221: l_element_link_id,
1248: -- a mode of 'UPDATE_INSERT'.
1249: --
1250: If p_datetrack_mode = 'INSERT_OLD' then
1251: --
1252: hr_utility.set_location(l_proc, 70);
1253: --
1254: l_pct_inc := 0;
1255: l_inc := 0;
1256: --
1281: --
1282: If l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1283: or not l_element_rec_tbl.exists(l_pct_inc + 1) then
1284: --
1285: hr_utility.set_location(l_proc, 75);
1286: --
1287: hr_entry_api.update_element_entry
1288: (
1289: p_dt_update_mode => 'UPDATE',
1294: p_entry_value_tbl => l_new_vertex_value_tbl
1295: );
1296: else
1297: --
1298: hr_utility.set_location(l_proc, 80);
1299: --
1300: hr_entry_api.update_element_entry
1301: (
1302: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1320: -- update the effective start date of the first record in
1321: -- pay_element_entries_f and pay_element_entry_values_f.
1322: elsif p_datetrack_mode = 'INSERT_OVERRIDE' then
1323: --
1324: hr_utility.set_location(l_proc, 85);
1325: --
1326: if l_io = TRUE then
1327: l_new_vertex_value_tbl(3) := get_curr_jurisdiction_db_value
1328: (
1336: l_element_rec_tbl(l_pct_inc).effective_end_date >= p_effective_date and
1337: (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1338: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1339: --
1340: hr_utility.set_location(l_proc, 90);
1341: --
1342: hr_entry_api.update_element_entry
1343: (
1344: p_dt_update_mode => 'UPDATE',
1348: p_input_value_id_tbl => l_input_value_id_tbl,
1349: p_entry_value_tbl => l_new_vertex_value_tbl);
1350: else
1351: --
1352: hr_utility.set_location(l_proc, 95);
1353: --
1354: hr_entry_api.update_element_entry
1355: (
1356: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1364: -- Remove trailing records
1365: --
1366: Begin
1367: --
1368: hr_utility.set_location(l_proc, 100);
1369: --
1370: --
1371: -- Remove element entry values first
1372: --
1380: Where element_entry_id = l_element_entry_id
1381: and effective_start_date < p_effective_date;
1382: Exception
1383: when others then
1384: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1385: hr_utility.set_message_token('PROCEDURE', l_proc ||
1386: '- SQLCODE:'|| to_char(sqlcode));
1387: hr_utility.set_message_token('STEP','102');
1388: hr_utility.raise_error;
1381: and effective_start_date < p_effective_date;
1382: Exception
1383: when others then
1384: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1385: hr_utility.set_message_token('PROCEDURE', l_proc ||
1386: '- SQLCODE:'|| to_char(sqlcode));
1387: hr_utility.set_message_token('STEP','102');
1388: hr_utility.raise_error;
1389: End;
1383: when others then
1384: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1385: hr_utility.set_message_token('PROCEDURE', l_proc ||
1386: '- SQLCODE:'|| to_char(sqlcode));
1387: hr_utility.set_message_token('STEP','102');
1388: hr_utility.raise_error;
1389: End;
1390: else
1391: --
1384: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1385: hr_utility.set_message_token('PROCEDURE', l_proc ||
1386: '- SQLCODE:'|| to_char(sqlcode));
1387: hr_utility.set_message_token('STEP','102');
1388: hr_utility.raise_error;
1389: End;
1390: else
1391: --
1392: hr_utility.set_location(l_proc, 105);
1388: hr_utility.raise_error;
1389: End;
1390: else
1391: --
1392: hr_utility.set_location(l_proc, 105);
1393: --
1394: Begin
1395: Update pay_element_entry_values_f
1396: Set effective_start_date = p_effective_date
1403: and effective_start_date = t_effective_start_date;
1404: --
1405: Exception
1406: when others then
1407: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1408: hr_utility.set_message_token('PROCEDURE', l_proc ||
1409: '- SQLCODE:'|| to_char(sqlcode));
1410: hr_utility.set_message_token('STEP','109');
1411: hr_utility.raise_error;
1404: --
1405: Exception
1406: when others then
1407: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1408: hr_utility.set_message_token('PROCEDURE', l_proc ||
1409: '- SQLCODE:'|| to_char(sqlcode));
1410: hr_utility.set_message_token('STEP','109');
1411: hr_utility.raise_error;
1412: End;
1406: when others then
1407: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1408: hr_utility.set_message_token('PROCEDURE', l_proc ||
1409: '- SQLCODE:'|| to_char(sqlcode));
1410: hr_utility.set_message_token('STEP','109');
1411: hr_utility.raise_error;
1412: End;
1413: End if;
1414: /*
1407: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1408: hr_utility.set_message_token('PROCEDURE', l_proc ||
1409: '- SQLCODE:'|| to_char(sqlcode));
1410: hr_utility.set_message_token('STEP','109');
1411: hr_utility.raise_error;
1412: End;
1413: End if;
1414: /*
1415: a. create a percentage record for the new jurisdiction at
1424: is a city.
1425: */
1426: elsif p_datetrack_mode = 'INSERT' then
1427: --
1428: hr_utility.set_location(l_proc ,110);
1429:
1430: If l_defaulting_date is not null then
1431: --
1432: -- Create a percentage record for the new jurisdiction at
1433: -- the defaulting date.
1434: --
1435: l_new_vertex_value_tbl(3) := '0';
1436: --
1437: hr_utility.set_location(l_proc ,115);
1438: --
1439: hr_entry_api.insert_element_entry
1440: (
1441: p_effective_start_date => l_defaulting_date,
1449: p_input_value_id_tbl => l_input_value_id_tbl,
1450: p_entry_value_tbl => l_new_vertex_value_tbl
1451: );
1452: --
1453: hr_utility.set_location(l_proc ,120);
1454: --
1455: -- Find all location changes in the assignment record.
1456: -- Apply all location changes to the new percentage record, by
1457: -- calling hr_entry_element.update_element_entry with MODE = 'UPDATE'.
1457: -- calling hr_entry_element.update_element_entry with MODE = 'UPDATE'.
1458: --
1459: if l_location_chg_tbl.first is not null then
1460: --
1461: hr_utility.set_location(l_proc ,125);
1462: --
1463: l_inc := l_location_chg_tbl.next(l_location_chg_tbl.first);
1464: While l_location_chg_tbl.exists(l_inc) loop
1465: --
1491: p_county_code||'-'||
1492: p_city_code
1493: ) then
1494: --
1495: hr_utility.set_location(l_proc ,125);
1496: --
1497: l_new_vertex_value_tbl(3) := '100';
1498: else
1499: l_new_vertex_value_tbl(3) := '0';
1498: else
1499: l_new_vertex_value_tbl(3) := '0';
1500: end if; --
1501: --
1502: hr_utility.set_location(l_proc ,130);
1503: --
1504: hr_entry_api.update_element_entry
1505: (
1506: p_dt_update_mode => 'CORRECTION',
1510: p_input_value_id_tbl => l_input_value_id_tbl,
1511: p_entry_value_tbl => l_new_vertex_value_tbl
1512: );
1513: else
1514: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1515: hr_utility.set_message_token('PROCEDURE', l_proc);
1516: hr_utility.raise_error;
1517: end if;
1518: end if;
1511: p_entry_value_tbl => l_new_vertex_value_tbl
1512: );
1513: else
1514: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1515: hr_utility.set_message_token('PROCEDURE', l_proc);
1516: hr_utility.raise_error;
1517: end if;
1518: end if;
1519: --
1512: );
1513: else
1514: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1515: hr_utility.set_message_token('PROCEDURE', l_proc);
1516: hr_utility.raise_error;
1517: end if;
1518: end if;
1519: --
1520: /*
1554: at the effective date.
1555: */
1556: elsif p_datetrack_mode = 'CORRECTION' then
1557: --
1558: hr_utility.set_location(l_proc ,140);
1559: --
1560: l_inc := 0;
1561: if l_location_chg_tbl.first is not null then
1562: l_inc := l_location_chg_tbl.first;
1583: l_pct_inc := l_inc;
1584: end if;
1585: end loop;
1586: else
1587: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1588: hr_utility.set_message_token('PROCEDURE', l_proc);
1589: hr_utility.raise_error;
1590: end if;
1591: If l_location_chg_tbl(l_asg_inc).end_date <
1584: end if;
1585: end loop;
1586: else
1587: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1588: hr_utility.set_message_token('PROCEDURE', l_proc);
1589: hr_utility.raise_error;
1590: end if;
1591: If l_location_chg_tbl(l_asg_inc).end_date <
1592: l_element_rec_tbl(l_pct_inc).effective_end_date
1585: end loop;
1586: else
1587: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1588: hr_utility.set_message_token('PROCEDURE', l_proc);
1589: hr_utility.raise_error;
1590: end if;
1591: If l_location_chg_tbl(l_asg_inc).end_date <
1592: l_element_rec_tbl(l_pct_inc).effective_end_date
1593: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1592: l_element_rec_tbl(l_pct_inc).effective_end_date
1593: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1594: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1595: --
1596: hr_utility.set_location(l_proc ,145);
1597: --
1598: hr_entry_api.update_element_entry
1599: (
1600: p_dt_update_mode => 'UPDATE',
1606: );
1607: elsif l_location_chg_tbl(l_asg_inc).end_date <
1608: l_element_rec_tbl(l_pct_inc).effective_end_date then
1609: --
1610: hr_utility.set_location(l_proc ,150);
1611: --
1612: hr_entry_api.update_element_entry
1613: (
1614: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1626: --
1627: -- With new pct values
1628: --
1629: --
1630: hr_utility.set_location(l_proc ,155);
1631: --
1632: hr_entry_api.update_element_entry
1633: (
1634: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1640: );
1641: else
1642: If l_modified_correction = FALSE then
1643: if l_defaulting_date is null then
1644: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
1645: hr_utility.set_message_token('PROCEDURE', l_proc);
1646: hr_utility.raise_error;
1647: end if;
1648:
1641: else
1642: If l_modified_correction = FALSE then
1643: if l_defaulting_date is null then
1644: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
1645: hr_utility.set_message_token('PROCEDURE', l_proc);
1646: hr_utility.raise_error;
1647: end if;
1648:
1649: if asg_loc_comp(
1642: If l_modified_correction = FALSE then
1643: if l_defaulting_date is null then
1644: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
1645: hr_utility.set_message_token('PROCEDURE', l_proc);
1646: hr_utility.raise_error;
1647: end if;
1648:
1649: if asg_loc_comp(
1650: l_asg_tbl ,
1652: p_effective_date,
1653: l_defaulting_date
1654: ) = TRUE and p_calculate_pct = FALSE then
1655: --
1656: hr_utility.set_location(l_proc ,160);
1657: --
1658: hr_entry_api.delete_element_entry
1659: (
1660: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1668: p_effective_date,
1669: l_defaulting_date
1670: ) = TRUE and p_calculate_pct = FALSE then
1671: --
1672: hr_utility.set_location(l_proc ,165);
1673: --
1674: hr_entry_api.delete_element_entry
1675: (
1676: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1680: End if;
1681: --
1682: if p_calculate_pct then
1683: --
1684: hr_utility.set_location(l_proc ,170);
1685: --
1686: calculate_db_percentage
1687: (
1688: p_percentage,
1694: p_effective_date
1695: );
1696: else
1697: --
1698: hr_utility.set_location(l_proc ,175);
1699: --
1700: hr_entry_api.update_element_entry
1701: (
1702: p_dt_update_mode => 'CORRECTION',
1729: with a mode of 'DELETE_NEXT_CHANGE' at the effective date.
1730: */
1731: elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
1732: --
1733: hr_utility.set_location(l_proc ,180);
1734: --
1735: l_inc := 0;
1736: if l_location_chg_tbl.first is not null then
1737: l_inc := l_location_chg_tbl.first;
1758: l_pct_inc := l_inc;
1759: end if;
1760: end loop;
1761: else
1762: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1763: hr_utility.set_message_token('PROCEDURE', l_proc ||
1764: '- SQLCODE:'|| to_char(sqlcode));
1765: hr_utility.set_message_token('STEP','184');
1766: hr_utility.raise_error;
1759: end if;
1760: end loop;
1761: else
1762: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1763: hr_utility.set_message_token('PROCEDURE', l_proc ||
1764: '- SQLCODE:'|| to_char(sqlcode));
1765: hr_utility.set_message_token('STEP','184');
1766: hr_utility.raise_error;
1767: end if;
1761: else
1762: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1763: hr_utility.set_message_token('PROCEDURE', l_proc ||
1764: '- SQLCODE:'|| to_char(sqlcode));
1765: hr_utility.set_message_token('STEP','184');
1766: hr_utility.raise_error;
1767: end if;
1768: If l_location_chg_tbl(l_asg_inc).end_date <
1769: l_element_rec_tbl(l_pct_inc).effective_end_date
1762: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1763: hr_utility.set_message_token('PROCEDURE', l_proc ||
1764: '- SQLCODE:'|| to_char(sqlcode));
1765: hr_utility.set_message_token('STEP','184');
1766: hr_utility.raise_error;
1767: end if;
1768: If l_location_chg_tbl(l_asg_inc).end_date <
1769: l_element_rec_tbl(l_pct_inc).effective_end_date
1770: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1769: l_element_rec_tbl(l_pct_inc).effective_end_date
1770: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1771: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1772: --
1773: hr_utility.set_location(l_proc ,185);
1774: --
1775: hr_entry_api.update_element_entry
1776: (
1777: p_dt_update_mode => 'UPDATE',
1783: );
1784: elsif l_location_chg_tbl(l_asg_inc).end_date <
1785: l_element_rec_tbl(l_pct_inc).effective_end_date then
1786: --
1787: hr_utility.set_location(l_proc ,190);
1788: --
1789: hr_entry_api.update_element_entry
1790: (
1791: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1801: -- 'UPDATE_CHANGE_INSERT' as of the effective date.
1802: --
1803: l_new_vertex_value_tbl(3) := fnd_number.canonical_to_number(p_percentage);
1804: --
1805: hr_utility.set_location(l_proc ,195);
1806: --
1807: hr_entry_api.update_element_entry
1808: (
1809: p_dt_update_mode => p_datetrack_mode,
1819: p_effective_date,
1820: p_effective_date
1821: ) then
1822: --
1823: hr_utility.set_location(l_proc ,200);
1824: --
1825: hr_entry_api.delete_element_entry
1826: (
1827: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1833: a. call hr_entry_api.update_element_entry with a mode of p_datetrack_mode
1834: at the effective date.
1835: */
1836: elsif p_datetrack_mode in ('UPDATE_OVERRIDE', 'UPDATE') then
1837: hr_utility.set_location(l_proc ,205);
1838: --
1839: hr_entry_api.update_element_entry
1840: (
1841: p_dt_update_mode => p_datetrack_mode,
1852: < next percentage record's
1853: */
1854: elsif p_datetrack_mode in ('ZAP', 'DELETE', 'FUTURE_CHANGE') then
1855: --
1856: hr_utility.set_location(l_proc ,210);
1857: --
1858: hr_entry_api.delete_element_entry
1859: (
1860: p_dt_delete_mode => p_datetrack_mode,
1863: );
1864:
1865: elsif p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
1866: --
1867: hr_utility.set_location(l_proc ,215);
1868: --
1869: l_asg_inc := 0;
1870: l_pct_inc := 0;
1871: l_inc := 0;
1910: --
1911: -- Make sure that the start and end dates match the 'VERTEX'
1912: -- Element Entry row(s).
1913: --
1914: hr_utility.set_location(l_proc ,220);
1915: --
1916: l_new_vertex_value_tbl(3) := get_curr_jurisdiction_db_value
1917: (
1918: p_assignment_id,
1925: l_element_rec_tbl(l_pct_inc).effective_end_date
1926: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1927: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1928: --
1929: hr_utility.set_location(l_proc ,225);
1930: --
1931: hr_entry_api.update_element_entry
1932: (
1933: p_dt_update_mode => 'UPDATE',
1939: );
1940: elsif l_location_chg_tbl(l_asg_inc).end_date <
1941: l_element_rec_tbl(l_pct_inc).effective_start_date then
1942: --
1943: hr_utility.set_location(l_proc ,230);
1944: --
1945: hr_entry_api.update_element_entry
1946: (
1947: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1957: --
1958: -- Call hr_entry_api.delete_element_entry with MODE =
1959: -- 'DELETE_NEXT_CHANGE' for any rows found.
1960: --
1961: hr_utility.set_location(l_proc ,235);
1962: --
1963: hr_entry_api.delete_element_entry
1964: (
1965: p_dt_delete_mode => p_datetrack_mode,
1968: );
1969:
1970: end if;
1971: --
1972: hr_utility.set_location(l_proc, 245);
1973: --
1974: -- Set all output arguments
1975: --
1976: p_effective_start_date := l_effective_start_date;
1977: p_effective_end_date := l_effective_end_date;
1978: --
1979: end if; -- if l_payroll_installed
1980: --
1981: hr_utility.set_location(' Leaving:'||l_proc, 250);
1982: --
1983: --
1984: end maintain_tax_percentage;
1985:
2114: l_effective_end_date := trunc(p_effective_end_date);
2115: --
2116: -- Check that Oracle Payroll is installed
2117: --
2118: l_payroll_installed := hr_utility.chk_product_install(
2119: p_product =>'Oracle Payroll',
2120: p_legislation => 'US');
2121: if l_payroll_installed then
2122:
2119: p_product =>'Oracle Payroll',
2120: p_legislation => 'US');
2121: if l_payroll_installed then
2122:
2123: hr_utility.set_location('Entering:'|| l_proc ,10);
2124: --
2125: if p_datetrack_mode = 'INSERT_OVERRIDE' then
2126: open csr_min_fed_tax_date;
2127: fetch csr_min_fed_tax_date into l_defaulting_date;
2126: open csr_min_fed_tax_date;
2127: fetch csr_min_fed_tax_date into l_defaulting_date;
2128: if csr_min_fed_tax_date%notfound then
2129: close csr_min_fed_tax_date;
2130: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2131: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2132: hr_utility.raise_error;
2133: end if;
2134: close csr_min_fed_tax_date;
2127: fetch csr_min_fed_tax_date into l_defaulting_date;
2128: if csr_min_fed_tax_date%notfound then
2129: close csr_min_fed_tax_date;
2130: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2131: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2132: hr_utility.raise_error;
2133: end if;
2134: close csr_min_fed_tax_date;
2135: --
2128: if csr_min_fed_tax_date%notfound then
2129: close csr_min_fed_tax_date;
2130: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2131: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2132: hr_utility.raise_error;
2133: end if;
2134: close csr_min_fed_tax_date;
2135: --
2136: -- Set l_get_old_value_date to the later of l_effective_date and
2156: close csr_fed_detail;
2157: --
2158: -- No Federal tax rule exists for this id
2159: --
2160: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2161: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2162: hr_utility.raise_error;
2163: end if;
2164: close csr_fed_detail;
2157: --
2158: -- No Federal tax rule exists for this id
2159: --
2160: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2161: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2162: hr_utility.raise_error;
2163: end if;
2164: close csr_fed_detail;
2165: --
2158: -- No Federal tax rule exists for this id
2159: --
2160: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2161: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2162: hr_utility.raise_error;
2163: end if;
2164: close csr_fed_detail;
2165: --
2166: -- Check for datetrack modes ZAP and DELETE. These should only be processed
2205: end loop;
2206:
2207: close csr_wc_tax_element;
2208:
2209: hr_utility.set_location('Entering:'|| l_proc,20);
2210:
2211: /* Check that all of the input value id for vertex, exists */
2212:
2213: for i in 1..2 loop
2217: fnd_message.set_name('PAY', 'HR_7713_TAX_ELEMENT_ERROR');
2218: fnd_message.raise_error;
2219: end if;
2220: end loop;
2221: hr_utility.set_location('Entering:'|| l_proc, 30);
2222:
2223: /* Get element link */
2224: l_element_link_id := hr_entry_api.get_link(
2225: P_assignment_id => l_assignment_id,
2229: if l_element_link_id is null or l_element_link_id = 0 then
2230: fnd_message.set_name('PAY', 'HR_7713_TAX_ELEMENT_ERROR');
2231: fnd_message.raise_error;
2232: end if;
2233: hr_utility.set_location('Entering:'|| l_proc, 40);
2234:
2235: /* Store screen entry value in the table */
2236: l_scr_value_table(1) := null;
2237: l_scr_value_table(2) := l_jurisdiction_code;
2244:
2245: if p_datetrack_mode = 'INSERT' then
2246: /* Insert the worker's compensation element entry */
2247:
2248: hr_utility.set_location('Entering:'|| l_proc, 50);
2249: hr_entry_api.insert_element_entry(
2250: P_effective_start_date => l_effective_start_date,
2251: P_effective_end_date => l_effective_end_date,
2252: P_element_entry_id => l_element_entry_id,
2256: P_entry_type => 'E',
2257: P_num_entry_values => 2,
2258: P_input_value_id_tbl => l_inp_value_id_table,
2259: P_entry_value_tbl => l_scr_value_table);
2260: hr_utility.set_location('Entering:'|| l_proc, 80);
2261:
2262: elsif p_datetrack_mode in ('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
2263: 'UPDATE_OVERRIDE', 'DELETE_NEXT_CHANGE',
2264: 'FUTURE_CHANGE', 'INSERT_OVERRIDE', 'INSERT_OLD') then
2318: open csr_get_curr_jurisd(l_element_entry_id, l_inp_value_id_table(2));
2319: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2320: if csr_get_curr_jurisd%notfound then
2321: close csr_get_curr_jurisd;
2322: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2323: hr_utility.set_message_token('PROCEDURE', l_proc);
2324: hr_utility.set_message_token('STEP', '9');
2325: hr_utility.raise_error;
2326: end if;
2319: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2320: if csr_get_curr_jurisd%notfound then
2321: close csr_get_curr_jurisd;
2322: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2323: hr_utility.set_message_token('PROCEDURE', l_proc);
2324: hr_utility.set_message_token('STEP', '9');
2325: hr_utility.raise_error;
2326: end if;
2327: close csr_get_curr_jurisd;
2320: if csr_get_curr_jurisd%notfound then
2321: close csr_get_curr_jurisd;
2322: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2323: hr_utility.set_message_token('PROCEDURE', l_proc);
2324: hr_utility.set_message_token('STEP', '9');
2325: hr_utility.raise_error;
2326: end if;
2327: close csr_get_curr_jurisd;
2328:
2321: close csr_get_curr_jurisd;
2322: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2323: hr_utility.set_message_token('PROCEDURE', l_proc);
2324: hr_utility.set_message_token('STEP', '9');
2325: hr_utility.raise_error;
2326: end if;
2327: close csr_get_curr_jurisd;
2328:
2329: open csr_fed_rule_exists;
2369: open csr_get_curr_jurisd(l_element_entry_id, l_inp_value_id_table(2));
2370: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2371: if csr_get_curr_jurisd%notfound then
2372: close csr_get_curr_jurisd;
2373: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2374: hr_utility.set_message_token('PROCEDURE', l_proc);
2375: hr_utility.set_message_token('STEP', '9');
2376: hr_utility.raise_error;
2377: end if;
2370: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2371: if csr_get_curr_jurisd%notfound then
2372: close csr_get_curr_jurisd;
2373: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2374: hr_utility.set_message_token('PROCEDURE', l_proc);
2375: hr_utility.set_message_token('STEP', '9');
2376: hr_utility.raise_error;
2377: end if;
2378: close csr_get_curr_jurisd;
2371: if csr_get_curr_jurisd%notfound then
2372: close csr_get_curr_jurisd;
2373: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2374: hr_utility.set_message_token('PROCEDURE', l_proc);
2375: hr_utility.set_message_token('STEP', '9');
2376: hr_utility.raise_error;
2377: end if;
2378: close csr_get_curr_jurisd;
2379:
2372: close csr_get_curr_jurisd;
2373: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2374: hr_utility.set_message_token('PROCEDURE', l_proc);
2375: hr_utility.set_message_token('STEP', '9');
2376: hr_utility.raise_error;
2377: end if;
2378: close csr_get_curr_jurisd;
2379:
2380: open csr_fed_rule_exists;
2466: end if;
2467: close csr_fed_rule;
2468: --
2469: if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
2470: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
2471: hr_utility.raise_error;
2472: end if;
2473: --
2474: -- Validate that this routine is called from Assignment code
2467: close csr_fed_rule;
2468: --
2469: if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
2470: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
2471: hr_utility.raise_error;
2472: end if;
2473: --
2474: -- Validate that this routine is called from Assignment code
2475: --
2473: --
2474: -- Validate that this routine is called from Assignment code
2475: --
2476: if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
2477: hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
2478: hr_utility.raise_error;
2479: end if;
2480: --
2481: open csr_state_rules;
2474: -- Validate that this routine is called from Assignment code
2475: --
2476: if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
2477: hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
2478: hr_utility.raise_error;
2479: end if;
2480: --
2481: open csr_state_rules;
2482: loop
2518: ,p_delete_routine => p_delete_routine
2519: );
2520: --
2521: --
2522: hr_utility.set_location(l_proc, 60);
2523: --
2524: -- Set all output arguments
2525: --
2526: p_effective_start_date := l_effective_start_date;
2526: p_effective_start_date := l_effective_start_date;
2527: p_effective_end_date := l_effective_end_date;
2528: p_object_version_number := l_object_version_number;
2529: --
2530: hr_utility.set_location(' Leaving:'||l_proc, 70);
2531: --
2532: exception
2533: --
2534: when l_exit_quietly then
2536: p_effective_start_date := null;
2537: p_effective_end_date := null;
2538: p_object_version_number := null;
2539: --
2540: hr_utility.set_location(' Leaving:'||l_proc, 75);
2541: --
2542: --
2543: end delete_fed_tax_rule;
2544: -- ----------------------------------------------------------------------------
2587: --
2588:
2589: begin
2590: --
2591: hr_utility.set_location('Entering:'|| l_proc, 10);
2592: --
2593: -- Ensure that all the mandatory arguments are not null
2594: --
2595: hr_api.mandatory_arg_error(p_api_name => l_proc,
2604: open csr_asg_id;
2605: fetch csr_asg_id into l_person_id;
2606: if csr_asg_id%notfound then
2607: close csr_asg_id;
2608: hr_utility.set_message(801, 'HR_7702_PDT_VALUE_NOT_FOUND');
2609: hr_utility.raise_error;
2610: end if;
2611: close csr_asg_id;
2612: elsif p_person_id is not null then
2605: fetch csr_asg_id into l_person_id;
2606: if csr_asg_id%notfound then
2607: close csr_asg_id;
2608: hr_utility.set_message(801, 'HR_7702_PDT_VALUE_NOT_FOUND');
2609: hr_utility.raise_error;
2610: end if;
2611: close csr_asg_id;
2612: elsif p_person_id is not null then
2613: open csr_per_id;
2613: open csr_per_id;
2614: fetch csr_per_id into l_temp_char;
2615: if csr_per_id%NOTFOUND then
2616: close csr_per_id;
2617: hr_utility.set_message(801, 'HR_51396_WEB_PERSON_NOT_FND');
2618: hr_utility.raise_error;
2619: end if;
2620: close csr_per_id;
2621: l_person_id := p_person_id;
2614: fetch csr_per_id into l_temp_char;
2615: if csr_per_id%NOTFOUND then
2616: close csr_per_id;
2617: hr_utility.set_message(801, 'HR_51396_WEB_PERSON_NOT_FND');
2618: hr_utility.raise_error;
2619: end if;
2620: close csr_per_id;
2621: l_person_id := p_person_id;
2622: else
2619: end if;
2620: close csr_per_id;
2621: l_person_id := p_person_id;
2622: else
2623: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
2624: hr_utility.set_message_token('ELEMENT_OR_INPUT',
2625: 'Assignment_id or person_id');
2626: hr_utility.raise_error;
2627: end if;
2620: close csr_per_id;
2621: l_person_id := p_person_id;
2622: else
2623: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
2624: hr_utility.set_message_token('ELEMENT_OR_INPUT',
2625: 'Assignment_id or person_id');
2626: hr_utility.raise_error;
2627: end if;
2628: --
2622: else
2623: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
2624: hr_utility.set_message_token('ELEMENT_OR_INPUT',
2625: 'Assignment_id or person_id');
2626: hr_utility.raise_error;
2627: end if;
2628: --
2629: -- Select the codes for the new primary residence state, county and city
2630: --
2642: l_res_ovrd_state_code, l_res_ovrd_county_code,
2643: l_res_ovrd_city_code;
2644: if csr_res_addr_no_eff_dt%NOTFOUND then
2645: close csr_res_addr_no_eff_dt;
2646: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
2647: hr_utility.raise_error;
2648: end if;
2649: close csr_res_addr_no_eff_dt;
2650: else
2643: l_res_ovrd_city_code;
2644: if csr_res_addr_no_eff_dt%NOTFOUND then
2645: close csr_res_addr_no_eff_dt;
2646: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
2647: hr_utility.raise_error;
2648: end if;
2649: close csr_res_addr_no_eff_dt;
2650: else
2651: close csr_res_addr;
2711: --
2712: -- Set all output arguments
2713: --
2714: --
2715: hr_utility.set_location(' Leaving:'||l_proc, 40);
2716: --
2717: end address_change;
2718: -- ----------------------------------------------------------------------------
2719: -- |-----------------------< validate_us_address >-----------------------|
2739: --
2740: l_final_date date; -- final process date
2741:
2742: BEGIN
2743: hr_utility.set_location('Entering:'|| l_proc, 10);
2744: --
2745: if p_primary_flag = 'Y' then
2746: if p_style <> 'US' then
2747: hr_utility.set_location(l_proc, 15);
2743: hr_utility.set_location('Entering:'|| l_proc, 10);
2744: --
2745: if p_primary_flag = 'Y' then
2746: if p_style <> 'US' then
2747: hr_utility.set_location(l_proc, 15);
2748: if Hr_General2.is_person_type(p_person_id,'EX_EMP',p_effective_date) then
2749: --
2750: hr_utility.set_location(l_proc, 20);
2751: --
2746: if p_style <> 'US' then
2747: hr_utility.set_location(l_proc, 15);
2748: if Hr_General2.is_person_type(p_person_id,'EX_EMP',p_effective_date) then
2749: --
2750: hr_utility.set_location(l_proc, 20);
2751: --
2752: open csr_latest_pos;
2753: fetch csr_latest_pos into l_final_date;
2754: if csr_latest_pos%FOUND then
2756: or (l_final_date is not null
2757: and l_final_date >= p_effective_date)
2758: then
2759: close csr_latest_pos;
2760: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2761: hr_utility.raise_error;
2762:
2763: end if;
2764: end if;
2757: and l_final_date >= p_effective_date)
2758: then
2759: close csr_latest_pos;
2760: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2761: hr_utility.raise_error;
2762:
2763: end if;
2764: end if;
2765: close csr_latest_pos;
2763: end if;
2764: end if;
2765: close csr_latest_pos;
2766: elsif Hr_General2.is_person_type(p_person_id,'EMP',p_effective_date) then
2767: hr_utility.set_location(l_proc, 30);
2768: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2769: hr_utility.raise_error;
2770: end if;
2771: end if; -- style
2764: end if;
2765: close csr_latest_pos;
2766: elsif Hr_General2.is_person_type(p_person_id,'EMP',p_effective_date) then
2767: hr_utility.set_location(l_proc, 30);
2768: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2769: hr_utility.raise_error;
2770: end if;
2771: end if; -- style
2772: end if; -- primary flag
2765: close csr_latest_pos;
2766: elsif Hr_General2.is_person_type(p_person_id,'EMP',p_effective_date) then
2767: hr_utility.set_location(l_proc, 30);
2768: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2769: hr_utility.raise_error;
2770: end if;
2771: end if; -- style
2772: end if; -- primary flag
2773: --
2770: end if;
2771: end if; -- style
2772: end if; -- primary flag
2773: --
2774: hr_utility.set_location('Leaving:'|| l_proc, 50);
2775:
2776: END validate_us_address;
2777: -- ----------------------------------------------------------------------------
2778: -- |-----------------------< create_default_tax_rules >-----------------------|
2910: --
2911: --
2912: begin
2913: --
2914: hr_utility.set_location('Entering:'|| l_proc, 10);
2915: --
2916: l_effective_date := trunc(p_effective_date);
2917: --
2918: hr_utility.set_location(l_proc, 20);
2914: hr_utility.set_location('Entering:'|| l_proc, 10);
2915: --
2916: l_effective_date := trunc(p_effective_date);
2917: --
2918: hr_utility.set_location(l_proc, 20);
2919: --
2920: -- First check if geocode has been installed or not. If no geocodes
2921: -- installed then return because there is nothing to be done by this
2922: -- defaulting procedure
2929: open csr_asg_bg_id;
2930: fetch csr_asg_bg_id into l_business_group_id;
2931: if csr_asg_bg_id%NOTFOUND then
2932: close csr_asg_bg_id;
2933: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
2934: hr_utility.raise_error;
2935: end if;
2936: close csr_asg_bg_id;
2937: --
2930: fetch csr_asg_bg_id into l_business_group_id;
2931: if csr_asg_bg_id%NOTFOUND then
2932: close csr_asg_bg_id;
2933: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
2934: hr_utility.raise_error;
2935: end if;
2936: close csr_asg_bg_id;
2937: --
2938: -- Check that no Federal rule already exists for this assignment
2943: if l_defaulting_date is not null then
2944: raise l_exit_quietly;
2945: end if;
2946: --
2947: hr_utility.set_location(l_proc, 30);
2948:
2949: -- Select initial date that the tax defaulting criteria was met.
2950: -- Check when the assignment criteria was met, and when the primary
2951: -- address criteria was met. The defaulting date will be the later
2965: exit when csr_asg_loc_id%notfound;
2966: fetch csr_asg_loc_id into l_tmp_loc_id;
2967: if l_tmp_loc_id <> l_hold_loc_id then
2968: close csr_asg_loc_id;
2969: hr_utility.set_message('801','PAY_52299_TAX_FUT_LOC');
2970: hr_utility.raise_error;
2971: end if;
2972: end loop;
2973: close csr_asg_loc_id;
2966: fetch csr_asg_loc_id into l_tmp_loc_id;
2967: if l_tmp_loc_id <> l_hold_loc_id then
2968: close csr_asg_loc_id;
2969: hr_utility.set_message('801','PAY_52299_TAX_FUT_LOC');
2970: hr_utility.raise_error;
2971: end if;
2972: end loop;
2973: close csr_asg_loc_id;
2974: --
2984: -- if l_asg_min_start_date is null then
2985: -- raise l_exit_quietly;
2986: -- end if;
2987: --
2988: hr_utility.set_location(l_proc, 40);
2989: open csr_adr_defaulting_date;
2990: fetch csr_adr_defaulting_date into l_adr_min_start_date;
2991: close csr_adr_defaulting_date;
2992: if l_adr_min_start_date is null then
3012: l_loc_ovrd_city_code;
3013:
3014: if csr_loc_addr%NOTFOUND then
3015: close csr_loc_addr;
3016: hr_utility.set_message(801, 'HR_51138_TAX_NOT_COMP_LOC_COV');
3017: hr_utility.raise_error;
3018: end if;
3019: close csr_loc_addr;
3020: hr_utility.set_location(l_proc, 50);
3013:
3014: if csr_loc_addr%NOTFOUND then
3015: close csr_loc_addr;
3016: hr_utility.set_message(801, 'HR_51138_TAX_NOT_COMP_LOC_COV');
3017: hr_utility.raise_error;
3018: end if;
3019: close csr_loc_addr;
3020: hr_utility.set_location(l_proc, 50);
3021: --
3016: hr_utility.set_message(801, 'HR_51138_TAX_NOT_COMP_LOC_COV');
3017: hr_utility.raise_error;
3018: end if;
3019: close csr_loc_addr;
3020: hr_utility.set_location(l_proc, 50);
3021: --
3022: -- Derive the jurisdiction codes for the assignment work location.
3023: if l_loc_ovrd_state_code is not null then
3024: l_sui_state_code := l_loc_ovrd_state_code;
3188:
3189: if csr_loc_addr%NOTFOUND then
3190: close csr_loc_addr;
3191: close csr_get_tax_loc;
3192: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3193: hr_utility.raise_error;
3194: end if;
3195:
3196: close csr_loc_addr;
3189: if csr_loc_addr%NOTFOUND then
3190: close csr_loc_addr;
3191: close csr_get_tax_loc;
3192: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3193: hr_utility.raise_error;
3194: end if;
3195:
3196: close csr_loc_addr;
3197:
3220: l_res_ovrd_state_code, l_res_ovrd_county_code,
3221: l_res_ovrd_city_code;
3222: if csr_res_addr%NOTFOUND then
3223: close csr_res_addr;
3224: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
3225: hr_utility.raise_error;
3226: end if;
3227: close csr_res_addr;
3228: --
3221: l_res_ovrd_city_code;
3222: if csr_res_addr%NOTFOUND then
3223: close csr_res_addr;
3224: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
3225: hr_utility.raise_error;
3226: end if;
3227: close csr_res_addr;
3228: --
3229: -- Determine if other tax rules for the residence location should be created
3257: ,p_city_code => l_res_ovrd_city_code
3258: );
3259: end if;
3260: --
3261: hr_utility.set_location(l_proc, 60);
3262: --
3263: -- Set all output arguments
3264: --
3265: p_emp_fed_tax_rule_id := l_emp_fed_tax_rule_id;
3266: p_fed_effective_start_date := l_fed_effective_start_date;
3267: p_fed_effective_end_date := l_fed_effective_end_date;
3268: p_fed_object_version_number := l_fed_object_version_number;
3269: --
3270: hr_utility.set_location(' Leaving:'||l_proc, 70);
3271: --
3272: exception
3273: --
3274: when l_exit_quietly then
3282: p_emp_fed_tax_rule_id := null;
3283: p_fed_effective_start_date := null;
3284: p_fed_effective_end_date := null;
3285: p_fed_object_version_number := null;
3286: hr_utility.set_location(' Leaving:'||l_proc, 75);
3287: --
3288: end create_default_tax_rules;
3289:
3290: -- ----------------------------------------------------------------------------
3362: --
3363: --
3364: begin
3365: --
3366: hr_utility.set_location('Entering:'|| l_proc, 10);
3367: --
3368: l_effective_date := trunc(p_effective_date);
3369: --
3370: hr_utility.set_location(l_proc, 20);
3366: hr_utility.set_location('Entering:'|| l_proc, 10);
3367: --
3368: l_effective_date := trunc(p_effective_date);
3369: --
3370: hr_utility.set_location(l_proc, 20);
3371: --
3372: -- First check if geocode has been installed or not. If no geocodes
3373: -- installed then return because there is nothing to be done by this
3374: -- procedure
3376: raise l_exit_quietly;
3377: end if;
3378: --
3379: if p_assignment_id is null and p_address_id is null then
3380: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
3381: hr_utility.set_message_token('ELEMENT_OR_INPUT',
3382: 'Assignment_id or address_id');
3383: hr_utility.raise_error;
3384: end if;
3377: end if;
3378: --
3379: if p_assignment_id is null and p_address_id is null then
3380: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
3381: hr_utility.set_message_token('ELEMENT_OR_INPUT',
3382: 'Assignment_id or address_id');
3383: hr_utility.raise_error;
3384: end if;
3385: --
3379: if p_assignment_id is null and p_address_id is null then
3380: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
3381: hr_utility.set_message_token('ELEMENT_OR_INPUT',
3382: 'Assignment_id or address_id');
3383: hr_utility.raise_error;
3384: end if;
3385: --
3386: hr_utility.set_location(l_proc, 30);
3387: --
3382: 'Assignment_id or address_id');
3383: hr_utility.raise_error;
3384: end if;
3385: --
3386: hr_utility.set_location(l_proc, 30);
3387: --
3388: --
3389: if p_datetrack_mode NOT IN ('ZAP',
3390: 'DELETE',
3391: 'UPDATE',
3392: 'CORRECTION',
3393: 'UPDATE_OVERRIDE',
3394: 'UPDATE_CHANGE_INSERT') then
3395: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
3396: hr_utility.raise_error;
3397: elsif p_datetrack_mode in ('ZAP', 'DELETE') then
3398: hr_utility.set_location(l_proc, 40);
3399: --
3392: 'CORRECTION',
3393: 'UPDATE_OVERRIDE',
3394: 'UPDATE_CHANGE_INSERT') then
3395: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
3396: hr_utility.raise_error;
3397: elsif p_datetrack_mode in ('ZAP', 'DELETE') then
3398: hr_utility.set_location(l_proc, 40);
3399: --
3400: if p_assignment_id is not null then
3394: 'UPDATE_CHANGE_INSERT') then
3395: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
3396: hr_utility.raise_error;
3397: elsif p_datetrack_mode in ('ZAP', 'DELETE') then
3398: hr_utility.set_location(l_proc, 40);
3399: --
3400: if p_assignment_id is not null then
3401: l_assignment_tbl(1) := p_assignment_id;
3402: else
3411: open csr_asg_id(l_assignment_tbl(l_cnt));
3412: fetch csr_asg_id into l_temp_num;
3413: if csr_asg_id%notfound then
3414: close csr_asg_id;
3415: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3416: hr_utility.raise_error;
3417: end if;
3418: close csr_asg_id;
3419: --
3412: fetch csr_asg_id into l_temp_num;
3413: if csr_asg_id%notfound then
3414: close csr_asg_id;
3415: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3416: hr_utility.raise_error;
3417: end if;
3418: close csr_asg_id;
3419: --
3420: hr_utility.set_location(l_proc, 45);
3416: hr_utility.raise_error;
3417: end if;
3418: close csr_asg_id;
3419: --
3420: hr_utility.set_location(l_proc, 45);
3421: delete_fed_tax_rule(
3422: p_effective_date => l_effective_date
3423: ,p_datetrack_delete_mode => p_datetrack_mode
3424: ,p_assignment_id => l_assignment_tbl(l_cnt)
3430: end loop;
3431: else
3432: if p_assignment_id is not null then
3433: --
3434: hr_utility.set_location(l_proc, 50);
3435: l_assignment_id := p_assignment_id;
3436: --
3437: open csr_asg_id(l_assignment_id);
3438: fetch csr_asg_id into l_temp_num;
3437: open csr_asg_id(l_assignment_id);
3438: fetch csr_asg_id into l_temp_num;
3439: if csr_asg_id%notfound then
3440: close csr_asg_id;
3441: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3442: hr_utility.raise_error;
3443: end if;
3444: close csr_asg_id;
3445: --
3438: fetch csr_asg_id into l_temp_num;
3439: if csr_asg_id%notfound then
3440: close csr_asg_id;
3441: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3442: hr_utility.raise_error;
3443: end if;
3444: close csr_asg_id;
3445: --
3446: open csr_defaulting_date(l_assignment_id);
3467: open csr_defaulting_met(p_assignment_id,l_effective_date);
3468: fetch csr_defaulting_met into l_temp_char;
3469: if csr_defaulting_met%NOTFOUND then
3470: close csr_defaulting_met;
3471: hr_utility.set_message(801,'PAY_52292_TAX_DEF_CRT');
3472: hr_utility.raise_error;
3473: end if;
3474: close csr_defaulting_met;
3475: end if;
3468: fetch csr_defaulting_met into l_temp_char;
3469: if csr_defaulting_met%NOTFOUND then
3470: close csr_defaulting_met;
3471: hr_utility.set_message(801,'PAY_52292_TAX_DEF_CRT');
3472: hr_utility.raise_error;
3473: end if;
3474: close csr_defaulting_met;
3475: end if;
3476:
3498:
3499: if csr_loc_addr%NOTFOUND then
3500: close csr_loc_addr;
3501: close csr_get_tax_loc;
3502: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3503: hr_utility.raise_error;
3504: end if;
3505:
3506: close csr_loc_addr;
3499: if csr_loc_addr%NOTFOUND then
3500: close csr_loc_addr;
3501: close csr_get_tax_loc;
3502: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3503: hr_utility.raise_error;
3504: end if;
3505:
3506: close csr_loc_addr;
3507:
3535:
3536: if l_location_id is not null and
3537: l_effective_date >= l_defaulting_date then
3538: --
3539: hr_utility.set_location(l_proc, 60);
3540: --
3541: location_change(
3542: p_effective_date => l_effective_date
3543: ,p_datetrack_mode => p_datetrack_mode
3546: );
3547:
3548: elsif l_effective_date < l_defaulting_date then
3549: --
3550: hr_utility.set_location(l_proc, 70);
3551: --
3552: move_tax_default_date(
3553: p_effective_date => l_effective_date
3554: ,p_datetrack_mode => p_datetrack_mode
3559: end if; -- assignment id is not null, defaulting date found?
3560:
3561: else -- assignment id is null, so address id must not be null
3562: --
3563: hr_utility.set_location(l_proc, 80);
3564: --
3565: open csr_adr_id(p_address_id);
3566: fetch csr_adr_id into l_person_id, l_adr_primary, l_adr_style;
3567: if csr_adr_id%notfound then
3565: open csr_adr_id(p_address_id);
3566: fetch csr_adr_id into l_person_id, l_adr_primary, l_adr_style;
3567: if csr_adr_id%notfound then
3568: close csr_adr_id;
3569: hr_utility.set_message(801,'HR_51396_WEB_PERSON_NOT_FND');
3570: hr_utility.raise_error;
3571: --
3572: -- # 2858888: validate address is US style
3573: -- Reverting these changes, no longer needed (3406718)
3566: fetch csr_adr_id into l_person_id, l_adr_primary, l_adr_style;
3567: if csr_adr_id%notfound then
3568: close csr_adr_id;
3569: hr_utility.set_message(801,'HR_51396_WEB_PERSON_NOT_FND');
3570: hr_utility.raise_error;
3571: --
3572: -- # 2858888: validate address is US style
3573: -- Reverting these changes, no longer needed (3406718)
3574: --else
3579: --
3580: -- This will loop through each assignment for the given person's
3581: -- address_id
3582: --
3583: hr_utility.set_location(l_proc, 85);
3584: for l_assgn_rec in csr_adr_asg_id(p_address_id) loop
3585: --
3586: open csr_defaulting_date(l_assgn_rec.assignment_id);
3587: fetch csr_defaulting_date into l_defaulting_date;
3588: close csr_defaulting_date;
3589: --
3590: if l_defaulting_date is null then
3591: --
3592: hr_utility.set_location(l_proc, 90);
3593: --
3594: create_default_tax_rules(
3595: p_effective_date => l_effective_date
3596: ,p_assignment_id => l_assgn_rec.assignment_id
3603: else -- federal tax rule found for this assignment
3604: --
3605: -- address id not null, defaulting date found, hire date null
3606: --
3607: hr_utility.set_location(l_proc, 100);
3608: --
3609: address_change(
3610: p_effective_date => l_effective_date
3611: ,p_assignment_id => l_assgn_rec.assignment_id
3615: end loop; -- loop through each assignment for address_id
3616: end if; -- assignment id is not null?
3617: end if; -- datetrack mode is ZAP?
3618: --
3619: hr_utility.set_location(' Leaving:'||l_proc, 120);
3620: --
3621: exception
3622: --
3623: when l_exit_quietly then
3620: --
3621: exception
3622: --
3623: when l_exit_quietly then
3624: hr_utility.set_location(' Leaving:'||l_proc, 125);
3625: --
3626: end maintain_us_employee_taxes;
3627:
3628: -- ----------------------------------------------------------------------------
4181: and fed.effective_start_date = l_csr_tmp_date;
4182: --
4183: begin
4184: --
4185: hr_utility.set_location('Entering:'|| l_proc, 10);
4186: --
4187: -- Ensure that all the mandatory arguments are not null
4188: --
4189: hr_api.mandatory_arg_error(p_api_name => l_proc,
4190: p_argument => 'effective_date',
4191: p_argument_value => p_effective_date);
4192: --
4193: if p_assignment_id is null then
4194: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4195: hr_utility.raise_error;
4196: end if;
4197: --
4198: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4191: p_argument_value => p_effective_date);
4192: --
4193: if p_assignment_id is null then
4194: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4195: hr_utility.raise_error;
4196: end if;
4197: --
4198: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4199: 'UPDATE_OVERRIDE') and p_location_id is null then
4196: end if;
4197: --
4198: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4199: 'UPDATE_OVERRIDE') and p_location_id is null then
4200: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4201: hr_utility.raise_error;
4202: end if;
4203: --
4204: l_effective_date := trunc(p_effective_date);
4197: --
4198: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4199: 'UPDATE_OVERRIDE') and p_location_id is null then
4200: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4201: hr_utility.raise_error;
4202: end if;
4203: --
4204: l_effective_date := trunc(p_effective_date);
4205: --
4202: end if;
4203: --
4204: l_effective_date := trunc(p_effective_date);
4205: --
4206: hr_utility.set_location(l_proc, 20);
4207: --
4208: -- First check if geocode has been installed or not. If no geocodes
4209: -- installed then return because there is nothing to be done by this
4210: -- defaulting procedure
4219: l_chg_effective_start_date,
4220: l_chg_effective_end_date;
4221: if csr_asg_data%notfound then
4222: close csr_asg_data;
4223: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4224: hr_utility.raise_error;
4225: end if;
4226: close csr_asg_data;
4227: --
4220: l_chg_effective_end_date;
4221: if csr_asg_data%notfound then
4222: close csr_asg_data;
4223: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4224: hr_utility.raise_error;
4225: end if;
4226: close csr_asg_data;
4227: --
4228: -- Ensure that the new location id is different from the current location id
4252: open csr_chk_location_id;
4253: fetch csr_chk_location_id into l_temp_char;
4254: if csr_chk_location_id%notfound then
4255: close csr_chk_location_id;
4256: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4257: hr_utility.raise_error;
4258: end if;
4259: close csr_chk_location_id;
4260: l_location_id := p_location_id;
4253: fetch csr_chk_location_id into l_temp_char;
4254: if csr_chk_location_id%notfound then
4255: close csr_chk_location_id;
4256: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4257: hr_utility.raise_error;
4258: end if;
4259: close csr_chk_location_id;
4260: l_location_id := p_location_id;
4261: else
4267: open csr_fed_tax_dates;
4268: fetch csr_fed_tax_dates into l_defaulting_date, l_fed_tax_end_date;
4269: close csr_fed_tax_dates;
4270: if l_chg_effective_start_date < l_defaulting_date then
4271: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4272: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4273: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4274: hr_utility.raise_error;
4275: end if;
4268: fetch csr_fed_tax_dates into l_defaulting_date, l_fed_tax_end_date;
4269: close csr_fed_tax_dates;
4270: if l_chg_effective_start_date < l_defaulting_date then
4271: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4272: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4273: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4274: hr_utility.raise_error;
4275: end if;
4276: --
4269: close csr_fed_tax_dates;
4270: if l_chg_effective_start_date < l_defaulting_date then
4271: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4272: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4273: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4274: hr_utility.raise_error;
4275: end if;
4276: --
4277: hr_utility.set_location(l_proc, 30);
4270: if l_chg_effective_start_date < l_defaulting_date then
4271: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4272: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4273: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4274: hr_utility.raise_error;
4275: end if;
4276: --
4277: hr_utility.set_location(l_proc, 30);
4278: --
4273: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4274: hr_utility.raise_error;
4275: end if;
4276: --
4277: hr_utility.set_location(l_proc, 30);
4278: --
4279: -- Select state, county and city codes for the new work location of the
4280: -- assignment.
4281: --
4283: fetch csr_loc_addr into l_loc_state_code, l_loc_county_code, l_loc_city_code,
4284: l_loc_ovrd_state_code,l_loc_ovrd_county_code,l_loc_ovrd_city_code;
4285: if csr_loc_addr%NOTFOUND then
4286: close csr_loc_addr;
4287: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
4288: hr_utility.raise_error;
4289: end if;
4290: close csr_loc_addr;
4291:
4284: l_loc_ovrd_state_code,l_loc_ovrd_county_code,l_loc_ovrd_city_code;
4285: if csr_loc_addr%NOTFOUND then
4286: close csr_loc_addr;
4287: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
4288: hr_utility.raise_error;
4289: end if;
4290: close csr_loc_addr;
4291:
4292: -- since l_loc_ovrd_state_code defaults to the loc_state_code, we
4293: -- use it to set the sui
4294: l_sui_state_code := l_loc_ovrd_state_code;
4295:
4296: --
4297: hr_utility.set_location(l_proc, 40);
4298: --
4299: --
4300: -- Branch according to p_datetrack_mode here
4301: --
4300: -- Branch according to p_datetrack_mode here
4301: --
4302: if p_datetrack_mode = 'CORRECTION' then
4303: --
4304: hr_utility.set_location(l_proc, 50);
4305: --
4306: -- Select the federal tax record as of the assignment end date.
4307: --
4308: open csr_fed_rec1(p_assignment_id, l_chg_effective_end_date,
4310: fetch csr_fed_rec1 into l_fed_rec;
4311: if csr_fed_rec1%notfound then
4312: -- No federal tax rule exists as of the end of the assignment record!
4313: close csr_fed_rec1;
4314: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4315: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4316: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4317: hr_utility.raise_error;
4318: end if;
4311: if csr_fed_rec1%notfound then
4312: -- No federal tax rule exists as of the end of the assignment record!
4313: close csr_fed_rec1;
4314: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4315: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4316: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4317: hr_utility.raise_error;
4318: end if;
4319: close csr_fed_rec1;
4312: -- No federal tax rule exists as of the end of the assignment record!
4313: close csr_fed_rec1;
4314: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4315: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4316: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4317: hr_utility.raise_error;
4318: end if;
4319: close csr_fed_rec1;
4320: --
4313: close csr_fed_rec1;
4314: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4315: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4316: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4317: hr_utility.raise_error;
4318: end if;
4319: close csr_fed_rec1;
4320: --
4321: -- If the assignment effective end date does not match the federal tax rule
4349: fetch csr_fed_rec1 into l_fed_rec;
4350: if csr_fed_rec1%notfound then
4351: -- No federal tax rule exists as of the start of the assignment record!
4352: close csr_fed_rec1;
4353: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4354: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4355: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4356: hr_utility.raise_error;
4357: end if;
4350: if csr_fed_rec1%notfound then
4351: -- No federal tax rule exists as of the start of the assignment record!
4352: close csr_fed_rec1;
4353: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4354: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4355: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4356: hr_utility.raise_error;
4357: end if;
4358: close csr_fed_rec1;
4351: -- No federal tax rule exists as of the start of the assignment record!
4352: close csr_fed_rec1;
4353: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4354: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4355: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4356: hr_utility.raise_error;
4357: end if;
4358: close csr_fed_rec1;
4359: --
4352: close csr_fed_rec1;
4353: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4354: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4355: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4356: hr_utility.raise_error;
4357: end if;
4358: close csr_fed_rec1;
4359: --
4360: -- Set new sui location in the record structure.
4513:
4514:
4515: elsif p_datetrack_mode = 'UPDATE' then
4516: --
4517: hr_utility.set_location(l_proc, 60);
4518: --
4519: -- Select the federal record as of the assignment start date.
4520: --
4521: open csr_fed_rec1(p_assignment_id, l_chg_effective_start_date,
4523: fetch csr_fed_rec1 into l_fed_rec;
4524: if csr_fed_rec1%notfound then
4525: -- No federal tax rule exists as of the start of the assignment record!
4526: close csr_fed_rec1;
4527: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4528: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4529: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4530: hr_utility.raise_error;
4531: end if;
4524: if csr_fed_rec1%notfound then
4525: -- No federal tax rule exists as of the start of the assignment record!
4526: close csr_fed_rec1;
4527: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4528: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4529: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4530: hr_utility.raise_error;
4531: end if;
4532: close csr_fed_rec1;
4525: -- No federal tax rule exists as of the start of the assignment record!
4526: close csr_fed_rec1;
4527: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4528: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4529: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4530: hr_utility.raise_error;
4531: end if;
4532: close csr_fed_rec1;
4533: --
4526: close csr_fed_rec1;
4527: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4528: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4529: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4530: hr_utility.raise_error;
4531: end if;
4532: close csr_fed_rec1;
4533: --
4534: -- Set new sui location in the record structure.
4686:
4687:
4688: elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
4689: --
4690: hr_utility.set_location(l_proc, 70);
4691: --
4692: -- Select the federal tax record as of the assignment end date.
4693: --
4694: open csr_fed_rec1(p_assignment_id, l_chg_effective_end_date,
4696: fetch csr_fed_rec1 into l_fed_rec;
4697: if csr_fed_rec1%notfound then
4698: -- No federal tax rule exists as of the end of the assignment record!
4699: close csr_fed_rec1;
4700: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4701: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4702: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4703: hr_utility.raise_error;
4704: end if;
4697: if csr_fed_rec1%notfound then
4698: -- No federal tax rule exists as of the end of the assignment record!
4699: close csr_fed_rec1;
4700: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4701: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4702: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4703: hr_utility.raise_error;
4704: end if;
4705: close csr_fed_rec1;
4698: -- No federal tax rule exists as of the end of the assignment record!
4699: close csr_fed_rec1;
4700: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4701: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4702: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4703: hr_utility.raise_error;
4704: end if;
4705: close csr_fed_rec1;
4706: --
4699: close csr_fed_rec1;
4700: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4701: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4702: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4703: hr_utility.raise_error;
4704: end if;
4705: close csr_fed_rec1;
4706: --
4707: -- If the assignment effective end date does not match the federal tax rule
4735: fetch csr_fed_rec1 into l_fed_rec;
4736: if csr_fed_rec1%notfound then
4737: -- No federal tax rule exists as of the start of the assignment record!
4738: close csr_fed_rec1;
4739: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4740: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4741: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4742: hr_utility.raise_error;
4743: end if;
4736: if csr_fed_rec1%notfound then
4737: -- No federal tax rule exists as of the start of the assignment record!
4738: close csr_fed_rec1;
4739: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4740: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4741: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4742: hr_utility.raise_error;
4743: end if;
4744: close csr_fed_rec1;
4737: -- No federal tax rule exists as of the start of the assignment record!
4738: close csr_fed_rec1;
4739: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4740: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4741: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4742: hr_utility.raise_error;
4743: end if;
4744: close csr_fed_rec1;
4745: --
4738: close csr_fed_rec1;
4739: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4740: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4741: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4742: hr_utility.raise_error;
4743: end if;
4744: close csr_fed_rec1;
4745: --
4746: -- Set new sui location in the record structure.
4899:
4900:
4901: elsif p_datetrack_mode = 'UPDATE_OVERRIDE' then
4902: --
4903: hr_utility.set_location(l_proc, 80);
4904: --
4905: -- Select the federal record as of the assignment start date.
4906: --
4907: open csr_fed_rec1(p_assignment_id, l_chg_effective_start_date,
4909: fetch csr_fed_rec1 into l_fed_rec;
4910: if csr_fed_rec1%notfound then
4911: -- No federal tax rule exists as of the start of the assignment record!
4912: close csr_fed_rec1;
4913: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4914: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4915: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4916: hr_utility.raise_error;
4917: end if;
4910: if csr_fed_rec1%notfound then
4911: -- No federal tax rule exists as of the start of the assignment record!
4912: close csr_fed_rec1;
4913: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4914: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4915: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4916: hr_utility.raise_error;
4917: end if;
4918: close csr_fed_rec1;
4911: -- No federal tax rule exists as of the start of the assignment record!
4912: close csr_fed_rec1;
4913: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4914: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4915: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4916: hr_utility.raise_error;
4917: end if;
4918: close csr_fed_rec1;
4919: --
4912: close csr_fed_rec1;
4913: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4914: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4915: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4916: hr_utility.raise_error;
4917: end if;
4918: close csr_fed_rec1;
4919: --
4920: -- Set new sui location in the record structure.
5073: elsif p_datetrack_mode in ('ZAP', 'DELETE',
5074: 'DELETE_NEXT_CHANGE',
5075: 'FUTURE_CHANGE') then
5076: --
5077: hr_utility.set_location(l_proc, 110);
5078: --
5079: -- This process should not be called with these modes. Call
5080: -- Maintain_us_emp_taxes for a ZAP delete.
5081: -- Other delete modes are not allowed.
5078: --
5079: -- This process should not be called with these modes. Call
5080: -- Maintain_us_emp_taxes for a ZAP delete.
5081: -- Other delete modes are not allowed.
5082: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
5083: hr_utility.raise_error;
5084:
5085: else
5086: -- Unknown datetrack mode
5079: -- This process should not be called with these modes. Call
5080: -- Maintain_us_emp_taxes for a ZAP delete.
5081: -- Other delete modes are not allowed.
5082: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
5083: hr_utility.raise_error;
5084:
5085: else
5086: -- Unknown datetrack mode
5087: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5083: hr_utility.raise_error;
5084:
5085: else
5086: -- Unknown datetrack mode
5087: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5088: hr_utility.set_message_token('DT_MODE', p_datetrack_mode);
5089: hr_utility.raise_error;
5090:
5091: end if;
5084:
5085: else
5086: -- Unknown datetrack mode
5087: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5088: hr_utility.set_message_token('DT_MODE', p_datetrack_mode);
5089: hr_utility.raise_error;
5090:
5091: end if;
5092: --
5085: else
5086: -- Unknown datetrack mode
5087: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5088: hr_utility.set_message_token('DT_MODE', p_datetrack_mode);
5089: hr_utility.raise_error;
5090:
5091: end if;
5092: --
5093: --
5188: );
5189:
5190:
5191: --
5192: hr_utility.set_location(' Leaving:'||l_proc, 130);
5193: --
5194: exception
5195: --
5196: -- This is called when location_change runs but the new location is the
5978: --
5979: open csr_check_payroll(p_defaulting_date, p_new_default_date);
5980: fetch csr_check_payroll into l_exists;
5981: if csr_check_payroll%FOUND then
5982: hr_utility.set_location(l_proc,15);
5983: close csr_check_payroll;
5984: hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
5985: hr_utility.raise_error;
5986: end if;
5980: fetch csr_check_payroll into l_exists;
5981: if csr_check_payroll%FOUND then
5982: hr_utility.set_location(l_proc,15);
5983: close csr_check_payroll;
5984: hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
5985: hr_utility.raise_error;
5986: end if;
5987: close csr_check_payroll;
5988: --
5981: if csr_check_payroll%FOUND then
5982: hr_utility.set_location(l_proc,15);
5983: close csr_check_payroll;
5984: hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
5985: hr_utility.raise_error;
5986: end if;
5987: close csr_check_payroll;
5988: --
5989: -- Create a break in the federal tax rule at the new defaulting date, then
6244: --
6245: --
6246: begin -- move_tax_default_date procedure
6247: --
6248: hr_utility.set_location('Entering:'|| l_proc, 10);
6249: --
6250: l_new_hire_date := trunc(p_new_hire_date);
6251: l_effective_date := nvl(l_new_hire_date, trunc(p_effective_date));
6252: --
6249: --
6250: l_new_hire_date := trunc(p_new_hire_date);
6251: l_effective_date := nvl(l_new_hire_date, trunc(p_effective_date));
6252: --
6253: hr_utility.set_location(l_proc, 20);
6254: --
6255: -- First check if geocode has been installed or not. If no geocodes
6256: -- installed then return because there is nothing to be done by this
6257: -- defaulting procedure
6267: --
6268: -- Validate p_assignment_id against per_assignments_f
6269: --
6270: if p_assignment_id is null then
6271: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6272: hr_utility.raise_error;
6273: else
6274: open csr_assignment_id;
6275: fetch csr_assignment_id into l_temp_char;
6268: -- Validate p_assignment_id against per_assignments_f
6269: --
6270: if p_assignment_id is null then
6271: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6272: hr_utility.raise_error;
6273: else
6274: open csr_assignment_id;
6275: fetch csr_assignment_id into l_temp_char;
6276: if csr_assignment_id%notfound then
6274: open csr_assignment_id;
6275: fetch csr_assignment_id into l_temp_char;
6276: if csr_assignment_id%notfound then
6277: close csr_assignment_id;
6278: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6279: hr_utility.raise_error;
6280: end if;
6281: close csr_assignment_id;
6282: end if;
6275: fetch csr_assignment_id into l_temp_char;
6276: if csr_assignment_id%notfound then
6277: close csr_assignment_id;
6278: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6279: hr_utility.raise_error;
6280: end if;
6281: close csr_assignment_id;
6282: end if;
6283:
6287: open csr_asg_data(p_assignment_id, l_effective_date);
6288: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6289: if csr_asg_data%notfound then
6290: close csr_asg_data;
6291: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6292: hr_utility.set_message_token('DATE',l_effective_date);
6293: hr_utility.raise_error;
6294: end if;
6295: close csr_asg_data;
6288: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6289: if csr_asg_data%notfound then
6290: close csr_asg_data;
6291: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6292: hr_utility.set_message_token('DATE',l_effective_date);
6293: hr_utility.raise_error;
6294: end if;
6295: close csr_asg_data;
6296: --
6289: if csr_asg_data%notfound then
6290: close csr_asg_data;
6291: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6292: hr_utility.set_message_token('DATE',l_effective_date);
6293: hr_utility.raise_error;
6294: end if;
6295: close csr_asg_data;
6296: --
6297: -- Identify the new default date as either the new hire date passed in, or
6303: fetch csr_fed_tax_rule into l_defaulting_date, l_fed_eed,
6304: l_emp_fed_tax_rule_id;
6305: if csr_fed_tax_rule%notfound then
6306: close csr_fed_tax_rule;
6307: hr_utility.set_message(801,'PAY_51465_PDT_INVALID_LOC');
6308: hr_utility.raise_error;
6309: end if;
6310: close csr_fed_tax_rule;
6311: --
6304: l_emp_fed_tax_rule_id;
6305: if csr_fed_tax_rule%notfound then
6306: close csr_fed_tax_rule;
6307: hr_utility.set_message(801,'PAY_51465_PDT_INVALID_LOC');
6308: hr_utility.raise_error;
6309: end if;
6310: close csr_fed_tax_rule;
6311: --
6312: -- To pull back the defaulting date, the assignment as of the new default date
6317: open csr_asg_data(p_assignment_id, l_new_default_date);
6318: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6319: if csr_asg_data%notfound then
6320: close csr_asg_data;
6321: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6322: hr_utility.set_message_token('DATE',l_new_default_date);
6323: hr_utility.raise_error;
6324: end if;
6325: close csr_asg_data;
6318: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6319: if csr_asg_data%notfound then
6320: close csr_asg_data;
6321: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6322: hr_utility.set_message_token('DATE',l_new_default_date);
6323: hr_utility.raise_error;
6324: end if;
6325: close csr_asg_data;
6326: --
6319: if csr_asg_data%notfound then
6320: close csr_asg_data;
6321: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6322: hr_utility.set_message_token('DATE',l_new_default_date);
6323: hr_utility.raise_error;
6324: end if;
6325: close csr_asg_data;
6326: --
6327: if l_defaulting_date - 1 not between l_asg_esd and l_asg_eed then
6364: l_loc_city_code, l_loc_ovrd_state_code,
6365: l_loc_ovrd_county_code,l_loc_ovrd_city_code;
6366: if csr_loc_addr%notfound then
6367: close csr_loc_addr;
6368: hr_utility.set_message(801,'HR_51357_POS_LOC_NOT_EXIST');
6369: hr_utility.raise_error;
6370: end if;
6371: close csr_loc_addr;
6372:
6365: l_loc_ovrd_county_code,l_loc_ovrd_city_code;
6366: if csr_loc_addr%notfound then
6367: close csr_loc_addr;
6368: hr_utility.set_message(801,'HR_51357_POS_LOC_NOT_EXIST');
6369: hr_utility.raise_error;
6370: end if;
6371: close csr_loc_addr;
6372:
6373: end if;
7105:
7106:
7107: else -- hire date is null, datetrack mode is invalid
7108: --
7109: hr_utility.set_message(801,'HR_7184_DT_MODE_UNKNOWN');
7110: hr_utility.set_message_token('DT_MODE',p_datetrack_mode);
7111: hr_utility.raise_error;
7112: --
7113: end if; -- hire date is null, branching on datetrack mode
7106:
7107: else -- hire date is null, datetrack mode is invalid
7108: --
7109: hr_utility.set_message(801,'HR_7184_DT_MODE_UNKNOWN');
7110: hr_utility.set_message_token('DT_MODE',p_datetrack_mode);
7111: hr_utility.raise_error;
7112: --
7113: end if; -- hire date is null, branching on datetrack mode
7114: end if; -- hire date is null and new default < old default
7107: else -- hire date is null, datetrack mode is invalid
7108: --
7109: hr_utility.set_message(801,'HR_7184_DT_MODE_UNKNOWN');
7110: hr_utility.set_message_token('DT_MODE',p_datetrack_mode);
7111: hr_utility.raise_error;
7112: --
7113: end if; -- hire date is null, branching on datetrack mode
7114: end if; -- hire date is null and new default < old default
7115: --
7133: ,p_city_code => l_loc_ovrd_city_code
7134: );
7135: end if;
7136: --
7137: hr_utility.set_location(' Leaving:'||l_proc, 130);
7138: --
7139: exception
7140: --
7141: when l_exit_quietly then
7139: exception
7140: --
7141: when l_exit_quietly then
7142: --
7143: hr_utility.set_location(' Leaving:'||l_proc, 135);
7144: --
7145: end move_tax_default_date;
7146:
7147: