[Home] [Help]
10511:
10512: CURSOR get_user_id_csr(p_emp_name VARCHAR2, p_org_id NUMBER) IS
10513:
10514: /* fix for perf. bug# 4919298.
10515: 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
10516: WHERE NVL(PF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y'
10517: AND PEPT.PERSON_TYPE_ID = PF.PERSON_TYPE_ID
10518: AND PEPT.SYSTEM_PERSON_TYPE = 'EMP'
10519: AND PF.PERSON_ID = BRE.PERSON_ID
10520: AND (TRUNC(SYSDATE) BETWEEN PF.EFFECTIVE_START_DATE AND PF.EFFECTIVE_END_DATE)
10521: AND HOU.BUSINESS_GROUP_ID = PF.BUSINESS_GROUP_ID
10522: AND HOU.ORGANIZATION_ID = NVL(p_org_id,HOU.ORGANIZATION_ID)
10523: --AND NVL(FU.employee_id,-1) = PF.person_id
10524: AND FU.employee_id = PF.person_id -- removed NVL to avoid FTS on fnd_user.
10525: AND UPPER(PF.FULL_NAME) like UPPER(p_emp_name);
10526: */
10527:
10528: SELECT DISTINCT bre.person_id, fu.user_id
10525: AND UPPER(PF.FULL_NAME) like UPPER(p_emp_name);
10526: */
10527:
10528: SELECT DISTINCT bre.person_id, fu.user_id
10529: FROM mtl_employees_current_view pf, bom_resource_employees bre, fnd_user fu
10530: WHERE pf.employee_id=bre.person_id
10531: and pf.organization_id = bre.organization_id
10532: and sysdate between BRE.EFFECTIVE_START_DATE and BRE.EFFECTIVE_END_DATE
10533: and FU.employee_id = pf.employee_id
12135: ) IS
12136: --Cursor to Get Current login user's employee name and employee id
12137: CURSOR Get_Emp_Name_And_Id(c_organization_id NUMBER) IS
12138: SELECT distinct pf.full_name emp_name, pf.employee_id
12139: FROM mtl_employees_current_view pf, bom_resource_employees bre, fnd_user fu
12140: WHERE pf.employee_id=bre.person_id
12141: and pf.organization_id = bre.organization_id
12142: and sysdate between BRE.EFFECTIVE_START_DATE and BRE.EFFECTIVE_END_DATE
12143: and FU.employee_id = pf.employee_id