[Home] [Help]
649: CURSOR c_get_end_date(v_assignment_id NUMBER)
650: IS
651: SELECT LEAST(NVL(pps.actual_termination_date,p_calculation_date), p_calculation_date)
652: FROM per_periods_of_service pps
653: ,per_assignments_f paf
654: WHERE paf.assignment_id = v_assignment_id
655: AND paf.period_of_service_id = pps.period_of_service_id;
656:
657: BEGIN
1826: pay_element_entry_values_f pev,
1827: pay_input_values_f piv,
1828: pay_accrual_plans pap,
1829: hr_lookups hrl,
1830: per_assignments_f asg,
1831: per_periods_of_service pps
1832: WHERE pev.element_entry_id = pee.element_entry_id
1833: AND pap.accrual_plan_element_type_id = piv.element_type_id
1834: AND piv.input_value_id = pev.input_value_id
1924: select a.effective_start_date
1925: , a.effective_end_date
1926: , a.normal_hours
1927: , a.frequency
1928: from per_assignments_f a
1929: where a.assignment_id = p_assignment_id
1930: and a.effective_start_date <= p_end_date
1931: and a.effective_end_date >= p_start_date
1932: order by
1949: cursor c_periods (p_assignment_id number
1950: ,p_start_date date
1951: ,p_end_date date) is
1952: select greatest(tp.start_date,paf.effective_start_date) start_date,least(tp.end_date,paf.effective_end_date) end_date
1953: from per_time_periods tp,per_assignments_f paf
1954: where paf.assignment_id = p_assignment_id
1955: and tp.payroll_id = paf.payroll_id
1956: and tp.start_date <= paf.effective_end_date
1957: and tp.end_date >= paf.effective_start_date
2290: NVL(SUM(1+
2291: LEAST(effective_end_date, c_end_date)
2292: - GREATEST(effective_start_date, c_start_date)),0)
2293: FROM
2294: per_assignments_f asg
2295: ,per_assignment_status_types t
2296: WHERE
2297: assignment_id = c_assignment_id
2298: AND t.assignment_status_type_id = asg.assignment_status_type_id
2690: from pay_accrual_plans pap,
2691: pay_element_types_f pet,
2692: pay_element_links_f pel,
2693: pay_element_entries_f pee,
2694: per_assignments_f paf,
2695: per_periods_of_service pps
2696: where pee.element_link_id = pel.element_link_id
2697: and pel.element_type_id = pet.element_type_id
2698: and pet.element_type_id = pap.accrual_plan_element_type_id