[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 past,
per_pay_proposals ppp,
per_jobs job,
per_pay_bases ppb,
hr_lookups hl
WHERE peo.person_id = ass.person_id
AND ass.pay_basis_id = ppb.pay_basis_id
AND ass.assignment_id = ppp.assignment_id
AND hl.lookup_code = job.job_information1
AND job.job_information1 IS NOT NULL
AND job.job_information_category = ''US''
AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
AND ass.job_id = job.job_id
AND ass.primary_flag = ''Y''
AND ppp.change_date = ( SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= ''' || to_char(l_report_date) || '''
AND pro.approved = ''Y'' )
AND ass.organization_id IN (SELECT organization_id
FROM hr_all_organization_units
WHERE business_group_id = ' || p_business_group_id || '
AND SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE) )
AND ass.assignment_status_type_id = past.assignment_status_type_id
AND peo.current_employee_flag = ''Y''
AND ass.assignment_type = ''E''
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 past.per_system_status <> ''TERM_ASSIGN''
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;