SELECT dbo.tServiceID.ServiceID, dbo.tServiceID.Service, dbo.tCustomer.ShipTo, dbo.tCustomer.FullName, dbo.tApplication.Description AS Apln, dbo.tLocation.Description AS Location, dbo.tPerson.Person, dbo.tSupplier.SupplierName AS Supplier, dbo.tServiceType.Code, dbo.tServiceType.Description AS ServType, dbo.tElement.ElementCode, dbo.tElement.Description, CASE WHEN (elementcode = '60001' OR elementcode = '60069' OR elementcode = '60068' OR elementcode = '81517' OR elementcode = '60058' OR elementcode = '60032' OR elementcode = '60033' OR elementcode = '81007' OR elementcode = '81040' OR elementcode = '81031' OR elementcode = '60073' OR elementcode = '81516' OR elementcode = '81521' OR elementcode = '60079' OR elementcode = '60047' OR elementcode = '60015' OR elementcode = '81025' OR elementcode = '81515' OR elementcode = '81026' OR elementcode = '81030' OR elementcode = '81038' OR elementcode = '81045' OR elementcode = '81046') THEN 'FIXED' WHEN (elementcode = '60003' OR elementcode = '60016' OR elementcode = '60074' OR elementcode = '81037' OR elementcode = '60077' OR elementcode = '60078' OR elementcode = '81004' OR elementcode = '81013' OR elementcode = '81032' OR elementcode = '81036') THEN 'MOBIL' WHEN (elementcode = '60071' OR elementcode = '81028' OR elementcode = '83501') THEN 'FCALL' WHEN (elementcode = '60037') THEN 'SATEL' END AS Value, dbo.tServiceID.DateLastChanged, dbo.tServiceID.InstallationDate FROM dbo.tServiceID INNER JOIN dbo.tCentre ON dbo.tServiceID.CustomerCentreID = dbo.tCentre.ID INNER JOIN dbo.tServiceType ON dbo.tServiceID.ServiceTypeID = dbo.tServiceType.ID INNER JOIN dbo.tSupplier ON dbo.tServiceType.SupplierID = dbo.tSupplier.ID LEFT OUTER JOIN dbo.tApplication ON dbo.tServiceID.ApplicationID = dbo.tApplication.ID LEFT OUTER JOIN dbo.tPerson ON dbo.tServiceID.PersonID = dbo.tPerson.ID LEFT OUTER JOIN dbo.tLocation ON dbo.tServiceID.LocationID = dbo.tLocation.ID LEFT OUTER JOIN dbo.tElement ON dbo.tServiceType.RevenueElementID = dbo.tElement.ID LEFT OUTER JOIN dbo.tGroup LEFT OUTER JOIN dbo.tCustomer ON dbo.tGroup.CustomerID = dbo.tCustomer.ID RIGHT OUTER JOIN dbo.tDepartment ON dbo.tGroup.ID = dbo.tDepartment.GroupID ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID WHERE (dbo.tSupplier.SupplierName LIKE 'TELSTRA%') AND (dbo.tServiceID.Active = 1) AND (NOT (dbo.tElement.ElementCode IN ('82522', '81047', '81024', '81012', '60067', '33002') ) ) OR (dbo.tSupplier.SupplierName LIKE 'AAPT%') AND (dbo.tServiceID.Active = 1) OR (dbo.tSupplier.SupplierName LIKE 'OPTUS%') AND (dbo.tServiceID.Active = 1) ORDER BY dbo.tServiceID.ServiceID