/**
*    
*    Upserts an Xml Order Link which links the specified system to the specified order.
*    @param @inExternalSystem - The name of an Xml System Data Type Map (i.e. 'Wheaton Order Information')
*    @param @inOrderId - Primary key of a MoversSuite order.
*    @param @inExternalKey - External code that identifies the order in the external system.
*/

create procedure [dbo].[UpsertXmlOrderLink]
    @inXmlSystemDataTypeMap varchar(64),
    @inOrderId int,
    @inExternalKey varchar(60)
as
set nocount on

declare @theXmlSystemFid int
select @theXmlSystemFid = XmlSystemFID
from XmlSystemDataTypeMap
where [Name] = @inXmlSystemDataTypeMap

merge XmlOrderLink as target
using ( values( @inOrderID, @theXmlSystemFid, @inExternalKey ) ) as source ( OrderFID, XmlSystemFID, ExternalKey )
on ( target.OrderFID = source.OrderFID and target.XmlSystemFID = source.XmlSystemFID )
when matched then
    update set target.ExternalKey = source.ExternalKey
when not matched then
    insert( OrderFID, XmlSystemFID, ExternalKey )
    values( source.OrderFID, source.XmlSystemFID, source.ExternalKey );
GO
GRANT EXECUTE ON  [dbo].[UpsertXmlOrderLink] TO [MssExec]
GO