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,
 tFinalisedTransaction201003.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201003.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201003.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201003.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201003.Var01,
 tFinalisedTransaction201003.Var02,
 tFinalisedTransaction201003.Var03,
 tFinalisedTransaction201003.Var04,
 tFinalisedTransaction201003.Var05,
 tFinalisedTransaction201003.Var06,
 tFinalisedTransaction201003.Var07,
 tFinalisedTransaction201003.Var08,
 tFinalisedTransaction201003.Var09,
 tFinalisedTransaction201003.Var10,
 tFinalisedTransaction201003.Var11,
 tFinalisedTransaction201003.Var12,
 tFinalisedTransaction201003.Var13,
 tFinalisedTransaction201003.Var14,
 tFinalisedTransaction201003.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201003
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201003.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201003.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201003.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201003.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201003.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201003.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201003.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201003.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201003.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201003.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201003.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/03'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201003.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201003.Var01, tFinalisedTransaction201003.Var02,
 tFinalisedTransaction201003.Var03, tFinalisedTransaction201003.Var04, 
 tFinalisedTransaction201003.Var05, tFinalisedTransaction201003.Var06,
 tFinalisedTransaction201003.Var07, tFinalisedTransaction201003.Var08,
 tFinalisedTransaction201003.Var09, tFinalisedTransaction201003.Var10,
 tFinalisedTransaction201003.Var11, tFinalisedTransaction201003.Var12,
 tFinalisedTransaction201003.Var13, tFinalisedTransaction201003.Var14,
 tFinalisedTransaction201003.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201003.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201004.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201004.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201004.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201004.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201004.Var01,
 tFinalisedTransaction201004.Var02,
 tFinalisedTransaction201004.Var03,
 tFinalisedTransaction201004.Var04,
 tFinalisedTransaction201004.Var05,
 tFinalisedTransaction201004.Var06,
 tFinalisedTransaction201004.Var07,
 tFinalisedTransaction201004.Var08,
 tFinalisedTransaction201004.Var09,
 tFinalisedTransaction201004.Var10,
 tFinalisedTransaction201004.Var11,
 tFinalisedTransaction201004.Var12,
 tFinalisedTransaction201004.Var13,
 tFinalisedTransaction201004.Var14,
 tFinalisedTransaction201004.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201004
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201004.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201004.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201004.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201004.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201004.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201004.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201004.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201004.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201004.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201004.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201004.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/04'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201004.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201004.Var01, tFinalisedTransaction201004.Var02,
 tFinalisedTransaction201004.Var03, tFinalisedTransaction201004.Var04, 
 tFinalisedTransaction201004.Var05, tFinalisedTransaction201004.Var06,
 tFinalisedTransaction201004.Var07, tFinalisedTransaction201004.Var08,
 tFinalisedTransaction201004.Var09, tFinalisedTransaction201004.Var10,
 tFinalisedTransaction201004.Var11, tFinalisedTransaction201004.Var12,
 tFinalisedTransaction201004.Var13, tFinalisedTransaction201004.Var14,
 tFinalisedTransaction201004.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201004.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201005.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201005.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201005.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201005.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201005.Var01,
 tFinalisedTransaction201005.Var02,
 tFinalisedTransaction201005.Var03,
 tFinalisedTransaction201005.Var04,
 tFinalisedTransaction201005.Var05,
 tFinalisedTransaction201005.Var06,
 tFinalisedTransaction201005.Var07,
 tFinalisedTransaction201005.Var08,
 tFinalisedTransaction201005.Var09,
 tFinalisedTransaction201005.Var10,
 tFinalisedTransaction201005.Var11,
 tFinalisedTransaction201005.Var12,
 tFinalisedTransaction201005.Var13,
 tFinalisedTransaction201005.Var14,
 tFinalisedTransaction201005.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201005
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201005.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201005.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201005.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201005.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201005.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201005.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201005.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201005.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201005.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201005.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201005.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/05'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201005.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201005.Var01, tFinalisedTransaction201005.Var02,
 tFinalisedTransaction201005.Var03, tFinalisedTransaction201005.Var04, 
 tFinalisedTransaction201005.Var05, tFinalisedTransaction201005.Var06,
 tFinalisedTransaction201005.Var07, tFinalisedTransaction201005.Var08,
 tFinalisedTransaction201005.Var09, tFinalisedTransaction201005.Var10,
 tFinalisedTransaction201005.Var11, tFinalisedTransaction201005.Var12,
 tFinalisedTransaction201005.Var13, tFinalisedTransaction201005.Var14,
 tFinalisedTransaction201005.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201005.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201006.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201006.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201006.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201006.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201006.Var01,
 tFinalisedTransaction201006.Var02,
 tFinalisedTransaction201006.Var03,
 tFinalisedTransaction201006.Var04,
 tFinalisedTransaction201006.Var05,
 tFinalisedTransaction201006.Var06,
 tFinalisedTransaction201006.Var07,
 tFinalisedTransaction201006.Var08,
 tFinalisedTransaction201006.Var09,
 tFinalisedTransaction201006.Var10,
 tFinalisedTransaction201006.Var11,
 tFinalisedTransaction201006.Var12,
 tFinalisedTransaction201006.Var13,
 tFinalisedTransaction201006.Var14,
 tFinalisedTransaction201006.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201006
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201006.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201006.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201006.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201006.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201006.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201006.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201006.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201006.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201006.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201006.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201006.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/06'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201006.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201006.Var01, tFinalisedTransaction201006.Var02,
 tFinalisedTransaction201006.Var03, tFinalisedTransaction201006.Var04, 
 tFinalisedTransaction201006.Var05, tFinalisedTransaction201006.Var06,
 tFinalisedTransaction201006.Var07, tFinalisedTransaction201006.Var08,
 tFinalisedTransaction201006.Var09, tFinalisedTransaction201006.Var10,
 tFinalisedTransaction201006.Var11, tFinalisedTransaction201006.Var12,
 tFinalisedTransaction201006.Var13, tFinalisedTransaction201006.Var14,
 tFinalisedTransaction201006.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201006.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201007.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201007.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201007.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201007.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201007.Var01,
 tFinalisedTransaction201007.Var02,
 tFinalisedTransaction201007.Var03,
 tFinalisedTransaction201007.Var04,
 tFinalisedTransaction201007.Var05,
 tFinalisedTransaction201007.Var06,
 tFinalisedTransaction201007.Var07,
 tFinalisedTransaction201007.Var08,
 tFinalisedTransaction201007.Var09,
 tFinalisedTransaction201007.Var10,
 tFinalisedTransaction201007.Var11,
 tFinalisedTransaction201007.Var12,
 tFinalisedTransaction201007.Var13,
 tFinalisedTransaction201007.Var14,
 tFinalisedTransaction201007.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201007
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201007.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201007.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201007.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201007.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201007.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201007.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201007.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201007.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201007.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201007.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201007.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/07'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201007.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201007.Var01, tFinalisedTransaction201007.Var02,
 tFinalisedTransaction201007.Var03, tFinalisedTransaction201007.Var04, 
 tFinalisedTransaction201007.Var05, tFinalisedTransaction201007.Var06,
 tFinalisedTransaction201007.Var07, tFinalisedTransaction201007.Var08,
 tFinalisedTransaction201007.Var09, tFinalisedTransaction201007.Var10,
 tFinalisedTransaction201007.Var11, tFinalisedTransaction201007.Var12,
 tFinalisedTransaction201007.Var13, tFinalisedTransaction201007.Var14,
 tFinalisedTransaction201007.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201007.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201008.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201008.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201008.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201008.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201008.Var01,
 tFinalisedTransaction201008.Var02,
 tFinalisedTransaction201008.Var03,
 tFinalisedTransaction201008.Var04,
 tFinalisedTransaction201008.Var05,
 tFinalisedTransaction201008.Var06,
 tFinalisedTransaction201008.Var07,
 tFinalisedTransaction201008.Var08,
 tFinalisedTransaction201008.Var09,
 tFinalisedTransaction201008.Var10,
 tFinalisedTransaction201008.Var11,
 tFinalisedTransaction201008.Var12,
 tFinalisedTransaction201008.Var13,
 tFinalisedTransaction201008.Var14,
 tFinalisedTransaction201008.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201008
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201008.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201008.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201008.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201008.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201008.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201008.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201008.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201008.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201008.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201008.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201008.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/08'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201008.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201008.Var01, tFinalisedTransaction201008.Var02,
 tFinalisedTransaction201008.Var03, tFinalisedTransaction201008.Var04, 
 tFinalisedTransaction201008.Var05, tFinalisedTransaction201008.Var06,
 tFinalisedTransaction201008.Var07, tFinalisedTransaction201008.Var08,
 tFinalisedTransaction201008.Var09, tFinalisedTransaction201008.Var10,
 tFinalisedTransaction201008.Var11, tFinalisedTransaction201008.Var12,
 tFinalisedTransaction201008.Var13, tFinalisedTransaction201008.Var14,
 tFinalisedTransaction201008.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201008.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201009.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201009.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201009.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201009.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201009.Var01,
 tFinalisedTransaction201009.Var02,
 tFinalisedTransaction201009.Var03,
 tFinalisedTransaction201009.Var04,
 tFinalisedTransaction201009.Var05,
 tFinalisedTransaction201009.Var06,
 tFinalisedTransaction201009.Var07,
 tFinalisedTransaction201009.Var08,
 tFinalisedTransaction201009.Var09,
 tFinalisedTransaction201009.Var10,
 tFinalisedTransaction201009.Var11,
 tFinalisedTransaction201009.Var12,
 tFinalisedTransaction201009.Var13,
 tFinalisedTransaction201009.Var14,
 tFinalisedTransaction201009.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201009
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201009.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201009.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201009.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201009.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201009.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201009.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201009.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201009.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201009.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201009.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201009.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/09'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201009.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201009.Var01, tFinalisedTransaction201009.Var02,
 tFinalisedTransaction201009.Var03, tFinalisedTransaction201009.Var04, 
 tFinalisedTransaction201009.Var05, tFinalisedTransaction201009.Var06,
 tFinalisedTransaction201009.Var07, tFinalisedTransaction201009.Var08,
 tFinalisedTransaction201009.Var09, tFinalisedTransaction201009.Var10,
 tFinalisedTransaction201009.Var11, tFinalisedTransaction201009.Var12,
 tFinalisedTransaction201009.Var13, tFinalisedTransaction201009.Var14,
 tFinalisedTransaction201009.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201009.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201010.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201010.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201010.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201010.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201010.Var01,
 tFinalisedTransaction201010.Var02,
 tFinalisedTransaction201010.Var03,
 tFinalisedTransaction201010.Var04,
 tFinalisedTransaction201010.Var05,
 tFinalisedTransaction201010.Var06,
 tFinalisedTransaction201010.Var07,
 tFinalisedTransaction201010.Var08,
 tFinalisedTransaction201010.Var09,
 tFinalisedTransaction201010.Var10,
 tFinalisedTransaction201010.Var11,
 tFinalisedTransaction201010.Var12,
 tFinalisedTransaction201010.Var13,
 tFinalisedTransaction201010.Var14,
 tFinalisedTransaction201010.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201010
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201010.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201010.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201010.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201010.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201010.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201010.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201010.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201010.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201010.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201010.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201010.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/10'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201010.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201010.Var01, tFinalisedTransaction201010.Var02,
 tFinalisedTransaction201010.Var03, tFinalisedTransaction201010.Var04, 
 tFinalisedTransaction201010.Var05, tFinalisedTransaction201010.Var06,
 tFinalisedTransaction201010.Var07, tFinalisedTransaction201010.Var08,
 tFinalisedTransaction201010.Var09, tFinalisedTransaction201010.Var10,
 tFinalisedTransaction201010.Var11, tFinalisedTransaction201010.Var12,
 tFinalisedTransaction201010.Var13, tFinalisedTransaction201010.Var14,
 tFinalisedTransaction201010.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201010.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201011.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201011.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201011.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201011.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201011.Var01,
 tFinalisedTransaction201011.Var02,
 tFinalisedTransaction201011.Var03,
 tFinalisedTransaction201011.Var04,
 tFinalisedTransaction201011.Var05,
 tFinalisedTransaction201011.Var06,
 tFinalisedTransaction201011.Var07,
 tFinalisedTransaction201011.Var08,
 tFinalisedTransaction201011.Var09,
 tFinalisedTransaction201011.Var10,
 tFinalisedTransaction201011.Var11,
 tFinalisedTransaction201011.Var12,
 tFinalisedTransaction201011.Var13,
 tFinalisedTransaction201011.Var14,
 tFinalisedTransaction201011.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201011
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201011.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201011.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201011.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201011.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201011.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201011.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201011.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201011.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201011.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201011.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201011.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/11'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201011.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201011.Var01, tFinalisedTransaction201011.Var02,
 tFinalisedTransaction201011.Var03, tFinalisedTransaction201011.Var04, 
 tFinalisedTransaction201011.Var05, tFinalisedTransaction201011.Var06,
 tFinalisedTransaction201011.Var07, tFinalisedTransaction201011.Var08,
 tFinalisedTransaction201011.Var09, tFinalisedTransaction201011.Var10,
 tFinalisedTransaction201011.Var11, tFinalisedTransaction201011.Var12,
 tFinalisedTransaction201011.Var13, tFinalisedTransaction201011.Var14,
 tFinalisedTransaction201011.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201011.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201012.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201012.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201012.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201012.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201012.Var01,
 tFinalisedTransaction201012.Var02,
 tFinalisedTransaction201012.Var03,
 tFinalisedTransaction201012.Var04,
 tFinalisedTransaction201012.Var05,
 tFinalisedTransaction201012.Var06,
 tFinalisedTransaction201012.Var07,
 tFinalisedTransaction201012.Var08,
 tFinalisedTransaction201012.Var09,
 tFinalisedTransaction201012.Var10,
 tFinalisedTransaction201012.Var11,
 tFinalisedTransaction201012.Var12,
 tFinalisedTransaction201012.Var13,
 tFinalisedTransaction201012.Var14,
 tFinalisedTransaction201012.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201012
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201012.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201012.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201012.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201012.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201012.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201012.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201012.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201012.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201012.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201012.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201012.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2010/12'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201012.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201012.Var01, tFinalisedTransaction201012.Var02,
 tFinalisedTransaction201012.Var03, tFinalisedTransaction201012.Var04, 
 tFinalisedTransaction201012.Var05, tFinalisedTransaction201012.Var06,
 tFinalisedTransaction201012.Var07, tFinalisedTransaction201012.Var08,
 tFinalisedTransaction201012.Var09, tFinalisedTransaction201012.Var10,
 tFinalisedTransaction201012.Var11, tFinalisedTransaction201012.Var12,
 tFinalisedTransaction201012.Var13, tFinalisedTransaction201012.Var14,
 tFinalisedTransaction201012.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201012.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201101.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201101.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201101.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201101.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201101.Var01,
 tFinalisedTransaction201101.Var02,
 tFinalisedTransaction201101.Var03,
 tFinalisedTransaction201101.Var04,
 tFinalisedTransaction201101.Var05,
 tFinalisedTransaction201101.Var06,
 tFinalisedTransaction201101.Var07,
 tFinalisedTransaction201101.Var08,
 tFinalisedTransaction201101.Var09,
 tFinalisedTransaction201101.Var10,
 tFinalisedTransaction201101.Var11,
 tFinalisedTransaction201101.Var12,
 tFinalisedTransaction201101.Var13,
 tFinalisedTransaction201101.Var14,
 tFinalisedTransaction201101.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201101
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201101.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201101.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201101.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201101.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201101.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201101.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201101.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201101.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201101.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201101.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201101.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2011/01'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201101.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201101.Var01, tFinalisedTransaction201101.Var02,
 tFinalisedTransaction201101.Var03, tFinalisedTransaction201101.Var04, 
 tFinalisedTransaction201101.Var05, tFinalisedTransaction201101.Var06,
 tFinalisedTransaction201101.Var07, tFinalisedTransaction201101.Var08,
 tFinalisedTransaction201101.Var09, tFinalisedTransaction201101.Var10,
 tFinalisedTransaction201101.Var11, tFinalisedTransaction201101.Var12,
 tFinalisedTransaction201101.Var13, tFinalisedTransaction201101.Var14,
 tFinalisedTransaction201101.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201101.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 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
	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
		INNER 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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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
\go -m bcp
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,
 tFinalisedTransaction201103.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201103.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201103.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201103.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201103.Var01,
 tFinalisedTransaction201103.Var02,
 tFinalisedTransaction201103.Var03,
 tFinalisedTransaction201103.Var04,
 tFinalisedTransaction201103.Var05,
 tFinalisedTransaction201103.Var06,
 tFinalisedTransaction201103.Var07,
 tFinalisedTransaction201103.Var08,
 tFinalisedTransaction201103.Var09,
 tFinalisedTransaction201103.Var10,
 tFinalisedTransaction201103.Var11,
 tFinalisedTransaction201103.Var12,
 tFinalisedTransaction201103.Var13,
 tFinalisedTransaction201103.Var14,
 tFinalisedTransaction201103.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201103
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201103.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201103.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201103.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201103.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201103.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201103.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201103.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201103.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201103.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201103.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201103.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2011/03'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201103.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201103.Var01, tFinalisedTransaction201103.Var02,
 tFinalisedTransaction201103.Var03, tFinalisedTransaction201103.Var04, 
 tFinalisedTransaction201103.Var05, tFinalisedTransaction201103.Var06,
 tFinalisedTransaction201103.Var07, tFinalisedTransaction201103.Var08,
 tFinalisedTransaction201103.Var09, tFinalisedTransaction201103.Var10,
 tFinalisedTransaction201103.Var11, tFinalisedTransaction201103.Var12,
 tFinalisedTransaction201103.Var13, tFinalisedTransaction201103.Var14,
 tFinalisedTransaction201103.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201103.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201104.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201104.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201104.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201104.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201104.Var01,
 tFinalisedTransaction201104.Var02,
 tFinalisedTransaction201104.Var03,
 tFinalisedTransaction201104.Var04,
 tFinalisedTransaction201104.Var05,
 tFinalisedTransaction201104.Var06,
 tFinalisedTransaction201104.Var07,
 tFinalisedTransaction201104.Var08,
 tFinalisedTransaction201104.Var09,
 tFinalisedTransaction201104.Var10,
 tFinalisedTransaction201104.Var11,
 tFinalisedTransaction201104.Var12,
 tFinalisedTransaction201104.Var13,
 tFinalisedTransaction201104.Var14,
 tFinalisedTransaction201104.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201104
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201104.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201104.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201104.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201104.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201104.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201104.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201104.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201104.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201104.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201104.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201104.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2011/04'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201104.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201104.Var01, tFinalisedTransaction201104.Var02,
 tFinalisedTransaction201104.Var03, tFinalisedTransaction201104.Var04, 
 tFinalisedTransaction201104.Var05, tFinalisedTransaction201104.Var06,
 tFinalisedTransaction201104.Var07, tFinalisedTransaction201104.Var08,
 tFinalisedTransaction201104.Var09, tFinalisedTransaction201104.Var10,
 tFinalisedTransaction201104.Var11, tFinalisedTransaction201104.Var12,
 tFinalisedTransaction201104.Var13, tFinalisedTransaction201104.Var14,
 tFinalisedTransaction201104.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201104.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201105.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201105.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201105.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201105.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201105.Var01,
 tFinalisedTransaction201105.Var02,
 tFinalisedTransaction201105.Var03,
 tFinalisedTransaction201105.Var04,
 tFinalisedTransaction201105.Var05,
 tFinalisedTransaction201105.Var06,
 tFinalisedTransaction201105.Var07,
 tFinalisedTransaction201105.Var08,
 tFinalisedTransaction201105.Var09,
 tFinalisedTransaction201105.Var10,
 tFinalisedTransaction201105.Var11,
 tFinalisedTransaction201105.Var12,
 tFinalisedTransaction201105.Var13,
 tFinalisedTransaction201105.Var14,
 tFinalisedTransaction201105.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201105
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201105.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201105.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201105.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201105.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201105.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201105.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201105.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201105.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201105.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201105.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201105.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2011/05'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201105.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201105.Var01, tFinalisedTransaction201105.Var02,
 tFinalisedTransaction201105.Var03, tFinalisedTransaction201105.Var04, 
 tFinalisedTransaction201105.Var05, tFinalisedTransaction201105.Var06,
 tFinalisedTransaction201105.Var07, tFinalisedTransaction201105.Var08,
 tFinalisedTransaction201105.Var09, tFinalisedTransaction201105.Var10,
 tFinalisedTransaction201105.Var11, tFinalisedTransaction201105.Var12,
 tFinalisedTransaction201105.Var13, tFinalisedTransaction201105.Var14,
 tFinalisedTransaction201105.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201105.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp
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,
 tFinalisedTransaction201106.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201106.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201106.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201106.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201106.Var01,
 tFinalisedTransaction201106.Var02,
 tFinalisedTransaction201106.Var03,
 tFinalisedTransaction201106.Var04,
 tFinalisedTransaction201106.Var05,
 tFinalisedTransaction201106.Var06,
 tFinalisedTransaction201106.Var07,
 tFinalisedTransaction201106.Var08,
 tFinalisedTransaction201106.Var09,
 tFinalisedTransaction201106.Var10,
 tFinalisedTransaction201106.Var11,
 tFinalisedTransaction201106.Var12,
 tFinalisedTransaction201106.Var13,
 tFinalisedTransaction201106.Var14,
 tFinalisedTransaction201106.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201106
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201106.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201106.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER 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 tFinalisedTransaction201106.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201106.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

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

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201106.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201106.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201106.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201106.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201106.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201106.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201106.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '2011/06'    and tInvoice.PlatinumInvoiceNo = 'INV010505'
        AND tFinalisedTransaction201106.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'))
          
           and tInvoiceDetail.BatchID NOT IN (
	36303
	) 
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,
 tFinalisedTransaction201106.Var01, tFinalisedTransaction201106.Var02,
 tFinalisedTransaction201106.Var03, tFinalisedTransaction201106.Var04, 
 tFinalisedTransaction201106.Var05, tFinalisedTransaction201106.Var06,
 tFinalisedTransaction201106.Var07, tFinalisedTransaction201106.Var08,
 tFinalisedTransaction201106.Var09, tFinalisedTransaction201106.Var10,
 tFinalisedTransaction201106.Var11, tFinalisedTransaction201106.Var12,
 tFinalisedTransaction201106.Var13, tFinalisedTransaction201106.Var14,
 tFinalisedTransaction201106.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201106.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
\go -m bcp