VIEW: APPS.CS_SR_CONTRACT_NOTES_V

Source

View Text - Preformatted

SELECT INC.INCIDENT_ID, 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 , NOTE.CREATED_BY, NOTE.LAST_UPDATE_DATE, NOTE.LAST_UPDATED_BY, NOTE.LAST_UPDATE_LOGIN, 'JTF_CONTRACT_NOTE' RECORD_SOURCE_TYPE, NOTE.NOTE_STATUS, LKUP.MEANING NOTE_STATUS_MEANING, INC.CONTRACT_SERVICE_ID OBJ_ID, INC.CONTRACT_NUMBER OBJ_NUMBER, FND.USER_NAME, CSL.MEANING || ' ' || INC.CONTRACT_NUMBER SOURCE, DECODE (DBMS_LOB.GETLENGTH(NOTE_TL.NOTES_DETAIL), 0, 'N' , 'Y') IS_DETAIL FROM JTF_NOTES_B NOTE, JTF_NOTES_TL NOTE_TL, FND_LOOKUPS LKCMTTYPE, FND_LOOKUPS LKUP, CS_INCIDENTS_ALL_B INC, FND_USER FND, CS_LOOKUPS CSL 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 AND NOTE.SOURCE_OBJECT_ID = INC.CONTRACT_SERVICE_ID AND NOTE.SOURCE_OBJECT_CODE = 'OKS_COV_NOTE' AND FND.USER_ID = NOTE.CREATED_BY AND CSL.LOOKUP_TYPE = 'CS_SR_SOURCE_TYPE' AND CSL.LOOKUP_CODE = 'CONTRACT'

View Text - HTML Formatted

SELECT INC.INCIDENT_ID
, 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
, NOTE.CREATED_BY
, NOTE.LAST_UPDATE_DATE
, NOTE.LAST_UPDATED_BY
, NOTE.LAST_UPDATE_LOGIN
, 'JTF_CONTRACT_NOTE' RECORD_SOURCE_TYPE
, NOTE.NOTE_STATUS
, LKUP.MEANING NOTE_STATUS_MEANING
, INC.CONTRACT_SERVICE_ID OBJ_ID
, INC.CONTRACT_NUMBER OBJ_NUMBER
, FND.USER_NAME
, CSL.MEANING || ' ' || INC.CONTRACT_NUMBER SOURCE
, DECODE (DBMS_LOB.GETLENGTH(NOTE_TL.NOTES_DETAIL)
, 0
, 'N'
, 'Y') IS_DETAIL
FROM JTF_NOTES_B NOTE
, JTF_NOTES_TL NOTE_TL
, FND_LOOKUPS LKCMTTYPE
, FND_LOOKUPS LKUP
, CS_INCIDENTS_ALL_B INC
, FND_USER FND
, CS_LOOKUPS CSL
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
AND NOTE.SOURCE_OBJECT_ID = INC.CONTRACT_SERVICE_ID
AND NOTE.SOURCE_OBJECT_CODE = 'OKS_COV_NOTE'
AND FND.USER_ID = NOTE.CREATED_BY
AND CSL.LOOKUP_TYPE = 'CS_SR_SOURCE_TYPE'
AND CSL.LOOKUP_CODE = 'CONTRACT'