[Home] [Help]
56: --
57: l_dummy VARCHAR2(1);
58: --
59: cursor csr_pds_exists is
60: select 'y' from dual where exists
61: (select 'x'
62: from per_periods_of_service pds
63: where pds.person_id = p_person_id
64: and pds.date_start = p_old_start_date);
63: where pds.person_id = p_person_id
64: and pds.date_start = p_old_start_date);
65: --
66: cursor csr_pdp_exists is
67: select 'y' from dual where exists
68: (select 'x'
69: from per_periods_of_placement pdp
70: where pdp.person_id = p_person_id
71: and pdp.date_start = p_old_start_date);
124: --
125: l_dummy VARCHAR2(1);
126: --
127: cursor csr_pds_exists is
128: select 'y' from dual where exists
129: (select 'x'
130: from per_periods_of_service pds
131: where pds.person_id = p_person_id
132: and pds.actual_termination_date < p_new_start_date
132: and pds.actual_termination_date < p_new_start_date
133: and pds.final_process_date >= p_new_start_date);
134: --
135: cursor csr_pdp_exists is
136: select 'y' from dual where exists
137: (select 'x'
138: from per_periods_of_placement pdp
139: where pdp.person_id = p_person_id
140: and pdp.actual_termination_date < p_new_start_date
170: AND p_new_start_date BETWEEN pds.actual_termination_date
171: AND pds.final_process_date;
172: -- Fix for 10124468
173: cursor csr_pdp_overlap is
174: select 'y' from dual where exists
175: (select 'x'
176: from per_periods_of_placement pdp
177: where pdp.person_id = p_person_id
178: and p_new_start_date between pdp.date_start
302: -- Bug 4221947. In below cursor, check the payroll actions in between old start
303: -- date and one day before new start date.
304: --
305: cursor csr_compl_actions is
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
442: --
443: if l_payroll_id is not null then --currently always null for p_type = C
444: begin
445: select '1' into l_temp
446: from dual
447: where exists ( select payroll_id
448: from pay_payrolls_f
449: where payroll_id = l_payroll_id
450: and p_new_start_date between
458: end if;
459: if l_supervisor_id is not null then
460: begin
461: select '1' into l_temp
462: from dual
463: where exists ( select person_id
464: from per_all_people_f -- Fix 3562224
465: where person_id = l_supervisor_id
466: and ( current_employee_flag = 'Y' OR current_npw_flag = 'Y') -- fix for the bug 9100657
484: ,p_pds_or_pdp_id NUMBER
485: ,p_new_start_date DATE
486: ,p_type VARCHAR2) is
487: cursor csr_sp_placement_pds is
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
512: */
513: -- End changes for bug 9304366
514: --
515: cursor csr_sp_placement_pdp is
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
576: ,p_pds_or_pdp_id NUMBER
577: ,p_new_start_date DATE
578: ,p_type VARCHAR2) is
579: cursor csr_asg_rates_pdp is
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
631: ,p_pds_or_pdp_id NUMBER
632: ,p_new_start_date DATE
633: ,p_type VARCHAR2) is
634: cursor csr_cost_pds is
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
658: */
659: -- End changes for bug 9304366
660: --
661: cursor csr_cost_pdp is
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
722: ,p_pds_or_pdp_id NUMBER
723: ,p_new_start_date DATE
724: ,p_type VARCHAR2) is
725: cursor csr_budget_pds is
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
749: */
750: -- End changes for bug 9304366
751: --
752: cursor csr_budget_pdp is
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
813: ,p_earlier_date DATE
814: ,p_later_date DATE
815: ,p_old_start_date DATE) is
816: cursor csr_people_change is
817: select 'x' from dual where exists
818: (select 1
819: from per_people_f p
820: where p.effective_start_date between p_earlier_date and p_later_date
821: and p.effective_start_date <> p_old_start_date
863: ,p_earlier_date DATE
864: ,p_later_date DATE
865: ,p_old_start_date DATE) is
866: cursor csr_person_type_change is
867: select 'x' from dual where exists
868: (select 1
869: from per_person_type_usages_f ptu,
870: per_person_types pt
871: where ptu.person_id = p_person_id
896: ,p_later_date DATE
897: ,p_type VARCHAR2
898: ,p_old_start_date DATE) is
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
932: ,p_earlier_date DATE
933: ,p_later_date DATE
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
963: ,p_type VARCHAR2
964: ,p_old_start_date DATE
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
1011: end if;
1012: begin
1013: select 'Y'
1014: into l_warn
1015: from dual
1016: where exists
1017: (select null
1018: from pay_element_entries_f ee,
1019: pay_element_links_f el,