[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ppa.business_group_id
,nvl(pay_core_utils.get_parameter('END_DATE',ppa.legislative_parameters), to_char(sysdate,'YYYY') || '/12/31')
,nvl(pay_core_utils.get_parameter('START_DATE',ppa.legislative_parameters),to_char(sysdate,'YYYY/MM/DD'))
/*,pay_core_utils.get_parameter('STATE_DATE',ppa.legislative_parameters) */
,pay_core_utils.get_parameter('TAX_UNIT_ID',ppa.legislative_parameters)
,pay_core_utils.get_parameter('EVS_CATEGORY',ppa.legislative_parameters)
INTO
g_business_group_id
,g_end_date
,g_start_date
,g_tax_unit_id
,g_evs_category
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_payroll_action_id;
SELECT hou.organization_id organization_id,
hou.name org_name
FROM hr_all_organization_units hou,
hr_organization_information hoi
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL';
SELECT hoi.org_information1 requester_code,
hou.name org_name
FROM hr_all_organization_units hou,
hr_organization_information hoi
WHERE hoi.organization_id = hou.organization_id
AND hoi.organization_id = p_organization_id
AND hoi.org_information_context = 'EVS Filing';
SELECT hou.name org_name
FROM hr_all_organization_units hou
WHERE hou.organization_id = p_organization_id;
'SELECT /*+ INDEX(hsck,HR_SOFT_CODING_KEYFLEX_PK),
INDEX(HR_ORGANIZATION_UNITS_PK,hou)*/
DISTINCT ppf.person_id
FROM per_all_people_f ppf
,per_all_assignments_f paf
,hr_soft_coding_keyflex hsck
,hr_organization_units hou
,hr_organization_information hoi
WHERE paf.assignment_type = ''E''
AND paf.primary_flag = ''Y''
AND paf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
AND paf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND paf.person_id = ppf.person_id
AND ppf.effective_start_date <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
AND ppf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
And ppf.business_group_id +0 = ' ||g_business_group_id || '
AND hou.business_group_id + 0 = ' ||g_business_group_id || '
AND hou.date_from <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
AND nvl(hou.date_to,to_date(''4712-12-31'',''YYYY-MM-DD''))
>= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
and hsck.segment1= nvl('''||g_tax_unit_id||''',to_char(hou.organization_id))
AND ppf.business_group_id = hou.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = ''CLASS''
AND hoi.org_information1 = ''HR_LEGAL''
AND hoi.org_information2 = ''Y''
AND :payroll_action_id is not NULL
ORDER BY ppf.person_id';
'select distinct ppf.person_id
from per_all_people_f ppf
,per_all_assignments_f paf
,hr_soft_coding_keyflex hsck
,hr_organization_units hou
,hr_organization_information hoi
where paf.assignment_type = ''E''
and paf.primary_flag = ''Y''
and paf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
and paf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.person_id = ppf.person_id
And ppf.business_group_id +0 = ' ||g_business_group_id || '
and ppf.effective_start_date <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
and ppf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
and ppf.business_group_id = hou.business_group_id
and hou.date_from <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
and nvl(hou.date_to,to_date(''4712-12-31'',''YYYY-MM-DD''))
>= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
and hsck.segment1= nvl('''||g_tax_unit_id||''',to_char(hou.organization_id))
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = ''CLASS''
and hoi.org_information1 = ''HR_LEGAL''
and hoi.org_information2 = ''Y''
and :payroll_action_id is not NULL
order by ppf.person_id'; */
'select distinct ppf.person_id
from
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
where ppf.person_id = paf.person_id
and ppf.effective_start_date <= to_date('''||g_end_date||''',''YYYY/MM/DD/'')
and ppf.effective_end_date >= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
and paf.effective_start_date <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
and paf.effective_end_date >= to_date('''|| g_start_date||''',''YYYY/MM/DD/'')
and hsck.segment1 in
(
select distinct hsck2.segment1
from hr_organization_information hoi
,hr_organization_units hou
,hr_soft_coding_keyflex hsck2
where
hou.business_group_id +0 = ' || g_business_group_id || '
and hsck2.segment1 = nvl('''||g_tax_unit_id||''',to_char(hou.organization_id))
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = ''CLASS''
and hoi.org_information1 = ''HR_LEGAL''
and hoi.org_information2 = ''Y''
and hou.date_from <= to_date('''|| g_end_date||''',''YYYY/MM/DD/'')
and nvl(hou.date_to,to_date(''4712-12-31'',''YYYY-MM-DD''))
>= to_date('''||g_start_date||''',''YYYY/MM/DD/'')
)
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.assignment_type = ''E''
and paf.primary_flag = ''Y''
And ppf.business_group_id +0 = ' ||g_business_group_id || '
and :payroll_action_id is not NULL
order by ppf.person_id';
select distinct paf.assignment_id
,hsck.segment1
from
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
,per_periods_of_service pps
where ppf.person_id = pps.person_id
and pps.date_start
between to_date(g_start_date,'YYYY/MM/DD/')
and to_date(g_end_date,'YYYY/MM/DD/')
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
)
and ppf.person_id = paf.person_id
/* and pps.date_start = paf.effective_start_date */
and hsck.segment1 in
(
select distinct hsck2.segment1
from
hr_organization_information hoi
,hr_organization_units hou
,hr_soft_coding_keyflex hsck2
where
hou.business_group_id +0 = g_business_group_id
and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.org_information2 = 'Y'
and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
>= to_date(g_start_date,'YYYY/MM/DD/')
)
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
And ppf.business_group_id +0 = g_business_group_id
and paf.person_id between stperson and endperson
order by paf.assignment_id;
select /*+ index(hou,HR_ORGANIZATION_UNITS_FK1)*/
distinct paf.assignment_id,
hsck.segment1
from per_all_people_f ppf ,
per_all_assignments_f paf,
hr_soft_coding_keyflex hsck,
hr_all_organization_units hou,
hr_organization_information hoi,
per_assignment_status_types past
where paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and paf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and paf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
and paf.person_id between stperson and endperson
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and paf.person_id = ppf.person_id
and ppf.current_employee_flag = 'Y'
and ppf.effective_start_date = (select max(ppf2.effective_start_date)
from per_all_people_f ppf2
where ppf.person_id = ppf2.person_id
and ppf2.current_employee_flag = 'Y'
and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
)
and ppf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ppf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
and ppf.business_group_id +0 = g_business_group_id
and hou.business_group_id + 0 = g_business_group_id
and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
>= to_date(g_start_date,'YYYY/MM/DD/')
and hsck.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
and ppf.business_group_id = hou.business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.org_information2 = 'Y'
order by paf.assignment_id;
/* select distinct paf.assignment_id
,hsck.segment1
from
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
,per_periods_of_service pps
,per_assignment_status_types past
where ppf.person_id = paf.person_id
and ppf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ppf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
and paf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and paf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
and ppf.current_employee_flag = 'Y'
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.current_employee_flag = 'Y'
and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
)
and hsck.segment1 in
(
select distinct hsck2.segment1
from hr_organization_information hoi
,hr_organization_units hou
,hr_soft_coding_keyflex hsck2
where
hou.business_group_id +0 = g_business_group_id
and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.org_information2 = 'Y'
and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
>= to_date(g_start_date,'YYYY/MM/DD/')
)
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and paf.assignment_status_type_id = past.assignment_status_type_id
And past.per_system_status = 'ACTIVE_ASSIGN'
And ppf.business_group_id +0 = g_business_group_id
and paf.person_id between stperson and endperson
order by paf.assignment_id;
select distinct paf.assignment_id, hsck.segment1
from per_all_people_f ppf
,per_all_assignments_f paf
,hr_soft_coding_keyflex hsck
,hr_organization_units hou
,hr_organization_information hoi
,per_assignment_status_types past
where paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and paf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and paf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
and paf.person_id between stperson and endperson
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and paf.person_id = ppf.person_id
and ppf.current_employee_flag = 'Y'
and ppf.effective_start_date = (select max(ppf2.effective_start_date)
from per_all_people_f ppf2
where ppf.person_id = ppf2.person_id
and ppf2.current_employee_flag = 'Y'
and ppf2.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ppf2.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
)
and ppf.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ppf.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
and ppf.business_group_id +0 = g_business_group_id
and ppf.business_group_id = hou.business_group_id
and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
>= to_date(g_start_date,'YYYY/MM/DD/')
and hsck.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.org_information2 = 'Y'
order by paf.assignment_id;
select distinct paf.assignment_id
,hsck.segment1
from
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
,per_periods_of_service pps
,per_person_type_usages_f ptu
,per_person_types ppt
where ppf.person_id = pps.person_id
and pps.actual_termination_date is not NULL
and pps.actual_termination_date
between to_date(g_start_date,'YYYY/MM/DD/')
and to_date(g_end_date,'YYYY/MM/DD/')
and pps.leaving_reason = 'R'
and ppf.person_id = ptu.person_id
and ptu.effective_start_date <= to_date(g_end_date,'YYYY/MM/DD/')
and ptu.effective_end_date >= to_date(g_start_date,'YYYY/MM/DD/')
and paf.effective_start_date <= to_date( g_end_date,'YYYY/MM/DD/')
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type = 'RETIREE'
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.current_employee_flag is null
)
and ppf.person_id = paf.person_id
and paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf.assignment_id = paf2.assignment_id
)
and hsck.segment1 in
(
select distinct hsck2.segment1
from hr_organization_information hoi
,hr_organization_units hou
,hr_soft_coding_keyflex hsck2
where
hou.business_group_id +0 = g_business_group_id
and hsck2.segment1 = nvl(g_tax_unit_id,to_char(hou.organization_id))
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.org_information2 = 'Y'
and hou.date_from <= to_date(g_end_date,'YYYY/MM/DD/')
and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD'))
>= to_date(g_start_date,'YYYY/MM/DD/')
)
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
And ppf.business_group_id +0 = g_business_group_id
and paf.person_id between stperson and endperson
order by paf.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
select
distinct ppf.PERSON_ID -- BUG4084819
,substr(ppf.LAST_NAME,1,13) last_name
,substr(ppf.MIDDLE_NAMES,1,7) middle_name
,substr(ppf.FIRST_NAME,1,10) first_name
,ppf.NATIONAL_IDENTIFIER
,ppf.DATE_OF_BIRTH
,substr(ppf.SEX,1,1) GENDER
--,paf.ASSIGNMENT_ID
From
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
,per_periods_of_service pps
,per_assignment_status_types past
Where
pps.person_id = ppf.person_id
and ppf.person_id = paf.person_id
and ppf.effective_start_date <= p_end_date
and ppf.effective_end_date >= p_start_date
and paf.effective_start_date <= p_end_date
and paf.effective_end_date >= p_start_date
and ppf.current_employee_flag = 'Y'
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.current_employee_flag = 'Y'
and ppf2.effective_start_date <= p_end_date
and ppf2.effective_end_date >= p_start_date
)
And hsck.segment1 = to_char(p_tax_unit_id)
And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
And paf.assignment_type = 'E'
And paf.primary_flag = 'Y'
And paf.assignment_status_type_id = past.assignment_status_type_id
And past.per_system_status = 'ACTIVE_ASSIGN'
And ppf.business_group_id +0 = p_business_group_id
Order by national_identifier ;
select
distinct ppf.PERSON_ID
,substr(ppf.LAST_NAME,1,13) last_name
,substr(ppf.MIDDLE_NAMES,1,7) middle_name
,substr(ppf.FIRST_NAME,1,10) first_name
,ppf.NATIONAL_IDENTIFIER
,ppf.DATE_OF_BIRTH
,substr(ppf.SEX,1,1) GENDER
--,paf.ASSIGNMENT_ID
From
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
,per_periods_of_service pps
Where
ppf.person_id = pps.person_id
and pps.date_start between
p_start_date and p_end_date
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.effective_start_date <= p_end_date
and ppf2.effective_end_date >= p_start_date
)
and ppf.person_id = paf.person_id
and pps.date_start = paf.effective_start_date
And hsck.segment1 = to_char(p_tax_unit_id)
And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
And paf.assignment_type = 'E'
And paf.primary_flag = 'Y'
And ppf.business_group_id +0 = p_business_group_id
Order by national_identifier ;
select
distinct ppf.PERSON_ID
,substr(ppf.LAST_NAME,1,13) last_name
,substr(ppf.MIDDLE_NAMES,1,7) middle_name
,substr(ppf.FIRST_NAME,1,10) first_name
,ppf.NATIONAL_IDENTIFIER
,ppf.DATE_OF_BIRTH
,substr(ppf.SEX,1,1) GENDER
--,paf.ASSIGNMENT_ID
From
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
,per_periods_of_service pps
,per_person_type_usages_f ptu
,per_person_types ppt
Where
ppf.person_id = pps.person_id
and pps.actual_termination_date is not NULL
and pps.actual_termination_date
between p_start_date and p_end_date
and pps.leaving_reason = 'R'
and ppf.person_id = ptu.person_id
and ptu.effective_start_date <= p_end_date
and ptu.effective_end_date >= p_start_date
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type = 'RETIREE'
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where
ppf2.person_id = ppf.person_id
and ppf2.current_employee_flag is null
)
and ppf.person_id = paf.person_id
and paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where
paf.assignment_id = paf2.assignment_id
)
And hsck.segment1 = to_char(p_tax_unit_id)
And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
And paf.assignment_type = 'E'
And paf.primary_flag = 'Y'
And ppf.business_group_id +0 = p_business_group_id
Order by national_identifier ;
select
distinct ppf.PERSON_ID
,substr(ppf.LAST_NAME,1,13) last_name
,substr(ppf.MIDDLE_NAMES,1,7) middle_name
,substr(ppf.FIRST_NAME,1,10) first_name
,ppf.NATIONAL_IDENTIFIER
,ppf.DATE_OF_BIRTH
,substr(ppf.SEX,1,1) GENDER
--,paf.ASSIGNMENT_ID
From
per_people_f ppf
,hr_soft_coding_keyflex hsck
,per_assignments_f paf
,per_periods_of_service pps
,per_person_type_usages_f ptu
,per_person_types ppt
Where
ppf.person_id = pps.person_id
and pps.actual_termination_date is not NULL
and pps.actual_termination_date
between p_start_date and p_end_date
and pps.leaving_reason = 'R'
and ppf.person_id = ptu.person_id
and ptu.effective_start_date <= p_end_date
and ptu.effective_end_date >= p_start_date
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type = 'RETIREE'
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where
ppf2.person_id = ppf.person_id
and ppf2.current_employee_flag is null
)
and ppf.person_id = paf.person_id
and paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where
paf.assignment_id = paf2.assignment_id
)
And hsck.segment1 = to_char(p_tax_unit_id)
And paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
And paf.assignment_type = 'E'
And paf.primary_flag = 'Y'
And ppf.business_group_id +0 = p_business_group_id
and exists
(select null
from per_people_f ppf2
,per_periods_of_service pps2
where
ppf2.person_id = ppf.person_id
and ppf2.current_employee_flag = 'Y'
and pps2.person_id = ppf2.person_id
and pps2.date_start
between p_start_date and p_end_date
and pps2.date_start = ppf2.effective_start_date
)
Order by national_identifier ;
SELECT value
FROM v$parameter
WHERE name='utl_file_dir';
select distinct hou.name -- BUG4192188
,hsck.segment1 tax_unit_id
,hoi2.org_information1 requester_id_code
,hoi2.org_information2 user_control_data
from hr_organization_information hoi
,hr_organization_units hou
,hr_soft_coding_keyflex hsck
,hr_organization_information hoi2
where hou.business_group_id = p_business_group_id
and hsck.segment1 = to_char(hou.organization_id)
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.org_information2 = 'Y'
and hou.date_from <= l_end_date
and nvl(hou.date_to,to_date('4712-12-31','YYYY-MM-DD')) >= l_start_date
and hoi2.organization_id(+) = hou.organization_id
and hoi2.org_information_context(+) = 'EVS Filing'
order by hou.name;
SELECT
hoi.org_information1 requester_id_code
,hoi.org_information2 user_control_data
,hou.name name
FROM
hr_organization_information hoi
,hr_organization_units hou
WHERE
hoi.organization_id = p_tax_unit_id
AND hoi.org_information_context = 'EVS Filing'
and hoi.organization_id = hou.organization_id
;
select name into l_gre_name
from hr_organization_units
where organization_id = p_tax_unit_id;