# gettbstransactionhierarchy

	# uses the beloow SQL to get a list of PERIOD's to run
	# gettbscust
	#
	# SELECT DISTINCT Period
	# FROM tInvoice, tInvoiceDetail
	# where tInvoiceDetail.InvoiceID = tInvoice.ID
	#   and tInvoice.InvoiceDate >
	# 	    dateadd($IOFFSETDAYS,'$EYYYYMMDD')
  	# and tInvoice.InvoiceDate <=
	#        dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD'))
  	# $EXCLUDEINVBIT
  	# $EXCLUDEBATCHIDBIT
	# ORDER BY Period

# gettrnscusthier does this SQL for each PERIOD
# non - VRT
SELECT distinct
    ${transactiontable}.ServiceID AS ServiceIDID, tServiceID.ServiceID,
    tCentre.ID AS CentreID, tCentre.Description AS CentreDesc,
    tDepartment.ID As DeptID, tDepartment.Description AS DeptDesc,
    tGroup.ID AS GrpID, tGroup.Description AS GrpDesc, 
    tCustomer.ID AS CustID, tCustomer.ShipTo, tCustomer.FullName,
    tCustomer.VNumber
, 1, 1, 1, 1, 1
FROM
 ${transactiontable}
 LEFT JOIN tStatus
        ON ${transactiontable}.StatusID = tStatus.ID
 LEFT JOIN tServiceID
	ON ${transactiontable}.ServiceID = tServiceID.ID
 LEFT JOIN ( tCentre
        LEFT JOIN tDepartment
                ON tCentre.DepartmentID = tDepartment.ID
        ) ON ${transactiontable}.CustomerCentreID = tCentre.ID
 LEFT JOIN tCustomer
        ON ${transactiontable}.CustomerID = tCustomer.ID
 LEFT JOIN tGroup
	ON ${transactiontable}.GroupID = tGroup.ID
 INNER JOIN tBatch
	ON tBatch.ID = ${transactiontable}.BatchID
 INNER JOIN ( tInvoiceDetail
                INNER JOIN tInvoice
                        ON tInvoiceDetail.InvoiceID = tInvoice.ID
           )
         ) ON ${transactiontable}.InvoiceDetailID = tInvoiceDetail.ID
WHERE tBatch.BillingPeriod = '$tbsbillingperiod' $BATCHIDBIT
 and ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo <> "VRT"

          AND ${transactiontable}.RevenueFlag = 1
          AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')
          AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD'))
          $EXCLUDEINVBIT
 
# for VRT
SELECT distinct
    ${transactiontable}.ServiceID AS ServiceIDID, tServiceID.ServiceID,
    tCentre.ID AS CentreID, tCentre.Description AS CentreDesc,
    tDepartment.ID As DeptID, tDepartment.Description AS DeptDesc,
    tGroup.ID AS GrpID, tGroup.Description AS GrpDesc, 
    tCustomer.ID AS CustID, tCustomer.ShipTo, tCustomer.FullName,
    tCustomer.VNumber
, 1, 1, 1, 1, 1
FROM
 ${transactiontable}
 LEFT JOIN tStatus
        ON ${transactiontable}.StatusID = tStatus.ID
 LEFT JOIN tServiceID
	ON ${transactiontable}.ServiceID = tServiceID.ID
 LEFT JOIN ( tCentre
	LEFT JOIN tDepartment
		ON tCentre.DepartmentID = tDepartment.ID
	  ) ON ${transactiontable}.CustomerCentreID = tCentre.ID
 LEFT JOIN tCustomer
        ON ${transactiontable}.CustomerID = tCustomer.ID
 LEFT JOIN tGroup
	ON ${transactiontable}.GroupID = tGroup.ID
 LEFT JOIN tBatch
	ON ${transactiontable}.BatchID = tBatch.ID
 LEFT JOIN ( tInvoiceDetail
	INNER JOIN tInvoice
		ON tInvoiceDetail.InvoiceID = tInvoice.ID
 ) ON ${transactiontable}.InvoiceDetailID = tInvoiceDetail.ID
WHERE tBatch.BillingPeriod = '$curtbsbillingperiod' $BATCHIDBIT
 and ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo = "VRT"
          AND ${transactiontable}.RevenueFlag = 1
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT