-- First part of select is for individual files on the attach tab (not folders)
SELECT
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000)
then (select change.change_number from agile.change where agile.ATTACHMENT_MAP.PARENT_ID=change.id)
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select item.item_number from agile.item where agile.ATTACHMENT_MAP.PARENT_ID=item.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1442)
then (select manufacturers.name from agile.manufacturers where agile.ATTACHMENT_MAP.PARENT_ID=manufacturers.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1483)
then (select manu_parts.part_number from agile.manu_parts where agile.ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id)
else
null end as Obj_Number,
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select REV.REV_NUMBER from agile.REV where REV.ITEM = agile.ATTACHMENT_MAP.PARENT_ID AND REV.CHANGE = ATTACHMENT_MAP.PARENT_ID2)
WHEN ATTACHMENT_MAP.PARENT_CLASS in (1483)
THEN ( select name from agile.manufacturers where id =
(select manu_id from agile.manu_parts where agile.ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id ) )
ELSE
NULL END AS REV_NBR,
NODETABLE.DESCRIPTION as Obj_Class,
ATTACHMENT.ATTACHMENT_NUMBER,
VERSION_FILE_MAP.DESCRIPTION,
ATTACHMENT.CREATE_DATE AS Att_Created_Date,
ATTACHMENT.ID AS ATT_ID,
VERSION.ID AS VER_ID,
VERSION.VERSION_NUM,
ATTACHMENT_MAP.LATEST_VSN,
FILES.FILENAME,
FILES.FILE_TYPE,
FILES.FILE_SIZE,
FILES.CREATED AS FILE_CREATED,
FILES.LAST_UPD AS FILE_LAST_UPD,
FILES.ID as FILE_ID,
FILE_INFO.IFS_FILEPATH,
FILE_INFO.HFS_FILEPATH
FROM agile.ATTACHMENT
INNER JOIN agile.VERSION
ON VERSION.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.VERSION_FILE_MAP
ON VERSION_FILE_MAP.VERSION_ID = VERSION.ID
INNER JOIN agile.FILES
ON VERSION_FILE_MAP.FILE_ID = FILES.ID
INNER JOIN agile.ATTACHMENT_MAP
ON ATTACHMENT_MAP.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.NODETABLE
ON NODETABLE.ID = ATTACHMENT_MAP.PARENT_CLASS
LEFT OUTER JOIN agile.FILE_INFO
ON FILE_INFO.FILE_ID = FILES.ID
WHERE ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000,9000,10000,1442,1483)
AND VERSION.ID = ATTACHMENT_MAP.LATEST_VSN
AND ATTACHMENT.DELETE_FLAG IS NULL
AND ATTACHMENT_MAP.FILE_ID > 0 -- Include this line for when it is one specific file on the attach tab, not a whole folder
AND VERSION_FILE_MAP.FILE_ID = ATTACHMENT_MAP.FILE_ID -- Include this line for when it is one specific file on the attach tab, not a whole folder
UNION ALL
-- Second part of select is for whole folders that exist on attach tab
SELECT
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000)
then (select change.change_number from agile.change where ATTACHMENT_MAP.PARENT_ID=change.id)
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select item.item_number from agile.item where ATTACHMENT_MAP.PARENT_ID=item.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1442)
then (select manufacturers.name from agile.manufacturers where ATTACHMENT_MAP.PARENT_ID=manufacturers.id)
when ATTACHMENT_MAP.PARENT_CLASS in (1483)
then (select manu_parts.part_number from agile.manu_parts where ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id)
else
null end as Obj_Number,
CASE
when ATTACHMENT_MAP.PARENT_CLASS in (9000,10000)
then (select REV.REV_NUMBER from agile.REV where REV.ITEM = ATTACHMENT_MAP.PARENT_ID AND REV.CHANGE = ATTACHMENT_MAP.PARENT_ID2)
WHEN ATTACHMENT_MAP.PARENT_CLASS in (1483)
THEN ( select name from agile.manufacturers where id =
(select manu_id from agile.manu_parts where ATTACHMENT_MAP.PARENT_ID=MANU_PARTS.id ) )
ELSE
NULL END AS REV_NBR,
NODETABLE.DESCRIPTION as Obj_Class,
ATTACHMENT.ATTACHMENT_NUMBER,
VERSION_FILE_MAP.DESCRIPTION,
ATTACHMENT.CREATE_DATE AS Att_Created_Date,
ATTACHMENT.ID AS ATT_ID,
VERSION.ID AS VER_ID,
VERSION.VERSION_NUM,
ATTACHMENT_MAP.LATEST_VSN,
FILES.FILENAME,
FILES.FILE_TYPE,
FILES.FILE_SIZE,
FILES.CREATED AS FILE_CREATED,
FILES.LAST_UPD AS FILE_LAST_UPD,
FILES.ID as FILE_ID,
FILE_INFO.IFS_FILEPATH,
FILE_INFO.HFS_FILEPATH
FROM agile.ATTACHMENT
INNER JOIN agile.VERSION
ON VERSION.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.VERSION_FILE_MAP
ON VERSION_FILE_MAP.VERSION_ID = VERSION.ID
INNER JOIN agile.FILES
ON VERSION_FILE_MAP.FILE_ID = FILES.ID
INNER JOIN agile.ATTACHMENT_MAP
ON ATTACHMENT_MAP.ATTACH_ID = ATTACHMENT.ID
INNER JOIN agile.NODETABLE
ON NODETABLE.ID = ATTACHMENT_MAP.PARENT_CLASS
LEFT OUTER JOIN agile.FILE_INFO
ON FILE_INFO.FILE_ID = FILES.ID
WHERE ATTACHMENT_MAP.PARENT_CLASS in (8000,6000,7000,1450,11000,9000,10000,1442,1483)
AND VERSION.ID = ATTACHMENT_MAP.LATEST_VSN
AND ATTACHMENT.DELETE_FLAG IS NULL
AND ATTACHMENT_MAP.file_id = 0 -- Include this line for when it IS a whole folder on the attach tab