SELECT
 tBatch.ID,
 tBatch.BillingPeriod,
 tBatchType.SupplierID,
 tBatchType.Description AS BatchType,
 tSupplier.SupplierName,
 tServiceID.ServiceID,
 tServiceID.Service,
 tServiceType.Code AS ServiceTypeCode,
 tServiceType.Description AS ServiceType,
 tInvoice.InvoiceDate,
 tFinalisedTransaction201311.TxnDate,
 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,

 tOrigin.Description AS Origin,
 tDestination.Description AS Destination,
 tRateDescription.Description AS RateDescription,
 tTransactionType.Description AS TransactionType,
 tTransactionGroup.Description AS TransactionGroup,
 tDialledNumber.Description AS DialledNumber,
 tFinalisedTransaction201311.Duration,

 tFinalisedTransaction201311.AmountExGST,

 tBatchType.Var01Description,
 tFinalisedTransaction201311.Var01,
 tBatchType.Var02Description,
 tFinalisedTransaction201311.Var02,
 tBatchType.Var03Description,
 tFinalisedTransaction201311.Var03,
 tBatchType.Var04Description,
 tFinalisedTransaction201311.Var04,
 tBatchType.Var05Description,
 tFinalisedTransaction201311.Var05,
 tBatchType.Var06Description,
 tFinalisedTransaction201311.Var06,
 tBatchType.Var07Description,
 tFinalisedTransaction201311.Var07,
 tBatchType.Var08Description,
 tFinalisedTransaction201311.Var08,
 tBatchType.Var09Description,
 tFinalisedTransaction201311.Var09,
 tBatchType.Var10Description,
 tFinalisedTransaction201311.Var10,
 tBatchType.Var11Description,
 tFinalisedTransaction201311.Var11,
 tBatchType.Var12Description,
 tFinalisedTransaction201311.Var12,
 tBatchType.Var13Description,
 tFinalisedTransaction201311.Var13,
 tBatchType.Var14Description,
 tFinalisedTransaction201311.Var14,
 tBatchType.Var15Description,
 tFinalisedTransaction201311.Var15

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

FROM
 tFinalisedTransaction201311
 INNER JOIN (
        tBatch
        LEFT JOIN (
                tBatchType
                LEFT JOIN tReportGroup
                        ON tBatchType.ReportGroupID = tReportGroup.ID
                )
                ON tBatch.BatchTypeID = tBatchType.ID
        )
        ON tFinalisedTransaction201311.BatchID = tBatch.ID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201311.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 tFinalisedTransaction201311.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tDialledNumber
        ON tFinalisedTransaction201311.DialledNumberID = tDialledNumber.ID
 LEFT JOIN tRateDescription
        ON tFinalisedTransaction201311.RateDescriptionID = tRateDescription.ID
 LEFT JOIN tDestination
        ON tFinalisedTransaction201311.DestinationID = tDestination.ID
 LEFT JOIN tOrigin
        ON tFinalisedTransaction201311.OriginID = tOrigin.ID
 LEFT JOIN tElement
        ON tFinalisedTransaction201311.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON tFinalisedTransaction201311.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 tFinalisedTransaction201311.ServiceTypeID = tServiceType.ID
  LEFT JOIN (
        tTransactionType
        LEFT JOIN tTransactionGroup
                ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
--#     LEFT JOIN tSupplierServiceCode
--#             ON tTransactionType.ID = tSupplierServiceCode.TransactionTypeID
        ) ON tFinalisedTransaction201311.TransactionTypeID = tTransactionType.ID
--# LEFT JOIN tSupplierServiceCode
--#     ON tFinalisedTransaction201311.SupplierServiceCodeID = tSupplierServiceCode.id

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201311.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201311.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201311.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201311.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201311.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201311.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201311.PersonID = tPerson.ID

WHERE
        tBatch.BillingPeriod = "2013/11"
        AND tFinalisedTransaction201311.RevenueFlag = 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,15,"20131130")
          AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,15,"20131130"))



  and tCustomer.ShipTo = "ZCZ"
  and tInvoice.PlatinumInvoiceNo= "INV028247"
  and tBatch.ID = 42721
--#  and tServiceID.ServiceID = "0407-326077
ORDER BY tInvoice.PlatinumInvoiceNo, tBatch.ID,
         tServiceID.ServiceID, tFinalisedTransaction201311.Var01