CREATE PROCEDURE [arvy].[GetLocalDispatchByDateAndBranch]
@inBeginServiceDate DATETIME,
@inEndServiceDate DATETIME,
@inBranchID INT,
@inPageNo INT,
@RowsPerPage INT,
@inDynamicWhere NVARCHAR(4000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ParmDefinition NVARCHAR(2000);
SET @ParmDefinition = N' @inBeginServiceDate DATETIME, @inEndServiceDate DATETIME, @inBranchID INT, @inPageNo INT, @RowsPerPage INT'
IF( @inDynamicWhere IS NULL OR LEN(LTRIM(@inDynamicWhere)) <= 0 )
BEGIN
SET @inDynamicWhere = ' WHERE 1=1 ';
END
SET @SQL = N' DECLARE @STATUS_RESERVED VARCHAR(50), @STATUS_BOOKED VARCHAR(50), @STATUS_CLEARED VARCHAR(50), @STATUS_DISPATCHED VARCHAR(50);
DECLARE @theOriginHomeTypeID INT, @theOriginWorkTypeID INT, @theDestinationHomeTypeID INT, @theDestinationWorkTypeID INT;
DECLARE @theAddressTypeID INT, @theOriginAddressType INT, @theDestinationAddressType INT, @theMovingToAddressType INT, @theVoidStatusID INT;
DECLARE @theLocServPriKeys TABLE ( LocServID INT, HasCPU INT );
DECLARE @theCommonBranches TABLE ( BranchID INT, ServiceBranch VARCHAR(50) );
SET @STATUS_RESERVED = ''Reserved'' ;
SET @STATUS_BOOKED = ''Booked'';
SET @STATUS_CLEARED = ''Cleared'';
SET @STATUS_DISPATCHED = ''Dispatched'';
SET @theOriginHomeTypeID = ( SELECT OrderPhoneTypeID FROM OrderPhoneType WHERE TypeName = ''Origin Home'' );
SET @theOriginWorkTypeID = ( SELECT OrderPhoneTypeID FROM OrderPhoneType WHERE TypeName = ''Origin Work'' );
SET @theDestinationHomeTypeID = ( SELECT OrderPhoneTypeID FROM OrderPhoneType WHERE TypeName = ''Destination Home'' );
SET @theDestinationWorkTypeID = ( SELECT OrderPhoneTypeID FROM OrderPhoneType WHERE TypeName = ''Destination Work'' );
INSERT INTO @theLocServPriKeys ( LocServID, HasCPU )
SELECT
LocServ.PriKey,
0
FROM LocServ
LEFT OUTER JOIN LSStatus ON LSStatus.PriKey = LocServ.ServStatus
WHERE CONVERT( DATETIME, CONVERT( VARCHAR, LocServ.ServiceDate, 101 ) ) >= CONVERT( datetime, CONVERT( varchar, @inBeginServiceDate, 101 ) ) AND
CONVERT( DATETIME, CONVERT( VARCHAR, LocServ.ServiceDate, 101 ) ) <= CONVERT( datetime, CONVERT( varchar, @inEndServiceDate, 101 ) ) AND
LSStatus.Status in ( @STATUS_RESERVED, @STATUS_BOOKED, @STATUS_CLEARED, @STATUS_DISPATCHED )
UPDATE @theLocServPriKeys SET
HasCPU = 1
FROM @theLocServPriKeys theLocServPriKeys
INNER JOIN LSMaterial ON theLocServPriKeys.LocServID = LSMaterial.LocServPriKey AND LSMaterial.EstQuantity > 0
UPDATE @theLocServPriKeys SET
HasCPU = 1
FROM @theLocServPriKeys theLocServPriKeys
INNER JOIN LSCrates ON theLocServPriKeys.LocServID = LSCrates.LSPriKey AND LSCrates.Quantity > 0
WHERE theLocServPriKeys.HasCPU = 0
INSERT INTO @theCommonBranches( BranchID, ServiceBranch )
SELECT
BranchID = Branch_Common.BranchPrikey,
ServiceBranch = Branch_Common.BranchID
FROM Branch Branch_Given
INNER JOIN Branch Branch_Common ON ( Branch_Common.BranchPriKey = Branch_Given.BranchPriKey or ( Branch_Common.CommonView = Branch_Given.CommonView AND
ISNULL( Branch_Given.CommonView, '''' ) != '''' ) )
WHERE Branch_Given.BranchPriKey = @inBranchID
SET @theAddressTypeID = ( SELECT AddressTypeID FROM AddressType WHERE TypeName = ''Main'' )
SET @theOriginAddressType =( SELECT AddressTypeID FROM AddressType WHERE TypeName = ''Origin'' )
SET @theDestinationAddressType = ( SELECT AddressTypeID FROM AddressType WHERE TypeName = ''Destination'' )
SET @theMovingToAddressType = ( SELECT AddressTypeID FROM AddressType WHERE TypeName = ''MovingTo'')
SET @theVoidStatusID = ( SELECT PriKey FROM LSStatus WHERE [Status] = ''Void'' )
SELECT * FROM (
SELECT
LocServ.Prikey AS LocServID,
LocServ.ServiceDate AS ServiceDate,
dbo.FormatLastNameFirstName( Orders.LastName, Orders.FirstName ) AS ShipperName,
LocServ.BranchPriKey,
CASE Orders.Archived WHEN 1 THEN Orders.ArchivedOrderNo + '' [A]'' ELSE Orders.OrderNo END AS OrderNumber,
ServType.Service AS Service,
LocServ.Crew AS NumberOfCrew,
LocServ.TotCrewHrs AS CrewHours,
LocServ.JobStart AS TimeJobStart,
LocServ.JobEnd AS TimeJobEnd,
CASE
WHEN oOrderLocationAddress.OrderLocationAddressID is not null THEN oOrderLocationAddress.Address1
WHEN OriginXtraStopAddress.XtraStopAddressID is null THEN OriginOrderAddress.Address1
ELSE OriginXtraStopAddress.Address1
END AS OriginAddress,
CASE
WHEN oOrderLocationAddress.OrderLocationAddressID is not null THEN oOrderLocationAddress.City
WHEN OriginXtraStopAddress.XtraStopAddressID is null THEN OriginOrderAddress.City
ELSE OriginXtraStopAddress.City
END AS OriginCity,
CASE
WHEN oOrderLocationAddress.OrderLocationAddressID is not null THEN oOrderLocationAddress.State
WHEN OriginXtraStopAddress.XtraStopAddressID is null THEN OriginOrderAddress.State
ELSE OriginXtraStopAddress.State
END AS OriginState,
CASE
WHEN oOrderLocationAddress.OrderLocationAddressID is not null THEN oOrderLocationAddress.PostalCode
WHEN OriginXtraStopAddress.XtraStopAddressID is null THEN OriginOrderAddress.PostalCode
ELSE OriginXtraStopAddress.PostalCode
END AS OriginZipCode,
CASE
WHEN dOrderLocationAddress.OrderLocationAddressID is not null THEN dOrderLocationAddress.Address1
WHEN DestinationXtraStopAddress.XtraStopAddressID is null THEN CASE
WHEN MovingToOrderAddress.OrderAddressID is null THEN DestinationOrderAddress.Address1
ELSE MovingToOrderAddress.Address1
END
ELSE DestinationXtraStopAddress.Address1
END AS DestinationAddress,
CASE
WHEN dOrderLocationAddress.OrderLocationAddressID is not null THEN dOrderLocationAddress.City
WHEN DestinationXtraStopAddress.XtraStopAddressID is null THEN CASE
WHEN MovingToOrderAddress.OrderAddressID is null THEN DestinationOrderAddress.City
ELSE MovingToOrderAddress.City
END
ELSE DestinationXtraStopAddress.City
END AS DestinationCity,
CASE
WHEN dOrderLocationAddress.OrderLocationAddressID is not null THEN dOrderLocationAddress.State
WHEN DestinationXtraStopAddress.XtraStopAddressID is null THEN CASE
WHEN MovingToOrderAddress.OrderAddressID is null THEN DestinationOrderAddress.State
ELSE MovingToOrderAddress.State
END
ELSE DestinationXtraStopAddress.State
END AS DestinationState,
CASE
WHEN dOrderLocationAddress.OrderLocationAddressID is not null THEN dOrderLocationAddress.PostalCode
WHEN DestinationXtraStopAddress.XtraStopAddressID is null THEN CASE
WHEN MovingToOrderAddress.OrderAddressID is null THEN DestinationOrderAddress.PostalCode
ELSE MovingToOrderAddress.PostalCode
END
ELSE DestinationXtraStopAddress.PostalCode
END AS DestinationZipCode,
Orders.PriKey AS OrderID,
Orders.Email,
Orders.EstimatedWeight AS OrderEstimatedWeight,
Orders.EstimateNo,
Orders.EstAmt,
Orders.Weight AS OrderWeight,
LocServ.WkTicketNo AS WkTicketNo,
LSStatus.Status AS Status,
LocServ.OriginDestination AS OriginDestination,
CASE
WHEN EXISTS( SELECT OrderNoteDetail.OrderNoteDetailID FROM OrderNoteDetail WHERE OrderNoteDetail.LocalServiceFID = LocServ.PriKey ) THEN 1
ELSE 0
END AS NoteAttached,
CASE
WHEN EXISTS( SELECT OrderNoteDetail.OrderNoteDetailID FROM OrderNoteDetail WHERE OrderNoteDetail.LocalServiceFID = LocServ.PriKey )
THEN (SELECT TOP 1 OrderNoteDetail.NoteBody FROM OrderNoteDetail WHERE OrderNoteDetail.LocalServiceFID = LocServ.PriKey)
ELSE ''''
END AS NoteBody,
ISNULL( dbo.LocalDispatchSumOrderContainers( Orders.PriKey ), 0 ) AS TotalOrderContainers,
ISNULL( dbo.LocalDispatchSumServiceContainers( LocServ.PriKey ),
CASE UnitType.Description
WHEN ''Cartons'' THEN LocServ.Quantity
ELSE 0
END ) AS TotalServiceContainers,
dbo.HasLeadAssignedToCrew( LocServ.PriKey ) AS CrewHasLead,
dbo.GetLeadCrewEmail( LocServ.PriKey ) AS CrewLeadEmail,
dbo.GetServiceCrewMemberList( LocServ.PriKey ) AS Crew,
dbo.GetServiceEquipmentList( LocServ.PriKey ) AS Equipment,
dbo.GetFormattedPhoneNumber( OrigHPhone.CountryCodeStANDardFID, OrigHPhone.AreaCode, OrigHPhone.LocalNumber ) AS FROMHomePhone,
dbo.GetFormattedPhoneNumber( DestHPhone.CountryCodeStANDardFID, DestHPhone.AreaCode, DestHPhone.LocalNumber ) AS ToHomePhone,
dbo.GetFormattedPhoneNumber( OrigWPhone.CountryCodeStANDardFID, OrigWPhone.AreaCode, OrigWPhone.LocalNumber ) AS FROMWorkPhone,
dbo.GetFormattedPhoneNumber( DestWPhone.CountryCodeStANDardFID, DestWPhone.AreaCode, DestWPhone.LocalNumber ) AS ToWorkPhone,
dbo.LocalDispatchSumOrderCrates( Orders.PriKey ) AS TotalOrderCrates,
dbo.LocalDispatchSumServiceCrates( LocServ.PriKey ) AS TotalServiceCrates,
LocServ.DispatchStatusFID AS DispatchStatusFID,
CommonBranches.ServiceBranch,
dbo.GetShipmentStatus( LocServ.OrdPriKey ) AS ShipmentStatus,
Orders.ETA,
Orders.ETATime,
Orders.AssignedOn,
Orders.AssignedOnTime,
Accounts.AcctNo,
Accounts.AcctName,
dbo.GetSysuserName( Driver.SysUserID, 1 ) AS DriverName,
Orders.VanNum,
LocServ.CreatedOn,
dbo.GetSysuserName( LocServ.CreatedBy, 1 ) AS CreatedBy,
LocServ.SubmittedOn,
dbo.GetSysuserName( LocServ.SubmittedBy, 1 ) AS SubmittedBy,
LocServ.ClearedOn,
dbo.GetSysuserName( LocServ.ClearedBy, 1 ) AS ClearedBy,
LocServ.LastEditedOn,
dbo.GetSysuserName( LocServ.LastEditedBy, 1 ) AS LastEditedBy,
Orders.FirstName,
Orders.LastName,
MoveType.MoveName AS MoveName,
Orders.MoveType,
Orders.Commodity,
Orders.Archived,
HaulAgent.AgentID AS HaulAgentID,
HaulAgent.Name AS HaulAgentName,
OrgAgent.AgentID AS OriginAgentID,
OrgAgent.Name AS OriginAgentName,
DestAgent.AgentID AS DestinationAgentID,
DestAgent.Name AS DestinationAgentName,
BookAgent.AgentID AS BookAgentID,
BookAgent.Name AS BookAgentName,
theLocServPriKeys.HasCPU AS HasCPU,
ServType.ContainerizationEligibleFlag,
LocServ.ContainerizationStatusFID,
CONVERT( bit, CASE UnitType.Description WHEN ''Cartons'' THEN 1 ELSE 0 END ) AS UsesContainerUnits,
dbo.GetSysuserName( Orders.Coordinator, 1 ) AS CoordinatorName,
Instruction = dbo.StripLfCrSpaces( LSInstructions.Instruction ),
ISNULL( VanLine.VanLineCode, OrderBranchVanLine.VanLineCode ) AS OrderOrBranchVanLineCode,
LocServ.RequestedStartTime,
LocServ.RequestedEndTime,
OperationalPlan.PlanName AS OperationalPlan,
LocServ.Sort1,
LocServ.Sort2,
LocServ.CrewReportTime,
LocServ.CrewReportLocationFID AS CrewReportLocation,
Orders.StartPack,
Orders.EndPack,
Orders.StartLoad,
Orders.EndLoad,
Orders.BookAgentContact,
Orders.BookAgentMemo,
Orders.OrgAgentContact,
Orders.OrgAgentMemo,
Orders.DestAgentContact,
Orders.DestAgentMemo,
Orders.HaulAgentContact,
Orders.HaulAgentMemo,
Orders.CustomerNumber,
DOrderInstructions.Instruction AS DestSpecialInstruction,
OOrderInstructions.Instruction AS OriginSpecialInstruction,
MilitaryOrder.MilitaryOrderID,
MilitaryOrder.GBLNumber,
MilitaryOrder.RankGrade,
MilitaryOrder.MilitaryBranchOfServiceFID,
MilitaryOrder.MilitaryCodeOfServiceFID,
MilitaryOrder.MilitaryCarrierFID,
MilitaryCarrier.Name AS MilitaryCarrierName,
MilitaryCarrier.SCACNumber,
MilitaryBranchOfService.BranchOfService AS MilitaryBranchOfService,
MilitaryCodeOfService.CodeOfService
FROM @theLocServPriKeys theLocServPriKeys
JOIN LocServ ON theLocServPriKeys.LocServID = LocServ.PriKey
JOIN @theCommonBranches CommonBranches ON CommonBranches.BranchID = LocServ.BranchPriKey
JOIN Orders ON Orders.PriKey = LocServ.OrdPriKey
LEFT OUTER JOIN VanLine ON Orders.VanLineFID = VanLine.VLPriKey
LEFT OUTER JOIN Branch AS OrderBranch ON Orders.BranchPriKey = OrderBranch.BranchPriKey
LEFT OUTER JOIN VanLine AS OrderBranchVanLine ON OrderBranch.VLPriKey = OrderBranchVanLine.VLPriKey
LEFT OUTER JOIN ServType ON ServType.ServiceID = LocServ.ServiceID
LEFT OUTER JOIN UnitType ON ServType.UnitTypeFID = UnitType.UnitTypeID
LEFT OUTER JOIN LSStatus ON LSStatus.PriKey = LocServ.ServStatus
LEFT OUTER JOIN XtraStop OriginXtraStop ON OriginXtraStop.Prikey = LocServ.OriginXtraStopFID
LEFT OUTER JOIN XtraStopAddress OriginXtraStopAddress ON
OriginXtraStopAddress.XtraStopFID = OriginXtraStop.Prikey AND
OriginXtraStopAddress.AddressTypeFID = @theAddressTypeID
LEFT OUTER JOIN XtraStop DestinationXtraStop ON DestinationXtraStop.Prikey = LocServ.DestXtraStopFID
LEFT OUTER JOIN XtraStopAddress DestinationXtraStopAddress ON
DestinationXtraStopAddress.XtraStopFID = DestinationXtraStop.Prikey AND
DestinationXtraStopAddress.AddressTypeFID = @theAddressTypeID
LEFT OUTER JOIN Profiles ON Profiles.ProfileID = Orders.ProfileID
LEFT OUTER JOIN OrderPhoneNumber AS OrigHPhone ON
OrigHPhone.OrderFID = Orders.PriKey AND
OrigHPhone.OrderPhoneTypeFID = @theOriginHomeTypeID
LEFT OUTER JOIN OrderPhoneNumber AS OrigWPhone ON
OrigWPhone.OrderFID = Orders.PriKey AND
OrigWPhone.OrderPhoneTypeFID = @theOriginWorkTypeID
LEFT OUTER JOIN OrderPhoneNumber AS DestHPhone ON
DestHPhone.OrderFID = Orders.PriKey AND
DestHPhone.OrderPhoneTypeFID = @theDestinationHomeTypeID
LEFT OUTER JOIN OrderPhoneNumber AS DestWPhone ON
DestWPhone.OrderFID = Orders.PriKey AND
DestWPhone.OrderPhoneTypeFID = @theDestinationWorkTypeID
LEFT OUTER JOIN OrderAddress OriginOrderAddress ON
OriginOrderAddress.OrderFID = Orders.Prikey AND
OriginOrderAddress.AddressTypeFID = @theOriginAddressType
LEFT OUTER JOIN OrderAddress DestinationOrderAddress ON
DestinationOrderAddress.OrderFID = Orders.Prikey AND
DestinationOrderAddress.AddressTypeFID = @theDestinationAddressType
LEFT OUTER JOIN OrderAddress MovingToOrderAddress ON
MovingToOrderAddress.OrderFID = Orders.Prikey AND
MovingToOrderAddress.AddressTypeFID = @theMovingToAddressType
LEFT OUTER JOIN MoveType ON Orders.MoveType = MoveType.PriKey
LEFT OUTER JOIN Accounts ON Orders.AcctPriKey = Accounts.AccountPriKey
LEFT OUTER JOIN TripOrd ON TripOrd.OrdPriKey = Orders.PriKey
LEFT OUTER JOIN Trips ON Trips.PriKey = TripOrd.TripPriKey
LEFT OUTER JOIN Sysuser AS Driver ON Driver.SysUserID = Trips.DriverEmpNo
LEFT OUTER JOIN Agent HaulAgent ON Orders.HaulAgent = HaulAgent.AgentPriKey
LEFT OUTER JOIN Agent OrgAgent ON Orders.OrgAgent = OrgAgent.AgentPriKey
LEFT OUTER JOIN Agent DestAgent ON Orders.DestAgent = DestAgent.AgentPriKey
LEFT OUTER JOIN Agent BookAgent ON Orders.BookAgent = BookAgent.AgentPriKey
LEFT OUTER JOIN LSInstructions ON LSInstructions.LSPriKey = LocServ.PriKey
LEFT OUTER JOIN OperationalPlan ON LocServ.OperationalPlanFID = OperationalPlan.OperationalPlanID
LEFT OUTER JOIN OrderLocations oOrderLocations ON LocServ.OriginOrderLocationFID = oOrderLocations.OrderLocationID
LEFT OUTER JOIN OrderLocationAddress oOrderLocationAddress ON
( oOrderLocations.OrderLocationID = oOrderLocationAddress.OrderLocationFID AND oOrderLocationAddress.AddressTypeFID = @theAddressTypeID )
LEFT OUTER JOIN OrderLocations dOrderLocations ON LocServ.DestinationOrderLocationFID = dOrderLocations.OrderLocationID
LEFT OUTER JOIN OrderLocationAddress dOrderLocationAddress ON ( dOrderLocations.OrderLocationID = dOrderLocationAddress.OrderLocationFID AND
dOrderLocationAddress.AddressTypeFID = @theAddressTypeID )
LEFT OUTER JOIN CrewReportLocation ON LocServ.CrewReportLocationFID = CrewReportLocation.CrewReportLocationID AND LocServ.ServStatus != @theVoidStatusID
LEFT OUTER JOIN OrderInstructions AS OOrderInstructions ON OOrderInstructions.OrderFID = Orders.PriKey AND OOrderInstructions.OrderInstructionTypeFID = 1 AND
OOrderInstructions.OriginDestination = ''O''
LEFT OUTER JOIN OrderInstructions AS DOrderInstructions ON DOrderInstructions.OrderFID = Orders.PriKey AND DOrderInstructions.OrderInstructionTypeFID = 1 AND
DOrderInstructions.OriginDestination = ''D''
LEFT OUTER JOIN MilitaryOrder ON MilitaryOrder.OrdersFID=Orders.PriKey
LEFT OUTER JOIN MilitaryCarrier ON MilitaryCarrier.MilitaryCarrierID= MilitaryOrder.MilitaryCarrierFID
LEFT OUTER JOIN MilitaryBranchOfService ON MilitaryBranchOfService.MilitaryBranchOfServiceID = MilitaryOrder.MilitaryBranchOfServiceFID
LEFT OUTER JOIN MilitaryCodeOfService ON MilitaryCodeOfService.MilitaryCodeOfServiceID = MilitaryOrder.MilitaryCodeOfServiceFID
) A ' + @inDynamicWhere + N'
ORDER BY a.LocServID ASC
OFFSET (@inPageNo-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY
FOR XML RAW';
EXECUTE sp_executesql @SQL, @ParmDefinition, @inBeginServiceDate,@inEndServiceDate, @inBranchID, @inPageNo, @RowsPerPage
END
GO
GRANT EXECUTE ON [arvy].[GetLocalDispatchByDateAndBranch] TO [MssExec]
GO
[arvy].[GetLocalDispatchByDateAndBranch]