[dbo].[XmlExportOrderLocation]
CREATE PROCEDURE [dbo].[XmlExportOrderLocation]
@inXmlInterfaceID int,
@inOrderID int,
@outStatusCode int output,
@outErrorCode int output
as
set nocount on;
set @outStatusCode = 0;
set @outErrorCode = 0;
declare @ERROR_CODE_INVALID_ORDER int;
set @ERROR_CODE_INVALID_ORDER = 2048;
declare @STATUS_CODE_PROCESSED int;
set @STATUS_CODE_PROCESSED = 1;
declare @STATUS_CODE_ERROR int;
set @STATUS_CODE_ERROR = 0;
declare @theMainAddressTypeId int
declare @theValidOrderFlag bit;
exec @theValidOrderFlag = dbo.ValidateOrderID @inOrderID;
if ( 0 = @theValidOrderFlag )
begin
set @outErrorCode = @ERROR_CODE_INVALID_ORDER;
set @outStatusCode = @STATUS_CODE_ERROR;
end
else
begin
select @theMainAddressTypeId = AddressTypeID from AddressType where TypeName = 'Main'
select
1 as tag,
null as parent,
[OrderLocation!1!Name!element] = OrderLocations.[Name],
[OrderLocation!1!Contact!element] = OrderContacts.Contact,
[OrderLocation!1!ContactName!element] = OrderContacts.[Name],
[OrderLocation!1!LocationType!element] = OrderLocationAddressType.TypeName,
[OrderLocation!1!Building!element] = Buildings.[Description],
[OrderLocation!1!BuildingExternalLocationType!element] = BuildingXmlAddressLocationTypeMap.ExternalCode,
[OrderLocation!1!BuildingCrossStreet!element] = Buildings.CrossStreet,
[OrderLocation!1!BuildingServiceEntrance!element] = Buildings.ServiceEntrance,
[OrderLocation!1!BuildingSiteNote!element] = Buildings.SiteNote,
[OrderLocationAddress!2!AddressLocationType!element] = null,
[OrderLocationAddress!2!Address!element] = null,
[OrderLocationAddress!2!Address2!element] = null,
[OrderLocationAddress!2!Address3!element] = null,
[OrderLocationAddress!2!City!element] = null,
[OrderLocationAddress!2!State!element] = null,
[OrderLocationAddress!2!Zip!element] = null,
[OrderLocationAddress!2!Country!element] = null,
[OrderLocationAddress!2!CountryName!element] = null,
[BuildingConstraint!3!Description!element] = null,
[BuildingConstraint!3!ConstraintFlag!element] = null,
[BuildingConstraint!3!Note!element] = null
from OrderLocations
left outer join OrderContacts on OrderContacts.OrderContactID = OrderLocations.OrderContactFID
left outer join OrderLocationAddressType on
OrderLocationAddressType.OrderLocationAddressTypeID = OrderLocations.OrderLocationAddressTypeFID
left outer join Buildings on Buildings.BuildingID = OrderLocations.BuildingFID
left outer join XmlAddressLocationTypeMap as BuildingXmlAddressLocationTypeMap on
BuildingXmlAddressLocationTypeMap.AddressLocationTypeFID = Buildings.AddressLocationTypeFID and
BuildingXmlAddressLocationTypeMap.XmlInterfaceFID = @inXmlInterfaceID
where OrderContacts.OrderFID = @inOrderID
union all
select
2 as tag,
1 as parent,
[OrderLocation!1!Name!element] = OrderLocations.[Name],
[OrderLocation!1!Contact!element] = OrderContacts.Contact,
[OrderLocation!1!ContactName!element] = OrderContacts.[Name],
[OrderLocation!1!LocationType!element] = OrderLocationAddressType.TypeName,
[OrderLocation!1!Building!element] = Buildings.[Description],
[OrderLocation!1!BuildingExternalLocationType!element] = BuildingXmlAddressLocationTypeMap.ExternalCode,
[OrderLocation!1!BuildingCrossStreet!element] = null,
[OrderLocation!1!BuildingServiceEntrance!element] = null,
[OrderLocation!1!BuildingSiteNote!element] = null,
[OrderLocationAddress!2!AddressLocationType!element] = AddressLocationType.[Description],
[OrderLocationAddress!2!Address!element] = OrderLocationAddress.Address1,
[OrderLocationAddress!2!Address2!element] = OrderLocationAddress.Address2,
[OrderLocationAddress!2!Address3!element] = OrderLocationAddress.Address3,
[OrderLocationAddress!2!City!element] = OrderLocationAddress.City,
[OrderLocationAddress!2!State!element] = OrderLocationAddress.[State],
[OrderLocationAddress!2!Zip!element] = OrderLocationAddress.PostalCode,
[OrderLocationAddress!2!Country!element] = CountryCodeStandard.VanlineCountryCode,
[OrderLocationAddress!2!CountryName!element] = CountryCodeStandard.CountryName,
[BuildingConstraint!3!Description!element] = null,
[BuildingConstraint!3!ConstraintFlag!element] = null,
[BuildingConstraint!3!Note!element] = null
from OrderLocations
left outer join OrderContacts on OrderContacts.OrderContactID = OrderLocations.OrderContactFID
left outer join OrderLocationAddressType on
OrderLocationAddressType.OrderLocationAddressTypeID = OrderLocations.OrderLocationAddressTypeFID
left outer join Buildings on Buildings.BuildingID = OrderLocations.BuildingFID
left outer join XmlAddressLocationTypeMap as BuildingXmlAddressLocationTypeMap on
BuildingXmlAddressLocationTypeMap.AddressLocationTypeFID = Buildings.AddressLocationTypeFID and
BuildingXmlAddressLocationTypeMap.XmlInterfaceFID = @inXmlInterfaceID
left outer join OrderLocationAddress on
OrderLocationAddress.OrderLocationFID = OrderLocations.OrderLocationID and
OrderLocationAddress.AddressTypeFID = @theMainAddressTypeId
left outer join AddressLocationType on AddressLocationType.AddressLocationTypeID = OrderLocationAddress.AddressLocationTypeFID
left outer join CountryCodeStandard on
CountryCodeStandard.CountryCodeStandardID = OrderLocationAddress.CountryCodeStandardFID
where OrderContacts.OrderFID = @inOrderID
union all
select
3 as tag,
1 as parent,
[OrderLocation!1!Name!element] = OrderLocations.[Name],
[OrderLocation!1!Contact!element] = OrderContacts.Contact,
[OrderLocation!1!ContactName!element] = OrderContacts.[Name],
[OrderLocation!1!LocationType!element] = OrderLocationAddressType.TypeName,
[OrderLocation!1!Building!element] = Buildings.[Description],
[OrderLocation!1!BuildingExternalLocationType!element] = BuildingXmlAddressLocationTypeMap.ExternalCode,
[OrderLocation!1!BuildingCrossStreet!element] = null,
[OrderLocation!1!BuildingServiceEntrance!element] = null,
[OrderLocation!1!BuildingSiteNote!element] = null,
[OrderLocationAddress!2!AddressLocationType!element] = null,
[OrderLocationAddress!2!Address!element] = null,
[OrderLocationAddress!2!Address2!element] = null,
[OrderLocationAddress!2!Address3!element] = null,
[OrderLocationAddress!2!City!element] = null,
[OrderLocationAddress!2!State!element] = null,
[OrderLocationAddress!2!Zip!element] = null,
[OrderLocationAddress!2!Country!element] = null,
[OrderLocationAddress!2!CountryName!element] = null,
[BuildingConstraint!3!Description!element] = BuildingConstraintItems.[Description],
[BuildingConstraint!3!ConstraintFlag!element] = OrderLocationBuildingConstraint.ConstraintFlag,
[BuildingConstraint!3!Note!element] = OrderLocationBuildingConstraint.Note
from OrderLocations
left outer join OrderContacts on OrderContacts.OrderContactID = OrderLocations.OrderContactFID
left outer join OrderLocationAddressType on
OrderLocationAddressType.OrderLocationAddressTypeID = OrderLocations.OrderLocationAddressTypeFID
left outer join Buildings on Buildings.BuildingID = OrderLocations.BuildingFID
left outer join XmlAddressLocationTypeMap as BuildingXmlAddressLocationTypeMap on
BuildingXmlAddressLocationTypeMap.AddressLocationTypeFID = Buildings.AddressLocationTypeFID and
BuildingXmlAddressLocationTypeMap.XmlInterfaceFID = @inXmlInterfaceID
left outer join OrderLocationBuildingConstraint on
OrderLocationBuildingConstraint.OrderLocationFID = OrderLocations.OrderLocationID
left outer join BuildingConstraintItems on
BuildingConstraintItems.BuildingConstraintItemID = OrderLocationBuildingConstraint.BuildingConstraintItemFID
where OrderLocations.OrderFID = @inOrderID
order by
[OrderLocation!1!Name!element],
[OrderLocation!1!Contact!element],
[OrderLocation!1!ContactName!element],
[OrderLocation!1!LocationType!element],
[OrderLocation!1!Building!element],
[OrderLocation!1!BuildingExternalLocationType!element],
[OrderLocationAddress!2!AddressLocationType!element],
[OrderLocationAddress!2!Address!element],
[OrderLocationAddress!2!Zip!element],
[BuildingConstraint!3!Description!element],
[BuildingConstraint!3!Note!element]
for xml explicit;
set @outStatusCode = @STATUS_CODE_PROCESSED;
end
GO
GRANT EXECUTE ON [dbo].[XmlExportOrderLocation] TO [MssExec]
GO