Example 1: Inactive Users in R12
SELECT DISTINCT
user_name,
p.full_name,
application_name,
responsibility_name,
security_group_name,
mp.organization_name,
NVL (mp.organization_code, '995'),
b.region_1,
b.region_2,
GREATEST (TO_DATE (u.start_date),
TO_DATE (ur.start_date),
TO_DATE (r.start_date))
start_date,
DECODE (
LEAST (NVL (u.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY')),
NVL (ur.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY')),
NVL (r.end_date, TO_DATE ('01/01/4712', 'DD/MM/YYYY'))),
TO_DATE ('01/01/4712', 'DD/MM/YYYY'),
'',
LEAST (NVL (u.end_date, NVL (ur.end_date, r.end_date)),
NVL (ur.end_date, NVL (u.end_date, r.end_date)),
NVL (r.end_date, NVL (u.end_date, ur.end_date)))
)
end_date
FROM fnd_user u,
fnd_user_resp_groups_all ur,
fnd_responsibility_vl r,
fnd_application_vl a,
fnd_security_groups_vl s,
PER_ALL_PEOPLE_F p,
per_all_assignments_f pa,
org_organization_definitions mp,
HR_ORGANIZATION_UNITS_V b
WHERE a.application_id = r.application_id
AND u.user_id = ur.user_id
AND p.person_id = u.employee_id
--and u.user_id=p.user_id
AND r.application_id = ur.responsibility_application_id
AND r.responsibility_id = ur.responsibility_id
AND ur.start_date <= SYSDATE
AND NVL (ur.end_date, SYSDATE + 1) > SYSDATE
AND u.start_date <= SYSDATE
AND NVL (u.end_date, SYSDATE + 1) < SYSDATE
AND r.start_date <= SYSDATE
AND NVL (r.end_date, SYSDATE + 1) > SYSDATE
AND ur.security_group_id = s.security_group_id
AND [Link] IN ('4', 'W', 'M')
AND TRUNC (SYSDATE) BETWEEN TRUNC (p.effective_start_date)
AND TRUNC (p.effective_end_date)
AND pa.location_id = b.location_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (pa.effective_start_date)
AND TRUNC (pa.effective_end_date)
AND pa.person_id = p.person_id
AND mp.organization_id(+) = pa.organization_id
-- and mp.organization_id = b.organization_id (+)
ORDER BY user_name,
application_name,
responsibility_name,
security_group_name;
Example 2: for each employee in the employees table, the average salary of the employees
reporting to the same manager who were hired in the range just before through just after the
employee
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees
ORDER BY manager_id, hire_date, salary;
Example 3: For each department ID in the R12 Employee table, the last name of employees in ;
format in that department in order of their hire date:
SELECT dept_id "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM per_all_people_f
GROUP BY department_id
ORDER BY department_id;
Example 4: Each employees manager name and the number of people in the managers
department as Subquery-refactoring (CTEs)
with dept_count as (
select deptno, count(*) as dept_count
from emp
group by deptno)
select [Link] as employee_name,
dc1.dept_count as emp_dept_count,
[Link] as manager_name,
dc2.dept_count as mgr_dept_count
from emp e
join dept_count dc1 on [Link] = [Link]
join emp m on [Link] = [Link]
join dept_count dc2 on [Link] = [Link];