SELECT
 tBatch.ID,
 tBatch.BillingPeriod,
 tBatchType.ID,
 tBatchType.SupplierID,
 tBatchType.Description AS BatchType,
 tSupplier.SupplierName,
 tServiceID.ServiceID,
 tServiceID.Service,
 tServiceType.Code AS ServiceTypeCode,
 tServiceType.Description AS ServiceType,
 "" AS InvoiceDate,
 tFinalisedTransaction201004.TxnDate,
 tReportGroup.ID AS ReportGrpID,
 tReportGroup.GroupDesc AS ReportGrpDesc,
 "" AS PlatinumFileName,
 "VRT999999" AS 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,
 tApplication.Description,

 tOrigin.Description AS Origin,
 tDestination.Description AS Destination,
 tRateDescription.Description AS RateDescription,
 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

--# tServiceType.SupplierID,
--# tSupplierServiceCode.SupplierID,
--# tSupplierServiceCode.Code AS SupplierServCode,

FROM
 tFinalisedTransaction201004
 INNER JOIN (
        tBatch
        LEFT JOIN (
                tBatchType
                LEFT JOIN tReportGroup
                        ON tBatchType.ReportGroupID = tReportGroup.ID
                )
                ON tBatch.BatchTypeID = tBatchType.ID
        )
        ON tFinalisedTransaction201004.BatchID = tBatch.ID
 LEFT JOIN ( tServiceID
         LEFT JOIN tApplication
                ON tServiceID.ApplicationID = tApplication.ID
        ) ON tFinalisedTransaction201004.ServiceID = tServiceID.ID
--# LEFT 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 tDialledNumber
        ON tFinalisedTransaction201004.DialledNumberID = tDialledNumber.ID
 LEFT JOIN tRateDescription
        ON tFinalisedTransaction201004.RateDescriptionID = tRateDescription.ID
 LEFT JOIN tDestination
        ON tFinalisedTransaction201004.DestinationID = tDestination.ID
 LEFT JOIN tOrigin
        ON tFinalisedTransaction201004.OriginID = tOrigin.ID
 LEFT JOIN tElement
        ON tFinalisedTransaction201004.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON tFinalisedTransaction201004.CustomerElementID = tElement_1.ID
 LEFT JOIN (
        tServiceType
        LEFT JOIN tSupplier
                ON tServiceType.SupplierID = tSupplier.ID
        LEFT JOIN tElement tElement_2
                ON tServiceType.RevenueElementID = tElement_2.ID
        )
        ON tFinalisedTransaction201004.ServiceTypeID = tServiceType.ID
  LEFT JOIN (
        tTransactionType
        LEFT JOIN tTransactionGroup
                ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
--#     LEFT JOIN tSupplierServiceCode
--#             ON tTransactionType.ID = tSupplierServiceCode.TransactionTypeID
        ) ON tFinalisedTransaction201004.TransactionTypeID = tTransactionType.ID
--# LEFT JOIN tSupplierServiceCode
--#     ON tFinalisedTransaction201004.SupplierServiceCodeID = tSupplierServiceCode.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 tFinalisedTransaction201004.RevenueFlag = 1
        AND tCustomer.ShipTo = "VRT"
        AND tBatchType.Active = 1
--#     AND tSupplierServiceCode.Active <> 0

--#     and tElement.ID = tServiceType.RevenueElementID

--#     and tTransactionType.ID = tSupplierServiceCode.TransactionTypeID

--#     and tSupplier.ID = SupplierServiceCode.SupplierID

--#--#    AND tInvoice.InvoiceDate > dateadd(dd,9,'20100430')
--#--#    AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,9,'20100430'))

 and tServiceID.ServiceID = "0422-661384"
ORDER BY tCustomer.ShipTo, PlatinumInvoiceNo, tBatch.ID,
         tServiceID.ServiceID, tFinalisedTransaction201004.Var01,
         tReportGroup.ID