create procedure [dbo].[ValidateWheatonOrder]
@inOrderId int
as
declare @theWheatonXmlInterfaceId int
declare @theWheatonXmlSystemId int
declare @theSalespersonMappingIsMissing bit
declare @theSalespersonWrongLength bit
declare @theBookAgentIsMissing bit
declare @theBookAgentTooShort bit
declare @theHaulAgentTooShort bit
declare @theOriginAgentTooShort bit
declare @theDestinationAgentTooShort bit
declare @theShipmentTypeIsMissing bit
declare @theShipmentTypeMappingIsMissing bit
declare @theShipmentTypeInvalid bit
declare @theValuationTypeMappingIsMissing bit
declare @theValuationTypeInvalid bit
declare @theMilesTooLarge bit
declare @thePaymentTypeMappingIsMissing bit
declare @theInvalidPaymentType bit
declare @theAccountNumberMappingIsMissing bit
declare @theInvalidAccountNumber bit
declare @theTariffAndSalespersonAreMissing bit
declare @theRatePlanCodeMissing bit
declare @theRatePlanInvalid bit
declare @theUserIdIsMissing bit
declare @theAgentIdIsMissing bit
declare @theWebServiceUrlIsMissing bit
declare @theTokenIsMissing bit
declare @theDiscountInvalid bit
declare @theSitDiscountInvalid bit
declare @theEstimatedWeightIsMissing bit
declare @theLoadDateIsMissing bit
select @theWheatonXmlSystemId = XmlSystemFID
from XmlSystemDataTypeMap
where
[Name] = 'Wheaton Order Information'
select @theWheatonXmlInterfaceId = XmlInterfaceFID
from XmlSystem
where XmlSystemID = @theWheatonXmlSystemId
select
@theTariffAndSalespersonAreMissing =
case
when
Orders.RPPriKey is null and
Sysuser.SysUserID is null
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theSalespersonMappingIsMissing =
case
when
Sysuser.SysUserID is not null and
dbo.IsNullOrWhiteSpace( XMLUserMap.UserID ) = 1
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theSalespersonWrongLength =
case
when
Sysuser.SysUserID is not null and
@theSalespersonMappingIsMissing = 0 and
( len( XMLUserMap.UserID ) < 5 or len( XmlUserMap.UserID ) > 15 )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theShipmentTypeIsMissing =
case
when Orders.ShipmentTypeFID is null then cast( 1 as bit )
else cast( 0 as bit )
end,
@theShipmentTypeMappingIsMissing =
case
when dbo.IsNullOrWhiteSpace( XmlShipmentTypeMap.ExternalCode ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end,
@theShipmentTypeInvalid =
case
when
@theShipmentTypeIsMissing = 0 and
@theShipmentTypeMappingIsMissing = 0 and
XmlShipmentTypeMap.ExternalCode not in ( 'DP3', 'GSA', 'IND', 'NAT' )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theBookAgentIsMissing = dbo.IsNullOrWhiteSpace( BookingAgent.AgentID ),
@theBookAgentTooShort =
case
when
@theBookAgentIsMissing = 0 and
len( dbo.ExtractNumericCharacters( BookingAgent.AgentID ) ) < 4
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theHaulAgentTooShort =
case
when
dbo.IsNotNullOrWhiteSpace( HaulingAgent.AgentID ) = 1 and
len( dbo.ExtractNumericCharacters( HaulingAgent.AgentID ) ) < 4
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theOriginAgentTooShort =
case
when
dbo.IsNotNullOrWhiteSpace( OriginAgent.AgentID ) = 1 and
len( dbo.ExtractNumericCharacters( OriginAgent.AgentID ) ) < 4
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theDestinationAgentTooShort =
case
when
dbo.IsNotNullOrWhiteSpace( DestinationAgent.AgentID ) = 1 and
len( dbo.ExtractNumericCharacters( DestinationAgent.AgentID ) ) < 4
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theMilesTooLarge =
case
when
Orders.Miles is not null and
Orders.Miles > 999999
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theRatePlanCodeMissing =
case
when
Orders.RPPriKey is not null and
dbo.IsNullOrWhiteSpace( RatePlans.VanLineCode ) = 1
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theRatePlanInvalid =
case
when
Orders.RPPriKey is not null and
@theRatePlanCodeMissing = 0 and
dbo.IsNotNullOrWhiteSpace( RatePlans.VanLineCode ) = 1 and
RatePlans.VanLineCode not in ( '100', '400', '4NG', 'LOG', 'TRU', 'WVL', 'WRT' )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@thePaymentTypeMappingIsMissing =
case
when
Orders.PayTypeFID is not null and
dbo.IsNullOrWhiteSpace( XmlInterfacePayTypeMap.ExternalPayTypeCode ) = 1
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theInvalidPaymentType =
case
when
dbo.IsNotNullOrWhiteSpace( XmlInterfacePayTypeMap.ExternalPayTypeCode ) = 1 and
XmlInterfacePayTypeMap.ExternalPayTypeCode not in ( 'BIL', 'COD', 'CRE' )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theAccountNumberMappingIsMissing =
case
when
Orders.AcctPriKey is not null and
dbo.IsNullOrWhiteSpace( XmlInterfaceAccountMap.ExternalCode ) = 1
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theInvalidAccountNumber =
case
when
dbo.IsNotNullOrWhiteSpace( XmlInterfaceAccountMap.ExternalCode ) = 1 and
( try_convert( int, XmlInterfaceAccountMap.ExternalCode ) is null or
cast( XmlInterfaceAccountMap.ExternalCode as int ) > 999999999 )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theValuationTypeMappingIsMissing =
case
when
Orders.ValuationFID is not null and
XmlValuationVanLineCodeMap.XmlValuationVanLineCodeMapID is null
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theValuationTypeInvalid =
case
when
dbo.IsNotNullOrWhiteSpace( XmlValuationVanLineCodeMap.VanLineCode ) = 1 and
XmlValuationVanLineCodeMap.VanLineCode not in ( 'R', 'B', 'C', 'D', 'S', 'L' )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theUserIdIsMissing =
case
when dbo.IsNullOrWhiteSpace( UserIdXmlSystemOption.[Value] ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end,
@theAgentIdIsMissing =
case
when dbo.IsNullOrWhiteSpace( AgentIdXmlSystemOption.[Value] ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end,
@theWebServiceUrlIsMissing =
case
when dbo.IsNullOrWhiteSpace( WebServiceUrlXmlSystemOption.[Value] ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end,
@theTokenIsMissing =
case
when dbo.IsNullOrWhiteSpace( TokenValidation.KeyValue ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end,
@theDiscountInvalid =
case
when
Orders.Discount is not null and
( Orders.Discount not between 0.00 and 999.99 or
cast( Orders.Discount as decimal(5,2) ) <> Orders.Discount )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theSitDiscountInvalid =
case
when
SITJointInfo.SITDiscount is not null and
( SITJointInfo.SITDiscount not between 0.0 and 999.99 or
cast( SITJointInfo.SITDiscount as decimal(5,2) ) <> SITJointInfo.SITDiscount )
then cast( 1 as bit )
else cast( 0 as bit )
end,
@theEstimatedWeightIsMissing =
case
when isnull( Orders.EstimatedWeight, 0 ) < 1 then cast( 1 as bit )
else cast( 0 as bit )
end,
@theLoadDateIsMissing =
case
when Orders.StartLoad is null then cast( 1 as bit )
else cast( 0 as bit )
end
from Orders
left outer join XMLUserMap on Orders.SalesPerson = XmlUserMap.SysUserFID and XmlUserMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join XmlValuationVanLineCodeMap on Orders.ValuationFID = XmlValuationVanLineCodeMap.ValuationFID and XmlValuationVanLineCodeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join XmlInterfacePayTypeMap on Orders.PayTypeFID = XmlInterfacePayTypeMap.PayTypeFID and XmlInterfacePayTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join XmlShipmentTypeMap on Orders.ShipmentTypeFID = XmlShipmentTypeMap.ShipmentTypeFID and XmlShipmentTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join SysUser on Orders.SalesPerson = Sysuser.SysUserID
left outer join Branch as SisterHaulAgencyBranch on SisterHaulAgencyBranch.AgentPriKey = Orders.HaulAgent
left outer join Accounts on Orders.AcctPriKey = Accounts.AccountPriKey
left outer join SITJointInfo on Orders.PriKey = SITJointInfo.OrdersFID
left outer join RatePlans on Orders.RPPriKey = 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 [Validation] as TokenValidation on TokenValidation.NameKey = 'WHEATON'
left outer join XmlSystemOptions as AgentIdXmlSystemOption on
AgentIdXmlSystemOption.[Name] = 'Agent ID' and
AgentIdXmlSystemOption.XmlSystemFID = @theWheatonXmlSystemId
left outer join XmlSystemOptions as UserIdXmlSystemOption on
UserIdXmlSystemOption.[Name] = 'User ID' and
UserIdXmlSystemOption.XmlSystemFID = @theWheatonXmlSystemId
left outer join XmlSystemOptions as WebServiceUrlXmlSystemOption on
WebServiceUrlXmlSystemOption.[Name] = 'Web Service URL' and
WebServiceUrlXmlSystemOption.XmlSystemFID = @theWheatonXmlSystemId
left outer join XmlInterfaceAccountMap on XmlInterfaceAccountMap.AccountFid = Orders.AcctPriKey
where Orders.PriKey = @inOrderId
declare @theOriginSitExists bit
declare @theDestinationSitExists bit
declare @theOriginSitInDateMissing bit
declare @theDestinationSitInDateMissing bit
set @theOriginSitExists = cast( 0 as bit )
set @theDestinationSitExists = cast( 0 as bit )
set @theOriginSitInDateMissing = cast( 0 as bit )
set @theDestinationSitInDateMissing = cast( 0 as bit )
select
@theOriginSitExists =
case
when SITInformation.SITInformationID is not null then cast( 1 as bit )
else cast( 0 as bit )
end,
@theOriginSitInDateMissing =
case
when
@theOriginSitExists = 1 and
SITInformation.EstimatedInDate is null
then cast( 1 as bit )
else cast( 0 as bit )
end
from SITInformation
left outer join SITType ON SITInformation.SITTypeFID = SITType.SITTypeID
where
SITInformation.OrdersFID = @inOrderId and
SITType.[Type] = 'Origin'
select
@theDestinationSitExists =
case
when SITInformation.SITInformationID is not null then cast( 1 as bit )
else cast( 0 as bit )
end,
@theDestinationSitInDateMissing =
case
when
@theDestinationSitExists = 1 and
SITInformation.EstimatedInDate is null
then cast( 1 as bit )
else cast( 0 as bit )
end
from SITInformation
left outer join SITType ON SITInformation.SITTypeFID = SITType.SITTypeID
where
SITInformation.OrdersFID = @inOrderId and
SITType.[Type] = 'Destination'
select
@theSalespersonMappingIsMissing as SalespersonMappingIsMissing,
@theSalespersonWrongLength as SalespersonWrongLength,
@theBookAgentIsMissing as BookAgentIsMissing,
@theBookAgentTooShort as BookAgentTooShort,
@theHaulAgentTooShort as HaulAgentTooShort,
@theOriginAgentTooShort as OriginAgentTooShort,
@theDestinationAgentTooShort as DestinationAgentTooShort,
@theShipmentTypeIsMissing as ShipmentTypeIsMissing,
@theShipmentTypeMappingIsMissing as ShipmentTypeMappingIsMissing,
@theShipmentTypeInvalid as ShipmentTypeInvalid,
@theValuationTypeMappingIsMissing as ValuationTypeMappingIsMissing,
@theValuationTypeInvalid as ValuationTypeInvalid,
@theMilesTooLarge as MilesTooLarge,
@thePaymentTypeMappingIsMissing as PaymentTypeMappingIsMissing,
@theInvalidPaymentType as InvalidPaymentType,
@theAccountNumberMappingIsMissing as AccountNumberMappingIsMissing,
@theInvalidAccountNumber as InvalidAccountNumber,
@theTariffAndSalespersonAreMissing as TariffAndSalespersonAreMissing,
@theRatePlanCodeMissing as RatePlanCodeIsMissing,
@theRatePlanInvalid as RatePlanInvalid,
@theUserIdIsMissing as UserIdIsMissing,
@theAgentIdIsMissing as AgentIdIsMissing,
@theWebServiceUrlIsMissing as WebServiceUrlIsMissing,
@theTokenIsMissing as TokenIsMissing,
@theOriginSitInDateMissing as OriginSitInDateMissing,
@theDestinationSitInDateMissing as DestinationSitInDateMissing,
@theDiscountInvalid as DiscountInvalid,
@theSitDiscountInvalid as SitDiscountInvalid,
@theEstimatedWeightIsMissing as EstimatedWeightIsMissing,
case
when
@theLoadDateIsMissing = 1 and
( @theOriginSitExists = 0 or @theOriginSitInDateMissing = 1 )
then cast( 1 as bit )
else cast( 0 as bit )
end as MissingStartLoadAndOriginEstimatedInDate
select
AddressType.TypeName as AddressType,
case
when dbo.IsNullOrWhiteSpace( OrderAddress.City ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end as CityMissing,
case
when dbo.IsNullOrWhiteSpace( OrderAddress.[State] ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end as StateMissing,
case
when
dbo.IsNotNullOrWhiteSpace( OrderAddress.[State] ) = 1 and
OrderAddress.[State] not in ( 'AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM', 'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY', 'AB', 'BC', 'MB', 'NB', 'NL', 'NS', 'NT', 'NU', 'ON', 'PE', 'QC', 'SK', 'YT' )
then cast( 1 as bit )
else cast( 0 as bit )
end as StateInvalid,
case
when dbo.IsNullOrWhiteSpace( OrderAddress.PostalCode ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end as PostalCodeMissing,
case
when
dbo.IsNotNullOrWhiteSpace( OrderAddress.PostalCode ) = 1 and
(
left( OrderAddress.PostalCode, 5 ) not like '[0-9][0-9][0-9][0-9][0-9]' and
left( dbo.ExtractAlphaNumericCharacters( OrderAddress.PostalCode ), 6 ) not like '[a-zA-Z][0-9][a-zA-Z][0-9][a-zA-Z][0-9]'
)
then cast( 1 as bit )
else cast( 0 as bit )
end as PostalCodeInvalid,
case
when
dbo.IsNotNullOrWhiteSpace( OrderAddress.PostalCode ) = 1 and
(
(
left( OrderAddress.PostalCode, 5 ) like '[0-9][0-9][0-9][0-9][0-9]' and
dbo.ExtractNumericCharacters( substring( OrderAddress.PostalCode, 6 , len( OrderAddress.PostalCode ) - 5 ) ) not like '[0-9][0-9][0-9][0-9]'
)
)
then cast( 1 as bit )
else cast( 0 as bit )
end as PostalExtInvalid
from OrderAddress
left outer join XmlAddressLocationTypeMap on
XmlAddressLocationTypeMap.AddressLocationTypeFID = OrderAddress.AddressLocationTypeFID and
XmlAddressLocationTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
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
OrderPhoneType.TypeName as PhoneType,
dbo.GetFormattedPhoneNumberWithExtension(
OrderPhoneNumber.CountryCodeStandardFID,
OrderPhoneNumber.AreaCode,
OrderPhoneNumber.LocalNumber,
OrderPhoneNumber.Extension ) as PhoneNumber,
case
when OrderPhoneType.OrderPhoneTypeID is null then cast( 1 as bit )
else cast( 0 as bit )
end as PhoneTypeMissing,
case
when
OrderPhoneType.OrderPhoneTypeID is not null and
XmlOrderPhoneTypeMap.ExternalCode is null
then cast( 1 as bit )
else cast( 0 as bit )
end as PhoneTypeMappingMissing,
case
when
XmlOrderPhoneTypeMap.ExternalCode is not null and
XmlOrderPhoneTypeMap.ExternalCode not in ( 'CE', 'HO', 'OF', 'OT' )
then cast( 1 as bit )
else cast( 0 as bit )
end as PhoneTypeInvalid,
case
when dbo.IsNullOrWhiteSpace( OrderPhoneNumber.LocalNumber ) = 1 then cast( 1 as bit )
else cast( 0 as bit )
end as LocalNumberMissing,
case
when
len( isnull( OrderPhoneNumber.AreaCode, '' ) + replace( replace( replace( isnull( OrderPhoneNumber.LocalNumber, '' ), '-', '' ), ' ', '' ), 'x', '' ) ) <> 10 and
len( replace( replace( replace( isnull( OrderPhoneNumber.LocalNumber, '' ), '-', '' ), ' ', '' ), 'x', '' ) ) <> 10
then cast( 1 as bit )
else cast( 0 as bit )
end as PhoneNumberWrongLength
from dbo.OrderPhoneNumber
left outer join dbo.XmlOrderPhoneTypeMap on
XmlOrderPhoneTypeMap.OrderPhoneTypeFID = OrderPhoneNumber.OrderPhoneTypeFID and
XmlOrderPhoneTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join dbo.OrderPhoneType on OrderPhoneType.OrderPhoneTypeID = OrderPhoneNumber.OrderPhoneTypeFID
where
OrderPhoneNumber.OrderFID = @inOrderId and
OrderPhoneType.IsShipperPhoneType = 1
select
OrderEmailType.EmailType as EmailType,
nullif( ltrim( rtrim( left( OrderEmailAddress.EmailAddress, 60 ) ) ), '' ) as EmailAddress,
case
when OrderEmailType.OrderEmailTypeID is null then cast( 1 as bit )
else cast( 0 as bit )
end as EmailTypeMissing,
case
when
OrderEmailType.OrderEmailTypeID is not null and
XmlOrderEmailTypeMap.ExternalCode is null
then cast( 1 as bit )
else cast( 0 as bit )
end as EmailTypeMappingMissing,
case
when
XmlOrderEmailTypeMap.ExternalCode is not null and
XmlOrderEmailTypeMap.ExternalCode not in ( 'OT', 'PE', 'WO' )
then cast( 1 as bit )
else cast( 0 as bit )
end as EmailTypeInvalid,
case
when len( ltrim( rtrim( isnull( OrderEmailAddress.EmailAddress, '' ) ) ) ) > 60 then cast( 1 as bit )
else cast( 0 as bit )
end as EmailAddressTooLong
from OrderEmailAddress
left outer join XmlOrderEmailTypeMap on
XmlOrderEmailTypeMap.OrderEmailTypeFID = OrderEmailAddress.OrderEmailTypeFID and
XmlOrderEmailTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join OrderEmailType on OrderEmailType.OrderEmailTypeID = OrderEmailAddress.OrderEmailTypeFID
where dbo.OrderEmailAddress.OrderFID = @inOrderId
GO
GRANT EXECUTE ON [dbo].[ValidateWheatonOrder] TO [MssExec]
GO