[Home] [Help]
1020: and pugu.id = g_geo_phase_id);
1021:
1022: l_city_tax_exists varchar2(2);
1023:
1024: --Retrieve all affected rows in the pay_element_entry_values_f table.
1025: --Since we can join on the main part of the pk of the table we do not
1026: --need the logic in the element_entries procedure.
1027:
1028: --
1037: CURSOR pev_cur(geocode VARCHAR2, assign_id NUMBER) IS
1038: SELECT /*+ ORDERED */ distinct pev.screen_entry_value, pev.element_entry_id,
1039: pev.input_value_id
1040: FROM pay_element_entries_f pee,
1041: pay_element_entry_values_f pev,
1042: pay_input_values_f piv
1043: WHERE pee.assignment_id = assign_id
1044: AND pee.element_entry_id = pev.element_entry_id
1045: AND pev.screen_entry_value = geocode
1050: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1051: where pugu.assignment_id = assign_id
1052: and pugu.table_value_id = pev.element_entry_id
1053: and pugu.old_juri_code = geocode
1054: and pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
1055: and pugu.process_mode = g_mode
1056: and pugu.process_type = g_process_type
1057: and pugu.id = g_geo_phase_id);
1058:
2155: hr_utility.trace('Before update of element entry values for assignment_id: '||to_char(p_assign_id));
2156:
2157: IF G_MODE = 'UPGRADE' THEN
2158:
2159: UPDATE pay_element_entry_values_f
2160: SET screen_entry_value = p_new_juri_code
2161: WHERE screen_entry_value = p_old_juri_code
2162: AND input_value_id+0 = p_input_value_id
2163: AND element_entry_id = p_ele_ent_id;
2172: p_person_id => p_person_id,
2173: p_assign_id => p_assign_id,
2174: p_old_juri_code => p_old_juri_code,
2175: p_new_juri_code => p_new_juri_code,
2176: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2177: p_id => p_ele_ent_id);
2178:
2179: ELSE
2180:
2185: p_person_id => p_person_id,
2186: p_assign_id => p_assign_id,
2187: p_old_juri_code => p_old_juri_code,
2188: p_new_juri_code => p_new_juri_code,
2189: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2190: p_id => p_ele_ent_id);
2191:
2192: END IF;
2193:
2273:
2274: -- This cursor will get us the element entries of the assignments processed
2275: cursor csr_get_dup (p_assignment number) is
2276: select pev.screen_entry_value sev, pev.element_entry_id eei
2277: from pay_element_entry_values_f pev,
2278: pay_input_values_f piv,
2279: pay_element_types_f pet,
2280: pay_element_links_f pel,
2281: pay_element_entries_f pee
2294:
2295: cursor csr_get_percentage (p_element_entry_id NUMBER) is
2296: select /*Bug 7592909*/distinct pev.screen_entry_value , pev.effective_start_date,
2297: pev.effective_end_date
2298: from pay_element_entry_values_f pev,
2299: pay_input_values_f piv,
2300: pay_element_types_f pet,
2301: pay_element_links_f pel,
2302: pay_element_entries_f pef
2311: and piv.name = 'Percentage'
2312: and piv.legislation_code = 'US'
2313: and pet.legislation_code = 'US';
2314:
2315: l_prev_screen pay_element_entry_values_f.screen_entry_value%TYPE;
2316: l_prev_eleid pay_element_entry_values_f.element_entry_id%TYPE;
2317: l_percent pay_element_entry_values_f.screen_entry_value%TYPE;
2318: l_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE;
2319: l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2312: and piv.legislation_code = 'US'
2313: and pet.legislation_code = 'US';
2314:
2315: l_prev_screen pay_element_entry_values_f.screen_entry_value%TYPE;
2316: l_prev_eleid pay_element_entry_values_f.element_entry_id%TYPE;
2317: l_percent pay_element_entry_values_f.screen_entry_value%TYPE;
2318: l_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE;
2319: l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2320:
2313: and pet.legislation_code = 'US';
2314:
2315: l_prev_screen pay_element_entry_values_f.screen_entry_value%TYPE;
2316: l_prev_eleid pay_element_entry_values_f.element_entry_id%TYPE;
2317: l_percent pay_element_entry_values_f.screen_entry_value%TYPE;
2318: l_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE;
2319: l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2320:
2321: BEGIN
2314:
2315: l_prev_screen pay_element_entry_values_f.screen_entry_value%TYPE;
2316: l_prev_eleid pay_element_entry_values_f.element_entry_id%TYPE;
2317: l_percent pay_element_entry_values_f.screen_entry_value%TYPE;
2318: l_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE;
2319: l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2320:
2321: BEGIN
2322:
2315: l_prev_screen pay_element_entry_values_f.screen_entry_value%TYPE;
2316: l_prev_eleid pay_element_entry_values_f.element_entry_id%TYPE;
2317: l_percent pay_element_entry_values_f.screen_entry_value%TYPE;
2318: l_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE;
2319: l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2320:
2321: BEGIN
2322:
2323: hr_utility.trace('Entering pay_us_geo_upd_pkg.duplicate_vertex_ee');
2357: IF G_MODE = 'UPGRADE' THEN
2358:
2359: hr_utility.trace('The previous element entry id is: '||to_char(l_prev_eleid));
2360:
2361: update pay_element_entry_values_f pev
2362: set pev.screen_entry_value = pev.screen_entry_value + l_percent
2363: where pev.element_entry_id = l_prev_eleid
2364: and pev.screen_entry_value is not null
2365: and pev.input_value_id = (select distinct piv.input_value_id
2683: pay_us_emp_state_tax_rules_f pusf
2684: WHERE pusf.assignment_id = p_assign_id
2685: AND pusf.state_code = pus.state_abbrev
2686: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
2687: where pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
2688: and pugu.process_type = 'PERCENTAGE_OVER_100'
2689: and pugu.assignment_id = p_assign_id
2690: and pugu.process_mode = g_mode
2691: and pugu.process_type = g_process_type
2700: CURSOR sum_cur(p_assign_id NUMBER, start_date DATE,
2701: end_date DATE, state_code CHAR) IS
2702: SELECT sum(nvl(to_number(pev2.screen_entry_value),0))
2703: FROM pay_input_values_f piv2,
2704: pay_element_entry_values_f pev2,
2705: pay_input_values_f piv1,
2706: pay_element_entry_values_f pev1,
2707: pay_element_types_f pet,
2708: pay_element_links_f pel,
2702: SELECT sum(nvl(to_number(pev2.screen_entry_value),0))
2703: FROM pay_input_values_f piv2,
2704: pay_element_entry_values_f pev2,
2705: pay_input_values_f piv1,
2706: pay_element_entry_values_f pev1,
2707: pay_element_types_f pet,
2708: pay_element_links_f pel,
2709: pay_element_entries_f pef
2710: WHERE pef.assignment_id = p_assign_id
2794: p_person_id => l_person_id,
2795: p_assign_id => p_assign_id,
2796: p_old_juri_code => null,
2797: p_new_juri_code => null,
2798: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2799: p_id => null);
2800:
2801:
2802: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);