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