jueves, 28 de agosto de 2014

Query para obtener Usuarios y Responsabilidades

Este query nos devolverá la información de los usuarios y responsabilidades asignadas mutuamente el EBS. Puede consultar ya sea por Usuario o por la Responsabilidad.

SELECT fuser.USER_NAME         USUARIO,
       per.FULL_NAME           EMPLEADO,
       per.EMPLOYEE_NUMBER     NUM_EMPLEADO,
       frt.RESPONSIBILITY_NAME RESPONSABILIDAD
      
  FROM apps.FND_USER              fuser,
       apps.PER_PEOPLE_F          per,
       apps.FND_USER_RESP_GROUPS  furg,
       apps.FND_RESPONSIBILITY_TL frt
 WHERE fuser.EMPLOYEE_ID = per.PERSON_ID
   AND fuser.USER_ID = furg.USER_ID
   AND (to_char(fuser.END_DATE) is null OR fuser.END_DATE > sysdate)
   AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
   AND (to_char(furg.END_DATE) is null OR furg.END_DATE > sysdate)
   AND EFFECTIVE_END_DATE > sysdate
   AND frt.LANGUAGE = 'US'
   and (FRt.responsibIlity_name =&RESP_NAME or &RESP_NAME  IS NULL)
  and (fuser.USER_NAME  =&USER_NAME  or &USER_NAME  IS NULL)

 ORDER BY fuser.USER_NAME;

1 comentario: