[dbo].[GetWheatonOrderInformation]
create procedure [dbo].[GetWheatonOrderInformation]
@inOrderId int
as
declare @theWheatonXmlInterfaceId int
declare @theWheatonXmlSystemId int
select @theWheatonXmlSystemId = XmlSystemFID
from XmlSystemDataTypeMap
where
[Name] = 'Wheaton Order Information'
select @theWheatonXmlInterfaceId = XmlInterfaceFID
from XmlSystem
where XmlSystemID = @theWheatonXmlSystemId
select
XmlUserMap.UserID as SalespersonId,
nullif( ltrim( rtrim( Sysuser.FIRSTNAME ) ), '' ) as FirstName,
nullif( ltrim( rtrim( Sysuser.LASTNAME ) ), '' ) as LastName,
Orders.PriKey as OrderPriKey,
case
when Orders.IsLead = '1' then 'LNW'
else 'INC'
end as OrderStatus,
BookingAgent.AgentID as BookingAgent,
HaulingAgent.AgentID as HaulingAgent,
OriginAgent.AgentID as OriginAgent,
DestinationAgent.AgentID as DestinationAgent,
case
when Orders.SelfHaul= 'Y' then cast(1 as bit)
when Orders.SelfHaul='N' then cast(0 as bit)
end as SelfHaul,
case
when SisterHaulAgencyBranch.BranchPriKey is null then cast( 0 as bit )
else cast( 1 as bit )
end as IsHaulAgentSistserAgent,
Orders.BookDate as BookingDate,
Orders.Miles,
Orders.ActLineHaul as DiscountedLinehaul,
case
when isnull( Orders.EstimatedWeight, 0 ) > 0 then Orders.EstimatedWeight
else null
end as EstimateWeight,
case
when isnull( Orders.[Weight], 0 ) > 0 then Orders.[Weight]
else null
end as ActualWeight,
Orders.CubicFeet as [Cube],
Orders.StartPack as PackingStart,
Orders.EndPack as PackingEnd,
Orders.StartLoad as LoadStart,
Orders.EndLoad as LoadEnd,
Orders.StartDeliv as DeliveryStart,
Orders.EndDeliv as DeliveryEnd,
Orders.ValuationAmount,
nullif( ltrim( rtrim( left( Orders.CreditAuth, 9 ) ) ), '' ) as CreditAuthorization,
XmlShipmentTypeMap.ExternalCode as ShipmentType,
XmlValuationVanLineCodeMap.VanLineCode as ValuationType,
XmlInterfacePayTypeMap.ExternalPayTypeCode as PaymentType,
nullif( ltrim( rtrim( left( XmlInterfaceAccountMap.ExternalCode, 9 ) ) ), '' ) as AccountNumber,
RatePlans.VanLineCode AS Tariff,
case
when isnull( Orders.Discount, 200 ) <= 100.00 then cast( Orders.Discount as decimal(5,2) )
when isnull( Orders.Discount, 50 ) > 100.00 then cast( ( Orders.Discount / 100 ) as decimal(5,2) )
else null
end as DiscountPercent,
case
when isnull( SITJointInfo.SITDiscount, 200 ) <= 100.00 then cast( SITJointInfo.SITDiscount as decimal(5,2) )
when isnull( SITJointInfo.SITDiscount, 50 ) > 100.00 then cast( ( SITJointInfo.SITDiscount / 100 ) as decimal(5,2) )
else null
end as SitDiscount
from dbo.Orders
left outer join dbo.XMLUserMap on Orders.SalesPerson = dbo.XmlUserMap.SysUserFID and dbo.XmlUserMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join dbo.XmlValuationVanLineCodeMap on dbo.Orders.ValuationFID = dbo.XmlValuationVanLineCodeMap.ValuationFID and dbo.XmlValuationVanLineCodeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join dbo.XmlInterfacePayTypeMap on dbo.Orders.PayTypeFID = XmlInterfacePayTypeMap.PayTypeFID and dbo.XmlInterfacePayTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join dbo.XmlShipmentTypeMap on dbo.Orders.ShipmentTypeFID = XmlShipmentTypeMap.ShipmentTypeFID and dbo.XmlShipmentTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join dbo.SysUser on dbo.Orders.SalesPerson = dbo.Sysuser.SysUserID
left outer join dbo.Branch as SisterHaulAgencyBranch on SisterHaulAgencyBranch.AgentPriKey = dbo.Orders.HaulAgent
left outer join dbo.Accounts on dbo.Orders.AcctPriKey = Accounts.AccountPriKey
left outer join dbo.SITJointInfo on dbo.Orders.PriKey = dbo.SITJointInfo.OrdersFID
left outer join dbo.MilitaryOrder on dbo.Orders.PriKey = dbo.MilitaryOrder.OrdersFID
left outer join dbo.MilitaryCarrier on dbo.MilitaryOrder.MilitaryCarrierFID = MilitaryCarrier.MilitaryCarrierID
left outer join dbo.MilitaryWeight on dbo.MilitaryWeight.OrdersFID = dbo.Orders.PriKey
left outer join dbo.RatePlans on dbo.Orders.RPPriKey = dbo.RatePlans.RPPriKey
left outer join Agent as BookingAgent on Orders.BookAgent = BookingAgent.AgentPriKey
left outer join Agent as HaulingAgent on Orders.HaulAgent = HaulingAgent.AgentPriKey
left outer join Agent as OriginAgent on Orders.OrgAgent = OriginAgent.AgentPriKey
left outer join Agent as DestinationAgent on Orders.DestAgent = DestinationAgent.AgentPriKey
left outer join XmlInterfaceAccountMap on XmlInterfaceAccountMap.AccountFid = Orders.AcctPriKey
where Orders.PriKey = @inOrderId
select
case
when AddressType.TypeName = 'Origin' then 'O'
else 'D'
end as AddressLocationType,
AddressType.TypeName as [Description],
nullif( ltrim( rtrim( Orders.FirstName ) ), '' ) as FirstName,
nullif( ltrim( rtrim( Orders.LastName ) ), '' ) as LastName,
nullif( ltrim( rtrim( left( OrderAddress.Address1, 100 ) ) ), '' ) as Street1,
nullif( ltrim( rtrim( left( OrderAddress.Address2, 100 ) ) ), '' ) as Street2,
nullif( ltrim( rtrim( OrderAddress.City ) ), '' ) as City,
OrderAddress.[State],
OrderAddress.PostalCode,
case
when
len( OrderAddress.PostalCode ) > 5 and
left( OrderAddress.PostalCode, 5 ) like '[0-9][0-9][0-9][0-9][0-9]'
then dbo.ExtractNumericCharacters( substring( OrderAddress.PostalCode, 6 , len( OrderAddress.PostalCode ) - 5 ) )
when
len( OrderAddress.PostalCode ) > 6 and
left( OrderAddress.PostalCode, 6 ) like '[a-zA-Z][0-9][a-zA-Z][0-9][a-zA-Z][0-9]'
then dbo.ExtractNumericCharacters( substring( OrderAddress.PostalCode, 7 , len( OrderAddress.PostalCode ) - 6 ) )
else null
end as PostalExt
from OrderAddress
left outer join AddressType on AddressType.AddressTypeID = OrderAddress.AddressTypeFID
left outer join Orders on Orders.PriKey = OrderAddress.OrderFID
where
OrderAddress.OrderFID = @inOrderId and
AddressType.TypeName in ( 'Origin', 'Destination' )
select
XmlOrderPhoneTypeMap.ExternalCode as PhoneType,
isnull( OrderPhoneNumber.AreaCode, '' ) + OrderPhoneNumber.LocalNumber as PhoneNumber,
nullif( ltrim( rtrim( left( OrderPhoneNumber.Extension, 6 ) ) ), '' ) as PhoneExt,
case
when
OrderPhoneType.TypeName like '%Destination%' or
OrderPhoneType.TypeName like '%Moving To%'
then cast(0 as bit)
else cast(1 as bit)
end as IsOrigin,
case
when
OrderPhoneType.TypeName like '%Destination%' or
OrderPhoneType.TypeName like '%Moving To%'
then cast(1 as bit)
else cast(0 as bit)
end as IsDestination
from OrderPhoneNumber
left outer join XmlOrderPhoneTypeMap on
XmlOrderPhoneTypeMap.OrderPhoneTypeFID = OrderPhoneNumber.OrderPhoneTypeFID and
XmlOrderPhoneTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join OrderPhoneType on OrderPhoneType.OrderPhoneTypeID = OrderPhoneNumber.OrderPhoneTypeFID
where
OrderPhoneNumber.OrderFID = @inOrderId and
OrderPhoneType.IsShipperPhoneType = 1
select
XmlOrderEmailTypeMap.ExternalCode as EmailType ,
nullif( ltrim( rtrim( left( OrderEmailAddress.EmailAddress, 60 ) ) ), '' ) as EmailAddress
from OrderEmailAddress
inner join XmlOrderEmailTypeMap on
XmlOrderEmailTypeMap.OrderEmailTypeFID = OrderEmailAddress.OrderEmailTypeFID and
XmlOrderEmailTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
where dbo.OrderEmailAddress.OrderFID = @inOrderId
select
case
when SITType.[Type] = 'Origin' then 'O'
when SITType.[Type] = 'Destination' then 'D'
else null
end as SitType,
SITInformation.EstimatedInDate as InDate,
SITInformation.EstimatedOutDate as OutDate,
SITInformation.[Days] as NumSitDays,
SITInformation.[Weight],
SITInformation.DrayageMiles as Miles
from SITInformation
left outer join SITType on SITInformation.SITTypeFID = SITType.SITTypeID
left outer join Agent on Agent.AgentPriKey = SITInformation.AgentFID
where SITInformation.OrdersFID = @inOrderId
GO
GRANT EXECUTE ON [dbo].[GetWheatonOrderInformation] TO [MssExec]
GO