VIEW: APPS.CSF_M_USER_V

Source

View Text - Preformatted

SELECT acc.access_id as access_id, usr.user_id, usr.user_name, usr.resource_id, usr.client_number, usr.language, usr.org_id, usr.last_synch_date_start, usr.last_synch_date_end, jtrs.source_first_name first_name, jtrs.source_last_name last_name, jtrs.source_name full_name, usr.responsibility_id , usr.migration_completed , usr.migration_completed_date, usr.migration_completion_version, fu.person_party_id as party_id, substr(csm_util_pkg.get_varchar(tl.ROLE_NAME),1,60) as role_name, substr(csm_util_pkg.get_varchar(DECODE(MEMBER_FLAG,'Y','MEMBER','N',DECODE(ADMIN_FLAG,'Y','ADMIN','N',DECODE(MANAGER_FLAG,'Y','OWNER')))),1,30) as role_type, usr.group_id as group_id, DECODE(usr.OWNER_ID,ASG_BASE.GET_USER_ID,'Y','N') AS IN_CURRENT_GROUP FROM asg_user usr, jtf_rs_resource_extns jtrs,fnd_user fu, JTF_RS_ROLES_B b,JTF_RS_ROLES_TL tl, CSM_USER_ACC acc WHERE acc.owner_id = ASG_BASE.GET_USER_ID and acc.user_id=usr.user_id AND jtrs.resource_id = usr.resource_id AND usr.user_name=fu.user_name AND b.ROLE_ID = usr.ROLE_ID AND b.ROLE_ID = tl.ROLE_ID AND tl.LANGUAGE = ASG_BASE.GET_LANGUAGE UNION ALL SELECT acc.access_id as access_id, usr.user_id, usr.user_name, usr.resource_id, usr.client_number, usr.language, usr.org_id, usr.last_synch_date_start, usr.last_synch_date_end, jtrs.source_first_name first_name, jtrs.source_last_name last_name, jtrs.source_name full_name, usr.responsibility_id , usr.migration_completed , usr.migration_completed_date, usr.migration_completion_version, fu.person_party_id as party_id, NULL as role_name, NULL as role_type, usr.group_id as group_id, NULL AS IN_CURRENT_GROUP from asg_user usr, jtf_rs_resource_extns jtrs, fnd_user fu, CSM_USER_ACC acc WHERE acc.owner_id = ASG_BASE.GET_USER_ID AND jtrs.resource_id = usr.resource_id AND usr.user_name=fu.user_name AND acc.user_id = usr.user_id AND usr.GROUP_ID IS NULL UNION ALL SELECT acc.access_id as access_id, fu.user_id, fu.user_name, jtrs.resource_id, NULL AS client_number, NULL as language, NULL as org_id, NULL as last_synch_date_start, NULL as last_synch_date_end, jtrs.source_first_name first_name, jtrs.source_last_name last_name, jtrs.source_name full_name, NULL as responsibility_id , NULL as migration_completed , NULL AS migration_completed_date, NULL AS migration_completion_version, fu.person_party_id as party_id, NULL as role_name, NULL as role_type, NULL as group_id, NULL AS IN_CURRENT_GROUP FROM jtf_rs_resource_extns jtrs,fnd_user fu,CSM_USER_ACC acc WHERE acc.owner_id = ASG_BASE.GET_USER_ID AND jtrs.user_name=fu.user_name AND fu.user_id=acc.user_id AND NOT EXISTS(select 1 from asg_user where user_id=acc.user_id)

View Text - HTML Formatted

SELECT ACC.ACCESS_ID AS ACCESS_ID
, USR.USER_ID
, USR.USER_NAME
, USR.RESOURCE_ID
, USR.CLIENT_NUMBER
, USR.LANGUAGE
, USR.ORG_ID
, USR.LAST_SYNCH_DATE_START
, USR.LAST_SYNCH_DATE_END
, JTRS.SOURCE_FIRST_NAME FIRST_NAME
, JTRS.SOURCE_LAST_NAME LAST_NAME
, JTRS.SOURCE_NAME FULL_NAME
, USR.RESPONSIBILITY_ID
, USR.MIGRATION_COMPLETED
, USR.MIGRATION_COMPLETED_DATE
, USR.MIGRATION_COMPLETION_VERSION
, FU.PERSON_PARTY_ID AS PARTY_ID
, SUBSTR(CSM_UTIL_PKG.GET_VARCHAR(TL.ROLE_NAME)
, 1
, 60) AS ROLE_NAME
, SUBSTR(CSM_UTIL_PKG.GET_VARCHAR(DECODE(MEMBER_FLAG
, 'Y'
, 'MEMBER'
, 'N'
, DECODE(ADMIN_FLAG
, 'Y'
, 'ADMIN'
, 'N'
, DECODE(MANAGER_FLAG
, 'Y'
, 'OWNER'))))
, 1
, 30) AS ROLE_TYPE
, USR.GROUP_ID AS GROUP_ID
, DECODE(USR.OWNER_ID
, ASG_BASE.GET_USER_ID
, 'Y'
, 'N') AS IN_CURRENT_GROUP
FROM ASG_USER USR
, JTF_RS_RESOURCE_EXTNS JTRS
, FND_USER FU
, JTF_RS_ROLES_B B
, JTF_RS_ROLES_TL TL
, CSM_USER_ACC ACC
WHERE ACC.OWNER_ID = ASG_BASE.GET_USER_ID
AND ACC.USER_ID=USR.USER_ID
AND JTRS.RESOURCE_ID = USR.RESOURCE_ID
AND USR.USER_NAME=FU.USER_NAME
AND B.ROLE_ID = USR.ROLE_ID
AND B.ROLE_ID = TL.ROLE_ID
AND TL.LANGUAGE = ASG_BASE.GET_LANGUAGE UNION ALL SELECT ACC.ACCESS_ID AS ACCESS_ID
, USR.USER_ID
, USR.USER_NAME
, USR.RESOURCE_ID
, USR.CLIENT_NUMBER
, USR.LANGUAGE
, USR.ORG_ID
, USR.LAST_SYNCH_DATE_START
, USR.LAST_SYNCH_DATE_END
, JTRS.SOURCE_FIRST_NAME FIRST_NAME
, JTRS.SOURCE_LAST_NAME LAST_NAME
, JTRS.SOURCE_NAME FULL_NAME
, USR.RESPONSIBILITY_ID
, USR.MIGRATION_COMPLETED
, USR.MIGRATION_COMPLETED_DATE
, USR.MIGRATION_COMPLETION_VERSION
, FU.PERSON_PARTY_ID AS PARTY_ID
, NULL AS ROLE_NAME
, NULL AS ROLE_TYPE
, USR.GROUP_ID AS GROUP_ID
, NULL AS IN_CURRENT_GROUP
FROM ASG_USER USR
, JTF_RS_RESOURCE_EXTNS JTRS
, FND_USER FU
, CSM_USER_ACC ACC
WHERE ACC.OWNER_ID = ASG_BASE.GET_USER_ID
AND JTRS.RESOURCE_ID = USR.RESOURCE_ID
AND USR.USER_NAME=FU.USER_NAME
AND ACC.USER_ID = USR.USER_ID
AND USR.GROUP_ID IS NULL UNION ALL SELECT ACC.ACCESS_ID AS ACCESS_ID
, FU.USER_ID
, FU.USER_NAME
, JTRS.RESOURCE_ID
, NULL AS CLIENT_NUMBER
, NULL AS LANGUAGE
, NULL AS ORG_ID
, NULL AS LAST_SYNCH_DATE_START
, NULL AS LAST_SYNCH_DATE_END
, JTRS.SOURCE_FIRST_NAME FIRST_NAME
, JTRS.SOURCE_LAST_NAME LAST_NAME
, JTRS.SOURCE_NAME FULL_NAME
, NULL AS RESPONSIBILITY_ID
, NULL AS MIGRATION_COMPLETED
, NULL AS MIGRATION_COMPLETED_DATE
, NULL AS MIGRATION_COMPLETION_VERSION
, FU.PERSON_PARTY_ID AS PARTY_ID
, NULL AS ROLE_NAME
, NULL AS ROLE_TYPE
, NULL AS GROUP_ID
, NULL AS IN_CURRENT_GROUP
FROM JTF_RS_RESOURCE_EXTNS JTRS
, FND_USER FU
, CSM_USER_ACC ACC
WHERE ACC.OWNER_ID = ASG_BASE.GET_USER_ID
AND JTRS.USER_NAME=FU.USER_NAME
AND FU.USER_ID=ACC.USER_ID
AND NOT EXISTS(SELECT 1
FROM ASG_USER
WHERE USER_ID=ACC.USER_ID)