[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_query1 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Ethnic Origin (EIT) information 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 (NOT EXISTS (SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''US_ETHNIC_ORIGIN'')
)';
l_query2 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
DECODE((DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
), 0, ''No individual race field is set to yes in Ethnic Origin (EIT)'',
''More than one individual race field is set to yes in Ethnic Origin (EIT)'') exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types ast,
per_jobs job,
hr_lookups hl,
per_people_extra_info ppei
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 ppei.person_id = peo.person_id
AND ppei.information_type = ''US_ETHNIC_ORIGIN''
AND (DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
+ DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
) != 1';
l_query3 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Job information is missing or is not of EEO-5 category'' exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types ast
WHERE peo.person_id = ass.person_id
AND peo.current_employee_flag = ''Y''
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.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 (NOT EXISTS (SELECT ''X''
FROM per_jobs job,
hr_lookups hl
WHERE job.job_information1 = hl.lookup_code
AND job.job_id = ass.job_id
AND job.job_information_category = ''US''
AND hl.lookup_type = ''US_EEO5_JOB_CATEGORIES'')
)';
l_query4 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Employment Category inforamtion is missing or is not of reporting category'' 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 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 pqh_employment_category.identify_empl_category(ass.employment_category,
' || l_fr || ',' || l_ft || ','
|| l_pr ||',' || l_pt || ') NOT IN (''FR'',''PR'')';
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;