/*
* Gets connection information needed to communicate with the Wheaton web service.
*/

create procedure [dbo].[GetWheatonConnectionInfo]
as
    declare @theWheatonXmlInterfaceId int
    declare @theWheatonXmlSystemId int
    declare @theAgentId varchar(8)
    declare @theUserId varchar(32)
    declare @theWebServiceUrl varchar(512)

    select @theWheatonXmlSystemId = XmlSystemFID
    from XmlSystemDataTypeMap
    where
        [Name] = 'Wheaton Order Information'

    select @theWheatonXmlInterfaceId = XmlInterfaceFID
    from XmlSystem
    where XmlSystemID = @theWheatonXmlSystemId

    select @theAgentId = [Value]
    from XmlSystemOptions
    where
        [Name] = 'Agent ID' and
        XmlSystemFID = @theWheatonXmlSystemId
    
    select @theUserId = [Value]
    from XmlSystemOptions
    where
        [Name] = 'User ID' and
        XmlSystemFID = @theWheatonXmlSystemId

    select @theWebServiceUrl = [Value]
    from XmlSystemOptions
    where
        [Name] = 'Web Service URL' and
        XmlSystemFID = @theWheatonXmlSystemId

    select
        @theAgentId as AgentId,
        @theUserId as UserId,
        @theWebServiceUrl as WebServiceUrl,
        [Validation].KeyValue as ScrambledToken
    from [Validation]
    where [Validation].NameKey = 'WHEATON'
GO
GRANT EXECUTE ON  [dbo].[GetWheatonConnectionInfo] TO [MssExec]
GO