[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,
''EEO4A Ethnic Origin (EIT) information 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 ass.employment_category IS NOT NULL
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 ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
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 (NOT EXISTS (SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''US_EEO4A_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 EEO4A Ethnic Origin (EIT)'',
''More than one individual race field is set to yes in EEO4A Ethnic Origin (EIT)'') 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,
per_people_extra_info ppei
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 ass.employment_category IS NOT NULL
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 ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
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 ppei.person_id = peo.person_id
AND ppei.information_type = ''US_EEO4A_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-4 category'' exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types past,
per_pay_proposals ppp,
per_pay_bases ppb
WHERE peo.person_id = ass.person_id
AND ass.pay_basis_id = ppb.pay_basis_id
AND ass.assignment_id = ppp.assignment_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 ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
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 (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_EEO4_JOB_CATEGORIES'')
)';
l_query4 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Assignment Category information 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 ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
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 ass.employment_category IS NULL';
l_query5 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''EEO Function is missing in Job information'' 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 ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
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 job.job_information7 IS NULL';
l_query6 :='SELECT peo.full_name name,
peo.employee_number employee_number,
''Both EEO4 Ethnic Origin and EEO4A Ethnic Origin (EIT) information exist : Employee can have only one type of ethnic origin(EEO4 or EEO4A)'' 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 ass.employment_category IS NOT NULL
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 ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
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 (EXISTS (SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''US_ETHNIC_ORIGIN''
AND (DECODE(ppei.pei_information1, ''Y'', 1,0)
+ DECODE(ppei.pei_information2, ''Y'', 1,0)
+ DECODE(ppei.pei_information3, ''Y'', 1,0)
+ DECODE(ppei.pei_information4, ''Y'', 1,0)
+ DECODE(ppei.pei_information5, ''Y'', 1,0)
+ DECODE(ppei.pei_information6, ''Y'', 1,0)
) >= 1
)
)
AND (EXISTS (SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN''
AND (DECODE(ppei.pei_information1, ''Y'', 1,0)
+ DECODE(ppei.pei_information2, ''Y'', 1,0)
+ DECODE(ppei.pei_information3, ''Y'', 1,0)
+ DECODE(ppei.pei_information4, ''Y'', 1,0)
+ DECODE(ppei.pei_information5, ''Y'', 1,0)
+ DECODE(ppei.pei_information6, ''Y'', 1,0)
) >= 1
)
)';
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;