[Home] [Help]
23: ,p_expiry_date date
24: ) is
25: select nvl(greatest(min(ASS.effective_start_date), p_expiry_date),
26: g_eot)
27: from per_assignments_f ASS
28: where ASS.assignment_id = p_assignment_id
29: and ASS.effective_start_date <= p_upload_date
30: and ASS.effective_end_date >= p_expiry_date
31: and ASS.payroll_id is not null;
33: cursor csr_asg_ptd
34: is
35: select nvl(ptp.start_date,g_eot)
36: from per_time_periods ptp,
37: per_assignments_f pa
38: where pa.assignment_id = p_assignment_id
39: and p_upload_date
40: between pa.effective_start_date and pa.effective_end_date
41: and ptp.payroll_id = pa.payroll_id
44: --
45: cursor csr_asg_itd
46: is
47: select nvl(min(pa.effective_start_date),g_eot)
48: from per_assignments_f pa
49: where pa.assignment_id = p_assignment_id
50: and pa.effective_start_date <= p_upload_date
51: and exists (
52: select null
61: (floor(floor(months_between(p_upload_date,
62: fnd_date.canonical_to_date(org_information11)))/12)*12)),
63: g_eot)
64: from hr_organization_information hoi,
65: per_assignments_f pa
66: where pa.assignment_id = p_assignment_id
67: and p_upload_date
68: between pa.effective_start_date and pa.effective_end_date
69: and hoi.organization_id = pa.business_group_id
84: (floor(floor(months_between(p_upload_date,
85: ppos.date_start))/12)*12)),
86: g_eot)
87: from per_periods_of_service ppos,
88: per_assignments_f pa
89: where pa.assignment_id = p_assignment_id
90: and p_upload_date
91: between pa.effective_start_date and pa.effective_end_date
92: and ppos.period_of_service_id = pa.period_of_service_id;
103: l_expiry_date DATE
104: )
105: IS
106: SELECT MIN(ptp.regular_payment_date)
107: FROM per_time_periods ptp, per_assignments_f paf
108: WHERE paf.assignment_id = l_assignment_id
109: AND l_upload_date BETWEEN paf.effective_start_date
110: AND paf.effective_end_date
111: AND ptp.payroll_id = paf.payroll_id