[Home] [Help]
1224: parameters:
1225: return:
1226: specs:
1227: **************************************************************************/
1228: FUNCTION fnc_get_tax_limit_rate(IN_state_code IN pay_us_states.state_code%TYPE,
1229: IN_start_date IN DATE,
1230: IN_as_of_date IN DATE,
1231: IN_tax_type IN VARCHAR2,
1232: IN_ee_or_er IN VARCHAR2,
1381: AND org_information_context = 'State Tax Rules';
1382:
1383: CURSOR c_get_state_code (IN_sui_state_code IN VARCHAR2) Is
1384: SELECT state_abbrev
1385: FROM pay_us_states
1386: WHERE state_code = IN_sui_state_code;
1387:
1388: l_flag VARCHAR2(2) := 'F' ;
1389: l_count NUMBER := 0;
1451: L_return_val NUMBER;
1452:
1453: CURSOR c_get_sui_sdi_overide_rt (IN_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE) IS
1454: SELECT pus.state_code,org_information6/100, org_information7/100 , org_information14/100
1455: FROM hr_organization_information org, pay_us_states pus
1456: WHERE org.org_information1 = pus.state_abbrev
1457: AND pus.state_code between 00 and 99
1458: AND org.organization_id = IN_tax_unit_id
1459: AND org.org_information_context = 'State Tax Rules';
1672: IN_as_of_date -
1673: return: TRUE/FALSE
1674: specs:
1675: **************************************************************************/
1676: FUNCTION fnc_sit_exists(IN_state_code IN pay_us_states.state_code%TYPE,
1677: IN_as_of_date IN DATE) RETURN BOOLEAN IS
1678:
1679: /********************* 3005756 START ************************************/
1680:
1856: pus.state_code state_code,
1857: pus.state_abbrev
1858: FROM pay_run_balances prb,
1859: per_assignments_f paf,
1860: pay_us_states pus
1861: WHERE paf.person_id = IN_person_id
1862: AND prb.effective_date BETWEEN IN_start_date and IN_as_of_date
1863: AND prb.effective_date BETWEEN paf.effective_start_date
1864: AND paf.effective_end_date
1877: prb.jurisdiction_code||'-0000' jurisdiction_code,
1878: puc.county_name||','||pus.state_abbrev jurisdiction_name
1879: FROM pay_run_balances prb,
1880: per_assignments_f paf,
1881: pay_us_states pus,
1882: pay_us_counties puc
1883: WHERE paf.person_id = IN_person_id
1884: AND paf.effective_start_date <= IN_as_of_date
1885: AND paf.effective_end_date >= IN_start_date
1903: prb.jurisdiction_code,
1904: pun.city_name||','||pus.state_abbrev jurisdiction_name
1905: FROM pay_run_balances prb,
1906: per_assignments_f paf,
1907: pay_us_states pus,
1908: pay_us_city_names pun
1909: WHERE paf.person_id = IN_person_id
1910: AND paf.effective_start_date <= IN_as_of_date
1911: AND paf.effective_end_date >= IN_start_date
1935: psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1936: psd.state_code||'-'||psd.county_code||'-'||psd.city_code reg_jurisdiction_cd
1937: FROM pay_run_balances prb,
1938: per_assignments_f paf,
1939: pay_us_states pus,
1940: pay_us_city_school_dsts psd
1941: WHERE paf.person_id = IN_person_id
1942: AND paf.effective_start_date <= IN_as_of_date
1943: AND paf.effective_end_date >= IN_start_date
1958: psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
1959: psd.state_code||'-'||psd.county_code||'-0000' reg_jurisdiction_cd
1960: FROM per_assignments_f paf,
1961: pay_run_balances prb,
1962: pay_us_states pus,
1963: pay_us_county_school_dsts psd
1964: WHERE paf.person_id = IN_person_id
1965: AND paf.effective_start_date <= IN_as_of_date
1966: AND paf.effective_end_date >= IN_start_date
1990: pes.state_code,
1991: pus.state_abbrev
1992: FROM pay_us_emp_state_tax_rules_f pes,
1993: per_assignments_f paf,
1994: pay_us_states pus
1995: WHERE pes.assignment_id = paf.assignment_id
1996: AND pes.state_code = pus.state_code
1997: AND paf.effective_start_date BETWEEN pes.effective_start_date
1998: AND pes.effective_end_date
2035: SELECT DISTINCT pes.jurisdiction_code,
2036: puc.county_name||','||pus.state_abbrev jurisdiction_name
2037: FROM pay_us_emp_county_tax_rules_f pes,
2038: per_assignments_f paf,
2039: pay_us_states pus,
2040: pay_us_counties puc
2041: WHERE pes.assignment_id = paf.assignment_id
2042: AND pes.state_code = pus.state_code
2043: AND pes.county_code = puc.county_code
2080: pes.jurisdiction_code,
2081: pun.city_name||','||pus.state_abbrev jurisdiction_name
2082: FROM pay_us_emp_city_tax_rules_f pes,
2083: per_assignments_f paf,
2084: pay_us_states pus,
2085: pay_us_city_names pun
2086: WHERE pes.assignment_id = paf.assignment_id
2087: AND pes.state_code = pus.state_code
2088: AND pes.state_code = pun.state_code
2129: psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
2130: pes.jurisdiction_code reg_jurisdiction_cd
2131: FROM pay_us_emp_city_tax_rules_f pes,
2132: per_assignments_f paf,
2133: pay_us_states pus,
2134: pay_us_city_school_dsts psd
2135: WHERE pes.assignment_id = paf.assignment_id
2136: AND pes.school_district_code IS NOT NULL
2137: AND pes.state_code = pus.state_code
2172: psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
2173: pes.jurisdiction_code reg_jurisdiction_cd
2174: FROM pay_us_emp_county_tax_rules_f pes,
2175: per_assignments_f paf,
2176: pay_us_states pus,
2177: pay_us_county_school_dsts psd
2178: WHERE pes.assignment_id = paf.assignment_id
2179: AND pes.school_district_code IS NOT NULL
2180: AND pes.state_code = pus.state_code