[Home] [Help]
171: -- incorrectly.
172: --
173: select distinct
174: asg.assignment_id
175: from per_all_assignments_f ASG
176: where asg.period_of_service_id = L_PERIOD_OF_SERVICE_ID
177: and asg.payroll_id is not null
178: --6791913 begin - To treat adoption in the same way as Maternity
179: --and (( p_absence_category = 'M'
238: from per_time_periods ptp
239: where ptp.payroll_id in
240: ( select papf.payroll_id
241: from pay_all_payrolls_f papf,
242: per_all_assignments_f paf,
243: per_all_people_f ppf,
244: per_time_periods ptp
245: where ppf.person_id = paf.person_id
246: and papf.payroll_id = paf.payroll_id
275: from per_time_periods ptp
276: where ptp.payroll_id in
277: ( select papf.payroll_id
278: from pay_all_payrolls_f papf,
279: per_all_assignments_f paf,
280: per_all_people_f ppf,
281: per_time_periods ptp
282: where ppf.person_id = paf.person_id
283: and papf.payroll_id = paf.payroll_id
305: --
306: cursor csr_get_payroll_id_start is
307: select ppf.payroll_id
308: from pay_all_payrolls_f ppf
309: , per_all_assignments_f paf
310: where ppf.payroll_id = paf.payroll_id
311: and paf.assignment_id = l_assignment_id
312: and l_start_of_relevant_period between
313: paf.effective_start_date and paf.effective_end_date;
317: -- of the last eight weeks for calculating average earnings.
318: --
319: cursor csr_get_payroll_id_end is
320: select payroll_id
321: from per_all_assignments_f paf
322: where paf.assignment_id = L_ASSIGNMENT_ID
323: and P_EFFECTIVE_DATE between
324: paf.effective_start_date and paf.effective_end_date;
325:
326: --
327: --
328: cursor csr_chk_asg is
329: select 1
330: from per_all_assignments_f
331: where assignment_id = L_ASSIGNMENT_ID
332: and l_start_of_relevant_period between effective_start_date and effective_end_date;
333:
334: cursor csr_get_start_date is
332: and l_start_of_relevant_period between effective_start_date and effective_end_date;
333:
334: cursor csr_get_start_date is
335: select min(effective_start_date)
336: from per_all_assignments_f
337: where assignment_id = L_ASSIGNMENT_ID;
338: --
339: /* Bug 9411096
340: Added cursor to identify TERM_ASSIGN, and handle the relavant dates accordingly. */
339: /* Bug 9411096
340: Added cursor to identify TERM_ASSIGN, and handle the relavant dates accordingly. */
341: cursor get_asg_status is
342: select past.per_system_status
343: from per_all_assignments_f paaf, per_assignment_status_types past
344: where paaf.assignment_id = L_ASSIGNMENT_ID
345: and paaf.effective_start_date <= P_EFFECTIVE_DATE
346: and paaf.assignment_status_type_id = past.assignment_status_type_id
347: order by paaf.effective_start_date desc;
476: -- PIW_start_date(SSP) or QW_start_date(SMP).
477: --
478: select period_type.number_per_fiscal_year fiscal_year
479: from pay_all_payrolls_f PAYROLL,
480: per_all_assignments_f ASSIGNMENT,
481: per_time_period_types PERIOD_TYPE
482: where assignment.assignment_id = l_assignment_id
483: and assignment.effective_start_date <= l_end_of_relevant_period
484: and assignment.effective_end_date >= l_start_of_relevant_period
734: -- actual number of payment periods.
735: --
736: cursor csr_get_number_of_reg_payments is
737: select peev.screen_entry_value
738: from per_all_assignments_f paf
739: , pay_element_entry_values_f peev
740: , pay_element_entries_f pee
741: , pay_element_types_f pet
742: , pay_element_links_f pel
772: --
773: cursor csr_number_of_days is
774: select period_type.number_per_fiscal_year fiscal_year
775: from pay_all_payrolls_f PAYROLL,
776: per_all_assignments_f ASSIGNMENT,
777: per_time_period_types PERIOD_TYPE
778: where assignment.assignment_id = l_assignment_id
779: and payroll.payroll_id = assignment.payroll_id
780: and payroll.period_type = period_type.period_type