eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.UMX_ROLE_ASSIGNMENTS_V

Source

View Text - Preformatted


select WFRoles.name role_name,
       UMXRegReqs.reg_request_id,
       UMXRegReqs.status_code,
       WFRoles.display_name,
       FNDLkupVal.meaning status_text,
       decode (UMXRegReqs.status_code, 'PENDING', decode(UMX_REG_REQUESTS_PVT.is_pend_request_error(UMXRegReqs.reg_request_id),'Y','Error', '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,
       WFUserRoles.ASSIGNMENT_REASON 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,
       WFUserRoleAssignments.ASSIGNMENT_REASON 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
                                        )
	 )
    )

View Text - HTML Formatted

SELECT WFROLES.NAME ROLE_NAME
, UMXREGREQS.REG_REQUEST_ID
, UMXREGREQS.STATUS_CODE
, WFROLES.DISPLAY_NAME
, FNDLKUPVAL.MEANING STATUS_TEXT
, DECODE (UMXREGREQS.STATUS_CODE
, 'PENDING'
, DECODE(UMX_REG_REQUESTS_PVT.IS_PEND_REQUEST_ERROR(UMXREGREQS.REG_REQUEST_ID)
, 'Y'
, 'ERROR'
, '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
, WFUSERROLES.ASSIGNMENT_REASON 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
, WFUSERROLEASSIGNMENTS.ASSIGNMENT_REASON 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 ) ) )