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,'ARCHIVE')
,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'EMPLOYEEID')
,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'ASSIGNMENTSETID')
,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 => 'PYNOHLEA',
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_action_information5 => g_assignment_set_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';
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 (csr_v_assignment_set_id is null
or exists(select hasav.assignment_id
from
HR_ASSIGNMENT_SET_AMENDMENTS_V hasav,
HR_ASSIGNMENT_SETS_V hasv
where
hasav.assignment_set_id = hasv.assignment_set_id
and hasv.assignment_set_id = csr_v_assignment_set_id
and hasav.assignment_id = paaf.assignment_id));
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT papf.person_id,
papf.full_name,
paaf.assignment_id,
paaf.assignment_number,
past.per_system_status
FROM per_all_people_f papf,
per_all_assignments_f paaf,
per_assignment_status_types past,
pay_assignment_actions paas,
hr_soft_coding_keyflex hsc,
hr_organization_information hoi
WHERE paas.assignment_action_id = p_assignment_action_id
AND papf.person_id = nvl(csr_v_person_id,papf.person_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 hoi.org_information_context = 'NO_LOCAL_UNITS'
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
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 (csr_v_assignment_set_id is null
or exists(select hasav.assignment_id
from
HR_ASSIGNMENT_SET_AMENDMENTS_V hasav,
HR_ASSIGNMENT_SETS_V hasv
where
hasav.assignment_set_id = hasv.assignment_set_id
and hasv.assignment_set_id = csr_v_assignment_set_id
and hasav.assignment_id = paaf.assignment_id));
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pbt.legislation_code = 'NO'
AND pbd.database_item_suffix = '_ASG_YTD'
AND pbt.balance_name = csr_balance_name;