[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,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_selection in varchar2
,p_is_element_name in varchar2
,p_element_type_id in varchar2
,p_is_element_set in varchar2
,p_element_set_id in varchar2
) is
-- ==========================================================================
-- Cursor to get the run result value for given input_value_id and
-- element_type_id along with assignment_action_id for (P)rocessed actions.
-- ==========================================================================
cursor c_get_run_value (p_asg_action_id in number
,p_element_type_id in number
,p_effective_date in date
,p_input_value_name in varchar2) is
select prv.result_value
from pay_run_results prr
,pay_run_result_values prv
where prr.assignment_action_id = p_asg_action_id
and prr.element_type_id = p_element_type_id
and prv.input_value_id in
(select distinct input_value_id
from pay_input_values_f
where element_type_id = p_element_type_id
and p_effective_date between effective_start_date
and effective_end_date
and name = p_input_value_name)
and prv.run_result_id = prr.run_result_id;
select per.party_id
from per_people_f per,
per_assignments_f paf
where per.person_id = paf.person_id
and paf.assignment_id = c_asssignment_id
and c_effective_date between per.effective_start_date
and per.effective_end_date
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
hr_utility.set_location('Selection Criteria : '||p_selection_criteria, 15);
hr_utility.set_location('Element Selection : '||p_element_selection, 15);
if p_selection_criteria = 'Assignment Set' then
if p_element_selection = 'Element Name' then
l_selcrs :=
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = :1
and ppa.action_type in
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned between :2
and :3
and prr.assignment_action_id = paa.assignment_action_id
and prr.status in (''P'',''PA'')
and prr.element_type_id = :4
and exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = :5
and hasa.assignment_id = paa.assignment_id
and upper(hasa.include_or_exclude) = ''I'')';
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = :1
and ppa.action_type in
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned between :2
and :3
and prr.assignment_action_id = paa.assignment_action_id
and prr.status in (''P'',''PA'')
and prr.element_type_id in
(select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = :4
and petr.include_or_exclude = ''I''
union all
select distinct pet1.element_type_id
from pay_element_types_f pet1
where pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = :5)
minus
select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = :6
and petr.include_or_exclude = ''E''
)
and exists
(select 1
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = :7
and hasa.assignment_id = paa.assignment_id
and upper(hasa.include_or_exclude) = ''I'')';
end if;-- if p_element_selection
elsif p_selection_criteria = 'OSS Student Person Group' then
-- Call OSS Dynamic SQL to get the party_ids for groupid
l_grp_selcrs := get_person_id(to_number(p_person_group_id));
if p_element_selection = 'Element Name' then
l_selcrs :=
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = ' || p_business_group_id || '
and ppa.action_type in
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned
between '||''''|| fnd_date.canonical_to_date(p_begin_date_paid) ||''''|| '
and '||''''|| fnd_date.canonical_to_date(p_end_date_paid) ||''''|| '
and prr.assignment_action_id = paa.assignment_action_id
and prr.status in (''P'',''PA'')
and prr.element_type_id = '|| p_element_type_id ||'
and exists
(select 1
from per_people_extra_info pei,
per_people_f per ,
per_assignments_f paf
where pei.person_id = per.person_id
and paf.person_id = per.person_id
and paf.assignment_id = paa.assignment_id
and ppa.date_earned between per.effective_start_date
and per.effective_end_date
and ppa.date_earned between paf.effective_start_date
and paf.effective_end_date
and paf.assignment_type =''E''
and paf.primary_flag=''Y''
and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
and per.party_id in ' || l_grp_party_list_id || '
and per.business_group_id= ' || p_business_group_id || '
)';
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = ' || p_business_group_id || '
and ppa.action_type in
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned
between '||''''|| fnd_date.canonical_to_date(p_begin_date_paid) ||''''|| '
and '||''''|| fnd_date.canonical_to_date(p_end_date_paid) ||''''|| '
and prr.assignment_action_id = paa.assignment_action_id
and prr.status IN (''P'',''PA'')
and prr.element_type_id in
(select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = ' || p_element_set_id || '
and petr.include_or_exclude = ''I''
union all
select distinct pet1.element_type_id
from pay_element_types_f pet1
where pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = ' || p_element_set_id || ')
minus
select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = ' || p_element_set_id || '
and petr.include_or_exclude = ''E''
)
and exists
(select 1
from per_people_extra_info pei,
per_people_f per ,
per_assignments_f paf
where pei.person_id = per.person_id
and paf.person_id = per.person_id
and paf.assignment_id = paa.assignment_id
and ppa.date_earned between per.effective_start_date
and per.effective_end_date
and ppa.date_earned between paf.effective_start_date
and paf.effective_end_date
and paf.assignment_type =''E''
and paf.primary_flag =''Y''
and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
and per.party_id in ' || l_grp_party_list_id || '
and per.business_group_id=' || p_business_group_id || ' )';
end if;--if p_element_selection
elsif p_selection_criteria = 'ALL' then
if p_element_selection = 'Element Name' then
l_selcrs :=
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = :1
and ppa.action_type in
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned between :2
and :3
and prr.assignment_action_id = paa.assignment_action_id
and prr.status IN (''P'',''PA'')
and prr.element_type_id = :4
and exists
(select 1
from per_people_extra_info pei,
per_assignments_f paf
where pei.person_id = paf.person_id
and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
and paf.assignment_id = paa.assignment_id)';
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = :1
and ppa.action_type IN
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned between :2
and :3
and prr.assignment_action_id = paa.assignment_action_id
and prr.status in (''P'',''PA'')
and prr.element_type_id in
(select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = :4
and petr.include_or_exclude = ''I''
union all
select distinct pet1.element_type_id
from pay_element_types_f pet1
where pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = :5)
minus
select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = :6
and petr.include_or_exclude = ''E''
)
and exists
(select 1
from per_people_extra_info pei,
per_assignments_f paf
where pei.person_id = paf.person_id
and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
and paf.assignment_id = paa.assignment_id)';
end if;--if p_element_selection
elsif p_selection_criteria = 'Social Security Number' then
if p_element_selection = 'Element Name' then
l_selcrs :=
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = :1
and ppa.action_type in
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned between :2
and :3
and prr.assignment_action_id = paa.assignment_action_id
and prr.status IN (''P'',''PA'')
and prr.element_type_id = :4
and exists
(select 1
from per_people_extra_info pei,
per_people_f per ,
per_assignments_f paf
where pei.person_id = per.person_id
and paf.person_id = per.person_id
and paf.assignment_id = paa.assignment_id
and ppa.date_earned between per.effective_start_date
and per.effective_end_date
and ppa.date_earned between paf.effective_start_date
and paf.effective_end_date
and paf.assignment_type = ''E''
and paf.primary_flag = ''Y''
and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
and per.national_identifier = :5
and per.business_group_id = :6 )';
'select paa.assignment_id
,paa.assignment_action_id
,paa.tax_unit_id
,ppa.date_earned
,ppa.payroll_id
,prr.element_type_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
where ppa.action_status = ''C''
and paa.action_status = ''C''
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = :1
and ppa.action_type in
(''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
and ppa.date_earned between :2
and :3
and prr.assignment_action_id = paa.assignment_action_id
and prr.status IN (''P'',''PA'')
and prr.element_type_id in
(select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = :4
and petr.include_or_exclude = ''I''
union all
select distinct pet1.element_type_id
from pay_element_types_f pet1
where pet1.classification_id in
(select classification_id
from pay_ele_classification_rules
where element_set_id = :5)
minus
select distinct petr.element_type_id
from pay_element_type_rules petr
where petr.element_set_id = :6
and petr.include_or_exclude = ''E''
)
and exists
(select 1
from per_people_extra_info pei,
per_people_f per ,
per_assignments_f paf
where pei.person_id = per.person_id
and paf.person_id = per.person_id
and paf.assignment_id = paa.assignment_id
and ppa.date_earned between per.effective_start_date
and per.effective_end_date
and ppa.date_earned between paf.effective_start_date
and paf.effective_end_date
and paf.assignment_type =''E''
and paf.primary_flag = ''Y''
and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
and per.national_identifier = :7
and per.business_group_id = :8
)';
end if; --if p_element_selection
end if; --if p_selection_criteria