View: ENG_CHANGE_OBJ_RELATIONSHIPS_V

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

View Text

SELECT ECA.CHANGE_ID
, ECA.CHANGE_RELATIONSHIP_ID
, ECA.RELATIONSHIP_CODE
, LK1.MEANING RELATIONSHIP
, ECA.OBJECT_TO_NAME
, ECOTTL.TYPE_NAME OBJECT_TO
, ECA.OBJECT_TO_ID1
, ECA.OBJECT_TO_ID2
, ECA.OBJECT_TO_ID3
, 'OAFUNC=ENG_CHANGE_SUMMARY_PAGE' || '&' || 'CHANGEID='|| ECA.OBJECT_TO_ID1 OBJECT_TO_URL
, EEC.CHANGE_NOTICE RELATIONSHIP_TO_NUMBER
, EEC.CHANGE_NAME RELATIONSHIP_TO_NAME
, LK1.TAG RELATIONSHIP_TYPE
, 'N' WUSED_RELATIONSHIP
, ECSTL.STATUS_NAME STATUS
, EEC.NEED_BY_DATE NEED_BY_DATE
, NULL REVISION
, EEC.CREATION_DATE
, EEC.LAST_UPDATE_DATE
, NULL REVISION_LABEL
FROM ENG_CHANGE_OBJ_RELATIONSHIPS ECA
, ENG_CHANGE_ORDER_TYPES ECOT
, ENG_CHANGE_ORDER_TYPES_TL ECOTTL
, ENG_ENGINEERING_CHANGES EEC
, FND_LOOKUP_VALUES_VL LK1
, ENG_CHANGE_STATUSES_TL ECSTL
WHERE ECOT.CHANGE_MGMT_TYPE_CODE = EEC.CHANGE_MGMT_TYPE_CODE
AND ECOT.TYPE_CLASSIFICATION = 'CATEGORY'
AND ECOT.CHANGE_ORDER_TYPE_ID = ECOTTL.CHANGE_ORDER_TYPE_ID
AND ECOTTL.LANGUAGE = USERENV('LANG')
AND LK1.LOOKUP_TYPE = 'ENG_CHANGE_RELATIONSHIPS'
AND LK1.LOOKUP_CODE = ECA.RELATIONSHIP_CODE
AND EEC.CHANGE_ID = ECA.OBJECT_TO_ID1
AND EEC.STATUS_CODE = ECSTL.STATUS_CODE
AND ECSTL.LANGUAGE = USERENV('LANG')
AND ECA.OBJECT_TO_NAME = 'ENG_CHANGE' UNION ALL SELECT ECA.OBJECT_TO_ID1 CHANGE_ID
, ECA.CHANGE_RELATIONSHIP_ID
, LK2.LOOKUP_CODE RELATIONSHIP_CODE
, LK2.MEANING RELATIONSHIP
, ECA.OBJECT_TO_NAME
, ECOTTL.TYPE_NAME OBJECT_TO
, ECA.CHANGE_ID OBJECT_TO_ID1
, TO_NUMBER(NULL) OBJECT_TO_ID2
, ECA.OBJECT_TO_ID3
, 'OAFUNC=ENG_CHANGE_SUMMARY_PAGE' || '&' || 'CHANGEID='|| ECA.CHANGE_ID OBJECT_TO_URL
, EEC.CHANGE_NOTICE RELATIONSHIP_TO_NUMBER
, EEC.CHANGE_NAME RELATIONSHIP_TO_NAME
, LK1.TAG RELATIONSHIP_TYPE
, 'Y' WUSED_RELATIONSHIP
, ECSTL.STATUS_NAME STATUS
, EEC.NEED_BY_DATE NEED_BY_DATE
, NULL REVISION
, EEC.CREATION_DATE
, EEC.LAST_UPDATE_DATE
, NULL REVISION_LABEL
FROM ENG_CHANGE_OBJ_RELATIONSHIPS ECA
, ENG_CHANGE_ORDER_TYPES ECOT
, ENG_CHANGE_ORDER_TYPES_TL ECOTTL
, ENG_ENGINEERING_CHANGES EEC
, FND_LOOKUP_VALUES LK1
, FND_LOOKUP_VALUES_VL LK2
, ENG_CHANGE_STATUSES_TL ECSTL
WHERE ECOT.CHANGE_MGMT_TYPE_CODE = EEC.CHANGE_MGMT_TYPE_CODE
AND ECOT.CHANGE_ORDER_TYPE_ID = ECOTTL.CHANGE_ORDER_TYPE_ID
AND ECOT.TYPE_CLASSIFICATION = 'CATEGORY'
AND ECOTTL.LANGUAGE = USERENV('LANG')
AND LK2.LOOKUP_TYPE = 'ENG_CHANGE_WUSED_RELATIONSHIPS'
AND LK2.TAG = LK1.LOOKUP_CODE
AND LK1.LOOKUP_TYPE = 'ENG_CHANGE_RELATIONSHIPS'
AND LK1.LOOKUP_CODE = ECA.RELATIONSHIP_CODE
AND LK1.LANGUAGE = USERENV('LANG')
AND EEC.CHANGE_ID = ECA.CHANGE_ID
AND EEC.STATUS_CODE = ECSTL.STATUS_CODE
AND ECSTL.LANGUAGE = USERENV('LANG')
AND ECA.OBJECT_TO_NAME = 'ENG_CHANGE' UNION ALL SELECT ECA.CHANGE_ID
, ECA.CHANGE_RELATIONSHIP_ID
, ECA.RELATIONSHIP_CODE
, LK1.MEANING RELATIONSHIP
, ECA.OBJECT_TO_NAME
, LK2.MEANING OBJECT_TO
, ECA.OBJECT_TO_ID1
, ECA.OBJECT_TO_ID2
, ECA.OBJECT_TO_ID3
, DECODE(ECA.OBJECT_TO_ID2
, NULL
, 'AKREGIONCODE=PA_PROJECT_HOME_LAYOUT' || '&' || 'AKREGIONAPPLICATIONID=275' || '&' || 'ADDBREADCRUMB=RS' || '&' || 'PAPROJECTID='|| ECA.OBJECT_TO_ID1
, 'AKREGIONCODE=PA_STRUCT_TASK_OVERVIEW_LAYOUT' || '&' || 'AKREGIONAPPLICATIONID=275' || '&' || 'ADDBREADCRUMB=RS' || '&' || 'PAPROJECTID='|| ECA.OBJECT_TO_ID1 || '&' || 'PAPROJELEMENTID=' || ECA.OBJECT_TO_ID2 ) OBJECT_TO_URL
, DECODE(ECA.OBJECT_TO_ID2
, NULL
, PROJ.SEGMENT1
, TASK.TASK_NUMBER ||'('|| PROJ.SEGMENT1 || ')') RELATIONSHIP_TO_NUMBER
, DECODE(ECA.OBJECT_TO_ID2
, NULL
, PROJ.NAME
, TASK.TASK_NAME ||'('|| PROJ.NAME || ')') RELATIONSHIP_TO_NAME
, LK1.TAG RELATIONSHIP_TYPE
, 'N' WUSED_RELATIONSHIP
, NULL STATUS
, TO_DATE(NULL) NEED_BY_DATE
, NULL REVISION
, ECA.CREATION_DATE
, ECA.LAST_UPDATE_DATE
, NULL REVISION_LABEL
FROM ENG_CHANGE_OBJ_RELATIONSHIPS ECA
, FND_LOOKUP_VALUES_VL LK2
, PA_PROJECTS_ALL PROJ
, FND_LOOKUP_VALUES_VL LK1
, PA_TASKS TASK
WHERE LK2.LOOKUP_TYPE = 'ENG_CHANGE_RELATION_OBJECTS'
AND LK2.LOOKUP_CODE = ECA.OBJECT_TO_NAME
AND LK1.LOOKUP_TYPE = 'ENG_CHANGE_RELATIONSHIPS'
AND LK1.LOOKUP_CODE = ECA.RELATIONSHIP_CODE
AND TASK.TASK_ID (+) = ECA.OBJECT_TO_ID2
AND PROJ.PROJECT_ID = ECA.OBJECT_TO_ID1
AND ECA.OBJECT_TO_NAME = 'PA_PROJECTS' UNION ALL SELECT ECA.CHANGE_ID
, ECA.CHANGE_RELATIONSHIP_ID
, ECA.RELATIONSHIP_CODE
, LK1.MEANING RELATIONSHIP
, ECA.OBJECT_TO_NAME
, LK2.MEANING OBJECT_TO
, ECA.OBJECT_TO_ID1
, ECA.OBJECT_TO_ID2
, ECA.OBJECT_TO_ID3
, 'OAFUNC=EGO_ITEM_OVERVIEW' || '&' || 'INVENTORYITEMID='|| ECA.OBJECT_TO_ID1 || '&' || 'ORGANIZATIONID=' || ECA.OBJECT_TO_ID2 OBJECT_TO_URL
, MSIKFV.CONCATENATED_SEGMENTS RELATIONSHIP_TO_NUMBER
, MSIKFV.DESCRIPTION RELATIONSHIP_TO_NAME
, LK1.TAG RELATIONSHIP_TYPE
, 'N' WUSED_RELATIONSHIP
, NULL STATUS
, TO_DATE(NULL) NEED_BY_DATE
, NULL REVISION
, ECA.CREATION_DATE
, ECA.LAST_UPDATE_DATE
, NULL REVISION_LABEL
FROM ENG_CHANGE_OBJ_RELATIONSHIPS ECA
, FND_LOOKUP_VALUES_VL LK2
, FND_LOOKUP_VALUES_VL LK1
, MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE LK2.LOOKUP_TYPE = 'ENG_CHANGE_RELATION_OBJECTS'
AND LK2.LOOKUP_CODE = ECA.OBJECT_TO_NAME
AND LK1.LOOKUP_TYPE = 'ENG_CHANGE_RELATIONSHIPS'
AND LK1.LOOKUP_CODE = ECA.RELATIONSHIP_CODE
AND MSIKFV.INVENTORY_ITEM_ID = ECA.OBJECT_TO_ID1
AND MSIKFV.ORGANIZATION_ID = ECA.OBJECT_TO_ID2
AND ECA.OBJECT_TO_NAME = 'EGO_ITEM' UNION ALL SELECT ECA.CHANGE_ID
, ECA.CHANGE_RELATIONSHIP_ID
, ECA.RELATIONSHIP_CODE
, LK1.MEANING RELATIONSHIP
, ECA.OBJECT_TO_NAME
, LK2.MEANING OBJECT_TO
, ECA.OBJECT_TO_ID1
, ECA.OBJECT_TO_ID2
, ECA.OBJECT_TO_ID3
, 'OAFUNC=EGO_ITEM_OVERVIEW' || '&' || 'INVENTORYITEMID='|| ECA.OBJECT_TO_ID1 || '&' || 'ORGANIZATIONID=' || ECA.OBJECT_TO_ID2 OBJECT_TO_URL
, MSIKFV.CONCATENATED_SEGMENTS RELATIONSHIP_TO_NUMBER
, MSIKFV.DESCRIPTION RELATIONSHIP_TO_NAME
, LK1.TAG RELATIONSHIP_TYPE
, 'N' WUSED_RELATIONSHIP
, NULL STATUS
, TO_DATE(NULL) NEED_BY_DATE
, MIRV.REVISION
, ECA.CREATION_DATE
, ECA.LAST_UPDATE_DATE
, MIRV.REVISION_LABEL
FROM ENG_CHANGE_OBJ_RELATIONSHIPS ECA
, FND_LOOKUP_VALUES_VL LK2
, FND_LOOKUP_VALUES_VL LK1
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, MTL_ITEM_REVISIONS_VL MIRV
WHERE LK2.LOOKUP_TYPE = 'ENG_CHANGE_RELATION_OBJECTS'
AND LK2.LOOKUP_CODE = ECA.OBJECT_TO_NAME
AND LK1.LOOKUP_TYPE = 'ENG_CHANGE_RELATIONSHIPS'
AND LK1.LOOKUP_CODE = ECA.RELATIONSHIP_CODE
AND MSIKFV.INVENTORY_ITEM_ID = ECA.OBJECT_TO_ID1
AND MSIKFV.ORGANIZATION_ID = ECA.OBJECT_TO_ID2
AND ECA.OBJECT_TO_NAME = 'EGO_ITEM_REVISION'
AND MIRV.INVENTORY_ITEM_ID = ECA.OBJECT_TO_ID1
AND MIRV.ORGANIZATION_ID = ECA.OBJECT_TO_ID2
AND MIRV.REVISION_ID = ECA.OBJECT_TO_ID3

Columns