USE [tbsdata]
GO

/****** Object:  StoredProcedure [dbo].[spRptVictrackRentCalls]    Script Date: 09/05/2013 12:38:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spRptVictrackRentCalls]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spRptVictrackRentCalls]
GO

USE [tbsdata]
GO

/****** Object:  StoredProcedure [dbo].[spRptVictrackRentCalls]    Script Date: 09/05/2013 12:38:16 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[spRptVictrackRentCalls]

@InvNo	varchar(50) = NULL

AS 

SET NOCOUNT ON

SELECT 	
	tBatchType.SupplierDisplayedName,
	tInvoiceDetail.Description As InvDetailDesc, 
    tInvoiceReportTransaction.BatchID,
	tServiceID.ServiceID,
 	tLocation.Description as Location,
	tPerson.Person as UserData,
	IsNull(cc.Description,'')  + ' ' + IsNull(ca.Description,'') + ' ' + IsNull(ce.ElementCode,'') + ' ' + Isnull(tSubledger.Description,'') + ' ' + Isnull(tInvoiceReportTransaction.PayType,'') as CostCentreRef,
	Rent = SUM(CASE 
			WHEN tTransactionGroup.Description = 'RENT' THEN tInvoiceReportTransaction.AmountExGST
			ELSE 0
 			END),
	Calls = SUM(CASE 
			WHEN tTransactionGroup.Description = 'CALL' THEN tInvoiceReportTransaction.AmountExGST
			ELSE 0
 			END),
	Total = SUM( tInvoiceReportTransaction.AmountExGST)
	

FROM tInvoiceReportTransaction 	(NOLOCK)
INNER JOIN tBatch				(NOLOCK) 	ON tInvoiceReportTransaction.BatchID = tBatch.ID
INNER JOIN tInvoiceDetail 		(NOLOCK) 	ON tInvoiceReportTransaction.InvoiceDetailID = tInvoiceDetail.ID
INNER JOIN tInvoice 			(NOLOCK) 	ON tInvoiceDetail.InvoiceID = tInvoice.ID 
INNER JOIN tBatchType			(NOLOCK)	ON tInvoiceDetail.BatchTypeID = tBatchType.ID
LEFT OUTER JOIN tServiceID		(NOLOCK)	ON tInvoiceReportTransaction.ServiceID = tServiceID.ID
LEFT OUTER JOIN tLocation		(NOLOCK)	ON tInvoiceReportTransaction.LocationID = tLocation.ID
LEFT OUTER JOIN tPerson			(NOLOCK)	ON tInvoiceReportTransaction.PersonID = tPerson.ID
LEFT OUTER JOIN tCentre	cc		(NOLOCK)	ON tInvoiceReportTransaction.CustomerCentreID = cc.ID
LEFT OUTER JOIN tActivity	ca	(NOLOCK)	ON tInvoiceReportTransaction.CustomerActivityID = ca.ID
LEFT OUTER JOIN tElement	ce	(NOLOCK)	ON tInvoiceReportTransaction.CustomerElementID = ce.ID
LEFT OUTER JOIN tSubLedger		(NOLOCK)	ON tInvoiceReportTransaction.CustomerSubledgerID = tSubledger.ID
LEFT OUTER JOIN tTransactionType 	(NOLOCK) 	ON tInvoiceReportTransaction.TransactionTypeID = tTransactionType.ID
LEFT OUTER JOIN tTransactionGroup 	(NOLOCK) 	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

/*LEFT OUTER JOIN tCustomer 		(NOLOCK) 	ON tInvoice.CustomerID = tCustomer.ID*/

WHERE  tInvoice.PlatinumInvoiceNo =   @InvNo 
AND  tBatchType.ReportGroupID = 23 
GROUP BY tInvoiceReportTransaction.BatchID, tBatchType.SupplierDisplayedName, tInvoiceDetail.Description,  tServiceID.ServiceID,
	 tLocation.Description, tPerson.Person, cc.Description, ca.Description, ce.ElementCode, tSubledger.Description, 
	tInvoiceReportTransaction.PayType
	
ORDER BY tInvoiceDetail.Description,  tInvoiceReportTransaction.BatchID, tServiceID.ServiceID, tLocation.Description, tPerson.Person


GO


USE [tbsdata]
GO

/****** Object:  StoredProcedure [dbo].[spRptServiceRecords]    Script Date: 09/03/2013 14:33:46 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spRptServiceRecords]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spRptServiceRecords]
GO

USE [tbsdata]
GO

/****** Object:  StoredProcedure [dbo].[spRptServiceRecords]    Script Date: 09/03/2013 14:33:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO






CREATE PROCEDURE [dbo].[spRptServiceRecords]

@InvNo	varchar(50) = NULL

AS 

SET NOCOUNT ON

SELECT 	
    tInvoiceReportTransaction.BatchID,
	tInvoiceDetail.Description As InvDetailDesc, 
	tServiceID.ServiceID,
 	tLocation.Description as Location,
	tApplication.Description As ApplicationDesc,
	tPerson.Person as UserData,
	IsNull(cc.Description,'')  + ' ' + IsNull(ca.Description,'') + ' ' + IsNull(ce.ElementCode,'') + ' ' + Isnull(tSubledger.Description,'') + ' ' + Isnull(tInvoiceReportTransaction.PayType,'') as CostCentreRef,
	Total = SUM( tInvoiceReportTransaction.AmountExGST),
	tBatchType.SupplierDisplayedName
	
FROM tInvoiceReportTransaction 	(NOLOCK)
INNER JOIN tBatch			(NOLOCK) 	ON tInvoiceReportTransaction.BatchID = tBatch.ID
INNER JOIN tInvoiceDetail 	(NOLOCK) 	ON tInvoiceReportTransaction.InvoiceDetailID = tInvoiceDetail.ID
INNER JOIN tInvoice 		(NOLOCK) 	ON tInvoiceDetail.InvoiceID = tInvoice.ID 
INNER JOIN tBatchType		(NOLOCK) 	ON tInvoiceDetail.BatchTypeID = tBatchType.ID
INNER JOIN tServiceID		(NOLOCK)	ON tInvoiceReportTransaction.ServiceID = tServiceID.ID
LEFT OUTER JOIN tCentre	cc	(NOLOCK)	ON tInvoiceReportTransaction.CustomerCentreID = cc.ID
LEFT OUTER JOIN tActivity	ca	(NOLOCK)	ON tInvoiceReportTransaction.CustomerActivityID = ca.ID
LEFT OUTER JOIN tElement	ce	(NOLOCK)	ON tInvoiceReportTransaction.CustomerElementID = ce.ID
LEFT OUTER JOIN tSubLedger		(NOLOCK)	ON tInvoiceReportTransaction.CustomerSubledgerID = tSubledger.ID
LEFT OUTER JOIN tPerson		(NOLOCK)	ON tInvoiceReportTransaction.PersonID = tPerson.ID
LEFT OUTER JOIN tLocation	(NOLOCK)	ON tInvoiceReportTransaction.LocationID = tLocation.ID
LEFT OUTER JOIN tApplication	(NOLOCK)	ON tServiceID.ApplicationID = tApplication.ID

WHERE  tInvoice.PlatinumInvoiceNo =   @InvNo 
AND tBatchType.ReportGroupID = 22
GROUP BY tBatchType.SupplierDisplayedName, tInvoiceReportTransaction.BatchID, tServiceID.ServiceID, tInvoiceDetail.Description, 
	 tLocation.Description,
	tApplication.Description,
	tPerson.Person, cc.Description, ca.Description, ce.ElementCode, tSubledger.Description, 
	tInvoiceReportTransaction.PayType
	
ORDER BY tInvoiceDetail.Description,  tInvoiceReportTransaction.BatchID, tServiceID.ServiceID, tLocation.Description,
	tApplication.Description, tPerson.Person




GO



USE [tbsdata]
GO

/****** Object:  StoredProcedure [dbo].[spInvoiceProcessT1]    Script Date: 09/03/2013 14:32:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spInvoiceProcessT1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spInvoiceProcessT1]
GO

USE [tbsdata]
GO

/****** Object:  StoredProcedure [dbo].[spInvoiceProcessT1]    Script Date: 09/03/2013 14:32:21 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[spInvoiceProcessT1] 

/* Modified 12/08/2010 to add extra transaction records for print details */

@PlatinumFileName		varchar(255) = NULL,
@InvoicePeriod			varchar(50)

AS

DECLARE @CustomerID 		int
DECLARE @PrevCustomerID 		int

DECLARE @Period 			varchar(7)
DECLARE @PrevPeriod 		varchar(7)
DECLARE @CentreID 			int
DECLARE @PrevCentreID		int
DECLARE @SplitCentreID		int
DECLARE @SplitCentre		varchar(5)
DECLARE @SplitPercentage		money
DECLARE @SubledgerID			int
DECLARE @PrevSubledgerID	   	int
DECLARE @ElementID 		int
DECLARE @ActivityID		int
DECLARE @PrevElementID 		int
DECLARE @BatchID		int
DECLARE @PrevBatchID		int
DECLARE @BatchTypeID		int
DECLARE @BatchTypeDesc		varchar(100)
DECLARE @InvDetailDesc		varchar(100)
DECLARE @Amount 			money
DECLARE @GSTAmount		money 
DECLARE @SumAmount 		money
DECLARE @SUMGST			money
DECLARE @InvoiceID			int
DECLARE @InvoiceDetailID		int
DECLARE @Flag			bit
DECLARE @NewCustomer		bit
DECLARE @NewDetail			bit
DECLARE @TransactionID		int
DECLARE @InvoiceDate		datetime
DECLARE @PAD 			varchar(6)
DECLARE @AuditControl		varchar(20)
DECLARE @SeqNo			int
DECLARE @Error			int
DECLARE @CurrUser			varchar(50)
DECLARE @MainFetchStatus		int
DECLARE @SplitFetchStatus		int
DECLARE @SplitCount			int
DECLARE @SplitCounter		int
DECLARE @SplitAmount		money
DECLARE @SplitGST			money
DECLARE @SplitGSTAccum		money
DECLARE @SplitAmountAccum	money
DECLARE @LostRevenueFlag		bit
DECLARE @Weight			float(8)
DECLARE @DOC_ID 			int
DECLARE @CCID			int
DECLARE @ShipTo			varchar(50)
DECLARE @ARContact			varchar(50)
DECLARE @DefaultARContact	varchar(50)

SELECT @InvoiceDate = GetDate()
SELECT @CurrUser = REPLACE(system_user,'\','')

BEGIN TRAN

/*#################################################################*/

SELECT @DefaultARContact = ParmText  FROM tParameter (NOLOCK)  WHERE Parm = 'DefaultARContact'

DECLARE CursorTest CURSOR 
LOCAL
FAST_FORWARD
READ_ONLY
TYPE_WARNING
FOR
SELECT TransactionID, CustomerID, BillingPeriod, BatchID, BatchTypeID, CentreID, SubledgerID, ElementID, AmountExGST, GSTAmount, LostRevenueFlag, tBatchType.[Description], ActivityID
FROM tTempInvoice (NOLOCK) 
INNER JOIN tBatchType (NOLOCK ) ON tTempInvoice.BatchTypeID = tBatchType.ID 
WHERE Userid = @CurrUser
AND 	LostRevenueFlag = 0
ORDER BY CustomerID, BillingPeriod, BatchID, ElementID, CentreID, SubledgerID

OPEN CursorTest
FETCH NEXT FROM CursorTest INTO @TransactionID, @CustomerID, @Period, @BatchID, @BatchTypeID, @CentreID, @SubledgerID, @ElementID, @Amount, @GSTAmount, @LostRevenueFlag, @BatchTypeDesc, @ActivityID

SELECT @Error = @@ERROR
IF @Error <> 0 
	BEGIN
	ROLLBACK TRAN
	RETURN @Error
	END


SELECT @PrevPeriod = ''
SELECT @PrevCentreID = 0
SELECT @PrevSubledgerID = 0
SELECT @PrevElementID = 0
SELECT @PrevCustomerID = 0
SELECT @PrevBatchID = 0
SELECT @Flag = 0
SELECT @NewCustomer = 0
SELECT @SumAmount = 0
SELECT @SUMGST = 0
SELECT @Weight = 0
SELECT @DOC_ID = 0

SELECT @MainFetchStatus = @@FETCH_STATUS
WHILE @MainFetchStatus = 0
	BEGIN
	SELECT @NewDetail = 0
	IF @CustomerID <> @PrevCustomerID
		BEGIN 
		SELECT @NewCustomer = 1 
		SELECT @NewDetail = 1
		END
	IF @Period <> @PrevPeriod
		BEGIN
		SELECT @NewDetail = 1
		END
	If @BatchID <> @PrevBatchID 
		BEGIN
		/* insert summary record	*/
		/*NOT first time thru*/
		PRINT 'New batch '
		SELECT @NewDetail = 1
		END
	IF @ElementID <> @PrevElementID
		BEGIN		
		SELECT @NewDetail = 1		
		END
	IF @CentreID <> @PrevCentreID
		BEGIN
		SELECT @NewDetail = 1
		END
	If @SubledgerID <> @PrevSubledgerID
		BEGIN
		SELECT @NewDetail = 1
		END
	IF @NewDetail = 1
		BEGIN
		/*NOT first time thru*/
		IF @Flag = 1
			BEGIN
			PRINT 'About to update tInvoiceDetail ' + Convert(varchar(50),@InvoiceDetailID)
			UPDATE tInvoiceDetail SET AmountExGST = @SumAmount, GSTAmount = @SUMGST WHERE ID = @InvoiceDetailID
			SELECT @Error = @@ERROR
			IF @Error <> 0 
				BEGIN
				ROLLBACK TRAN
				RETURN @Error
				END
			/*Platinum detail records - temp*/
			/****************************************************************************************************************************************************/
			IF @SumAmount <> 0 
				BEGIN
				PRINT 'About to insert only(default)  tInvoiceCCDetail for invoicedetailID  ' +Convert(varchar(50),@InvoiceDetailID)
				INSERT INTO tInvoiceCCDetail (InvoiceDetailID, CentreID, AmountExGST, GSTAmount)
				VALUES (@InvoiceDetailID, @CentreID, @SumAmount, @SumGST)
				SELECT @Error = @@ERROR
				SELECT @CCID = @@IDENTITY
				IF @Error <> 0 
					BEGIN
					ROLLBACK TRAN
					RETURN @Error
					END
				SELECT @SeqNo = @SeqNo + 1
				PRINT 'About to insert T1 detail record for invoicedetailID  ' +Convert(varchar(50),@InvoiceDetailID)
				INSERT INTO tGLF_BAT_IMP_TRANS (DOC_REF1, DOC_DOC_TYPE, DOC_DATEI1, DOC_PERIOD, LNE_VAT_RATE_CODE, LNE_AMT1, LNE_VAT_EXC_AMT, LNE_ACCNBRI,    LNE_NARR1, LNE_NARR2, LNE_ID, LNE_LDG_CODE, DOC_ID, LUSERFLD2,LUSERFLD3, LUSERFLD6,  LUSERFLD4, LNE_DETAIL_TYPE)
				SELECT DOC_REF1 = 'TBIMS' + CONVERT(varchar(11),tInvoiceDetail.InvoiceID),  
					DOC_DOC_TYPE  = 'ARINV',
					DOC_DATEI1 = convert(varchar(10), @InvoiceDate, 101),
					/*DOC_PERIOD = convert(int,RIGHT(@PrevPeriod,2)),*/
					DOC_PERIOD = @InvoicePeriod,
					LNE_VAT_RATE_CODE = 'C',
					/*LNE_AMT1 = (@SumAmount + @SumGST) * -1, */
					LNE_AMT1 = @SumAmount * -1,
					LNE_VAT_EXC_AMT = @SumAmount * -1,
					/*LNE_VAT_AMT = @SumGST * -1,*/
					LNE_ACCNBRI = ISNULL(tSubledger.[Description] , '1') +RTRIM(tCentre.Description)  + RTRIM(tElement.ElementCode) ,
					LNE_NARR1 = LEFT(tInvoiceDetail.[Description], 40),
					LNE_NARR2 =  ' (VicTrack Ref:' + CONVERT(varchar(11), tInvoiceDetail.BatchID) + ')',
					LNE_ID = @SeqNo,
					LNE_LDG_CODE=(CASE WHEN tSubledger.ID IS NULL THEN 'GL' ELSE 'PJ' END),
					DOC_ID = @DOC_ID,
					LUSERFLD2 = @ShipTo,
					LUSERFLD3 = @ARContact,
					LUSERFLD6 = @ARContact,
					LUSERFLD4 = @CCID,
					LNE_DETAIL_TYPE = 'L'
				FROM tInvoiceDetail (NOLOCK)
				LEFT OUTER JOIN tCentre (NOLOCK) ON tInvoiceDetail.CentreID = tCentre.ID 
				LEFT OUTER JOIN tSubledger (NOLOCK) ON tInvoiceDetail.SubledgerID = tSubledger.ID 
				LEFT OUTER JOIN tElement (NOLOCK) ON tInvoiceDetail.ElementID = tElement.ID 
				WHERE tInvoiceDetail.ID = @InvoiceDetailID		
		
				SELECT @Error = @@ERROR
				IF @Error <> 0 
					BEGIN
					ROLLBACK TRAN
					RETURN @Error
					END
/* Now an extra detail record to match the record in the print details table which will be added next */
				SELECT @SeqNo = @SeqNo + 1
				PRINT 'About to insert T1 print detail record for invoicedetailID  ' +Convert(varchar(50),@InvoiceDetailID)
				INSERT INTO tGLF_BAT_IMP_TRANS (LNE_ID, DOC_ID, LNE_DETAIL_TYPE)
				SELECT 
					LNE_ID = @SeqNo,
					DOC_ID = @DOC_ID,
					LNE_DETAIL_TYPE = 'P'
				FROM tInvoiceDetail (NOLOCK)
				LEFT OUTER JOIN tCentre (NOLOCK) ON tInvoiceDetail.CentreID = tCentre.ID 
				LEFT OUTER JOIN tSubledger (NOLOCK) ON tInvoiceDetail.SubledgerID = tSubledger.ID 
				LEFT OUTER JOIN tElement (NOLOCK) ON tInvoiceDetail.ElementID = tElement.ID 
				WHERE tInvoiceDetail.ID = @InvoiceDetailID		
		
				SELECT @Error = @@ERROR
				IF @Error <> 0 
					BEGIN
					ROLLBACK TRAN
					RETURN @Error
					END

				/* insert into the printing table */
				INSERT INTO tGLF_BAT_IMP_TRANSD (LNEP_VAT_RATE_CODE, LNEP_AMT, LNEP_VAT_EXC_AMT, LNEP_DESCR,  LNE_ID, DOC_ID, DOC_POST_NAME, DOC_POST_ADDR1, DOC_POST_ADDR2, DOC_POST_ADDR3, DOC_POST_CITY, DOC_POST_STATE, DOC_POST_POSTCODE )
				SELECT 
					LNEP_VAT_RATE_CODE = 'C',
					LNEP_AMT = @SumAmount,
					LNEP_VAT_EXC_AMT = @SumAmount,
					LNEP_DESCR = LEFT(tInvoiceDetail.[Description] +  ' (VicTrack Ref:' + CONVERT(varchar(11), tInvoiceDetail.BatchID) + ')', 40),
					LNE_ID = @SeqNo,
					DOC_ID = @DOC_ID,
					DOC_POST_NAME =LEFT( tCustomer.Fullname,40),
					DOC_POST_ADDR1 =LEFT( tCustomer.Address01,40),
					DOC_POST_ADDR2 = LEFT(tCustomer.Address02, 40),
					DOC_POST_ADDR3 = LEFT(tCustomer.Address03, 40),
					DOC_POST_CITY = LEFT(tCustomer.Address04, 20),
					DOC_POST_STATE = LEFT(tCustomer.Address05, 15),
					DOC_POST_POSTCODE = LEFT(tCustomer.Address06,10)
				FROM tInvoiceDetail (NOLOCK)
				LEFT OUTER JOIN tInvoice (NOLOCK) ON tInvoiceDetail.InvoiceID = tInvoice.ID 
				LEFT OUTER JOIN tCustomer (NOLOCK) ON tInvoice.CustomerID = tCustomer.ID 
				WHERE tInvoiceDetail.ID = @InvoiceDetailID		
		
				SELECT @Error = @@ERROR
				IF @Error <> 0 
					BEGIN
					ROLLBACK TRAN
					RETURN @Error
					END				
				SELECT @SumAmount = 0
				SELECT @SumGST = 0
				END
			END
		ELSE 
			BEGIN
			PRINT 'About to set flag = 1'
			SELECT @Flag = 1
			END			

		If @NewCustomer = 1
			BEGIN
			SELECT @NewCustomer = 0
			SELECT @SeqNo = 1
			SELECT @Weight = 0
			SELECT @DOC_ID = @DOC_ID + 1
			SELECT @ShipTo = ShipTo FROM tCustomer WHERE ID = @CustomerID
/*
			Lookup next Audit Control identifier 
			This is unique for each 'ShipTo'
			*/
			EXEC spGetNextSeqNo 'AuditControl',  @ParmData = @AuditControl OUTPUT
			SELECT @PAD = SPACE(6 - LEN(LTRIM(@AuditControl)))
			SELECT @PAD = Replace(@PAD, ' ', '0')
			SELECT @AuditControl = 'TBSAR' + @PAD + @AuditControl
	 		
			INSERT 
			INTO tInvoice
				(CustomerID,
				InvoiceDate,
				PlatinumFileName,
				AuditControl,
				UserName,
				ReportedFlag,
				StatusID)
			VALUES(@CustomerID,
				@InvoiceDate,
				@PlatinumFileName,
				@AuditControl,
				@CurrUser,
				0,
				10)
	
			SELECT @InvoiceID = @@IDENTITY		
			SELECT @Error = @@ERROR
			IF @Error <> 0 
				BEGIN
				ROLLBACK TRAN
				RETURN @Error
				END		
			/* Get AR Contact once per cust to save a lot of joins later */		
			/*SELECT @ArContact = ISNULL(tARContact.[Description],'CDOWELL') */
			SELECT @ArContact = ISNULL(tARContact.[Description],@DefaultARContact) 
			FROM tCustomer (NOLOCK)
				LEFT OUTER JOIN tARContact (NOLOCK) ON tCustomer.ARContactID = tARContact.ID 
			WHERE tCustomer.ID = @CustomerID 	

			/*Platinum header records - temp*/
			/****************************************************************************************************************************************************/
			
			INSERT INTO tGLF_BAT_IMP_TRANS (DOC_ID, DOC_DOC_TYPE, DOC_PERIOD, DOC_REF1, LNE_VAT_RATE_CODE, LUSERFLD4, LUSERFLD3, LUSERFLD6, LUSERFLD2,  DOC_DATEI1,  LNE_ACCNBRI, LNE_ID, LNE_LDG_CODE)			
				SELECT DISTINCT  
				DOC_ID = @DOC_ID,
				DOC_DOC_TYPE = 'ARINV',
				DOC_PERIOD = @InvoicePeriod,
				DOC_REF1  = 'TBIMS' + CONVERT(varchar(11), @InvoiceID),
				LNE_VAT_RATE_CODE = 'C',
				LUSERFLD4 = @InvoiceID, 
				LUSERFLD3 = @ARContact,
				LUSERFLD6 = @ARContact,
				LUSERFLD2 = tCustomer.ShipTo, 
				DOC_DATEI1 = convert(varchar(10), @InvoiceDate, 101),
				tCustomer.VNumber + '.' + tCustomer.ShipTo,
				LNE_ID = @SeqNo,
				LNE_LDG_CODE = 'AR'
			FROM  tCustomer (NOLOCK) 
			WHERE tCustomer.ID = @CustomerID  
		
			SELECT @Error = @@ERROR
			IF @Error <> 0 
				BEGIN
				ROLLBACK TRAN
				RETURN @Error
				END	
			END


		PRINT 'About to insert tInvoiceDetailID  ' 
	
		INSERT INTO tInvoiceDetail 
			(AmountExGST, CentreID, ElementID, SubledgerID, Period, InvoiceID, BatchTypeID, BatchID, GSTAmount, [Description], ActivityID)
		VALUES(@Amount, 	@CentreID, @ElementID, @SubledgerID, @Period, @InvoiceID, @BatchTypeID, @BatchID, @GSTAmount, 
				CASE 
				WHEN RIGHT(@Period,2) = '01' THEN @BatchTypeDesc + ' JAN ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '02' THEN  @BatchTypeDesc + ' FEB ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '03' THEN  @BatchTypeDesc + ' MAR ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '04' THEN @BatchTypeDesc + ' APR ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '05' THEN @BatchTypeDesc + ' MAY ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '06' THEN @BatchTypeDesc + ' JUN ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '07' THEN @BatchTypeDesc + ' JUL ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '08' THEN @BatchTypeDesc + ' AUG ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '09' THEN @BatchTypeDesc + ' SEP ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '10' THEN @BatchTypeDesc + ' OCT ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '11' THEN  @BatchTypeDesc + ' NOV ' + LEFT(@Period,4) 
				WHEN RIGHT(@Period,2) = '12' THEN  @BatchTypeDesc + ' DEC ' + LEFT(@Period,4) 
				ELSE NULL
				END, @ActivityID)
		
		SELECT @InvoiceDetailID = @@IDENTITY
		SELECT @Error = @@ERROR

		IF @Error <> 0 
			BEGIN
			ROLLBACK TRAN
			RETURN @Error
			END
		Print 'About to update tProcessedTransaction.invoicedetailid= ' + Convert(varchar(50),@InvoiceDetailID)
		UPDATE tProcessedTransaction
		SET tProcessedTransaction.InvoiceDetailID = @InvoiceDetailID 
		FROM tProcessedTransaction INNER JOIN tTempInvoice ON tProcessedTransaction.ID = tTempInvoice.TransactionID
		WHERE tTempInvoice.CustomerID = @CustomerID
		AND tTempInvoice.BillingPeriod = @Period
		AND tTempInvoice.BatchID = @BatchID
		AND tTempInvoice.ElementID = @ElementID
		AND tTempInvoice.CentreID = @CentreID
		AND tTempInvoice.SubledgerID = @SubledgerID

	
		SELECT @Error = @@ERROR
		IF @Error <> 0 
			BEGIN
			ROLLBACK TRAN
			RETURN @Error
			END


		
		END
	SELECT @PrevCustomerID = @CustomerID
	SELECT @PrevPeriod = @Period
	SELECT @PrevBatchID = @BatchID
	SELECT @PrevElementID = @ElementID
	SELECT @PrevCentreID = @CentreID
	SELECT @PrevSubledgerID = @SubledgerID
	 
	SELECT @SumAmount = @SumAmount + @Amount
	SELECT @SumGST = @SumGST + @GSTAmount
	Print 'Get the next record'
	FETCH NEXT FROM CursorTest INTO @TransactionID, @CustomerID, @Period, @BatchID, @BatchTypeID, @CentreID, @SubledgerID, @ElementID, @Amount, @GSTAmount, @LostRevenueFlag, @BatchTypeDesc, @ActivityID
	SELECT @MainFetchStatus = @@FETCH_STATUS
	SELECT @Error = @@ERROR
	IF @Error <> 0 
		BEGIN
		ROLLBACK TRAN
		RETURN @Error
		END
END
CLOSE CursorTest
DEALLOCATE CursorTest 
Print 'About to update tInvoiceDetail ' + Convert(varchar(50),@InvoiceDetailID)
UPDATE tInvoiceDetail SET AmountExGST = @SumAmount, GSTAmount = @SumGST WHERE ID = @InvoiceDetailID


SELECT @Error = @@ERROR
IF @Error <> 0 
	BEGIN
	ROLLBACK TRAN
	RETURN @Error
	END
/*Platinum detail records - temp*/
/****************************************************************************************************************************************************/

If @SumAmount <> 0 
	BEGIN
	PRINT 'About to insert only(default)  tInvoiceCCDetail for invoicedetailID  ' +Convert(varchar(50),@InvoiceDetailID)
	INSERT INTO tInvoiceCCDetail (InvoiceDetailID, CentreID, AmountExGST, GSTAmount)
	VALUES (@InvoiceDetailID, @CentreID, @SumAmount, @SumGST)
	SELECT @Error = @@ERROR
	SELECT @CCID = @@IDENTITY
	IF @Error <> 0 
		BEGIN
		ROLLBACK TRAN
		RETURN @Error
		END
	SELECT @SeqNo = @SeqNo + 1
	PRINT 'About to insert imardtl for invoicedetailID  ' +Convert(varchar(50),@InvoiceDetailID)

	INSERT INTO tGLF_BAT_IMP_TRANS (DOC_REF1, DOC_DOC_TYPE, DOC_DATEI1, DOC_PERIOD, LNE_VAT_RATE_CODE, LNE_AMT1, LNE_VAT_EXC_AMT, LNE_ACCNBRI,   LNE_NARR1, LNE_NARR2,  LNE_ID, LNE_LDG_CODE, DOC_ID, LUSERFLD2, LUSERFLD3, LUSERFLD6,  LUSERFLD4, LNE_DETAIL_TYPE)
	SELECT DOC_REF1 = 'TBIMS' + CONVERT(varchar(11),tInvoiceDetail.InvoiceID), 
		DOC_DOC_TYPE = 'ARINV',
		DOC_DATEI1 = convert(varchar(10), @InvoiceDate, 101), 
		/*DOC_PERIOD = convert(int,RIGHT(@PrevPeriod,2)),*/
		DOC_PERIOD = @InvoicePeriod,
		LNE_VAT_RATE_CODE = 'C',
		/*LNE_AMT1 = (@SumAmount + @SumGST) * -1, */
		LNE_AMT1 = @SumAmount * -1,
		LNE_VAT_EXC_AMT = @SumAmount * -1,
		/*LNE_VAT_AMT = @SumGST * -1,*/
		LNE_ACCNBRI =  ISNULL(tSubledger.[Description] , '1')  + RTRIM(tCentre.Description)  + RTRIM(tElement.ElementCode) ,
		LNE_NARR1 = LEFT(tInvoiceDetail.[Description], 40),
		LNE_NARR2 =  ' (VicTrack Ref:' + CONVERT(varchar(11), tInvoiceDetail.BatchID) + ')',
		LNE_ID = @SeqNo,
		LNE_LDG_CODE=(CASE WHEN tSubledger.ID IS NULL THEN 'GL' ELSE 'PJ' END),
		DOC_ID = @DOC_ID,
		LUSERFLD2 = @ShipTo,
		LUSERFLD3 = @ARContact,
		LUSERFLD6 = @ARContact,
		LUSERFLD4 = @CCID,
		LNE_DETAIL_TYPE = 'L'				
	FROM tInvoiceDetail (NOLOCK)
	LEFT OUTER JOIN tCentre (NOLOCK) ON tInvoiceDetail.CentreID = tCentre.ID 
	LEFT OUTER JOIN tSubledger (NOLOCK) ON tInvoiceDetail.SubledgerID = tSubledger.ID 
	LEFT OUTER JOIN tElement (NOLOCK) ON tInvoiceDetail.ElementID = tElement.ID 
	WHERE tInvoiceDetail.ID = @InvoiceDetailID		


	SELECT @Error = @@ERROR
	IF @Error <> 0 			BEGIN
		ROLLBACK TRAN
		RETURN @Error
		END
/* Now an extra detail record to match the record in the print details table which will be added next */
	SELECT @SeqNo = @SeqNo + 1
	PRINT 'About to insert T1 print detail record for invoicedetailID  ' +Convert(varchar(50),@InvoiceDetailID)
	INSERT INTO tGLF_BAT_IMP_TRANS (DOC_REF1, DOC_DOC_TYPE, DOC_DATEI1, DDIRECTDEBITIND, LNE_AMT1, LNE_VAT_EXC_AMT, LNE_VAT_AMT, LNE_ID, DOC_ID, LUSERFLD1, LUSERFLD6, LNE_DETAIL_TYPE, LNE_VAT_TYPE, [STATUS])
	SELECT DOC_REF1 = 'TBIMS' + CONVERT(varchar(11),tInvoiceDetail.InvoiceID), 
		DOC_DOC_TYPE = NULL,
		DOC_DATEI1 = convert(varchar(10), @InvoiceDate, 101), 
		DDIRECTDEBITIND = NULL,
		LNE_AMT1 = NULL,
		LNE_VAT_EXC_AMT = NULL,
		LNE_VAT_AMT = NULL,
		LNE_ID = @SeqNo,
		DOC_ID = @DOC_ID,
		LUSERFLD1 = NULL,
		LUSERFLD6 = NULL,
		LNE_DETAIL_TYPE = 'P',
		LNE_VAT_TYPE = NULL,
		[STATUS] = NULL		
	FROM tInvoiceDetail (NOLOCK)
	LEFT OUTER JOIN tCentre (NOLOCK) ON tInvoiceDetail.CentreID = tCentre.ID 
	LEFT OUTER JOIN tSubledger (NOLOCK) ON tInvoiceDetail.SubledgerID = tSubledger.ID 
	LEFT OUTER JOIN tElement (NOLOCK) ON tInvoiceDetail.ElementID = tElement.ID 
	WHERE tInvoiceDetail.ID = @InvoiceDetailID		
		
	SELECT @Error = @@ERROR
	IF @Error <> 0 
		BEGIN
		ROLLBACK TRAN
		RETURN @Error
		END
	

INSERT INTO tGLF_BAT_IMP_TRANSD (LNEP_VAT_RATE_CODE, LNEP_AMT, LNEP_VAT_EXC_AMT, LNEP_DESCR,  LNE_ID, DOC_ID, DOC_POST_NAME, DOC_POST_ADDR1, DOC_POST_ADDR2, DOC_POST_ADDR3, DOC_POST_CITY, DOC_POST_STATE, DOC_POST_POSTCODE )
	SELECT 
		LNEP_VAT_RATE_CODE = 'C',
		LNEP_AMT = @SumAmount,
		LNEP_VAT_EXC_AMT = @SumAmount,
		LNEP_DESCR = LEFT(tInvoiceDetail.[Description] +  ' (VicTrack Ref:' + CONVERT(varchar(11), tInvoiceDetail.BatchID) + ')', 40),
		LNE_ID = @SeqNo,
		DOC_ID = @DOC_ID,
		DOC_POST_NAME =LEFT( tCustomer.Fullname,40),
		DOC_POST_ADDR1 =LEFT( tCustomer.Address01,40),
		DOC_POST_ADDR2 = LEFT(tCustomer.Address02, 40),
		DOC_POST_ADDR3 = LEFT(tCustomer.Address03, 40),
		DOC_POST_CITY = LEFT(tCustomer.Address04, 20),
		DOC_POST_STATE = LEFT(tCustomer.Address05, 15),
		DOC_POST_POSTCODE = LEFT(tCustomer.Address06,10)
	FROM tInvoiceDetail (NOLOCK)
	LEFT OUTER JOIN tInvoice (NOLOCK) ON tInvoiceDetail.InvoiceID = tInvoice.ID 
	LEFT OUTER JOIN tCustomer (NOLOCK) ON tInvoice.CustomerID = tCustomer.ID 
	WHERE tInvoiceDetail.ID = @InvoiceDetailID		

	SELECT @Error = @@ERROR
	IF @Error <> 0 
		BEGIN
		ROLLBACK TRAN
		RETURN @Error
		END				
	END

COMMIT TRAN
SET NOCOUNT OFF
RETURN 0
GO

