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