[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 NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
WHERE peo.person_id = pei.person_id
AND pei.information_type = ''PER_US_VISA_DETAILS''
AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
AND peo.current_employee_flag = ''Y''
AND hl.lookup_code = job.job_information8
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_IPEDS_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)
)
)';
l_query1 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Warning: Ethnic Origin(EIT) or Visa Details information is missing, '' ||
''However the employee gets counted in IPEDS report under "Unknown Race"'' 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 NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
WHERE peo.person_id = pei.person_id
AND pei.information_type = ''PER_US_VISA_DETAILS''
AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
AND peo.current_employee_flag = ''Y''
AND hl.lookup_code = job.job_information8
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_IPEDS_JOB_CATEGORIES''
AND hl.enabled_flag = ''Y''
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.information_type = ''US_ETHNIC_ORIGIN''
AND ppei.person_id = peo.person_id
)
AND NOT EXISTS(SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''PER_US_VISA_DETAILS''
AND ''' || to_char(l_report_date) || ''' BETWEEN ppei.pei_information7 AND ppei.pei_information8
AND ppei.pei_information9 IN (''04'',''05'',''06'',''07'',''12'')
)';
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, DECODE(ppei.pei_information7,
''Y'', ''Warning: "Two or More race" field is set to "Yes" and no individual race field is set to "Yes" in Ethnic Origin(EIT), '' ||
''However the employee gets counted in IPEDS report under "Two or More races"'',
''N'', ''Warning: All race fields are set to "No" in Ethnic Origin(EIT), However the employee gets counted in IPEDS report '' ||
''under "Unknown Race"''
),
''Warning: "Two or More race" field is set to "Yes" and only one individual race field is set to "Yes" in Ethnic Origin(EIT), '' ||
''However the employee gets counted in IPEDS report under "Hispanic/Latino" or "Two or More races"''
) 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 NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
WHERE peo.person_id = pei.person_id
AND pei.information_type = ''PER_US_VISA_DETAILS''
AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
AND peo.current_employee_flag = ''Y''
AND hl.lookup_code = job.job_information8
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_IPEDS_JOB_CATEGORIES''
AND hl.enabled_flag = ''Y''
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.information_type = ''US_ETHNIC_ORIGIN''
AND ppei.person_id = peo.person_id
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)
) = 0
OR (ppei.pei_information7 = ''Y''
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
)
)
AND NOT EXISTS(SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''PER_US_VISA_DETAILS''
AND ''' || to_char(l_report_date) || ''' BETWEEN ppei.pei_information7 AND ppei.pei_information8
AND ppei.pei_information9 IN (''04'',''05'',''06'',''07'',''12'')
)';
l_query3 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Job information is missing or is not of IPEDS category'' exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types past
WHERE peo.person_id = ass.person_id
AND ass.primary_flag = ''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 peo.business_group_id = '||p_business_group_id || '
AND ass.job_id IS NOT NULL
AND (NOT EXISTS (SELECT ''X''
FROM per_jobs job,
hr_lookups hl
WHERE job.job_information8 = hl.lookup_code
AND job.job_id = ass.job_id
AND job.job_information_category = ''US''
AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
AND hl.enabled_flag = ''Y''
AND hl.lookup_code <> 12)
)';
l_query4 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Employment Category information is missing'' exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types past,
per_jobs job,
hr_lookups hl
WHERE peo.person_id = ass.person_id
AND hl.lookup_code = job.job_information8
AND job.job_information8 IS NOT NULL
AND job.job_information_category = ''US''
AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
AND hl.enabled_flag = ''Y''
AND ass.job_id = job.job_id
AND ass.primary_flag = ''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 peo.business_group_id = '||p_business_group_id || '
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,
''Gender is missing'' exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types past
WHERE peo.person_id = ass.person_id
AND ass.primary_flag = ''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 peo.business_group_id = '||p_business_group_id || '
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.sex IS NULL';
l_query6 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Job is not assigned'' exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types past
WHERE peo.person_id = ass.person_id
AND ass.primary_flag = ''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 peo.business_group_id = '||p_business_group_id || '
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.job_id IS NULL';
l_query7 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Academic Rank is missing for Instruction Job category'' exception_reason
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_assignment_status_types past
WHERE peo.person_id = ass.person_id
AND ass.primary_flag = ''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 peo.business_group_id = '||p_business_group_id || '
AND (EXISTS (SELECT ''X''
FROM per_jobs job,
hr_lookups hl
WHERE job.job_information8 = hl.lookup_code
AND job.job_id = ass.job_id
AND job.job_information_category = ''US''
AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
AND hl.lookup_code IN (''21'',''22'',''23'',''24'') )
)
AND (NOT EXISTS (SELECT ''X''
FROM per_people_extra_info ppea
WHERE ppea.person_id = peo.person_id
AND ppea.information_type = ''PQH_ACADEMIC_RANK''
AND ppea.pei_information1 IS NOT NULL)
)';
l_query8 := 'SELECT peo.full_name name,
peo.employee_number employee_number,
''Contract Information is not provided for faculty status Not on Tenure. This employees will not be reported.'' exception_reason
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppet
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppet.person_id
AND ppet.information_type = ''PQH_TENURE_STATUS''
AND ppet.pei_information1 IN (''03'',''05'')
AND peo.current_employee_flag = ''Y''
AND pqh_employment_category.identify_empl_category(asg.employment_category,
' || l_fr || ',' || l_ft || ',' || l_pr ||',' || l_pt || ') IS NOT NULL
AND job.business_group_id = ' || p_business_group_id || '
AND job.job_information_category = ''US''
AND job.job_information8 IS NOT NULL
AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
AND ''' || to_char(l_report_date) || ''' BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = ''Y''
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> ''TERM_ASSIGN''
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
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 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = ''E''
AND peo.business_group_id = '||p_business_group_id || '
AND EXISTS (SELECT ''Y''
FROM hr_all_organization_units hou
WHERE hou.business_group_id = '||p_business_group_id || '
AND hou.organization_id = asg.organization_id)
AND NOT EXISTS (SELECT ''Y''
FROM per_contracts_f pco
WHERE pco.person_id = peo.person_id
AND ''' || to_char(l_report_date) || ''' BETWEEN pco.effective_start_date AND pco.effective_end_date)';
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;