# gettbspersonservicelist SQL  (called by getmobconf in both Phase1 and Phase2)

tbssql >"$outfile" <<SQLCMD0
SELECT
    dbo.tPerson.ID AS PersonID, dbo.tPerson.Person,
    dbo.tServiceID.ID, dbo.tServiceID.ServiceID,
    dbo.tCustomer.ShipTo, dbo.tCustomer.FullName,
    dbo.tLocation.ID, dbo.tLocation.Description,
    dbo.tDepartment.ID, dbo.tDepartment.Description,
    dbo.tSupplier.ID, dbo.tSupplier.SupplierName
FROM
    dbo.tPerson
    LEFT OUTER JOIN dbo.tGroup
	    INNER JOIN dbo.tCentre
     INNER JOIN dbo.tServiceID
		    LEFT OUTER JOIN dbo.tLocation
			ON dbo.tLocation.ID = dbo.tServiceID.LocationID
		ON dbo.tCentre.ID = dbo.tServiceID.CustomerCentreID

		    LEFT JOIN tServiceType
		        ON tServiceType.ID = tServiceID.ServiceTypeID
			    LEFT JOIN tSupplier
			 	ON tServiceType.SupplierID = tSupplier.ID

	    INNER JOIN dbo.tDepartment
		ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID
        ON dbo.tGroup.ID = dbo.tDepartment.GroupID
    INNER JOIN dbo.tCustomer
        ON dbo.tGroup.CustomerID = dbo.tCustomer.ID
    ON dbo.tPerson.ID = dbo.tServiceID.PersonID
WHERE
    (dbo.tPerson.Active = 1) AND (dbo.tServiceID.Active = 1)
  AND (NOT (dbo.tServiceID.ServiceID LIKE 'kamco%'))
  AND (NOT (dbo.tServiceID.ServiceID LIKE 'annual%'))
ORDER BY dbo.tPerson.Person