View: CS_SR_KB_NOTE_LINKS_V

Product:	CS - Service
Description:	This view combines JTF notes with knowledge management element links and set links. Used by Oracle Support for integration from Service Requests to Knowledge Management.
Implementation/DBA Data:	APPS.CS_SR_KB_NOTE_LINKS_V

View Text

SELECT NOTE.JTF_NOTE_ID ID
, NOTE.SOURCE_OBJECT_ID APP_OBJ_ID
, NOTE.SOURCE_OBJECT_CODE APP_OBJ_CODE
, NOTE.NOTE_TYPE NOTE_TYPE_CODE
, LKCMTTYPE.MEANING NOTE_TYPE_MEANING
, NOTE_TL.NOTES NOTE
, 'T' TRUE_STMT
, NOTE.CREATION_DATE CREATION_DATE
, NOTE.CREATED_BY CREATED_BY
, NOTE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NOTE.LAST_UPDATED_BY LAST_UPDATED_BY
, NOTE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, 'JTF_NOTE' RECORD_SOURCE_TYPE
, NOTE.NOTE_STATUS NOTE_STATUS
, LKUP.MEANING NOTE_STATUS_MEANING
, TO_NUMBER(NULL) SOLUTION_ID
, NULL OUTCOME
, NULL SOLUTION_NUMBER
FROM JTF_NOTES_B NOTE
, JTF_NOTES_TL NOTE_TL
, FND_LOOKUPS LKCMTTYPE
, FND_LOOKUPS LKUP
WHERE NOTE.JTF_NOTE_ID = NOTE_TL.JTF_NOTE_ID
AND NOTE_TL.LANGUAGE = USERENV('LANG')
AND LKCMTTYPE.LOOKUP_TYPE(+) = 'JTF_NOTE_TYPE'
AND NOTE.NOTE_TYPE = LKCMTTYPE.LOOKUP_CODE(+)
AND LKUP.LOOKUP_TYPE = 'JTF_NOTE_STATUS'
AND LKUP.LOOKUP_CODE = NOTE.NOTE_STATUS UNION ALL SELECT ELEM.ELEMENT_ID
, ELEM_LINK.OTHER_ID
, ELEM_LINK.OBJECT_CODE
, LKUP.LOOKUP_CODE
, LKUP.MEANING
, ELEM_TL.NAME
, ELEM_LINK.LINK_TYPE
, ELEM_LINK.CREATION_DATE
, ELEM_LINK.CREATED_BY
, ELEM_LINK.LAST_UPDATE_DATE
, ELEM_LINK.LAST_UPDATED_BY
, ELEM_LINK.LAST_UPDATE_LOGIN
, 'KB_ELEM'
, NULL
, NULL
, TO_NUMBER(NULL) SOLUTION_ID
, NULL OUTCOME
, NULL SOLUTION_NUMBER
FROM CS_KB_ELEMENT_LINKS ELEM_LINK
, CS_KB_ELEMENTS_B ELEM
, CS_KB_ELEMENTS_TL ELEM_TL
, CS_KB_ELE_TYPE_LINKS ELEM_TYPE_LNK
, FND_LOOKUPS LKUP
WHERE ELEM.ELEMENT_ID = ELEM_TL.ELEMENT_ID
AND ELEM_TL.LANGUAGE = USERENV('LANG')
AND ELEM_LINK.ELEMENT_ID = ELEM.ELEMENT_ID
AND ELEM.ELEMENT_TYPE_ID = ELEM_TYPE_LNK.ELEMENT_TYPE_ID
AND ELEM_TYPE_LNK.OTHER_CODE = LKUP.LOOKUP_CODE
AND LKUP.LOOKUP_TYPE = 'JTF_NOTE_TYPE' UNION ALL SELECT KB_SET.SET_ID
, KB_SET_LINK.OTHER_ID
, KB_SET_LINK.OBJECT_CODE
, TO_CHAR(KB_SET_TYPE.SET_TYPE_ID)
, KB_SET_TYPE.NAME
, KB_SET.NAME
, KB_SET_LINK.LINK_TYPE
, 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'
, NULL
, NULL
, KB_SET.SET_ID SOLUTION_ID
, CSL.MEANING OUTCOME
, KB_SET.SET_NUMBER SOLUTION_NUMBER
FROM CS_KB_SET_LINKS KB_SET_LINK
, CS_KB_SET_TYPES_VL KB_SET_TYPE
, CS_KB_SETS_VL KB_SET
, CS_LOOKUPS CSL
WHERE KB_SET_LINK.SET_ID = KB_SET.SET_ID
AND KB_SET.SET_TYPE_ID = KB_SET_TYPE.SET_TYPE_ID
AND CSL.LOOKUP_TYPE(+)='CS_KB_LINK_TYPE'
AND CSL.LOOKUP_CODE(+)=KB_SET_LINK.LINK_TYPE
AND KB_SET_LINK.OBJECT_CODE='SR'
AND KB_SET.SET_ID = DECODE( ( SELECT COUNT( B3.SET_ID )
FROM CS_KB_SETS_B B3
WHERE B3.STATUS = 'OBS'
AND B3.SET_NUMBER = KB_SET.SET_NUMBER )
, 1
, NULL
, DECODE(( SELECT COUNT( B2.SET_ID )
FROM CS_KB_SETS_B B2
WHERE B2.STATUS = 'PUB'
AND B2.SET_NUMBER = KB_SET.SET_NUMBER )
, 1
, ( SELECT B2.SET_ID
FROM CS_KB_SETS_B B2
WHERE B2.STATUS = 'PUB'
AND B2.SET_NUMBER = KB_SET.SET_NUMBER )
, ( SELECT MAX(B2.SET_ID)
FROM CS_KB_SETS_B B2
WHERE B2.SET_NUMBER = KB_SET.SET_NUMBER ) ) )

Columns