[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT HR.EMPLOYEE_ID,
HR.FULL_NAME,
NVL(hr.LOCATION_ID,0)
INTO X_emp_id,
X_emp_name,
X_location_id
FROM FND_USER FND, hr_operating_units org, (SELECT p.person_id employee_id, p.full_name, a.SET_OF_BOOKS_ID , a.location_id
FROM PER_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F A,
PER_PERIODS_OF_SERVICE B
WHERE A.PERSON_ID = P.PERSON_ID
--AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_TYPE = 'E'
AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND (B.ACTUAL_TERMINATION_DATE>= trunc(sysdate) or B.ACTUAL_TERMINATION_DATE is null)
AND P.EMPLOYEE_NUMBER IS NOT NULL) hr
WHERE FND.USER_ID = X_user_id
AND FND.EMPLOYEE_ID = hr.EMPLOYEE_ID
AND hr.set_of_books_id = org.set_of_books_id
AND org.organization_id = fnd_global.org_id
AND ROWNUM = 1;
SELECT HR.EMPLOYEE_ID,
HR.FULL_NAME,
NVL(HR.LOCATION_ID,0)
INTO X_emp_id,
X_emp_name,
X_location_id
FROM FND_USER FND, PER_EMPLOYEES_CURRENT_X HR
WHERE FND.USER_ID = TO_NUMBER(X_user_id)
AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
AND ROWNUM = 1;
/* if no rows selected
then user is not an employee
else user is an employee */
ELSE
-- Bug 4664653
-- Add to_number to x_user_id
--Bug 13552967
--Included Exception block to avoid failure of below
--sql by bug 10413227
-- subquery merged with main queyr as part of bug:15924594 to improve the performance.
BEGIN
SELECT HR.EMPLOYEE_ID,
HR.FULL_NAME,
NVL(HR.LOCATION_ID,0)
INTO X_emp_id,
X_emp_name,
X_location_id
FROM (SELECT p.person_id employee_id,
p.full_name,
a.set_of_books_id,
a.location_id
FROM per_people_f p,
per_all_assignments_f a,
per_periods_of_service ps,
fnd_user fnd,
hr_operating_units org
WHERE a.person_id = p.person_id
AND a.person_id = ps.person_id
AND a.assignment_type = 'E'
AND p.employee_number IS NOT NULL
AND a.period_of_service_id = ps.period_of_service_id
-- AND a.primary_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND ( ps.actual_termination_date >= TRUNC(SYSDATE)
OR ps.actual_termination_date IS NULL )
AND fnd.user_id = TO_NUMBER(X_user_id)
AND fnd.employee_id = p.person_id
AND a.set_of_books_id = org.set_of_books_id
AND org.organization_id = fnd_global.org_id
UNION ALL
SELECT p.person_id employee_id,
p.full_name,
a.set_of_books_id,
a.location_id
FROM per_people_f p,
per_all_assignments_f a,
per_periods_of_placement pp,
fnd_user fnd,
hr_operating_units org
WHERE a.person_id = p.person_id
AND a.person_id = pp.person_id
AND a.assignment_type = 'C'
AND p.npw_number IS NOT NULL
AND a.period_of_placement_date_start = pp.date_start
-- AND a.primary_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND ( pp.actual_termination_date >= TRUNC(SYSDATE)
OR pp.actual_termination_date IS NULL )
AND fnd.user_id = TO_NUMBER(X_user_id)
AND fnd.employee_id = p.person_id
AND a.set_of_books_id = org.set_of_books_id
AND org.organization_id = fnd_global.org_id
) HR
WHERE ROWNUM = 1;
SELECT HR.PERSON_ID,
HR.FULL_NAME,
NVL(HR.LOCATION_ID,0)
INTO X_emp_id,
X_emp_name,
X_location_id
FROM FND_USER FND, PER_WORKFORCE_CURRENT_X HR
WHERE FND.USER_ID = TO_NUMBER(X_user_id)
AND FND.EMPLOYEE_ID = HR.PERSON_ID
AND ROWNUM = 1;
SELECT HR.LOCATION_CODE
INTO X_location_code
FROM HR_LOCATIONS HR,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE HR.LOCATION_ID = X_location_id
AND HR.INVENTORY_ORGANIZATION_ID = OOD.ORGANIZATION_ID (+) ;
SELECT 'Y'
INTO X_buyer_code
FROM PO_AGENTS
WHERE agent_id = X_emp_id
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1)
AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
SELECT count(*)
INTO x_data_exists
FROM fnd_user
WHERE employee_id = x_person_id
AND sysdate < nvl(end_date, sysdate + 1);
SELECT full_name
INTO x_emp_name
FROM po_workforce_current_x --
WHERE person_id = x_emp_id;
sql_str := 'select hr_employees.full_name, hr_employees.employee_id from hr_employees where ';
X_sql_str := 'select hr_employees.full_name, hr_employees.employee_id from hr_employees where ';
select distinct prf.full_name,prf.effective_start_date
from per_all_people_f prf
where prf.person_id = x_person_id
order by prf.effective_start_date desc;