VIEW: APPS.ENG_CHANGE_OBJ_RELATIONSHIPS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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