[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER(legislative_parameters,
'LEGAL_EMPLOYER')
,FND_DATE.canonical_to_date(PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER
(legislative_parameters,'START_DATE'))
,FND_DATE.canonical_to_date(PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER
(legislative_parameters,'END_DATE'))
,PAY_NO_ARCHIVE_ABSENCE.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
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';
)is select papf.person_id,
papf.full_name,
papf.sex sex,
'Q'||to_char(paa.date_start,'Q') quaterstart,
'Q'||to_char(paa.date_end,'Q') quaterend,
paa.date_start,
paa.date_end,
paaf.assignment_id,
decode(paat.absence_category,'CMS',paa.abs_information1,'S',paa.abs_information1,'PTS',paa.abs_information1,null) certificate_type,
paat.absence_category,
paa.abs_information3,
paa.abs_information4
from per_absence_attendances paa,
per_all_people_f papf,
per_all_assignments_f paaf,
per_absence_attendance_types paat,
hr_soft_coding_keyflex hsc,
hr_organization_information hoi
where paa.person_id = papf.person_id
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paaf.person_id = papf.person_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 paaf.primary_flag = 'Y'
and papf.per_information15 = 'N'
and paat.absence_category <> 'VAC'
and ((paa.date_start between csr_v_start_date and csr_v_end_date)
or (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
and not exists (select '1'
from
pay_element_entries_f peef,
pay_element_types_f petf,
per_all_assignments_f paaf1
where peef.element_type_id = petf.element_type_id
and paaf1.person_id = papf.person_id
and peef.assignment_id = paaf1.assignment_id
and petf.element_name = 'Sickness Unpaid'
and peef.effective_start_date between csr_v_start_date and csr_v_end_date);
select paaf.assignment_id
from per_all_people_f papf,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsc,
hr_organization_information hoi
where paaf.person_id = papf.person_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 paaf.primary_flag = 'Y'
and papf.per_information15 = 'N'
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 papf.sex = csr_v_sex
and not exists (select '1'
from
pay_element_entries_f peef,
pay_element_types_f petf,
per_all_assignments_f paaf1
where peef.element_type_id = petf.element_type_id
and paaf1.person_id = papf.person_id
and peef.assignment_id = paaf1.assignment_id
and petf.element_name = 'Sickness Unpaid'
and peef.effective_start_date between csr_v_start_date and csr_v_end_date);
is select value
from PER_ASSIGNMENT_BUDGET_VALUES_F
where business_group_id = csr_business_grp_id
and assignment_id = csr_assignment_id and unit = 'PFT'
and csr_v_effective_date between effective_start_date and effective_end_date;
/** 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 => l_effective_date,
p_source_id => NULL,
p_source_text => NULL,
p_action_information_category=> 'EMEA REPORT DETAILS',
p_action_information1 => 'PYNOABSA',
p_action_information2 => l_legal_employer_id,
p_action_information3 => fnd_date.date_to_canonical(l_start_date),
p_action_information4 => fnd_date.date_to_canonical(l_end_date),
p_action_information5 => fnd_date.date_to_canonical(l_effective_date)
);
SELECT count(*) INTO l_count
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1= 'PYNOABSA'
AND action_context_id= p_payroll_action_id;