[Home] [Help]
7904:
7905: CURSOR get_user_id_csr(p_emp_name VARCHAR2, p_org_id NUMBER) IS
7906:
7907: /* fix for perf. bug# 4919298.
7908: SELECT FU.user_id, PF.person_id FROM fnd_user FU,PER_PEOPLE_F PF, HR_ORGANIZATION_UNITS HOU, PER_PERSON_TYPES PEPT, BOM_RESOURCE_EMPLOYEES BRE
7909: WHERE NVL(PF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y'
7910: AND PEPT.PERSON_TYPE_ID = PF.PERSON_TYPE_ID
7911: AND PEPT.SYSTEM_PERSON_TYPE = 'EMP'
7912: AND PF.PERSON_ID = BRE.PERSON_ID
7913: AND (TRUNC(SYSDATE) BETWEEN PF.EFFECTIVE_START_DATE AND PF.EFFECTIVE_END_DATE)
7914: AND HOU.BUSINESS_GROUP_ID = PF.BUSINESS_GROUP_ID
7915: AND HOU.ORGANIZATION_ID = NVL(p_org_id,HOU.ORGANIZATION_ID)
7916: --AND NVL(FU.employee_id,-1) = PF.person_id
7917: AND FU.employee_id = PF.person_id -- removed NVL to avoid FTS on fnd_user.
7918: AND UPPER(PF.FULL_NAME) like UPPER(p_emp_name);
7919: */
7920:
7921: SELECT DISTINCT bre.person_id, fu.user_id
7918: AND UPPER(PF.FULL_NAME) like UPPER(p_emp_name);
7919: */
7920:
7921: SELECT DISTINCT bre.person_id, fu.user_id
7922: FROM mtl_employees_current_view pf, bom_resource_employees bre, fnd_user fu
7923: WHERE pf.employee_id=bre.person_id
7924: and pf.organization_id = bre.organization_id
7925: and sysdate between BRE.EFFECTIVE_START_DATE and BRE.EFFECTIVE_END_DATE
7926: and FU.employee_id = pf.employee_id