CREATE PROCEDURE [arvy].[GetLocalDispatchByDateAndBranch]
@inBeginServiceDate DATETIME,
@inEndServiceDate DATETIME,
@inBranchID INT,
@inPageNo INT,
@RowsPerPage INT,
@inDynamicWhere NVARCHAR(4000),
@inComIdentity NVARCHAR(64)
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
IF (@inDynamicWhere LIKE '%UPDATE %' OR @inDynamicWhere LIKE '%TRUNCATE %' OR @inDynamicWhere LIKE '%DROP %' OR @inDynamicWhere
LIKE '%DELETE %' OR @inDynamicWhere LIKE '%INFORMATION_SCHEMA%' OR @inDynamicWhere LIKE '%SELECT %')
BEGIN
SET NOEXEC ON;
END
ELSE
BEGIN
IF @inComIdentity IN ('68AC92CF-AEA3-4719-A09E-C34AFBCCD806', '2AA858AA-D32A-41B5-91B1-F6B409C28D9D')
BEGIN
SET @SQL = N' DECLARE @STATUS_RESERVED VARCHAR(50), @STATUS_BOOKED VARCHAR(50), @STATUS_CLEARED VARCHAR(50);
DECLARE @STATUS_DISPATCHED VARCHAR(50), @theOriginHomeTypeID INT; DECLARE @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.Address2
when OriginXtraStopAddress.XtraStopAddressID is null then OriginOrderAddress.Address2
else OriginXtraStopAddress.Address2
end as OriginAddress1 ,
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.Address2
when DestinationXtraStopAddress.XtraStopAddressID is null then
case
when MovingToOrderAddress.OrderAddressID is null then DestinationOrderAddress.Address2
else MovingToOrderAddress.Address2 end
else DestinationXtraStopAddress.Address2
end as DestinationAddress1 ,
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 ,
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,
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 ,
Profiles.profilename,
Orders.LoadTime,
Orders.LoadEndTime,
Orders.StartLoad,
Orders.EndLoad,
ShipmentType.ShipmentTypeDescription,
CommType.Commodity as CommodityName,
dbo.GetSysuserName( Orders.LogisticCoordinator, 1 ) as LogisticCoordinator ,
DOrderInstructions.Instruction as DestSpecialInstruction ,
dbo.GetSysuserName( Orders.SalesPerson, 1 ) as SalesPerson,
Orders.CubicFeet, Orders.LineHaul ,
Orders.Miles,
LocServ.HrsPerMan ,
OOrderInstructions.Instruction as OriginSpecialInstruction,
Orders.OrderStatus ,
Orders.FirstName ,
Orders.LastName ,
Orders.StartPack ,
Orders.EndPack ,
Orders.StartDeliv ,
Orders.EndDeliv ,
Orders.CustomerNumber ,
Orders.ActualCost,
Orders.PayTypeFID,
Orders.PurchaseOrderNo ,
Orders.ValuationAmount ,
dbo.GetSumOfOrderCpuEstimates(Orders.PriKey, ''Packing'') AS TotalOrderPackings,
dbo.GetSumOfOrderCpuEstimates(Orders.PriKey, ''Unpacking'') AS TotalOrderUnpackings,
PayType.PayName as PaymentType ,
CONTACTPHONES.* ,
EquimentsRequested.NumberOfEquiments ,
( SELECT LocalServiceEquipmentRequest.LocalServiceEquipmentRequestID, EqType.[Type] AS EquipmentType, EqType.TypeNumber AS EquipmentTypeID,
LocalServiceEquipmentRequest.Quantity AS EquipmentQuantity
FROM EqType
left outer join LocalServiceEquipmentRequest ON EqType.TypeNumber = LocalServiceEquipmentRequest.EquipmentTypeFID
AND LocalServiceEquipmentRequest.LocalServiceFID = LocServ.PriKey
WHERE ISNULL( LocalServiceEquipmentRequest.Quantity, 0 ) > 0 FOR XML PATH )
AS EquimentsRequestDetails ,
( SELECT LocalServiceLaborRequest.LocalServiceLaborRequestID, LaborType.LaborType, LaborType.PriKey AS LaborTypeID, LocalServiceLaborRequest.Quantity AS LaborRequestQuantity
FROM LaborType
LEFT OUTER JOIN LocalServiceLaborRequest ON LaborType.PriKey = LocalServiceLaborRequest.LaborTypeFID
AND LocalServiceLaborRequest.LocalServiceFID = LocServ.PriKey
WHERE ISNULL( LaborType.IsLocalDispatch, ''N'') = ''Y'' AND ISNULL( LocalServiceLaborRequest.Quantity, 0 ) > 0 FOR XML PATH )
AS LaborRequestDetails ,
ONI.OfficeIndustrialOrderID,
ONI.ProjectManager,
dbo.GetSysuserName( ONI.ProjectManager, 1 ) as ProjectManager_Name,
ONI.JobStartDate,
ONI.JobEndDate,
ONI.HeadCount,
STRING_ESCAPE(ONI.ClientNote, ''json'') as ClientNote,
STRING_ESCAPE(ONI.OperationalNote, ''json'') as OperationalNote
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
left outer join ShipmentType ShipmentType on ShipmentType.ShipmentTypeID = Orders.ShipmentTypeFID
left outer join CommType CommType on CommType.PriKey = Orders.Commodity
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 PayType as PayType on PayType.PayTypeID = Orders.PayTypeFID
OUTER APPLY (SELECT SUM(Quantity) As NumberOfEquiments FROM LocalServiceEquipmentRequest WHERE LocalServiceFID = LocServ.PriKey ) AS EquimentsRequested
OUTER APPLY ( SELECT MAX( CASE
WHEN OrderContactPhoneNumber.OrderContactPhoneTypeFID = 3
THEN dbo.GetFormattedPhoneNumber( OrderContactPhoneNumber.CountryCodeStandardFID, OrderContactPhoneNumber.AreaCode, OrderContactPhoneNumber.LocalNumber )
END ) AS CONTACT_MOBILE ,
MAX(CASE
WHEN OrderContactPhoneNumber.OrderContactPhoneTypeFID = 1 THEN dbo.GetFormattedPhoneNumber( OrderContactPhoneNumber.CountryCodeStandardFID,
OrderContactPhoneNumber.AreaCode, OrderContactPhoneNumber.LocalNumber ) END ) AS CONTACT_PHONE,
MAX(OrderContacts.Email) AS CONTACT_EMAIL
FROM OrderContacts
LEFT OUTER JOIN OrderContactPhoneNumber ON OrderContactPhoneNumber.OrderContactFID = OrderContacts.OrderContactID
WHERE OrderContacts.OrderFID = Orders.PriKey AND OrderContactPhoneNumber.OrderContactPhoneTypeFID IN (3,1)
GROUP BY OrderContacts.OrderContactID
ORDER BY OrderContacts.OrderContactID ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY )
AS CONTACTPHONES
INNER JOIN OfficeIndustrialOrder as ONI on ONI.OrderFID = Orders.PriKey
where LocServ.ServStatus != @theVoidStatusID ) A '
END
ELSE IF @inComIdentity = 'FB0E0056-F21C-4C12-B57E-AC60FABB5924'
BEGIN
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 '
END
SET @SQL = @SQL + @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
END
GO
GRANT EXECUTE ON [arvy].[GetLocalDispatchByDateAndBranch] TO [MssExec]
GO
[arvy].[GetLocalDispatchByDateAndBranch]