View: CS_KB_INTEGRATION_V

Product:	CS - Service
Description:	View combines JTF notes with knowledge management element links and set links. Used by Knowledge Management for integration from any application module.
Implementation/DBA Data:	APPS.CS_KB_INTEGRATION_V

View Text

SELECT NOTE_B.ROWID ROW_ID
, NOTE_B.JTF_NOTE_ID ID
, NOTE_B.SOURCE_OBJECT_ID APP_OBJ_ID
, NOTE_B.SOURCE_OBJECT_CODE APP_OBJ_CODE
, NOTE_B.NOTE_TYPE NOTE_TYPE_CODE
, LOOKUP.MEANING NOTE_TYPE_MEANING
, ELEM_TYPE_B.ELEMENT_TYPE_ID KB_TYPE_ID
, ELEM_TYPE_TL.NAME KB_TYPE_MEANING
, NOTE_B.NOTE_STATUS STATUS
, NOTE_TL.NOTES NOTE
, 'T' TRUE_STMT
, NOTE_B.CREATION_DATE CREATION_DATE
, NOTE_B.CREATED_BY CREATED_BY
, NOTE_B.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NOTE_B.LAST_UPDATED_BY LAST_UPDATED_BY
, NOTE_B.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, 'JTF_NOTE' RECORD_SOURCE_TYPE
FROM JTF_NOTES_B NOTE_B
, JTF_NOTES_TL NOTE_TL
, CS_KB_ELE_TYPE_LINKS ELEM_TYPE_LNK
, CS_KB_ELEMENT_TYPES_B ELEM_TYPE_B
, CS_KB_ELEMENT_TYPES_TL ELEM_TYPE_TL
, FND_LOOKUP_VALUES LOOKUP
WHERE NOTE_B.JTF_NOTE_ID = NOTE_TL.JTF_NOTE_ID
AND NOTE_TL.LANGUAGE = USERENV('LANG')
AND LOOKUP.LOOKUP_TYPE(+) = 'JTF_NOTE_TYPE'
AND NOTE_B.NOTE_TYPE = LOOKUP.LOOKUP_CODE(+)
AND LOOKUP.LANGUAGE(+) = USERENV('LANG')
AND ELEM_TYPE_B.ELEMENT_TYPE_ID = ELEM_TYPE_TL.ELEMENT_TYPE_ID
AND ELEM_TYPE_TL.LANGUAGE = USERENV('LANG')
AND NOTE_B.NOTE_TYPE = ELEM_TYPE_LNK.OTHER_CODE
AND ELEM_TYPE_B.ELEMENT_TYPE_ID = ELEM_TYPE_LNK.ELEMENT_TYPE_ID UNION ALL SELECT KB_ELEM_B.ROWID
, KB_ELEM_B.ELEMENT_ID
, KB_ELEM_LINK.OTHER_ID
, OBJECTS.OBJECT_CODE
, LKUP.LOOKUP_CODE
, LKUP.MEANING
, KB_ELEM_TYPE_B.ELEMENT_TYPE_ID
, KB_ELEM_TYPE_TL.NAME
, NULL
, KB_ELEM_TL.NAME
, DECODE(KB_ELEM_LINK.LINK_TYPE
, 'T'
, 'T'
, 'F'
, 'F'
, 'T')
, KB_ELEM_LINK.CREATION_DATE
, KB_ELEM_LINK.CREATED_BY
, KB_ELEM_LINK.LAST_UPDATE_DATE
, KB_ELEM_LINK.LAST_UPDATED_BY
, KB_ELEM_LINK.LAST_UPDATE_LOGIN
, 'KB_ELEM'
FROM CS_KB_ELEMENT_LINKS KB_ELEM_LINK
, CS_KB_ELEMENT_TYPES_B KB_ELEM_TYPE_B
, CS_KB_ELEMENT_TYPES_TL KB_ELEM_TYPE_TL
, CS_KB_ELEMENTS_B KB_ELEM_B
, CS_KB_ELEMENTS_TL KB_ELEM_TL
, JTF_OBJECTS_B OBJECTS
, CS_KB_ELE_TYPE_LINKS KB_ELEM_TYPE_LNK
, FND_LOOKUPS LKUP
WHERE KB_ELEM_B.ELEMENT_ID = KB_ELEM_TL.ELEMENT_ID
AND KB_ELEM_TL.LANGUAGE = USERENV('LANG')
AND KB_ELEM_TYPE_B.ELEMENT_TYPE_ID = KB_ELEM_TYPE_TL.ELEMENT_TYPE_ID
AND KB_ELEM_TYPE_TL.LANGUAGE = USERENV('LANG')
AND KB_ELEM_LINK.ELEMENT_ID = KB_ELEM_B.ELEMENT_ID
AND KB_ELEM_B.ELEMENT_TYPE_ID = KB_ELEM_TYPE_B.ELEMENT_TYPE_ID
AND KB_ELEM_LINK.OBJECT_CODE = OBJECTS.OBJECT_CODE
AND KB_ELEM_TYPE_LNK.ELEMENT_TYPE_ID = KB_ELEM_TYPE_B.ELEMENT_TYPE_ID
AND KB_ELEM_TYPE_LNK.OTHER_CODE = LKUP.LOOKUP_CODE
AND LKUP.LOOKUP_TYPE = 'JTF_NOTE_TYPE' UNION ALL SELECT KB_SET_B.ROWID
, KB_SET_B.SET_ID
, KB_SET_LINK.OTHER_ID
, OBJECTS.OBJECT_CODE
, 'KB_SOLN'
, KB_SET_TYPE_TL.NAME
, KB_SET_TYPE_B.SET_TYPE_ID
, KB_SET_TYPE_TL.NAME
, NULL
, KB_SET_TL.NAME
, DECODE(KB_SET_LINK.LINK_TYPE
, 'T'
, 'T'
, 'F'
, 'F'
, 'T')
, KB_SET_LINK.CREATION_DATE
, KB_SET_LINK.CREATED_BY
, KB_SET_LINK.LAST_UPDATE_DATE
, KB_SET_LINK.LAST_UPDATED_BY
, KB_SET_LINK.LAST_UPDATE_LOGIN
, 'KB_SOLN'
FROM CS_KB_SET_LINKS KB_SET_LINK
, CS_KB_SET_TYPES_B KB_SET_TYPE_B
, CS_KB_SET_TYPES_TL KB_SET_TYPE_TL
, CS_KB_SETS_B KB_SET_B
, CS_KB_SETS_TL KB_SET_TL
, JTF_OBJECTS_B OBJECTS
WHERE KB_SET_B.SET_ID = KB_SET_TL.SET_ID
AND KB_SET_TL.LANGUAGE = USERENV('LANG')
AND KB_SET_TYPE_B.SET_TYPE_ID = KB_SET_TYPE_TL.SET_TYPE_ID
AND KB_SET_TYPE_TL.LANGUAGE = USERENV('LANG')
AND KB_SET_LINK.SET_ID = KB_SET_B.SET_ID
AND KB_SET_B.SET_TYPE_ID = KB_SET_TYPE_B.SET_TYPE_ID
AND KB_SET_LINK.OBJECT_CODE = OBJECTS.OBJECT_CODE

Columns