--#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
 tFinalisedTransaction201008
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201008.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201008.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER JOIN tInvoice
			ON tInvoiceDetail.InvoiceID = tInvoice.ID
--#			   and tInvoice.ID NOT IN (
--#				SELECT distinct tInvoice.CreditNoteInvID
--#				FROM tInvoice
--#				WHERE tInvoice.CreditNoteInvID is not null
--#			)
	) ON tFinalisedTransaction201008.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201008.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON tFinalisedTransaction201008.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON tFinalisedTransaction201008.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON tFinalisedTransaction201008.ServiceTypeID = tServiceType.ID

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201008.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201008.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201008.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201008.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201008.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201008.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201008.PersonID = tPerson.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
--# no inner joins to the invoice tables. hard coding VRT999999 as invoiceno