oracle apps SQL to get user supervisor details with user authorized limit

Posted by

SQL Query in Oracle apps R12 to get user supervisor details with user authorized limit

SELECT
FU.USER_NAME                                                       ” USER ID” ,
PER_PEOPLE.FULL_NAME                                        ” USER FULL NAME”,
PER_ASSIGN.D_SUPERVISOR_ID                              ” SUPERVISOR USER ID” ,
GL_AUTH_LIMIT1.AUTHORIZATION_LIMIT               ” USER AUTH LIMIT” ,
GL_AUTH_LIMIT2.AUTHORIZATION_LIMIT                ” SUPERVISOR AUTH LIMIT”
FROM
FND_USER FU,
PER_ALL_PEOPLE_F PER_PEOPLE,
PER_ASSIGNMENTS_V7 PER_ASSIGN,
GL_AUTHORIZATION_LIMITS_V GL_AUTH_LIMIT1,
GL_AUTHORIZATION_LIMITS_V GL_AUTH_LIMIT2
WHERE 1=1
AND FU.EMPLOYEE_ID=PER_PEOPLE.PERSON_ID
AND PER_PEOPLE.PERSON_ID=PER_ASSIGN.PERSON_ID(+)
AND FU.EMPLOYEE_ID= GL_AUTH_LIMIT1.EMPLOYEE_ID(+)
AND PER_ASSIGN.SUPERVISOR_ID= GL_AUTH_LIMIT2.EMPLOYEE_ID(+)
AND FU.USER_NAME IN (<PROVIDE_USER_NAME>)