[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT effective_end_date,payroll_id
INTO l_asg_ed,l_asg_py_id
FROM PER_ALL_ASSIGNMENTS_F
WHERE assignment_id = p_in_asg_id
AND p_in_eff_date BETWEEN
effective_start_date
AND effective_end_date;
SELECT time.end_date
INTO l_py_ed
FROM per_time_periods time
WHERE time.payroll_id = l_asg_py_id
AND time.end_date > ( SELECT MAX(effective_date)
FROM pay_payroll_actions act
WHERE act.payroll_id =l_asg_py_id
and act.action_status='C')
AND ROWNUM = 1;
SELECT business_group_id
,effective_start_date
,effective_end_date
,person_id
INTO l_bg_id
,l_asg_sd
,l_asg_ed
,l_person_id
FROM PER_ALL_ASSIGNMENTS_F
WHERE assignment_id = p_in_asg_id
AND p_in_eff_date
BETWEEN effective_start_date AND
effective_end_date;
SELECT count(asg.assignment_id)
INTO l_asg_count
FROM pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_types_f pet,
per_all_assignments_f asg,
pay_element_type_extra_info pete,
pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = pete.element_type_id
AND pee.assignment_id = asg.assignment_id
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND pee.assignment_id <> p_in_asg_id
AND asg.person_id = l_person_id
AND piv.name IN ('Vehicle Type','Rate Type')
AND peev.screen_entry_value = p_in_veh_type
AND asg.business_group_id = l_bg_id
AND p_in_eff_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND asg.effective_end_date > p_in_claim_end_date
AND asg.effective_start_date < l_asg_ed
AND p_in_eff_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
AND p_in_eff_date BETWEEN
pee.effective_start_date AND pee.effective_end_date
AND p_in_eff_date BETWEEN
peev.effective_start_date AND peev.effective_end_date
AND p_in_eff_date BETWEEN
piv.effective_start_date AND piv.effective_end_date
AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
AND pete.eei_information1 <>'L';
select max(ppa.effective_date) effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
WHERE paf.person_id =(SELECT distinct person_id from per_assignments_f
where assignment_id =p_assignment_id_o)
AND paf.assignment_id=paa.assignment_id
and paa.payroll_action_id=ppa.payroll_action_id
AND ppa.action_type in ('R','Q','V');
SELECT effective_start_date,
effective_end_date
FROM pay_element_entries_f
WHERE element_entry_id=p_element_entry_id
AND p_effective_date BETWEEN
effective_start_date
AND effective_end_date;
SELECT pee.element_entry_id,
pee.effective_end_date,
pet.element_type_id,
pel.element_link_id,
pel.effective_end_date link_end_date,
pee.effective_start_date
FROM pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info pete
WHERE pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = pete.element_type_id
AND p_effective_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
AND p_effective_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND pee.assignment_id = p_assignment_id_o
-- Open only those entries entered after the current entry.
AND pee.element_entry_id > p_element_entry_id
AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
AND pete.eei_information1 <>'L'
AND pee.effective_start_date >= p_ele_start_date
AND (pee.effective_end_date <= p_ele_end_date
OR pee.effective_end_date = hr_general.end_of_time)
ORDER BY 1,2 desc;
SELECT 'x'
FROM pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info pete
WHERE pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = pete.element_type_id
AND pee.assignment_id = p_assignment_id_o
AND pee.element_entry_id = p_element_entry_id
AND p_effective_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND p_effective_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
AND p_effective_date BETWEEN
pee.effective_start_date AND pee.effective_end_date
AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
AND pete.eei_information1 <>'L';
SELECT peev.screen_entry_value
FROM pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info pete,
pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = pete.element_type_id
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.name = p_name
AND pee.assignment_id = p_assignment_id_o
AND pee.element_entry_id = p_in_element_entry_id
AND p_effective_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND p_effective_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
AND p_effective_date BETWEEN
pee.effective_start_date AND pee.effective_end_date
AND p_effective_date BETWEEN
peev.effective_start_date AND peev.effective_end_date
AND p_effective_date BETWEEN
piv.effective_start_date AND piv.effective_end_date
AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
AND pete.eei_information1 <>'L';
SELECT peev.screen_entry_value
FROM pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info pete,
pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = pete.element_type_id
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.name IN ('Vehicle Type','Rate Type')
AND pee.assignment_id = p_assignment_id_o
AND pee.element_entry_id = p_in_element_entry_id
AND p_effective_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND p_effective_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
AND p_effective_date BETWEEN
pee.effective_start_date AND pee.effective_end_date
AND p_effective_date BETWEEN
peev.effective_start_date AND peev.effective_end_date
AND p_effective_date BETWEEN
piv.effective_start_date AND piv.effective_end_date
AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
AND pete.eei_information1 <>'L';
SELECT peev.screen_entry_value
FROM pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info pete,
pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = pete.element_type_id
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.name = p_name
AND pee.assignment_id = p_assignment_id_o
AND pee.element_entry_id = p_in_element_entry_id
AND p_effective_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND p_effective_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
-- Code commented out to fix bug .
-- If the session date is before the EE start date
-- the hook failed. Go on effective end date instead.
-- AND p_effective_date BETWEEN
-- pee.effective_start_date AND pee.effective_end_date
AND p_effective_date BETWEEN
piv.effective_start_date AND piv.effective_end_date
AND peev.effective_end_date = p_in_end_date
AND pee.effective_end_date = p_in_end_date
AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
AND pete.eei_information1 <>'L';
SELECT peev.screen_entry_value
FROM pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info pete,
pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = pete.element_type_id
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.name IN ('Vehicle Type','Rate Type')
AND pee.assignment_id = p_assignment_id_o
AND pee.element_entry_id = p_in_element_entry_id
AND p_effective_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND p_effective_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
-- Code commented out to fix bug .
-- If the session date is before the EE start date
-- the hook failed. Go on effective end date instead.
-- AND p_effective_date BETWEEN
-- pee.effective_start_date AND pee.effective_end_date
AND p_effective_date BETWEEN
piv.effective_start_date AND piv.effective_end_date
AND peev.effective_end_date = p_in_end_date
AND pee.effective_end_date = p_in_end_date
AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
AND pete.eei_information1 <>'L';
IF p_datetrack_mode <> 'DELETE' THEN -- DT_MODE_CHK
-- Check if asg expires
-- Check not required as you cannot perform a NEXT / ALL
-- if the ASG is already end dated
--IF NOT asg_expires (p_assignment_id_o,p_effective_date) THEN
-- Get values from the screen for Claim End Date and Vehicle Type
-- OR Rate Type for the session date
OPEN c_chk_payrun;
SELECT effective_end_date
INTO l_asg_eff_ed
FROM PER_ALL_ASSIGNMENTS_F
WHERE assignment_id = p_assignment_id_o
AND p_effective_date BETWEEN
effective_start_date
AND effective_end_date;
UPDATE pay_element_entries_f
SET effective_end_date =
LEAST(temp_rec.link_end_date,l_asg_eff_ed)
WHERE element_entry_id = temp_rec.element_entry_id
AND effective_end_date = temp_rec.effective_end_date;
'DELETE_NEXT_CHANGE', --dt_delete_mode,
--p_effective_date, --p_session_date,
-- Open the entry values from the start_date of
-- the element entry.
temp_rec.effective_start_date, --p_session_date,
NULL, --validation_start_date,
NULL); --validation_end_date);
END IF; -- Check for DateTrack delete mode
SELECT pete.eei_information1 ele_type
FROM pay_element_type_extra_info pete
WHERE pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
AND pete.element_type_id= cp_element_type_id;
SELECT 'Y' terminated
FROM per_periods_of_service pds
,per_assignments_f pas
WHERE NVL(pds.final_process_date,hr_api.g_eot) >= p_effective_date
AND pds.last_standard_process_date <= p_effective_date
AND pds.period_of_service_id = pas.period_of_service_id
AND p_effective_date
BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pas.primary_flag = 'Y'
AND pas.assignment_id =p_assignment_id;
SELECT 'Y'
FROM pay_element_entries_f pef
WHERE pef.assignment_id = p_assignment_id
AND pef.element_link_id=cp_link_id
AND p_effective_date
BETWEEN pef.effective_start_date
AND pef.effective_end_date;
SELECT element.element_type_id , link.element_link_id,element.business_group_id
FROM pay_element_types_f_tl elementtl,
pay_element_types_f element,
pay_element_links_f link,
per_all_assignments_f asgt ,
per_periods_of_service service_period
WHERE
--element.element_type_id = elementtl.element_type_id
-- AND elementtl.language = USERENV('LANG')
--AND
asgt.business_group_id = link.business_group_id
AND asgt.business_group_id =service_period.business_group_id
AND element.element_type_id = link.element_type_id
AND service_period.period_of_service_id = asgt.period_of_service_id
AND p_effective_date
between element.effective_start_date and element.effective_end_date
AND p_effective_date
between asgt.effective_start_date and asgt.effective_end_date
AND p_effective_date
between link.effective_start_date and link.effective_end_date
AND element.indirect_only_flag = 'N'
AND ((link.payroll_id is NOT NULL AND
link.payroll_id = asgt.payroll_id)
OR (link.link_to_all_payrolls_flag = 'Y'
AND asgt.payroll_id IS NOT NULL)
OR (link.payroll_id IS NULL
AND link.link_to_all_payrolls_flag = 'N'))
AND (link.organization_id = asgt.organization_id
OR link.organization_id IS NULL)
AND (link.position_id = asgt.position_id
OR link.position_id IS NULL)
AND (link.job_id = asgt.job_id OR link.job_id IS NULL)
AND (link.grade_id = asgt.grade_id OR link.grade_id IS NULL)
AND (link.location_id = asgt.location_id
OR link.location_id IS NULL)
AND (link.pay_basis_id = asgt.pay_basis_id
OR link.pay_basis_id IS NULL)
AND (link.employment_category = asgt.employment_category
OR link.employment_category IS NULL)
AND (link.people_group_id IS NULL OR EXISTS
( SELECT 1 FROM pay_assignment_link_usages_f usage
WHERE usage.assignment_id = asgt.assignment_id
AND usage.element_link_id = link.element_link_id
AND p_effective_date
BETWEEN usage.effective_start_date
AND usage.effective_end_date))
AND (service_period.actual_termination_date
IS NULL OR (service_period.actual_termination_date IS NOT NULL
AND p_effective_date <=
DECODE(element.post_termination_rule, 'L',
service_period.last_standard_process_date, 'F',
NVL(service_period.final_process_date,hr_api.g_eot),
service_period.actual_termination_date) ))
AND asgt.assignment_id=p_assignment_id
-- AND asgt.business_group_id=2899
AND element.element_name='Recurring Entry Processor for Terminated EE'
ORDER BY element.effective_start_date DESC;
PROCEDURE UPDATE_PSI_ASS_DFF_COL
(
p_effective_start_date date,
p_element_entry_id number,
p_assignment_id number,
p_element_type_id Number
)
IS
--Cursor to fetch current Context value, Employment category and Business group for Employee
Cursor csr_get_curr_asg_dtls
IS
SELECT ass_attribute_category,
employment_category,
business_group_id,
effective_start_date,
effective_end_date,
object_version_number,
soft_coding_keyflex_id,
cagr_grade_def_id,
ass_attribute1,
ass_attribute2,
ass_attribute3,
ass_attribute4,
ass_attribute5,
ass_attribute6,
ass_attribute7,
ass_attribute8,
ass_attribute9,
ass_attribute10,
ass_attribute11,
ass_attribute12,
ass_attribute13,
ass_attribute14,
ass_attribute15,
ass_attribute16,
ass_attribute17,
ass_attribute18,
ass_attribute19,
ass_attribute20,
ass_attribute21,
ass_attribute22,
ass_attribute23,
ass_attribute24,
ass_attribute25,
ass_attribute26,
ass_attribute27,
ass_attribute28,
ass_attribute29,
ass_attribute30
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_start_date between effective_start_date and effective_end_date;
SELECT pcv_information1 --element_type_id
FROM pqp_configuration_values
WHERE pcv_information_category = 'PQP_GB_PENSERV_SCHEME_MAP_INFO'
AND business_group_id =c_business_group_id
AND pcv_information2 in ('NUVOS','PARTNER');
SELECT pcv_information1, --penserver_eligibility_context
pcv_information2, --mapped_segment
pcv_information3 --mapped_dff_segment
FROM pqp_configuration_values
WHERE pcv_information_category='PQP_GB_PENSERVER_ELIGBLTY_CONF'
AND business_group_id = c_business_group_id;
SELECT pcv_information1 --mapped_casual_emp_categories
FROM pqp_configuration_values
WHERE pcv_information_category='PQP_GB_PENSERVER_EMPLYMT_TYPE'
AND business_group_id = c_business_group_id
AND pcv_information2 = 'CASUAL';
SELECT pcv_information1 --mapped_non_casual_emp_cate
FROM pqp_configuration_values
WHERE pcv_information_category='PQP_GB_PENSERVER_EMPLYMT_TYPE'
AND business_group_id = c_business_group_id
AND pcv_information2 in ('FIXED','REGULAR');
SELECT employment_category,
ass_attribute_category
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND effective_start_date < p_effective_start_date;
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND employment_category = c_employment_category;
SELECT element_type_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND effective_start_date BETWEEN c_asg_start_date AND (p_effective_start_date-1)
ORDER BY effective_start_date;
hr_utility.set_location('Entering procedure pqp_gb_ad_ee.update_ass_dff_col',10);
l_segment_val_query := 'select '||l_rec_get_mapped_context.pcv_information2||' '||
'from per_all_assignments_f'||' '||
'where assignment_id = '||p_assignment_id||' '||
'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') between effective_start_date'||' '||
'and effective_end_date';
l_prior_asg_dtls_query := 'select employment_category, ass_attribute_category, '||
l_rec_get_mapped_context.pcv_information2||' '||
'from per_all_assignments_f'||' '||
'where assignment_id = '||p_assignment_id||' '||
'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') > effective_start_date';
l_query := 'select '||l_rec_get_mapped_context.pcv_information3||' '||
'from per_all_assignments_f'||' '||
'where assignment_id = '||p_assignment_id||' '||
'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') between effective_start_date'||' '||
'and effective_end_date';
hr_utility.set_location('Call update asg API in correction mode', 19);
hr_utility.set_location('Call update asg API in update_change_insert mode', 20);
l_call_mode := 'UPDATE_CHANGE_INSERT';
hr_utility.set_location('Call update asg API in update mode', 19);
l_call_mode := 'UPDATE';
--Now Call update API
hr_assignment_api.update_gb_emp_asg
(p_validate => false
,p_effective_date => p_effective_start_date
,p_datetrack_update_mode => l_call_mode
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number
,p_ass_attribute1 => l_rec_curr_asg_dtls.ass_attribute1
,p_ass_attribute2 => l_rec_curr_asg_dtls.ass_attribute2
,p_ass_attribute3 => l_rec_curr_asg_dtls.ass_attribute3
,p_ass_attribute4 => l_rec_curr_asg_dtls.ass_attribute4
,p_ass_attribute5 => l_rec_curr_asg_dtls.ass_attribute5
,p_ass_attribute6 => l_rec_curr_asg_dtls.ass_attribute6
,p_ass_attribute7 => l_rec_curr_asg_dtls.ass_attribute7
,p_ass_attribute8 => l_rec_curr_asg_dtls.ass_attribute8
,p_ass_attribute9 => l_rec_curr_asg_dtls.ass_attribute9
,p_ass_attribute10 => l_rec_curr_asg_dtls.ass_attribute10
,p_ass_attribute11 => l_rec_curr_asg_dtls.ass_attribute11
,p_ass_attribute12 => l_rec_curr_asg_dtls.ass_attribute12
,p_ass_attribute13 => l_rec_curr_asg_dtls.ass_attribute13
,p_ass_attribute14 => l_rec_curr_asg_dtls.ass_attribute14
,p_ass_attribute15 => l_rec_curr_asg_dtls.ass_attribute15
,p_ass_attribute16 => l_rec_curr_asg_dtls.ass_attribute16
,p_ass_attribute17 => l_rec_curr_asg_dtls.ass_attribute17
,p_ass_attribute18 => l_rec_curr_asg_dtls.ass_attribute18
,p_ass_attribute19 => l_rec_curr_asg_dtls.ass_attribute19
,p_ass_attribute20 => l_rec_curr_asg_dtls.ass_attribute20
,p_ass_attribute21 => l_rec_curr_asg_dtls.ass_attribute21
,p_ass_attribute22 => l_rec_curr_asg_dtls.ass_attribute22
,p_ass_attribute23 => l_rec_curr_asg_dtls.ass_attribute23
,p_ass_attribute24 => l_rec_curr_asg_dtls.ass_attribute24
,p_ass_attribute25 => l_rec_curr_asg_dtls.ass_attribute25
,p_ass_attribute26 => l_rec_curr_asg_dtls.ass_attribute26
,p_ass_attribute27 => l_rec_curr_asg_dtls.ass_attribute27
,p_ass_attribute28 => l_rec_curr_asg_dtls.ass_attribute28
,p_ass_attribute29 => l_rec_curr_asg_dtls.ass_attribute29
,p_ass_attribute30 => l_rec_curr_asg_dtls.ass_attribute30
,p_cagr_grade_def_id => l_cagr_grade_def_id
,p_cagr_concatenated_segments => l_cagr_concatenated_segments
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
hr_utility.set_location('Update of assignment complete',21);
hr_utility.set_location('Leaving procedure pqp_gb_ad_ee.update_ass_dff_col',22);
hr_utility.set_location('Proc: pqp_gb_ad_ee.update_ass_dff_col: Exception Section',23);
END UPDATE_PSI_ASS_DFF_COL;
SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id
AND p_effective_start_date BETWEEN effective_start_date AND effective_end_date
AND legislation_code = 'GB';
SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
max (decode (piv.name,'Reference', peev.screen_entry_value))
FROM pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE peev.element_entry_id = p_element_entry_id
AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id = piv.input_value_id
AND piv.element_type_id = p_element_type_id
AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.name in ('Type','Reference')
AND piv.legislation_code = 'GB'
ORDER BY piv.name DESC;
SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id_o
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
AND legislation_code = 'GB';
SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
max (decode (piv.name,'Reference', peev.screen_entry_value))
FROM pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE peev.element_entry_id = p_element_entry_id
AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id = piv.input_value_id
AND piv.element_type_id = p_element_type_id_o
AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.name in ('Type','Reference')
AND piv.legislation_code = 'GB'
ORDER BY piv.name DESC;
SELECT nvl(prrv.result_value,'Unknown')
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_assignment_actions pac,
pay_input_values_f piv ,
pay_payroll_actions ppa
WHERE prr.run_result_id = prrv.run_result_id
AND prr.entry_type = 'E'
AND PRR.source_type IN ('E', 'I')
AND prr.source_id = p_element_entry_id
AND pac.assignment_action_id = prr.assignment_action_id
AND pac.action_status IN ('C')
and ppa.action_type IN ('R','Q')
AND ppa.payroll_action_id = pac.payroll_action_id
AND pac.assignment_action_id = (SELECT max(pac1.assignment_action_id)
FROM pay_assignment_actions pac1,
pay_run_results prr1,
pay_payroll_actions ppa1
WHERE pac1.assignment_action_id = prr1.assignment_action_id
AND ppa1.payroll_action_id = pac1.payroll_action_id
AND prr1.source_id = p_element_entry_id
AND pac1.action_status IN ('C')
and ppa1.action_type IN ('R','Q')
and prr1.entry_type = 'E'
AND PRR1.source_type IN ('E', 'I') )
AND piv.legislation_code = 'GB'
AND piv.name = 'Reference'
AND piv.input_value_id = prrv.input_value_id
AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date ;
SELECT PRR.run_result_id
FROM pay_run_results PRR,
pay_assignment_actions ASA,
pay_payroll_actions PPA
WHERE PRR.source_id = p_element_entry_id
AND PRR.source_type IN ('E', 'I')
AND PRR.status IN ('P', 'PA', 'R', 'O')
AND ASA.assignment_action_id = PRR.assignment_action_id
AND asa.action_status IN ( 'C')
and ppa.action_type IN ('R','Q')
AND PPA.payroll_action_id = ASA.payroll_action_id
-- Check whether the run_result has been revered.
AND NOT EXISTS (SELECT null
FROM pay_run_results prr2
WHERE prr2.source_id = PRR.run_result_id
AND prr2.source_type IN ('R', 'V'));