: !/bin/sh

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

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


# view created by Robyn 18/05/2010
#tbssql >"$outfile" <<SQLCMD0
#select * from vwRD_Bilmax
#order by 1
#SQLCMD0
#
#exit 0
# (added PersonID)
# (added GrpID)
# (added DeptID)
# (added CentreID)

#----------------------------
# SQL for view is...
tbssql >"$outfile" <<SQLCMD0
SELECT dbo.tServiceID.ID, dbo.tServiceID.ServiceID,
 dbo.tCentre.ID AS CustCentID, dbo.tCentre.Description AS CustCent,
 dbo.tDepartment.ID AS CustDeptID, dbo.tDepartment.Description AS CustDept,
 dbo.tGroup.ID AS CustGrpID, dbo.tGroup.Description AS CustGrp,
 dbo.tCustomer.ShipTo, dbo.tCustomer.FullName,
 dbo.tServiceID.Active AS ServiceActive, dbo.tServiceType.Code,
 dbo.tServiceType.Description AS ServType, 
 dbo.tPerson.ID, dbo.tPerson.Person,
 dbo.tLocation.Description AS Location, dbo.tSystem.System,
 dbo.tBatchType.Description AS Batchtype,
 dbo.tServiceID.InstallationDate, dbo.tServiceID.TerminationDate
FROM dbo.tBatchType RIGHT OUTER JOIN
     dbo.tSystem ON dbo.tBatchType.ID = dbo.tSystem.BatchTypeID RIGHT OUTER JOIN
             dbo.tServiceID INNER JOIN
     dbo.tCentre ON dbo.tServiceID.CustomerCentreID = dbo.tCentre.ID INNER JOIN
     dbo.tDepartment ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID INNER JOIN
             dbo.tGroup ON dbo.tDepartment.GroupID = dbo.tGroup.ID INNER JOIN
     dbo.tCustomer ON dbo.tGroup.CustomerID = dbo.tCustomer.ID INNER JOIN
     dbo.tLocation ON dbo.tServiceID.LocationID = dbo.tLocation.ID LEFT OUTER JOIN
     dbo.tPerson ON dbo.tServiceID.PersonID = dbo.tPerson.ID LEFT OUTER JOIN
     dbo.tValidAccountNumber ON dbo.tServiceID.SupplierAccountID = dbo.tValidAccountNumber.ID LEFT OUTER JOIN
     dbo.tServiceType ON dbo.tServiceID.ServiceTypeID = dbo.tServiceType.ID ON dbo.tSystem.ID = dbo.tServiceType.SystemID
WHERE     (dbo.tServiceID.Active = 1)
SQLCMD0