[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning
from fnd_lookup_values_vl
where lookup_type = p_lookup_type
and upper(lookup_code) = upper(p_lookup_code);
select piv.input_value_id
,piv.name
,piv.display_sequence
,piv.lookup_type
,piv.max_value
,piv.min_value
,piv.default_value
,piv.warning_or_error
from pay_input_values_f piv
where piv.element_type_id = c_ele_type_id
and c_effective_date between piv.effective_start_date
and piv.effective_end_date
order by piv.display_sequence;
Select distinct
pel.element_type_id
,pee.element_entry_id
,pel.element_link_id
From pay_element_entries_f pee
,pay_element_links_f pel
,per_all_assignments_f paf
Where (c_end_date between pee.effective_start_date
and pee.effective_end_date
or
pee.effective_end_date between c_start_date
and c_end_date
)
and pee.assignment_id = c_assignment_id
and (c_end_date between pel.effective_start_date
and pel.effective_end_date
or
pel.effective_end_date between c_start_date
and c_end_date
)
and pee.element_link_id = pel.element_link_id
and pel.element_type_id in
(Select pet.element_type_id
From pay_element_types_f pet
Where pet.element_information_category = 'US_EARNINGS'
and pet.business_group_id = c_bg_id
and pet.element_information1 ='SE'
and c_end_date between pet.effective_start_date
and pet.effective_end_date)
and paf.assignment_id = pee.assignment_id
and paf.business_group_id = c_bg_id
and pel.business_group_id = c_bg_id
and c_end_date between paf.effective_start_date
and paf.effective_end_date
order by pee.element_entry_id desc;
Select pev.screen_entry_value
from pay_element_entry_values_f pev
where pev.input_value_id = c_input_value_id
and pev.element_entry_id = c_element_entry_id
and (c_end_date between pev.effective_start_date
and pev.effective_end_date
or
(pev.effective_end_date >= c_start_date and
pev.effective_start_date <= c_end_date)
)
order by pev.effective_start_date desc;
select piv.input_value_id
,piv.name
from pay_input_values_f piv
where piv.element_type_id = c_ele_type_id
and piv.name = c_ipv_name
order by piv.display_sequence;
select max(pee.effective_start_date)
,max(pee.effective_end_date)
from pay_element_entries_f pee
where pee.assignment_id = c_assignment_id
and pee.element_entry_id = c_element_entry_id;
,p_selection_criteria in varchar2
,p_business_group_id in varchar2
,p_is_asg_set in varchar2
,p_assignment_set in varchar2
,p_is_ssn in varchar2
,p_ssn in varchar2
,p_is_person_group in varchar2
,p_person_group_id in varchar2
,p_element_type_id in varchar2
) is
-- ===========================================================================
-- Cursor to get the Assignment ID and Assignment Number
-- if the selection criteria is Person Group
-- ===========================================================================
cursor get_stu_details (c_ssn in varchar2
,c_business_group_id in number
,c_effective_date in date) is
select paf.assignment_id,
paf.assignment_number
from per_people_f per,
per_assignments_f paf
where per.national_identifier = c_ssn
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and c_effective_date between per.effective_start_date
and per.effective_end_date
and paf.person_id = per.person_id
and per.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
select element_name
from pay_element_types_f
where element_type_id = p_element_type_id
and p_effective_date between effective_start_date
and effective_end_date;
hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 5);
if p_selection_criteria = 'Assignment Set' then
hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 11);
'select per.national_identifier,
paf.assignment_id,
paf.assignment_number,
per.party_id
from per_assignments_f paf,
per_people_f per
where paf.person_id = per.person_id
and paf.assignment_type =''E''
and paf.primary_flag=''Y''
and :1 between paf.effective_start_date
and paf.effective_end_date
and :2 between per.effective_start_date
and per.effective_end_date
and per.business_group_id=:3
and exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = :4
and hasa.assignment_id = paf.assignment_id
and upper(hasa.include_or_exclude) = ''I'')
and exists
(select 1
from per_people_extra_info pei
where pei.person_id = per.person_id
and pei.information_type = ''PQP_OSS_PERSON_DETAILS'')';
elsif p_selection_criteria = 'OSS Student Person Group' then
hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 12);
elsif p_selection_criteria = 'ALL' then
hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 13);
'select per.national_identifier,
paf.assignment_id,
paf.assignment_number,
per.party_id
from per_assignments_f paf,
per_people_f per
where paf.person_id = per.person_id
and :1 between paf.effective_start_date
and paf.effective_end_date
and :2 between per.effective_start_date
and per.effective_end_date
and per.business_group_id=:3
and paf.assignment_type =''E''
and paf.primary_flag=''Y''
and exists
(select 1
from per_people_extra_info pei
where pei.person_id = per.person_id
and pei.information_type = ''PQP_OSS_PERSON_DETAILS'')';
elsif p_selection_criteria = 'Social Security Number' then
hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 14);
'select per.national_identifier,
paf.assignment_id,
paf.assignment_number,
per.party_id
from per_people_f per,
per_assignments_f paf
where per.national_identifier = :1
and :2 between per.effective_start_date
and per.effective_end_date
and paf.person_id = per.person_id
and :3 between paf.effective_start_date
and paf.effective_end_date
and per.business_group_id=:4
and paf.assignment_type =''E''
and paf.primary_flag=''Y''
and exists (select 1
from per_people_extra_info pei
where pei.person_id = per.person_id
and pei.information_type = ''PQP_OSS_PERSON_DETAILS'')';
hr_utility.set_location('Delete the PL/SQL table and create null records ', 15);
l_ipv.delete;
if p_selection_criteria = 'OSS Student Person Group' then
fetch pri_cur into l_party_id;
if p_selection_criteria = 'OSS Student Person Group' then
-- If selection criteria is OSS Person Group ,then we have only party_id
-- now so we need to have different query for this criteria and get
-- the same financial aid details
l_sqlstmt :=
'select authorization_id,
authorized_amt,
fund_id,
authorization_start_date,
authorization_end_date,
social_security_number
from igf_se_authorization_v
where person_id = nvl(:1, person_id)
and sys_fund_source_code = nvl(:2, sys_fund_source_code)';
'select authorization_id,
authorized_amt,
fund_id,
authorization_start_date,
authorization_end_date,
social_security_number
from igf_se_authorization_v
where social_security_number = nvl(:1, social_security_number)
and person_id = nvl(:2, person_id)
and sys_fund_source_code = nvl(:3, sys_fund_source_code)';
if p_selection_criteria = 'OSS Student Person Group' then
for c1 in get_stu_details ( l_stusys_ssn
,p_business_group_id
,l_effective_date
)
loop
l_assignment_id := c1.assignment_id;