[Home] [Help]
54: FUNCTION get_bus_group_id (p_asg_id IN NUMBER) RETURN NUMBER IS
55: --
56: CURSOR get_bus_group_id IS
57: SELECT business_group_id
58: FROM per_all_assignments_f
59: WHERE assignment_id = p_asg_id;
60: --
61: l_bus_group_id NUMBER;
62:
441: l_mileage_balance NUMBER;
442: --
443: CURSOR get_asg_eff_dates(p_asg_id IN NUMBER) IS
444: SELECT min(effective_start_date) min_start_date, max(effective_end_date) max_end_date
445: FROM per_all_assignments_f
446: WHERE assignment_id = p_asg_id;
447: --
448: l_min_start_date DATE;
449: l_max_end_date DATE;
450: --
451: CURSOR get_asg_details(p_asg_id IN NUMBER) IS
452: SELECT pp.payroll_id, asg.person_id, pp.consolidation_set_id, flex.segment1 tax_dist
453: FROM pay_all_payrolls_f pp,
454: per_all_assignments_f asg,
455: hr_soft_coding_keyflex flex
456: WHERE asg.assignment_id = p_asg_id
457: AND asg.payroll_id = pp.payroll_id
458: AND ben_ext_person.g_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
463: CURSOR check_asg_set_include(p_asg_id IN NUMBER) IS
464: SELECT 'Y' include_flag
465: FROM hr_assignment_set_amendments hasa,
466: hr_assignment_sets has,
467: per_all_assignments_f paaf
468: WHERE has.assignment_set_id = l_ext_asg_set_id
469: AND paaf.assignment_id = p_asg_id
470: -- AND ben_ext_person.g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
471: AND has.assignment_set_id = hasa.assignment_set_id (+)
710:
711: cursor csr_ben_start_date is
712: select greatest(pps.date_start, l_tax_year_start)
713: from per_periods_of_service pps,
714: per_all_assignments_f paf
715: where paf.period_of_service_id = pps.period_of_service_id
716: and paf.assignment_id = p_assignment_id;
717:
718: begin
757: cursor csr_ben_end_date is
758: select least(nvl(pps.actual_termination_date,hr_general.end_of_time),
759: l_tax_year_end)
760: from per_periods_of_service pps,
761: per_all_assignments_f paf
762: where paf.period_of_service_id = pps.period_of_service_id
763: and paf.assignment_id = p_assignment_id;
764:
765: begin
801: l_tax_year_start date;
802:
803: cursor csr_asg_start_date is
804: select greatest(min(paf.effective_start_date),l_tax_year_start)
805: from per_all_assignments_f paf
806: where paf.assignment_id = p_assignment_id;
807:
808: begin
809:
845: l_tax_year_end date;
846:
847: cursor csr_asg_end_date is
848: select least(max(paf.effective_end_date),l_tax_year_end)
849: from per_all_assignments_f paf,
850: per_assignment_status_types past
851: where paf.assignment_id = p_assignment_id
852: and past.per_system_status = 'ACTIVE_ASSIGN'
853: and paf.assignment_status_type_id = past.assignment_status_type_id;
1277: PROCEDURE process_sec_asg(p_asg_id IN NUMBER) IS
1278: --
1279: CURSOR csr_sec_asg IS
1280: SELECT distinct paa1.assignment_id
1281: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1282: WHERE paa2.assignment_id = p_asg_id
1283: AND paa2.person_id = paa1.person_id
1284: AND nvl(paa1.primary_flag, 'N') = 'N';
1285: --
1355: PROCEDURE process_term_primary_asg(p_asg_id IN NUMBER) IS
1356: --
1357: CURSOR csr_term_prim_asg IS
1358: SELECT distinct paa1.assignment_id
1359: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1360: WHERE paa2.assignment_id = p_asg_id
1361: AND paa2.person_id = paa1.person_id
1362: AND paa1.effective_end_date < paa2.effective_start_date
1363: -- AND paa1.period_of_service_id <> paa2.period_of_service_id
1574: l_person_include VARCHAR2(1);
1575: --
1576: CURSOR get_all_asg IS
1577: SELECT distinct paa1.assignment_id
1578: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1579: WHERE paa2.assignment_id = p_assignment_id
1580: AND paa2.person_id = paa1.person_id
1581: ORDER BY paa1.assignment_id;
1582: --