SELECT c.user_name
,a.responsibility_name responsibility
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_indirect b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
UNION
SELECT c.user_name
,a.responsibility_name ROLE
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_direct b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
ORDER BY start_date
,a.responsibility_name responsibility
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_indirect b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
UNION
SELECT c.user_name
,a.responsibility_name ROLE
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_direct b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
ORDER BY start_date
No comments:
Post a Comment