VIEW: APPS.FND_RESP_SEC_UR

Source

View Text - Preformatted

SELECT
  U.USER_NAME user_name,
  decode(u.employee_id,NULL,'FND_USR','PER') user_orig_system,
  decode(u.employee_id,NULL,U.USER_ID,u.employee_id) user_orig_system_id,
  'FND_RESP|'||A.APPLICATION_SHORT_NAME||'|'||replace(R.RESPONSIBILITY_KEY,':','%col')||'|'||S.SECURITY_GROUP_KEY role_name,
  'FND_RESP' role_orig_system,
  R.RESPONSIBILITY_ID role_orig_system_id,
  ur.start_date start_date,
  ur.end_date expiration_date,
  ur.security_group_id security_group_id, to_number(NULL) partition_id,
  ur.created_by created_by,
  ur.creation_date creation_date,
  ur.last_updated_by last_updated_by,
  ur.last_update_date last_update_date,
  ur.last_update_login last_update_login,
  u.start_date user_start_date,
  u.end_date user_end_date,
  r.start_date role_start_date,
  r.end_date role_end_date
from FND_USER U, FND_USER_RESP_GROUPS_OLD UR, FND_RESPONSIBILITY R,
     FND_APPLICATION A, FND_SECURITY_GROUPS S
WHERE U.USER_ID = UR.USER_ID
and UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
and UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID
and R.APPLICATION_ID = A.APPLICATION_ID
and UR.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID
union
select
  U.USER_NAME user_name,
  decode(u.employee_id,NULL,'FND_USR','PER') user_orig_system,
  decode(u.employee_id,NULL,U.USER_ID,u.employee_id) user_orig_system_id,
  'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||UR.RESPONSIBILITY_ID role_name,
  'FND_RESP'||R.APPLICATION_ID  role_orig_system,
  R.RESPONSIBILITY_ID role_orig_system_id,
  ur.start_date start_date,
  ur.end_date expiration_date,
  ur.security_group_id security_group_id, to_number(NULL) partition_id,
  ur.created_by created_by,
  ur.creation_date creation_date,
  ur.last_updated_by last_updated_by,
  ur.last_update_date last_update_date,
  ur.last_update_login last_update_login,
  u.start_date user_start_date,
  u.end_date user_end_date,
  r.start_date role_start_date,
  r.end_date role_end_date
from FND_USER U, FND_USER_RESP_GROUPS_OLD UR, FND_RESPONSIBILITY R,
     FND_APPLICATION A, FND_SECURITY_GROUPS S
WHERE U.USER_ID = UR.USER_ID
and UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
and UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID
and R.APPLICATION_ID = A.APPLICATION_ID
and UR.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID

   

View Text - HTML Formatted

SELECT U.USER_NAME USER_NAME
, DECODE(U.EMPLOYEE_ID
, NULL
, 'FND_USR'
, 'PER') USER_ORIG_SYSTEM
, DECODE(U.EMPLOYEE_ID
, NULL
, U.USER_ID
, U.EMPLOYEE_ID) USER_ORIG_SYSTEM_ID
, 'FND_RESP|'||A.APPLICATION_SHORT_NAME||'|'||REPLACE(R.RESPONSIBILITY_KEY
, ':'
, '%COL')||'|'||S.SECURITY_GROUP_KEY ROLE_NAME
, 'FND_RESP' ROLE_ORIG_SYSTEM
, R.RESPONSIBILITY_ID ROLE_ORIG_SYSTEM_ID
, UR.START_DATE START_DATE
, UR.END_DATE EXPIRATION_DATE
, UR.SECURITY_GROUP_ID SECURITY_GROUP_ID
, TO_NUMBER(NULL) PARTITION_ID
, UR.CREATED_BY CREATED_BY
, UR.CREATION_DATE CREATION_DATE
, UR.LAST_UPDATED_BY LAST_UPDATED_BY
, UR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, UR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, U.START_DATE USER_START_DATE
, U.END_DATE USER_END_DATE
, R.START_DATE ROLE_START_DATE
, R.END_DATE ROLE_END_DATE FROM FND_USER U
, FND_USER_RESP_GROUPS_OLD UR
, FND_RESPONSIBILITY R
, FND_APPLICATION A
, FND_SECURITY_GROUPS S WHERE U.USER_ID = UR.USER_ID AND UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND R.APPLICATION_ID = A.APPLICATION_ID AND UR.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID UNION SELECT U.USER_NAME USER_NAME
, DECODE(U.EMPLOYEE_ID
, NULL
, 'FND_USR'
, 'PER') USER_ORIG_SYSTEM
, DECODE(U.EMPLOYEE_ID
, NULL
, U.USER_ID
, U.EMPLOYEE_ID) USER_ORIG_SYSTEM_ID
, 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||UR.RESPONSIBILITY_ID ROLE_NAME
, 'FND_RESP'||R.APPLICATION_ID ROLE_ORIG_SYSTEM
, R.RESPONSIBILITY_ID ROLE_ORIG_SYSTEM_ID
, UR.START_DATE START_DATE
, UR.END_DATE EXPIRATION_DATE
, UR.SECURITY_GROUP_ID SECURITY_GROUP_ID
, TO_NUMBER(NULL) PARTITION_ID
, UR.CREATED_BY CREATED_BY
, UR.CREATION_DATE CREATION_DATE
, UR.LAST_UPDATED_BY LAST_UPDATED_BY
, UR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, UR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, U.START_DATE USER_START_DATE
, U.END_DATE USER_END_DATE
, R.START_DATE ROLE_START_DATE
, R.END_DATE ROLE_END_DATE FROM FND_USER U
, FND_USER_RESP_GROUPS_OLD UR
, FND_RESPONSIBILITY R
, FND_APPLICATION A
, FND_SECURITY_GROUPS S WHERE U.USER_ID = UR.USER_ID AND UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND R.APPLICATION_ID = A.APPLICATION_ID AND UR.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID