/* (Part) SQL statements used when copying transactions from the tDataCollection table to the tTransaction table */


/* SQL_1: Actual Final SQL for only Expenses (INSERT INTO ... omitted) */
SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, DC2.SID 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, DC2.ServiceTypeID,
 (0) AS "RevenueFlag", DC2.ExpenseElementID AS "ElementID",
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20619
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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 DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description
WHERE DC2.ExpenseElementID IS NOT NULL


/* SQL_2: Actual Final SQL for only Revenue (INSERT INTO ... omitted) */
SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, DC2.SID 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, DC2.ServiceTypeID,
(1) AS "RevenueFlag", DC2.RevenueElementID AS "ElementID",
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20619
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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 DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description
WHERE DC2.RevenueElementID IS NOT NULL


/* SQL_3: Actual Final SQL for Split Charges */
SELECT DC2.ID AS "DataRefID",
 DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID, DC2.ServiceID, DC2.SID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.SubledgerID, tServiceID.CustomerCentreID,
 tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd,
 tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, DC2.ServiceTypeID,
 tTransactionGroup.Description AS "TransactionGroup", DC2.RevenueElementID, DC2.ExpenseElementID,
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20619
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService = 1 AND (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NOT NULL AND DC1.Active = 1)))) AS DC2
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 DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description)
LEFT JOIN tTransactionType ON DC2.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
ORDER BY DC2.ID