/* TreeMenu 1 - OK */
SELECT tDataSourceType.DataSource, tBatchType.Description, tBatchStatusHistory.BatchStatusID, tBatch.BillingPeriod
FROM (tBatchType LEFT JOIN tDataSourceType ON tBatchType.DataSourceTypeID = tDataSourceType.ID)
 LEFT JOIN (tBatch
 RIGHT JOIN tBatchStatusHistory ON tBatch.ID = tBatchStatusHistory.BatchID) ON tBatchType.ID = tBatch.BatchTypeID
WHERE tBatch.BillingPeriod LIKE '04/2004'
ORDER BY tDataSourceType.DataSource

/* TreeMenu 2 - OK */
SELECT tDataSourceType.DataSource, tBatchType.Description, BH.BatchStatusID, BH.BillingPeriod, BH.ID
FROM (tBatchType LEFT JOIN tDataSourceType ON tBatchType.DataSourceTypeID = tDataSourceType.ID)
 LEFT JOIN (SELECT tBatch.BillingPeriod, tBatch.BatchTypeID, tBatch.BatchStatusID, tBatch.ID
  FROM tBatch
  WHERE tBatch.BillingPeriod LIKE '04/2004') AS BH ON tBatchType.ID = BH.BatchTypeID
ORDER BY tDataSourceType.DataSource, tBatchType.Description

/* BatchStatus Summary - OK */
SELECT BH.*
FROM (SELECT tBatch.ID AS BatchID, tBatchStatus.Description AS BatchStatus, tBatchType.Description AS BatchType,
 (tBatch.TotalPayable - tBatch.GSTAmount) AS AmountExGST,
 tBatch.GSTAmount, tBatch.TotalPayable, tBatch.TotalAdjustments
FROM (tBatch
LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID)
LEFT JOIN tBatchStatus ON tBatch.BatchStatusID = tBatchStatus.ID
WHERE tBatch.BillingPeriod = '2004/04') AS BH
ORDER BY BH.BatchID

/* Critical Error Summary - OK */
SELECT tDataCollection.BatchID, tBatchType.Description AS BatchType,
 Sum(tDataCollection.AmountExGST) AS TotalExGST, Sum(tDataCollection.GSTAmount) AS TotalGST,
 Sum(tDataCollection.AmountIncGST) AS TotalIncGST,
 Sum(CASE WHEN tDataCollection.StatusID IS NOT NULL THEN tDataCollection.AmountExGST ELSE 0 END) AS CriticalValue
FROM (tDataCollection
LEFT JOIN tBatch ON tDataCollection.BatchID = tBatch.ID)
LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID
WHERE tBatch.BillingPeriod = '2004/04'
GROUP BY tDataCollection.BatchID, tBatchType.Description
ORDER BY tDataCollection.BatchID

/* Critical Error Resolution - OK OLD (with tTransaction.Description included) */
SELECT DC.*, tCriticalType.Description AS CriticalType, tTransactionType.Description AS Transaction_Type
FROM (SELECT tDataCollection.BatchID, tDataCollection.ServiceID, tDataCollection.StatusID,
 tDataCollection.TransactionType, tBatch.BatchTypeID,
 Sum(tDataCollection.AmountExGST) AS TotalExGST, Sum(tDataCollection.GSTAmount) AS TotalGST,
 Sum(tDataCollection.AmountIncGST) AS TotalIncGST, COUNT(tDataCollection.StatusID) AS RecordCount
FROM tDataCollection
LEFT JOIN tBatch ON tDataCollection.BatchID = tBatch.ID
WHERE tBatch.BillingPeriod = '2004/04'
AND tDataCollection.StatusID IS NOT NULL
GROUP BY tDataCollection.BatchID, tBatch.BatchTypeID, tDataCollection.ServiceID, tDataCollection.StatusID,
 tDataCollection.TransactionType) AS DC
LEFT JOIN tCriticalType ON DC.StatusID = tCriticalType.ID
LEFT JOIN tTransactionType ON DC.BatchTypeID = tTransactionType.ID
ORDER BY DC.BatchID, DC.ServiceID

/* Critical Error Resolution - OK (final) */
SELECT DC.*, tCriticalType.Description AS CriticalType
FROM (SELECT tDataCollection.BatchID, tDataCollection.ServiceID, tDataCollection.StatusID,
 tDataCollection.TransactionType, tBatch.BatchTypeID,
 Sum(tDataCollection.AmountExGST) AS TotalExGST, Sum(tDataCollection.GSTAmount) AS TotalGST,
 Sum(tDataCollection.AmountIncGST) AS TotalIncGST, COUNT(tDataCollection.StatusID) AS RecordCount
FROM tDataCollection
LEFT JOIN tBatch ON tDataCollection.BatchID = tBatch.ID
WHERE tBatch.BillingPeriod = '2004/04'
AND tDataCollection.StatusID IS NOT NULL
GROUP BY tDataCollection.BatchID, tBatch.BatchTypeID, tDataCollection.ServiceID, tDataCollection.StatusID,
 tDataCollection.TransactionType) AS DC
LEFT JOIN tCriticalType ON DC.StatusID = tCriticalType.ID
ORDER BY DC.BatchID, DC.ServiceID

/* Adjustment View - OK */
SELECT tAdjustment.BatchID, tBatchType.Description AS BatchType,
 Sum(tAdjustment.AmountExGST) AS TotalExGST, Sum(tAdjustment.GSTAmount) AS TotalGST,
 Sum(tAdjustment.AmountIncGST) AS TotalIncGST,
 Sum(CASE WHEN tAdjustment.StatusID IS NOT NULL THEN tAdjustment.AmountExGST ELSE 0 END) AS CriticalValue
FROM (tAdjustment
LEFT JOIN tBatch ON tAdjustment.BatchID = tBatch.ID)
LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID
WHERE tBatch.BillingPeriod = '2004/04'
GROUP BY tAdjustment.BatchID, tBatchType.Description
ORDER BY tAdjustment.BatchID

/* Dubious Error Summary - OK */
SELECT tDubiousResult.BatchID, tServiceID.ServiceID, tDubiousType.Description AS DubiousError,
 tDubiousResolution.Description AS Resolution, tTransactionType.Description AS TransactionType,
 TN.TotalExGST, TN.TotalGST, TN.TotalIncGST, TN.RecordCount, tDubiousResult.DubiousTypeID
FROM (((((tDubiousResult
LEFT JOIN tBatch ON tDubiousResult.BatchID = tBatch.ID)
LEFT JOIN tServiceID ON tDubiousResult.ServiceID = tServiceID.ID)
LEFT JOIN tDubiousType ON tDubiousResult.DubiousTypeID = tDubiousType.ID)
LEFT JOIN tDubiousResolution ON tDubiousResult.ResolutionID = tDubiousResolution.ID)
INNER JOIN (SELECT tTransaction.BatchID, tTransaction.ServiceID,
 tTransaction.TransactionTypeID, tBatch.BatchTypeID,
 Sum(tTransaction.AmountExGST) AS TotalExGST, Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST, COUNT(tTransaction.StatusID) AS RecordCount
FROM tTransaction
LEFT JOIN tBatch ON tTransaction.BatchID = tBatch.ID
WHERE tBatch.BillingPeriod = '2004/04'
AND (tBatch.BatchStatusID = 6 OR tBatch.BatchStatusID = 7)
AND tTransaction.StatusID IS NOT NULL
GROUP BY tTransaction.BatchID, tBatch.BatchTypeID, tTransaction.ServiceID,
 tTransaction.TransactionTypeID) AS TN
 ON ((tDubiousResult.BatchID = TN.BatchID)
 AND (tDubiousResult.ServiceID = TN.ServiceID)))
LEFT JOIN tTransactionType ON TN.TransactionTypeID = tTransactionType.ID
ORDER BY tDubiousResult.BatchID, tServiceID.ServiceID

/* Dubious Error Summary (NEW) - OK */
SELECT TN1.*
FROM (SELECT tDubiousResult.BatchID, tServiceID.ServiceID, tDubiousType.Description AS DubiousError,
 tDubiousResolution.Description AS Resolution, tTransactionType.Description AS TransactionType,
 TN.TotalExGST, TN.TotalGST, TN.TotalIncGST, TN.RecordCount, tDubiousResult.DubiousTypeID, tDubiousResult.ID AS DID
FROM (((((tDubiousResult
LEFT JOIN tBatch ON tDubiousResult.BatchID = tBatch.ID)
LEFT JOIN tServiceID ON tDubiousResult.ServiceID = tServiceID.ID)
LEFT JOIN tDubiousType ON tDubiousResult.DubiousTypeID = tDubiousType.ID)
LEFT JOIN tDubiousResolution ON tDubiousResult.ResolutionID = tDubiousResolution.ID)
INNER JOIN (SELECT tTransaction.BatchID, tTransaction.ServiceID,
 tTransaction.TransactionTypeID, tBatch.BatchTypeID,
 Sum(tTransaction.AmountExGST) AS TotalExGST, Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST, COUNT(tTransaction.ServiceID) AS RecordCount
FROM tTransaction
LEFT JOIN tBatch ON tTransaction.BatchID = tBatch.ID
WHERE tBatch.BillingPeriod = '2004/04'
AND tTransaction.StatusID IS NULL
GROUP BY tTransaction.BatchID, tBatch.BatchTypeID, tTransaction.ServiceID,
 tTransaction.TransactionTypeID) AS TN
 ON ((tDubiousResult.BatchID = TN.BatchID)
 AND (tDubiousResult.ServiceID = TN.ServiceID)))
LEFT JOIN tTransactionType ON TN.TransactionTypeID = tTransactionType.ID) AS TN1
ORDER BY TN1.BatchID, TN1.ServiceID

/* Dubious Error Summary (Update) - OK */
SELECT DR.*
FROM (SELECT TN.*, tDubiousResult.DubiousTypeID, tDubiousResult.ID AS DID, tServiceID.ServiceID AS Service,
 tDubiousType.Description AS DubiousError, tDubiousResolution.Description AS Resolution
FROM ((((SELECT tTransaction.BatchID, tTransaction.ServiceID,
 tTransaction.TransactionTypeID, tBatch.BatchTypeID, tTransactionType.Description AS TransactionType,
 Sum(tTransaction.AmountExGST) AS TotalExGST, Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST, COUNT(tTransaction.ServiceID) AS RecordCount
FROM ((tTransaction
LEFT JOIN tBatch ON tTransaction.BatchID = tBatch.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 14511
AND (tBatch.BatchStatusID = 7 OR tBatch.BatchStatusID = 8)
AND tTransactionGroup.Description NOT LIKE 'ADMIN'
AND tTransactionType.Description NOT LIKE 'Fee%'
AND tTransaction.StatusID IS NULL
GROUP BY tTransaction.BatchID, tBatch.BatchTypeID, tTransaction.ServiceID,
 tTransaction.TransactionTypeID, tTransactionType.Description) AS TN
LEFT JOIN tDubiousResult ON ((TN.BatchID = tDubiousResult.BatchID) AND (TN.ServiceID = tDubiousResult.ServiceID)))
LEFT JOIN tDubiousType ON tDubiousResult.DubiousTypeID = tDubiousType.ID)
LEFT JOIN tDubiousResolution ON tDubiousResult.ResolutionID = tDubiousResolution.ID)
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
WHERE tDubiousResult.DubiousTypeID IS NOT NULL) AS DR
ORDER BY DR.BatchID, DR.ServiceID

/* Dubious Error Summary (040815) - OK, BUT STILL DOES NOT SHOW ALL RESOLUTIONS  **************************************   */
SELECT DR.*
FROM (SELECT TN.*, tDubiousResult.DubiousTypeID, tDubiousResult.ID AS DID, tServiceID.ServiceID AS Service,
 tDubiousType.Description AS DubiousError, tDubiousResolution.Description AS Resolution
FROM ((((SELECT tTransaction.BatchID, tTransaction.ServiceID, tBatch.BatchTypeID,
 Sum(tTransaction.AmountExGST) AS TotalExGST, Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST, COUNT(tTransaction.ServiceID) AS RecordCount
FROM ((tTransaction
LEFT JOIN tBatch ON tTransaction.BatchID = tBatch.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 14502
AND (tBatch.BatchStatusID = 8 OR tBatch.BatchStatusID = 9)
AND tTransactionGroup.Description NOT LIKE 'ADMIN'
AND tTransactionType.Description NOT LIKE '%Fee %'
AND tTransaction.StatusID IS NULL
GROUP BY tTransaction.BatchID, tBatch.BatchTypeID, tTransaction.ServiceID) AS TN
LEFT JOIN tDubiousResult ON ((TN.BatchID = tDubiousResult.BatchID) AND (TN.ServiceID = tDubiousResult.ServiceID)))
LEFT JOIN tDubiousType ON tDubiousResult.DubiousTypeID = tDubiousType.ID)
LEFT JOIN tDubiousResolution ON tDubiousResult.ResolutionID = tDubiousResolution.ID)
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
WHERE tDubiousResult.DubiousTypeID IS NOT NULL) AS DR
ORDER BY DR.BatchID, DR.ServiceID










/*  Dubious Error Detail - OK (Usual to limit to one ServiceID as selected using Dubious Error Summary)  */
SELECT tTransaction.BatchID, tServiceID.ServiceID, Min(tTransaction.FromDate) AS DateFrom,
 Max(tTransaction.ToDate) AS DateTo, tTransactionType.Description AS TransactionType,
 Sum(tTransaction.AmountExGST) AS TotalExGST, Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST
FROM ((tTransaction
LEFT JOIN tDubiousResult ON ((tTransaction.BatchID = tDubiousResult.BatchID)
 AND (tTransaction.ServiceID = tDubiousResult.ServiceID)))
LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID
WHERE tTransaction.BatchID = 14501
AND tDubiousResult.ID IS NOT NULL
AND tTransaction.RevenueFlag <> 0
AND tTransactionType.Description NOT LIKE '%Lost%'
GROUP BY tTransaction.BatchID, tServiceID.ServiceID, tTransactionType.Description
ORDER BY tServiceID.ServiceID

/* Batch Transaction Summary - OK */
/* Revenue Release (same data) - OK */
SELECT TN.BatchID, tServiceID.ServiceID, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Total
FROM (SELECT tTransaction.BatchID, tTransaction.ServiceID,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountIncGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountIncGST ELSE 0 END) AS Other,
 Sum(tTransaction.AmountIncGST) AS Total
FROM (tTransaction
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 14501
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag = 0
GROUP BY tTransaction.BatchID, tTransaction.ServiceID) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
ORDER BY tServiceID.ServiceID

/*  Expenditure Release ServiceID details - OK (Usual to limit to one ServiceID as selected using BatchTrans Summary)  */
SELECT SID2.ServiceID, SID2.Code, SID2.ServiceType, SID2.Description, SID2.RevElement, tElement.ElementCode AS ExpElement, SID2.Supplier,
 SID2.ShipTo, SID2.CustomerCentre, SID2.CustomerActivity, SID2.CustomerElement, SID2.SplitService, SID2.VRTService
FROM (SELECT SID1.ServiceID, SID1.Code, SID1.ServiceType, SID1.Description, tElement.ElementCode AS RevElement, ExpElementID, SID1.Supplier,
 SID1.ShipTo, SID1.CustomerCentre, SID1.CustomerActivity, SID1.CustomerElement, SID1.SplitService, SID1.VRTService
FROM (SELECT SID.ServiceID, tServiceType.Code, tServiceType.Description AS ServiceType, SID.Description,
 tServiceType.RevenueElementID AS RevElementID, tServiceType.ExpenseElementID as ExpElementID,
 tSupplier.SupplierName AS Supplier, tCustomer.ShipTo, tCentre.Description AS CustomerCentre,
 tActivity.Description AS CustomerActivity, tElement.ElementCode AS CustomerElement,
 tSubledger.Description AS CustomerSubledger, tServiceType.SplitService, tServiceType.VRTServiceType as VRTService
FROM ((((((((tServiceID AS SID LEFT JOIN tServiceType ON SID.ServiceTypeID = tServiceType.ID)
 LEFT JOIN tSupplier ON tServiceType.SupplierID = tSupplier.ID)
 LEFT JOIN tCentre ON SID.CustomerCentreID = tCentre.ID)
 LEFT JOIN tActivity ON SID.CustomerActivityID = tActivity.ID)
 LEFT JOIN tElement ON SID.CustomerElementID = tElement.ID)
 LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID)
 LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)
 LEFT JOIN tSubledger ON SID.CustomerSubledgerID = tSubledger.ID)
 LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID) AS SID1
 LEFT JOIN tElement ON SID1.RevElementID = tElement.ID) AS SID2
 LEFT JOIN tElement ON SID2.ExpElementID = tElement.ID
ORDER BY SID2.ServiceID

/*  Dubious Error Detail - OK (Usual to limit to one ServiceID as selected using Dubious Error Summary)  */
SELECT tTransaction.BatchID, tServiceID.ServiceID,
 Min(tTransaction.FromDate) AS DateFrom,
 Max(tTransaction.ToDate) AS DateTo, tTransactionType.Description,
 Sum(tTransaction.AmountExGST) AS TotalExGST,
 Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST
FROM ((tTransaction
LEFT JOIN tDubiousResult ON ((tTransaction.BatchID = tDubiousResult.BatchID)
 AND (tTransaction.ServiceID = tDubiousResult.ServiceID)))
LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID
WHERE tTransaction.BatchID = 14501
AND tDubiousResult.ID IS NOT NULL
AND tTransaction.RevenueFlag <> 0
AND tTransactionType.Description NOT LIKE '%Lost%'
GROUP BY tTransaction.BatchID, tServiceID.ServiceID, tTransactionType.Description
ORDER BY tServiceID.ServiceID

/*  Admin Fees Summary - OK  */
SELECT TN.BatchID, tServiceID.ServiceID, tServiceType.Code, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin
FROM (SELECT tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountIncGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountIncGST ELSE 0 END) AS Other,
 Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountIncGST ELSE 0 END) AS Admin
FROM ((tTransaction
LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tBatch.BillingPeriod = '2004/04'
AND tBatch.BatchStatusID = 4
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag <> 0
GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
ORDER BY tServiceID.ServiceID

/*  Admin Fees Detail  */
SELECT tTransaction.BatchID, tServiceID.ServiceID,
 Min(tTransaction.FromDate) AS DateFrom,
 Max(tTransaction.ToDate) AS DateTo, tTransactionType.Description,
 Sum(tTransaction.AmountExGST) AS TotalExGST,
 Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST
FROM (tTransaction
LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID
WHERE tTransaction.BatchID = 14501
AND tServiceID.ServiceID LIKE '02-60245697'
AND tTransaction.RevenueFlag <> 0
AND tTransactionType.Description NOT LIKE '%Lost%'
GROUP BY tTransaction.BatchID, tServiceID.ServiceID, tTransactionType.Description
ORDER BY tServiceID.ServiceID

/*  Admin Fees Summary - Update  */
SELECT TN1.*
FROM (SELECT TN.BatchID, tServiceID.ServiceID, tServiceType.Code, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin,
tDiscount.DiscountRate, tDiscount.NonStdRevAdminFee, tDiscount.NonStdExpAdminFee
FROM (SELECT tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountIncGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountIncGST ELSE 0 END) AS Other,
 Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountIncGST ELSE 0 END) AS Admin,
 tTransaction.CustomerID
FROM ((tTransaction
LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tBatch.BillingPeriod = '2004/04'
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag <> 0
GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.CustomerID) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
LEFT JOIN tDiscount ON ((TN.CustomerID = tDiscount.CustomerID) AND (tServiceType.ID = tDiscount.ServiceTypeID))) AS TN1
ORDER BY TN1.ServiceID

/* Admin Fees Details - Ok */
SELECT tTransaction.BatchID, tServiceID.ServiceID,
 Min(tTransaction.FromDate) AS DateFrom,
 Max(tTransaction.ToDate) AS DateTo, tTransactionType.Description,
 Sum(tTransaction.AmountExGST) AS TotalExGST,
 Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST
FROM ((tTransaction
LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 14501
AND tServiceID.ServiceID LIKE '0401-048418'
AND (tTransaction.RevenueFlag <> 0 OR tTransactionGroup.Description LIKE 'ADMIN')
AND tTransactionType.Description NOT LIKE '%Lost%'
GROUP BY tTransaction.BatchID, tServiceID.ServiceID, tTransactionType.Description
ORDER BY tServiceID.ServiceID

/* Dubious resolution options */
SELECT tDubiousValidResolution.ID, tDubiousType.Description AS DubiousType, tDubiousResolution.Description AS DubiousResolution
FROM tDubiousValidResolution
LEFT JOIN tDubiousType ON tDubiousValidResolution.DubiousTypeID = tDubiousType.ID
LEFT JOIN tDubiousResolution ON tDubiousValidResolution.DubiousResolutionID = tDubiousResolution.ID
WHERE tDubiousValidResolution.Active = 1
AND tDubiousValidResolution.DubiousTypeID = qDubSummary['DubiousTypeID'];

/* Valid Dubious resolution options */
SELECT tDubiousValidResolution.ID, tDubiousValidResolution.DubiousTypeID
FROM tDubiousValidResolution
WHERE tDubiousValidResolution.Active = 1
AND tDubiousValidResolution.DubiousResolutionID = 1

/* CriticalTest_4 Update (040613) - OK */
UPDATE tDataCollection
SET tDataCollection.StatusID = 4
FROM (SELECT DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.NoCalls
 FROM (SELECT tDataCollection.ServiceID,
  Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountIncGST ELSE 0 END) AS CALL
  FROM (tDataCollection
  LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
  LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
  WHERE tDataCollection.BatchID = 14527
 AND tDataCollection.StatusID IS NULL
  GROUP BY tDataCollection.ServiceID) AS DC1
 LEFT JOIN tServiceID
  ON DC1.ServiceID = tServiceID.ServiceID
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC2
WHERE DC2.Call <> 0
AND DC2.NoCalls <> 0) AS DC3
WHERE tDataCollection.BatchID = 14527
AND tDataCollection.ServiceID = DC3.ServiceID

/* Transfer data from tDataCollection to tTransaction tables (040614) - OK */
SELECT tDataCollection.*,
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS SID, tServiceID.SubledgerID, tServiceID.CustomerCentreID, tServiceID.CustomerActivityID,
 tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd, tServiceID.UpMDF,
 tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.Code, tServiceType.ID AS TID, tServiceType.Description,
 tServiceType.RevenueElementID, tServiceType.ExpenseElementID, tServiceType.SplitService, tCustomer.ID AS Customer_ID,
 tDialledNumber.ID as DialledID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tTransactionGroup.Description AS TransactionGroup
FROM (((((tDataCollection LEFT JOIN ((tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN (tGroup LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID) ON tDepartment.GroupID = tGroup.ID)
 ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) ON tDataCollection.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tDialledNumber ON tDataCollection.DialledNumber = tDialledNumber.Description) LEFT JOIN tOrigin ON tDataCollection.Origin = tOrigin.Description)
 LEFT JOIN tDestination ON tDataCollection.Destination = tDestination.Description)
 LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE (tServiceID.ServiceID IS NOT NULL)
AND (tDataCollection.BatchID = 14527)
ORDER BY tDataCollection.ID

/* Transfer data from tDataCollection to tTransaction tables - NEW - (040815) - Okay */
SELECT tDataCollection.*,
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS SID, tServiceID.SubledgerID, tServiceID.CustomerCentreID, tServiceID.CustomerActivityID,
 tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd, tServiceID.UpMDF,
 tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.Code, tServiceType.ID AS TID, tServiceType.Description,
 tServiceType.RevenueElementID, tServiceType.ExpenseElementID, tServiceType.SplitService, tCustomer.ID AS Customer_ID, tDialledNumber.ID as DialledID,
 tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, tTransactionGroup.Description AS TransactionGroup
FROM ((((((tDataCollection LEFT JOIN ((tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN (tGroup LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID) ON tDepartment.GroupID = tGroup.ID)
 ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) ON tDataCollection.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tDialledNumber ON tDataCollection.DialledNumber = tDialledNumber.Description)
 LEFT JOIN tOrigin ON tDataCollection.Origin = tOrigin.Description)
 LEFT JOIN tDestination ON tDataCollection.Destination = tDestination.Description)
 LEFT JOIN tRateDescription ON tDataCollection.RateDescription = tRateDescription.Description)
 LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tServiceID.ServiceID IS NOT NULL
AND tDataCollection.BatchID = 14501
ORDER BY tDataCollection.ServiceID

/* DC Transactions (040614) - OK */
SELECT DC3.*
FROM (SELECT DC2.*, tCriticalType.Description AS Reason
FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) AS Total
FROM (SELECT tDataCollection.ServiceID, tDataCollection.StatusID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tDataCollection.AmountIncGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountIncGST ELSE 0 END) AS Call,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tDataCollection.AmountIncGST ELSE 0 END) AS Other,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tDataCollection.FromDate END) AS RentFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tDataCollection.ToDate END) AS RentTill,
 Max(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.ToDate END) AS CallTill
FROM (tDataCollection
LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tDataCollection.BatchID = 14527
GROUP BY tDataCollection.ServiceID, tDataCollection.StatusID) AS DC1) AS DC2
 LEFT JOIN tCriticalType ON DC2.StatusID = tCriticalType.ID) AS DC3
ORDER BY DC3.ServiceID

/* DC Critical Error Details (040614) - OK */
SELECT DC.*, tCriticalType.Description AS CriticalType
FROM (SELECT tDataCollection.BatchID, tDataCollection.ServiceID, tDataCollection.StatusID,
 tTransactionGroup.Description AS TransactionType, 
 Sum(tDataCollection.AmountExGST) AS TotalExGST, Sum(tDataCollection.GSTAmount) AS TotalGST,
 Sum(tDataCollection.AmountIncGST) AS TotalIncGST, COUNT(tDataCollection.StatusID) AS RecordCount
FROM (tDataCollection
LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tDataCollection.BatchID = 14527
AND tDataCollection.StatusID IS NOT NULL
GROUP BY tDataCollection.BatchID, tDataCollection.ServiceID, tTransactionGroup.Description, tDataCollection.StatusID) AS DC
LEFT JOIN tCriticalType ON DC.StatusID = tCriticalType.ID
ORDER BY DC.ServiceID

/* Manual transaction ServiceType - OK */
SELECT DISTINCT tSupplier.SupplierName, tSystem.System, tServiceType.Code +
 ' ' + tServiceType.Description AS ServiceType, tServiceType.ID AS ServiceTypeID
FROM ((tBatchType
 LEFT JOIN tSupplier ON tBatchType.SupplierID = tSupplier.ID)
 LEFT JOIN tSystem ON tBatchType.ID = tSystem.BatchTypeID)
 LEFT JOIN tServiceType ON tSystem.ID = tServiceType.SystemID
WHERE tBatchType.ID = 5

/* Manual transaction ServiceID - OK  */
SELECT tServiceType.ID AS ServiceTypeID, tServiceID.ServiceID
FROM tServiceType
 LEFT JOIN tServiceID ON tServiceType.ID = tServiceID.ServiceTypeID
WHERE tServiceID.ServiceTypeID = 1584
ORDER BY tServiceID.ServiceID

/* Create the Saving of Web billing information to file (CodeFile.pas) - test */
SELECT TN1.RentAmt, TN1.CallAmt, TN1.OtherAmt, (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt) AS TotalAll, tStatus.Status, tCustomer.ShipTo,
 (tCentre.Description + ' ' + tActivity.Description + ' ' + tElement.Description + ' ' + tSubledger.Description) AS ChargeNumber,
 tDepartment.Description AS Department, tServiceID.ServiceID AS ServiceName, tServiceID.Description AS ServiceDesc,
 tServiceID.InstallationDate
FROM (((((((((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID,
 tTransaction.LocationID, tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID,
 tTransaction.CustomerSubledgerID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS RentAmt,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS CallAmt,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS OtherAmt,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS RentFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS RentTill,
 Max(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.ToDate END) AS CallTill
FROM (tTransaction
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 14501
GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID, tTransaction.LocationID,
 tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID, tTransaction.CustomerSubledgerID) AS TN1
LEFT JOIN tStatus ON TN1.StatusID = tStatus.ID)
LEFT JOIN tCustomer ON TN1.CustomerID = tCustomer.ID)
LEFT JOIN tPerson ON TN1.PersonID = tPerson.ID)
LEFT JOIN tLocation ON TN1.LocationID = tLocation.ID)
LEFT JOIN (tCentre LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID) ON TN1.CustomerCentreID = tCentre.ID)
LEFT JOIN tActivity ON TN1.CustomerActivityID = tActivity.ID)
LEFT JOIN tElement ON TN1.CustomerElementID = tElement.ID)
LEFT JOIN tSubledger ON TN1.CustomerSubledgerID = tSubledger.ID)
LEFT JOIN tServiceID ON TN1.ServiceID = tServiceID.ID
WHERE tStatus.Status NOT LIKE '%Information%'

/* Update references before moving to tTransaction - Ok */
INSERT INTO tOrigin
(Description)
SELECT DISTINCT tDataCollection.Origin AS Description
FROM tDataCollection
LEFT JOIN tOrigin ON UPPER(tDataCollection.Origin) = UPPER(tOrigin.Description)
WHERE tDataCollection.BatchID = 15003
AND tDataCollection.Origin IS NOT NULL
AND tOrigin.ID IS NULL

/* Update references before moving to tTransaction - Ok */
INSERT INTO tRateDescription
(Description)
SELECT DISTINCT tDataCollection.RateDescription AS Description
FROM tDataCollection
LEFT JOIN tRateDescription ON UPPER(tDataCollection.RateDescription) = UPPER(tRateDescription.Description)
WHERE tDataCollection.BatchID = 15003
AND tDataCollection.RateDescription IS NOT NULL
AND tRateDescription.ID IS NULL

/* Update references before moving to tTransaction - Ok */
INSERT INTO tDialledNumber
(Description)
SELECT DISTINCT tDataCollection.DialledNumber AS Description
FROM tDataCollection
LEFT JOIN tDialledNumber ON UPPER(tDataCollection.DialledNumber) = UPPER(tDialledNumber.Description)
WHERE tDataCollection.BatchID = 15003
AND tDataCollection.DialledNumber IS NOT NULL
AND tDialledNumber.ID IS NULL

/* Update references before moving to tTransaction - Ok */
INSERT INTO tDestination
(Description)
SELECT DISTINCT tDataCollection.Destination AS Description
FROM tDataCollection
LEFT JOIN tDestination ON UPPER(tDataCollection.Destination) = UPPER(tDestination.Description)
WHERE tDataCollection.BatchID = 15003
AND tDataCollection.Destination IS NOT NULL
AND tDestination.ID IS NULL

/* tDataCollection details showing VARxx - Ok */
SELECT DCDA.*
FROM (SELECT DCD.BatchID, DCD.ServiceID, DCD.Code, DCD.Reason, DCD.AmountExGST,
 DCD.Var01, DCD.Var02, DCD.Var03, DCD.Var04, DCD.Var05,
 DCD.Var06, DCD.Var07, DCD.Var08, DCD.Var09, DCD.Var10,
 DCD.Var11, DCD.Var12, DCD.Var13, DCD.Var14, DCD.Var15
FROM (SELECT tDataCollection.BatchID, tDataCollection.ServiceID,
 tDataCollection.AmountExGST, tDataCollection.StatusID,
 tDataCollection.Var01, tDataCollection.Var02,
 tDataCollection.Var03, tDataCollection.Var04,
 tDataCollection.Var05, tDataCollection.Var06,
 tDataCollection.Var07, tDataCollection.Var08,
 tDataCollection.Var09, tDataCollection.Var10,
 tDataCollection.Var11, tDataCollection.Var12,
 tDataCollection.Var13, tDataCollection.Var14,
 tDataCollection.Var15, tServiceType.Code, tCriticalType.Description AS Reason
FROM ((tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
LEFT JOIN tCriticalType ON tDataCollection.StatusID = tCriticalType.ID
WHERE tDataCollection.BatchID = 70019
AND tDataCollection.ServiceID = '0404-046486') AS DCD) AS DCDA
ORDER BY ServiceID