SELECT     TOP 100 PERCENT
 tBatch.ID,
 tBatch.BillingPeriod,
 tBatchType.ID AS Expr1,
 tBatchType.SupplierID,
 tBatchType.Description AS BatchType,
 tSupplier.SupplierName,
 tServiceID.ServiceID,
 tServiceID.Service,
 tServiceType.Code AS ServiceTypeCode,
 tServiceType.Description AS ServiceType,
 tInvoice.InvoiceDate,
 tFinalisedTransaction201004.TxnDate,
 tReportGroup.ID AS ReportGrpID,
 tReportGroup.GroupDesc AS ReportGrpDesc,
 tInvoice.PlatinumFileName,
 tInvoice.PlatinumInvoiceNo,
 tCustomer.ShipTo,
 tCustomer.FullName,
 tCentre.Description AS CustCentre,
 tActivity.Description AS CustActivity,
 tElement.ElementCode AS EleRevServTypeCode,
 tElement.Description AS EleRevServTypeDesc,
 tElement_1.ElementCode AS CustEleCode,
 tElement_1.Description AS CustEleDesc,
 tElement_2.ElementCode AS EleAdmCode,
 tElement_2.Description AS EleAdmDesc,
 tSubledger.Description AS CustSubLedger,
 tDepartment.Description AS CustDept,
 tLocation.Description AS Location,
 tPerson.Person,
 tApplication.ID AS Expr2,
 tApplication.Description,
 tOrigin.Description AS Origin,
 tDestination.Description AS Destination,
 tRateDescription.Description AS RateDescription,
 tRate.Cost,
 tTransactionType.Description AS TransactionType,
 tTransactionGroup.Description AS TransactionGroup,
 tDialledNumber.Description AS DialledNumber,
 tFinalisedTransaction201004.Duration,
 tFinalisedTransaction201004.AmountExGST,
 tBatchType.Var01Description,
 tFinalisedTransaction201004.Var01,
 tBatchType.Var02Description,
 tFinalisedTransaction201004.Var02,
 tBatchType.Var03Description,
 tFinalisedTransaction201004.Var03,
 tBatchType.Var04Description,
 tFinalisedTransaction201004.Var04,
 tBatchType.Var05Description,
 tFinalisedTransaction201004.Var05,
 tBatchType.Var06Description,
 tFinalisedTransaction201004.Var06,
 tBatchType.Var07Description,
 tFinalisedTransaction201004.Var07,
 tBatchType.Var08Description,
 tFinalisedTransaction201004.Var08,
 tBatchType.Var09Description,
 tFinalisedTransaction201004.Var09,
 tBatchType.Var10Description,
 tFinalisedTransaction201004.Var10,
 tBatchType.Var11Description,
 tFinalisedTransaction201004.Var11,
 tBatchType.Var12Description,
 tFinalisedTransaction201004.Var12,
 tBatchType.Var13Description,
 tFinalisedTransaction201004.Var13,
 tBatchType.Var14Description,
 tFinalisedTransaction201004.Var14,
 tBatchType.Var15Description,
 tFinalisedTransaction201004.Var15

FROM tFinalisedTransaction201004
 INNER JOIN tBatch
	LEFT OUTER JOIN tBatchType
		LEFT OUTER JOIN tReportGroup
		 	ON tBatchType.ReportGroupID = tReportGroup.ID
	 	ON tBatch.BatchTypeID = tBatchType.ID
 	ON tFinalisedTransaction201004.BatchID = tBatch.ID
 LEFT OUTER JOIN tServiceID
	LEFT OUTER JOIN tApplication
		ON tServiceID.ApplicationID = tApplication.ID
 	ON tFinalisedTransaction201004.ServiceID = tServiceID.ID
 LEFT OUTER JOIN tInvoiceDetail
	INNER JOIN tInvoice
		ON tInvoiceDetail.InvoiceID = tInvoice.ID
 	ON tFinalisedTransaction201004.InvoiceDetailID = tInvoiceDetail.ID
 LEFT OUTER JOIN tDialledNumber
 	ON tFinalisedTransaction201004.DialledNumberID = tDialledNumber.ID
 LEFT OUTER JOIN tRateDescription
 	ON tFinalisedTransaction201004.RateDescriptionID = tRateDescription.ID
 LEFT OUTER JOIN tDestination
 	ON tFinalisedTransaction201004.DestinationID = tDestination.ID
 LEFT OUTER JOIN tOrigin
 	ON tFinalisedTransaction201004.OriginID = tOrigin.ID
 LEFT OUTER JOIN tElement
 	ON tFinalisedTransaction201004.ElementID = tElement.ID
 LEFT OUTER JOIN tElement tElement_1
 	ON tFinalisedTransaction201004.CustomerElementID = tElement_1.ID
 LEFT OUTER JOIN ( tServiceType
	LEFT OUTER JOIN tSupplier
		ON tServiceType.SupplierID = tSupplier.ID
	LEFT OUTER JOIN tElement tElement_2
		ON tServiceType.RevenueElementID = tElement_2.ID
	LEFT OUTER JOIN tRate
		ON tServiceType.ID = tRate.ServiceTypeID
 	) ON tFinalisedTransaction201004.ServiceTypeID = tServiceType.ID
 LEFT OUTER JOIN ( tTransactionType
	LEFT OUTER JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
 	) ON tFinalisedTransaction201004.TransactionTypeID = tTransactionType.ID
 LEFT OUTER JOIN tCustomer
 	ON tFinalisedTransaction201004.CustomerID = tCustomer.ID
 LEFT OUTER JOIN tCentre
 	ON tFinalisedTransaction201004.CustomerCentreID = tCentre.ID
 LEFT OUTER JOIN tActivity
 	ON tFinalisedTransaction201004.CustomerActivityID = tActivity.ID
 LEFT OUTER JOIN tSubledger
 	ON tFinalisedTransaction201004.CustomerSubledgerID = tSubledger.ID
 LEFT OUTER JOIN tDepartment
 	ON tFinalisedTransaction201004.DepartmentID = tDepartment.ID
 LEFT OUTER JOIN tLocation
 	ON tFinalisedTransaction201004.LocationID = tLocation.ID
 LEFT OUTER JOIN tPerson
 	ON tFinalisedTransaction201004.PersonID = tPerson.ID

WHERE (tBatch.BillingPeriod = '2010/04')
  AND (tFinalisedTransaction201004.RevenueFlag = 1)
  AND (tBatchType.Active = 1)
  AND (tInvoice.InvoiceDate > DATEADD(dd, 9, '20100430'))
  AND (tInvoice.InvoiceDate <= DATEADD(mm, 1, DATEADD(dd, 9, '20100430')))
  AND (tBatchType.ID <> 199) AND (tServiceID.ServiceID LIKE 'Jobs apb %')

ORDER BY
 tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, tBatch.ID, tServiceID.ServiceID,
 tFinalisedTransaction201004.Var01, tReportGroup.ID