: !/bin/sh

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

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

#----------------------------
# SQL for view is...
if [ -n "$nopersonal" ]; then	# include personal info
	tbssql >"$outfile" <<SQLCMD0
SELECT
    dbo.tServiceID.ID, dbo.tServiceID.ServiceID,
    dbo.tCentre.Description AS CustCent,
    dbo.tDepartment.Description AS CustDept,
    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
else				# without personal info
	tbssql >"$outfile" <<SQLCMD1
SELECT
    dbo.tServiceID.ID, dbo.tServiceID.ServiceID,
    dbo.tCentre.Description AS CustCent,
    dbo.tDepartment.Description AS CustDept,
    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.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.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)
SQLCMD1
fi

exit 0