[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT employee_number, full_name, last_name, title, email_address, start_date
FROM per_all_people_f
WHERE SYSDATE BETWEEN effective_start_date and effective_end_date
AND person_id = p_person_id;
SELECT pei_information1 tenure_status,
FND_DATE.canonical_to_date(PEI_INFORMATION2) date_determined,
FND_DATE.canonical_to_date(nvl(PEI_INFORMATION4,PEI_INFORMATION3)) adjusted_tenure_date
FROM per_people_extra_info
WHERE person_id = p_person_id
AND information_type = 'PQH_TENURE_STATUS' ;
SELECT address_line1||DECODE(NVL(address_line2,'X'),'X',FND_GLOBAL.local_chr(10),', '||address_line2||
FND_GLOBAL.local_chr(10))|| DECODE(NVL(address_line3,'X'),'X','',address_line3||FND_GLOBAL.local_chr(10))||
town_or_city||', '||region_2||' '||postal_code
FROM per_addresses
WHERE SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE)
AND person_id = p_person_id
AND business_group_id = p_bgroup_id
AND primary_flag = 'Y';
SELECT supervisor_id
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND primary_flag = 'Y'
AND SYSDATE BETWEEN effective_start_date and effective_end_date;
SELECT hr_general.decode_lookup('PQH_ACADEMIC_RANK',pei_information1)
FROM per_people_extra_info
WHERE person_id = p_person_id
AND information_type = 'PQH_ACADEMIC_RANK' ;
SELECT assignment_id,job_id,position_id
FROM per_all_assignments_f paf
WHERE paf.person_id = p_person_id
and sysdate between effective_start_date and effective_end_date
and primary_flag = 'Y'
and assignment_type = 'E';
SELECT COUNT(paf.person_id)
FROM per_all_assignments_f paf,
per_people_extra_info ppe
WHERE paf.person_id = ppe.person_id
and primary_flag ='Y' -- primary assignments only
and assignment_type ='E' -- only employees
AND ppe.information_type = 'PQH_TENURE_STATUS'
AND ppe.pei_information1 = p_tenure_status
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND FND_DATE.canonical_to_date(PEI_INFORMATION2) <= p_effective_date
AND paf.supervisor_id = p_supervisor_id ;
SELECT count(person_id)
FROM per_all_assignments_f paf
WHERE assignment_type ='E'
and exists (
SELECT null
FROM per_people_extra_info ppe
WHERE ppe.information_type = 'PQH_TENURE_STATUS'
AND ppe.pei_information1 = p_tenure_status
AND paf.person_id = ppe.person_id
AND FND_DATE.canonical_to_date(PPE.PEI_INFORMATION2) BETWEEN p_start_date AND p_end_date)
and exists (
SELECT null
FROM per_people_extra_info eiar
WHERE eiar.information_type = 'PQH_ACADEMIC_RANK'
AND paf.person_id = eiar.person_id
AND SYSDATE BETWEEN FND_DATE.canonical_to_date(eiar.PEI_INFORMATION2) AND
NVL(FND_DATE.canonical_to_date(eiar.PEI_INFORMATION3),SYSDATE))
CONNECT BY PRIOR person_id = supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND primary_flag ='Y'
START WITH supervisor_id = p_supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and primary_flag ='Y';
SELECT count(person_id)
FROM per_all_assignments_f paf
WHERE assignment_type ='E'
and exists (
SELECT null
FROM per_people_extra_info ppe
WHERE ppe.information_type = 'PQH_TENURE_STATUS'
AND ppe.pei_information1 = p_tenure_status
AND paf.person_id = ppe.person_id
AND fnd_date.canonical_to_date(ppe.pei_information2) <= SYSDATE)
CONNECT BY PRIOR person_id = supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND primary_flag ='Y'
START WITH supervisor_id = p_supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and primary_flag ='Y';
SELECT COUNT(person_id)
FROM per_all_assignments_f
WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date
and primary_flag ='Y'
-- 2005/08/08: NS: Performance fix: fetch the count for the business group
and business_group_id = hr_general.get_business_group_id
and assignment_type ='E';
SELECT COUNT(paf.person_id)
FROM per_all_assignments_f paf,
per_people_extra_info ppe
WHERE paf.person_id = ppe.person_id
AND ppe.information_type = 'PQH_TENURE_STATUS'
and primary_flag ='Y'
and assignment_type ='E'
AND ppe.pei_information1 = l_tenure_status
AND paf.effective_start_date <= SYSDATE
AND paf.effective_end_date >= SYSDATE
AND nvl(fnd_date.canonical_to_date(NVL(ppe.pei_information4,ppe.pei_information3)),sysdate)
BETWEEN p_start_academic_dt AND p_end_academic_dt;
SELECT COUNT(paf.person_id)
FROM per_all_assignments_f paf,
per_people_extra_info ppe
WHERE paf.person_id = ppe.person_id
and primary_flag ='Y'
and assignment_type ='E'
AND ppe.information_type= 'PQH_TENURE_STATUS'
AND ppe.pei_information1= l_tenure_status
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND fnd_date.canonical_to_date(ppe.pei_information2) BETWEEN
p_start_academic_dt AND p_end_academic_dt;
SELECT COUNT(paf.person_id)
FROM per_all_assignments_f paf,
per_people_extra_info ppe
WHERE paf.person_id = ppe.person_id
AND ppe.information_type = 'PQH_TENURE_STATUS'
and primary_flag ='Y'
and assignment_type ='E'
AND ppe.pei_information1= l_tenure_status
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND fnd_date.canonical_to_date(ppe.pei_information2) <= l_effective_date;
SELECT count(person_id)
FROM per_all_assignments_f paf
WHERE assignment_type ='E'
and exists (
SELECT null
FROM per_people_extra_info ppe
WHERE ppe.information_type = 'PQH_TENURE_STATUS'
AND ppe.pei_information1 = l_tenure_status
AND paf.person_id = ppe.person_id
AND nvl(fnd_date.canonical_to_date(
NVL(ppe.pei_information4,ppe.pei_information3)),SYSDATE)
BETWEEN p_start_academic_dt AND p_end_academic_dt)
CONNECT BY PRIOR person_id = supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND primary_flag ='Y'
START WITH supervisor_id = p_supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and primary_flag ='Y';
SELECT count(person_id)
FROM per_all_assignments_f paf
WHERE assignment_type ='E'
and exists (
SELECT null
FROM per_people_extra_info ppe
WHERE ppe.information_type = 'PQH_TENURE_STATUS'
AND ppe.pei_information1 = l_tenure_status
AND paf.person_id = ppe.person_id
AND fnd_date.canonical_to_date(ppe.pei_information2)
BETWEEN p_start_academic_dt AND p_end_academic_dt)
CONNECT BY PRIOR person_id = supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND primary_flag ='Y'
START WITH supervisor_id = p_supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and primary_flag ='Y';
SELECT count(person_id)
FROM per_all_assignments_f paf
WHERE assignment_type ='E'
and exists (
SELECT null
FROM per_people_extra_info ppe
WHERE ppe.information_type = 'PQH_TENURE_STATUS'
AND ppe.pei_information1 = l_tenure_status
AND paf.person_id = ppe.person_id
AND fnd_date.canonical_to_date(ppe.pei_information2) <= l_effective_date)
CONNECT BY PRIOR person_id = supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND primary_flag ='Y'
START WITH supervisor_id = p_supervisor_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and primary_flag ='Y';