[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,
'LEGAL_EMPLOYER')
,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'EMPLOYEEID')
,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
/** Insert report parameters **/
pay_action_information_api.create_action_information (
p_action_information_id => l_action_info_id,
p_action_context_id => p_payroll_action_id,
p_action_context_type => 'PA',
p_object_version_number => l_ovn,
p_effective_date => g_effective_date,
p_source_id => NULL,
p_source_text => NULL,
p_action_information_category=> 'EMEA REPORT DETAILS',
p_action_information1 => 'PYNOSFCA',
p_action_information2 => g_legal_employer_id,
p_action_information3 => g_employee_id,
p_action_information4 => fnd_date.date_to_canonical(g_effective_date));
p_sql := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
-- Return cursor that selects no rows
p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT paaf.assignment_id
FROM per_all_people_f papf,
per_all_assignments_f paaf,
per_assignment_status_types past,
hr_soft_coding_keyflex hsc,
hr_organization_information hoi
WHERE paaf.person_id = papf.person_id
AND papf.person_id = nvl(csr_v_person_id,papf.person_id)
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND papf.business_group_id = csr_v_business_group_id
AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
AND hoi.org_information1 = hsc.segment2
AND hoi.organization_id = csr_v_legal_employer_id
AND hoi.org_information_context = 'NO_LOCAL_UNITS'
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND papf.person_id BETWEEN p_start_person and p_end_person
AND csr_v_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND csr_v_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND EXISTS(select paa.absence_attendance_id from per_absence_attendances paa, per_absence_attendance_types paat
where paa.person_id = papf.person_id
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.absence_category in ('CMS','S','PTS')
and paa.abs_information1 = 'SC'
and paa.date_start between csr_v_start_date and csr_v_end_date
and paa.date_end between csr_v_start_date and csr_v_end_date );
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
select papf.employee_number,
papf.full_name,
paaf.assignment_id,
count(paa.abs_information1) sc_days
from per_absence_attendances paa,
per_absence_attendance_types paat,
per_all_assignments_f paaf,
per_assignment_status_types past,
pay_assignment_actions paas,
per_all_people_f papf,
hr_soft_coding_keyflex hsc,
hr_organization_information hoi
where paas.assignment_action_id = p_assignment_action_id
and paa.person_id = papf.person_id
AND papf.person_id = nvl(csr_v_person_id,papf.person_id)
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paaf.person_id = papf.person_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
and paaf.assignment_id = paas.assignment_id
and papf.business_group_id = csr_v_business_group_id
and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and hoi.org_information1 = hsc.segment2
and hoi.organization_id = csr_v_legal_employer_id
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
and hoi.ORG_INFORMATION_CONTEXT = 'NO_LOCAL_UNITS'
and paaf.primary_flag = 'Y'
and paat.absence_category in ('CMS','S','PTS')
and paa.abs_information1 = 'SC'
and paa.date_start between csr_v_start_date and csr_v_end_date
and paa.date_end between csr_v_start_date and csr_v_end_date
and csr_v_effective_date between papf.effective_start_date and papf.effective_end_date
and csr_v_effective_date between paaf.effective_start_date and paaf.effective_end_date
group by papf.employee_number,papf.full_name,paaf.assignment_id;