VIEW: APPS.CS_SR_KB_NOTE_LINKS_V

Source

View Text - Preformatted

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 ) ) )

View Text - HTML Formatted

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 ) ) )