/*
*    Description: Looks up an order by the specified XmlSystem and external key and optionally
*    creates a new order if a matching order is not found.
*/
CREATE PROCEDURE [dbo].[GetOrderByXmlOrderLink]
    @inXmlSystemDataTypeMapName varchar(64),
    @inExternalOrderKey varchar(60),
    @inCreateOrderIfNotFound bit,
    @inShipperLastName varchar(26),
    @inShipperFirstName varchar(16),
    @inBookAgentID varchar(10),
    @inOriginAgentID varchar(10),
    @inDestinationAgentID varchar(10),
    @outOrderID int output
as
set nocount on

declare @theBranchPriKey int
declare @theExternalAppUserId int

select
    @outOrderID = null

select top 1
    @outOrderID = XmlOrderLink.OrderFID
from XmlSystemDataTypeMap
inner join XmlOrderLink on XmlOrderLink.XmlSystemFID = XmlSystemDataTypeMap.XmlSystemFID and
    XmlOrderLink.ExternalKey = @inExternalOrderKey and
    XmlOrderLink.SecondaryExternalKey is null
where XmlSystemDataTypeMap.[Name] = @inXmlSystemDataTypeMapName
order by XmlOrderLink.XmlOrderLinkID desc

if( @outOrderID is null and isnull( @inCreateOrderIfNotFound, 0 ) = 1 )
begin
select
    @theExternalAppUserId = (
        select SysUser.SysUserID
        from SysUser
        where SysUser.LastName = 'Application' and
            SysUser.FirstName = 'External'
    )

    -- Find the first branch that is linked to the book, orig or dest agents, in that order,
    select top 1
        @theBranchPriKey = AgentBranches.BranchPriKey
    from
    (
        select
            SortBy = 1,
            BranchPriKey = Branch.BranchPriKey
        from Agent
        inner join Branch on Branch.AgentPriKey = Agent.AgentPriKey
        where Agent.AgentID = @inBookAgentID
        union all
        select
            SortBy = 2,
            BranchPriKey = Branch.BranchPriKey
        from Agent
        inner join Branch on Branch.AgentPriKey = Agent.AgentPriKey
        where Agent.AgentID = @inOriginAgentID
        union all
        select
            SortBy = 3,
            BranchPriKey = Branch.BranchPriKey
        from Agent
        inner join Branch on Branch.AgentPriKey = Agent.AgentPriKey
        where Agent.AgentID = @inDestinationAgentID
    ) as AgentBranches
    order by AgentBranches.SortBy

    -- Easy way to create a minimal order.
    exec AddEntryLevelOrder
        @inBranchPriKey = @theBranchPriKey,
        @inCurrentUserId = @theExternalAppUserId,
        @inLastName = @inShipperLastName,
        @inOrderNumber = null,
        @inOrderSeg = null,
        @outExistingOrderId = @outOrderID output,
        @inVanlineId = null,
        @inPSeg = null,
        @inOSeg = null,
        @inSSeg = null,
        @inBookDate = null,
        @inFirstName = @inShipperFirstName,
        @inMoveTypeId = null,
        @inCommodityTypeId = null,
        @inAuthorityTypeId = null,
        @inSalespersonId = null,
        @inRevenueClerkId = null,
        @inRatePlanId = null,
        @inDivisionId = null,
        @inCustomerNumber = null,
        @inUpdateSource = @inXmlSystemDataTypeMapName

    if( @outOrderID is not null and @inExternalOrderKey is not null )
    begin
        insert into XmlOrderLink
        (
            OrderFID,
            XmlSystemFID,
            ExternalKey,
            SecondaryExternalKey
        )
        select
            OrderFID = @outOrderID,
            XmlSystemFID = XmlSystemDataTypeMap.XmlSystemFID,
            ExternalKey = @inExternalOrderKey,
            SecondaryExternalKey = null
        from XmlSystemDataTypeMap
        where XmlSystemDataTypeMap.[Name] = @inXmlSystemDataTypeMapName
        except
        select
            OrderFID,
            XmlSystemFID,
            ExternalKey,
            SecondaryExternalKey
        from XmlOrderLink
    end

end
GO
GRANT EXECUTE ON  [dbo].[GetOrderByXmlOrderLink] TO [MssExec]
GO