VIEW: APPS.WF_USER_ROLE_ASSIGNMENTS_V

Source

View Text - Preformatted

select
 WURA.USER_NAME,
 WURA.ROLE_NAME,
 WURA.ASSIGNING_ROLE,
 WURA.START_DATE,
 WURA.END_DATE,
 decode(WURA.RELATIONSHIP_ID, -1, 'DIRECT', 'INHERITED'),
 WURA.ASSIGNMENT_REASON,
 WURA.CREATION_DATE,
 WURA.CREATED_BY,
 WURA.LAST_UPDATE_DATE,
 WURA.LAST_UPDATED_BY,
WURA.LAST_UPDATE_LOGIN
 from WF_USER_ROLE_ASSIGNMENTS wura
where  ((wura.effective_start_date is null
and (wura.start_date is NULL or
     trunc(sysdate) >= trunc(wura.start_date))
and  (wura.user_start_date is NULL or
    trunc(sysdate) >= trunc(wura.user_start_date))
and  (wura.role_start_date is NULL or
   trunc(sysdate) >= trunc(wura.role_start_date))
  and  (wura.assigning_role_start_date is NULL or
  trunc(sysdate) >= trunc(wura.assigning_role_start_date)))
 or  trunc(sysdate)>=trunc(wura.effective_start_date))
and ((wura.effective_end_date is null
 and (wura.end_date is NULL or
    trunc(sysdate) < trunc(wura.end_date))
  and  (wura.user_end_date is NULL or
  trunc(sysdate) < trunc(wura.user_end_date))
  and  (wura.role_end_date is NULL or
   trunc(sysdate) < trunc(wura.role_end_date))
    and  (wura.assigning_role_end_date is NULL or
    trunc(sysdate) < trunc(wura.assigning_role_end_date)))
    or  trunc(sysdate)< trunc(wura.effective_end_date))

View Text - HTML Formatted

SELECT WURA.USER_NAME
, WURA.ROLE_NAME
, WURA.ASSIGNING_ROLE
, WURA.START_DATE
, WURA.END_DATE
, DECODE(WURA.RELATIONSHIP_ID
, -1
, 'DIRECT'
, 'INHERITED')
, WURA.ASSIGNMENT_REASON
, WURA.CREATION_DATE
, WURA.CREATED_BY
, WURA.LAST_UPDATE_DATE
, WURA.LAST_UPDATED_BY
, WURA.LAST_UPDATE_LOGIN
FROM WF_USER_ROLE_ASSIGNMENTS WURA WHERE ((WURA.EFFECTIVE_START_DATE IS NULL AND (WURA.START_DATE IS NULL OR TRUNC(SYSDATE) >= TRUNC(WURA.START_DATE)) AND (WURA.USER_START_DATE IS NULL OR TRUNC(SYSDATE) >= TRUNC(WURA.USER_START_DATE)) AND (WURA.ROLE_START_DATE IS NULL OR TRUNC(SYSDATE) >= TRUNC(WURA.ROLE_START_DATE))
AND (WURA.ASSIGNING_ROLE_START_DATE IS NULL OR TRUNC(SYSDATE) >= TRUNC(WURA.ASSIGNING_ROLE_START_DATE))) OR TRUNC(SYSDATE)>=TRUNC(WURA.EFFECTIVE_START_DATE)) AND ((WURA.EFFECTIVE_END_DATE IS NULL
AND (WURA.END_DATE IS NULL OR TRUNC(SYSDATE) < TRUNC(WURA.END_DATE))
AND (WURA.USER_END_DATE IS NULL OR TRUNC(SYSDATE) < TRUNC(WURA.USER_END_DATE))
AND (WURA.ROLE_END_DATE IS NULL OR TRUNC(SYSDATE) < TRUNC(WURA.ROLE_END_DATE))
AND (WURA.ASSIGNING_ROLE_END_DATE IS NULL OR TRUNC(SYSDATE) < TRUNC(WURA.ASSIGNING_ROLE_END_DATE))) OR TRUNC(SYSDATE)< TRUNC(WURA.EFFECTIVE_END_DATE))