USE [VRTQuote] GO /****** Object: View [dbo].[billjobs] Script Date: 18/12/2024 1:00:21 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[billjobs] AS SELECT CONVERT(varchar, dbo.Quote.CreatedDate, 103) AS LogDateTime, SUBSTRING(dbo.Quote_Items.BillPeriod, 3, 4) + '/' + SUBSTRING(dbo.Quote_Items.BillPeriod, 1, 2) AS BillPeriod, dbo.Quote_Items.ServiceTypeID, dbo.Quote.CallNumber, dbo.Quote_Items.Supplier, dbo.Quote_Items.BillDate AS BilledDate, LEFT(dbo.Quote.CustomerRef, 50) AS CustRef, LEFT(dbo.Quote.QuoteSubject, 50) AS Description, dbo.Quote.ShipTo, configQ.ConfigId AS Location, dbo.Quote_Items.ItemQuantity AS ProgOnly, LEFT(dbo.Quote_Items.ItemDescription, 50) AS ProgLocation, dbo.Quote.RequestedBy AS Lastname, dbo.Quote_Items.Bill, dbo.Quote_Items.RevAmtExGST + ISNULL(dbo.Quote_Items.AdminFee, 0) AS AmtExGST, dbo.Quote.CallNumber AS Seqno, cq.IthelpUser, dbo.Quote_Items.PPTaken FROM dbo.Quote LEFT OUTER JOIN dbo.Quote_Items ON dbo.Quote.CallNumber = dbo.Quote_Items.CallNumber LEFT OUTER JOIN QuetzalSC.dbo.Call AS cq ON dbo.Quote.CallNumber = cq.CallNumber LEFT OUTER JOIN QuetzalSC.dbo.Configuration AS configQ ON cq.ConfigCode = configQ.ConfigCode WHERE (dbo.Quote_Items.Bill = 1) AND (dbo.Quote_Items.ItemTypeID = '1' OR dbo.Quote_Items.ItemTypeID = '3' OR dbo.Quote_Items.ItemTypeID = '4' OR dbo.Quote_Items.ItemTypeID = '5' OR dbo.Quote_Items.ItemTypeID = '6') GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[15] 4[63] 2[7] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1[50] 4[25] 3) )" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1[50] 2[25] 3) )" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4[30] 2[16] 3) )" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1[56] 3) )" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2[66] 3) )" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3) )" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1 [56] 4 [18] 2))" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 3 End Begin DiagramPane = PaneHidden = Begin Origin = Top = -116 Left = 0 End Begin Tables = Begin Table = "Quote" Begin Extent = Top = 198 Left = 38 Bottom = 317 Right = 232 End DisplayFlags = 280 TopColumn = 25 End Begin Table = "Quote_Items" Begin Extent = Top = 198 Left = 270 Bottom = 317 Right = 454 End DisplayFlags = 280 TopColumn = 28 End Begin Table = "cq" Begin Extent = Top = 108 Left = 561 Bottom = 325 Right = 792 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "configQ" Begin Extent = Top = 0 Left = 713 Bottom = 115 Right = 920 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 19 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 4890 Alias = 3330 Table ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'billjobs' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'= 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 4500 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'billjobs' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'billjobs' GO