CREATE PROCEDURE [dbo].[XmlExportOrderContact]
@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 @theMainPhoneTypeId int
declare @theFaxPhoneTypeId int
declare @theMobilePhoneTypeId 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 @theMainPhoneTypeId = OrderContactPhoneType.OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Main'
select @theFaxPhoneTypeId = OrderContactPhoneType.OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Fax'
select @theMobilePhoneTypeId = OrderContactPhoneType.OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Mobile'
select
1 as tag,
null as parent,
[OrderContact!1!Name!element] = OrderContacts.[Name],
[OrderContact!1!Contact!element] = OrderContacts.Contact,
[OrderContact!1!Title!element] = OrderContacts.Title,
[OrderContact!1!Email!element] = OrderContacts.Email,
[OrderContact!1!Address!element] = OrderContactAddress.Address1,
[OrderContact!1!Address2!element] = OrderContactAddress.Address2,
[OrderContact!1!Address3!element] = OrderContactAddress.Address3,
[OrderContact!1!City!element] = OrderContactAddress.City,
[OrderContact!1!State!element] = OrderContactAddress.State,
[OrderContact!1!Zip!element] = OrderContactAddress.PostalCode,
[OrderContact!1!Country!element] = CountryCodeStandard.VanlineCountryCode,
[OrderContact!1!CountryName!element] = CountryCodeStandard.CountryName,
[MainPhone!2!Number!element] = null,
[MainPhone!2!Country!element] = null,
[MainPhone!2!CountryName!element] = null,
[MainPhone!2!CountryCode!element] = null,
[MainPhone!2!AreaCode!element] = null,
[MainPhone!2!LocalNumber!element] = null,
[MainPhone!2!Extension!element] = null,
[Fax!3!Number!element] = null,
[Fax!3!Country!element] = null,
[Fax!3!CountryName!element] = null,
[Fax!3!CountryCode!element] = null,
[Fax!3!AreaCode!element] = null,
[Fax!3!LocalNumber!element] = null,
[Fax!3!Extension!element] = null,
[MobilePhone!4!Number!element] = null,
[MobilePhone!4!Country!element] = null,
[MobilePhone!4!CountryName!element] = null,
[MobilePhone!4!CountryCode!element] = null,
[MobilePhone!4!AreaCode!element] = null,
[MobilePhone!4!LocalNumber!element] = null,
[MobilePhone!4!Extension!element] = null
from OrderContacts
left outer join OrderContactAddress on
OrderContactAddress.OrderContactFID = OrderContacts.OrderContactID and
OrderContactAddress.AddressTypeFID = @theMainAddressTypeId
left outer join CountryCodeStandard on
CountryCodeStandard.CountryCodeStandardID = OrderContactAddress.CountryCodeStandardFID
where OrderContacts.OrderFID = @inOrderID
union all
select
2 as tag,
1 as parent,
[OrderContact!1!Name!element] = OrderContacts.[Name],
[OrderContact!1!Contact!element] = OrderContacts.Contact,
[OrderContact!1!Title!element] = OrderContacts.Title,
[OrderContact!1!Email!element] = OrderContacts.Email,
[OrderContact!1!Address!element] = null,
[OrderContact!1!Address2!element] = null,
[OrderContact!1!Address3!element] = null,
[OrderContact!1!City!element] = null,
[OrderContact!1!State!element] = null,
[OrderContact!1!Zip!element] = null,
[OrderContact!1!Country!element] = null,
[OrderContact!1!CountryName!element] = null,
[MainPhone!2!Number!element] = dbo.GetFormattedPhoneNumber( MainPhone.CountryCodeStandardFID, MainPhone.AreaCode, MainPhone.LocalNumber ),
[MainPhone!2!Country!element] = MainPhoneCountryCodeStandard.VanlineCountryCode,
[MainPhone!2!CountryName!element] = MainPhoneCountryCodeStandard.CountryName,
[MainPhone!2!CountryCode!element] = MainPhoneCountryCodeStandard.PhoneCountryCode,
[MainPhone!2!AreaCode!element] = MainPhone.AreaCode,
[MainPhone!2!LocalNumber!element] = MainPhone.LocalNumber,
[MainPhone!2!Extension!element] = MainPhone.Extension,
[Fax!3!Number!element] = null,
[Fax!3!Country!element] = null,
[Fax!3!CountryName!element] = null,
[Fax!3!CountryCode!element] = null,
[Fax!3!AreaCode!element] = null,
[Fax!3!LocalNumber!element] = null,
[Fax!3!Extension!element] = null,
[MobilePhone!4!Number!element] = null,
[MobilePhone!4!Country!element] = null,
[MobilePhone!4!CountryName!element] = null,
[MobilePhone!4!CountryCode!element] = null,
[MobilePhone!4!AreaCode!element] = null,
[MobilePhone!4!LocalNumber!element] = null,
[MobilePhone!4!Extension!element] = null
from OrderContacts
left outer join OrderContactPhoneNumber as MainPhone on
MainPhone.OrderContactFID = OrderContacts.OrderContactID and
MainPhone.OrderContactPhoneTypeFID = @theMainPhoneTypeId
left outer join CountryCodeStandard as MainPhoneCountryCodeStandard on
MainPhoneCountryCodeStandard.CountryCodeStandardID = MainPhone.CountryCodeStandardFID
where OrderContacts.OrderFID = @inOrderID
union all
select
3 as tag,
1 as parent,
[OrderContact!1!Name!element] = OrderContacts.[Name],
[OrderContact!1!Contact!element] = OrderContacts.Contact,
[OrderContact!1!Title!element] = OrderContacts.Title,
[OrderContact!1!Email!element] = OrderContacts.Email,
[OrderContact!1!Address!element] = null,
[OrderContact!1!Address2!element] = null,
[OrderContact!1!Address3!element] = null,
[OrderContact!1!City!element] = null,
[OrderContact!1!State!element] = null,
[OrderContact!1!Zip!element] = null,
[OrderContact!1!Country!element] = null,
[OrderContact!1!CountryName!element] = null,
[MainPhone!2!Number!element] = null,
[MainPhone!2!Country!element] = null,
[MainPhone!2!CountryName!element] = null,
[MainPhone!2!CountryCode!element] = null,
[MainPhone!2!AreaCode!element] = null,
[MainPhone!2!LocalNumber!element] = null,
[MainPhone!2!Extension!element] = null,
[Fax!3!Number!element] = dbo.GetFormattedPhoneNumber( FaxPhone.CountryCodeStandardFID, FaxPhone.AreaCode, FaxPhone.LocalNumber ),
[Fax!3!Country!element] = FaxPhoneCountryCodeStandard.VanlineCountryCode,
[Fax!3!CountryName!element] = FaxPhoneCountryCodeStandard.CountryName,
[Fax!3!CountryCode!element] = FaxPhoneCountryCodeStandard.PhoneCountryCode,
[Fax!3!AreaCode!element] = FaxPhone.AreaCode,
[Fax!3!LocalNumber!element] = FaxPhone.LocalNumber,
[Fax!3!Extension!element] = FaxPhone.Extension,
[MobilePhone!4!Number!element] = null,
[MobilePhone!4!Country!element] = null,
[MobilePhone!4!CountryName!element] = null,
[MobilePhone!4!CountryCode!element] = null,
[MobilePhone!4!AreaCode!element] = null,
[MobilePhone!4!LocalNumber!element] = null,
[MobilePhone!4!Extension!element] = null
from OrderContacts
left outer join OrderContactPhoneNumber as FaxPhone on
FaxPhone.OrderContactFID = OrderContacts.OrderContactID and
FaxPhone.OrderContactPhoneTypeFID = @theFaxPhoneTypeId
left outer join CountryCodeStandard as FaxPhoneCountryCodeStandard on
FaxPhoneCountryCodeStandard.CountryCodeStandardID = FaxPhone.CountryCodeStandardFID
where OrderContacts.OrderFID = @inOrderID
union all
select
4 as tag,
1 as parent,
[OrderContact!1!Name!element] = OrderContacts.[Name],
[OrderContact!1!Contact!element] = OrderContacts.Contact,
[OrderContact!1!Title!element] = OrderContacts.Title,
[OrderContact!1!Email!element] = OrderContacts.Email,
[OrderContact!1!Address!element] = null,
[OrderContact!1!Address2!element] = null,
[OrderContact!1!Address3!element] = null,
[OrderContact!1!City!element] = null,
[OrderContact!1!State!element] = null,
[OrderContact!1!Zip!element] = null,
[OrderContact!1!Country!element] = null,
[OrderContact!1!CountryName!element] = null,
[MainPhone!2!Number!element] = null,
[MainPhone!2!Country!element] = null,
[MainPhone!2!CountryName!element] = null,
[MainPhone!2!CountryCode!element] = null,
[MainPhone!2!AreaCode!element] = null,
[MainPhone!2!LocalNumber!element] = null,
[MainPhone!2!Extension!element] = null,
[Fax!3!Number!element] = null,
[Fax!3!Country!element] = null,
[Fax!3!CountryName!element] = null,
[Fax!3!CountryCode!element] = null,
[Fax!3!AreaCode!element] = null,
[Fax!3!LocalNumber!element] = null,
[Fax!3!Extension!element] = null,
[MobilePhone!4!Number!element] = dbo.GetFormattedPhoneNumber( MobilePhone.CountryCodeStandardFID, MobilePhone.AreaCode, MobilePhone.LocalNumber ),
[MobilePhone!4!Country!element] = MobilePhoneCountryCodeStandard.VanlineCountryCode,
[MobilePhone!4!CountryName!element] = MobilePhoneCountryCodeStandard.CountryName,
[MobilePhone!4!CountryCode!element] = MobilePhoneCountryCodeStandard.PhoneCountryCode,
[MobilePhone!4!AreaCode!element] = MobilePhone.AreaCode,
[MobilePhone!4!LocalNumber!element] = MobilePhone.LocalNumber,
[MobilePhone!4!Extension!element] = MobilePhone.Extension
from OrderContacts
left outer join OrderContactPhoneNumber as MobilePhone on
MobilePhone.OrderContactFID = OrderContacts.OrderContactID and
MobilePhone.OrderContactPhoneTypeFID = @theMobilePhoneTypeId
left outer join CountryCodeStandard as MobilePhoneCountryCodeStandard on
MobilePhoneCountryCodeStandard.CountryCodeStandardID = MobilePhone.CountryCodeStandardFID
where OrderContacts.OrderFID = @inOrderID
order by
[OrderContact!1!Name!element],
[OrderContact!1!Contact!element],
[OrderContact!1!Title!element],
[OrderContact!1!Email!element],
[MobilePhone!4!Number!element],
[Fax!3!Number!element],
[MainPhone!2!Number!element]
for xml explicit;
set @outStatusCode = @STATUS_CODE_PROCESSED;
end
GO
GRANT EXECUTE ON [dbo].[XmlExportOrderContact] TO [MssExec]
GO