View: UMX_CURRENT_ROLES_V

Product:	FND - Application Object Library
Description:	Private view used by UMX to display roles and registration requests for a given user
Implementation/DBA Data:	APPS.UMX_CURRENT_ROLES_V

View Text

SELECT -1 REG_REQUEST_ID
, NULL REG_SERVICE_CODE
, 'APPROVED' STATUS_CODE
, WFU.ROLE_NAME WF_ROLE_NAME
, NULL WF_ROLE_DISPLAY_NAME
, NULL WF_ROLE_DESCRIPTION
, NULL STATUS_MEANING
, FU.USER_ID USER_ID
, TO_NUMBER(NULL) REQUESTED_BY_USER_ID
FROM WF_USER_ROLES WFU
, FND_USER FU
WHERE ((WFU.USER_NAME = FU.USER_NAME) OR (WFU.USER_NAME = ( SELECT NAME
FROM WF_LOCAL_ROLES WLR
WHERE FU.PERSON_PARTY_ID = WLR.ORIG_SYSTEM_ID
AND WLR.ORIG_SYSTEM = 'HZ_PARTY'
AND ROWNUM < 2)))
AND (WFU.ROLE_ORIG_SYSTEM = 'UMX' OR WFU.ROLE_ORIG_SYSTEM = 'FND_RESP')
AND WFU.PARTITION_ID IN (2
, 13) UNION ALL SELECT REGREQ.REG_REQUEST_ID
, REGREQ.REG_SERVICE_CODE
, REGREQ.STATUS_CODE
, WFR.NAME WF_ROLE_NAME
, NULL WF_ROLE_DISPLAY_NAME
, NULL WF_ROLE_DESCRIPTION
, NULL STATUS_MEANING
, REGREQ.REQUESTED_FOR_USER_ID
, REGREQ.REQUESTED_BY_USER_ID USER_ID
FROM UMX_REG_REQUESTS REGREQ
, WF_LOCAL_ROLES WFR
WHERE REGREQ.WF_ROLE_NAME = WFR.NAME
AND WFR.ORIG_SYSTEM = 'FND_RESP'
AND WFR.PARTITION_ID IN 2
AND NVL(REGREQ.REQUESTED_END_DATE
, SYSDATE + 1) > SYSDATE
AND ((REGREQ.REQUESTED_BY_USER_ID = REGREQ.REQUESTED_FOR_USER_ID
AND REGREQ.STATUS_CODE = 'UNASSIGNED') OR (REGREQ.STATUS_CODE IN ('VERIFYING'
, 'PENDING')))
AND WFR.START_DATE <= SYSDATE
AND NVL(WFR.EXPIRATION_DATE
, SYSDATE + 1) > SYSDATE UNION ALL SELECT REGREQ.REG_REQUEST_ID
, REGREQ.REG_SERVICE_CODE
, REGREQ.STATUS_CODE
, WFR.NAME WF_ROLE_NAME
, NULL WF_ROLE_DISPLAY_NAME
, NULL WF_ROLE_DESCRIPTION
, NULL STATUS_MEANING
, REGREQ.REQUESTED_FOR_USER_ID
, REGREQ.REQUESTED_BY_USER_ID USER_ID
FROM UMX_REG_REQUESTS REGREQ
, WF_LOCAL_ROLES WFR
WHERE REGREQ.WF_ROLE_NAME = WFR.NAME
AND WFR.ORIG_SYSTEM = 'UMX'
AND WFR.PARTITION_ID IN 13
AND NVL(REGREQ.REQUESTED_END_DATE
, SYSDATE + 1) > SYSDATE
AND ((REGREQ.REQUESTED_BY_USER_ID = REGREQ.REQUESTED_FOR_USER_ID
AND REGREQ.STATUS_CODE = 'UNASSIGNED') OR (REGREQ.STATUS_CODE IN ('VERIFYING'
, 'PENDING')))
AND WFR.START_DATE <= SYSDATE
AND NVL(WFR.EXPIRATION_DATE
, SYSDATE + 1) > SYSDATE

Columns