[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ipv.input_value_id INTO l_input_value_id
FROM PAY_INPUT_VALUES_F ipv,
PAY_ELEMENT_TYPES_F ele
WHERE ele.element_name = 'Student Loan'
and ele.legislation_code = 'GB'
AND ipv.name = p_name
and ipv.legislation_code = 'GB'
AND ele.element_type_id = ipv.element_type_id
AND p_effective_date between ele.effective_start_date
and ele.effective_end_date
AND p_effective_date between ipv.effective_start_date
and ipv.effective_end_date;
select balance_type_id
from pay_balance_types
where balance_name = p_name
and legislation_code = 'GB';
select prr.assignment_action_id,
prr.source_id
from pay_run_results prr,
pay_element_types_f ele
where prr.assignment_action_id in (
SELECT /*+ use_nl(paa,ppa,ptp,ses) */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
fnd_sessions ses
WHERE paa.assignment_id = p_asg
AND paa.action_status = 'C'
AND ses.session_id = userenv('sessionid')
AND ppa.payroll_action_id = paa.payroll_action_id
AND ses.effective_date between ptp.start_date and ptp.end_date
AND ppa.time_period_id = ptp.time_period_id
AND (paa.source_action_id is not null
or ppa.action_type in ('I','V','B'))
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
AND prr.element_type_id = ele.element_type_id
AND prr.source_id = p_ent_id;
select prr.assignment_action_id,
prr.source_id
from pay_run_results prr,
pay_element_types_f ele
where prr.assignment_action_id in (
SELECT /*+ use_nl(paa,ppa,ptp,ses) */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
fnd_sessions ses,
per_time_periods ptp
WHERE paa.assignment_id = p_asg
AND paa.action_status = 'C'
AND ses.session_id = userenv('sessionid')
AND ptp.payroll_id = ppa.payroll_id
AND ses.effective_date between ptp.start_date and ptp.end_date
AND ppa.effective_date between ptp.start_date and ptp.end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null
or ppa.action_type in ('I','V','B'))
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
AND prr.element_type_id = ele.element_type_id
and ele.element_type_id = p_element_type_id
and prr.source_id = p_ent;
PROCEDURE Update_Court_Order(
p_datetrack_update_mode in varchar2
,p_effective_date in date
,p_business_group_id in number
,p_element_entry_id in number
,p_object_version_number in out nocopy number
,p_subpriority in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date) is
l_update_warning BOOLEAN;
py_element_entry_api.update_element_entry(
p_validate => FALSE,
p_datetrack_update_mode => p_datetrack_update_mode,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_element_entry_id => p_element_entry_id,
p_object_version_number => p_object_version_number,
p_subpriority => p_subpriority,
P_EFFECTIVE_START_DATE =>p_effective_start_date,
P_EFFECTIVE_END_DATE =>p_effective_end_date,
P_UPDATE_WARNING =>l_update_warning);
END Update_Court_Order;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select element_type_id
from pay_element_types_f
where element_name = 'Student Loan'
and legislation_code = 'GB';
PROCEDURE Delete_Student_Loan(
p_datetrack_mode in VARCHAR2
,p_element_entry_id in NUMBER
,p_effective_date in DATE
,p_object_version_number in NUMBER) IS
l_object_version_number NUMBER;
l_delete_warning BOOLEAN;
py_element_entry_api.delete_element_entry(
p_validate => FALSE,
p_datetrack_delete_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_element_entry_id => p_element_entry_id,
p_object_version_number => l_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_delete_warning => l_delete_warning
);
END Delete_Student_Loan;
PROCEDURE Update_Student_Loan(
p_datetrack_update_mode in varchar2
,p_effective_date in date
,p_business_group_id in number
,p_element_entry_id in number
,p_object_version_number in out nocopy number
,p_start_date in VARCHAR2
,p_end_date in VARCHAR2
,p_subpriority in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date) is
l_update_warning BOOLEAN;
py_element_entry_api.update_element_entry(
p_validate => FALSE,
p_datetrack_update_mode => p_datetrack_update_mode,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_element_entry_id => p_element_entry_id,
p_object_version_number => p_object_version_number,
P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Start Date',
p_effective_date),
P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('End Date',
p_effective_date),
P_SUBPRIORITY =>p_subpriority,
P_ENTRY_VALUE1 =>p_start_date,
P_ENTRY_VALUE2 =>p_end_date,
P_EFFECTIVE_START_DATE =>p_effective_start_date,
P_EFFECTIVE_END_DATE =>p_effective_end_date,
P_UPDATE_WARNING =>l_update_warning);
END Update_Student_Loan;
SELECT scl.segment1
FROM hr_soft_coding_keyflex scl,
fnd_sessions fs,
pay_payrolls_f ppf,
per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND fs.session_id = userenv('sessionid')
AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppf.payroll_id = paaf.payroll_id
AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT piv.input_value_id
FROM fnd_sessions fs,
pay_element_types_f pet,
pay_input_values_f piv
WHERE fs.session_id = userenv('sessionid')
AND pet.element_name = p_ele_name
AND pet.business_group_id IS NULL
AND pet.legislation_code = 'GB'
AND fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pet.element_type_id = piv.element_type_id
AND piv.name = p_iv_name
AND piv.business_group_id IS NULL
AND piv.legislation_code = 'GB'
AND fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT person_id
FROM per_all_assignments_f paaf,
fnd_sessions fs
WHERE fs.session_id = userenv('sessionid')
AND paaf.assignment_id =p_assignment_id
AND fs.effective_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT ptpt.number_per_fiscal_year
FROM per_all_assignments_f papf,
pay_all_payrolls_f pap,
per_time_period_types ptpt
WHERE papf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN papf.effective_start_date and papf.effective_end_date
AND pap.payroll_id = papf.payroll_id
AND p_date_earned BETWEEN pap.effective_start_date and pap.effective_end_date
AND pap.period_type=ptpt.period_type;
SELECT nvl(ptp.regular_payment_date , to_date('01-01-0001','DD-MM-YYYY'))
FROM per_all_assignments_f papf,
pay_all_payrolls_f pap,
per_time_periods ptp
WHERE papf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN papf.effective_start_date and papf.effective_end_date
AND pap.payroll_id = papf.payroll_id
AND p_date_earned BETWEEN pap.effective_start_date and pap.effective_end_date
AND pap.payroll_id=ptp.payroll_id
AND p_date_earned BETWEEN ptp.start_date and ptp.end_date;
SELECT /*+ ORDERED use_nl(papf, paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
count(*) cnt
from per_all_people_f papf,
per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
pay_input_values_f piv1 ,
pay_input_values_f piv2,
pay_element_entries_f peef1,
pay_element_entries_f peef2,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
hr_soft_coding_keyflex scl
where papf.person_id = p_person_id
and papf.person_id = paaf1.person_id
and papf.person_id = paaf2.person_id
-- and paaf1.person_id = paaf2.person_id -- redundant
and ppf.payroll_id = paaf2.payroll_id
and ppf.payroll_id = paaf1.payroll_id
AND scl.segment1 = p_asg_tax_ref
and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
and piv1.input_value_id = peev1.input_value_id
and piv2.input_value_id = peev2.input_value_id
AND paaf1.assignment_id = peef1.assignment_id
AND paaf2.assignment_id = peef2.assignment_id
and peef1.element_entry_id = peef2.element_entry_id
and peev1.element_entry_id = peev2.element_entry_id
and piv1.element_type_id = piv2.element_type_id
AND peef1.element_entry_id = peev1.element_entry_id
AND peef2.element_entry_id = peev2.element_entry_id -- AND fs.session_id = userenv('sessionid')
AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
and peev1.screen_entry_value = p_reference
group by peev1.screen_entry_value,peev2.screen_entry_Value
having ( peev2.screen_entry_Value =p_entry_value) ;
SELECT /*+ ORDERED use_nl(papf, paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
count(*) cnt
from per_all_people_f papf,
per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
pay_input_values_f piv1 ,
pay_input_values_f piv2,
pay_element_entries_f peef1,
pay_element_entries_f peef2,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
hr_soft_coding_keyflex scl
where papf.person_id = p_person_id
and papf.person_id = paaf1.person_id
and papf.person_id = paaf2.person_id
-- and paaf1.person_id = paaf2.person_id -- redundant
and ppf.payroll_id = paaf2.payroll_id
and ppf.payroll_id = paaf1.payroll_id
AND scl.segment1 = p_asg_tax_ref
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
and piv1.input_value_id = peev1.input_value_id
and piv2.input_value_id = peev2.input_value_id
AND paaf1.assignment_id = peef1.assignment_id
AND paaf2.assignment_id = peef2.assignment_id
and peef1.element_entry_id = peef2.element_entry_id
and peev1.element_entry_id = peev2.element_entry_id
and piv1.element_type_id = piv2.element_type_id
AND peef1.element_entry_id = peev1.element_entry_id
AND peef2.element_entry_id = peev2.element_entry_id -- AND fs.session_id = userenv('sessionid')
AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
and peev1.screen_entry_value = p_reference
group by peev1.screen_entry_value;
SELECT 1 cnt
FROM per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl,
pay_element_entries_f peef,
pay_element_entry_values_f peev
WHERE paaf1.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND paaf1.person_id = paaf2.person_id
AND nvl(paaf2.primary_flag, 'N') = 'Y'
AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.payroll_id = ppf.payroll_id
AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND paaf2.assignment_id = peef.assignment_id
AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = p_reference;
SELECT ptp.start_date
FROM per_time_periods ptp
WHERE ptp.payroll_id = l_asg_payroll_id
AND p_date_earned =ptp.regular_payment_date;
SELECT ppf.payroll_id
FROM per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl,
pay_element_entries_f peef,
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1,
per_all_people_f papf
WHERE
p_date_earned BETWEEN paaf1.effective_start_date and paaf1.effective_end_date
AND paaf1.person_id = paaf2.person_id
AND p_date_earned BETWEEN paaf2.effective_start_date and paaf2.effective_end_date
AND paaf2.payroll_id = ppf.payroll_id
AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND paaf2.assignment_id = peef.assignment_id
AND p_date_earned BETWEEN peef.effective_start_date and peef.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
AND peev.element_entry_id=peev1.element_entry_id
AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = p_person_id
AND papf.person_id = paaf1.person_id
AND papf.person_id = paaf2.person_id;
SELECT ppf.payroll_id
FROM per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl,
pay_element_entries_f peef,
pay_element_entry_values_f peev
WHERE paaf1.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND paaf1.person_id = paaf2.person_id
AND nvl(paaf2.primary_flag, 'N') = 'Y'
AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.payroll_id = ppf.payroll_id
AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND paaf2.assignment_id = peef.assignment_id
AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id);
SELECT nvl(ptp.regular_payment_date , to_date('01-01-0001','DD-MM-YYYY'))
FROM per_time_periods ptp
WHERE p_date_earned BETWEEN ptp.start_date and ptp.end_date
AND ptp.payroll_id =p_payroll_id;
SELECT ppf.payroll_id
FROM per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl,
pay_element_entries_f peef,
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1,
per_all_people_f papf
WHERE p_date_earned BETWEEN paaf1.effective_start_date and paaf1.effective_end_date
AND paaf1.person_id = paaf2.person_id
AND p_date_earned BETWEEN paaf2.effective_start_date and paaf2.effective_end_date
AND paaf2.payroll_id = ppf.payroll_id
AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND paaf2.assignment_id = peef.assignment_id
AND p_date_earned BETWEEN peef.effective_start_date and peef.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
AND peev.element_entry_id=peev1.element_entry_id
AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = p_person_id
AND papf.person_id = paaf1.person_id
AND papf.person_id = paaf2.person_id;
SELECT ppf.payroll_id
FROM fnd_sessions fs,
per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl,
pay_element_entries_f peef,
pay_element_entry_values_f peev
WHERE paaf1.assignment_id = p_assignment_id
AND fs.session_id = userenv('sessionid')
AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND paaf1.person_id = paaf2.person_id
AND nvl(paaf2.primary_flag, 'N') = 'Y'
AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.payroll_id = ppf.payroll_id
AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND paaf2.assignment_id = peef.assignment_id
AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id);
SELECT number_per_fiscal_year
FROM per_time_periods ptp,
per_time_period_types ptpt
WHERE p_date_earned BETWEEN ptp.start_date AND ptp.end_Date
AND ptp.payroll_id = l_payroll_id
AND ptp.period_type = ptpt.period_type;
SELECT peef.element_entry_id
FROM per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl,
pay_element_entries_f peef,
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1,
per_all_people_f papf
WHERE --paaf1.assignment_id = p_assignment_id AND
p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND paaf1.person_id = paaf2.person_id
AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.payroll_id = ppf.payroll_id
AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND paaf2.assignment_id = peef.assignment_id
AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
AND peev1.element_entry_id=peev.element_entry_id
AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
AND peef.target_entry_id IS NULL
AND papf.person_id =p_person_id
AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = paaf1.person_id
AND papf.person_id = paaf2.person_id;
SELECT peef.element_entry_id
FROM
per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl,
pay_element_entries_f peef,
pay_element_entry_values_f peev
WHERE paaf1.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND paaf1.person_id = paaf2.person_id
AND nvl(paaf2.primary_flag, 'N') = 'Y'
AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.payroll_id = ppf.payroll_id
AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
AND paaf2.assignment_id = peef.assignment_id
AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id IN (g_cto_main_ref_id, g_cto_ntpp_main_ref_id)
AND peef.target_entry_id IS NULL
AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = p_reference;
SELECT peev.screen_entry_value
FROM pay_element_entry_values_f peev
WHERE p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
AND peev.element_entry_id = l_entry_id
AND peev.input_value_id IN (l_cto_iv_id, l_cto_ntpp_iv_id);
SELECT /*+ ORDERED use_nl(paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
peev2.screen_entry_value main_entry_value
from per_all_assignments_f paaf1,
per_all_assignments_f paaf2,
pay_all_payrolls_f ppf,
pay_input_values_f piv1 ,
pay_input_values_f piv2,
pay_element_entries_f peef1,
pay_element_entries_f peef2,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2 ,
hr_soft_coding_keyflex scl
where paaf1.assignment_id = p_assignment_id
AND paaf2.assignment_id = paaf1.assignment_id
and paaf2.payroll_id = ppf.payroll_id
and paaf1.payroll_id = ppf.payroll_id
AND peef1.assignment_id = paaf1.assignment_id
AND peef2.assignment_id = paaf2.assignment_id
and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
and piv1.input_value_id = peev1.input_value_id
and piv2.input_value_id = peev2.input_value_id
and peef1.element_entry_id = peef2.element_entry_id
and peev1.element_entry_id = peev2.element_entry_id
and piv1.element_type_id = piv2.element_type_id
AND peef1.element_entry_id = peev1.element_entry_id
AND peef2.element_entry_id = peev2.element_entry_id
AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_asg_tax_ref
and peev1.screen_entry_value = p_reference;
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 <> p_asg_action_id
AND 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_date_earned 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'));