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,
LTRIM(
RTRIM(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(ONI.ClientNote, CHAR(9), '''')
,CHAR(10), '''')
,CHAR(11), '''')
,CHAR(12), '''')
,CHAR(13), '''')
,CHAR(00), '''')
)) AS ClientNote,
LTRIM(
RTRIM(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(ONI.OperationalNote, CHAR(9), '''')
,CHAR(10), '''')
,CHAR(11), '''')
,CHAR(12), '''')
,CHAR(13), '''')
,CHAR(00), '''')
)) AS OperationalNote,
CNR_Phone.phone as Coordinator_Phone,
SP_Phone.phone as SalesPerson_Phone,
LGCNR_Phone.phone as LogistiCoordinator_Phone ,
( SELECT MaterialType.MaterialTypeID, MaterialType.Description
,SUM(CASE WHEN MaterialCPUType.[Type] = ''Container'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS ContainerEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Packing'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS PackingEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Unpacking'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS UnpackingEstimate
FROM OrdMaterial
INNER JOIN MaterialItemCodeMap ON MaterialItemCodeMap.MaterialItemCodeMapID = OrdMaterial.MaterialItemCodeMapFID
INNER JOIN MaterialCPUType ON MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
INNER JOIN MaterialType ON MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
WHERE OrdMaterial.OrdPriKey= Orders.PriKey
GROUP BY MaterialType.MaterialTypeID, MaterialType.Description
ORDER BY 1 ASC
FOR XML PATH ) AS OrderCPUItems ,
( SELECT MaterialType.MaterialTypeID, MaterialType.Description
,SUM(CASE WHEN MaterialCPUType.[Type] = ''Container'' THEN LSMaterial.EstQuantity ELSE 0 END) AS ContainerEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Packing'' THEN LSMaterial.EstQuantity ELSE 0 END) AS PackingEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Unpacking'' THEN LSMaterial.EstQuantity ELSE 0 END) AS UnpackingEstimate
FROM LSMaterial
INNER JOIN MaterialItemCodeMap ON MaterialItemCodeMap.MaterialItemCodeMapID = LSMaterial.MaterialItemCodeMapFID
INNER JOIN MaterialCPUType ON MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
INNER JOIN MaterialType ON MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
WHERE LSMaterial.LocServPriKey= LocServ.PriKey
GROUP BY MaterialType.MaterialTypeID, MaterialType.Description
ORDER BY 1 ASC
FOR XML PATH ) AS LocalServiceCPUItems,
Branch_Address.*,
OrderBranch.DOT As Branch_DOT
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
LEFT JOIN OfficeIndustrialOrder as ONI on ONI.OrderFID = Orders.PriKey
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.Coordinator
) AS CNR_Phone
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.SalesPerson
) AS SP_Phone
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.LogisticCoordinator
) AS LGCNR_Phone
OUTER APPLY (
SELECT BranchAddress.Title AS BrAdd_Title, BranchAddress.Contact AS BrAdd_Contact, BranchAddress.Address AS BrAdd_Address,
BranchAddress.City AS BrAdd_City, BranchAddress.State AS BrAdd_State, BranchAddress.Zip AS BrAdd_Zip, BranchAddress.County AS BrAdd_County
, BranchAddress.Email AS BrAdd_Email, BranchAddress.Phone AS BrAdd_Phone, BranchAddress.PhoneExt AS BrAdd_PhoneExt, BranchAddress.Fax AS BrAdd_Fax
FROM BranchAddress
INNER JOIN BranchAddressType ON BranchAddressType.BranchAddressTypeID = BranchAddress.BranchAddressTypeFID
AND BranchAddressType.BranchAddressType = ''Default''
WHERE BranchAddress.BranchFID = OrderBranch.BranchPriKey
) AS Branch_Address
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,
CNR_Phone.phone as Coordinator_Phone,
SP_Phone.phone as SalesPerson_Phone,
LGCNR_Phone.phone as LogistiCoordinator_Phone ,
( SELECT MaterialType.MaterialTypeID, MaterialType.Description
,SUM(CASE WHEN MaterialCPUType.[Type] = ''Container'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS ContainerEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Packing'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS PackingEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Unpacking'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS UnpackingEstimate
FROM OrdMaterial
INNER JOIN MaterialItemCodeMap ON MaterialItemCodeMap.MaterialItemCodeMapID = OrdMaterial.MaterialItemCodeMapFID
INNER JOIN MaterialCPUType ON MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
INNER JOIN MaterialType ON MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
WHERE OrdMaterial.OrdPriKey= Orders.PriKey
GROUP BY MaterialType.MaterialTypeID, MaterialType.Description
ORDER BY 1 ASC
FOR XML PATH ) AS OrderCPUItems ,
( SELECT MaterialType.MaterialTypeID, MaterialType.Description
,SUM(CASE WHEN MaterialCPUType.[Type] = ''Container'' THEN LSMaterial.EstQuantity ELSE 0 END) AS ContainerEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Packing'' THEN LSMaterial.EstQuantity ELSE 0 END) AS PackingEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Unpacking'' THEN LSMaterial.EstQuantity ELSE 0 END) AS UnpackingEstimate
FROM LSMaterial
INNER JOIN MaterialItemCodeMap ON MaterialItemCodeMap.MaterialItemCodeMapID = LSMaterial.MaterialItemCodeMapFID
INNER JOIN MaterialCPUType ON MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
INNER JOIN MaterialType ON MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
WHERE LSMaterial.LocServPriKey= LocServ.PriKey
GROUP BY MaterialType.MaterialTypeID, MaterialType.Description
ORDER BY 1 ASC
FOR XML PATH ) AS LocalServiceCPUItems,
Branch_Address.*,
OrderBranch.DOT As Branch_DOT
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
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.Coordinator
) AS CNR_Phone
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.SalesPerson
) AS SP_Phone
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.LogisticCoordinator
) AS LGCNR_Phone
OUTER APPLY (
SELECT BranchAddress.Title AS BrAdd_Title, BranchAddress.Contact AS BrAdd_Contact, BranchAddress.Address AS BrAdd_Address,
BranchAddress.City AS BrAdd_City, BranchAddress.State AS BrAdd_State, BranchAddress.Zip AS BrAdd_Zip, BranchAddress.County AS BrAdd_County
, BranchAddress.Email AS BrAdd_Email, BranchAddress.Phone AS BrAdd_Phone, BranchAddress.PhoneExt AS BrAdd_PhoneExt, BranchAddress.Fax AS BrAdd_Fax
FROM BranchAddress
INNER JOIN BranchAddressType ON BranchAddressType.BranchAddressTypeID = BranchAddress.BranchAddressTypeFID
AND BranchAddressType.BranchAddressType = ''Default''
WHERE BranchAddress.BranchFID = OrderBranch.BranchPriKey
) AS Branch_Address
) A '
END
ELSE IF @inComIdentity IN ('24BB477D-876D-403F-92D1-98326BA280D1', '6C3817A8-63FC-43B8-B269-510C145E6D40')
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,
LTRIM(
RTRIM(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(ONI.ClientNote, CHAR(9), '''')
,CHAR(10), '''')
,CHAR(11), '''')
,CHAR(12), '''')
,CHAR(13), '''')
,CHAR(00), '''')
)) AS ClientNote,
LTRIM(
RTRIM(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(ONI.OperationalNote, CHAR(9), '''')
,CHAR(10), '''')
,CHAR(11), '''')
,CHAR(12), '''')
,CHAR(13), '''')
,CHAR(00), '''')
)) AS OperationalNote,
Orders.BookAgentContact,
Orders.BookAgentMemo,
Orders.OrgAgentContact,
Orders.OrgAgentMemo,
Orders.DestAgentContact,
Orders.DestAgentMemo,
Orders.HaulAgentContact,
Orders.HaulAgentMemo,
MilitaryOrder.MilitaryOrderID,
MilitaryOrder.GBLNumber,
MilitaryOrder.RankGrade,
MilitaryOrder.MilitaryBranchOfServiceFID,
MilitaryOrder.MilitaryCodeOfServiceFID,
MilitaryOrder.MilitaryCarrierFID,
MilitaryCarrier.Name AS MilitaryCarrierName,
MilitaryCarrier.SCACNumber,
MilitaryBranchOfService.BranchOfService AS MilitaryBranchOfService,
MilitaryCodeOfService.CodeOfService,
PA.PickUpAddress1,
PA.PickUpAddress2,
PA.PickUpCity,
PA.PickUpState,
PA.PickUpPostalCode ,
DA.DeliveryAddress1,
DA.DeliveryAddress2,
DA.DeliveryCity,
DA.DeliveryState,
DA.DeliveryPostalCode,
OLA1.*,
OLA2.*,
( SELECT MaterialType.MaterialTypeID, MaterialType.Description
,SUM(CASE WHEN MaterialCPUType.[Type] = ''Container'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS ContainerEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Packing'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS PackingEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Unpacking'' THEN OrdMaterial.EstQuantity ELSE 0 END) AS UnpackingEstimate
FROM OrdMaterial
INNER JOIN MaterialItemCodeMap ON MaterialItemCodeMap.MaterialItemCodeMapID = OrdMaterial.MaterialItemCodeMapFID
INNER JOIN MaterialCPUType ON MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
INNER JOIN MaterialType ON MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
WHERE OrdMaterial.OrdPriKey= Orders.PriKey
GROUP BY MaterialType.MaterialTypeID, MaterialType.Description
ORDER BY 1 ASC
FOR XML PATH ) AS OrderCPUItems ,
( SELECT MaterialType.MaterialTypeID, MaterialType.Description
,SUM(CASE WHEN MaterialCPUType.[Type] = ''Container'' THEN LSMaterial.EstQuantity ELSE 0 END) AS ContainerEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Packing'' THEN LSMaterial.EstQuantity ELSE 0 END) AS PackingEstimate
,SUM (CASE WHEN MaterialCPUType.[Type] = ''Unpacking'' THEN LSMaterial.EstQuantity ELSE 0 END) AS UnpackingEstimate
FROM LSMaterial
INNER JOIN MaterialItemCodeMap ON MaterialItemCodeMap.MaterialItemCodeMapID = LSMaterial.MaterialItemCodeMapFID
INNER JOIN MaterialCPUType ON MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
INNER JOIN MaterialType ON MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
WHERE LSMaterial.LocServPriKey= LocServ.PriKey
GROUP BY MaterialType.MaterialTypeID, MaterialType.Description
ORDER BY 1 ASC
FOR XML PATH ) AS LocalServiceCPUItems,
Orders.OrderSeg,
dbo.GetFormattedPhoneNumber( CellPhoneShipper.CountryCodeStandardFID, CellPhoneShipper.AreaCode, CellPhoneShipper.LocalNumber ) as Cell_Phone_Shipper,
CASE WHEN Orders.CustomerNumber ! ='''' THEN CONCAT(dbo.GetCustomerName(Orders.CustomerNumber), '' ('' , Orders.CustomerNumber , '')'' ) ELSE NULL END AS BillToCustomer,
authority.Description as AuthorityName,
dbo.GetFormattedPhoneNumber(ConsignOriginPhone.CountryCodeStandardFID, ConsignOriginPhone.AreaCode, ConsignOriginPhone.LocalNumber) AS ConsignOriginPhone,
dbo.GetFormattedPhoneNumber(ConsignDestPhone.CountryCodeStandardFID, ConsignDestPhone.AreaCode, ConsignDestPhone.LocalNumber) AS ConsignDestPhone,
CNR_Phone.phone as Coordinator_Phone,
SP_Phone.phone as SalesPerson_Phone,
LGCNR_Phone.phone as LogistiCoordinator_Phone,
Branch_Address.*,
OrderBranch.DOT As Branch_DOT
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
LEFT OUTER JOIN OfficeIndustrialOrder as ONI on ONI.OrderFID = Orders.PriKey
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
OUTER APPLY (
SELECT TOP 1 PickUpAddress.Address1 AS PickUpAddress1, PickUpAddress.Address2 AS PickUpAddress2, PickUpAddress.City AS PickUpCity,
PickUpAddress.State AS PickUpState, PickUpAddress.PostalCode AS PickUpPostalCode,
PickUpXStop.OrdPriKey FROM XtraStop AS PickUpXStop
INNER JOIN XtraStopAddress AS PickUpAddress ON PickUpAddress.XtraStopFID=PickUpXStop.PriKey and PickUpXStop.OriginDest = ''O''
WHERE PickUpXStop.OrdPriKey = Orders.PriKey ORDER BY PickUpXStop.PriKey ASC ) AS PA
OUTER APPLY (
SELECT TOP 1 DeliveryAddress.Address1 AS DeliveryAddress1, DeliveryAddress.Address2 AS DeliveryAddress2, DeliveryAddress.City AS DeliveryCity,
DeliveryAddress.State AS DeliveryState, DeliveryAddress.PostalCode AS DeliveryPostalCode ,DeliveryXStop.OrdPriKey
FROM XtraStop AS DeliveryXStop
INNER JOIN XtraStopAddress AS DeliveryAddress ON DeliveryAddress.XtraStopFID=DeliveryXStop.PriKey and DeliveryXStop.OriginDest = ''D''
WHERE DeliveryXStop.OrdPriKey = Orders.PriKey ORDER BY DeliveryXStop.PriKey ASC ) AS DA
OUTER APPLY (
SELECT OLA.OrderLocationAddressID AS OLA1ID, OLA.Address1 AS OLA1Address1, OLA.Address2 AS OLA1Address2, OLA.City AS OLA1City,
OLA.State AS OLA1State , OLA.PostalCode AS OLA1PostalCode
FROM OrderLocationAddress AS OLA
INNER JOIN OrderLocations AS OLS ON OLA.OrderLocationFID = OLS.OrderLocationID
INNER JOIN LocServOrderLocationMap on LocServOrderLocationMap.OrderLocationFID = OLS.OrderLocationID
WHERE LocServOrderLocationMap.LocServFID = LocServ.PriKey
Order by OLA.OrderLocationAddressID ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY ) AS OLA1
OUTER APPLY (
SELECT OLA.OrderLocationAddressID AS OLA2ID, OLA.Address1 AS OLA2Address1, OLA.Address2 AS OLA2Address2, OLA.City AS OLA2City,
OLA.State AS OLA2State , OLA.PostalCode AS OLA2PostalCode
FROM OrderLocationAddress AS OLA
INNER JOIN OrderLocations AS OLS ON OLA.OrderLocationFID = OLS.OrderLocationID
INNER JOIN LocServOrderLocationMap on LocServOrderLocationMap.OrderLocationFID = OLS.OrderLocationID
WHERE LocServOrderLocationMap.LocServFID = LocServ.PriKey
Order by OLA.OrderLocationAddressID ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY ) AS OLA2
left outer join OrderPhoneNumber as CellPhoneShipper on CellPhoneShipper.OrderFID = Orders.PriKey and CellPhoneShipper.OrderPhoneTypeFID = 16
left outer join AuthorityTypes as authority on authority.AuthPriKey = Orders.AuthPriKey
left outer join Valuation as Valuation on Valuation.ValuationID = Orders.ValuationFID
left outer join OrderConsign As OrderConsignOrigin ON OrderConsignOrigin.OrdersFID=Orders.PriKey AND OrderConsignOrigin.ConsignTypeFID=1
left outer join OrderConsignPhoneNumber AS ConsignOriginPhone ON ConsignOriginPhone.OrderConsignFID=OrderConsignOrigin.OrderConsignID
left outer join OrderConsign AS OrderConsignDest ON OrderConsignDest.OrdersFID=Orders.PriKey AND OrderConsignDest.ConsignTypeFID=2
left outer join OrderConsignPhoneNumber AS ConsignDestPhone ON ConsignDestPhone.OrderConsignFID=OrderConsignDest.OrderConsignID
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.Coordinator
) AS CNR_Phone
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.SalesPerson
) AS SP_Phone
OUTER APPLY (
SELECT dbo.GetFormattedPhoneNumber( pn.CountryCodeStandardFID, pn.AreaCode, pn.LocalNumber ) as phone
FROM SysUserPhoneNumber pn
JOIN SysUserPhoneType pt ON pt.SysUserPhoneTypeID=pn.SysUserPhoneTypeFID AND pt.TypeName =''Work''
WHERE pn.SysUserFID = Orders.LogisticCoordinator
) AS LGCNR_Phone
OUTER APPLY (
SELECT BranchAddress.Title AS BrAdd_Title, BranchAddress.Contact AS BrAdd_Contact, BranchAddress.Address AS BrAdd_Address,
BranchAddress.City AS BrAdd_City, BranchAddress.State AS BrAdd_State, BranchAddress.Zip AS BrAdd_Zip, BranchAddress.County AS BrAdd_County
, BranchAddress.Email AS BrAdd_Email, BranchAddress.Phone AS BrAdd_Phone, BranchAddress.PhoneExt AS BrAdd_PhoneExt, BranchAddress.Fax AS BrAdd_Fax
FROM BranchAddress
INNER JOIN BranchAddressType ON BranchAddressType.BranchAddressTypeID = BranchAddress.BranchAddressTypeFID
AND BranchAddressType.BranchAddressType = ''Default''
WHERE BranchAddress.BranchFID = OrderBranch.BranchPriKey
) AS Branch_Address
) 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]