[Home] [Help]
46:
47: cursor csr_date_terminated is
48: select paaf1.effective_end_date,nvl(paaf1.payroll_id,-1)
49: from per_all_assignments_f paaf1,
50: per_assignment_status_types past1,
51: per_all_assignments_f paaf2,
52: per_assignment_status_types past2,
53: pay_all_payrolls_f papf,
54: per_time_periods ptp
48: select paaf1.effective_end_date,nvl(paaf1.payroll_id,-1)
49: from per_all_assignments_f paaf1,
50: per_assignment_status_types past1,
51: per_all_assignments_f paaf2,
52: per_assignment_status_types past2,
53: pay_all_payrolls_f papf,
54: per_time_periods ptp
55: where past1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
56: and paaf1.assignment_status_type_id=past1.assignment_status_type_id
69: --cursor duplicated in pl_get_tax_details
70: cursor csr_contract_assgt_type(r_date date) is
71: select kyflx.segment3,past.per_system_status,paaf.person_id
72: from hr_soft_coding_keyflex kyflx,
73: per_assignment_status_types past,
74: per_all_assignments_f paaf
75: where paaf.assignment_id=p_assignment_id
76: and r_date between paaf.effective_start_date and paaf.effective_end_date
77: and paaf.soft_coding_keyflex_id=kyflx.soft_coding_keyflex_id
93: and contract_category=r_contract_category
94: and r_sii_date between effective_start_date and effective_end_date ;
95:
96: l_contract_category hr_soft_coding_keyflex.segment3%type;
97: l_per_system_status per_assignment_status_types.per_system_status%type;
98: l_join_variable pay_pl_sii_details_f.per_or_asg_id%type;
99: l_date_earned date;
100: l_date_terminated date;
101: l_payroll_id number;
332:
333: cursor csr_date_terminated is
334: select paaf1.effective_end_date terminated_date,nvl(paaf1.payroll_id,-1) payroll_id
335: from per_all_assignments_f paaf1,
336: per_assignment_status_types past1,
337: per_all_assignments_f paaf2,
338: per_assignment_status_types past2,
339: pay_all_payrolls_f papf,
340: per_time_periods ptp
334: select paaf1.effective_end_date terminated_date,nvl(paaf1.payroll_id,-1) payroll_id
335: from per_all_assignments_f paaf1,
336: per_assignment_status_types past1,
337: per_all_assignments_f paaf2,
338: per_assignment_status_types past2,
339: pay_all_payrolls_f papf,
340: per_time_periods ptp
341: where past1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
342: and paaf1.assignment_status_type_id=past1.assignment_status_type_id
400:
401: cursor csr_contract_assgt_type(r_date date) is
402: select kyflx.segment3,kyflx.segment4,past.per_system_status,paaf.person_id
403: from hr_soft_coding_keyflex kyflx,
404: per_assignment_status_types past,
405: per_all_assignments_f paaf
406: where paaf.assignment_id=p_assignment_id
407: and r_date between paaf.effective_start_date and paaf.effective_end_date
408: and paaf.soft_coding_keyflex_id=kyflx.soft_coding_keyflex_id
420: cursor csr_other_normal_contracts(r_person_id number) is
421: select 'Y'
422: from per_all_assignments_f paaf
423: ,hr_soft_coding_keyflex kyflex
424: ,per_assignment_status_types past
425: where paaf.person_id=r_person_id
426: and paaf.assignment_id<>p_assignment_id
427: and p_date_earned between paaf.effective_start_date and paaf.effective_end_date
428: and kyflex.segment3='NORMAL'
445:
446: l_run_result_table run_result_table;
447: l_csr_date_terminated csr_date_terminated%rowtype;
448: l_csr_payroll_run csr_payroll_run%rowtype;
449: l_per_system_status per_assignment_status_types.per_system_status%type;
450: l_person_id number;
451: l_other_normal varchar2(1);--'Y' or 'N'
452: l_payroll_end_date date;
453: l_payroll_run varchar2(1);--'Y' or 'N'