[Home] [Help]
212: from per_all_assignments_f asg
213: ,pay_assignment_actions paa
214: ,pay_payroll_actions ppa
215: ,hr_soft_coding_keyflex hscl
216: ,per_periods_of_service pos
217: where asg.assignment_id = paa.assignment_id
218: and paa.payroll_action_id = ppa.payroll_action_id
219: and pos.period_of_service_id = asg.period_of_service_id
220: and ppa.action_type in ('R','Q')
235: from per_all_assignments_f asg
236: ,pay_assignment_actions paa
237: ,pay_payroll_actions ppa
238: ,hr_soft_coding_keyflex hscl
239: ,per_periods_of_service pos
240: where asg.assignment_id = paa.assignment_id
241: and paa.payroll_action_id = ppa.payroll_action_id
242: and pos.period_of_service_id = asg.period_of_service_id
243: and ppa.action_type in ('R','Q')
265: select distinct asg.person_id,asg.assignment_id from per_all_assignments_f asg
266: ,pay_assignment_actions paa
267: ,pay_payroll_actions ppa
268: ,hr_soft_coding_keyflex hscl
269: ,per_periods_of_service pos
270: where asg.assignment_id = paa.assignment_id
271: and paa.payroll_action_id = ppa.payroll_action_id
272: and pos.period_of_service_id = asg.period_of_service_id
273: and ppa.action_type in ('R','Q')
284: select paa.assignment_action_id from per_all_assignments_f asg
285: ,pay_assignment_actions paa
286: ,pay_payroll_actions ppa
287: ,hr_soft_coding_keyflex hscl
288: ,per_periods_of_service pos
289: where rownum < 2
290: and asg.assignment_id = paa.assignment_id
291: and asg.person_id = p_person_id
292: and paa.payroll_action_id = ppa.payroll_action_id
309: from per_all_assignments_f asg
310: ,pay_assignment_actions paa
311: ,pay_payroll_actions ppa
312: ,hr_soft_coding_keyflex hscl
313: ,per_periods_of_service pos
314: where asg.assignment_id = paa.assignment_id
315: and paa.payroll_action_id = ppa.payroll_action_id
316: and pos.period_of_service_id = asg.period_of_service_id
317: and ppa.action_type in ('R','Q')
351: from per_all_assignments_f asg
352: ,pay_assignment_actions paa
353: ,pay_payroll_actions ppa
354: ,hr_soft_coding_keyflex hscl
355: ,per_periods_of_service pos
356: where rownum < 2
357: and asg.assignment_id = paa.assignment_id
358: and asg.person_id = p_person_id
359: and paa.payroll_action_id = ppa.payroll_action_id
406: SELECT NATIONAL_IDENTIFIER from per_all_people_f pap WHERE pap.person_id = p_person_id
407: and trunc(p_date,'MM') between trunc(pap.effective_start_date,'MM') and pap.effective_end_date;
408: --cursor to get the joining date of the newly hired employees
409: cursor get_start_date(p_person_id number) is
410: select date_start from per_periods_of_service where person_id = p_person_id and trunc(date_start,'MM') = to_date(l_effective_date,'DD-MM-YYYY');
411: --cursor to get leaving date for new joinee
412: cursor get_term_date_new (p_person_id number) is
413: select actual_termination_date from per_periods_of_service where person_id = p_person_id;
414: --cursor to get the details of the newly hired employees
409: cursor get_start_date(p_person_id number) is
410: select date_start from per_periods_of_service where person_id = p_person_id and trunc(date_start,'MM') = to_date(l_effective_date,'DD-MM-YYYY');
411: --cursor to get leaving date for new joinee
412: cursor get_term_date_new (p_person_id number) is
413: select actual_termination_date from per_periods_of_service where person_id = p_person_id;
414: --cursor to get the details of the newly hired employees
415: cursor get_details_new (p_person_id number,p_date date) is
416: select full_name,nationality,date_of_birth from per_all_people_f where person_id = p_person_id
417: and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
432: where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
433: and paf.person_id = p_person_id;
434: --cursor to get the termination details of the terminated employees
435: cursor get_details_term (p_person_id number) is
436: select actual_termination_date,leaving_reason from per_periods_of_service where person_id = p_person_id;
437: /* Cursor to fetch lower limit of gosi base*/
438: CURSOR get_lower_base(l_effective_date DATE) IS
439: SELECT global_value
440: FROM ff_globals_f
1975: select distinct paa.assignment_action_id, paf.person_id
1976: from pay_assignment_actions paa
1977: ,pay_payroll_actions ppa
1978: ,per_all_assignments_f paf
1979: ,per_periods_of_service pps
1980: ,hr_soft_coding_keyflex hscl
1981: ,per_time_periods ptp
1982: ,pay_run_results prr
1983: where paf.period_of_service_id = pps.period_of_service_id
2003: select distinct paa.assignment_action_id, paf.person_id
2004: from pay_assignment_actions paa
2005: ,pay_payroll_actions ppa
2006: ,per_all_assignments_f paf
2007: ,per_periods_of_service pps
2008: ,hr_soft_coding_keyflex hscl
2009: ,per_time_periods ptp
2010: ,pay_run_results prr
2011: where paf.period_of_service_id = pps.period_of_service_id
2031: select distinct paa.assignment_action_id, paf.person_id
2032: from pay_assignment_actions paa
2033: ,pay_payroll_actions ppa
2034: ,per_all_assignments_f paf
2035: ,per_periods_of_service pps
2036: ,hr_soft_coding_keyflex hscl
2037: ,per_time_periods ptp
2038: ,pay_run_results prr
2039: where paf.period_of_service_id = pps.period_of_service_id
2660: cursor csr_get_gre_assignments (l_employer_id number, l_business_group_id number, l_effective_date date,l_form_type varchar2) is
2661: select /*+ INDEX(hsck, HR_SOFT_CODING_KEYFLEX_PK) */ distinct assignment_id
2662: from per_all_assignments_f paa,
2663: hr_soft_coding_keyflex hsck,
2664: per_periods_of_service pos
2665: where hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2666: and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
2667: and paa.business_group_id = l_business_group_id
2668: --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2684: cursor csr_get_assignment(l_assignment_set_id number, l_form_type varchar2) is
2685: select distinct has.assignment_id
2686: from hr_assignment_set_amendments has
2687: ,per_all_assignments_f paa
2688: ,per_periods_of_service pos
2689: ,hr_soft_coding_keyflex hsck
2690: where assignment_set_id = l_assignment_set_id
2691: and include_or_exclude = 'I'
2692: and has.assignment_id = paa.assignment_id
2807: cursor get_start_date (l_assignment_id NUMBER, l_effective_date Date) is
2808: select pps.date_start
2809: ,pps.actual_termination_date
2810: ,pps.leaving_reason
2811: from per_periods_of_service pps
2812: ,per_all_assignments_f paa
2813: where /*pps.person_id = paa.person_id*/
2814: pps.period_of_service_id = paa.period_of_service_id
2815: and paa.business_group_id = pps.business_group_id
2946: select 1 into i
2947: from hr_assignment_set_amendments haa
2948: ,per_all_assignments_f paa
2949: ,hr_soft_coding_keyflex hscl
2950: ,per_periods_of_service pos
2951: where assignment_set_id = p_assignment_set_id
2952: and include_or_exclude = 'I'
2953: and hscl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2954: and paa.assignment_id = haa.assignment_id
2979: begin
2980: select distinct paa.assignment_id
2981: into t_emp(0).emp_id
2982: from per_all_assignments_f paa,
2983: per_periods_of_service pos,
2984: hr_soft_coding_keyflex hsck
2985: where paa.assignment_id = p_assignment_id
2986: --and p_effective_date between paa.effective_start_date and paa.effective_end_date
2987: and hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id