/* Lists all assets in the Database */
SELECT I.groupfld AS [Group],
g.group_descv6 AS [Group Desc],
i.category AS [Category],
c.cat_descv6 AS [Category Desc],
A.product_code AS [Product Code],
I.descriptionv6 AS [Product Description],
A.asset_code AS [Bar Code],
A.description AS [Asset Description],
LO.locn_name AS [Location],
A.stock_number AS [Stock Number],
A.serial_no AS [Serial Number],
A.modelnumber AS [Asset Model Number],
A.purdate AS [Purchase Date],
A.booking_no AS [OUT on Booking],
CASE
WHEN A.idisposaltype = 0 THEN
CASE
WHEN A.servicestatus <> 0 THEN 'Inactive'
ELSE 'Active'
END
WHEN A.idisposaltype = 1 THEN 'Sold'
WHEN A.idisposaltype = 2 THEN 'Lost/Missing'
WHEN A.idisposaltype = 3 THEN 'Stolen'
WHEN A.idisposaltype = 4 THEN 'Written Off'
ELSE '**E**'
END AS [Disposal Type],
A.disdate AS [Disposal Date],
CASE
WHEN A.servicestatus = 1 THEN 'Out for Maintenance'
WHEN A.servicestatus = 2 THEN 'Permanently Out of Service'
WHEN A.servicestatus = 0 THEN
CASE
WHEN A.idisposaltype <> 0 THEN 'Inactive'
ELSE 'In Service'
END
ELSE '** E **'
END AS [Service Status]
FROM tblasset01 AS A
LEFT OUTER JOIN tblinvmas AS I
ON I.product_code = A.product_code
LEFT OUTER JOIN tblcategory AS c
ON c.category_code = i.category
LEFT OUTER JOIN tbllocnlist AS LO
ON LO.locn_number = A.locn
LEFT OUTER JOIN tblgroup AS G
ON G.group_code = I.groupfld
ORDER BY G.seqno,
I.seq_no,
a.stock_number