[Home] [Help]
47: --
48: l_dummy VARCHAR2(1);
49: --
50: cursor csr_pds_exists is
51: select 'y' from dual where exists
52: (select 'x'
53: from per_periods_of_service pds
54: where pds.person_id = p_person_id
55: and pds.date_start = p_old_start_date);
54: where pds.person_id = p_person_id
55: and pds.date_start = p_old_start_date);
56: --
57: cursor csr_pdp_exists is
58: select 'y' from dual where exists
59: (select 'x'
60: from per_periods_of_placement pdp
61: where pdp.person_id = p_person_id
62: and pdp.date_start = p_old_start_date);
115: --
116: l_dummy VARCHAR2(1);
117: --
118: cursor csr_pds_exists is
119: select 'y' from dual where exists
120: (select 'x'
121: from per_periods_of_service pds
122: where pds.person_id = p_person_id
123: and pds.actual_termination_date < p_new_start_date
123: and pds.actual_termination_date < p_new_start_date
124: and pds.final_process_date >= p_new_start_date);
125: --
126: cursor csr_pdp_exists is
127: select 'y' from dual where exists
128: (select 'x'
129: from per_periods_of_placement pdp
130: where pdp.person_id = p_person_id
131: and pdp.actual_termination_date < p_new_start_date
269: -- Bug 4221947. In below cursor, check the payroll actions in between old start
270: -- date and one day before new start date.
271: --
272: cursor csr_compl_actions is
273: select 'y' from dual where exists
274: (SELECT NULL
275: FROM pay_payroll_actions pac,
276: pay_assignment_actions act,
277: per_assignments_f asg
409: --
410: if l_payroll_id is not null then --currently always null for p_type = C
411: begin
412: select '1' into l_temp
413: from dual
414: where exists ( select payroll_id
415: from pay_payrolls_f
416: where payroll_id = l_payroll_id
417: and p_new_start_date between
425: end if;
426: if l_supervisor_id is not null then
427: begin
428: select '1' into l_temp
429: from dual
430: where exists ( select person_id
431: from per_all_people_f -- Fix 3562224
432: where person_id = l_supervisor_id
433: and current_employee_flag = 'Y'
451: ,p_pds_or_pdp_id NUMBER
452: ,p_new_start_date DATE
453: ,p_type VARCHAR2) is
454: cursor csr_sp_placement_pds is
455: select 'x' from dual where exists
456: (select 1
457: from per_spinal_point_placements_f sp,
458: per_periods_of_service p,
459: per_assignments_f a
466: -- and sp.effective_start_date < p_new_start_date); --update for bug 6021004
467: and sp.effective_start_date <= p_new_start_date);
468: --
469: cursor csr_sp_placement_pdp is
470: select 'x' from dual where exists
471: (select 1
472: from per_spinal_point_placements_f sp,
473: per_periods_of_placement p,
474: per_assignments_f a
517: ,p_pds_or_pdp_id NUMBER
518: ,p_new_start_date DATE
519: ,p_type VARCHAR2) is
520: cursor csr_asg_rates_pdp is
521: select 'x' from dual where exists
522: (select 1
523: from pay_grade_rules_f pgr,
524: per_periods_of_placement p,
525: per_assignments_f a
559: ,p_pds_or_pdp_id NUMBER
560: ,p_new_start_date DATE
561: ,p_type VARCHAR2) is
562: cursor csr_cost_pds is
563: select 'x' from dual where exists
564: (select 1
565: from PAY_COST_ALLOCATIONS_F ca,
566: per_periods_of_service p,
567: per_assignments_f a
573: and ca.effective_start_date > p.date_start
574: and ca.effective_start_date < p_new_start_date);
575: --
576: cursor csr_cost_pdp is
577: select 'x' from dual where exists
578: (select 1
579: from PAY_COST_ALLOCATIONS_F ca,
580: per_periods_of_placement p,
581: per_assignments_f a
624: ,p_pds_or_pdp_id NUMBER
625: ,p_new_start_date DATE
626: ,p_type VARCHAR2) is
627: cursor csr_budget_pds is
628: select 'x' from dual where exists
629: (select 1
630: from per_assignment_budget_values_f bud,
631: per_periods_of_service p,
632: per_assignments_f a
638: and bud.effective_start_date > p.date_start
639: and bud.effective_start_date < p_new_start_date);
640: --
641: cursor csr_budget_pdp is
642: select 'x' from dual where exists
643: (select 1
644: from per_assignment_budget_values_f bud,
645: per_periods_of_placement p,
646: per_assignments_f a
689: ,p_earlier_date DATE
690: ,p_later_date DATE
691: ,p_old_start_date DATE) is
692: cursor csr_people_change is
693: select 'x' from dual where exists
694: (select 1
695: from per_people_f p
696: where p.effective_start_date between p_earlier_date and p_later_date
697: and p.effective_start_date <> p_old_start_date
728: ,p_earlier_date DATE
729: ,p_later_date DATE
730: ,p_old_start_date DATE) is
731: cursor csr_person_type_change is
732: select 'x' from dual where exists
733: (select 1
734: from per_person_type_usages_f ptu,
735: per_person_types pt
736: where ptu.person_id = p_person_id
761: ,p_later_date DATE
762: ,p_type VARCHAR2
763: ,p_old_start_date DATE) is
764: cursor csr_asg_status is
765: select 'x' from dual where exists
766: (select 1
767: from per_assignments a
768: ,per_assignments_f f
769: where f.effective_start_date between p_earlier_date and p_later_date
797: ,p_earlier_date DATE
798: ,p_later_date DATE
799: ,p_old_start_date DATE) is
800: cursor csr_asg_change is
801: select 'x' from dual where exists
802: (select 1
803: from per_assignments_f f
804: where f.effective_start_date between p_earlier_date and p_later_date
805: and f.effective_start_date <> p_old_start_date
828: ,p_type VARCHAR2
829: ,p_old_start_date DATE
830: ,p_new_start_date DATE) is
831: cursor csr_prev_asg is
832: select 'x' from dual where exists
833: (select 1
834: from per_assignments_f f
835: where f.effective_start_date >= p_new_start_date
836: and f.effective_start_date < p_old_start_date
876: end if;
877: begin
878: select 'Y'
879: into l_warn
880: from dual
881: where exists
882: (select null
883: from pay_element_entries_f ee,
884: pay_element_links_f el,