[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_query := 'SELECT peo.full_name name,
peo.employee_number employee_number,
decode(peo.per_information1,
13, ''Ethnic Origin is "Two or More Races" and "Additional Ethnic Code" is missing'',
null, ''Ethnic Origin is missing'') exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types ast,
per_jobs job,
hr_lookups hl
WHERE peo.person_id = ass.person_id
AND peo.current_employee_flag = ''Y''
AND hl.lookup_code = job.job_information1
AND pqh_employment_category.identify_empl_category(ass.employment_category,
' || l_fr || ',' || l_ft || ','
|| l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
AND hl.lookup_type = ''US_EEO5_JOB_CATEGORIES''
AND job.job_information_category = ''US''
AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
AND ass.primary_flag = ''Y''
AND ass.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> ''TERM_ASSIGN''
AND ass.job_id = job.job_id
AND ass.assignment_type = ''E''
AND ass.organization_id IN (
SELECT organization_id
FROM hr_all_organization_units
WHERE business_group_id = '||p_business_group_id || ')
AND (peo.per_information1 is null
OR (peo.per_information1 =''13''
AND not EXISTS (SELECT 1
FROM per_people_extra_info ppei
WHERE ppei.information_type=''PER_US_ADDL_ETHNIC_CAT''
AND ppei.pei_information5 IS not NULL
AND ppei.person_id=peo.person_id)
)
)';
SELECT name into l_business_group_name from hr_organization_units
WHERE organization_id = p_business_group_id
AND business_group_id = p_business_group_id;