VIEW: APPS.CSF_M_USER_V

Source

View Text - Preformatted

SELECT usr.user_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 IN_CURRENT_GROUP, NULL AS PRIMARY_USER FROM asg_user usr, jtf_rs_resource_extns jtrs,fnd_user fu WHERE usr.user_id= ASG_BASE.GET_USER_ID AND jtrs.resource_id = usr.resource_id AND usr.user_id=fu.user_id AND NVL(usr.MULTI_PLATFORM,'N')='Y' UNION ALL SELECT csm_util_pkg.get_number(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, DECODE(usr.USER_NAME, asg_base.get_user_name, 'Y', 'N') AS PRIMARY_USER 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_id=fu.user_id AND b.ROLE_ID = usr.ROLE_ID AND b.ROLE_ID = tl.ROLE_ID AND tl.LANGUAGE = ASG_BASE.GET_LANGUAGE AND NVL(usr.MULTI_PLATFORM,'N')='N' UNION ALL SELECT csm_util_pkg.get_number(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, DECODE(usr.USER_NAME, asg_base.get_user_name, 'Y', 'N') AS PRIMARY_USER 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_id=fu.user_id AND acc.user_id = usr.user_id AND usr.GROUP_ID IS NULL AND NVL(usr.MULTI_PLATFORM,'N')='N' UNION ALL SELECT csm_util_pkg.get_number(acc.ACCESS_ID) as access_id, fu.user_id, fu.user_name, jtrs.resource_id, NULL AS client_number, csm_util_pkg.get_fnd_user_language(fu.user_id) 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, 'N' AS PRIMARY_USER 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_id=fu.user_id 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 USR.USER_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 IN_CURRENT_GROUP
, NULL AS PRIMARY_USER
FROM ASG_USER USR
, JTF_RS_RESOURCE_EXTNS JTRS
, FND_USER FU
WHERE USR.USER_ID= ASG_BASE.GET_USER_ID
AND JTRS.RESOURCE_ID = USR.RESOURCE_ID
AND USR.USER_ID=FU.USER_ID
AND NVL(USR.MULTI_PLATFORM
, 'N')='Y' UNION ALL SELECT CSM_UTIL_PKG.GET_NUMBER(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
, DECODE(USR.USER_NAME
, ASG_BASE.GET_USER_NAME
, 'Y'
, 'N') AS PRIMARY_USER
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_ID=FU.USER_ID
AND B.ROLE_ID = USR.ROLE_ID
AND B.ROLE_ID = TL.ROLE_ID
AND TL.LANGUAGE = ASG_BASE.GET_LANGUAGE
AND NVL(USR.MULTI_PLATFORM
, 'N')='N' UNION ALL SELECT CSM_UTIL_PKG.GET_NUMBER(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
, DECODE(USR.USER_NAME
, ASG_BASE.GET_USER_NAME
, 'Y'
, 'N') AS PRIMARY_USER
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_ID=FU.USER_ID
AND ACC.USER_ID = USR.USER_ID
AND USR.GROUP_ID IS NULL
AND NVL(USR.MULTI_PLATFORM
, 'N')='N' UNION ALL SELECT CSM_UTIL_PKG.GET_NUMBER(ACC.ACCESS_ID) AS ACCESS_ID
, FU.USER_ID
, FU.USER_NAME
, JTRS.RESOURCE_ID
, NULL AS CLIENT_NUMBER
, CSM_UTIL_PKG.GET_FND_USER_LANGUAGE(FU.USER_ID) 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
, 'N' AS PRIMARY_USER
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_ID=FU.USER_ID
AND FU.USER_ID=ACC.USER_ID
AND NOT EXISTS(SELECT 1
FROM ASG_USER
WHERE USER_ID=ACC.USER_ID)