/****** Object: View [dbo].[vw_DefaultNew] Script Date: 2/22/2021 7:36:43 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create VIEW [dbo].[vw_DefaultNew] AS SELECT '' AS 'NotesLink', '' AS 'UploadLink', '' AS 'CloneLink', '' AS 'BillToShortName', '' AS 'CHB', A.PK_OrderNo, A.HandlingStation, ISNULL(E.StationName, '') AS StationName, A.Housebill, A.OrgAirport, RTRIM(G.Description) AS OrgAirportTooltip, A.DestAirport, RTRIM(H.Description) AS DestAirportTooltip, CASE WHEN (SELECT ControlVAlue FROM tbl_Control WITH (NOLOCK) WHERE PK_Controlno = '1660') = 'N' THEN B.ShipName ELSE B.ShipAlpha END AS ShipAlpha, /* C.B.ShipAlpha, */ B.ShipName + '\n' + B.ShipAddress1 + '\n' + CASE WHEN B.ShipAddress2 IS NULL THEN '' WHEN B.ShipAddress2 = '' THEN '' ELSE B.ShipAddress2 + '\n' END + B.ShipCity + ', ' + B.FK_ShipState + ' ' + B.ShipZip AS ShipperTooltip, CASE WHEN (SELECT ControlVAlue FROM tbl_Control WITH (NOLOCK) WHERE PK_Controlno = '1660') = 'N' THEN C.ConName ELSE C.ConAlpha END AS ConAlpha, /* C.ConAlpha, */ C.ConName + '\n' + C.ConAddress1 + '\n' + CASE WHEN C.ConAddress2 IS NULL THEN '' WHEN C.ConAddress2 = '' THEN '' ELSE C.ConAddress2 + '\n' END + C.ConCity + ', ' + C.FK_ConState + ' ' + C.ConZip AS ConsigneeTooltip, A.ShipmentDateTime, CASE WHEN CONVERT(char(5), A.ShipmentDateTime, 108) = '00:00' THEN '' ELSE CONVERT(char(5), A.ShipmentDateTime, 108) END AS ShipmentTime, A.ReadyDateTime, CASE WHEN CONVERT(char(5), A.ReadyDateTime, 108) = '00:00' THEN '' ELSE CONVERT(char(5), A.ReadyDateTime, 108) END AS ReadyTime, CASE WHEN CONVERT(char(5), A.CloseTime, 108) = '00:00' THEN '' ELSE CONVERT(char(5), A.CloseTime, 108) END AS ClosedTime, A.FK_ServiceLevelId, I.ServiceLevel, A.ScheduledDateTime, CASE WHEN CONVERT(char(5), A.ScheduledDateTime, 108) = '00:00' THEN '' ELSE CONVERT(char(5), A.ScheduledDateTime, 108) END AS ScheduledTime, A.FK_OrderStatusId, J.Status AS StatusDescription, D .ModeName, D .PK_ModeId AS ModeId, A.FK_ProductCode, ISNULL(L.Description, '') AS ProductCodeDescription, A.Status, A.NextTrackingDateTime, ISNULL ((SELECT TOP (1) HoursAway FROM dbo.tbl_TimeZoneMaster WITH (NOLOCK) WHERE (PK_TimeZoneCode = A.NextTrackingDateTimeZone)), 0) AS NextTrackingTZHoursAway, A.PriorityCodeId, CASE WHEN (SELECT TOP 1 FileName FROM dbo.tbl_shipmentfile WITH (NOLOCK) WHERE FK_OrderNo = A.PK_OrderNo) IS NOT NULL THEN 'Y' ELSE 'N' END AS HasAttachments, ISNULL(A.FK_RegionCode, '') AS FK_RegionCode, A.FK_CategoryCode, CASE WHEN (SELECT TOP 1 FK_UserId FROM tbl_ShipmentsInUse WITH (NOLOCK) WHERE FK_OrderNo = A.PK_OrderNo ORDER BY OpenedDateTime) IS NULL THEN '' ELSE (SELECT SUBSTRING ((SELECT ', ' + U.FK_UserId AS [text()] FROM dbo.tbl_ShipmentsInUse AS U WITH (NOLOCK) WHERE (U.FK_OrderNo = A.PK_OrderNo) FOR xml PATH('')), 2, 100)) END AS InUseUser, ISNULL(F.Color, '') AS PriorityColor, A.ControllingStation, ISNULL(A.AcctManager, '') AS AcctManager, A.Del, ISNULL(F.CodeDescription, '') AS PriorityCodeDescription, CONVERT(nvarchar(20), A.PK_OrderNo) AS PrintHAWB, CONVERT(nvarchar(20), A.PK_OrderNo) AS PrintLabel, CONVERT(nvarchar(20), A.PK_OrderNo) AS PrintShipperConsigneeLabel, /*10/16/2020*/ CONVERT(nvarchar(20), A.PK_OrderNo) AS PrintRoutingLabel, /*12/22/2020*/ CONVERT(nvarchar(20), A.PK_OrderNo) AS PrintTLBOL, /*05/07/2020 21361*/ CASE WHEN EXISTS (SELECT TOP 1 * FROM tbl_ShipmentAPAR WHERE FK_OrderNo = A.PK_OrderNo AND APARCode = 'V' AND FK_ServiceId IN (SELECT PK_ServiceId FROM tbl_VendorServices WHERE ExpansionCode = 'LTLCA')) THEN 'Y' ELSE 'N' END AS 'ShowLTLBOL', /*05/07/2020 21361*/ CASE WHEN EXISTS (SELECT TOP 1 * FROM tbl_CarrierAlert WHERE FK_OrderNo = A.PK_OrderNo) THEN 'Y' ELSE 'N' END AS CarrierAlerts, /*05/07/2020 21361*/ M.CustName AS BillName, ISNULL(DocType.DocumentsAttached, 'Shortcut to Upload File') AS DocumentsAttached, PriorityCode, CASE WHEN EXISTS (SELECT TOP 1 FK_OrderNo FROM tbl_ConsolDetail WITH (NOLOCK) WHERE FK_OrderNo = A.PK_OrderNo) THEN 'Y' ELSE 'N' END AS ShipmentOnConsol, CASE WHEN EXISTS (SELECT TOP 1 FK_OrderNo FROM tbl_ShipmentAPAR WITH (NOLOCK) WHERE FK_OrderNo = A.PK_OrderNo AND Finalize = 'Y' AND SeqNo <> 9999) THEN 'Y' ELSE 'N' END AS CostsOrChargesFinalized, CASE WHEN EXISTS (SELECT TOP 1 FK_OrderNo FROM tbl_ShipmentAPAR WITH (NOLOCK) WHERE FK_OrderNo = A.PK_OrderNo AND InvPrinted = 'Y') THEN 'Y' ELSE 'N' END AS InvoicesPrinted, A.ShipmentType, A.ShipQuote, A.FK_SalesPerson, A.FK_ControlCustomerNo, M.FK_BillToCustNo, SHA.GatewayStation, CASE WHEN (SELECT TOP 1 X.ConsolNo FROM dbo.tbl_ShipmentAPAR as X With(NOLOCK) WHERE (X.FK_OrderNo=A.PK_OrderNo) AND (X.ConsolNo<>0)) IS NOT NULL THEN 'Y' ELSE 'N' END As IsConsolidation --02/16/2021 23276 FROM dbo.tbl_ShipmentHeader AS A WITH (NOLOCK) LEFT OUTER JOIN dbo.tbl_Shipper AS B WITH (NOLOCK) ON A.PK_OrderNo = B.FK_ShipOrderNo LEFT OUTER JOIN dbo.tbl_Consignee AS C WITH (NOLOCK) ON A.PK_OrderNo = C.FK_ConOrderNo LEFT OUTER JOIN dbo.tbl_Statuses AS J ON A.FK_OrderStatusId = J.PK_OrderStatusId LEFT OUTER JOIN dbo.tbl_Modes AS D WITH (NOLOCK) ON RTRIM(A.FK_ModeId) = D .PK_ModeId LEFT OUTER JOIN dbo.tbl_Stations AS E WITH (NOLOCK) ON A.HandlingStation = E.PK_StationId LEFT OUTER JOIN dbo.tbl_ServiceLevels AS I ON A.FK_ServiceLevelId = I.PK_ServiceLevelId LEFT OUTER JOIN dbo.tbl_Customers AS M WITH (NOLOCK) ON A.BillNo = M.PK_CustNo LEFT OUTER JOIN dbo.tbl_PriorityCode AS F WITH (NOLOCK) ON A.PriorityCodeId = F.PriorityCodeId LEFT OUTER JOIN dbo.tbl_Airports AS G WITH (NOLOCK) ON A.OrgAirport = G.PK_AirportId LEFT OUTER JOIN dbo.tbl_Airports AS H WITH (NOLOCK) ON A.DestAirport = H.PK_AirportId LEFT OUTER JOIN dbo.tbl_ProductCode AS L WITH (NOLOCK) ON L.PK_ProductCode = A.FK_ProductCode LEFT OUTER JOIN dbo.tbl_ShipmentHeaderAUX AS SHA WITH (NOLOCK) ON SHA.FK_OrderNo = A.PK_OrderNo LEFT OUTER JOIN (SELECT DISTINCT FK_OrderNo, SUBSTRING ((SELECT DISTINCT ', ' + SF_Sub.FK_DocType + ' - ' + DT.DocDescription FROM tbl_ShipmentFile SF_Sub WITH (NOLOCK) INNER JOIN tbl_DocumentType DT WITH (NOLOCK) ON DT.PK_DocType = SF_Sub.FK_DocType WHERE SF_Sub.FK_OrderNo = SF.FK_OrderNo FOR xml PATH('')), 3, 1000) AS DocumentsAttached FROM tbl_ShipmentFile SF WITH (NOLOCK)) AS DocType ON DocType.FK_OrderNo = A.PK_OrderNo GO