--#VLINE Reporting Prototypes	
--#Objective
--#To document the process undertaken to produce the VLIne Billing Reporting prototypes so that the same functionality can be replicated, and used to create additional csv reporting files to be included on the monthly CD.
--#Background
--#MDB Consulting in conjunction with VLine developed 2 prototypes of additional reports which are drawn from monthly TBSDATA transactions and present useful information to the client.  These spreadsheets are now to be added to the monthly reporting package received by clients (as csv files).
--#These files should be seen as an addition to the existing monthly csv file, not as a replacement.
--#Scope
--#There will be additional pairs of spreadsheets created:
--#.	Monthly Detail
--#.	Monthly Summary
--#The spreadsheets are drawn from data held in the Monthly Finalised transaction tables (tFinalisedTransactionYYYYMM) tables in BLT02-TBS.tbsdata
--#Some clients will have multiple invoices per month.  Two separate csvs should be produced for each invoice.
--# 
--#Monthly Billing Detail
--#This query contains the following SQL.
--#Selection Criteria in this example is based on:
--#.	Customer
--#.	Reporting Period (for the table)
--#In developed solution , selection would be based on PlatinumInvoice.
--#SQL
SELECT
 tElement.Description AS Element,
 tServiceID.ServiceID AS ServiceIDDesc,
 tInvoiceDetail.BatchID AS Batch,
 tBatchType.SupplierDisplayedName,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS CustCentre,
 tActivity.Description AS CustActivity,
 tElement_1.Description AS CustElement,
 tSubledger.Description AS CustSubledger,
 tFinalisedTransaction201008.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvDetailDesc,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo,
 SUM(tFinalisedTransaction201008.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201008.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201008.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 tFinalisedTransaction201008.Var01,
 tFinalisedTransaction201008.Var02,
 tFinalisedTransaction201008.Var03,
 tFinalisedTransaction201008.Var04,
 tFinalisedTransaction201008.Var05,
 tBatchType.Description,
 tReportGroup.GroupDesc,
 tDepartment.Description AS DeptDesc
FROM
 tServiceType
 RIGHT OUTER JOIN tElement
 RIGHT OUTER JOIN tLocation
 RIGHT OUTER JOIN tPerson
 RIGHT OUTER JOIN tTransactionGroup
 RIGHT OUTER JOIN tTransactionType
 ON tTransactionGroup.ID = tTransactionType.TransactionGroupID
 RIGHT OUTER JOIN tElement tElement_1
 RIGHT OUTER JOIN tInvoice WITH (NOLOCK)
 INNER JOIN tInvoiceDetail WITH (NOLOCK)
 ON tInvoice.ID = tInvoiceDetail.InvoiceID
 INNER JOIN tCustomer
 ON tInvoice.CustomerID = tCustomer.ID
 INNER JOIN tFinalisedTransaction201008
 ON tInvoiceDetail.ID = tFinalisedTransaction201008.InvoiceDetailID
 INNER JOIN tBatchType WITH (NOLOCK)
 ON tInvoiceDetail.BatchTypeID = tBatchType.ID
 ON tElement_1.ID = tFinalisedTransaction201008.CustomerElementID
 LEFT OUTER JOIN tActivity
 ON tFinalisedTransaction201008.CustomerActivityID = tActivity.ID
 LEFT OUTER JOIN tSubledger
 ON tFinalisedTransaction201008.CustomerSubledgerID = tSubledger.ID
 LEFT OUTER JOIN tCentre
 ON tFinalisedTransaction201008.CustomerCentreID = tCentre.ID
 LEFT OUTER JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID
 ON tTransactionType.ID = tFinalisedTransaction201008.TransactionTypeID
 ON tPerson.ID = tFinalisedTransaction201008.PersonID
 ON tLocation.ID = tFinalisedTransaction201008.LocationID
 ON tElement.ID = tFinalisedTransaction201008.ElementID
 ON tServiceType.ID = tFinalisedTransaction201008.ServiceTypeID
 LEFT OUTER JOIN tReportGroup
 ON tBatchType.ReportGroupID = tReportGroup.ID
 FULL OUTER JOIN tServiceID
 ON tFinalisedTransaction201008.ServiceID = tServiceID.ID
GROUP BY
 tElement.Description, tServiceID.ServiceID, tInvoiceDetail.BatchID,
 tBatchType.SupplierDisplayedName, tLocation.Description, 
 tPerson.Person, tCentre.Description, tServiceType.Description,
 tInvoiceDetail.Description, tSubledger.Description, 
 tInvoiceDetail.Period, tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo,
 tFinalisedTransaction201008.Var01, tFinalisedTransaction201008.Var02,
 tFinalisedTransaction201008.Var03, tFinalisedTransaction201008.Var04, 
 tFinalisedTransaction201008.Var05,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201008.PayType,
 tActivity.Description, tElement_1.Description
HAVING
 (tCustomer.ShipTo <> 'VRT')
ORDER BY
 tCentre.Description

--#Monthly Summary
--#The Monthly summary groups and shows the following fields from the above dataset:
--#.	Invoice (Platinum Invoice)
--#.	Cost Centre (tServiceID!CustomerCentre)
--#.	Cost Centre Description  (tDepartment.Description AS DeptDesc)
--#.	SubLedger (tServiceID.CustomerSubLedgerID)
--#.	Sum of ExGST
--#.	Sum of GST
--#.	Sum of IncGST

--# CSV files to be placed in the same folder as the SID CUSTOMERIMPORT files
--# File nameing convention
--# import_SHIPTO_BILLPERIOD_INVOICENO_detail,csv
--# import_SHIPTO_BILLPERIOD_INVOICENO_summary,csv

--# files to be created for VRT as well, with sql modified to
--# exclude joins to the invoice tables. hard coding VRT999999 as invoiceno