[Home] [Help]
591: l_primary_only VARCHAR2(1);
592:
593: CURSOR c_excess_over_fed IS
594: select 'x'
595: from per_assignments_f paf,
596: pay_us_emp_fed_tax_rules_f ftr
597: where paf.person_id = p_person_id
598: and ftr.assignment_id = paf.assignment_id
599: and paf.assignment_type = 'E'
608: or nvl(ftr.fit_override_amount,0) <> 0);
609:
610: CURSOR c_excess_over_state IS
611: select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ 'x'
612: from per_assignments_f paf,
613: pay_us_emp_state_tax_rules_f str,
614: pay_us_state_tax_info_f stif
615: where paf.person_id = p_person_id
616: and paf.assignment_type = 'E'
630: );
631:
632: CURSOR c_future_fed_recs IS
633: select 'x'
634: from per_assignments_f paf,
635: pay_us_emp_fed_tax_rules_f ftr
636: where paf.person_id = p_person_id
637: and paf.assignment_type = 'E'
638: and ftr.assignment_id = paf.assignment_id
642: paf.effective_end_date;
643:
644: CURSOR c_future_state_recs IS
645: select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ 'x'
646: from per_assignments_f paf,
647: pay_us_emp_state_tax_rules_f str,
648: pay_us_state_tax_info_f stif
649: where paf.person_id = p_person_id
650: and str.assignment_id = paf.assignment_id
1602: IS
1603: ln_person_id per_people_f.person_id%TYPE;
1604: ln_business_group_id per_people_f.business_group_id%TYPE;
1605: ln_parent_audit_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
1606: ln_assignment_id per_assignments_f.assignment_id%TYPE;
1607: ln_gre_id hr_organization_units.organization_id%TYPE;
1608: ln_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
1609: ln_state_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
1610: ld_old_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1720: IS
1721: ln_person_id per_people_f.person_id%TYPE;
1722: ln_business_group_id per_people_f.business_group_id%TYPE;
1723: ln_parent_audit_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
1724: ln_assignment_id per_assignments_f.assignment_id%TYPE;
1725: ln_gre_id hr_organization_units.organization_id%TYPE;
1726: ln_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
1727: ln_state_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
1728: ld_old_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1751: lv_update_error_msg VARCHAR2(10000);
1752: e_no_records EXCEPTION;
1753: e_no_update_allowed EXCEPTION;
1754: e_date_error EXCEPTION;
1755: l_primary_flag per_assignments_f.primary_flag%TYPE;
1756:
1757: l_state_exempt varchar2(15);
1758:
1759: lv_filing_status_changed boolean := false;
1791: decode(ftr.fit_exempt,'Y',1,0),
1792: decode(ftr.futa_tax_exempt,'Y',1,0),
1793: decode(ftr.medicare_tax_exempt,'Y',1,0),
1794: decode(ftr.ss_tax_exempt,'Y',1,0)
1795: from pay_us_emp_fed_tax_rules_f ftr, per_assignments_f paf,
1796: hr_soft_coding_keyflex hsck
1797: where paf.person_id = ln_person_id
1798: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1799: and paf.assignment_id = ftr.assignment_id
1816: decode(ftr.futa_tax_exempt,'Y',1,0) futa_tax_exempt_count,
1817: decode(ftr.medicare_tax_exempt,'Y',1,0) medicare_tax_exempt_count,
1818: decode(ftr.ss_tax_exempt,'Y',1,0) ss_tax_exempt_count
1819: from pay_us_emp_fed_tax_rules_f ftr
1820: ,per_assignments_f paf
1821: ,hr_soft_coding_keyflex hsck
1822: where paf.person_id = ln_person_id
1823: and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1824: and paf.assignment_id = ftr.assignment_id
1833:
1834: l_fed_tax_rec c_fed_tax_rows%rowtype;
1835:
1836: /*
1837: CURSOR c_state_tax_rows(curvar_assignment_id per_assignments_f.assignment_id%TYPE) IS
1838: select str.emp_state_tax_rule_id,
1839: str.object_version_number,
1840: str.effective_start_date,
1841: str.filing_status_code,
1852: ,decode(str.sit_exempt,'Y',1,0)
1853: ,decode(str.sui_exempt,'Y',1,0)
1854: ,decode(str.sdi_exempt,'Y',1,0)
1855: ,decode(str.wc_exempt,'Y',1,0)
1856: from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
1857: pay_us_state_tax_info_f stif, pay_us_states pus,
1858: hr_soft_coding_keyflex hsck
1859: where paf.person_id = ln_person_id
1860: and paf.assignment_id = curvar_assignment_id
1873: str.effective_end_date
1874: for update nowait;
1875: */
1876:
1877: CURSOR c_state_tax_rows(curvar_assignment_id per_assignments_f.assignment_id%TYPE) IS
1878: select str.*
1879: ,pus.state_name
1880: ,stif.sta_information7 state_as_fed
1881: ,hsck.segment1 gre_id
1884: ,decode(str.sit_exempt,'Y',1,0) sit_exempt_count
1885: ,decode(str.sui_exempt,'Y',1,0) sui_exempt_count
1886: ,decode(str.sdi_exempt,'Y',1,0) sdi_exempt_count
1887: ,decode(str.wc_exempt,'Y',1,0) wc_exempt_count
1888: from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
1889: pay_us_state_tax_info_f stif, pay_us_states pus,
1890: hr_soft_coding_keyflex hsck
1891: where paf.person_id = ln_person_id
1892: and paf.assignment_id = curvar_assignment_id
2730: select pus.state_name,
2731: stif.sta_information7,
2732: nvl(stif.sta_information9,'N')
2733: from pay_us_emp_state_tax_rules_f str,
2734: per_assignments_f paf,
2735: pay_us_state_tax_info_f stif,
2736: pay_us_states pus,
2737: hr_soft_coding_keyflex hsck
2738: where paf.person_id = p_person_id
2837: ppf.employee_number,
2838: ppf.national_identifier,
2839: ppf.full_name
2840: from per_people_f ppf,
2841: per_assignments_f paf
2842: where ppf.person_id = p_person_id
2843: and paf.person_id = ppf.person_id
2844: and paf.assignment_type = 'E'
2845: and paf.primary_flag = 'Y'