VIEW: APPS.ENG_CHANGE_RELATIONS_V

Source

View Text - Preformatted

SELECT lk2.LOOKUP_CODE || ECOT.CHANGE_MGMT_TYPE_CODE , ECOTL. TYPE_NAME NAME , lk1.LOOKUP_CODE , lk1.MEANING , lk1.TAG , 'ENG_CHANGE' ,ECOT.CHANGE_MGMT_TYPE_CODE FROM ENG_CHANGE_ORDER_TYPES ECOT , ENG_CHANGE_ORDER_TYPES_TL ECOTL , FND_LOOKUP_VALUES_VL lk1 , FND_LOOKUP_VALUES_VL lk2 WHERE lk1.lookup_type = 'ENG_CHANGE_RELATIONSHIPS' and lk2.lookup_type = 'ENG_CHANGE_RELATION_OBJECTS' and lk2.tag = 'ENG_CHANGE' and ECOT.CHANGE_ORDER_TYPE_ID = ECOTL.CHANGE_ORDER_TYPE_ID and ECOTL.LANGUAGE = userenv('LANG') and ( ECOT.TYPE_CLASSIFICATION= 'CATEGORY' and ( nvl(fnd_profile.value('RESP_APPL_ID'),1) = 1 or exists (select change_type_id from eng_change_type_applications where change_type_id = ECOT.change_order_type_id and application_id = fnd_profile.value('RESP_APPL_ID')) )) and ECOT.CHANGE_MGMT_TYPE_CODE not in ('ATTACHMENT_APPROVAL', 'ATTACHMENT_REVIEW') union SELECT lk2.LOOKUP_CODE , lk2.MEANING , lk1.LOOKUP_CODE , lk1.MEANING , lk1.TAG , lk2.LOOKUP_CODE , null FROM FND_LOOKUP_VALUES_VL lk2 , FND_LOOKUP_VALUES_VL lk1 WHERE lk1.lookup_type = 'ENG_CHANGE_RELATIONSHIPS' and lk2.lookup_type = 'ENG_CHANGE_RELATION_OBJECTS' and lk2.tag != 'ENG_CHANGE'

View Text - HTML Formatted

SELECT LK2.LOOKUP_CODE || ECOT.CHANGE_MGMT_TYPE_CODE
, ECOTL. TYPE_NAME NAME
, LK1.LOOKUP_CODE
, LK1.MEANING
, LK1.TAG
, 'ENG_CHANGE'
, ECOT.CHANGE_MGMT_TYPE_CODE
FROM ENG_CHANGE_ORDER_TYPES ECOT
, ENG_CHANGE_ORDER_TYPES_TL ECOTL
, FND_LOOKUP_VALUES_VL LK1
, FND_LOOKUP_VALUES_VL LK2
WHERE LK1.LOOKUP_TYPE = 'ENG_CHANGE_RELATIONSHIPS'
AND LK2.LOOKUP_TYPE = 'ENG_CHANGE_RELATION_OBJECTS'
AND LK2.TAG = 'ENG_CHANGE'
AND ECOT.CHANGE_ORDER_TYPE_ID = ECOTL.CHANGE_ORDER_TYPE_ID
AND ECOTL.LANGUAGE = USERENV('LANG')
AND ( ECOT.TYPE_CLASSIFICATION= 'CATEGORY'
AND ( NVL(FND_PROFILE.VALUE('RESP_APPL_ID')
, 1) = 1 OR EXISTS (SELECT CHANGE_TYPE_ID
FROM ENG_CHANGE_TYPE_APPLICATIONS
WHERE CHANGE_TYPE_ID = ECOT.CHANGE_ORDER_TYPE_ID
AND APPLICATION_ID = FND_PROFILE.VALUE('RESP_APPL_ID')) ))
AND ECOT.CHANGE_MGMT_TYPE_CODE NOT IN ('ATTACHMENT_APPROVAL'
, 'ATTACHMENT_REVIEW') UNION SELECT LK2.LOOKUP_CODE
, LK2.MEANING
, LK1.LOOKUP_CODE
, LK1.MEANING
, LK1.TAG
, LK2.LOOKUP_CODE
, NULL
FROM FND_LOOKUP_VALUES_VL LK2
, FND_LOOKUP_VALUES_VL LK1
WHERE LK1.LOOKUP_TYPE = 'ENG_CHANGE_RELATIONSHIPS'
AND LK2.LOOKUP_TYPE = 'ENG_CHANGE_RELATION_OBJECTS'
AND LK2.TAG != 'ENG_CHANGE'