SELECT MP.PLANNER_CODE
,
PPF.FULL_NAME
FROMMTL_PLANNERS MP
,
PER_PEOPLE_F PPF
,
ORG_ACCESS OA
WHERE mp.employee_id = ppf.person_id
AND mp.organization_id = oa.organization_id
AND NVL(oa.disable_date,SYSDATE) >= SYSDATE
AND oa.responsibility_id = to_number(fnd_profile.value('RESP_ID'))
AND oa.resp_application_id = to_number(fnd_profile.value('RESP_APPL_ID'))
AND trunc(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND NOT EXISTS (SELECT'X'
FROM mtl_planners mp1
,org_access oa
WHEREmp.planner_code = mp1.planner_code
ANDmp.organization_id > mp1.organization_id
ANDmp1.organization_id = oa.organization_id
ANDNVL(oa.disable_date,SYSDATE) >= SYSDATE
ANDoa.responsibility_id = to_number(fnd_profile.value('RESP_ID'))
ANDoa.resp_application_id = to_number(fnd_profile.value('RESP_APPL_ID')))
ORDER BY mp.planner_code
|
