VIEW: APPS.ENG_CHANGE_ROUTE_PEOPLE_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'