Document Attachment Paths.sql

-- Example to return PDF attachment file path and file name
-- These values can then be passed to Web Service to download the actual attachment
SELECT	A.DOCWORK_ID AS ID,
		D.ETQ$NUMBER AS ETQNumber,
		'/' + REPLACE(A.FILE_PATH, A.FILE_NAME,'') AS FilePath,
		A.FILE_NAME AS FileName,
		PS.DISPLAY_NAME AS CurrentPhase,
		CAST(D.ETQ$EFFECTIVE_DATE AS DATE) AS EffectiveDate
FROM DOCWORK.DOCWORK_DOCUMENT D
JOIN ENGINE.PHASE_SETTINGS PS ON D.ETQ$CURRENT_PHASE=PS.PHASE_ID
JOIN ENGINE.PHASE_TYPES PT ON PS.PHASE_TYPE=PT.PHASE_TYPE
JOIN DOCWORK.ATTACHMENT A ON D.DOCWORK_ID=A.DOCWORK_ID
WHERE PT.PHASE_TYPE=1 --Completed
  AND A.FILE_NAME LIKE '%.PDF'