View: ENG_CHANGE_ROUTE_PEOPLE_V

Product:	ENG - Engineering
Description:
Implementation/DBA Data:	APPS.ENG_CHANGE_ROUTE_PEOPLE_V

View Text

SELECT ROUTEPEOPLE.ROW_ID
, ROUTEPEOPLE.ROUTE_PEOPLE_ID
, ROUTEPEOPLE.STEP_ID
, ROUTEPEOPLE.ASSIGNEE_ID
, MENUTL.USER_MENU_NAME ASSIGNEE_NAME
, ROUTEPEOPLE.ASSIGNEE_TYPE_CODE
, ASSIGNEETYPELOOKUP.MEANING ASSIGNEE_TYPE
, '' ASSIGNEE_COMPANY_NAME
, ROUTEPEOPLE.ADHOC_PEOPLE_FLAG
, ROUTEPEOPLE.RESPONSE_DESCRIPTION
, ROUTEPEOPLE.WF_NOTIFICATION_ID
, ROUTEPEOPLE.RESPONSE_CODE
, PEPOPLERESPTYPELOOKUP.MEANING RESPONSE
, ROUTEPEOPLE.RESPONSE_DATE
, ROUTEPEOPLE.CREATION_DATE
, ROUTEPEOPLE.CREATED_BY
, ROUTEPEOPLE.LAST_UPDATE_DATE
, ROUTEPEOPLE.LAST_UPDATED_BY
, ROUTEPEOPLE.LAST_UPDATE_LOGIN
, ROUTEPEOPLE.REQUEST_ID
, ROUTEPEOPLE.PROGRAM_ID
, ROUTEPEOPLE.PROGRAM_APPLICATION_ID
, ROUTEPEOPLE.PROGRAM_UPDATE_DATE
, ROUTEPEOPLE.ORIGINAL_SYSTEM_REFERENCE
FROM FND_LOOKUPS ASSIGNEETYPELOOKUP
, FND_LOOKUPS PEPOPLERESPTYPELOOKUP
, FND_MENUS_TL MENUTL
, ENG_CHANGE_ROUTE_PEOPLE_VL ROUTEPEOPLE
WHERE PEPOPLERESPTYPELOOKUP.LOOKUP_CODE(+) = ROUTEPEOPLE.RESPONSE_CODE
AND PEPOPLERESPTYPELOOKUP.LOOKUP_TYPE(+) = 'ENG_CHANGE_ROUTE_STATUSES'
AND ASSIGNEETYPELOOKUP.LOOKUP_CODE = ROUTEPEOPLE.ASSIGNEE_TYPE_CODE
AND ASSIGNEETYPELOOKUP.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
AND MENUTL.LANGUAGE = USERENV('LANG')
AND MENUTL.MENU_ID = ROUTEPEOPLE.ASSIGNEE_ID
AND ROUTEPEOPLE.ASSIGNEE_TYPE_CODE = 'ROLE' UNION ALL SELECT ROUTEPEOPLE.ROW_ID
, ROUTEPEOPLE.ROUTE_PEOPLE_ID
, ROUTEPEOPLE.STEP_ID
, ROUTEPEOPLE.ASSIGNEE_ID
, PARTIES.PARTY_NAME ASSIGNEE_NAME
, ROUTEPEOPLE.ASSIGNEE_TYPE_CODE
, ASSIGNEETYPELOOKUP.MEANING ASSIGNEE_TYPE
, COMPANY.PARTY_NAME ASSIGNEE_COMPANY_NAME
, ROUTEPEOPLE.ADHOC_PEOPLE_FLAG
, ROUTEPEOPLE.RESPONSE_DESCRIPTION
, ROUTEPEOPLE.WF_NOTIFICATION_ID
, ROUTEPEOPLE.RESPONSE_CODE
, PEPOPLERESPTYPELOOKUP.MEANING RESPONSE
, ROUTEPEOPLE.RESPONSE_DATE
, ROUTEPEOPLE.CREATION_DATE
, ROUTEPEOPLE.CREATED_BY
, ROUTEPEOPLE.LAST_UPDATE_DATE
, ROUTEPEOPLE.LAST_UPDATED_BY
, ROUTEPEOPLE.LAST_UPDATE_LOGIN
, ROUTEPEOPLE.REQUEST_ID
, ROUTEPEOPLE.PROGRAM_ID
, ROUTEPEOPLE.PROGRAM_APPLICATION_ID
, ROUTEPEOPLE.PROGRAM_UPDATE_DATE
, ROUTEPEOPLE.ORIGINAL_SYSTEM_REFERENCE
FROM FND_LOOKUPS ASSIGNEETYPELOOKUP
, FND_LOOKUPS PEPOPLERESPTYPELOOKUP
, HZ_RELATIONSHIPS EMP_CMPY
, HZ_PARTIES COMPANY
, HZ_PARTIES PARTIES
, ENG_CHANGE_ROUTE_PEOPLE_VL ROUTEPEOPLE
WHERE PEPOPLERESPTYPELOOKUP.LOOKUP_CODE(+)= ROUTEPEOPLE.RESPONSE_CODE
AND PEPOPLERESPTYPELOOKUP.LOOKUP_TYPE(+)= 'ENG_CHANGE_ROUTE_STATUSES'
AND ASSIGNEETYPELOOKUP.LOOKUP_CODE = ROUTEPEOPLE.ASSIGNEE_TYPE_CODE
AND ASSIGNEETYPELOOKUP.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
AND EMP_CMPY.SUBJECT_TYPE (+)= 'PERSON'
AND EMP_CMPY.SUBJECT_TABLE_NAME (+)= 'HZ_PARTIES'
AND EMP_CMPY.OBJECT_TYPE (+)= 'ORGANIZATION'
AND EMP_CMPY.RELATIONSHIP_CODE (+)= 'EMPLOYEE_OF'
AND EMP_CMPY.OBJECT_TABLE_NAME (+)= 'HZ_PARTIES'
AND EMP_CMPY.STATUS (+)= 'A'
AND EMP_CMPY.START_DATE (+)<= SYSDATE
AND (EMP_CMPY.END_DATE IS NULL OR EMP_CMPY.END_DATE >= SYSDATE)
AND COMPANY.PARTY_ID (+)= EMP_CMPY.OBJECT_ID
AND COMPANY.STATUS (+)= 'A'
AND EMP_CMPY.SUBJECT_ID (+)= PARTIES.PARTY_ID
AND PARTIES.PARTY_TYPE = 'PERSON'
AND PARTIES.PARTY_ID = ROUTEPEOPLE.ASSIGNEE_ID
AND ROUTEPEOPLE.ASSIGNEE_TYPE_CODE = 'PERSON' UNION ALL SELECT ROUTEPEOPLE.ROW_ID
, ROUTEPEOPLE.ROUTE_PEOPLE_ID
, ROUTEPEOPLE.STEP_ID
, ROUTEPEOPLE.ASSIGNEE_ID
, GRP.PARTY_NAME ASSIGNEE_NAME
, ROUTEPEOPLE.ASSIGNEE_TYPE_CODE
, ASSIGNEETYPELOOKUP.MEANING ASSIGNEE_TYPE
, '' ASSIGNEE_COMPANY_NAME
, ROUTEPEOPLE.ADHOC_PEOPLE_FLAG
, ROUTEPEOPLE.RESPONSE_DESCRIPTION
, ROUTEPEOPLE.WF_NOTIFICATION_ID
, ROUTEPEOPLE.RESPONSE_CODE
, PEPOPLERESPTYPELOOKUP.MEANING RESPONSE
, ROUTEPEOPLE.RESPONSE_DATE
, ROUTEPEOPLE.CREATION_DATE
, ROUTEPEOPLE.CREATED_BY
, ROUTEPEOPLE.LAST_UPDATE_DATE
, ROUTEPEOPLE.LAST_UPDATED_BY
, ROUTEPEOPLE.LAST_UPDATE_LOGIN
, ROUTEPEOPLE.REQUEST_ID
, ROUTEPEOPLE.PROGRAM_ID
, ROUTEPEOPLE.PROGRAM_APPLICATION_ID
, ROUTEPEOPLE.PROGRAM_UPDATE_DATE
, ROUTEPEOPLE.ORIGINAL_SYSTEM_REFERENCE
FROM HZ_PARTIES GRP
, FND_LOOKUPS ASSIGNEETYPELOOKUP
, FND_LOOKUPS PEPOPLERESPTYPELOOKUP
, ENG_CHANGE_ROUTE_PEOPLE_VL ROUTEPEOPLE
WHERE PEPOPLERESPTYPELOOKUP.LOOKUP_CODE(+) = ROUTEPEOPLE.RESPONSE_CODE
AND PEPOPLERESPTYPELOOKUP.LOOKUP_TYPE(+) = 'ENG_CHANGE_ROUTE_STATUSES'
AND ASSIGNEETYPELOOKUP.LOOKUP_CODE = ROUTEPEOPLE.ASSIGNEE_TYPE_CODE
AND ASSIGNEETYPELOOKUP.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
AND GRP.PARTY_TYPE = 'GROUP'
AND GRP.PARTY_ID = ROUTEPEOPLE.ASSIGNEE_ID
AND ROUTEPEOPLE.ASSIGNEE_TYPE_CODE = 'GROUP'

Columns