/* */ /* TBIMS - Service List functionality */ /* */ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceList]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceList] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceListServiceRecord]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceListServiceRecord] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceListExtensionTypeA]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceListExtensionTypeA] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceListExtensionTypeD]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceListExtensionTypeD] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceListExtensionTypeO]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceListExtensionTypeO] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceListExtensionTypeAONoMatch]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceListExtensionTypeAONoMatch] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceListExtensionTypeAOMatch]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceListExtensionTypeAOMatch] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwServiceListExtension]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[vwServiceListExtension] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spServiceList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spServiceList] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spServiceListExtension]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spServiceListExtension] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spServiceListServiceRecord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spServiceListServiceRecord] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spServiceListGetCustomerList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spServiceListGetCustomerList] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spServiceListExtensionReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spServiceListExtensionReport] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spServiceListReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spServiceListReport] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spServiceListServiceRecordReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spServiceListServiceRecordReport] GO /* */ /* Views */ /* */ /* ------------- */ /* vwServiceList */ /* ------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceList AS SELECT dbo.tServiceID.ServiceID, dbo.tServiceID.Service, dbo.tSupplier.SupplierName, dbo.tServiceType.Description AS ServiceType, dbo.tCustomer.ShipTo, dbo.tCustomer.FullName AS Customer, dbo.tServiceID.InstallationDate, CAST(YEAR(dbo.tServiceID.InstallationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.InstallationDate) AS VARCHAR(2)), 2) AS InstallationPeriod, dbo.tCentre.Description AS Centre, dbo.tActivity.Description AS Activity, tElement_1.Description AS Element, dbo.tSubledger.Description AS Subledger, dbo.tServiceID.PayType, dbo.tPerson.Person, dbo.tLocation.Description AS Location, dbo.tApplication.Description AS Application, dbo.tServiceID.Description AS ServiceDescription, dbo.tServiceType.Code, dbo.tGroup.Description AS [Group], dbo.tDepartment.Description AS Department, dbo.tServiceID.TerminationDate, CAST(YEAR(dbo.tServiceID.TerminationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.TerminationDate) AS VARCHAR(2)), 2) AS TerminationPeriod, dbo.tSupplierAccount.SupplierAccount AS AccountNo, dbo.tServiceID.SACCnumber, dbo.tServiceID.JobNo, dbo.tServiceID.Active, tElement_1.Description AS RevenueElement, tElement_1.ElementCode AS RevenueElementCode, dbo.tServiceID.CurrentRent, dbo.tServiceID.CurrentCalls, dbo.tServiceID.PreviousRent, dbo.tServiceID.PreviousCalls, dbo.tCustomer.ID AS CustomerID FROM dbo.tServiceType LEFT OUTER JOIN dbo.tElement tElement_1 ON dbo.tServiceType.RevenueElementID = tElement_1.ID LEFT OUTER JOIN dbo.tSupplier LEFT OUTER JOIN dbo.tSupplierAccount ON dbo.tSupplier.ID = dbo.tSupplierAccount.SupplierID ON dbo.tServiceType.SupplierID = dbo.tSupplier.ID RIGHT OUTER JOIN dbo.tCentre LEFT OUTER JOIN dbo.tActivity RIGHT OUTER JOIN dbo.tServiceID ON dbo.tActivity.ID = dbo.tServiceID.ActivityID ON dbo.tCentre.ID = dbo.tServiceID.CustomerCentreID LEFT OUTER JOIN dbo.tSubledger ON dbo.tServiceID.SubledgerID = dbo.tSubledger.ID LEFT OUTER JOIN dbo.tPerson ON dbo.tServiceID.PersonID = dbo.tPerson.ID LEFT OUTER JOIN dbo.tGroup LEFT OUTER JOIN dbo.tCustomer ON dbo.tGroup.CustomerID = dbo.tCustomer.ID LEFT OUTER JOIN dbo.tDepartment ON dbo.tGroup.ID = dbo.tDepartment.GroupID ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID LEFT OUTER JOIN dbo.tElement tElement_2 ON dbo.tServiceID.ElementID = tElement_2.ID ON dbo.tServiceType.ID = dbo.tServiceID.ServiceTypeID LEFT OUTER JOIN dbo.tLocation ON dbo.tServiceID.LocationID = dbo.tLocation.ID LEFT OUTER JOIN dbo.tStatus ON dbo.tServiceID.StatusID = dbo.tStatus.ID LEFT OUTER JOIN dbo.tApplication ON dbo.tServiceID.ApplicationID = dbo.tApplication.ID WHERE (dbo.tServiceType.SystemID <> 53) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* -------------------------- */ /* vwServiceListServiceRecord */ /* -------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceListServiceRecord AS SELECT dbo.tServiceID.ServiceID, dbo.tServiceID.Service, dbo.tStatus.Status, dbo.tServiceMnemonic.ServiceCode AS Mnemonic, dbo.tServiceID.Description AS ServiceDescription, dbo.tServiceType.Description AS ServiceType, dbo.tCustomer.ShipTo, dbo.tCustomer.FullName AS Customer, dbo.tServiceID.InstallationDate, CAST(YEAR(dbo.tServiceID.InstallationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.InstallationDate) AS VARCHAR(2)), 2) AS InstallationPeriod, dbo.tPerson.Person, dbo.tLocation.Description AS Location, dbo.tServiceID.ServiceCode, dbo.tApplication.Description AS Application, dbo.tServiceID.SACCnumber, dbo.tServiceID.JobNo, dbo.rBearerSpeed.TextValue AS Speed, dbo.tServiceID.Cost, dbo.tServiceID.Cost100, dbo.tServiceID.Invoiced, dbo.tServiceType.Code, dbo.tGroup.Description AS [Group], dbo.tDepartment.Description AS Department, dbo.tServiceID.TerminationDate, CAST(YEAR(dbo.tServiceID.TerminationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.TerminationDate) AS VARCHAR(2)), 2) AS TerminationPeriod, dbo.tCentre.Description AS Centre, dbo.tActivity.Description AS Activity, dbo.tElement.Description AS Element, dbo.tSubledger.Description AS Subledger, dbo.tServiceID.PayType, dbo.tServiceID.Active, dbo.tCustomer.ID AS CustomerID, dbo.tServiceID.UpEnd, dbo.tServiceID.UpMDF, dbo.tServiceID.DownEnd, dbo.tServiceID.DownMDF FROM dbo.tApplication RIGHT OUTER JOIN dbo.tLocation RIGHT OUTER JOIN dbo.tServiceID LEFT OUTER JOIN dbo.rBearerSpeed ON dbo.tServiceID.SpeedID = dbo.rBearerSpeed.ID LEFT OUTER JOIN dbo.tElement ON dbo.tServiceID.ElementID = dbo.tElement.ID LEFT OUTER JOIN dbo.tPerson ON dbo.tServiceID.PersonID = dbo.tPerson.ID ON dbo.tLocation.ID = dbo.tServiceID.LocationID LEFT OUTER JOIN dbo.tSubledger ON dbo.tServiceID.SubledgerID = dbo.tSubledger.ID LEFT OUTER JOIN dbo.tStatus ON dbo.tServiceID.StatusID = dbo.tStatus.ID LEFT OUTER JOIN dbo.tActivity ON dbo.tServiceID.ActivityID = dbo.tActivity.ID ON dbo.tApplication.ID = dbo.tServiceID.ApplicationID LEFT OUTER JOIN dbo.tCustomer RIGHT OUTER JOIN dbo.tGroup ON dbo.tCustomer.ID = dbo.tGroup.CustomerID RIGHT OUTER JOIN dbo.tDepartment ON dbo.tGroup.ID = dbo.tDepartment.GroupID RIGHT OUTER JOIN dbo.tCentre ON dbo.tDepartment.ID = dbo.tCentre.DepartmentID ON dbo.tServiceID.CustomerCentreID = dbo.tCentre.ID LEFT OUTER JOIN dbo.tServiceMnemonic ON dbo.tServiceID.ServiceMnemonicID = dbo.tServiceMnemonic.ID LEFT OUTER JOIN dbo.tSupplier RIGHT OUTER JOIN dbo.tServiceType ON dbo.tSupplier.ID = dbo.tServiceType.SupplierID ON dbo.tServiceID.ServiceTypeID = dbo.tServiceType.ID WHERE (dbo.tServiceType.SystemID = 53) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* --------------------------- */ /* vwServiceListExtensionTypeA */ /* --------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceListExtensionTypeA AS SELECT ID, RecordNo, ext, ISNULL(lname, '') AS lname, ISNULL(fname, '') AS fname, ISNULL(tec, '') AS tec, ISNULL(division, '') AS division, ISNULL(location, '') AS location, ISNULL(sect, '') AS sect, ISNULL(title, '') AS title, ISNULL(email, '') AS email, ISNULL(site, '') AS site, ISNULL(extgrp, '') AS extgrp, date_updated, hdate, updop FROM dbo.tExtension WHERE (updop = 'A') GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* --------------------------- */ /* vwServiceListExtensionTypeD */ /* --------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceListExtensionTypeD AS SELECT ID, RecordNo, ext, ISNULL(lname, '') AS lname, ISNULL(fname, '') AS fname, ISNULL(tec, '') AS tec, ISNULL(division, '') AS division, ISNULL(location, '') AS location, ISNULL(sect, '') AS sect, ISNULL(title, '') AS title, ISNULL(email, '') AS email, ISNULL(site, '') AS site, ISNULL(extgrp, '') AS extgrp, date_updated, hdate, updop FROM dbo.tExtension WHERE (updop = 'D') GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* --------------------------- */ /* vwServiceListExtensionTypeO */ /* --------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceListExtensionTypeO AS SELECT ID, RecordNo, ext, ISNULL(lname, '') AS lname, ISNULL(fname, '') AS fname, ISNULL(tec, '') AS tec, ISNULL(division, '') AS division, ISNULL(location, '') AS location, ISNULL(sect, '') AS sect, ISNULL(title, '') AS title, ISNULL(email, '') AS email, ISNULL(site, '') AS site, ISNULL(extgrp, '') AS extgrp, date_updated, hdate, updop FROM dbo.tExtension WHERE (updop = 'O') GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* --------------------------------- */ /* vwServiceListExtensionTypeAOMatch */ /* --------------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceListExtensionTypeAOMatch AS SELECT dbo.vwServiceListExtensionTypeA.ID, dbo.vwServiceListExtensionTypeA.ext, dbo.vwServiceListExtensionTypeA.lname, dbo.vwServiceListExtensionTypeA.fname, dbo.vwServiceListExtensionTypeA.tec, dbo.vwServiceListExtensionTypeA.division, dbo.vwServiceListExtensionTypeA.location, dbo.vwServiceListExtensionTypeA.sect, dbo.vwServiceListExtensionTypeA.title, dbo.vwServiceListExtensionTypeA.email, dbo.vwServiceListExtensionTypeA.site, dbo.vwServiceListExtensionTypeA.extgrp, dbo.vwServiceListExtensionTypeA.date_updated, dbo.vwServiceListExtensionTypeA.hdate, dbo.vwServiceListExtensionTypeA.updop FROM dbo.vwServiceListExtensionTypeA INNER JOIN dbo.vwServiceListExtensionTypeO ON dbo.vwServiceListExtensionTypeA.ext = dbo.vwServiceListExtensionTypeO.ext AND dbo.vwServiceListExtensionTypeA.lname = dbo.vwServiceListExtensionTypeO.lname AND dbo.vwServiceListExtensionTypeA.fname = dbo.vwServiceListExtensionTypeO.fname AND dbo.vwServiceListExtensionTypeA.tec = dbo.vwServiceListExtensionTypeO.tec AND dbo.vwServiceListExtensionTypeA.division = dbo.vwServiceListExtensionTypeO.division AND dbo.vwServiceListExtensionTypeA.location = dbo.vwServiceListExtensionTypeO.location AND dbo.vwServiceListExtensionTypeA.sect = dbo.vwServiceListExtensionTypeO.sect AND dbo.vwServiceListExtensionTypeA.title = dbo.vwServiceListExtensionTypeO.title AND dbo.vwServiceListExtensionTypeA.email = dbo.vwServiceListExtensionTypeO.email AND dbo.vwServiceListExtensionTypeA.site = dbo.vwServiceListExtensionTypeO.site AND dbo.vwServiceListExtensionTypeA.extgrp = dbo.vwServiceListExtensionTypeO.extgrp GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* ----------------------------------- */ /* vwServiceListExtensionTypeAONoMatch */ /* ----------------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceListExtensionTypeAONoMatch AS SELECT ID, RecordNo, ext, lname, fname, tec, division, location, sect, title, email, site, extgrp, date_updated, hdate, updop FROM dbo.vwServiceListExtensionTypeA WHERE (ID NOT IN (SELECT ID FROM dbo.vwServiceListExtensionTypeAOMatch)) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* ---------------------- */ /* vwServiceListExtension */ /* ---------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.vwServiceListExtension AS SELECT ext AS Extension, lname AS LastName, fname AS FirstName, tec AS Tec, division AS Division, sect AS [Section], title AS Title, email AS Email, site AS Site, extgrp AS ExtensionGroup, NULL AS InstallationDate, [date_updated] AS TerminationDate, 'D' AS RecordType, [date_updated] AS SortDate FROM dbo.vwServiceListExtensionTypeD UNION SELECT ext AS Extension, lname AS LastName, fname AS FirstName, tec AS Tec, division AS Division, sect AS [Section], title AS Title, email AS Email, site AS Site, extgrp AS ExtensionGroup, [date_updated] AS InstallationDate, NULL AS TerminationDate, 'O' AS RecordType, [date_updated] AS SortDate FROM dbo.vwServiceListExtensionTypeO UNION SELECT ext AS Extension, lname AS LastName, fname AS FirstName, tec AS Tec, division AS Division, sect AS [Section], title AS Title, email AS Email, site AS Site, extgrp AS ExtensionGroup, [date_updated] AS InstallationDate, NULL AS TerminationDate, 'A' AS RecordType, [date_updated] AS SortDate FROM dbo.vwServiceListExtensionTypeAONoMatch GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* */ /* Stored Procs */ /* */ /* ------------- */ /* spServiceList */ /* ------------- */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[spServiceList] AS SELECT ServiceID, Service, SupplierName, ServiceType, ShipTo, Customer, InstallationDate, InstallationPeriod, Centre, Activity, Element, Subledger, PayType, Person, Location, Application, ServiceDescription, Code, [Group], Department, AccountNo, SACCnumber, JobNo, RevenueElement, RevenueElementCode, CurrentRent, CurrentCalls, PreviousRent, PreviousCalls FROM dbo.vwServiceList WHERE Active = 1 ORDER BY ServiceID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* ---------------------- */ /* spServiceListExtension */ /* ---------------------- */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[spServiceListExtension] AS SELECT ext AS Extension, lname AS LastName, fname AS FirstName, tec AS Tec, division AS ShipTo, dbo.tCustomer.FullName AS Customer, sect AS [Section], title AS Title, email AS Email, site AS PABX, extgrp AS ServiceID, [date_updated] AS InstallationDate, CAST(YEAR([date_updated]) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH([date_updated]) AS VARCHAR(2)), 2) AS InstallationPeriod FROM dbo.vwServiceListExtensionTypeO LEFT OUTER JOIN dbo.tCustomer ON dbo.vwServiceListExtensionTypeO.division = dbo.tCustomer.ShipTo ORDER BY ext, [date_updated] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* -------------------------- */ /* spServiceListServiceRecord */ /* -------------------------- */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[spServiceListServiceRecord] AS SELECT ServiceID, Service, Status, ServiceType, ShipTo, Customer, InstallationDate, InstallationPeriod, Person, Location, ServiceCode, Application, SACCnumber, Cost, Invoiced, Mnemonic, ServiceDescription, JobNo, Speed, Code, [Group], Department, Centre, Activity, Element, Subledger, PayType, UpEnd, UpMDF, DownEnd, DownMDF, Cost100 FROM dbo.vwServiceListServiceRecord WHERE Active = 1 ORDER BY ServiceID GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* ---------------------------- */ /* spServiceListGetCustomerList */ /* ---------------------------- */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE spServiceListGetCustomerList @p_CustomerFilter varchar(50) AS SET NOCOUNT ON if LEN(@p_CustomerFilter) = 0 SELECT DISTINCT tCustomer.ID, tCustomer.ShipTo, tCustomer.Fullname FROM tCustomer (NOLOCK) ORDER BY tCustomer.ShipTo else SELECT DISTINCT tCustomer.ID, tCustomer.ShipTo, tCustomer.Fullname FROM tCustomer (NOLOCK) WHERE tCustomer.Fullname LIKE @p_CustomerFilter + '%' ORDER BY tCustomer.ShipTo GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* ---------------------------- */ /* spServiceListExtensionReport */ /* ---------------------------- */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[spServiceListExtensionReport] @p_FromDate varchar(10), @p_ToDate varchar(10), @p_CustomerList varchar(8000) AS DECLARE @SQL varchar(8000) DECLARE @Where varchar(8000) DECLARE @OrderBy varchar(8000) -- SELECT Clause SET @SQL = ' SELECT Extension, LastName AS [Last Name], FirstName AS [First Name], Tec, Division AS [Ship To], dbo.tCustomer.FullName AS Customer, [Section], Title, Email, Site AS PABX, ExtensionGroup AS [Service ID], InstallationDate AS [Installation Date], CAST(YEAR(InstallationDate) AS VARCHAR(4)) + ''/'' + RIGHT(''0'' + CAST(MONTH(InstallationDate) AS VARCHAR(2)), 2) AS [Installation Period], TerminationDate AS [Termination Date], CAST(YEAR(TerminationDate) AS VARCHAR(4)) + ''/'' + RIGHT(''0'' + CAST(MONTH(TerminationDate) AS VARCHAR(2)), 2) AS [Termination Period] FROM dbo.vwServiceListExtension LEFT OUTER JOIN dbo.tCustomer ON dbo.vwServiceListExtension.division = dbo.tCustomer.ShipTo ' -- WHERE Clause SET @Where = CHAR(10) + ' WHERE ((InstallationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''') OR (TerminationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''')) ' if LEN(@p_CustomerList) > 0 begin SET @Where = @Where + CHAR(10) + 'AND dbo.tCustomer.ID IN (' + @p_CustomerList + ') ' end -- ORDER BY Clause SET @OrderBy = CHAR(10) + ' ORDER BY Extension, SortDate, RecordType' EXEC (@SQL + @Where + @OrderBy) --select @SQL + @Where + @OrderBy --PRINT @SQL + @Where + @OrderBy GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* ------------------- */ /* spServiceListReport */ /* ------------------- */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[spServiceListReport] @p_FromDate varchar(10), @p_ToDate varchar(10), @p_CustomerList varchar(8000) AS DECLARE @SQL varchar(8000) DECLARE @Where varchar(8000) DECLARE @OrderBy varchar(8000) -- SELECT Clause SET @SQL = ' SELECT ServiceID AS [Service ID], Service, SupplierName AS [Supplier Name], ServiceType AS [Service Type], ShipTo AS [Ship To], Customer, InstallationDate AS [Installation Date], InstallationPeriod AS [Installation Period], Centre, Activity, Element, Subledger, PayType AS [Pay Type], Person, Location, Application, ServiceDescription AS [Service Description], Code, [Group], Department, TerminationDate AS [Termination Date], TerminationPeriod AS [Termination Period], AccountNo AS [Account No], SACCnumber AS [SACC Number], JobNo AS [Job No], Active, RevenueElement AS [Revenue Element], RevenueElementCode AS [Revenue Element Code], CurrentRent AS [Current Rent], CurrentCalls AS [Current Calls], PreviousRent AS [Previous Rent], PreviousCalls AS [Previous Calls] FROM dbo.vwServiceList' -- WHERE Clause SET @Where = CHAR(10) + ' WHERE ((InstallationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''') OR (TerminationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''')) ' if LEN(@p_CustomerList) > 0 begin SET @Where = @Where + CHAR(10) + 'AND CustomerID IN (' + @p_CustomerList + ') ' end -- ORDER BY Clause SET @OrderBy = CHAR(10) + 'ORDER BY ServiceID' EXEC (@SQL + @Where + @OrderBy) --select @SQL + @Where + @OrderBy --PRINT @SQL + @Where + @OrderBy GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* -------------------------------- */ /* spServiceListServiceRecordReport */ /* -------------------------------- */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[spServiceListServiceRecordReport] @p_FromDate varchar(10), @p_ToDate varchar(10), @p_CustomerList varchar(8000) AS DECLARE @SQL varchar(8000) DECLARE @Where varchar(8000) DECLARE @OrderBy varchar(8000) -- SELECT Clause SET @SQL = ' SELECT ServiceID AS [Service ID], Service, Status, ServiceType AS [Service Type], ShipTo AS [Ship To], Customer, InstallationDate AS [Installation Date], InstallationPeriod AS [Installation Period], Person, Location, ServiceCode AS [Service Code], Application, SACCnumber AS [SACC Number], Cost, Invoiced, Cost100 AS [Cost 100], Mnemonic, ServiceDescription AS [Service Description], JobNo AS [Job No], Speed, Code, [Group], Department, TerminationDate AS [Termination Date], TerminationPeriod AS [Termination Period], Centre, Activity, Element, Subledger, PayType AS [Pay Type], Active, UpEnd, UpMDF, DownEnd, DownMDF FROM dbo.vwServiceListServiceRecord' -- WHERE Clause SET @Where = CHAR(10) + ' WHERE ((InstallationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''') OR (TerminationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''')) ' if LEN(@p_CustomerList) > 0 begin SET @Where = @Where + CHAR(10) + 'AND CustomerID IN (' + @p_CustomerList + ') ' end -- ORDER BY Clause SET @OrderBy = CHAR(10) + 'ORDER BY ServiceID' EXEC (@SQL + @Where + @OrderBy) --select @SQL + @Where + @OrderBy --PRINT @SQL + @Where + @OrderBy GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO