USP_COMBINATION_REMOVEINELIGIBLECOMBINATIONS

CREATE procedure dbo.USP_COMBINATION_REMOVEINELIGIBLECOMBINATIONS
(
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
begin

set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @contextCache varbinary(128);
    -- cache current context information 

    set @contextCache = CONTEXT_INFO();
    -- set CONTEXT_INFO to @CHANGEAGENTID 

    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

    declare @CONSTITUENTID uniqueidentifier = null
    select @CONSTITUENTID = [CONSTITUENTID]
    from dbo.[SALESORDER]
    where [ID] = @SALESORDERID

    -- Delete sales order item related fees

    delete [SALESORDERITEM]
    from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
        inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMFEE.SALESORDERITEMID = SALESORDERITEMTICKET.ID
        inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
        inner join dbo.COMBINATION on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID 
    where SALESORDERITEM.SALESORDERID = @SALESORDERID and dbo.UFN_COMBINATION_ELIGIBLEFORORDER(COMBINATION.ID, @CONSTITUENTID) = 0

    -- Delete sales order items in combination no longer eligible

    delete dbo.SALESORDERITEM
    from dbo.SALESORDERITEM 
        inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
        inner join dbo.COMBINATION on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID 
    where SALESORDERITEM.SALESORDERID = @SALESORDERID and dbo.UFN_COMBINATION_ELIGIBLEFORORDER(COMBINATION.ID, @CONSTITUENTID) = 0

    -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;    

end