[Home] [Help]
550: l_current_percentage number;
551: l_proc varchar2(72)
552: := g_package||'get_curr_jurisdiction_db_value';
553: begin
554: hr_utility.set_location('Entering:'||l_proc, 10);
555: open csr_ele_entry
556: (
557: p_element_link_id,
558: p_input_value_id_tbl(2),
559: p_jurisdiction,
560: p_assignment_id
561: );
562: fetch csr_ele_entry into l_element_entry_id;
563: hr_utility.set_location(l_proc, 20);
564: if csr_ele_entry%FOUND then
565: close csr_ele_entry;
566: open csr_get_curr_percnt
567: (
578: close csr_get_curr_percnt;
579: end if;
580: end if;
581: close csr_ele_entry;
582: hr_utility.set_location('Leaving:'||l_proc, 30);
583: return 0;
584: end get_curr_jurisdiction_db_value;
585: --
586: -- ----------------------------------------------------------------------------
596: l_jurisdiction varchar2(11);
597: l_proc varchar2(72)
598: := g_package||'chk_work_location';
599: begin
600: hr_utility.set_location('Entering:'||l_proc, 10);
601: open csr_get_jurisdiction
602: (
603: p_assignment_id,
604: p_effective_date
611: else
612: close csr_get_jurisdiction;
613: return FALSE;
614: end if;
615: hr_utility.set_location('Leaving:'||l_proc, 20);
616: end chk_work_location;
617: --
618: -- ----------------------------------------------------------------------------
619: -- |----------------------------< asg_loc_comp >------------------------------|
631: l_inc number;
632: l_proc varchar2(72)
633: := g_package||'asg_loc_comp';
634: Begin
635: hr_utility.set_location('Entering:'||l_proc, 10);
636: if t_asg_tbl.first is not null then
637: l_inc := t_asg_tbl.first;
638: while t_asg_tbl.exists(l_inc) loop
639: if l_effective_date >= t_asg_tbl(l_inc).start_date
650: end if;
651: l_inc := t_asg_tbl.next(l_inc);
652: end loop;
653: end if;
654: hr_utility.set_location('Leaving:'||l_proc, 20);
655: return FALSE;
656: end asg_loc_comp;
657: --
658: -- ----------------------------------------------------------------------------
676: := g_package||'set_and_correct_jurisdiction';
677: l_new_vertex_value_tbl hr_entry.varchar2_table;
678: l_element_entry_id number;
679: begin
680: hr_utility.set_location('Entering:'||l_proc, 10);
681: open csr_ele_entry
682: (
683: p_element_link_id,
684: p_input_value_id_tbl(2),
690: close csr_ele_entry;
691: l_new_vertex_value_tbl(1) := null;
692: l_new_vertex_value_tbl(2) := p_jurisdiction;
693: if p_percentage is not null then
694: hr_utility.set_location(l_proc, 20);
695: l_new_vertex_value_tbl(3) := fnd_number.canonical_to_number(p_percentage);
696: hr_entry_api.update_element_entry
697: (
698: p_dt_update_mode => 'CORRECTION',
702: p_input_value_id_tbl => p_input_value_id_tbl,
703: p_entry_value_tbl => l_new_vertex_value_tbl
704: );
705: else
706: hr_utility.set_location(l_proc, 30);
707: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
708: hr_utility.set_message_token('1',l_proc);
709: hr_utility.raise_error;
710: end if;
703: p_entry_value_tbl => l_new_vertex_value_tbl
704: );
705: else
706: hr_utility.set_location(l_proc, 30);
707: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
708: hr_utility.set_message_token('1',l_proc);
709: hr_utility.raise_error;
710: end if;
711: else
704: );
705: else
706: hr_utility.set_location(l_proc, 30);
707: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
708: hr_utility.set_message_token('1',l_proc);
709: hr_utility.raise_error;
710: end if;
711: else
712: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
705: else
706: hr_utility.set_location(l_proc, 30);
707: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
708: hr_utility.set_message_token('1',l_proc);
709: hr_utility.raise_error;
710: end if;
711: else
712: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
713: hr_utility.set_message_token('2','VERTEX');
708: hr_utility.set_message_token('1',l_proc);
709: hr_utility.raise_error;
710: end if;
711: else
712: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
713: hr_utility.set_message_token('2','VERTEX');
714: hr_utility.raise_error;
715: end if;
716: hr_utility.set_location('Leaving:'||l_proc, 40);
709: hr_utility.raise_error;
710: end if;
711: else
712: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
713: hr_utility.set_message_token('2','VERTEX');
714: hr_utility.raise_error;
715: end if;
716: hr_utility.set_location('Leaving:'||l_proc, 40);
717: end set_and_correct_jurisdiction;
710: end if;
711: else
712: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
713: hr_utility.set_message_token('2','VERTEX');
714: hr_utility.raise_error;
715: end if;
716: hr_utility.set_location('Leaving:'||l_proc, 40);
717: end set_and_correct_jurisdiction;
718: --
712: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
713: hr_utility.set_message_token('2','VERTEX');
714: hr_utility.raise_error;
715: end if;
716: hr_utility.set_location('Leaving:'||l_proc, 40);
717: end set_and_correct_jurisdiction;
718: --
719: -- ----------------------------------------------------------------------------
720: -- |------------------< get_sub_jurisdiction_sum >----------------------------|
738: l_sum number := 0;
739: l_proc varchar2(72)
740: := g_package||'get_sub_jurisdiction_sum';
741: begin
742: hr_utility.set_location('Entering:'||l_proc, 10);
743: l_state := substr(p_jurisdiction,1,2);
744: l_county := nvl(substr(p_jurisdiction,4,3),'000');
745: l_city := nvl(substr(p_jurisdiction,8,4),'0000');
746: l_exclude_jurisdiction := l_state||'-'||l_county||'-'||l_city;
759: p_element_link_id,
760: p_input_value_id_tbl
761: );
762: end loop;
763: hr_utility.set_location('Leaving:'||l_proc, 20);
764: return l_sum;
765: end get_sub_jurisdiction_sum;
766: --
767: -- ----------------------------------------------------------------------------
792: l_cty_sum number :=0;
793: l_cty_cnt_sum number :=0;
794: l_other_jurisdiction_sum number :=0;
795: begin
796: hr_utility.set_location('Entering:'||l_proc, 10);
797: l_jurisdiction := p_state_code||'-'||
798: p_county_code||'-'||
799: p_city_code;
800: if p_city_code <> '0000' then
797: l_jurisdiction := p_state_code||'-'||
798: p_county_code||'-'||
799: p_city_code;
800: if p_city_code <> '0000' then
801: hr_utility.set_location(l_proc, 15);
802: l_curr_cty_db_value := get_curr_jurisdiction_db_value
803: (
804: p_assignment_id,
805: l_jurisdiction,
806: p_effective_date,
807: p_element_link_id,
808: p_input_value_id_tbl
809: );
810: hr_utility.set_location(l_proc, 20);
811: l_curr_cnt_db_value := get_curr_jurisdiction_db_value
812: (
813: p_assignment_id,
814: p_state_code||'-'||
819: );
820: l_new_cnt_db_value := l_curr_cty_db_value - p_scn_percent +
821: l_curr_cnt_db_value;
822: if l_new_cnt_db_value < 0 then
823: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
824: hr_utility.set_message_token('PROCEDURE', l_proc);
825: hr_utility.set_message_token('STEP','25');
826: hr_utility.raise_error;
827: else
820: l_new_cnt_db_value := l_curr_cty_db_value - p_scn_percent +
821: l_curr_cnt_db_value;
822: if l_new_cnt_db_value < 0 then
823: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
824: hr_utility.set_message_token('PROCEDURE', l_proc);
825: hr_utility.set_message_token('STEP','25');
826: hr_utility.raise_error;
827: else
828: hr_utility.set_location(l_proc, 30);
821: l_curr_cnt_db_value;
822: if l_new_cnt_db_value < 0 then
823: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
824: hr_utility.set_message_token('PROCEDURE', l_proc);
825: hr_utility.set_message_token('STEP','25');
826: hr_utility.raise_error;
827: else
828: hr_utility.set_location(l_proc, 30);
829: set_and_correct_jurisdiction
822: if l_new_cnt_db_value < 0 then
823: hr_utility.set_message(801, 'PAY_52236_TAX_CITY_PERCENT');
824: hr_utility.set_message_token('PROCEDURE', l_proc);
825: hr_utility.set_message_token('STEP','25');
826: hr_utility.raise_error;
827: else
828: hr_utility.set_location(l_proc, 30);
829: set_and_correct_jurisdiction
830: (
824: hr_utility.set_message_token('PROCEDURE', l_proc);
825: hr_utility.set_message_token('STEP','25');
826: hr_utility.raise_error;
827: else
828: hr_utility.set_location(l_proc, 30);
829: set_and_correct_jurisdiction
830: (
831: p_assignment_id,
832: l_jurisdiction,
834: p_element_link_id,
835: p_input_value_id_tbl,
836: p_effective_date
837: );
838: hr_utility.set_location(l_proc, 35);
839: set_and_correct_jurisdiction
840: (
841: p_assignment_id,
842: p_state_code||'-'||p_county_code||'-0000',
846: p_effective_date
847: );
848: end if;
849: elsif p_county_code <> '000' and p_city_code = '0000' then
850: hr_utility.set_location(l_proc, 40);
851: l_curr_cnt_db_value := get_curr_jurisdiction_db_value
852: (
853: p_assignment_id,
854: l_jurisdiction,
855: p_effective_date,
856: p_element_link_id,
857: p_input_value_id_tbl
858: );
859: hr_utility.set_location(l_proc, 45);
860: l_cty_sum := get_sub_jurisdiction_sum
861: (
862: p_state_code||'-'||p_county_code,
863: p_assignment_id,
864: p_element_link_id,
865: p_input_value_id_tbl,
866: p_effective_date
867: );
868: hr_utility.set_location(l_proc, 50);
869: l_curr_sta_db_value := get_curr_jurisdiction_db_value
870: (
871: p_assignment_id,
872: p_state_code||'-'||'000-0000',
875: p_input_value_id_tbl
876: );
877: l_new_cnt_db_value := p_scn_percent - l_cty_sum;
878: if l_new_cnt_db_value < 0 then
879: hr_utility.set_message(801, 'PAY_72831_TAX_MIN_CNT_PCT');
880: hr_utility.raise_error;
881: else
882: hr_utility.set_location(l_proc, 60);
883: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
876: );
877: l_new_cnt_db_value := p_scn_percent - l_cty_sum;
878: if l_new_cnt_db_value < 0 then
879: hr_utility.set_message(801, 'PAY_72831_TAX_MIN_CNT_PCT');
880: hr_utility.raise_error;
881: else
882: hr_utility.set_location(l_proc, 60);
883: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
884: + l_curr_sta_db_value;
878: if l_new_cnt_db_value < 0 then
879: hr_utility.set_message(801, 'PAY_72831_TAX_MIN_CNT_PCT');
880: hr_utility.raise_error;
881: else
882: hr_utility.set_location(l_proc, 60);
883: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
884: + l_curr_sta_db_value;
885: if l_new_sta_db_value < 0 then
886: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
882: hr_utility.set_location(l_proc, 60);
883: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
884: + l_curr_sta_db_value;
885: if l_new_sta_db_value < 0 then
886: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
887: hr_utility.raise_error;
888: else
889: hr_utility.set_location(l_proc, 70);
890: set_and_correct_jurisdiction
883: l_new_sta_db_value := l_curr_cnt_db_value - l_new_cnt_db_value
884: + l_curr_sta_db_value;
885: if l_new_sta_db_value < 0 then
886: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
887: hr_utility.raise_error;
888: else
889: hr_utility.set_location(l_proc, 70);
890: set_and_correct_jurisdiction
891: (
885: if l_new_sta_db_value < 0 then
886: hr_utility.set_message(801, 'PAY_52237_TAX_COUNTY_PERCENT');
887: hr_utility.raise_error;
888: else
889: hr_utility.set_location(l_proc, 70);
890: set_and_correct_jurisdiction
891: (
892: p_assignment_id,
893: l_jurisdiction,
895: p_element_link_id,
896: p_input_value_id_tbl,
897: p_effective_date
898: );
899: hr_utility.set_location(l_proc, 75);
900: set_and_correct_jurisdiction
901: (
902: p_assignment_id,
903: p_state_code||'-'||'000-0000',
909: end if;
910: end if;
911: elsif p_state_code <> '00' and p_county_code = '000' and
912: p_city_code = '0000' then
913: hr_utility.set_location(l_proc, 80);
914: l_cty_cnt_sum := get_sub_jurisdiction_sum
915: (
916: p_state_code,
917: p_assignment_id,
920: p_effective_date
921: );
922: l_new_sta_db_value := p_scn_percent - l_cty_cnt_sum;
923: if l_new_sta_db_value < 0 then
924: hr_utility.set_message(801, 'PAY_72832_TAX_MIN_STA_PCT');
925: hr_utility.raise_error;
926: else
927: hr_utility.set_location(l_proc, 90);
928: for asg_other_jurisdiction in csr_get_sub_jurisdictions
921: );
922: l_new_sta_db_value := p_scn_percent - l_cty_cnt_sum;
923: if l_new_sta_db_value < 0 then
924: hr_utility.set_message(801, 'PAY_72832_TAX_MIN_STA_PCT');
925: hr_utility.raise_error;
926: else
927: hr_utility.set_location(l_proc, 90);
928: for asg_other_jurisdiction in csr_get_sub_jurisdictions
929: (
923: if l_new_sta_db_value < 0 then
924: hr_utility.set_message(801, 'PAY_72832_TAX_MIN_STA_PCT');
925: hr_utility.raise_error;
926: else
927: hr_utility.set_location(l_proc, 90);
928: for asg_other_jurisdiction in csr_get_sub_jurisdictions
929: (
930: p_assignment_id,
931: '',
931: '',
932: p_state_code||'-',
933: p_effective_date
934: ) loop
935: hr_utility.set_location(l_proc, 95);
936: l_other_jurisdiction_sum := l_other_jurisdiction_sum +
937: get_curr_jurisdiction_db_value
938: (
939: p_assignment_id,
944: );
945: end loop;
946: if l_cty_cnt_sum + l_new_sta_db_value +
947: l_other_jurisdiction_sum > 100 then
948: hr_utility.set_message(801, 'PAY_72833_TAX_MAX_STA_PCT');
949: hr_utility.raise_error;
950: else
951: hr_utility.set_location(l_proc, 105);
952: set_and_correct_jurisdiction
945: end loop;
946: if l_cty_cnt_sum + l_new_sta_db_value +
947: l_other_jurisdiction_sum > 100 then
948: hr_utility.set_message(801, 'PAY_72833_TAX_MAX_STA_PCT');
949: hr_utility.raise_error;
950: else
951: hr_utility.set_location(l_proc, 105);
952: set_and_correct_jurisdiction
953: (
947: l_other_jurisdiction_sum > 100 then
948: hr_utility.set_message(801, 'PAY_72833_TAX_MAX_STA_PCT');
949: hr_utility.raise_error;
950: else
951: hr_utility.set_location(l_proc, 105);
952: set_and_correct_jurisdiction
953: (
954: p_assignment_id,
955: l_jurisdiction,
960: );
961: end if;
962: end if;
963: end if;
964: hr_utility.set_location('Leaving:'||l_proc, 110);
965: end calculate_db_percentage;
966:
967: Begin
968: --
965: end calculate_db_percentage;
966:
967: Begin
968: --
969: hr_utility.set_location('Entering:'|| l_proc, 5);
970: --
971: --
972: -- Process Logic
973: --
973: --
974: --
975: -- Check to see if the PAYROLL product is installed.
976: --
977: l_payroll_installed := hr_utility.chk_product_install
978: (p_product =>'Oracle Payroll',
979: p_legislation =>'US');
980: if l_payroll_installed then
981:
978: (p_product =>'Oracle Payroll',
979: p_legislation =>'US');
980: if l_payroll_installed then
981:
982: hr_utility.set_location(l_proc, 15);
983: --
984: -- Find the element entry type id, input_value, and
985: -- input name for and element type with a screen value
986: -- of 'VERTEX'.
986: -- of 'VERTEX'.
987: --
988: Open csr_vertex_element;
989:
990: hr_utility.set_location(l_proc, 20);
991:
992: loop
993: Fetch csr_vertex_element into l_element_type_id,
994: l_input_value_id,
1005: end loop;
1006:
1007: Close csr_vertex_element;
1008:
1009: hr_utility.set_location(l_proc, 25);
1010:
1011: --
1012: -- Check that all of the input value id(s)
1013: -- for the vertex element exist.
1014: --
1015: for i in 1..3 loop
1016: if l_input_value_id_tbl(i) = null or
1017: l_input_value_id_tbl(i) = 0 then
1018: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1019: hr_utility.set_message_token('26','VERTEX');
1020: hr_utility.raise_error;
1021: end if;
1022: end loop;
1015: for i in 1..3 loop
1016: if l_input_value_id_tbl(i) = null or
1017: l_input_value_id_tbl(i) = 0 then
1018: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1019: hr_utility.set_message_token('26','VERTEX');
1020: hr_utility.raise_error;
1021: end if;
1022: end loop;
1023: --
1016: if l_input_value_id_tbl(i) = null or
1017: l_input_value_id_tbl(i) = 0 then
1018: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1019: hr_utility.set_message_token('26','VERTEX');
1020: hr_utility.raise_error;
1021: end if;
1022: end loop;
1023: --
1024: hr_utility.set_location(l_proc, 30);
1020: hr_utility.raise_error;
1021: end if;
1022: end loop;
1023: --
1024: hr_utility.set_location(l_proc, 30);
1025: --
1026: -- assign the parameters to local variables because
1027: -- the element entry procedures expect them to be in
1028: -- out parameters
1045: p_session_date => l_effective_start_date
1046: );
1047: if l_element_link_id is null or
1048: l_element_link_id = 0 then
1049: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1050: hr_utility.set_message_token('31','VERTEX');
1051: hr_utility.raise_error;
1052: end if;
1053: else
1046: );
1047: if l_element_link_id is null or
1048: l_element_link_id = 0 then
1049: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1050: hr_utility.set_message_token('31','VERTEX');
1051: hr_utility.raise_error;
1052: end if;
1053: else
1054: open csr_get_ele_entry_id(
1047: if l_element_link_id is null or
1048: l_element_link_id = 0 then
1049: hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1050: hr_utility.set_message_token('31','VERTEX');
1051: hr_utility.raise_error;
1052: end if;
1053: else
1054: open csr_get_ele_entry_id(
1055: p_assignment_id,
1059: fetch csr_get_ele_entry_id into l_element_entry_id;
1060: close csr_get_ele_entry_id;
1061: end if;
1062: --
1063: hr_utility.set_location(l_proc, 35);
1064: --
1065: if p_percentage < 0 or p_percentage > 100 then
1066: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1067: hr_utility.set_message_token('PROCEDURE', l_proc);
1062: --
1063: hr_utility.set_location(l_proc, 35);
1064: --
1065: if p_percentage < 0 or p_percentage > 100 then
1066: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1067: hr_utility.set_message_token('PROCEDURE', l_proc);
1068: hr_utility.set_message_token('STEP','36');
1069: hr_utility.raise_error;
1070: end if;
1063: hr_utility.set_location(l_proc, 35);
1064: --
1065: if p_percentage < 0 or p_percentage > 100 then
1066: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1067: hr_utility.set_message_token('PROCEDURE', l_proc);
1068: hr_utility.set_message_token('STEP','36');
1069: hr_utility.raise_error;
1070: end if;
1071: --
1064: --
1065: if p_percentage < 0 or p_percentage > 100 then
1066: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1067: hr_utility.set_message_token('PROCEDURE', l_proc);
1068: hr_utility.set_message_token('STEP','36');
1069: hr_utility.raise_error;
1070: end if;
1071: --
1072: -- Store screen entry value in the table
1065: if p_percentage < 0 or p_percentage > 100 then
1066: hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1067: hr_utility.set_message_token('PROCEDURE', l_proc);
1068: hr_utility.set_message_token('STEP','36');
1069: hr_utility.raise_error;
1070: end if;
1071: --
1072: -- Store screen entry value in the table
1073: --
1084: 'UPDATE_OVERRIDE',
1085: 'INSERT_OVERRIDE',
1086: 'INSERT_OLD') then
1087: --
1088: hr_utility.set_location(l_proc, 40);
1089: --
1090: -- Find the element link id for the current assignment
1091: -- with the derived element entry type id.
1092: --
1108: Fetch csr_ele_entry into l_element_entry_id;
1109: Close csr_ele_entry;
1110: end if;
1111: --
1112: hr_utility.set_location(l_proc, 45);
1113: --
1114: if p_datetrack_mode not in ('UPDATE','INSERT_OVERRIDE', 'UPDATE_OVERRIDE') then
1115: open csr_get_curr_percnt(l_element_entry_id,
1116: l_input_value_id_tbl(3),
1117: p_effective_date);
1118: fetch csr_get_curr_percnt into l_new_vertex_value_tbl(3);
1119: if csr_get_curr_percnt%NOTFOUND then
1120: close csr_get_curr_percnt;
1121: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1122: hr_utility.set_message_token('PROCEDURE', l_proc);
1123: hr_utility.set_message_token('STEP','46');
1124: hr_utility.raise_error;
1125: end if;
1118: fetch csr_get_curr_percnt into l_new_vertex_value_tbl(3);
1119: if csr_get_curr_percnt%NOTFOUND then
1120: close csr_get_curr_percnt;
1121: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1122: hr_utility.set_message_token('PROCEDURE', l_proc);
1123: hr_utility.set_message_token('STEP','46');
1124: hr_utility.raise_error;
1125: end if;
1126: close csr_get_curr_percnt;
1119: if csr_get_curr_percnt%NOTFOUND then
1120: close csr_get_curr_percnt;
1121: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1122: hr_utility.set_message_token('PROCEDURE', l_proc);
1123: hr_utility.set_message_token('STEP','46');
1124: hr_utility.raise_error;
1125: end if;
1126: close csr_get_curr_percnt;
1127: end if;
1120: close csr_get_curr_percnt;
1121: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1122: hr_utility.set_message_token('PROCEDURE', l_proc);
1123: hr_utility.set_message_token('STEP','46');
1124: hr_utility.raise_error;
1125: end if;
1126: close csr_get_curr_percnt;
1127: end if;
1128: End if;
1127: end if;
1128: End if;
1129:
1130: --
1131: hr_utility.set_location(l_proc, 47);
1132: --
1133: --
1134: -- Find defaulting date
1135: --
1147: cur_end_date;
1148:
1149: if csr_assignment_locations%FOUND then
1150: --
1151: hr_utility.set_location(l_proc, 50);
1152: --
1153:
1154: l_location_id := cur_location_id;
1155: l_start_date := cur_start_date;
1157: l_inc := 0;
1158: l_asg_inc := 0;
1159: While csr_assignment_locations%FOUND loop
1160: --
1161: hr_utility.set_location(l_proc, 55);
1162: --
1163: l_asg_inc := l_asg_inc + 1;
1164:
1165: --
1195: l_location_chg_tbl(l_inc).end_date := l_end_date;
1196: end if;
1197: End loop;
1198: --
1199: hr_utility.set_location(l_proc, 60);
1200: --
1201: elsif p_datetrack_mode <> 'ZAP' then
1202: close csr_assignment_locations;
1203:
1200: --
1201: elsif p_datetrack_mode <> 'ZAP' then
1202: close csr_assignment_locations;
1203:
1204: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1205: hr_utility.set_message_token('PROCEDURE', l_proc);
1206: hr_utility.raise_error;
1207: end if;
1208: if csr_assignment_locations%ISOPEN then
1201: elsif p_datetrack_mode <> 'ZAP' then
1202: close csr_assignment_locations;
1203:
1204: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1205: hr_utility.set_message_token('PROCEDURE', l_proc);
1206: hr_utility.raise_error;
1207: end if;
1208: if csr_assignment_locations%ISOPEN then
1209: close csr_assignment_locations;
1202: close csr_assignment_locations;
1203:
1204: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1205: hr_utility.set_message_token('PROCEDURE', l_proc);
1206: hr_utility.raise_error;
1207: end if;
1208: if csr_assignment_locations%ISOPEN then
1209: close csr_assignment_locations;
1210: end if;
1215: --
1216: l_inc := 0;
1217: l_io := FALSE;
1218: --
1219: hr_utility.set_location(l_proc, 65);
1220: --
1221: for l_insert_old_recs in csr_vertex_in_jurisdiction
1222: (
1223: l_element_link_id,
1250: -- a mode of 'UPDATE_INSERT'.
1251: --
1252: If p_datetrack_mode = 'INSERT_OLD' then
1253: --
1254: hr_utility.set_location(l_proc, 70);
1255: --
1256: l_pct_inc := 0;
1257: l_inc := 0;
1258: --
1283: --
1284: If l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1285: or not l_element_rec_tbl.exists(l_pct_inc + 1) then
1286: --
1287: hr_utility.set_location(l_proc, 75);
1288: --
1289: hr_entry_api.update_element_entry
1290: (
1291: p_dt_update_mode => 'UPDATE',
1296: p_entry_value_tbl => l_new_vertex_value_tbl
1297: );
1298: else
1299: --
1300: hr_utility.set_location(l_proc, 80);
1301: --
1302: hr_entry_api.update_element_entry
1303: (
1304: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1322: -- update the effective start date of the first record in
1323: -- pay_element_entries_f and pay_element_entry_values_f.
1324: elsif p_datetrack_mode = 'INSERT_OVERRIDE' then
1325: --
1326: hr_utility.set_location(l_proc, 85);
1327: --
1328: if l_io = TRUE then
1329: l_new_vertex_value_tbl(3) := get_curr_jurisdiction_db_value
1330: (
1338: l_element_rec_tbl(l_pct_inc).effective_end_date >= p_effective_date and
1339: (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1340: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1341: --
1342: hr_utility.set_location(l_proc, 90);
1343: --
1344: hr_entry_api.update_element_entry
1345: (
1346: p_dt_update_mode => 'UPDATE',
1350: p_input_value_id_tbl => l_input_value_id_tbl,
1351: p_entry_value_tbl => l_new_vertex_value_tbl);
1352: else
1353: --
1354: hr_utility.set_location(l_proc, 95);
1355: --
1356: hr_entry_api.update_element_entry
1357: (
1358: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1366: -- Remove trailing records
1367: --
1368: Begin
1369: --
1370: hr_utility.set_location(l_proc, 100);
1371: --
1372: --
1373: -- Remove element entry values first
1374: --
1382: Where element_entry_id = l_element_entry_id
1383: and effective_start_date < p_effective_date;
1384: Exception
1385: when others then
1386: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1387: hr_utility.set_message_token('PROCEDURE', l_proc ||
1388: '- SQLCODE:'|| to_char(sqlcode));
1389: hr_utility.set_message_token('STEP','102');
1390: hr_utility.raise_error;
1383: and effective_start_date < p_effective_date;
1384: Exception
1385: when others then
1386: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1387: hr_utility.set_message_token('PROCEDURE', l_proc ||
1388: '- SQLCODE:'|| to_char(sqlcode));
1389: hr_utility.set_message_token('STEP','102');
1390: hr_utility.raise_error;
1391: End;
1385: when others then
1386: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1387: hr_utility.set_message_token('PROCEDURE', l_proc ||
1388: '- SQLCODE:'|| to_char(sqlcode));
1389: hr_utility.set_message_token('STEP','102');
1390: hr_utility.raise_error;
1391: End;
1392: else
1393: --
1386: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1387: hr_utility.set_message_token('PROCEDURE', l_proc ||
1388: '- SQLCODE:'|| to_char(sqlcode));
1389: hr_utility.set_message_token('STEP','102');
1390: hr_utility.raise_error;
1391: End;
1392: else
1393: --
1394: hr_utility.set_location(l_proc, 105);
1390: hr_utility.raise_error;
1391: End;
1392: else
1393: --
1394: hr_utility.set_location(l_proc, 105);
1395: --
1396: Begin
1397: Update pay_element_entry_values_f
1398: Set effective_start_date = p_effective_date
1405: and effective_start_date = t_effective_start_date;
1406: --
1407: Exception
1408: when others then
1409: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1410: hr_utility.set_message_token('PROCEDURE', l_proc ||
1411: '- SQLCODE:'|| to_char(sqlcode));
1412: hr_utility.set_message_token('STEP','109');
1413: hr_utility.raise_error;
1406: --
1407: Exception
1408: when others then
1409: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1410: hr_utility.set_message_token('PROCEDURE', l_proc ||
1411: '- SQLCODE:'|| to_char(sqlcode));
1412: hr_utility.set_message_token('STEP','109');
1413: hr_utility.raise_error;
1414: End;
1408: when others then
1409: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1410: hr_utility.set_message_token('PROCEDURE', l_proc ||
1411: '- SQLCODE:'|| to_char(sqlcode));
1412: hr_utility.set_message_token('STEP','109');
1413: hr_utility.raise_error;
1414: End;
1415: End if;
1416: /*
1409: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1410: hr_utility.set_message_token('PROCEDURE', l_proc ||
1411: '- SQLCODE:'|| to_char(sqlcode));
1412: hr_utility.set_message_token('STEP','109');
1413: hr_utility.raise_error;
1414: End;
1415: End if;
1416: /*
1417: a. create a percentage record for the new jurisdiction at
1426: is a city.
1427: */
1428: elsif p_datetrack_mode = 'INSERT' then
1429: --
1430: hr_utility.set_location(l_proc ,110);
1431:
1432: If l_defaulting_date is not null then
1433: --
1434: -- Create a percentage record for the new jurisdiction at
1435: -- the defaulting date.
1436: --
1437: l_new_vertex_value_tbl(3) := '0';
1438: --
1439: hr_utility.set_location(l_proc ,115);
1440: --
1441: hr_entry_api.insert_element_entry
1442: (
1443: p_effective_start_date => l_defaulting_date,
1451: p_input_value_id_tbl => l_input_value_id_tbl,
1452: p_entry_value_tbl => l_new_vertex_value_tbl
1453: );
1454: --
1455: hr_utility.set_location(l_proc ,120);
1456: --
1457: -- Find all location changes in the assignment record.
1458: -- Apply all location changes to the new percentage record, by
1459: -- calling hr_entry_element.update_element_entry with MODE = 'UPDATE'.
1459: -- calling hr_entry_element.update_element_entry with MODE = 'UPDATE'.
1460: --
1461: if l_location_chg_tbl.first is not null then
1462: --
1463: hr_utility.set_location(l_proc ,125);
1464: --
1465: l_inc := l_location_chg_tbl.next(l_location_chg_tbl.first);
1466: While l_location_chg_tbl.exists(l_inc) loop
1467: --
1493: p_county_code||'-'||
1494: p_city_code
1495: ) then
1496: --
1497: hr_utility.set_location(l_proc ,125);
1498: --
1499: l_new_vertex_value_tbl(3) := '100';
1500: else
1501: l_new_vertex_value_tbl(3) := '0';
1500: else
1501: l_new_vertex_value_tbl(3) := '0';
1502: end if; --
1503: --
1504: hr_utility.set_location(l_proc ,130);
1505: --
1506: hr_entry_api.update_element_entry
1507: (
1508: p_dt_update_mode => 'CORRECTION',
1512: p_input_value_id_tbl => l_input_value_id_tbl,
1513: p_entry_value_tbl => l_new_vertex_value_tbl
1514: );
1515: else
1516: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1517: hr_utility.set_message_token('PROCEDURE', l_proc);
1518: hr_utility.raise_error;
1519: end if;
1520: end if;
1513: p_entry_value_tbl => l_new_vertex_value_tbl
1514: );
1515: else
1516: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1517: hr_utility.set_message_token('PROCEDURE', l_proc);
1518: hr_utility.raise_error;
1519: end if;
1520: end if;
1521: --
1514: );
1515: else
1516: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1517: hr_utility.set_message_token('PROCEDURE', l_proc);
1518: hr_utility.raise_error;
1519: end if;
1520: end if;
1521: --
1522: /*
1556: at the effective date.
1557: */
1558: elsif p_datetrack_mode = 'CORRECTION' then
1559: --
1560: hr_utility.set_location(l_proc ,140);
1561: --
1562: l_inc := 0;
1563: if l_location_chg_tbl.first is not null then
1564: l_inc := l_location_chg_tbl.first;
1585: l_pct_inc := l_inc;
1586: end if;
1587: end loop;
1588: else
1589: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1590: hr_utility.set_message_token('PROCEDURE', l_proc);
1591: hr_utility.raise_error;
1592: end if;
1593: If l_location_chg_tbl(l_asg_inc).end_date <
1586: end if;
1587: end loop;
1588: else
1589: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1590: hr_utility.set_message_token('PROCEDURE', l_proc);
1591: hr_utility.raise_error;
1592: end if;
1593: If l_location_chg_tbl(l_asg_inc).end_date <
1594: l_element_rec_tbl(l_pct_inc).effective_end_date
1587: end loop;
1588: else
1589: hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
1590: hr_utility.set_message_token('PROCEDURE', l_proc);
1591: hr_utility.raise_error;
1592: end if;
1593: If l_location_chg_tbl(l_asg_inc).end_date <
1594: l_element_rec_tbl(l_pct_inc).effective_end_date
1595: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1594: l_element_rec_tbl(l_pct_inc).effective_end_date
1595: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1596: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1597: --
1598: hr_utility.set_location(l_proc ,145);
1599: --
1600: hr_entry_api.update_element_entry
1601: (
1602: p_dt_update_mode => 'UPDATE',
1608: );
1609: elsif l_location_chg_tbl(l_asg_inc).end_date <
1610: l_element_rec_tbl(l_pct_inc).effective_end_date then
1611: --
1612: hr_utility.set_location(l_proc ,150);
1613: --
1614: hr_entry_api.update_element_entry
1615: (
1616: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1628: --
1629: -- With new pct values
1630: --
1631: --
1632: hr_utility.set_location(l_proc ,155);
1633: --
1634: hr_entry_api.update_element_entry
1635: (
1636: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1642: );
1643: else
1644: If l_modified_correction = FALSE then
1645: if l_defaulting_date is null then
1646: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
1647: hr_utility.set_message_token('PROCEDURE', l_proc);
1648: hr_utility.raise_error;
1649: end if;
1650:
1643: else
1644: If l_modified_correction = FALSE then
1645: if l_defaulting_date is null then
1646: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
1647: hr_utility.set_message_token('PROCEDURE', l_proc);
1648: hr_utility.raise_error;
1649: end if;
1650:
1651: if asg_loc_comp(
1644: If l_modified_correction = FALSE then
1645: if l_defaulting_date is null then
1646: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
1647: hr_utility.set_message_token('PROCEDURE', l_proc);
1648: hr_utility.raise_error;
1649: end if;
1650:
1651: if asg_loc_comp(
1652: l_asg_tbl ,
1654: p_effective_date,
1655: l_defaulting_date
1656: ) = TRUE and p_calculate_pct = FALSE then
1657: --
1658: hr_utility.set_location(l_proc ,160);
1659: --
1660: hr_entry_api.delete_element_entry
1661: (
1662: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1670: p_effective_date,
1671: l_defaulting_date
1672: ) = TRUE and p_calculate_pct = FALSE then
1673: --
1674: hr_utility.set_location(l_proc ,165);
1675: --
1676: hr_entry_api.delete_element_entry
1677: (
1678: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1682: End if;
1683: --
1684: if p_calculate_pct then
1685: --
1686: hr_utility.set_location(l_proc ,170);
1687: --
1688: calculate_db_percentage
1689: (
1690: p_percentage,
1696: p_effective_date
1697: );
1698: else
1699: --
1700: hr_utility.set_location(l_proc ,175);
1701: --
1702: hr_entry_api.update_element_entry
1703: (
1704: p_dt_update_mode => 'CORRECTION',
1731: with a mode of 'DELETE_NEXT_CHANGE' at the effective date.
1732: */
1733: elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
1734: --
1735: hr_utility.set_location(l_proc ,180);
1736: --
1737: l_inc := 0;
1738: if l_location_chg_tbl.first is not null then
1739: l_inc := l_location_chg_tbl.first;
1760: l_pct_inc := l_inc;
1761: end if;
1762: end loop;
1763: else
1764: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE', l_proc ||
1766: '- SQLCODE:'|| to_char(sqlcode));
1767: hr_utility.set_message_token('STEP','184');
1768: hr_utility.raise_error;
1761: end if;
1762: end loop;
1763: else
1764: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE', l_proc ||
1766: '- SQLCODE:'|| to_char(sqlcode));
1767: hr_utility.set_message_token('STEP','184');
1768: hr_utility.raise_error;
1769: end if;
1763: else
1764: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE', l_proc ||
1766: '- SQLCODE:'|| to_char(sqlcode));
1767: hr_utility.set_message_token('STEP','184');
1768: hr_utility.raise_error;
1769: end if;
1770: If l_location_chg_tbl(l_asg_inc).end_date <
1771: l_element_rec_tbl(l_pct_inc).effective_end_date
1764: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1765: hr_utility.set_message_token('PROCEDURE', l_proc ||
1766: '- SQLCODE:'|| to_char(sqlcode));
1767: hr_utility.set_message_token('STEP','184');
1768: hr_utility.raise_error;
1769: end if;
1770: If l_location_chg_tbl(l_asg_inc).end_date <
1771: l_element_rec_tbl(l_pct_inc).effective_end_date
1772: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1771: l_element_rec_tbl(l_pct_inc).effective_end_date
1772: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1773: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1774: --
1775: hr_utility.set_location(l_proc ,185);
1776: --
1777: hr_entry_api.update_element_entry
1778: (
1779: p_dt_update_mode => 'UPDATE',
1785: );
1786: elsif l_location_chg_tbl(l_asg_inc).end_date <
1787: l_element_rec_tbl(l_pct_inc).effective_end_date then
1788: --
1789: hr_utility.set_location(l_proc ,190);
1790: --
1791: hr_entry_api.update_element_entry
1792: (
1793: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1803: -- 'UPDATE_CHANGE_INSERT' as of the effective date.
1804: --
1805: l_new_vertex_value_tbl(3) := fnd_number.canonical_to_number(p_percentage);
1806: --
1807: hr_utility.set_location(l_proc ,195);
1808: --
1809: hr_entry_api.update_element_entry
1810: (
1811: p_dt_update_mode => p_datetrack_mode,
1821: p_effective_date,
1822: p_effective_date
1823: ) then
1824: --
1825: hr_utility.set_location(l_proc ,200);
1826: --
1827: hr_entry_api.delete_element_entry
1828: (
1829: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1835: a. call hr_entry_api.update_element_entry with a mode of p_datetrack_mode
1836: at the effective date.
1837: */
1838: elsif p_datetrack_mode in ('UPDATE_OVERRIDE', 'UPDATE') then
1839: hr_utility.set_location(l_proc ,205);
1840: --
1841: hr_entry_api.update_element_entry
1842: (
1843: p_dt_update_mode => p_datetrack_mode,
1854: < next percentage record's
1855: */
1856: elsif p_datetrack_mode in ('ZAP', 'DELETE', 'FUTURE_CHANGE') then
1857: --
1858: hr_utility.set_location(l_proc ,210);
1859: --
1860: hr_entry_api.delete_element_entry
1861: (
1862: p_dt_delete_mode => p_datetrack_mode,
1865: );
1866:
1867: elsif p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
1868: --
1869: hr_utility.set_location(l_proc ,215);
1870: --
1871: l_asg_inc := 0;
1872: l_pct_inc := 0;
1873: l_inc := 0;
1912: --
1913: -- Make sure that the start and end dates match the 'VERTEX'
1914: -- Element Entry row(s).
1915: --
1916: hr_utility.set_location(l_proc ,220);
1917: --
1918: l_new_vertex_value_tbl(3) := get_curr_jurisdiction_db_value
1919: (
1920: p_assignment_id,
1927: l_element_rec_tbl(l_pct_inc).effective_end_date
1928: and (l_element_rec_tbl(l_pct_inc).effective_end_date = hr_api.g_eot
1929: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1930: --
1931: hr_utility.set_location(l_proc ,225);
1932: --
1933: hr_entry_api.update_element_entry
1934: (
1935: p_dt_update_mode => 'UPDATE',
1941: );
1942: elsif l_location_chg_tbl(l_asg_inc).end_date <
1943: l_element_rec_tbl(l_pct_inc).effective_start_date then
1944: --
1945: hr_utility.set_location(l_proc ,230);
1946: --
1947: hr_entry_api.update_element_entry
1948: (
1949: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1959: --
1960: -- Call hr_entry_api.delete_element_entry with MODE =
1961: -- 'DELETE_NEXT_CHANGE' for any rows found.
1962: --
1963: hr_utility.set_location(l_proc ,235);
1964: --
1965: hr_entry_api.delete_element_entry
1966: (
1967: p_dt_delete_mode => p_datetrack_mode,
1970: );
1971:
1972: end if;
1973: --
1974: hr_utility.set_location(l_proc, 245);
1975: --
1976: -- Set all output arguments
1977: --
1978: p_effective_start_date := l_effective_start_date;
1979: p_effective_end_date := l_effective_end_date;
1980: --
1981: end if; -- if l_payroll_installed
1982: --
1983: hr_utility.set_location(' Leaving:'||l_proc, 250);
1984: --
1985: --
1986: end maintain_tax_percentage;
1987:
2116: l_effective_end_date := trunc(p_effective_end_date);
2117: --
2118: -- Check that Oracle Payroll is installed
2119: --
2120: l_payroll_installed := hr_utility.chk_product_install(
2121: p_product =>'Oracle Payroll',
2122: p_legislation => 'US');
2123: if l_payroll_installed then
2124:
2121: p_product =>'Oracle Payroll',
2122: p_legislation => 'US');
2123: if l_payroll_installed then
2124:
2125: hr_utility.set_location('Entering:'|| l_proc ,10);
2126: --
2127: if p_datetrack_mode = 'INSERT_OVERRIDE' then
2128: open csr_min_fed_tax_date;
2129: fetch csr_min_fed_tax_date into l_defaulting_date;
2128: open csr_min_fed_tax_date;
2129: fetch csr_min_fed_tax_date into l_defaulting_date;
2130: if csr_min_fed_tax_date%notfound then
2131: close csr_min_fed_tax_date;
2132: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2133: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2134: hr_utility.raise_error;
2135: end if;
2136: close csr_min_fed_tax_date;
2129: fetch csr_min_fed_tax_date into l_defaulting_date;
2130: if csr_min_fed_tax_date%notfound then
2131: close csr_min_fed_tax_date;
2132: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2133: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2134: hr_utility.raise_error;
2135: end if;
2136: close csr_min_fed_tax_date;
2137: --
2130: if csr_min_fed_tax_date%notfound then
2131: close csr_min_fed_tax_date;
2132: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2133: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2134: hr_utility.raise_error;
2135: end if;
2136: close csr_min_fed_tax_date;
2137: --
2138: -- Set l_get_old_value_date to the later of l_effective_date and
2158: close csr_fed_detail;
2159: --
2160: -- No Federal tax rule exists for this id
2161: --
2162: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2163: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2164: hr_utility.raise_error;
2165: end if;
2166: close csr_fed_detail;
2159: --
2160: -- No Federal tax rule exists for this id
2161: --
2162: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2163: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2164: hr_utility.raise_error;
2165: end if;
2166: close csr_fed_detail;
2167: --
2160: -- No Federal tax rule exists for this id
2161: --
2162: hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
2163: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
2164: hr_utility.raise_error;
2165: end if;
2166: close csr_fed_detail;
2167: --
2168: -- Check for datetrack modes ZAP and DELETE. These should only be processed
2207: end loop;
2208:
2209: close csr_wc_tax_element;
2210:
2211: hr_utility.set_location('Entering:'|| l_proc,20);
2212:
2213: /* Check that all of the input value id for vertex, exists */
2214:
2215: for i in 1..2 loop
2219: fnd_message.set_name('PAY', 'HR_7713_TAX_ELEMENT_ERROR');
2220: fnd_message.raise_error;
2221: end if;
2222: end loop;
2223: hr_utility.set_location('Entering:'|| l_proc, 30);
2224:
2225: /* Get element link */
2226: l_element_link_id := hr_entry_api.get_link(
2227: P_assignment_id => l_assignment_id,
2231: if l_element_link_id is null or l_element_link_id = 0 then
2232: fnd_message.set_name('PAY', 'HR_7713_TAX_ELEMENT_ERROR');
2233: fnd_message.raise_error;
2234: end if;
2235: hr_utility.set_location('Entering:'|| l_proc, 40);
2236:
2237: /* Store screen entry value in the table */
2238: l_scr_value_table(1) := null;
2239: l_scr_value_table(2) := l_jurisdiction_code;
2246:
2247: if p_datetrack_mode = 'INSERT' then
2248: /* Insert the worker's compensation element entry */
2249:
2250: hr_utility.set_location('Entering:'|| l_proc, 50);
2251: hr_entry_api.insert_element_entry(
2252: P_effective_start_date => l_effective_start_date,
2253: P_effective_end_date => l_effective_end_date,
2254: P_element_entry_id => l_element_entry_id,
2258: P_entry_type => 'E',
2259: P_num_entry_values => 2,
2260: P_input_value_id_tbl => l_inp_value_id_table,
2261: P_entry_value_tbl => l_scr_value_table);
2262: hr_utility.set_location('Entering:'|| l_proc, 80);
2263:
2264: elsif p_datetrack_mode in ('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
2265: 'UPDATE_OVERRIDE', 'DELETE_NEXT_CHANGE',
2266: 'FUTURE_CHANGE', 'INSERT_OVERRIDE', 'INSERT_OLD') then
2320: open csr_get_curr_jurisd(l_element_entry_id, l_inp_value_id_table(2));
2321: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2322: if csr_get_curr_jurisd%notfound then
2323: close csr_get_curr_jurisd;
2324: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2325: hr_utility.set_message_token('PROCEDURE', l_proc);
2326: hr_utility.set_message_token('STEP', '9');
2327: hr_utility.raise_error;
2328: end if;
2321: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2322: if csr_get_curr_jurisd%notfound then
2323: close csr_get_curr_jurisd;
2324: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2325: hr_utility.set_message_token('PROCEDURE', l_proc);
2326: hr_utility.set_message_token('STEP', '9');
2327: hr_utility.raise_error;
2328: end if;
2329: close csr_get_curr_jurisd;
2322: if csr_get_curr_jurisd%notfound then
2323: close csr_get_curr_jurisd;
2324: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2325: hr_utility.set_message_token('PROCEDURE', l_proc);
2326: hr_utility.set_message_token('STEP', '9');
2327: hr_utility.raise_error;
2328: end if;
2329: close csr_get_curr_jurisd;
2330:
2323: close csr_get_curr_jurisd;
2324: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2325: hr_utility.set_message_token('PROCEDURE', l_proc);
2326: hr_utility.set_message_token('STEP', '9');
2327: hr_utility.raise_error;
2328: end if;
2329: close csr_get_curr_jurisd;
2330:
2331: open csr_fed_rule_exists;
2371: open csr_get_curr_jurisd(l_element_entry_id, l_inp_value_id_table(2));
2372: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2373: if csr_get_curr_jurisd%notfound then
2374: close csr_get_curr_jurisd;
2375: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2376: hr_utility.set_message_token('PROCEDURE', l_proc);
2377: hr_utility.set_message_token('STEP', '9');
2378: hr_utility.raise_error;
2379: end if;
2372: fetch csr_get_curr_jurisd into l_scr_value_table(2);
2373: if csr_get_curr_jurisd%notfound then
2374: close csr_get_curr_jurisd;
2375: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2376: hr_utility.set_message_token('PROCEDURE', l_proc);
2377: hr_utility.set_message_token('STEP', '9');
2378: hr_utility.raise_error;
2379: end if;
2380: close csr_get_curr_jurisd;
2373: if csr_get_curr_jurisd%notfound then
2374: close csr_get_curr_jurisd;
2375: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2376: hr_utility.set_message_token('PROCEDURE', l_proc);
2377: hr_utility.set_message_token('STEP', '9');
2378: hr_utility.raise_error;
2379: end if;
2380: close csr_get_curr_jurisd;
2381:
2374: close csr_get_curr_jurisd;
2375: hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2376: hr_utility.set_message_token('PROCEDURE', l_proc);
2377: hr_utility.set_message_token('STEP', '9');
2378: hr_utility.raise_error;
2379: end if;
2380: close csr_get_curr_jurisd;
2381:
2382: open csr_fed_rule_exists;
2468: end if;
2469: close csr_fed_rule;
2470: --
2471: if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
2472: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
2473: hr_utility.raise_error;
2474: end if;
2475: --
2476: -- Validate that this routine is called from Assignment code
2469: close csr_fed_rule;
2470: --
2471: if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
2472: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
2473: hr_utility.raise_error;
2474: end if;
2475: --
2476: -- Validate that this routine is called from Assignment code
2477: --
2475: --
2476: -- Validate that this routine is called from Assignment code
2477: --
2478: if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
2479: hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
2480: hr_utility.raise_error;
2481: end if;
2482: --
2483: open csr_state_rules;
2476: -- Validate that this routine is called from Assignment code
2477: --
2478: if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
2479: hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
2480: hr_utility.raise_error;
2481: end if;
2482: --
2483: open csr_state_rules;
2484: loop
2520: ,p_delete_routine => p_delete_routine
2521: );
2522: --
2523: --
2524: hr_utility.set_location(l_proc, 60);
2525: --
2526: -- Set all output arguments
2527: --
2528: p_effective_start_date := l_effective_start_date;
2528: p_effective_start_date := l_effective_start_date;
2529: p_effective_end_date := l_effective_end_date;
2530: p_object_version_number := l_object_version_number;
2531: --
2532: hr_utility.set_location(' Leaving:'||l_proc, 70);
2533: --
2534: exception
2535: --
2536: when l_exit_quietly then
2538: p_effective_start_date := null;
2539: p_effective_end_date := null;
2540: p_object_version_number := null;
2541: --
2542: hr_utility.set_location(' Leaving:'||l_proc, 75);
2543: --
2544: --
2545: end delete_fed_tax_rule;
2546: -- ----------------------------------------------------------------------------
2589: --
2590:
2591: begin
2592: --
2593: hr_utility.set_location('Entering:'|| l_proc, 10);
2594: --
2595: -- Ensure that all the mandatory arguments are not null
2596: --
2597: hr_api.mandatory_arg_error(p_api_name => l_proc,
2606: open csr_asg_id;
2607: fetch csr_asg_id into l_person_id;
2608: if csr_asg_id%notfound then
2609: close csr_asg_id;
2610: hr_utility.set_message(801, 'HR_7702_PDT_VALUE_NOT_FOUND');
2611: hr_utility.raise_error;
2612: end if;
2613: close csr_asg_id;
2614: elsif p_person_id is not null then
2607: fetch csr_asg_id into l_person_id;
2608: if csr_asg_id%notfound then
2609: close csr_asg_id;
2610: hr_utility.set_message(801, 'HR_7702_PDT_VALUE_NOT_FOUND');
2611: hr_utility.raise_error;
2612: end if;
2613: close csr_asg_id;
2614: elsif p_person_id is not null then
2615: open csr_per_id;
2615: open csr_per_id;
2616: fetch csr_per_id into l_temp_char;
2617: if csr_per_id%NOTFOUND then
2618: close csr_per_id;
2619: hr_utility.set_message(801, 'HR_51396_WEB_PERSON_NOT_FND');
2620: hr_utility.raise_error;
2621: end if;
2622: close csr_per_id;
2623: l_person_id := p_person_id;
2616: fetch csr_per_id into l_temp_char;
2617: if csr_per_id%NOTFOUND then
2618: close csr_per_id;
2619: hr_utility.set_message(801, 'HR_51396_WEB_PERSON_NOT_FND');
2620: hr_utility.raise_error;
2621: end if;
2622: close csr_per_id;
2623: l_person_id := p_person_id;
2624: else
2621: end if;
2622: close csr_per_id;
2623: l_person_id := p_person_id;
2624: else
2625: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
2626: hr_utility.set_message_token('ELEMENT_OR_INPUT',
2627: 'Assignment_id or person_id');
2628: hr_utility.raise_error;
2629: end if;
2622: close csr_per_id;
2623: l_person_id := p_person_id;
2624: else
2625: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
2626: hr_utility.set_message_token('ELEMENT_OR_INPUT',
2627: 'Assignment_id or person_id');
2628: hr_utility.raise_error;
2629: end if;
2630: --
2624: else
2625: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
2626: hr_utility.set_message_token('ELEMENT_OR_INPUT',
2627: 'Assignment_id or person_id');
2628: hr_utility.raise_error;
2629: end if;
2630: --
2631: -- Select the codes for the new primary residence state, county and city
2632: --
2644: l_res_ovrd_state_code, l_res_ovrd_county_code,
2645: l_res_ovrd_city_code;
2646: if csr_res_addr_no_eff_dt%NOTFOUND then
2647: close csr_res_addr_no_eff_dt;
2648: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
2649: hr_utility.raise_error;
2650: end if;
2651: close csr_res_addr_no_eff_dt;
2652: else
2645: l_res_ovrd_city_code;
2646: if csr_res_addr_no_eff_dt%NOTFOUND then
2647: close csr_res_addr_no_eff_dt;
2648: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
2649: hr_utility.raise_error;
2650: end if;
2651: close csr_res_addr_no_eff_dt;
2652: else
2653: close csr_res_addr;
2713: --
2714: -- Set all output arguments
2715: --
2716: --
2717: hr_utility.set_location(' Leaving:'||l_proc, 40);
2718: --
2719: end address_change;
2720: -- ----------------------------------------------------------------------------
2721: -- |-----------------------< validate_us_address >-----------------------|
2741: --
2742: l_final_date date; -- final process date
2743:
2744: BEGIN
2745: hr_utility.set_location('Entering:'|| l_proc, 10);
2746: --
2747: if p_primary_flag = 'Y' then
2748: if p_style <> 'US' then
2749: hr_utility.set_location(l_proc, 15);
2745: hr_utility.set_location('Entering:'|| l_proc, 10);
2746: --
2747: if p_primary_flag = 'Y' then
2748: if p_style <> 'US' then
2749: hr_utility.set_location(l_proc, 15);
2750: if Hr_General2.is_person_type(p_person_id,'EX_EMP',p_effective_date) then
2751: --
2752: hr_utility.set_location(l_proc, 20);
2753: --
2748: if p_style <> 'US' then
2749: hr_utility.set_location(l_proc, 15);
2750: if Hr_General2.is_person_type(p_person_id,'EX_EMP',p_effective_date) then
2751: --
2752: hr_utility.set_location(l_proc, 20);
2753: --
2754: open csr_latest_pos;
2755: fetch csr_latest_pos into l_final_date;
2756: if csr_latest_pos%FOUND then
2758: or (l_final_date is not null
2759: and l_final_date >= p_effective_date)
2760: then
2761: close csr_latest_pos;
2762: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2763: hr_utility.raise_error;
2764:
2765: end if;
2766: end if;
2759: and l_final_date >= p_effective_date)
2760: then
2761: close csr_latest_pos;
2762: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2763: hr_utility.raise_error;
2764:
2765: end if;
2766: end if;
2767: close csr_latest_pos;
2765: end if;
2766: end if;
2767: close csr_latest_pos;
2768: elsif Hr_General2.is_person_type(p_person_id,'EMP',p_effective_date) then
2769: hr_utility.set_location(l_proc, 30);
2770: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2771: hr_utility.raise_error;
2772: end if;
2773: end if; -- style
2766: end if;
2767: close csr_latest_pos;
2768: elsif Hr_General2.is_person_type(p_person_id,'EMP',p_effective_date) then
2769: hr_utility.set_location(l_proc, 30);
2770: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2771: hr_utility.raise_error;
2772: end if;
2773: end if; -- style
2774: end if; -- primary flag
2767: close csr_latest_pos;
2768: elsif Hr_General2.is_person_type(p_person_id,'EMP',p_effective_date) then
2769: hr_utility.set_location(l_proc, 30);
2770: hr_utility.set_message(800, 'HR_51283_ADD_MUST_BE_US_STYLE');
2771: hr_utility.raise_error;
2772: end if;
2773: end if; -- style
2774: end if; -- primary flag
2775: --
2772: end if;
2773: end if; -- style
2774: end if; -- primary flag
2775: --
2776: hr_utility.set_location('Leaving:'|| l_proc, 50);
2777:
2778: END validate_us_address;
2779: -- ----------------------------------------------------------------------------
2780: -- |-----------------------< create_default_tax_rules >-----------------------|
2912: --
2913: --
2914: begin
2915: --
2916: hr_utility.set_location('Entering:'|| l_proc, 10);
2917: --
2918: l_effective_date := trunc(p_effective_date);
2919: --
2920: hr_utility.set_location(l_proc, 20);
2916: hr_utility.set_location('Entering:'|| l_proc, 10);
2917: --
2918: l_effective_date := trunc(p_effective_date);
2919: --
2920: hr_utility.set_location(l_proc, 20);
2921: --
2922: -- First check if geocode has been installed or not. If no geocodes
2923: -- installed then return because there is nothing to be done by this
2924: -- defaulting procedure
2931: open csr_asg_bg_id;
2932: fetch csr_asg_bg_id into l_business_group_id;
2933: if csr_asg_bg_id%NOTFOUND then
2934: close csr_asg_bg_id;
2935: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
2936: hr_utility.raise_error;
2937: end if;
2938: close csr_asg_bg_id;
2939: --
2932: fetch csr_asg_bg_id into l_business_group_id;
2933: if csr_asg_bg_id%NOTFOUND then
2934: close csr_asg_bg_id;
2935: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
2936: hr_utility.raise_error;
2937: end if;
2938: close csr_asg_bg_id;
2939: --
2940: -- Check that no Federal rule already exists for this assignment
2945: if l_defaulting_date is not null then
2946: raise l_exit_quietly;
2947: end if;
2948: --
2949: hr_utility.set_location(l_proc, 30);
2950:
2951: -- Select initial date that the tax defaulting criteria was met.
2952: -- Check when the assignment criteria was met, and when the primary
2953: -- address criteria was met. The defaulting date will be the later
2967: exit when csr_asg_loc_id%notfound;
2968: fetch csr_asg_loc_id into l_tmp_loc_id;
2969: if l_tmp_loc_id <> l_hold_loc_id then
2970: close csr_asg_loc_id;
2971: hr_utility.set_message('801','PAY_52299_TAX_FUT_LOC');
2972: hr_utility.raise_error;
2973: end if;
2974: end loop;
2975: close csr_asg_loc_id;
2968: fetch csr_asg_loc_id into l_tmp_loc_id;
2969: if l_tmp_loc_id <> l_hold_loc_id then
2970: close csr_asg_loc_id;
2971: hr_utility.set_message('801','PAY_52299_TAX_FUT_LOC');
2972: hr_utility.raise_error;
2973: end if;
2974: end loop;
2975: close csr_asg_loc_id;
2976: --
2986: -- if l_asg_min_start_date is null then
2987: -- raise l_exit_quietly;
2988: -- end if;
2989: --
2990: hr_utility.set_location(l_proc, 40);
2991: open csr_adr_defaulting_date;
2992: fetch csr_adr_defaulting_date into l_adr_min_start_date;
2993: close csr_adr_defaulting_date;
2994: if l_adr_min_start_date is null then
3014: l_loc_ovrd_city_code;
3015:
3016: if csr_loc_addr%NOTFOUND then
3017: close csr_loc_addr;
3018: hr_utility.set_message(801, 'HR_51138_TAX_NOT_COMP_LOC_COV');
3019: hr_utility.raise_error;
3020: end if;
3021: close csr_loc_addr;
3022: hr_utility.set_location(l_proc, 50);
3015:
3016: if csr_loc_addr%NOTFOUND then
3017: close csr_loc_addr;
3018: hr_utility.set_message(801, 'HR_51138_TAX_NOT_COMP_LOC_COV');
3019: hr_utility.raise_error;
3020: end if;
3021: close csr_loc_addr;
3022: hr_utility.set_location(l_proc, 50);
3023: --
3018: hr_utility.set_message(801, 'HR_51138_TAX_NOT_COMP_LOC_COV');
3019: hr_utility.raise_error;
3020: end if;
3021: close csr_loc_addr;
3022: hr_utility.set_location(l_proc, 50);
3023: --
3024: -- Derive the jurisdiction codes for the assignment work location.
3025: if l_loc_ovrd_state_code is not null then
3026: l_sui_state_code := l_loc_ovrd_state_code;
3190:
3191: if csr_loc_addr%NOTFOUND then
3192: close csr_loc_addr;
3193: close csr_get_tax_loc;
3194: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3195: hr_utility.raise_error;
3196: end if;
3197:
3198: close csr_loc_addr;
3191: if csr_loc_addr%NOTFOUND then
3192: close csr_loc_addr;
3193: close csr_get_tax_loc;
3194: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3195: hr_utility.raise_error;
3196: end if;
3197:
3198: close csr_loc_addr;
3199:
3222: l_res_ovrd_state_code, l_res_ovrd_county_code,
3223: l_res_ovrd_city_code;
3224: if csr_res_addr%NOTFOUND then
3225: close csr_res_addr;
3226: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
3227: hr_utility.raise_error;
3228: end if;
3229: close csr_res_addr;
3230: --
3223: l_res_ovrd_city_code;
3224: if csr_res_addr%NOTFOUND then
3225: close csr_res_addr;
3226: hr_utility.set_message(801, 'HR_7144_PER_NO_PRIM_ADD');
3227: hr_utility.raise_error;
3228: end if;
3229: close csr_res_addr;
3230: --
3231: -- Determine if other tax rules for the residence location should be created
3259: ,p_city_code => l_res_ovrd_city_code
3260: );
3261: end if;
3262: --
3263: hr_utility.set_location(l_proc, 60);
3264: --
3265: -- Set all output arguments
3266: --
3267: p_emp_fed_tax_rule_id := l_emp_fed_tax_rule_id;
3268: p_fed_effective_start_date := l_fed_effective_start_date;
3269: p_fed_effective_end_date := l_fed_effective_end_date;
3270: p_fed_object_version_number := l_fed_object_version_number;
3271: --
3272: hr_utility.set_location(' Leaving:'||l_proc, 70);
3273: --
3274: exception
3275: --
3276: when l_exit_quietly then
3284: p_emp_fed_tax_rule_id := null;
3285: p_fed_effective_start_date := null;
3286: p_fed_effective_end_date := null;
3287: p_fed_object_version_number := null;
3288: hr_utility.set_location(' Leaving:'||l_proc, 75);
3289: --
3290: end create_default_tax_rules;
3291:
3292: -- ----------------------------------------------------------------------------
3401:
3402: --
3403: begin
3404: --
3405: hr_utility.set_location('Entering:'|| l_proc, 10);
3406: --
3407: l_effective_date := trunc(p_effective_date);
3408: --
3409: hr_utility.set_location(l_proc, 20);
3405: hr_utility.set_location('Entering:'|| l_proc, 10);
3406: --
3407: l_effective_date := trunc(p_effective_date);
3408: --
3409: hr_utility.set_location(l_proc, 20);
3410: --
3411: -- First check if geocode has been installed or not. If no geocodes
3412: -- installed then return because there is nothing to be done by this
3413: -- procedure
3415: raise l_exit_quietly;
3416: end if;
3417: --
3418: if p_assignment_id is null and p_address_id is null then
3419: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
3420: hr_utility.set_message_token('ELEMENT_OR_INPUT',
3421: 'Assignment_id or address_id');
3422: hr_utility.raise_error;
3423: end if;
3416: end if;
3417: --
3418: if p_assignment_id is null and p_address_id is null then
3419: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
3420: hr_utility.set_message_token('ELEMENT_OR_INPUT',
3421: 'Assignment_id or address_id');
3422: hr_utility.raise_error;
3423: end if;
3424: --
3418: if p_assignment_id is null and p_address_id is null then
3419: hr_utility.set_message(801,'HR_6480_FF_DEF_RULE');
3420: hr_utility.set_message_token('ELEMENT_OR_INPUT',
3421: 'Assignment_id or address_id');
3422: hr_utility.raise_error;
3423: end if;
3424: --
3425: hr_utility.set_location(l_proc, 30);
3426: --
3421: 'Assignment_id or address_id');
3422: hr_utility.raise_error;
3423: end if;
3424: --
3425: hr_utility.set_location(l_proc, 30);
3426: --
3427: --
3428: if p_datetrack_mode NOT IN ('ZAP',
3429: 'DELETE',
3430: 'UPDATE',
3431: 'CORRECTION',
3432: 'UPDATE_OVERRIDE',
3433: 'UPDATE_CHANGE_INSERT') then
3434: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
3435: hr_utility.raise_error;
3436: elsif p_datetrack_mode in ('ZAP', 'DELETE') then
3437: hr_utility.set_location(l_proc, 40);
3438: --
3431: 'CORRECTION',
3432: 'UPDATE_OVERRIDE',
3433: 'UPDATE_CHANGE_INSERT') then
3434: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
3435: hr_utility.raise_error;
3436: elsif p_datetrack_mode in ('ZAP', 'DELETE') then
3437: hr_utility.set_location(l_proc, 40);
3438: --
3439: if p_assignment_id is not null then
3433: 'UPDATE_CHANGE_INSERT') then
3434: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
3435: hr_utility.raise_error;
3436: elsif p_datetrack_mode in ('ZAP', 'DELETE') then
3437: hr_utility.set_location(l_proc, 40);
3438: --
3439: if p_assignment_id is not null then
3440: l_assignment_tbl(1) := p_assignment_id;
3441: else
3450: open csr_asg_id(l_assignment_tbl(l_cnt));
3451: fetch csr_asg_id into l_temp_num;
3452: if csr_asg_id%notfound then
3453: close csr_asg_id;
3454: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3455: hr_utility.raise_error;
3456: end if;
3457: close csr_asg_id;
3458: --
3451: fetch csr_asg_id into l_temp_num;
3452: if csr_asg_id%notfound then
3453: close csr_asg_id;
3454: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3455: hr_utility.raise_error;
3456: end if;
3457: close csr_asg_id;
3458: --
3459: hr_utility.set_location(l_proc, 45);
3455: hr_utility.raise_error;
3456: end if;
3457: close csr_asg_id;
3458: --
3459: hr_utility.set_location(l_proc, 45);
3460: delete_fed_tax_rule(
3461: p_effective_date => l_effective_date
3462: ,p_datetrack_delete_mode => p_datetrack_mode
3463: ,p_assignment_id => l_assignment_tbl(l_cnt)
3469: end loop;
3470: else
3471: if p_assignment_id is not null then
3472: --
3473: hr_utility.set_location(l_proc, 50);
3474: l_assignment_id := p_assignment_id;
3475: --
3476: open csr_asg_id(l_assignment_id);
3477: fetch csr_asg_id into l_temp_num;
3476: open csr_asg_id(l_assignment_id);
3477: fetch csr_asg_id into l_temp_num;
3478: if csr_asg_id%notfound then
3479: close csr_asg_id;
3480: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3481: hr_utility.raise_error;
3482: end if;
3483: close csr_asg_id;
3484: --
3477: fetch csr_asg_id into l_temp_num;
3478: if csr_asg_id%notfound then
3479: close csr_asg_id;
3480: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
3481: hr_utility.raise_error;
3482: end if;
3483: close csr_asg_id;
3484: --
3485: open csr_defaulting_date(l_assignment_id);
3524:
3525: if csr_defaultpayrollremoved%FOUND then
3526: close csr_defaulting_met;
3527: close csr_defaultpayrollremoved;
3528: hr_utility.set_message(801,'PAY_75264_US_PAYROLL_REMOVAL');
3529: hr_utility.raise_error;
3530: end if;
3531:
3532: close csr_defaultpayrollremoved;
3525: if csr_defaultpayrollremoved%FOUND then
3526: close csr_defaulting_met;
3527: close csr_defaultpayrollremoved;
3528: hr_utility.set_message(801,'PAY_75264_US_PAYROLL_REMOVAL');
3529: hr_utility.raise_error;
3530: end if;
3531:
3532: close csr_defaultpayrollremoved;
3533: close csr_defaulting_met;
3582:
3583: if csr_loc_addr%NOTFOUND then
3584: close csr_loc_addr;
3585: close csr_get_tax_loc;
3586: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3587: hr_utility.raise_error;
3588: end if;
3589:
3590: close csr_loc_addr;
3583: if csr_loc_addr%NOTFOUND then
3584: close csr_loc_addr;
3585: close csr_get_tax_loc;
3586: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
3587: hr_utility.raise_error;
3588: end if;
3589:
3590: close csr_loc_addr;
3591:
3619:
3620: if l_location_id is not null and
3621: l_effective_date >= l_defaulting_date then
3622: --
3623: hr_utility.set_location(l_proc, 60);
3624: --
3625: location_change(
3626: p_effective_date => l_effective_date
3627: ,p_datetrack_mode => p_datetrack_mode
3630: );
3631:
3632: elsif l_effective_date < l_defaulting_date then
3633: --
3634: hr_utility.set_location(l_proc, 70);
3635: --
3636: move_tax_default_date(
3637: p_effective_date => l_effective_date
3638: ,p_datetrack_mode => p_datetrack_mode
3643: end if; -- assignment id is not null, defaulting date found?
3644:
3645: else -- assignment id is null, so address id must not be null
3646: --
3647: hr_utility.set_location(l_proc, 80);
3648: --
3649: open csr_adr_id(p_address_id);
3650: fetch csr_adr_id into l_person_id, l_adr_primary, l_adr_style;
3651: if csr_adr_id%notfound then
3649: open csr_adr_id(p_address_id);
3650: fetch csr_adr_id into l_person_id, l_adr_primary, l_adr_style;
3651: if csr_adr_id%notfound then
3652: close csr_adr_id;
3653: hr_utility.set_message(801,'HR_51396_WEB_PERSON_NOT_FND');
3654: hr_utility.raise_error;
3655: --
3656: -- # 2858888: validate address is US style
3657: -- Reverting these changes, no longer needed (3406718)
3650: fetch csr_adr_id into l_person_id, l_adr_primary, l_adr_style;
3651: if csr_adr_id%notfound then
3652: close csr_adr_id;
3653: hr_utility.set_message(801,'HR_51396_WEB_PERSON_NOT_FND');
3654: hr_utility.raise_error;
3655: --
3656: -- # 2858888: validate address is US style
3657: -- Reverting these changes, no longer needed (3406718)
3658: --else
3663: --
3664: -- This will loop through each assignment for the given person's
3665: -- address_id
3666: --
3667: hr_utility.set_location(l_proc, 85);
3668: for l_assgn_rec in csr_adr_asg_id(p_address_id) loop
3669: --
3670: open csr_defaulting_date(l_assgn_rec.assignment_id);
3671: fetch csr_defaulting_date into l_defaulting_date;
3672: close csr_defaulting_date;
3673: --
3674: if l_defaulting_date is null then
3675: --
3676: hr_utility.set_location(l_proc, 90);
3677: --
3678: create_default_tax_rules(
3679: p_effective_date => l_effective_date
3680: ,p_assignment_id => l_assgn_rec.assignment_id
3687: else -- federal tax rule found for this assignment
3688: --
3689: -- address id not null, defaulting date found, hire date null
3690: --
3691: hr_utility.set_location(l_proc, 100);
3692: --
3693: address_change(
3694: p_effective_date => l_effective_date
3695: ,p_assignment_id => l_assgn_rec.assignment_id
3699: end loop; -- loop through each assignment for address_id
3700: end if; -- assignment id is not null?
3701: end if; -- datetrack mode is ZAP?
3702: --
3703: hr_utility.set_location(' Leaving:'||l_proc, 120);
3704: --
3705: exception
3706: --
3707: when l_exit_quietly then
3704: --
3705: exception
3706: --
3707: when l_exit_quietly then
3708: hr_utility.set_location(' Leaving:'||l_proc, 125);
3709: --
3710: end maintain_us_employee_taxes;
3711:
3712: -- ----------------------------------------------------------------------------
4265: and fed.effective_start_date = l_csr_tmp_date;
4266: --
4267: begin
4268: --
4269: hr_utility.set_location('Entering:'|| l_proc, 10);
4270: --
4271: -- Ensure that all the mandatory arguments are not null
4272: --
4273: hr_api.mandatory_arg_error(p_api_name => l_proc,
4274: p_argument => 'effective_date',
4275: p_argument_value => p_effective_date);
4276: --
4277: if p_assignment_id is null then
4278: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4279: hr_utility.raise_error;
4280: end if;
4281: --
4282: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4275: p_argument_value => p_effective_date);
4276: --
4277: if p_assignment_id is null then
4278: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4279: hr_utility.raise_error;
4280: end if;
4281: --
4282: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4283: 'UPDATE_OVERRIDE') and p_location_id is null then
4280: end if;
4281: --
4282: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4283: 'UPDATE_OVERRIDE') and p_location_id is null then
4284: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4285: hr_utility.raise_error;
4286: end if;
4287: --
4288: l_effective_date := trunc(p_effective_date);
4281: --
4282: if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
4283: 'UPDATE_OVERRIDE') and p_location_id is null then
4284: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4285: hr_utility.raise_error;
4286: end if;
4287: --
4288: l_effective_date := trunc(p_effective_date);
4289: --
4286: end if;
4287: --
4288: l_effective_date := trunc(p_effective_date);
4289: --
4290: hr_utility.set_location(l_proc, 20);
4291: --
4292: -- First check if geocode has been installed or not. If no geocodes
4293: -- installed then return because there is nothing to be done by this
4294: -- defaulting procedure
4303: l_chg_effective_start_date,
4304: l_chg_effective_end_date;
4305: if csr_asg_data%notfound then
4306: close csr_asg_data;
4307: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4308: hr_utility.raise_error;
4309: end if;
4310: close csr_asg_data;
4311: --
4304: l_chg_effective_end_date;
4305: if csr_asg_data%notfound then
4306: close csr_asg_data;
4307: hr_utility.set_message(801, 'HR_51253_PYP_ASS__NOT_VALID');
4308: hr_utility.raise_error;
4309: end if;
4310: close csr_asg_data;
4311: --
4312: -- Ensure that the new location id is different from the current location id
4336: open csr_chk_location_id;
4337: fetch csr_chk_location_id into l_temp_char;
4338: if csr_chk_location_id%notfound then
4339: close csr_chk_location_id;
4340: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4341: hr_utility.raise_error;
4342: end if;
4343: close csr_chk_location_id;
4344: l_location_id := p_location_id;
4337: fetch csr_chk_location_id into l_temp_char;
4338: if csr_chk_location_id%notfound then
4339: close csr_chk_location_id;
4340: hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
4341: hr_utility.raise_error;
4342: end if;
4343: close csr_chk_location_id;
4344: l_location_id := p_location_id;
4345: else
4351: open csr_fed_tax_dates;
4352: fetch csr_fed_tax_dates into l_defaulting_date, l_fed_tax_end_date;
4353: close csr_fed_tax_dates;
4354: if l_chg_effective_start_date < l_defaulting_date then
4355: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4356: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4357: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4358: hr_utility.raise_error;
4359: end if;
4352: fetch csr_fed_tax_dates into l_defaulting_date, l_fed_tax_end_date;
4353: close csr_fed_tax_dates;
4354: if l_chg_effective_start_date < l_defaulting_date then
4355: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4356: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4357: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4358: hr_utility.raise_error;
4359: end if;
4360: --
4353: close csr_fed_tax_dates;
4354: if l_chg_effective_start_date < l_defaulting_date then
4355: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4356: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4357: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4358: hr_utility.raise_error;
4359: end if;
4360: --
4361: hr_utility.set_location(l_proc, 30);
4354: if l_chg_effective_start_date < l_defaulting_date then
4355: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4356: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4357: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4358: hr_utility.raise_error;
4359: end if;
4360: --
4361: hr_utility.set_location(l_proc, 30);
4362: --
4357: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4358: hr_utility.raise_error;
4359: end if;
4360: --
4361: hr_utility.set_location(l_proc, 30);
4362: --
4363: -- Select state, county and city codes for the new work location of the
4364: -- assignment.
4365: --
4367: fetch csr_loc_addr into l_loc_state_code, l_loc_county_code, l_loc_city_code,
4368: l_loc_ovrd_state_code,l_loc_ovrd_county_code,l_loc_ovrd_city_code;
4369: if csr_loc_addr%NOTFOUND then
4370: close csr_loc_addr;
4371: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
4372: hr_utility.raise_error;
4373: end if;
4374: close csr_loc_addr;
4375:
4368: l_loc_ovrd_state_code,l_loc_ovrd_county_code,l_loc_ovrd_city_code;
4369: if csr_loc_addr%NOTFOUND then
4370: close csr_loc_addr;
4371: hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
4372: hr_utility.raise_error;
4373: end if;
4374: close csr_loc_addr;
4375:
4376: -- since l_loc_ovrd_state_code defaults to the loc_state_code, we
4377: -- use it to set the sui
4378: l_sui_state_code := l_loc_ovrd_state_code;
4379:
4380: --
4381: hr_utility.set_location(l_proc, 40);
4382: --
4383: --
4384: -- Branch according to p_datetrack_mode here
4385: --
4384: -- Branch according to p_datetrack_mode here
4385: --
4386: if p_datetrack_mode = 'CORRECTION' then
4387: --
4388: hr_utility.set_location(l_proc, 50);
4389: --
4390: -- Select the federal tax record as of the assignment end date.
4391: --
4392: open csr_fed_rec1(p_assignment_id, l_chg_effective_end_date,
4394: fetch csr_fed_rec1 into l_fed_rec;
4395: if csr_fed_rec1%notfound then
4396: -- No federal tax rule exists as of the end of the assignment record!
4397: close csr_fed_rec1;
4398: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4399: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4400: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4401: hr_utility.raise_error;
4402: end if;
4395: if csr_fed_rec1%notfound then
4396: -- No federal tax rule exists as of the end of the assignment record!
4397: close csr_fed_rec1;
4398: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4399: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4400: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4401: hr_utility.raise_error;
4402: end if;
4403: close csr_fed_rec1;
4396: -- No federal tax rule exists as of the end of the assignment record!
4397: close csr_fed_rec1;
4398: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4399: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4400: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4401: hr_utility.raise_error;
4402: end if;
4403: close csr_fed_rec1;
4404: --
4397: close csr_fed_rec1;
4398: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4399: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4400: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4401: hr_utility.raise_error;
4402: end if;
4403: close csr_fed_rec1;
4404: --
4405: -- If the assignment effective end date does not match the federal tax rule
4433: fetch csr_fed_rec1 into l_fed_rec;
4434: if csr_fed_rec1%notfound then
4435: -- No federal tax rule exists as of the start of the assignment record!
4436: close csr_fed_rec1;
4437: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4438: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4439: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4440: hr_utility.raise_error;
4441: end if;
4434: if csr_fed_rec1%notfound then
4435: -- No federal tax rule exists as of the start of the assignment record!
4436: close csr_fed_rec1;
4437: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4438: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4439: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4440: hr_utility.raise_error;
4441: end if;
4442: close csr_fed_rec1;
4435: -- No federal tax rule exists as of the start of the assignment record!
4436: close csr_fed_rec1;
4437: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4438: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4439: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4440: hr_utility.raise_error;
4441: end if;
4442: close csr_fed_rec1;
4443: --
4436: close csr_fed_rec1;
4437: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4438: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4439: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4440: hr_utility.raise_error;
4441: end if;
4442: close csr_fed_rec1;
4443: --
4444: -- Set new sui location in the record structure.
4597:
4598:
4599: elsif p_datetrack_mode = 'UPDATE' then
4600: --
4601: hr_utility.set_location(l_proc, 60);
4602: --
4603: -- Select the federal record as of the assignment start date.
4604: --
4605: open csr_fed_rec1(p_assignment_id, l_chg_effective_start_date,
4607: fetch csr_fed_rec1 into l_fed_rec;
4608: if csr_fed_rec1%notfound then
4609: -- No federal tax rule exists as of the start of the assignment record!
4610: close csr_fed_rec1;
4611: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4612: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4613: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4614: hr_utility.raise_error;
4615: end if;
4608: if csr_fed_rec1%notfound then
4609: -- No federal tax rule exists as of the start of the assignment record!
4610: close csr_fed_rec1;
4611: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4612: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4613: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4614: hr_utility.raise_error;
4615: end if;
4616: close csr_fed_rec1;
4609: -- No federal tax rule exists as of the start of the assignment record!
4610: close csr_fed_rec1;
4611: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4612: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4613: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4614: hr_utility.raise_error;
4615: end if;
4616: close csr_fed_rec1;
4617: --
4610: close csr_fed_rec1;
4611: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4612: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4613: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4614: hr_utility.raise_error;
4615: end if;
4616: close csr_fed_rec1;
4617: --
4618: -- Set new sui location in the record structure.
4770:
4771:
4772: elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
4773: --
4774: hr_utility.set_location(l_proc, 70);
4775: --
4776: -- Select the federal tax record as of the assignment end date.
4777: --
4778: open csr_fed_rec1(p_assignment_id, l_chg_effective_end_date,
4780: fetch csr_fed_rec1 into l_fed_rec;
4781: if csr_fed_rec1%notfound then
4782: -- No federal tax rule exists as of the end of the assignment record!
4783: close csr_fed_rec1;
4784: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4785: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4786: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4787: hr_utility.raise_error;
4788: end if;
4781: if csr_fed_rec1%notfound then
4782: -- No federal tax rule exists as of the end of the assignment record!
4783: close csr_fed_rec1;
4784: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4785: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4786: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4787: hr_utility.raise_error;
4788: end if;
4789: close csr_fed_rec1;
4782: -- No federal tax rule exists as of the end of the assignment record!
4783: close csr_fed_rec1;
4784: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4785: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4786: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4787: hr_utility.raise_error;
4788: end if;
4789: close csr_fed_rec1;
4790: --
4783: close csr_fed_rec1;
4784: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4785: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4786: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_end_date);
4787: hr_utility.raise_error;
4788: end if;
4789: close csr_fed_rec1;
4790: --
4791: -- If the assignment effective end date does not match the federal tax rule
4819: fetch csr_fed_rec1 into l_fed_rec;
4820: if csr_fed_rec1%notfound then
4821: -- No federal tax rule exists as of the start of the assignment record!
4822: close csr_fed_rec1;
4823: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4824: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4825: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4826: hr_utility.raise_error;
4827: end if;
4820: if csr_fed_rec1%notfound then
4821: -- No federal tax rule exists as of the start of the assignment record!
4822: close csr_fed_rec1;
4823: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4824: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4825: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4826: hr_utility.raise_error;
4827: end if;
4828: close csr_fed_rec1;
4821: -- No federal tax rule exists as of the start of the assignment record!
4822: close csr_fed_rec1;
4823: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4824: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4825: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4826: hr_utility.raise_error;
4827: end if;
4828: close csr_fed_rec1;
4829: --
4822: close csr_fed_rec1;
4823: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4824: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4825: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
4826: hr_utility.raise_error;
4827: end if;
4828: close csr_fed_rec1;
4829: --
4830: -- Set new sui location in the record structure.
4983:
4984:
4985: elsif p_datetrack_mode = 'UPDATE_OVERRIDE' then
4986: --
4987: hr_utility.set_location(l_proc, 80);
4988: --
4989: -- Select the federal record as of the assignment start date.
4990: --
4991: open csr_fed_rec1(p_assignment_id, l_chg_effective_start_date,
4993: fetch csr_fed_rec1 into l_fed_rec;
4994: if csr_fed_rec1%notfound then
4995: -- No federal tax rule exists as of the start of the assignment record!
4996: close csr_fed_rec1;
4997: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4998: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4999: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
5000: hr_utility.raise_error;
5001: end if;
4994: if csr_fed_rec1%notfound then
4995: -- No federal tax rule exists as of the start of the assignment record!
4996: close csr_fed_rec1;
4997: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4998: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4999: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
5000: hr_utility.raise_error;
5001: end if;
5002: close csr_fed_rec1;
4995: -- No federal tax rule exists as of the start of the assignment record!
4996: close csr_fed_rec1;
4997: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4998: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4999: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
5000: hr_utility.raise_error;
5001: end if;
5002: close csr_fed_rec1;
5003: --
4996: close csr_fed_rec1;
4997: hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
4998: hr_utility.set_message_token('TABLE_NAME','PAY_US_EMP_FED_TAX_RULES_F');
4999: hr_utility.set_message_token('SESSION_DATE', l_chg_effective_start_date);
5000: hr_utility.raise_error;
5001: end if;
5002: close csr_fed_rec1;
5003: --
5004: -- Set new sui location in the record structure.
5157: elsif p_datetrack_mode in ('ZAP', 'DELETE',
5158: 'DELETE_NEXT_CHANGE',
5159: 'FUTURE_CHANGE') then
5160: --
5161: hr_utility.set_location(l_proc, 110);
5162: --
5163: -- This process should not be called with these modes. Call
5164: -- Maintain_us_emp_taxes for a ZAP delete.
5165: -- Other delete modes are not allowed.
5162: --
5163: -- This process should not be called with these modes. Call
5164: -- Maintain_us_emp_taxes for a ZAP delete.
5165: -- Other delete modes are not allowed.
5166: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
5167: hr_utility.raise_error;
5168:
5169: else
5170: -- Unknown datetrack mode
5163: -- This process should not be called with these modes. Call
5164: -- Maintain_us_emp_taxes for a ZAP delete.
5165: -- Other delete modes are not allowed.
5166: hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
5167: hr_utility.raise_error;
5168:
5169: else
5170: -- Unknown datetrack mode
5171: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5167: hr_utility.raise_error;
5168:
5169: else
5170: -- Unknown datetrack mode
5171: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5172: hr_utility.set_message_token('DT_MODE', p_datetrack_mode);
5173: hr_utility.raise_error;
5174:
5175: end if;
5168:
5169: else
5170: -- Unknown datetrack mode
5171: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5172: hr_utility.set_message_token('DT_MODE', p_datetrack_mode);
5173: hr_utility.raise_error;
5174:
5175: end if;
5176: --
5169: else
5170: -- Unknown datetrack mode
5171: hr_utility.set_message(801, 'HR_7184_DT_MODE_UNKNOWN');
5172: hr_utility.set_message_token('DT_MODE', p_datetrack_mode);
5173: hr_utility.raise_error;
5174:
5175: end if;
5176: --
5177: --
5272: );
5273:
5274:
5275: --
5276: hr_utility.set_location(' Leaving:'||l_proc, 130);
5277: --
5278: exception
5279: --
5280: -- This is called when location_change runs but the new location is the
6062: --
6063: open csr_check_payroll(p_defaulting_date, p_new_default_date);
6064: fetch csr_check_payroll into l_exists;
6065: if csr_check_payroll%FOUND then
6066: hr_utility.set_location(l_proc,15);
6067: close csr_check_payroll;
6068: hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
6069: hr_utility.raise_error;
6070: end if;
6064: fetch csr_check_payroll into l_exists;
6065: if csr_check_payroll%FOUND then
6066: hr_utility.set_location(l_proc,15);
6067: close csr_check_payroll;
6068: hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
6069: hr_utility.raise_error;
6070: end if;
6071: close csr_check_payroll;
6072: --
6065: if csr_check_payroll%FOUND then
6066: hr_utility.set_location(l_proc,15);
6067: close csr_check_payroll;
6068: hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
6069: hr_utility.raise_error;
6070: end if;
6071: close csr_check_payroll;
6072: --
6073: -- Create a break in the federal tax rule at the new defaulting date, then
6328: --
6329: --
6330: begin -- move_tax_default_date procedure
6331: --
6332: hr_utility.set_location('Entering:'|| l_proc, 10);
6333: --
6334: l_new_hire_date := trunc(p_new_hire_date);
6335: l_effective_date := nvl(l_new_hire_date, trunc(p_effective_date));
6336: --
6333: --
6334: l_new_hire_date := trunc(p_new_hire_date);
6335: l_effective_date := nvl(l_new_hire_date, trunc(p_effective_date));
6336: --
6337: hr_utility.set_location(l_proc, 20);
6338: --
6339: -- First check if geocode has been installed or not. If no geocodes
6340: -- installed then return because there is nothing to be done by this
6341: -- defaulting procedure
6351: --
6352: -- Validate p_assignment_id against per_assignments_f
6353: --
6354: if p_assignment_id is null then
6355: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6356: hr_utility.raise_error;
6357: else
6358: open csr_assignment_id;
6359: fetch csr_assignment_id into l_temp_char;
6352: -- Validate p_assignment_id against per_assignments_f
6353: --
6354: if p_assignment_id is null then
6355: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6356: hr_utility.raise_error;
6357: else
6358: open csr_assignment_id;
6359: fetch csr_assignment_id into l_temp_char;
6360: if csr_assignment_id%notfound then
6358: open csr_assignment_id;
6359: fetch csr_assignment_id into l_temp_char;
6360: if csr_assignment_id%notfound then
6361: close csr_assignment_id;
6362: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6363: hr_utility.raise_error;
6364: end if;
6365: close csr_assignment_id;
6366: end if;
6359: fetch csr_assignment_id into l_temp_char;
6360: if csr_assignment_id%notfound then
6361: close csr_assignment_id;
6362: hr_utility.set_message(801,'PAY_7702_PDT_VALUE_NOT_FOUND');
6363: hr_utility.raise_error;
6364: end if;
6365: close csr_assignment_id;
6366: end if;
6367:
6371: open csr_asg_data(p_assignment_id, l_effective_date);
6372: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6373: if csr_asg_data%notfound then
6374: close csr_asg_data;
6375: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6376: hr_utility.set_message_token('DATE',l_effective_date);
6377: hr_utility.raise_error;
6378: end if;
6379: close csr_asg_data;
6372: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6373: if csr_asg_data%notfound then
6374: close csr_asg_data;
6375: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6376: hr_utility.set_message_token('DATE',l_effective_date);
6377: hr_utility.raise_error;
6378: end if;
6379: close csr_asg_data;
6380: --
6373: if csr_asg_data%notfound then
6374: close csr_asg_data;
6375: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6376: hr_utility.set_message_token('DATE',l_effective_date);
6377: hr_utility.raise_error;
6378: end if;
6379: close csr_asg_data;
6380: --
6381: -- Identify the new default date as either the new hire date passed in, or
6387: fetch csr_fed_tax_rule into l_defaulting_date, l_fed_eed,
6388: l_emp_fed_tax_rule_id;
6389: if csr_fed_tax_rule%notfound then
6390: close csr_fed_tax_rule;
6391: hr_utility.set_message(801,'PAY_51465_PDT_INVALID_LOC');
6392: hr_utility.raise_error;
6393: end if;
6394: close csr_fed_tax_rule;
6395: --
6388: l_emp_fed_tax_rule_id;
6389: if csr_fed_tax_rule%notfound then
6390: close csr_fed_tax_rule;
6391: hr_utility.set_message(801,'PAY_51465_PDT_INVALID_LOC');
6392: hr_utility.raise_error;
6393: end if;
6394: close csr_fed_tax_rule;
6395: --
6396: -- To pull back the defaulting date, the assignment as of the new default date
6401: open csr_asg_data(p_assignment_id, l_new_default_date);
6402: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6403: if csr_asg_data%notfound then
6404: close csr_asg_data;
6405: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6406: hr_utility.set_message_token('DATE',l_new_default_date);
6407: hr_utility.raise_error;
6408: end if;
6409: close csr_asg_data;
6402: fetch csr_asg_data into l_asg_esd, l_asg_eed;
6403: if csr_asg_data%notfound then
6404: close csr_asg_data;
6405: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6406: hr_utility.set_message_token('DATE',l_new_default_date);
6407: hr_utility.raise_error;
6408: end if;
6409: close csr_asg_data;
6410: --
6403: if csr_asg_data%notfound then
6404: close csr_asg_data;
6405: hr_utility.set_message(801,'HR_7026_ELE_ENTRY_ASS_NOT_EXST');
6406: hr_utility.set_message_token('DATE',l_new_default_date);
6407: hr_utility.raise_error;
6408: end if;
6409: close csr_asg_data;
6410: --
6411: if l_defaulting_date - 1 not between l_asg_esd and l_asg_eed then
6448: l_loc_city_code, l_loc_ovrd_state_code,
6449: l_loc_ovrd_county_code,l_loc_ovrd_city_code;
6450: if csr_loc_addr%notfound then
6451: close csr_loc_addr;
6452: hr_utility.set_message(801,'HR_51357_POS_LOC_NOT_EXIST');
6453: hr_utility.raise_error;
6454: end if;
6455: close csr_loc_addr;
6456:
6449: l_loc_ovrd_county_code,l_loc_ovrd_city_code;
6450: if csr_loc_addr%notfound then
6451: close csr_loc_addr;
6452: hr_utility.set_message(801,'HR_51357_POS_LOC_NOT_EXIST');
6453: hr_utility.raise_error;
6454: end if;
6455: close csr_loc_addr;
6456:
6457: end if;
7189:
7190:
7191: else -- hire date is null, datetrack mode is invalid
7192: --
7193: hr_utility.set_message(801,'HR_7184_DT_MODE_UNKNOWN');
7194: hr_utility.set_message_token('DT_MODE',p_datetrack_mode);
7195: hr_utility.raise_error;
7196: --
7197: end if; -- hire date is null, branching on datetrack mode
7190:
7191: else -- hire date is null, datetrack mode is invalid
7192: --
7193: hr_utility.set_message(801,'HR_7184_DT_MODE_UNKNOWN');
7194: hr_utility.set_message_token('DT_MODE',p_datetrack_mode);
7195: hr_utility.raise_error;
7196: --
7197: end if; -- hire date is null, branching on datetrack mode
7198: end if; -- hire date is null and new default < old default
7191: else -- hire date is null, datetrack mode is invalid
7192: --
7193: hr_utility.set_message(801,'HR_7184_DT_MODE_UNKNOWN');
7194: hr_utility.set_message_token('DT_MODE',p_datetrack_mode);
7195: hr_utility.raise_error;
7196: --
7197: end if; -- hire date is null, branching on datetrack mode
7198: end if; -- hire date is null and new default < old default
7199: --
7217: ,p_city_code => l_loc_ovrd_city_code
7218: );
7219: end if;
7220: --
7221: hr_utility.set_location(' Leaving:'||l_proc, 130);
7222: --
7223: exception
7224: --
7225: when l_exit_quietly then
7223: exception
7224: --
7225: when l_exit_quietly then
7226: --
7227: hr_utility.set_location(' Leaving:'||l_proc, 135);
7228: --
7229: end move_tax_default_date;
7230:
7231: