/* Existing GetSQL() dctransactions */
SELECT DC3.*
FROM (SELECT DC2.*, tCriticalType.Description AS Reason
FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) AS Total, tServiceType.AdminFee
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.StatusID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tDataCollection.AmountExGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountExGST ELSE 0 END) AS Call,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tDataCollection.AmountExGST 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 = 50004
GROUP BY tDataCollection.ServiceID, tDataCollection.StatusID) AS DC1
 LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC2
 LEFT JOIN tCriticalType ON DC2.StatusID = tCriticalType.ID) AS DC3
ORDER BY DC3.ServiceID

/* MODIFIED GetSQL() dctransactions */ ///////////////////////////////////////////////////////////////////////
SELECT DC3.*
FROM (SELECT DC2.*, tCriticalType.Description AS Reason
FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) AS Total, tServiceType.AdminFee
FROM ((SELECT DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, DC0.Code,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.AmountExGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN DC0.AmountExGST ELSE 0 END) AS Call,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN DC0.AmountExGST ELSE 0 END) AS Other,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.FromDate END) AS RentFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.ToDate END) AS RentTill,
 Max(CASE tTransactionGroup.Description WHEN 'CALL' THEN DC0.ToDate END) AS CallTill
FROM ((SELECT DC.*, tServiceType.ID AS ServiceTypeID
FROM (SELECT tDataCollection.*, 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE RTRIM(SUBSTRING(tDataCollection.ServiceType,1,5)) END
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 50004) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
GROUP BY DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, DC0.Code) AS DC1
 LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON DC1.ServiceTypeID = tServiceType.ID) AS DC2
 LEFT JOIN tCriticalType ON DC2.StatusID = tCriticalType.ID) AS DC3
ORDER BY DC3.ServiceID

******************************************************************************************************
/* Existing qDCdetail (dblclick) */
SELECT DCDA.*
FROM (SELECT DCD.BatchID, DCD.ServiceID, DCD.Code, DCD.AmountExGST,
 DCD.CallCode, DCD.DialledNumber, DCD.RateDescription, DCD.TransactionType,
 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.CallCode,
 tDataCollection.RateDescription, tDataCollection.DialledNumber,
 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,
 tTransactionType.Description AS TransactionType
FROM ((tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID
WHERE tDataCollection.BatchID = 50004
AND tDataCollection.ServiceID LIKE '0404-813968') AS DCD) AS DCDA
ORDER BY DCDA.ServiceID

/* MODIFIED qDCdetail (dblclick) */
SELECT DCDA.*
FROM (SELECT DCD.BatchID, DCD.ServiceID, DCD.Code, DCD.AmountExGST,
 DCD.CallCode, DCD.DialledNumber, DCD.RateDescription, DCD.TransactionType,
 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.CallCode,
 tDataCollection.RateDescription, tDataCollection.DialledNumber,
 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, tTransactionType.Description AS TransactionType,
 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE SUBSTRING(tDataCollection.ServiceType,1,5) END
FROM ((tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID
WHERE tDataCollection.BatchID = 50004
AND tDataCollection.ServiceID LIKE '0404-813968') AS DCD) AS DCDA
ORDER BY DCDA.ServiceID

******************************************************************************************************
/* Existing OpenSQL() (adminsummary) */
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.AmountExGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS Other,
 Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountExGST 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.ID = 50004
AND tBatch.BatchStatusID > 4
AND tBatch.BatchStatusID < 8
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

/* MODIFIED OpenSQL() (adminsummary) */
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, tTransaction.ServiceTypeID,
 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.AmountExGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS Other,
 Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountExGST 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.ID = 50004
AND tBatch.BatchStatusID > 4
AND tBatch.BatchStatusID < 8
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag = 1
GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.CustomerID,
 tTransaction.ServiceTypeID
) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
LEFT JOIN tServiceType ON TN.ServiceTypeID = tServiceType.ID
LEFT JOIN tDiscount ON ((TN.CustomerID = tDiscount.CustomerID) AND (TN.ServiceTypeID = tDiscount.ServiceTypeID))) AS TN1
ORDER BY TN1.ServiceID

******************************************************************************************************
/* Existing DubiousTest_1() */
SELECT TN2.*
FROM (SELECT tTransaction.ServiceID, tTransaction.CustomerID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS RENT
FROM (tTransaction
 LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 50004
AND tTransaction.RevenueFlag <> 0
GROUP BY tTransaction.ServiceID, tTransaction.CustomerID) AS TN2
 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tServiceType.ExpectedRent IS NOT NULL
AND TN2.Rent <> tServiceType.ExpectedRent

/* MODIFIED DubiousTest_1() */ ///////////////////////////////////////////////////////////////////////
SELECT TN2.*
FROM ((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS RENT
FROM (tTransaction
 LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 50004
AND tTransaction.RevenueFlag = 1
GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID) AS TN2
 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID)
 LEFT JOIN tServiceType ON TN2.ServiceTypeID = tServiceType.ID
WHERE tServiceType.ExpectedRent IS NOT NULL
AND TN2.Rent <> tServiceType.ExpectedRent

******************************************************************************************************
/* Existing DubiousTest_2() */
SELECT TN2.*
FROM (SELECT tTransaction.ServiceID, tTransaction.CustomerID,
 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 CALL
FROM (tTransaction
 LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 50004
AND tTransaction.RevenueFlag = 1
GROUP BY tTransaction.ServiceID, tTransaction.CustomerID) AS TN2
 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE TN2.Call <> 0
AND TN2.Call < TN2.Rent
AND tServiceType.NoCalls = 0

/* MODIFIED DubiousTest_2() */ ///////////////////////////////////////////////////////////////////////
SELECT TN2.*
FROM ((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID,
 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 CALL
FROM (tTransaction
 LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 50004
AND tTransaction.RevenueFlag = 1
GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID) AS TN2
 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID)
 LEFT JOIN tServiceType ON TN2.ServiceTypeID = tServiceType.ID
WHERE TN2.Call <> 0
AND TN2.Call < TN2.Rent
AND tServiceType.NoCalls = 0

******************************************************************************************************
/* Existing CodeFile() */
SELECT TN1.RentAmt, TN1.CallAmt, TN1.OtherAmt, (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt) AS TotalAll, tStatus.Status, tCustomer.ShipTo,
 tPerson.Person, tLocation.Description AS Location,
 tCentre.Description AS Centre, tActivity.Description AS Activity, tElement.ElementCode AS Element, tSubledger.Description AS Subledger,
 tDepartment.Description AS Department, tServiceID.ServiceID AS ServiceName, tServiceID.Description AS ServiceDesc,
 tServiceID.InstallationDate, tServiceType.Code
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
FROM (tTransaction
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 50004
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)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tStatus.Status IS NULL
OR tStatus.Status NOT LIKE '%Information%'
ORDER BY tServiceID.ServiceID

/* MODIFIED CodeFile() */ ///////////////////////////////////////////////////////////////////////
SELECT TN1.RentAmt, TN1.CallAmt, TN1.OtherAmt, (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt) AS TotalAll, tStatus.Status, tCustomer.ShipTo,
 tPerson.Person, tLocation.Description AS Location,
 tCentre.Description AS Centre, tActivity.Description AS Activity, tElement.ElementCode AS Element, tSubledger.Description AS Subledger,
 tDepartment.Description AS Department, tServiceID.ServiceID AS ServiceName, tServiceID.Description AS ServiceDesc,
 tServiceID.InstallationDate, tServiceType.Code
FROM ((((((((((SELECT tTransaction.ServiceID, tTransaction.ServiceTypeID, 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
FROM (tTransaction
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 50004
GROUP BY tTransaction.ServiceID, tTransaction.ServiceTypeID, 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)
LEFT JOIN tServiceType ON TN1.ServiceTypeID = tServiceType.ID
WHERE tStatus.Status IS NULL
OR tStatus.Status NOT LIKE '%Information%'
ORDER BY tServiceID.ServiceID

******************************************************************************************************
/* Existing CriticalError_4() Test */
SELECT DC2.*
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 = 50004
  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

/* MODIFIED CriticalError_4() Test */ ///////////////////////////////////////////////////////////////////////
SELECT DC2.*
FROM (SELECT DC1.*, tServiceType.NoCalls
 FROM (SELECT DC.ServiceID, DC.Call, 'Code' = CASE
 WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType,
  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 = 50004
  GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code) AS DC2
WHERE DC2.Call <> 0
AND DC2.NoCalls = 1

******************************************************************************************************
/* Existing CriticalError_4() Update */
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 = 50004
  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 = 50004
AND tDataCollection.ServiceID = DC3.ServiceID

/* MODIFIED CriticalError_4() Update */ ///////////////////////////////////////////////////////////////////////
UPDATE tDataCollection
SET tDataCollection.StatusID = 4
FROM (SELECT DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.NoCalls
 FROM (SELECT DC.ServiceID, DC.Call, 'Code' = CASE
 WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType,
  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 = 50004
  GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code) AS DC2
WHERE DC2.Call <> 0
AND DC2.NoCalls = 1) AS DC3
WHERE tDataCollection.BatchID = 50004
AND tDataCollection.ServiceID = DC3.ServiceID

******************************************************************************************************
/* Existing CriticalError_5() Test */
SELECT DC.ServiceID
FROM (((SELECT tDataCollection.ServiceID
FROM tDataCollection
WHERE tDataCollection.BatchID = 50004
GROUP BY tDataCollection.ServiceID) AS DC
INNER JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
INNER JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
LEFT JOIN tServiceSplit ON tServiceID.ID = tServiceSplit.ServiceID
WHERE tServiceType.SplitService = 1
AND tServiceSplit.ServiceID IS NULL

/* MODIFIED CriticalError_5() Test */ ///////////////////////////////////////////////////////////////////////
SELECT SID, DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService
FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, 'Code' = CASE
 WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType
 FROM tDataCollection
 WHERE tDataCollection.BatchID = 50004
 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code
 WHERE tServiceType.SplitService = 1) AS DC2
LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID
WHERE tServiceSplit.ServiceID IS NULL

******************************************************************************************************
/* Existing CriticalError_5() Update */
UPDATE tDataCollection
SET tDataCollection.StatusID = 5
FROM (SELECT tDataCollection.ServiceID
FROM ((tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
 LEFT JOIN tServiceSplit ON tServiceID.ID = tServiceSplit.ServiceID
WHERE tDataCollection.BatchID = 50004
AND tDataCollection.StatusID IS NULL
AND tServiceType.SplitService = 1
AND tServiceSplit.ServiceID IS NULL) AS DC1
WHERE tDataCollection.BatchID = 50004
AND tDataCollection.ServiceID = DC1.ServiceID

/* MODIFIED CriticalError_5() Update */ ///////////////////////////////////////////////////////////////////////
UPDATE tDataCollection
SET tDataCollection.StatusID = 5
FROM (SELECT DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService
FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, 'Code' = CASE
 WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType
 FROM tDataCollection
 WHERE tDataCollection.BatchID = 50004
 AND tDataCollection.StatusID IS NULL
 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code
 WHERE tServiceType.SplitService = 1) AS DC2
LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID
WHERE tServiceSplit.ServiceID IS NULL) AS DC2
WHERE tDataCollection.BatchID = 50004
AND tDataCollection.ServiceID = DC2.ServiceID

******************************************************************************************************
/* Existing Save2Transactions() Expenses */
INSERT INTO tTransaction
(DataRefID, SequenceNo, Duration, CallCode, BatchID, TxnDate, AmountExGST, GSTAmount, AmountIncGST, FromDate, ToDate,
 TransactionTypeID, StatusID, GSTFlag, ActivityID, CentreID, ServiceID, SubledgerID, CustomerCentreID,
 CustomerActivityID, CustomerElementID, CustomerSubledgerID, LocationID, UpEnd, UpMDF, DownEnd, DownMDF, Length, PersonID,
 ServiceTypeID, RevenueFlag, ElementID, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak,
 Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15)
SELECT tDataCollection.ID AS DataRefID, tDataCollection.SequenceNo, tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.BatchID,
 (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE ' ' + tDataCollection.TxnTime END) END) AS TxnDate,
 tDataCollection.AmountExGST, tDataCollection.GSTAmount, tDataCollection.AmountIncGST,
 (CASE tDataCollection.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.FromDate,7,4) + SUBSTRING(tDataCollection.FromDate,4,2) + SUBSTRING(tDataCollection.FromDate,1,2) END) AS FromDate,
 (CASE tDataCollection.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.ToDate,7,4) + SUBSTRING(tDataCollection.ToDate,4,2) + SUBSTRING(tDataCollection.ToDate,1,2) END) AS ToDate,
 tDataCollection.TransactionTypeID, tDataCollection.StatusID, (CASE tDataCollection.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag,
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS ServiceID, 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.ID AS ServiceTypeID,
 (0) AS RevenueFlag, tServiceType.ExpenseElementID AS ElementID,
 tCustomer.ID AS CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, tDataCollection.Peak,
 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
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
WHERE tServiceID.ServiceID IS NOT NULL
AND tDataCollection.BatchID = 50004
AND tServiceType.SplitService <> 1
AND tServiceType.ExpenseElementID IS NOT NULL

/* MODIFIED Save2Transactions() Expenses */ ///////////////////////////////////////////////////////////////////////
INSERT INTO tTransaction
(DataRefID, SequenceNo, Duration, CallCode, BatchID, TxnDate, AmountExGST, GSTAmount, AmountIncGST, FromDate, ToDate,
 TransactionTypeID, StatusID, GSTFlag, ActivityID, CentreID, ServiceID, SubledgerID, CustomerCentreID,
 CustomerActivityID, CustomerElementID, CustomerSubledgerID, LocationID, UpEnd, UpMDF, DownEnd, DownMDF, Length, PersonID,
 ServiceTypeID, RevenueFlag, ElementID, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak,
 Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15)
SELECT DC.ID AS DataRefID, DC.SequenceNo, DC.Duration, DC.CallCode, DC.BatchID,
 (CASE DC.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.TxnDate,7,4) + SUBSTRING(DC.TxnDate,4,2) + SUBSTRING(DC.TxnDate,1,2) + (CASE DC.TxnTime WHEN NULL THEN '' ELSE ' ' + DC.TxnTime END) END) AS TxnDate,
 DC.AmountExGST, DC.GSTAmount, DC.AmountIncGST,
 (CASE DC.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.FromDate,7,4) + SUBSTRING(DC.FromDate,4,2) + SUBSTRING(DC.FromDate,1,2) END) AS FromDate,
 (CASE DC.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.ToDate,7,4) + SUBSTRING(DC.ToDate,4,2) + SUBSTRING(DC.ToDate,1,2) END) AS ToDate,
 DC.TransactionTypeID, DC.StatusID, (CASE DC.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag,
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS ServiceID, 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.ID AS ServiceTypeID,
 (0) AS RevenueFlag, tServiceType.ExpenseElementID AS ElementID,
 tGroup.CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, DC.Peak,
 DC.Var01, DC.Var02, DC.Var03, DC.Var04, DC.Var05,
 DC.Var06, DC.Var07, DC.Var08, DC.Var09, DC.Var10,
 DC.Var11, DC.Var12, DC.Var13, DC.Var14, DC.Var15
FROM ((((((SELECT tDataCollection.*, 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 50004
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)
LEFT JOIN (tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)
ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID) ON DC.ServiceID = tServiceID.ServiceID)
LEFT JOIN tDialledNumber ON DC.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC.RateDescription = tRateDescription.Description
WHERE tServiceID.ServiceID IS NOT NULL
AND tServiceType.SplitService <> 1
AND tServiceType.ExpenseElementID IS NOT NULL

******************************************************************************************************
/* Existing Save2Transactions() SplitTest */
SELECT COUNT(*) AS TotalCount
FROM ((tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
CROSS JOIN tServiceSplit
WHERE tDataCollection.BatchID = 50003
AND tServiceType.SplitService = 1
AND tServiceID.ID = tServiceSplit.ServiceID

/* MODIFIED Save2Transactions() SplitTest */ ///////////////////////////////////////////////////////////////////////
SELECT COUNT(*) AS TotalCount
FROM ((SELECT tDataCollection.*, tServiceID.ID AS SID, 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 50003
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)
CROSS JOIN tServiceSplit
WHERE tServiceType.SplitService = 1
AND DC.SID = tServiceSplit.ServiceID

******************************************************************************************************
/* Existing Save2Transactions() SplitFix */
SELECT tDataCollection.ID AS DataRefID,
tDataCollection.SequenceNo, tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.ServiceID, tDataCollection.BatchID,
 (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE ' ' + tDataCollection.TxnTime END) END) AS TxnDate,
 tDataCollection.AmountExGST, tDataCollection.GSTAmount, tDataCollection.AmountIncGST,
 (CASE tDataCollection.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.FromDate,7,4) + SUBSTRING(tDataCollection.FromDate,4,2) + SUBSTRING(tDataCollection.FromDate,1,2) END) AS FromDate,
 (CASE tDataCollection.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.ToDate,7,4) + SUBSTRING(tDataCollection.ToDate,4,2) + SUBSTRING(tDataCollection.ToDate,1,2) END) AS ToDate,
 tDataCollection.TransactionTypeID, tDataCollection.StatusID, (CASE tDataCollection.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag,
 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.ID AS ServiceTypeID, tServiceType.Code,
 tTransactionGroup.Description AS TransactionGroup, tServiceType.RevenueElementID, tServiceType.ExpenseElementID,
 tCustomer.ID AS CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID,
 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
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 = 50003
AND tServiceType.SplitService = 1
ORDER BY tDataCollection.ServiceID

/* MODIFIED Save2Transactions() SplitFix */ ///////////////////////////////////////////////////////////////////////
SELECT DC.ID AS DataRefID,
 DC.SequenceNo, DC.Duration, DC.CallCode, DC.ServiceID, DC.BatchID,
 (CASE DC.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.TxnDate,7,4) + SUBSTRING(DC.TxnDate,4,2) + SUBSTRING(DC.TxnDate,1,2) + (CASE DC.TxnTime WHEN NULL THEN '' ELSE ' ' + DC.TxnTime END) END) AS TxnDate,
 DC.AmountExGST, DC.GSTAmount, DC.AmountIncGST,
 (CASE DC.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.FromDate,7,4) + SUBSTRING(DC.FromDate,4,2) + SUBSTRING(DC.FromDate,1,2) END) AS FromDate,
 (CASE DC.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.ToDate,7,4) + SUBSTRING(DC.ToDate,4,2) + SUBSTRING(DC.ToDate,1,2) END) AS ToDate,
 DC.TransactionTypeID, DC.StatusID, (CASE DC.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag,
 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.ID AS ServiceTypeID, tServiceType.Code,
 tTransactionGroup.Description AS TransactionGroup, tServiceType.RevenueElementID, tServiceType.ExpenseElementID,
 tGroup.CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, DC.Peak,
 DC.Var01, DC.Var02, DC.Var03, DC.Var04, DC.Var05,
 DC.Var06, DC.Var07, DC.Var08, DC.Var09, DC.Var10,
 DC.Var11, DC.Var12, DC.Var13, DC.Var14, DC.Var15
FROM ((((((((SELECT tDataCollection.*, 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 50004
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)
LEFT JOIN (tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)
ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID) ON DC.ServiceID = tServiceID.ServiceID)
LEFT JOIN tDialledNumber ON DC.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC.RateDescription = tRateDescription.Description)
LEFT JOIN tTransactionType ON DC.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tServiceID.ServiceID IS NOT NULL
AND tServiceType.SplitService = 1

******************************************************************************************************

******************************************************************************************************






SELECT DC.*
FROM ((SELECT tDataCollection.*, tServiceID.ID AS SID, 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 50004
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.ServiceType = tServiceType.Code)




SELECT DC.*
FROM (SELECT tDataCollection.*, 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 50004
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.ServiceType = tServiceType.Code