View: UMX_ROLE_ASSIGNMENTS_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_ROLE_ASSIGNMENTS_V

View Text

SELECT WFROLES.NAME ROLE_NAME
, UMXREGREQS.REG_REQUEST_ID
, UMXREGREQS.STATUS_CODE
, WFROLES.DISPLAY_NAME
, FNDLKUPVAL.MEANING STATUS_TEXT
, DECODE (UMXREGREQS.STATUS_CODE
, 'PENDING'
, 'PENDINGCASE'
, 'OTHERCASE') DETAIL_REGION_SWITCH
, DECODE (UMXREGREQS.STATUS_CODE
, 'PENDING'
, DECODE(UMX_REG_REQUESTS_PVT.IS_PEND_REQUEST_ERROR(UMXREGREQS.REG_REQUEST_ID)
, 'Y'
, 'ERRORCASE'
, 'GENERICCASE')
, 'UNASSIGNED'
, 'UNASSIGNEDCASE'
, 'GENERICCASE') STATUS_TEXT_SWITCH
, UMXREGREQS.REQUESTED_START_DATE ACTIVE_FROM
, UMXREGREQS.REQUESTED_END_DATE ACTIVE_TO
, UMXREGREQS.JUSTIFICATION
, WFROLES.DESCRIPTION
, NULL USER_NAME
, 'N' HIDE_SHOW
, DECODE (UMXREGREQS.STATUS_CODE
, 'PENDING'
, 'DISABLEDCASE'
, 'ENABLEDCASE') REMOVEIMG
, UMXREGREQS.REG_SERVICE_CODE
, UMXREGREQS.REQUESTED_FOR_USER_ID USER_ID
FROM UMX_REG_REQUESTS UMXREGREQS
, WF_ALL_ROLES_VL WFROLES
, FND_LOOKUP_VALUES_VL FNDLKUPVAL
WHERE UMXREGREQS.STATUS_CODE IN ('PENDING'
, 'ERROR'
, 'UNASSIGNED')
AND DECODE(UMXREGREQS.STATUS_CODE
, 'PENDING'
, DECODE(UMX_REG_REQUESTS_PVT.IS_PEND_REQUEST_ERROR(UMXREGREQS.REG_REQUEST_ID)
, 'Y'
, 'ERROR'
, 'PENDING')
, UMXREGREQS.STATUS_CODE) = FNDLKUPVAL.LOOKUP_CODE
AND FNDLKUPVAL.LOOKUP_TYPE = 'UMX_ACCESS_ROLE_STATUS'
AND UMXREGREQS.WF_ROLE_NAME = WFROLES.NAME
AND DECODE (UMXREGREQS.STATUS_CODE
, 'UNASSIGNED'
, UMXREGREQS.REQUESTED_BY_USER_ID
, FND_GLOBAL.USER_ID) = FND_GLOBAL.USER_ID UNION ALL SELECT WFROLES.NAME ROLE_NAME
, -1 REG_REQUEST_ID
, DECODE (SIGN(SYSDATE - NVL (WFUSERROLES.START_DATE
, SYSDATE - 1))
, 1
, DECODE (SIGN (NVL(WFUSERROLES.END_DATE
, SYSDATE + 1) - SYSDATE)
, 1
, 'APPROVED'
, 'INACTIVE')
, -1
, 'INACTIVE'
, 'APPROVED') STATUS_CODE
, WFROLES.DISPLAY_NAME
, FNDLKUPVAL.MEANING STATUS_TEXT
, 'ASSIGNEDCASE' DETAIL_REGION_SWITCH
, 'GENERICCASE' STATUS_TEXT_SWITCH
, WFUSERROLES.START_DATE ACTIVE_FROM
, WFUSERROLES.END_DATE ACTIVE_TO
, NULL JUSTIFICATION
, WFROLES.DESCRIPTION
, WFUSERROLES.USER_NAME
, 'N' HIDE_SHOW
, 'DISABLEDCASE' REMOVEIMG
, 'REG_SERVICE_CODE' REG_SERVICE_CODE
, FNDUSER.USER_ID USER_ID
FROM WF_ALL_ROLES_VL WFROLES
, WF_ALL_USER_ROLE_ASSIGNMENTS WFUSERROLES
, FND_LOOKUP_VALUES_VL FNDLKUPVAL
, FND_USER FNDUSER
WHERE WFUSERROLES.USER_NAME = FNDUSER.USER_NAME
AND DECODE (SIGN(SYSDATE - NVL (WFUSERROLES.START_DATE
, SYSDATE - 1))
, 1
, DECODE (SIGN (NVL(WFUSERROLES.END_DATE
, SYSDATE + 1) - SYSDATE)
, 1
, 'APPROVED'
, 'INACTIVE')
, -1
, 'INACTIVE'
, 'APPROVED') = FNDLKUPVAL.LOOKUP_CODE
AND FNDLKUPVAL.LOOKUP_TYPE = 'UMX_ACCESS_ROLE_STATUS'
AND WFUSERROLES.ROLE_NAME = WFROLES.NAME
AND ( WFROLES.ORIG_SYSTEM = 'UMX' OR WFROLES.ORIG_SYSTEM = 'FND_RESP' )
AND WFROLES.PARTITION_ID IN (2
, 13)
AND WFUSERROLES.ASSIGNMENT_TYPE = 'DIRECT'
AND ( (NVL(WFUSERROLES.START_DATE
, SYSDATE - 1) < SYSDATE
AND NVL(WFUSERROLES.END_DATE
, SYSDATE + 1) > SYSDATE) OR ( NOT EXISTS ( SELECT NULL
FROM WF_ALL_USER_ROLE_ASSIGNMENTS WURA
WHERE WFUSERROLES.ROLE_NAME = WURA.ROLE_NAME
AND WFUSERROLES.USER_NAME = WURA.USER_NAME
AND NVL(WURA.START_DATE
, SYSDATE - 1) < SYSDATE
AND NVL(WURA.END_DATE
, SYSDATE + 1 ) > SYSDATE
AND WURA.ASSIGNMENT_TYPE = 'INHERITED' ) ) ) UNION ALL SELECT DISTINCT WFUSERROLEASSIGNMENTS.ROLE_NAME
, -99 REG_REQUEST_ID
, 'APPROVED' STATUS_CODE
, WFROLES.DISPLAY_NAME
, FNDLKUPVAL.MEANING STATUS_TEXT
, 'ASSIGNEDINDIRECTCASE' DETAIL_REGION_SWITCH
, 'GENERICCASE' STATUS_TEXT_SWITCH
, SYSDATE ACTIVE_FROM
, SYSDATE ACTIVE_TO
, NULL JUSTIFICATION
, WFROLES.DESCRIPTION
, FNDUSER.USER_NAME
, 'N' HIDE_SHOW
, 'DISABLEDCASE' REMOVEIMG
, 'REG_SERVICE_CODE' REG_SERVICE_CODE
, FNDUSER.USER_ID USER_ID
FROM WF_ALL_ROLES_VL WFROLES
, WF_ALL_USER_ROLE_ASSIGNMENTS WFUSERROLEASSIGNMENTS
, FND_LOOKUP_VALUES_VL FNDLKUPVAL
, FND_USER FNDUSER
WHERE WFUSERROLEASSIGNMENTS.ROLE_NAME = WFROLES.NAME
AND DECODE(SIGN(NVL(WFUSERROLEASSIGNMENTS.END_DATE
, SYSDATE + 1) - SYSDATE)
, 1
, 'APPROVED'
, 'APPROVED') = FNDLKUPVAL.LOOKUP_CODE
AND FNDLKUPVAL.LOOKUP_TYPE = 'UMX_ACCESS_ROLE_STATUS'
AND ( WFROLES.ORIG_SYSTEM = 'UMX' OR WFROLES.ORIG_SYSTEM = 'FND_RESP' )
AND WFROLES.PARTITION_ID IN (2
, 13)
AND WFUSERROLEASSIGNMENTS.ASSIGNMENT_TYPE = 'INHERITED'
AND NVL(WFUSERROLEASSIGNMENTS.START_DATE
, SYSDATE-1) < SYSDATE
AND NVL(WFUSERROLEASSIGNMENTS.END_DATE
, SYSDATE+1) > SYSDATE
AND NOT EXISTS ( SELECT NULL
FROM WF_ALL_USER_ROLE_ASSIGNMENTS WURA
WHERE WFUSERROLEASSIGNMENTS.ROLE_NAME = WURA.ROLE_NAME
AND WFUSERROLEASSIGNMENTS.USER_NAME = WURA.USER_NAME
AND NVL(WURA.START_DATE
, SYSDATE - 1) < SYSDATE
AND NVL(WURA.END_DATE
, SYSDATE + 1 ) > SYSDATE
AND WURA.ASSIGNMENT_TYPE = 'DIRECT' )
AND ((WFUSERROLEASSIGNMENTS.USER_NAME = FNDUSER.USER_NAME) OR ( WFUSERROLEASSIGNMENTS.USER_NAME = (SELECT NAME
FROM WF_LOCAL_ROLES WLR
WHERE WLR.ORIG_SYSTEM_ID = FNDUSER.PERSON_PARTY_ID
AND WLR.ORIG_SYSTEM = 'HZ_PARTY'
AND ROWNUM < 2 ) ) )

Columns