[Home] [Help]
59: FUNCTION get_bus_group_id (p_asg_id IN NUMBER) RETURN NUMBER IS
60: --
61: CURSOR get_bus_group_id IS
62: SELECT business_group_id
63: FROM per_all_assignments_f
64: WHERE assignment_id = p_asg_id;
65: --
66: l_bus_group_id NUMBER;
67:
478: l_mileage_balance NUMBER;
479: --
480: CURSOR get_asg_eff_dates(p_asg_id IN NUMBER) IS
481: SELECT min(effective_start_date) min_start_date, max(effective_end_date) max_end_date
482: FROM per_all_assignments_f
483: WHERE assignment_id = p_asg_id;
484: --
485: l_min_start_date DATE;
486: l_max_end_date DATE;
487: --
488: CURSOR get_asg_details(p_asg_id IN NUMBER) IS
489: SELECT pp.payroll_id, asg.person_id, pp.consolidation_set_id, flex.segment1 tax_dist
490: FROM pay_all_payrolls_f pp,
491: per_all_assignments_f asg,
492: hr_soft_coding_keyflex flex
493: WHERE asg.assignment_id = p_asg_id
494: AND asg.payroll_id = pp.payroll_id
495: AND ben_ext_person.g_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
500: CURSOR check_asg_set_include(p_asg_id IN NUMBER) IS
501: SELECT 'Y' include_flag
502: FROM hr_assignment_set_amendments hasa,
503: hr_assignment_sets has,
504: per_all_assignments_f paaf
505: WHERE has.assignment_set_id = l_ext_asg_set_id
506: AND paaf.assignment_id = p_asg_id
507: -- AND ben_ext_person.g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
508: AND has.assignment_set_id = hasa.assignment_set_id (+)
749:
750: cursor csr_ben_start_date is
751: select greatest(pps.date_start, l_tax_year_start)
752: from per_periods_of_service pps,
753: per_all_assignments_f paf
754: where paf.period_of_service_id = pps.period_of_service_id
755: and paf.assignment_id = p_assignment_id;
756:
757: begin
796: cursor csr_ben_end_date is
797: select least(nvl(pps.actual_termination_date,hr_general.end_of_time),
798: l_tax_year_end)
799: from per_periods_of_service pps,
800: per_all_assignments_f paf
801: where paf.period_of_service_id = pps.period_of_service_id
802: and paf.assignment_id = p_assignment_id;
803:
804: begin
840: l_tax_year_start date;
841:
842: cursor csr_asg_start_date is
843: select greatest(min(paf.effective_start_date),l_tax_year_start)
844: from per_all_assignments_f paf
845: where paf.assignment_id = p_assignment_id;
846:
847: begin
848:
884: l_tax_year_end date;
885:
886: cursor csr_asg_end_date is
887: select least(max(paf.effective_end_date),l_tax_year_end)
888: from per_all_assignments_f paf,
889: per_assignment_status_types past
890: where paf.assignment_id = p_assignment_id
891: and past.per_system_status = 'ACTIVE_ASSIGN'
892: and paf.assignment_status_type_id = past.assignment_status_type_id;
1613: PROCEDURE process_sec_asg(p_asg_id IN NUMBER) IS
1614: --
1615: CURSOR csr_sec_asg IS
1616: SELECT distinct paa1.assignment_id
1617: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1618: WHERE paa2.assignment_id = p_asg_id
1619: AND paa2.person_id = paa1.person_id
1620: AND nvl(paa1.primary_flag, 'N') = 'N';
1621: --
1679: PROCEDURE process_term_primary_asg(p_asg_id IN NUMBER) IS
1680: --
1681: CURSOR csr_term_prim_asg IS
1682: SELECT distinct paa1.assignment_id
1683: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1684: WHERE paa2.assignment_id = p_asg_id
1685: AND paa2.person_id = paa1.person_id
1686: AND paa1.effective_end_date < paa2.effective_start_date
1687: -- AND paa1.period_of_service_id <> paa2.period_of_service_id
1902: l_person_include VARCHAR2(1);
1903: --
1904: CURSOR get_all_asg IS
1905: SELECT distinct paa1.assignment_id
1906: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1907: WHERE paa2.assignment_id = p_assignment_id
1908: AND paa2.person_id = paa1.person_id
1909: ORDER BY paa1.assignment_id;
1910: --