SELECT /* $HEADER: AFREGVW.LDT 115.3 2002/06/17 18:39:12 SSUNG NOSHIP $ */ USR.USER_NAME , 'PER' , PER.PERSON_ID , 'POS'||':'||POS.POSITION_ID , 'POS' , POS.POSITION_ID FROM PER_ASSIGNMENTS_F ASS , PER_POSITIONS POS , FND_USER USR , PER_ALL_PEOPLE_F PER WHERE ASS.POSITION_ID = POS.POSITION_ID AND ASS.PERSON_ID = USR.EMPLOYEE_ID AND ASS.PERSON_ID = PER.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE AND ASS.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN USR.START_DATE AND NVL(USR.END_DATE , SYSDATE+1) AND PER.EMPLOYEE_NUMBER IS NOT NULL AND ASS.ASSIGNMENT_TYPE = 'E' /* ENGINEERING APPROVAL ROLES */ UNION ALL SELECT USR.USER_NAME , 'PER' , PER.PERSON_ID , 'ENG_LIST'||':'||EEAL.APPROVAL_LIST_ID , 'ENG_LIST' , EEAL.APPROVAL_LIST_ID FROM FND_USER USR , PER_ALL_PEOPLE_F PER , ENG_ECN_APPROVAL_LISTS EEAL , ENG_ECN_APPROVERS EEA WHERE EEA.EMPLOYEE_ID = PER.PERSON_ID AND PER.PERSON_ID = USR.EMPLOYEE_ID AND EEA.APPROVAL_LIST_ID = EEAL.APPROVAL_LIST_ID AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN USR.START_DATE AND NVL(USR.END_DATE , SYSDATE+1) AND TRUNC(SYSDATE) <= NVL(EEA.DISABLE_DATE , SYSDATE+1) UNION ALL /* USER RESPONSIBILITY WHERE USER NOT LINKED TO AN EMPLOYEE */ SELECT DISTINCT U.USER_NAME , 'FND_USR' , U.USER_ID , 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||R.RESPONSIBILITY_ID , 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID , R.RESPONSIBILITY_ID FROM FND_USER U , FND_USER_RESP_GROUPS UR , FND_RESPONSIBILITY R WHERE U.USER_ID = UR.USER_ID AND UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND TRUNC(SYSDATE) BETWEEN UR.START_DATE AND NVL(UR.END_DATE , SYSDATE + 1) AND TRUNC(SYSDATE) BETWEEN U.START_DATE AND NVL(U.END_DATE , SYSDATE + 1) AND TRUNC(SYSDATE) BETWEEN R.START_DATE AND NVL(R.END_DATE , SYSDATE + 1) AND U.EMPLOYEE_ID IS NULL UNION ALL SELECT DISTINCT U.USER_NAME , 'PER' , U.EMPLOYEE_ID , 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||R.RESPONSIBILITY_ID , 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID , R.RESPONSIBILITY_ID FROM FND_USER U , FND_USER_RESP_GROUPS UR , FND_RESPONSIBILITY R WHERE U.USER_ID = UR.USER_ID AND UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID AND TRUNC(SYSDATE) BETWEEN UR.START_DATE AND NVL(UR.END_DATE , SYSDATE + 1) AND TRUNC(SYSDATE) BETWEEN U.START_DATE AND NVL(U.END_DATE , SYSDATE + 1) AND TRUNC(SYSDATE) BETWEEN R.START_DATE AND NVL(R.END_DATE , SYSDATE + 1) AND U.EMPLOYEE_ID IS NOT NULL UNION ALL /* EVERY GROUP BOX USER MUST BE AN EMPLOYEE EXIST IN FND_USER */ SELECT GBU.USER_NAME , 'PER' , U.EMPLOYEE_ID , GBX.NAME , 'GBX' , GBX.GROUPBOX_ID FROM GHR_GROUPBOXES GBX , GHR_GROUPBOX_USERS GBU , FND_USER U WHERE GBU.GROUPBOX_ID = GBX.GROUPBOX_ID AND GBU.USER_NAME = U.USER_NAME AND U.EMPLOYEE_ID IS NOT NULL UNION ALL /* ADDED FOR AMV SEE BUG 1409680 FOR HISTORY */ SELECT USR.USER_NAME , 'PER' , PER.PERSON_ID , 'AMV_CHN'||':'||CHB.CHANNEL_ID , 'AMV_CHN' , CHB.CHANNEL_ID FROM AMV_C_CHANNELS_B CHB , AMV_U_ACCESS CUA , JTF_RS_RESOURCE_EXTNS JRE , PER_ALL_PEOPLE_F PER , FND_USER USR WHERE CUA.ACCESS_TO_TABLE_CODE = 'CHANNEL' AND CUA.ACCESS_TO_TABLE_RECORD_ID = CHB.CHANNEL_ID AND CUA.USER_OR_GROUP_TYPE = 'USER' AND CUA.EFFECTIVE_START_DATE <= SYSDATE AND NVL(CUA.EXPIRATION_DATE , SYSDATE) >= SYSDATE AND CUA.CHL_APPROVER_FLAG = 'T' AND CUA.USER_OR_GROUP_ID = JRE.RESOURCE_ID AND JRE.CATEGORY = 'EMPLOYEE' AND JRE.SOURCE_ID = PER.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND PER.PERSON_ID = USR.EMPLOYEE_ID UNION ALL /* ADDED FOR AMV SEE BUG 1409680 FOR HISTORY */ SELECT USR.USER_NAME , 'PER' , PER.PERSON_ID , 'AMV_APPR' , 'AMV_APPR' , 520 FROM JTF_RS_ROLES_B JRB , JTF_RS_ROLE_RELATIONS JRR , JTF_RS_RESOURCE_EXTNS JRE , PER_ALL_PEOPLE_F PER , FND_USER USR WHERE JRB.ROLE_TYPE_CODE = 'MES' AND JRB.ROLE_CODE = 'MES_APPROVE' AND JRB.ROLE_ID = JRR.ROLE_ID AND JRR.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL' AND JRR.ROLE_RESOURCE_ID = JRE.RESOURCE_ID AND JRE.CATEGORY = 'EMPLOYEE' AND JRE.SOURCE_ID = PER.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND PER.PERSON_ID = USR.EMPLOYEE_ID UNION ALL /* ADDED FOR AMV SEE BUG 1409680 FOR HISTORY */ SELECT USR.USER_NAME , 'PER' , PER.PERSON_ID , 'AMV_APPR' , 'AMV_APPR' , 520 FROM JTF_RS_ROLES_B JRB , JTF_RS_ROLE_RELATIONS JRR , JTF_RS_GROUP_USAGES JGU , JTF_RS_GROUPS_B JGB , JTF_RS_GROUP_MEMBERS JGM , JTF_RS_RESOURCE_EXTNS JRE , PER_ALL_PEOPLE_F PER , FND_USER USR WHERE JRB.ROLE_TYPE_CODE = 'MES' AND JRB.ROLE_CODE = 'MES_APPROVE' AND JRB.ROLE_ID = JRR.ROLE_ID AND JRR.ROLE_RESOURCE_TYPE = 'RS_GROUP' AND JRR.ROLE_RESOURCE_ID = JGB.GROUP_ID AND JGU.USAGE = 'MES_GROUP' AND JGU.GROUP_ID = JGB.GROUP_ID AND JGU.GROUP_ID = JRR.ROLE_RESOURCE_ID AND JGB.GROUP_ID = JGM.GROUP_ID AND JGM.DELETE_FLAG <> 'T' AND JGM.RESOURCE_ID = JRE.RESOURCE_ID AND JRE.CATEGORY = 'EMPLOYEE' AND JRE.SOURCE_ID = PER.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND PER.PERSON_ID = USR.EMPLOYEE_ID UNION ALL /* ADDED AS BUG 1391687 */ SELECT 'HZ_PARTY:'||TO_CHAR(SP.PARTY_ID) USER_NAME , 'HZ_PARTY' USER_ORIG_SYSTEM , SP.PARTY_ID USER_ORIG_SYSTEM_ID , 'HZ_GROUP:'||TO_CHAR(OP.PARTY_ID) ROLE_NAME , 'HZ_GROUP' ROLE_ORIG_SYSTEM , OP.PARTY_ID ROLE_ORIG_SYSTEM_ID FROM HZ_PARTY_RELATIONSHIPS PR , HZ_PARTIES SP , HZ_PARTIES OP WHERE SP.PARTY_ID = PR.SUBJECT_ID AND SP.PARTY_TYPE = 'PERSON' AND OP.PARTY_ID = PR.OBJECT_ID AND OP.PARTY_TYPE = 'GROUP' UNION ALL SELECT NAME , ORIG_SYSTEM , ORIG_SYSTEM_ID , NAME , ORIG_SYSTEM , ORIG_SYSTEM_ID FROM WF_LOCAL_USERS
[Home] [Help]