: !/bin/sh

if [ -n "$1" ]; then
	outfile="$1"
else
	outfile="tbspersonservicelist.unl"
fi

# Testing
cp tbspersonservicelist.unl  "$outfile"
exit 0


# view created by Robyn 08/0562010
tbssql >"$outfile" <<SQLCMD0
select * from vwRDBilmaxPersonServiceList
order by 1
SQLCMD0

exit 0

#----------------------------
# SQL for view is...
tbssql >"$outfile" <<SQLCMD0
SELECT
    dbo.tPerson.ID AS PersonID, dbo.tPerson.Person,
    dbo.tServiceID.ID, dbo.tServiceID.ServiceID,
    dbo.tCustomer.ShipTo, dbo.tCustomer.FullName
FROM
    dbo.tPerson
    LEFT OUTER JOIN dbo.tGroup
	    INNER JOIN dbo.tCentre
	    INNER JOIN dbo.tServiceID
		    ON dbo.tCentre.ID = dbo.tServiceID.CustomerCentreID
	    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
SQLCMD0