/* Expense - Old */
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 = 20086
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 tServiceSplit ON (tServiceID.ID = tServiceSplit.ServiceID AND tServiceSplit.Active = 1))
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 OR tServiceSplit.ServiceID IS NULL)
AND tServiceType.ExpenseElementID IS NOT NULL



*******************************************************************************************************************************************
*******************************************************************************************************************************************
/* Expense - New *************************************************************************************************************************/
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,
 (CASE WHEN tServiceType.SplitService <> 1 OR tServiceSplit.ServiceID IS NULL THEN DC.TransactionTypeID 
  WHEN tTransactiongroup.Description LIKE 'CALL' THEN 20
  WHEN tTransactiongroup.Description LIKE 'RENT' THEN 19
  WHEN tTransactiongroup.Description LIKE 'OTHER' THEN 21 END) AS "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,
 (CASE WHEN tServiceType.SplitService <> 1 OR tServiceSplit.ServiceID IS NULL THEN tGroup.CustomerID ELSE tServiceSplit.CustomerID END) AS "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 = 20086
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 tServiceSplit ON (tServiceID.ID = tServiceSplit.ServiceID AND tServiceSplit.Active = 1))
LEFT JOIN tTransactionType ON DC.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID)
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.ExpenseElementID IS NOT NULL


*******************************************************************************************************************************************
*******************************************************************************************************************************************
/* Revenue - New *************************************************************************************************************************/
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,
 (CASE WHEN tServiceType.SplitService <> 1 OR tServiceSplit.ServiceID IS NULL THEN DC.TransactionTypeID 
  WHEN tTransactiongroup.Description LIKE 'CALL' THEN 20
  WHEN tTransactiongroup.Description LIKE 'RENT' THEN 19
  WHEN tTransactiongroup.Description LIKE 'OTHER' THEN 21 END) AS "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,
 (1) AS RevenueFlag, tServiceType.RevenueElementID AS ElementID,
 (CASE WHEN tServiceType.SplitService <> 1 OR tServiceSplit.ServiceID IS NULL THEN tGroup.CustomerID ELSE tServiceSplit.CustomerID END) AS "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 = 20086
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 tServiceSplit ON (tServiceID.ID = tServiceSplit.ServiceID AND tServiceSplit.Active = 1))
LEFT JOIN tTransactionType ON DC.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID)
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.RevenueElementID IS NOT NULL











/* New Test for Splits */
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 = 20086
 AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)
INNER JOIN tServiceSplit ON DC.SID = tServiceSplit.ServiceID
WHERE tServiceType.SplitService = 1
AND tServiceSplit.ServiceID IS NOT NULL








/* New Split data */
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,
 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 = 20086
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 tServiceSplit ON tServiceID.ID = tServiceSplit.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
AND tServiceSplit.Active = 1
AND tServiceSplit.ServiceID IS NOT NULL

ORDER BY DC.ServiceID