[Home] [Help]
723: l_primary_only VARCHAR2(1);
724:
725: CURSOR c_excess_over_fed IS
726: select 'x'
727: from per_assignments_f paf,
728: pay_us_emp_fed_tax_rules_f ftr
729: where paf.person_id = p_person_id
730: and ftr.assignment_id = paf.assignment_id
731: and paf.assignment_type = 'E'
740: or nvl(ftr.fit_override_amount,0) <> 0);
741:
742: CURSOR c_excess_over_state IS
743: select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ 'x'
744: from per_assignments_f paf,
745: pay_us_emp_state_tax_rules_f str,
746: pay_us_state_tax_info_f stif
747: where paf.person_id = p_person_id
748: and paf.assignment_type = 'E'
762: );
763:
764: CURSOR c_future_fed_recs IS
765: select 'x'
766: from per_assignments_f paf,
767: pay_us_emp_fed_tax_rules_f ftr
768: where paf.person_id = p_person_id
769: and paf.assignment_type = 'E'
770: and ftr.assignment_id = paf.assignment_id
774: paf.effective_end_date;
775:
776: CURSOR c_future_state_recs IS
777: select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ 'x'
778: from per_assignments_f paf,
779: pay_us_emp_state_tax_rules_f str,
780: pay_us_state_tax_info_f stif
781: where paf.person_id = p_person_id
782: and str.assignment_id = paf.assignment_id
1743: IS
1744: ln_person_id per_people_f.person_id%TYPE;
1745: ln_business_group_id per_people_f.business_group_id%TYPE;
1746: ln_parent_audit_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
1747: ln_assignment_id per_assignments_f.assignment_id%TYPE;
1748: ln_gre_id hr_organization_units.organization_id%TYPE;
1749: ln_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
1750: ln_state_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
1751: ld_old_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1861: IS
1862: ln_person_id per_people_f.person_id%TYPE;
1863: ln_business_group_id per_people_f.business_group_id%TYPE;
1864: ln_parent_audit_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
1865: ln_assignment_id per_assignments_f.assignment_id%TYPE;
1866: ln_gre_id hr_organization_units.organization_id%TYPE;
1867: ln_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
1868: ln_state_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
1869: ld_old_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1892: lv_update_error_msg VARCHAR2(10000);
1893: e_no_records EXCEPTION;
1894: e_no_update_allowed EXCEPTION;
1895: e_date_error EXCEPTION;
1896: l_primary_flag per_assignments_f.primary_flag%TYPE;
1897:
1898: l_state_exempt varchar2(15);
1899:
1900: lv_filing_status_changed boolean := false;
1932: decode(ftr.fit_exempt,'Y',1,0),
1933: decode(ftr.futa_tax_exempt,'Y',1,0),
1934: decode(ftr.medicare_tax_exempt,'Y',1,0),
1935: decode(ftr.ss_tax_exempt,'Y',1,0)
1936: from pay_us_emp_fed_tax_rules_f ftr, per_assignments_f paf,
1937: hr_soft_coding_keyflex hsck
1938: where paf.person_id = ln_person_id
1939: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1940: and paf.assignment_id = ftr.assignment_id
1957: decode(ftr.futa_tax_exempt,'Y',1,0) futa_tax_exempt_count,
1958: decode(ftr.medicare_tax_exempt,'Y',1,0) medicare_tax_exempt_count,
1959: decode(ftr.ss_tax_exempt,'Y',1,0) ss_tax_exempt_count
1960: from pay_us_emp_fed_tax_rules_f ftr
1961: ,per_assignments_f paf
1962: ,hr_soft_coding_keyflex hsck
1963: where paf.person_id = ln_person_id
1964: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1965: and paf.assignment_id = ftr.assignment_id
1974:
1975: l_fed_tax_rec c_fed_tax_rows%rowtype;
1976:
1977: /*
1978: CURSOR c_state_tax_rows(curvar_assignment_id per_assignments_f.assignment_id%TYPE) IS
1979: select str.emp_state_tax_rule_id,
1980: str.object_version_number,
1981: str.effective_start_date,
1982: str.filing_status_code,
1993: ,decode(str.sit_exempt,'Y',1,0)
1994: ,decode(str.sui_exempt,'Y',1,0)
1995: ,decode(str.sdi_exempt,'Y',1,0)
1996: ,decode(str.wc_exempt,'Y',1,0)
1997: from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
1998: pay_us_state_tax_info_f stif, pay_us_states pus,
1999: hr_soft_coding_keyflex hsck
2000: where paf.person_id = ln_person_id
2001: and paf.assignment_id = curvar_assignment_id
2014: str.effective_end_date
2015: for update nowait;
2016: */
2017:
2018: CURSOR c_state_tax_rows(curvar_assignment_id per_assignments_f.assignment_id%TYPE) IS
2019: select str.*
2020: ,pus.state_name
2021: ,stif.sta_information7 state_as_fed
2022: ,hsck.segment1 gre_id
2027: ,decode(str.sit_exempt,'Y',1,0) sit_exempt_count
2028: ,decode(str.sui_exempt,'Y',1,0) sui_exempt_count
2029: ,decode(str.sdi_exempt,'Y',1,0) sdi_exempt_count
2030: ,decode(str.wc_exempt,'Y',1,0) wc_exempt_count
2031: from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
2032: pay_us_state_tax_info_f stif, pay_us_states pus,
2033: hr_soft_coding_keyflex hsck
2034: where paf.person_id = ln_person_id
2035: and paf.assignment_id = curvar_assignment_id
2902: stif.sta_information7,
2903: /*nvl(stif.sta_information9,'N') : Bug 6346579 */
2904: nvl(stif.sta_information9,'Y')
2905: from pay_us_emp_state_tax_rules_f str,
2906: per_assignments_f paf,
2907: pay_us_state_tax_info_f stif,
2908: pay_us_states pus,
2909: hr_soft_coding_keyflex hsck
2910: where paf.person_id = p_person_id
3018: ppf.employee_number,
3019: ppf.national_identifier,
3020: ppf.full_name
3021: from per_people_f ppf,
3022: per_assignments_f paf
3023: where ppf.person_id = p_person_id
3024: and paf.person_id = ppf.person_id
3025: and paf.assignment_type = 'E'
3026: and paf.primary_flag = 'Y'