[Home] [Help]
17: --
18: cursor supervisor
19: is
20: select 'Y'
21: from per_assignments_f paf
22: where paf.assignment_type in ('E','C')
23: and paf.supervisor_id = p_person_id
24: and p_new_start_date > paf.effective_start_date
25: and paf.effective_end_date >= p_old_start_date
306: select 'y' from dual where exists
307: (SELECT NULL
308: FROM pay_payroll_actions pac,
309: pay_assignment_actions act,
310: per_assignments_f asg
311: WHERE asg.person_id = p_person_id
312: AND act.assignment_id = asg.assignment_id
313: AND asg.assignment_type = p_type
314: AND pac.payroll_action_id = act.payroll_action_id
411: -- only need to test the first assignment row
412: --
413: cursor assignment_pds is
414: select a.payroll_id , a.supervisor_id
415: from per_assignments_f a,
416: per_periods_of_service p
417: where a.period_of_service_id = p.period_of_service_id
418: and p.period_of_service_id = p_pds_or_pdp_id
419: and p_type= 'E'
420: and p.date_start = a.effective_start_date;
421: --
422: cursor assignment_pdp is
423: select a.payroll_id , a.supervisor_id
424: from per_assignments_f a,
425: per_periods_of_placement p
426: where a.period_of_placement_date_start = p.date_start
427: and a.person_id = p.person_id
428: and p.period_of_placement_id = p_pds_or_pdp_id
488: select 'x' from dual where exists
489: (select 1
490: from per_spinal_point_placements_f sp,
491: per_periods_of_service p,
492: per_assignments_f a
493: where a.person_id = p_person_id
494: and a.period_of_service_id = p.period_of_service_id
495: and p.period_of_service_id = p_pds_or_pdp_id
496: and p_type = 'E'
516: select 'x' from dual where exists
517: (select 1
518: from per_spinal_point_placements_f sp,
519: per_periods_of_placement p,
520: per_assignments_f a
521: where a.person_id = p_person_id
522: and a.period_of_placement_date_start = p.date_start
523: and p.period_of_placement_id = p_pds_or_pdp_id
524: and p_type = 'C'
580: select 'x' from dual where exists
581: (select 1
582: from pay_grade_rules_f pgr,
583: per_periods_of_placement p,
584: per_assignments_f a
585: where a.person_id = p_person_id
586: and a.period_of_placement_date_start = p.date_start
587: and p.period_of_placement_id = p_pds_or_pdp_id
588: and a.assignment_type = p_type
635: select 'x' from dual where exists
636: (select 1
637: from PAY_COST_ALLOCATIONS_F ca,
638: per_periods_of_service p,
639: per_assignments_f a
640: where a.person_id = p_person_id
641: and a.period_of_service_id = p.period_of_service_id
642: and p.period_of_service_id = p_pds_or_pdp_id
643: and p_type = 'E'
662: select 'x' from dual where exists
663: (select 1
664: from PAY_COST_ALLOCATIONS_F ca,
665: per_periods_of_placement p,
666: per_assignments_f a
667: where a.person_id = p_person_id
668: and a.period_of_placement_date_start = p.date_start
669: and p.period_of_placement_id = p_pds_or_pdp_id
670: and p_type = 'C'
726: select 'x' from dual where exists
727: (select 1
728: from per_assignment_budget_values_f bud,
729: per_periods_of_service p,
730: per_assignments_f a
731: where a.person_id = p_person_id
732: and a.period_of_service_id = p.period_of_service_id
733: and p.period_of_service_id = p_pds_or_pdp_id
734: and p_type = 'E'
753: select 'x' from dual where exists
754: (select 1
755: from per_assignment_budget_values_f bud,
756: per_periods_of_placement p,
757: per_assignments_f a
758: where a.person_id = p_person_id
759: and a.period_of_placement_date_start = p.date_start
760: and p.period_of_placement_id = p_pds_or_pdp_id
761: and p_type = 'C'
899: cursor csr_asg_status is
900: select 'x' from dual where exists
901: (select 1
902: from per_assignments a
903: ,per_assignments_f f
904: where f.effective_start_date between p_earlier_date and p_later_date
905: and f.effective_start_date <> p_old_start_date
906: and f.assignment_id = a.assignment_id
907: and a.assignment_type = p_type
934: ,p_old_start_date DATE) is
935: cursor csr_asg_change is
936: select 'x' from dual where exists
937: (select 1
938: from per_assignments_f f
939: where f.effective_start_date between p_earlier_date and p_later_date
940: and f.effective_start_date <> p_old_start_date
941: and f.person_id = p_person_id);
942: --
965: ,p_new_start_date DATE) is
966: cursor csr_prev_asg is
967: select 'x' from dual where exists
968: (select 1
969: from per_assignments_f f
970: where f.effective_start_date >= p_new_start_date
971: and f.effective_start_date < p_old_start_date
972: and f.assignment_type =p_type
973: and f.person_id = p_person_id);
1019: pay_element_links_f el,
1020: pay_element_types_f et
1021: where ee.assignment_id in
1022: (select assignment_id
1023: from per_assignments_f asg
1024: where asg.person_id = p_person_id
1025: and asg.effective_start_date between l_earlier_date and l_later_date)
1026: and ee.element_link_id = el.element_link_id
1027: and el.element_type_id = et.element_type_id
1360: cursor csr_ssp is
1361: select placement_id
1362: from per_spinal_point_placements_f sp
1363: where assignment_id in (select a.assignment_id
1364: from per_assignments_f a
1365: where person_id = p_person_id
1366: and a.assignment_type = p_type
1367: and a.effective_start_date = p_old_start_date)
1368: and sp.effective_start_date = p_old_start_date;
1398: cursor csr_rate is
1399: select grade_rule_id
1400: from pay_grade_rules_f pgr
1401: where grade_or_spinal_point_id in (select a.assignment_id
1402: from per_assignments_f a
1403: where person_id = p_person_id
1404: and a.assignment_type = p_type
1405: and a.effective_start_date = p_old_start_date)
1406: and pgr.rate_type = 'A'
1437: cursor csr_cost is
1438: select COST_ALLOCATION_ID
1439: from PAY_COST_ALLOCATIONS_F pca
1440: where assignment_id in (select a.assignment_id
1441: from per_assignments_f a
1442: where person_id = p_person_id
1443: and a.assignment_type = p_type
1444: and a.effective_start_date = p_old_start_date)
1445: and pca.effective_start_date = p_old_start_date;
1475: cursor csr_abv is
1476: select abv.assignment_budget_value_id
1477: from per_assignment_budget_values_f abv
1478: where assignment_id in (select a.assignment_id
1479: from per_assignments_f a
1480: where person_id = p_person_id
1481: and a.assignment_type = p_type
1482: and a.effective_start_date = p_old_start_date)
1483: and abv.effective_start_date = p_old_start_date;
1562: open csr_apl_asg;
1563: loop
1564: fetch csr_apl_asg into l_assignment_id;
1565: exit when csr_apl_asg%NOTFOUND;
1566: update per_assignments_f a
1567: set a.effective_end_date = p_new_start_date - 1
1568: where a.effective_end_date =
1569: (select max(a2.effective_end_date)
1570: from per_assignments_f a2
1566: update per_assignments_f a
1567: set a.effective_end_date = p_new_start_date - 1
1568: where a.effective_end_date =
1569: (select max(a2.effective_end_date)
1570: from per_assignments_f a2
1571: where a2.assignment_id = a.assignment_id
1572: and a2.assignment_type = 'A')
1573: and a.assignment_id = l_assignment_id;
1574: if sql%rowcount <1 then
1680: select pay_proposal_id
1681: from per_pay_proposals
1682: where change_date = p_old_start_date
1683: and exists (select 1
1684: from per_assignments_f
1685: where person_id = p_person_id
1686: and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
1687: -- and primary_flag = 'Y'
1688: and effective_start_date = p_new_start_date
1682: where change_date = p_old_start_date
1683: and exists (select 1
1684: from per_assignments_f
1685: where person_id = p_person_id
1686: and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
1687: -- and primary_flag = 'Y'
1688: and effective_start_date = p_new_start_date
1689: and assignment_type = p_type);
1690: --
1694: from per_pay_proposals
1695: where change_date <= p_new_start_date
1696: and assignment_id =
1697: (select assignment_id
1698: from per_assignments_f
1699: where person_id = p_person_id
1700: and primary_flag = 'Y'
1701: and effective_start_date = p_new_start_date
1702: and assignment_type = p_type);
1707: select pay_proposal_id
1708: from per_pay_proposals
1709: where Last_change_date = p_old_start_date
1710: and exists (select 1
1711: from per_assignments_f
1712: where person_id = p_person_id
1713: and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
1714: and effective_start_date = p_new_start_date
1715: and assignment_type = p_type);
1709: where Last_change_date = p_old_start_date
1710: and exists (select 1
1711: from per_assignments_f
1712: where person_id = p_person_id
1713: and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
1714: and effective_start_date = p_new_start_date
1715: and assignment_type = p_type);
1716: -- end changes for bug 8267359
1717:
1760: where change_date = p_old_start_date
1761: and pay_proposal_id = l_pay_proposal_id;
1762: /* and assignment_id =
1763: (select assignment_id
1764: from per_assignments_f
1765: where person_id = p_person_id
1766: and primary_flag = 'Y'
1767: and effective_start_date = p_new_start_date
1768: and assignment_type = p_type);
1925: -- select all the assignments for the person_id starting on the Hire date.
1926: --
1927: Cursor csr_assignments(p_person_id number, p_new_effective_date date) IS
1928: select distinct paf.assignment_id
1929: from per_assignments_f paf
1930: where paf.person_id = p_person_id
1931: and paf.date_probation_end is not null
1932: and paf.effective_start_date = p_new_effective_date;
1933: --
1934: -- select any datetrack updations on given Assignment_id.
1935: --
1936: Cursor csr_asg_updates(p_assignment_id number, p_new_effective_date date) IS
1937: select paf.effective_start_date
1938: from per_assignments_f paf
1939: where paf.assignment_id = p_assignment_id
1940: and paf.effective_start_date >= p_new_effective_date
1941: order by paf.effective_start_date;
1942: --
1945: Cursor csr_probation_details(p_assignment_id number, p_effective_start_date date) IS
1946: select paf.date_probation_end
1947: ,paf.probation_period
1948: ,paf.probation_unit
1949: from per_assignments_f paf
1950: where paf.assignment_id = p_assignment_id
1951: and paf.effective_start_date = p_effective_start_date;
1952: --
1953: -- local variables.
2026: --
2027: else
2028: --
2029: hr_utility.set_location('date probation end is updated', 60);
2030: update per_assignments_f paf
2031: set paf.date_probation_end = l_new_date_probation_end
2032: where paf.assignment_id = l_assignment_id
2033: and paf.effective_start_date = l_effective_start_date;
2034: --
2059: --
2060: cursor csr_extra_details IS
2061: select 'Y'
2062: from pqp_assignment_attributes_f paa
2063: ,per_assignments_f paf
2064: where paf.person_id = p_person_id
2065: and paa.assignment_id = paf.assignment_id
2066: and paf.effective_start_date = trunc(p_old_start_date)
2067: and ( paa.effective_start_date between p_old_start_date+1 and p_new_start_date
2862: from pay_personal_payment_methods_f p
2863: where p.effective_start_date >= l_old_start_date
2864: and exists
2865: (select 1
2866: from per_assignments_f a
2867: where p.assignment_id = a.assignment_id
2868: and a.assignment_type = p_update_type
2869: and a.person_id = p_person_id) order by p.effective_start_date)
2870: where rownum=1);
2874: where p.effective_start_date = l_old_start_date
2875: and p.effective_end_date >= l_new_start_date
2876: and exists
2877: (select 1
2878: from per_assignments_f a
2879: where p.assignment_id = a.assignment_id
2880: and a.assignment_type = p_update_type
2881: and a.person_id = p_person_id);
2882: --
2916: --
2917: --first update ALL matching cases of the period_of_placement_date_start for integrity
2918: --
2919: if p_update_type='C' then
2920: update per_assignments_f a
2921: set a.period_of_placement_date_start = l_new_start_date
2922: where a.period_of_placement_date_start = l_old_start_date
2923: and a.assignment_type = p_update_type
2924: and a.person_id = p_person_id;
2928: hr_utility.raise_error;
2929: end if;
2930: end if;
2931: --
2932: update per_assignments_f a
2933: set a.effective_start_date = l_new_start_date
2934: where a.effective_start_date = l_old_start_date
2935: and a.assignment_type = p_update_type
2936: and a.person_id = p_person_id;
2943: -- 115.32 115.34 (START)
2944: --
2945: -- Update the EED for ASG records immediately before the updated ESD
2946: --
2947: UPDATE per_assignments_f A
2948: SET A.effective_end_date = (l_new_start_date - 1)
2949: WHERE A.effective_end_date = (l_old_start_date - 1)
2950: AND (l_new_start_date - 1) >= A.effective_start_date
2951: AND A.assignment_type = p_update_type
2973: set paa.effective_start_date = l_new_start_date
2974: where paa.effective_start_date = l_old_start_date
2975: and paa.assignment_id in
2976: (select paf.assignment_id
2977: from per_assignments_f paf
2978: where paf.effective_start_date = l_new_start_date
2979: and paf.person_id = p_person_id);
2980: --
2981: -- Fix for bug 3738058 ends here.