VIEW: APPS.UMX_CURRENT_ROLES_V

Source

View Text - Preformatted

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

   

View Text - HTML Formatted

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