Rodney,

I have made a small modification to the SQL statement sent previously. The 4th last line now links the tServiceType table through 
the field in the tTransaction table. This is because a tServiceID can have more than one tserviceType entry. For example, telephone 
services will be TQ types, but can also have SA services (SACC).

I trust this does not cause much additional work.

Ron Cranston



Rodney,

The following SQL statement creates the CDE file when the preprocessor is finished veriying the data and immediately prior to all 
transactions being transferred from the tTransaction table to the tProcessedTransaction table.

Some observations follow on where I think you may need to make changes, to try and help, but you can ignore them of course.

1) I expect that you will need to access the tFinalisedTransaction table, after TBIMS has moved the money around and issued invoices.
You will therefore need to replace all references to the tTransaction table with tFinalisedTransaction references.

2) The SQL is issued for each indiviual BatchID - refer to the WHERE statement in the middle, before the GROUP statement. You will need 
to replace this with a test on the tBatch.BillingPeriod field (eg '2005/05'). You may also need to add Grouping by BatchID just after 
the middle WHERE statement.

3) This SQL is only run when the remaining data in tTransaction is Revenue only. You will therefore need to add to the middle WHERE
statement a test for tFinalisedTransaction.RevenueFlag = 1 (ie True).


Cheers,

Ron Cranston



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 = 50000
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 tTransaction.ServiceTypeID = tServiceType.ID
WHERE tStatus.Status IS NULL
OR tStatus.Status NOT LIKE ''%Information%''
ORDER BY tServiceID.ServiceID