[Home] [Help]
13: FUNCTION get_bus_group_id (p_asg_id IN NUMBER) RETURN NUMBER IS
14: --
15: CURSOR get_bus_group_id IS
16: SELECT business_group_id
17: FROM per_all_assignments_f
18: WHERE assignment_id = p_asg_id;
19: --
20: l_bus_group_id NUMBER;
21: BEGIN
290: l_ext_start_date DATE;
291:
292: CURSOR csr_asg_start_date IS
293: SELECT greatest(min(paf.effective_start_date),l_ext_start_date)
294: FROM per_all_assignments_f paf
295: WHERE paf.assignment_id = p_assignment_id;
296: BEGIN
297: hr_utility.set_location('get_prim_asg_start_date',1);
298: --
325: l_ext_end_date DATE;
326:
327: CURSOR csr_asg_end_date IS
328: select least(max(paf.effective_end_date),l_ext_end_date)
329: from per_all_assignments_f paf,
330: per_assignment_status_types past
331: where paf.assignment_id = p_assignment_id
332: and past.per_system_status = 'ACTIVE_ASSIGN'
333: and paf.assignment_status_type_id = past.assignment_status_type_id;
974: l_asg_include VARCHAR2(1) := 'N';
975: --
976: CURSOR get_asg_eff_dates(p_asg_id IN NUMBER) IS
977: SELECT min(effective_start_date) min_start_date, max(effective_end_date) max_end_Date
978: FROM per_all_assignments_f
979: WHERE assignment_id = p_asg_id;
980: --
981: l_min_start_date DATE;
982: l_max_end_date DATE;
983: --
984: CURSOR get_asg_details(p_asg_id IN NUMBER) IS
985: SELECT pp.payroll_id, asg.person_id, pp.consolidation_set_id, flex.segment1 tax_dist
986: FROM pay_all_payrolls_f pp,
987: per_all_assignments_f asg,
988: hr_soft_coding_keyflex flex
989: WHERE asg.assignment_id = p_asg_id
990: AND asg.payroll_id = pp.payroll_id
991: AND ben_ext_person.g_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
996: CURSOR check_asg_set_include(p_asg_id IN NUMBER) IS
997: SELECT 'Y' include_flag
998: FROM hr_assignment_set_amendments hasa,
999: hr_assignment_sets has,
1000: per_all_assignments_f paaf
1001: WHERE has.assignment_set_id = l_ext_asg_set_id
1002: AND paaf.assignment_id = p_asg_id
1003: -- AND ben_ext_person.g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1004: AND has.assignment_set_id = hasa.assignment_set_id (+)
1110: l_person_include VARCHAR2(1);
1111: --
1112: CURSOR get_all_asg IS
1113: SELECT distinct paa1.assignment_id
1114: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1115: WHERE paa2.assignment_id = p_assignment_id
1116: AND paa2.person_id = paa1.person_id
1117: ORDER BY paa1.assignment_id;
1118: --
1478: PROCEDURE process_sec_asg(p_asg_id IN NUMBER) IS
1479: --
1480: CURSOR get_sec_asg IS
1481: SELECT distinct paa1.assignment_id
1482: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1483: WHERE paa2.assignment_id = p_asg_id
1484: AND paa2.person_id = paa1.person_id
1485: AND nvl(paa1.primary_flag, 'N') = 'N';
1486: --
1513: PROCEDURE process_term_primary_asg(p_asg_id IN NUMBER) IS
1514: --
1515: CURSOR get_term_primary_asg IS
1516: SELECT distinct paa1.assignment_id
1517: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1518: WHERE paa2.assignment_id = p_asg_id
1519: AND paa2.person_id = paa1.person_id
1520: AND paa1.effective_end_date < paa2.effective_start_date
1521: AND nvl(paa1.primary_flag, 'Y') = 'Y';
3006: PROCEDURE process_sec_asg_04(p_asg_id IN NUMBER) IS
3007: --
3008: CURSOR get_sec_asg IS
3009: SELECT distinct paa1.assignment_id
3010: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
3011: WHERE paa2.assignment_id = p_asg_id
3012: AND paa2.assignment_id <> paa1.assignment_id
3013: AND paa2.person_id = paa1.person_id
3014: AND nvl(paa1.primary_flag, 'N') = 'N';
3041: PROCEDURE process_term_primary_asg_04(p_asg_id IN NUMBER) IS
3042: --
3043: CURSOR get_term_primary_asg IS
3044: SELECT distinct paa1.assignment_id
3045: FROM per_all_assignments_f paa1, per_all_assignments_f paa2
3046: WHERE paa2.assignment_id = p_asg_id
3047: AND paa2.assignment_id <> paa1.assignment_id
3048: AND paa2.person_id = paa1.person_id
3049: AND paa1.effective_end_date < paa2.effective_start_date