SELECT
 tElement.Description AS Element,
 tServiceID.ServiceID AS ServiceIDDesc,
 tInvoiceDetail.BatchID AS Batch,
 tBatchType.SupplierDisplayedName AS Supplier,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS Centre,
 tActivity.Description AS Activity,
 tElement_1.Description AS Element1,
 tSubledger.Description AS SubLedger,
 tFinalisedTransaction201102.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
--# SUM(tFinalisedTransaction201102.AmountExGST) AS ExGST,
--# SUM(tFinalisedTransaction201102.GSTAmount) AS GST,
--# SUM(tFinalisedTransaction201102.AmountIncGST) AS IncGST,
tFinalisedTransaction201102.AmountExGST AS ExGST,
tFinalisedTransaction201102.GSTAmount AS GST,
tFinalisedTransaction201102.AmountIncGST AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201102.Var01,
 tFinalisedTransaction201102.Var02,
 tFinalisedTransaction201102.Var03,
 tFinalisedTransaction201102.Var04,
 tFinalisedTransaction201102.Var05,
 tFinalisedTransaction201102.Var06,
 tFinalisedTransaction201102.Var07,
 tFinalisedTransaction201102.Var08,
 tFinalisedTransaction201102.Var09,
 tFinalisedTransaction201102.Var10,
 tFinalisedTransaction201102.Var11,
 tFinalisedTransaction201102.Var12,
 tFinalisedTransaction201102.Var13,
 tFinalisedTransaction201102.Var14,
 tFinalisedTransaction201102.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201102
--# INNER JOIN ( tBatch
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201102.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201102.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		LEFT JOIN tInvoice
			ON tInvoiceDetail.InvoiceID = tInvoice.ID
--#			   and tInvoice.ID NOT IN (
--#				SELECT distinct tInvoice.CreditNoteInvID
--#				FROM tInvoice
--#				WHERE tInvoice.CreditNoteInvID is not null
--#			)
	) ON tFinalisedTransaction201102.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201102.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON tFinalisedTransaction201102.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON tFinalisedTransaction201102.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON tFinalisedTransaction201102.ServiceTypeID = tServiceType.ID

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201102.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201102.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201102.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201102.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201102.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201102.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201102.PersonID = tPerson.ID
WHERE
       tBatch.BillingPeriod = '2011/02' and
	 tInvoice.PlatinumInvoiceNo = 'INV010505'
       AND tFinalisedTransaction201102.RevenueFlag = 1
        AND tCustomer.ShipTo = "KAM"
--#        AND tBatchType.Active = 1
          AND tInvoice.InvoiceDate > dateadd(dd,9,'20110531')
          AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,9,'20110531'))
--#GROUP BY
--# tElement.Description, tServiceID.ServiceID, tServiceID.Service,
--# tInvoiceDetail.BatchID,
--# tBatchType.SupplierDisplayedName, tLocation.Description, 
--# tPerson.Person, tCentre.Description, tServiceType.Description,
--# tInvoiceDetail.Description, tSubledger.Description, 
--# tInvoiceDetail.Period, tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo,
--# tFinalisedTransaction201102.Var01, tFinalisedTransaction201102.Var02,
--# tFinalisedTransaction201102.Var03, tFinalisedTransaction201102.Var04, 
--# tFinalisedTransaction201102.Var05, tFinalisedTransaction201102.Var06,
--# tFinalisedTransaction201102.Var07, tFinalisedTransaction201102.Var08,
--# tFinalisedTransaction201102.Var09, tFinalisedTransaction201102.Var10,
--# tFinalisedTransaction201102.Var11, tFinalisedTransaction201102.Var12,
--# tFinalisedTransaction201102.Var13, tFinalisedTransaction201102.Var14,
--# tFinalisedTransaction201102.Var15,
--# tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
--# tDepartment.Description, tFinalisedTransaction201102.PayType,
--# tActivity.Description, tElement_1.Description
--#ORDER BY
--# tCentre.Description