[Home] [Help]
29: begin
30: select pos.actual_termination_date
31: into l_actual_termination_date
32: from per_periods_of_service pos,
33: per_assignments_f pa
34: where pos.person_id = pa.person_id
35: and pa.assignment_id = p_assignment_id
36: and p_session_date
37: between pa.effective_start_date
94: asg.effective_end_date
95: into v_payroll_id,
96: v_asg_effective_start_date,
97: v_asg_effective_end_date
98: from per_assignments_f asg
99: where asg.assignment_id = p_assignment_id
100: and asg.payroll_id is not null
101: and p_session_date between asg.effective_start_date
102: and asg.effective_end_date;
149: begin
150: --
151: select asg.effective_start_date
152: into v_start_date
153: from per_assignments_f asg
154: where asg.assignment_id = p_assignment_id
155: and asg.effective_end_date = v_asg_effective_start_date - 1
156: and asg.assignment_type = 'E' ;
157:
182: begin
183: --
184: select asg.effective_end_date
185: into v_end_date
186: from per_assignments_f asg
187: where asg.assignment_id = p_assignment_id
188: and asg.effective_start_date - 1 = v_asg_effective_end_date
189: and asg.assignment_type = 'E' ;
190:
368: -- calculate the element entry start date according to the qualifying age
369: -- condition
370: select add_months(p.date_of_birth, (l_qualifying_age * 12))
371: from per_all_people_f p,
372: per_assignments_f asg
373: where p.person_id = asg.person_id
374: and p.date_of_birth is not null
375: and asg.assignment_id = p_assignment_id
376: -- session_date comparison has been removed, so ensure that
615: -- Return a row if a match is found between the element link criteria
616: -- and the attributes of the assignment as at the effective date
617: --
618: select 'Y' ROW_RETURNED
619: from per_assignments_f ASG,
620: pay_element_links_f PEL
621: where lpi_effective_date between pel.effective_start_date
622: and pel.effective_end_date
623: and lpi_effective_date between asg.effective_start_date
776: is
777: --
778: cursor csr_sec_asg(p_asg_id number, p_session_date date) is
779: select 1
780: from per_assignments_f
781: where assignment_id = p_asg_id
782: and p_session_date between effective_start_date and effective_end_date;
783: --
784: v_asg_visible number;
881: -- ALWAYS return a row
882:
883: select min (paf.effective_start_date) ASGT_START,
884: max (paf.effective_end_date) ASGT_END
885: from per_assignments_f paf
886: where paf.assignment_id = P_ASSIGNMENT_ID
887: and paf.assignment_type in ('E','B','C') ; -- Added assignment_type 'C' in the check for bug 8792107
888: -- Added assignment_type 'B' in the check for bug 8371393
889: --and paf.assignment_type = 'E' ; -- Added assignment_type check for bug 7648259
890: --
891: cursor csr_minimum (p_assignment_id number, lpi_session_date date, p_time_period_start_date date) is
892: --
893: select asg1.effective_end_date
894: from per_assignments_f asg1
895: where asg1.assignment_id = p_assignment_id
896: -- Removed the following predicate as it is redundant
897: -- i.e. If assignment end date is less than session date then it
898: -- follows that the assignment start date must be less than
906: order by asg1.effective_end_date desc;
907: --
908: cursor csr_maximum (p_assignment_id number, lpi_session_date date, p_time_period_end_date date) is
909: select asg1.effective_start_date
910: from per_assignments_f asg1
911: where asg1.assignment_id = p_assignment_id
912: and asg1.effective_end_date >= lpi_session_date
913: -- Bugfix 5135065
914: -- Exclude any pieces of the assignment that start after the time
1292: -- a value_error would have been raised by the assert_condition call
1293: -- in the main body code otherwise.
1294: --
1295: select assignment_number
1296: from per_assignments_f
1297: where assignment_id = P_ASSIGNMENT_ID
1298: and lpi_session_date between effective_start_date
1299: and effective_end_date;
1300: --
1454: v_primary_flag,
1455: v_actual_termination_date,
1456: v_last_standard_process_date,
1457: v_final_process_date
1458: from per_assignments_f asg,
1459: per_periods_of_service pos
1460: where asg.assignment_id = p_assignment_id
1461: and asg.period_of_service_id = pos.period_of_service_id (+)
1462: and p_session_date between asg.effective_start_date
1639: end if;
1640: begin
1641: select min(asg.effective_start_date)
1642: into v_asg_term_date
1643: from per_assignments_f asg,
1644: per_assignment_status_types ast
1645: where asg.assignment_id = p_assignment_id
1646: and asg.effective_end_date >= p_session_date
1647: and asg.assignment_status_type_id = ast.assignment_status_type_id
1659: --
1660: begin
1661: select asg.effective_start_date
1662: into v_asg_term_date
1663: from per_assignments_f asg,
1664: per_assignment_status_types ast
1665: where asg.assignment_id = p_assignment_id
1666: and asg.effective_end_date >= p_session_date
1667: and asg.assignment_status_type_id = ast.assignment_status_type_id
1710: begin
1711: select ptp.end_date
1712: into v_element_term_rule_date
1713: from per_time_periods ptp,
1714: per_assignments_f asg
1715: where asg.assignment_id = p_assignment_id
1716: and v_asg_term_date between asg.effective_start_date and asg.effective_end_date
1717: and asg.payroll_id is not null
1718: and ptp.payroll_id = asg.payroll_id
1730: end if;
1731: begin
1732: select max(asg.effective_end_date)
1733: into v_element_term_rule_date
1734: from per_assignments_f asg
1735: where asg.assignment_id = p_assignment_id
1736: and asg.effective_start_date >= v_asg_term_date;
1737: end;
1738: elsif (v_post_termination_rule = 'L') then
1743: begin
1744: select ptp.end_date
1745: into v_element_term_rule_date
1746: from per_time_periods ptp,
1747: per_assignments_f asg
1748: where asg.assignment_id = p_assignment_id
1749: and v_asg_term_date
1750: between asg.effective_start_date
1751: and asg.effective_end_date
1776:
1777: begin
1778: select max(asg.effective_end_date)
1779: into v_element_term_rule_date
1780: from per_assignments_f asg
1781: where asg.assignment_id = p_assignment_id
1782: and asg.effective_start_date >= v_asg_term_date;
1783: end;
1784: --
2399: p_validation_end_date date
2400: ) is
2401: select tp.status
2402: from per_time_periods tp,
2403: per_assignments_f asg
2404: where asg.assignment_id = p_assignment_id
2405: and asg.payroll_id is not null
2406: and asg.effective_start_date <= p_validation_end_date
2407: and asg.effective_end_date >= p_validation_start_date
3335:
3336: select min(pos.actual_termination_date)
3337: into v_actual_termination_date
3338: from per_periods_of_service pos,
3339: per_assignments_f pa
3340: where pos.person_id = pa.person_id
3341: and pa.assignment_id = p_assignment_id
3342: and p_session_date
3343: between pa.effective_start_date
4004: end if;
4005: --
4006: select business_group_id
4007: into v_bg_id
4008: from per_assignments_f pas
4009: where pas.assignment_id = v_assignment_id
4010: and p_session_date between pas.effective_start_date
4011: and pas.effective_end_date
4012: and rownum=1;
5306: where paa.assignment_action_id =
5307: (select aa.assignment_action_id
5308: from pay_assignment_actions aa,
5309: pay_payroll_actions pa,
5310: per_assignments_f asg,
5311: per_time_periods tim,
5312: pay_payrolls_f pp
5313: where pa.effective_date =
5314: (select max(pa2.effective_date)
6061: into l_payroll_id,
6062: l_start_date,
6063: l_end_date
6064: from per_time_periods t,
6065: per_assignments_f a
6066: where a.assignment_id = p_assignment_id
6067: and p_session_date
6068: between a.effective_start_date
6069: and a.effective_end_date