View: OKC_KOL_ATTACHMENTS_V

View Text

SELECT ATT.ATTACHED_DOCUMENT_ID ATTACHED_DOCUMENT_ID
, ATT.DOCUMENT_ID DOCUMENT_ID
, DOC.CATEGORY_ID CATEGORY_ID
, DOC.DATATYPE_ID DATATYPE_ID
, DOC_TL.MEDIA_ID
, ATT.SEQ_NUM SEQ_NUM
, DECODE(DTYP.NAME
, 'FILE'
, SUBSTR(DOC_TL.FILE_NAME
, 1
, (DECODE(INSTR(DOC_TL.FILE_NAME
, '.'
, -1
, 1)
, '0'
, LENGTH(DOC_TL.FILE_NAME)
, INSTR(DOC_TL.FILE_NAME
, '.'
, -1
, 1)-1)))
, 'WEB_PAGE'
, NVL(SHTEXT.SHORT_TEXT
, 'INTERNAL')
, 'INTERNAL') ATTACHMENT_NAME
, DECODE(DTYP.NAME
, 'FILE'
, SUBSTR(DOC_TL.FILE_NAME
, (DECODE(INSTR(DOC_TL.FILE_NAME
, '.'
, -1
, 1)
, '0'
, LENGTH(DOC_TL.FILE_NAME)+1
, INSTR(DOC_TL.FILE_NAME
, '.'
, -1
, 1)))
, LENGTH(DOC_TL.FILE_NAME))
, 'WEB_PAGE'
, DTYP.USER_NAME
, ' ') FILE_TYPE
, CAT_TL.USER_NAME ATTACHMENT_TYPE
, DOC_TL.LAST_UPDATE_DATE LAST_DOC_UPDATE
, DTYP.NAME FND_DOCUMENT_TYPE
, NVL(DBMS_LOB.GETLENGTH(LOB.FILE_DATA)/1024
, 0) ATTACHMENT_SIZE
, DECODE(DTYP.NAME
, 'WEB_PAGE'
, ' '
, 'FILE'
, TO_CHAR(GREATEST (ROUND (NVL(DBMS_LOB.GETLENGTH(LOB.FILE_DATA)
, 0)/1024)
, 1)
, '99G99G999')||' KB'
, ' ') ATTACHMENT_SIZE_FORMATTED
, DOC_TL.DESCRIPTION DESCRIPTION
, OKC_KOL_ATT_UTIL_PUB.GET_URL(DOC_TL.FILE_NAME
, DTYP.NAME
, ATT.PK3_VALUE) URL
, ATT.ENTITY_NAME ENTITY_NAME
, ATT.PK1_VALUE PK1_VALUE
, ATT.PK2_VALUE PK2_VALUE
, ATT.PK3_VALUE PK3_VALUE
, ATT.PK4_VALUE PK4_VALUE
, ATT.PK5_VALUE PK5_VALUE
, DOC_TL.LANGUAGE LANGUAGE
, ATT.CREATION_DATE CREATION_DATE
, ATT.CREATED_BY CREATED_BY
, ATT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ATT.LAST_UPDATED_BY LAST_UPDATED_BY
, ATT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM FND_DOCUMENT_CATEGORIES_TL CAT_TL
, FND_DOCUMENT_DATATYPES DTYP
, FND_LOBS LOB
, FND_DOCUMENTS DOC
, FND_DOCUMENTS_SHORT_TEXT SHTEXT
, FND_ATTACHED_DOCUMENTS ATT
, FND_DOCUMENTS_TL DOC_TL
WHERE DOC_TL.DOCUMENT_ID = DOC.DOCUMENT_ID
AND DOC_TL.DOCUMENT_ID = ATT.DOCUMENT_ID
AND DOC.CATEGORY_ID = CAT_TL.CATEGORY_ID
AND CAT_TL.LANGUAGE = USERENV('LANG')
AND DOC.DATATYPE_ID = DTYP.DATATYPE_ID
AND DTYP.DATATYPE_ID IN (5
, 6)
AND DOC_TL.MEDIA_ID = LOB.FILE_ID(+)
AND DOC_TL.MEDIA_ID = SHTEXT.MEDIA_ID(+)
AND DOC_TL.LANGUAGE = USERENV('LANG')
AND DTYP.LANGUAGE = USERENV('LANG')
AND LOB.LANGUAGE(+) = USERENV('LANG')
AND ATT.ENTITY_NAME LIKE 'OKC\_K\_HEADERS\_%' ESCAPE '\'
AND (ATT.PK2_VALUE IS NULL OR OKC_KOL_ATT_UTIL_PUB.GET_MAJOR_VERSION(ATT.PK1_VALUE)=ATT.PK2_VALUE)

Columns