[Home] [Help]
26: begin
27: select pos.actual_termination_date
28: into l_actual_termination_date
29: from per_periods_of_service pos,
30: per_assignments_f pa
31: where pos.person_id = pa.person_id
32: and pa.assignment_id = p_assignment_id
33: and p_session_date
34: between pa.effective_start_date
89: asg.effective_end_date
90: into v_payroll_id,
91: v_asg_effective_start_date,
92: v_asg_effective_end_date
93: from per_assignments_f asg
94: where asg.assignment_id = p_assignment_id
95: and asg.payroll_id is not null
96: and p_session_date between asg.effective_start_date
97: and asg.effective_end_date;
138: begin
139: --
140: select asg.effective_start_date
141: into v_start_date
142: from per_assignments_f asg
143: where asg.assignment_id = p_assignment_id
144: and asg.effective_end_date = v_asg_effective_start_date - 1;
145:
146: -- bug 6485636
168: begin
169: --
170: select asg.effective_end_date
171: into v_end_date
172: from per_assignments_f asg
173: where asg.assignment_id = p_assignment_id
174: and asg.effective_start_date - 1 = v_asg_effective_end_date;
175:
176: -- bug 6485636
337: -- calculate the element entry start date according to the qualifying age
338: -- condition
339: select add_months(p.date_of_birth, (l_qualifying_age * 12))
340: from per_all_people_f p,
341: per_assignments_f asg
342: where p.person_id = asg.person_id
343: and p.date_of_birth is not null
344: and asg.assignment_id = p_assignment_id
345: -- session_date comparison has been removed, so ensure that
571: -- Return a row if a match is found between the element link criteria
572: -- and the attributes of the assignment as at the effective date
573: --
574: select 'Y' ROW_RETURNED
575: from per_assignments_f ASG,
576: pay_element_links_f PEL
577: where lpi_effective_date between pel.effective_start_date
578: and pel.effective_end_date
579: and lpi_effective_date between asg.effective_start_date
686: is
687: --
688: cursor csr_sec_asg(p_asg_id number, p_session_date date) is
689: select 1
690: from per_assignments_f
691: where assignment_id = p_asg_id
692: and p_session_date between effective_start_date and effective_end_date;
693: --
694: v_asg_visible number;
785: -- ALWAYS return a row
786: --
787: select min (effective_start_date) ASGT_START,
788: max (effective_end_date) ASGT_END
789: from per_assignments_f
790: where assignment_id = P_ASSIGNMENT_ID;
791: --
792: cursor csr_minimum (p_assignment_id number, lpi_session_date date, p_time_period_start_date date) is
793: --
791: --
792: cursor csr_minimum (p_assignment_id number, lpi_session_date date, p_time_period_start_date date) is
793: --
794: select asg1.effective_end_date
795: from per_assignments_f asg1
796: where asg1.assignment_id = p_assignment_id
797: -- Removed the following predicate as it is redundant
798: -- i.e. If assignment end date is less than session date then it
799: -- follows that the assignment start date must be less than
807: order by asg1.effective_end_date desc;
808: --
809: cursor csr_maximum (p_assignment_id number, lpi_session_date date, p_time_period_end_date date) is
810: select asg1.effective_start_date
811: from per_assignments_f asg1
812: where asg1.assignment_id = p_assignment_id
813: and asg1.effective_end_date >= lpi_session_date
814: -- Bugfix 5135065
815: -- Exclude any pieces of the assignment that start after the time
1101: -- a value_error would have been raised by the assert_condition call
1102: -- in the main body code otherwise.
1103: --
1104: select assignment_number
1105: from per_assignments_f
1106: where assignment_id = P_ASSIGNMENT_ID
1107: and lpi_session_date between effective_start_date
1108: and effective_end_date;
1109: --
1262: v_primary_flag,
1263: v_actual_termination_date,
1264: v_last_standard_process_date,
1265: v_final_process_date
1266: from per_assignments_f asg,
1267: per_periods_of_service pos
1268: where asg.assignment_id = p_assignment_id
1269: and asg.period_of_service_id = pos.period_of_service_id (+)
1270: and p_session_date between asg.effective_start_date
1431: end if;
1432: begin
1433: select min(asg.effective_start_date)
1434: into v_asg_term_date
1435: from per_assignments_f asg,
1436: per_assignment_status_types ast
1437: where asg.assignment_id = p_assignment_id
1438: and asg.effective_end_date >= p_session_date
1439: and asg.assignment_status_type_id = ast.assignment_status_type_id
1451: --
1452: begin
1453: select asg.effective_start_date
1454: into v_asg_term_date
1455: from per_assignments_f asg,
1456: per_assignment_status_types ast
1457: where asg.assignment_id = p_assignment_id
1458: and asg.effective_end_date >= p_session_date
1459: and asg.assignment_status_type_id = ast.assignment_status_type_id
1502: begin
1503: select ptp.end_date
1504: into v_element_term_rule_date
1505: from per_time_periods ptp,
1506: per_assignments_f asg
1507: where asg.assignment_id = p_assignment_id
1508: and v_asg_term_date between asg.effective_start_date and asg.effective_end_date
1509: and asg.payroll_id is not null
1510: and ptp.payroll_id = asg.payroll_id
1522: end if;
1523: begin
1524: select max(asg.effective_end_date)
1525: into v_element_term_rule_date
1526: from per_assignments_f asg
1527: where asg.assignment_id = p_assignment_id
1528: and asg.effective_start_date >= v_asg_term_date;
1529: end;
1530: elsif (v_post_termination_rule = 'L') then
1535: begin
1536: select ptp.end_date
1537: into v_element_term_rule_date
1538: from per_time_periods ptp,
1539: per_assignments_f asg
1540: where asg.assignment_id = p_assignment_id
1541: and v_asg_term_date
1542: between asg.effective_start_date
1543: and asg.effective_end_date
1566: --
1567: begin
1568: select max(asg.effective_end_date)
1569: into v_element_term_rule_date
1570: from per_assignments_f asg
1571: where asg.assignment_id = p_assignment_id
1572: and asg.effective_start_date >= v_asg_term_date;
1573: end;
1574: --
2156: p_validation_end_date date
2157: ) is
2158: select tp.status
2159: from per_time_periods tp,
2160: per_assignments_f asg
2161: where asg.assignment_id = p_assignment_id
2162: and asg.payroll_id is not null
2163: and asg.effective_start_date <= p_validation_end_date
2164: and asg.effective_end_date >= p_validation_start_date
3628: end if;
3629: --
3630: select business_group_id
3631: into v_bg_id
3632: from per_assignments_f pas
3633: where pas.assignment_id = v_assignment_id
3634: and p_session_date between pas.effective_start_date
3635: and pas.effective_end_date
3636: and rownum=1;
4930: where paa.assignment_action_id =
4931: (select aa.assignment_action_id
4932: from pay_assignment_actions aa,
4933: pay_payroll_actions pa,
4934: per_assignments_f asg,
4935: per_time_periods tim,
4936: pay_payrolls_f pp
4937: where pa.effective_date =
4938: (select max(pa2.effective_date)
5672: into l_payroll_id,
5673: l_start_date,
5674: l_end_date
5675: from per_time_periods t,
5676: per_assignments_f a
5677: where a.assignment_id = p_assignment_id
5678: and p_session_date
5679: between a.effective_start_date
5680: and a.effective_end_date