[Home] [Help]
39: /* Getting all Terminated Assignments for the Person concerned */
40:
41: cursor c_get_assignments(p_person_id in number) is
42: select distinct assignment_id
43: from per_assignments_f paf_o,
44: per_assignment_status_types past
45: where paf_o.person_id = p_person_id
46: and paf_o.assignment_status_type_id = past.assignment_status_type_id
47: and past.per_system_status = 'TERM_ASSIGN'
51: and past.legislation_code = 'US')
52: OR (past.legislation_code is null
53: and exists
54: (select 'x'
55: from per_assignments_f paf_a
56: where paf_a.assignment_id = paf_o.assignment_id
57: and paf_a.business_group_id = past.business_group_id)
58: )
59: )
57: and paf_a.business_group_id = past.business_group_id)
58: )
59: )
60: and paf_o.effective_end_date = (select max(paf_i.effective_end_date)
61: from per_assignments_f paf_i
62: where paf_i.assignment_id = paf_o.assignment_id) ;
63:
64: -- Fetching Federal Tax Rules details the latest record that needs to be
65: -- end dated as of New FPD
133: CURSOR c_compl_pay_act(p_new_final_process_date in date) IS
134: SELECT NULL
135: FROM pay_payroll_actions ppa
136: ,pay_assignment_actions paa
137: ,per_assignments_f asg
138: ,per_periods_of_service pds
139: WHERE pds.period_of_service_id = p_period_of_service_id
140: AND pds.person_id = asg.person_id
141: AND asg.period_of_service_id = pds.period_of_service_id
147:
148: l_person_id per_people_f.person_id%type ;
149: l_old_fpd per_periods_of_service.final_process_date%type ;
150: l_final_process_date per_periods_of_service.final_process_date%type ;
151: l_assignment_id per_assignments_f.assignment_id%type ;
152: l_emp_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%type ;
153: l_emp_st_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%type ;
154: l_emp_county_tax_rule_id pay_us_emp_county_tax_rules_f.emp_county_tax_rule_id%type ;
155: l_emp_city_tax_rule_id pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%type ;