USP_SALESORDER_PREREGISTEREDPROGRAMEVENTTICKET_EDIT

CREATE procedure dbo.USP_SALESORDER_PREREGISTEREDPROGRAMEVENTTICKET_EDIT
(
    @ID uniqueidentifier,
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @EVENTID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @REGISTRANTS xml = null,
    @CURRENTDATE datetime = null,
    @MARKREGISTRANTSATTENDED bit = 0
)
as
    set nocount on;

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

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    exec dbo.USP_SALESORDER_PREREGISTEREDEVENT_UPDATEHOST @CHANGEAGENTID, @SALESORDERID, @EVENTID, @CONSTITUENTID

    declare @SALESMETHODTYPECODE tinyint;
    select @SALESMETHODTYPECODE = [SALESMETHODTYPECODE] from dbo.[SALESORDER] where [ID] = @SALESORDERID;

    declare @HOSTREGISTRANTID uniqueidentifier = null
    declare @HOSTCONSTITUENTID uniqueidentifier = null
    select top 1 
        @HOSTREGISTRANTID = [REGISTRANT].[GUESTOFREGISTRANTID],
        @HOSTCONSTITUENTID = (select [REG].[CONSTITUENTID] from dbo.[REGISTRANT] as [REG] where [REG].[ID] = [REGISTRANT].[GUESTOFREGISTRANTID])
    from dbo.[REGISTRANT]
    inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
        on [REGISTRANT].[ID] = [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID]
    inner join dbo.[SALESORDERITEMTICKET]
        on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
    inner join dbo.[SALESORDERITEM]
        on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
    where
        [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
        [SALESORDERITEMTICKET].[EVENTID] = @EVENTID and
        [REGISTRANT].[GUESTOFREGISTRANTID] is not null

    if @HOSTREGISTRANTID is null
    begin
        --Host is only registrant

        select top 1 
            @HOSTREGISTRANTID = [REGISTRANT].[ID],
            @HOSTCONSTITUENTID = [REGISTRANT].[CONSTITUENTID]
        from dbo.[REGISTRANT]
        inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
            on [REGISTRANT].[ID] = [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID]
        inner join dbo.[SALESORDERITEMTICKET]
            on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[SALESORDERITEM]
            on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
        where
            [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
            [SALESORDERITEMTICKET].[EVENTID] = @EVENTID
    end

    -- Assign registrant IDs if they weren't passed in

    -- Assign the host registrant id if the host + guest record exists

    -- If a new constituent has taken the place of the host constituent, assign a new registrant id for that constituent

    set @REGISTRANTS = (
        select
            case 
                when T.registrants.value('(CONSTITUENTID)[1]', 'uniqueidentifier') = @HOSTCONSTITUENTID then @HOSTREGISTRANTID
                when T.registrants.value('(ID)[1]', 'uniqueidentifier') = @HOSTREGISTRANTID  then newid()
                else isnull(T.registrants.value('(ID)[1]', 'uniqueidentifier'), newid()) 
            end as [ID],
            T.registrants.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') as [PRICETYPECODEID],
            T.registrants.value('(CONSTITUENTID)[1]', 'uniqueidentifier') as [CONSTITUENTID],
            T.registrants.value('(NOTES)[1]', 'nvarchar(255)') as [NOTES],
            T.registrants.query('./PREFERENCES/ITEM') as [PREFERENCES],
            T.registrants.query('./REGISTRANTREGISTRATIONINFORMATION/ITEM') as [REGISTRANTREGISTRATIONINFORMATION]
        from
            @REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants)
        for xml raw('ITEM'), type, elements, root('REGISTRANTS'), binary base64
    );

    declare @ORDERREGISTRANTS table
    (
        ID uniqueidentifier,
        PRICETYPECODEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        NOTES nvarchar(255),
        SALESORDERITEMID uniqueidentifier
    );

    declare @ORDERREGISTRANTPREFERENCES table
    (
        ID uniqueidentifier,
        REGISTRANTID uniqueidentifier,
        EVENTPREFERENCEID uniqueidentifier
    );

    declare @ORDERREGISTRANTREGISTRATIONINFORMATION table
    (
        ID uniqueidentifier,
        REGISTRANTID uniqueidentifier,
        REGISTRATIONINFORMATIONID uniqueidentifier,
        RESPONSETYPECODE tinyint,
        PERSONDETAILTYPECODE tinyint,
        TEXTVALUE nvarchar(max),
        REGISTRATIONINFORMATIONOPTIONID uniqueidentifier                                
    );

    declare @AVAILABLEPRICETYPES table
    (
        PRICETYPECODEID uniqueidentifier,
        DESCRIPTION nvarchar(100),
        FACEPRICE money
    );

    insert into @ORDERREGISTRANTS
    (
        ID,
        PRICETYPECODEID,
        CONSTITUENTID,
        NOTES
    )
    select
        T.registrants.value('(ID)[1]', 'uniqueidentifier'),
        T.registrants.value('(PRICETYPECODEID)[1]', 'uniqueidentifier'),
        T.registrants.value('(CONSTITUENTID)[1]', 'uniqueidentifier'),
        isnull(T.registrants.value('(NOTES)[1]', 'nvarchar(255)'), '')
    from
        @REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants);

    --Update order registrants with the sales order item id which they belong

    update @ORDERREGISTRANTS
    set SALESORDERITEMID = (
        select SALESORDERITEM.ID
        from SALESORDERITEM
        inner join dbo.SALESORDERITEMTICKET
            on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        where
            SALESORDERITEM.SALESORDERID = @SALESORDERID and
            SALESORDERITEMTICKET.EVENTID = @EVENTID and
            SALESORDERITEMTICKET.PRICETYPECODEID = [@ORDERREGISTRANTS].PRICETYPECODEID
    )

    insert into @ORDERREGISTRANTPREFERENCES
    (
        ID,
        REGISTRANTID,
        EVENTPREFERENCEID
    )
    select
        isnull(T2.preferences.value('(ID)[1]', 'uniqueidentifier'), newid()),
        T.registrants.value('(ID)[1]', 'uniqueidentifier'),
        T2.preferences.value('(EVENTPREFERENCEID)[1]', 'uniqueidentifier')
    from
        @REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants)
    cross apply
        T.registrants.nodes('./PREFERENCES/ITEM') T2(preferences);

    insert into @ORDERREGISTRANTREGISTRATIONINFORMATION
    (
        ID,
        REGISTRANTID,
        REGISTRATIONINFORMATIONID,
        RESPONSETYPECODE,
        PERSONDETAILTYPECODE,
        TEXTVALUE,
        REGISTRATIONINFORMATIONOPTIONID                    
    )
    select
        isnull(T2.registrationinformation.value('(ID)[1]', 'uniqueidentifier'), newid()),
        T.registrants.value('(ID)[1]', 'uniqueidentifier'),
        T2.registrationinformation.value('(REGISTRATIONINFORMATIONID)[1]', 'uniqueidentifier'),                    
        T2.registrationinformation.value('(RESPONSETYPECODE)[1]', 'tinyint'),
        T2.registrationinformation.value('(PERSONDETAILTYPECODE)[1]', 'tinyint'),
        isnull(T2.registrationinformation.value('(TEXTVALUE)[1]', 'nvarchar(max)'), ''),
        cast(nullif(T2.registrationinformation.value('(REGISTRATIONINFORMATIONOPTIONID)[1]', 'nvarchar(36)'), '') as uniqueidentifier)
    from
        @REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants)
    cross apply
        T.registrants.nodes('./REGISTRANTREGISTRATIONINFORMATION/ITEM') T2(registrationinformation);

    insert into @AVAILABLEPRICETYPES
    (
        PRICETYPECODEID,
        DESCRIPTION,
        FACEPRICE
    )
    select
        PRICETYPECODE.ID,
        PRICETYPECODE.DESCRIPTION,
        PROGRAMEVENTPRICE.FACEPRICE
    from
        dbo.UFN_SALESMETHOD_ALLOWEDPRICETYPES(@SALESMETHODTYPECODE, 0) as PRICETYPECODE
    inner join
        dbo.UFN_PROGRAMEVENT_GETPRICES(@EVENTID, 0) as PROGRAMEVENTPRICE on PROGRAMEVENTPRICE.PRICETYPECODEID = PRICETYPECODE.ID;

    begin try
        if exists (
            select top 1 1 from @ORDERREGISTRANTS
            where PRICETYPECODEID not in (select PRICETYPECODEID from @AVAILABLEPRICETYPES)
        )
            raiserror('ERR_PRICETYPENOTFORSALE', 13, 1);

        declare @HOSTREGISTRANTCOUNT int;

        select 
            @HOSTREGISTRANTCOUNT = count(*)
        from @REGISTRANTS.nodes('/REGISTRANTS/ITEM') T(registrants)
        where T.registrants.value('(CONSTITUENTID)[1]', 'uniqueidentifier') = @HOSTCONSTITUENTID;

        if @HOSTREGISTRANTCOUNT > 1
            raiserror('ERR_ONEREGISTRANTPERPATRON', 13, 1);

        -- Remove registrants no longer registered for event

        declare @contextCache varbinary(128);

        --cache current context information

        set @contextCache = CONTEXT_INFO();

        --set CONTEXT_INFO to @CHANGEAGENTID

        set CONTEXT_INFO @CHANGEAGENTID;

        declare @DELETEREGISTRANTS table (
            REGISTRANTID uniqueidentifier
        );

        insert into @DELETEREGISTRANTS
        select REGISTRANT.ID
        from dbo.REGISTRANT
        inner join dbo.SALESORDERITEMTICKETREGISTRANT
            on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
        inner join dbo.SALESORDERITEMTICKET
            on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID
        inner join dbo.SALESORDERITEM
            on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
        where
            SALESORDERITEM.SALESORDERID = @SALESORDERID and
            SALESORDERITEMTICKET.EVENTID = @EVENTID and
            not exists (
                select 1
                from @ORDERREGISTRANTS as [ORDERREGISTRANTS]
                where ORDERREGISTRANTS.ID = REGISTRANT.ID
            );

        delete from dbo.SALESORDERITEMTICKETREGISTRANT
        where 
            exists (
                select 1
                from @DELETEREGISTRANTS as [DELETEREGISTRANTS]
                where DELETEREGISTRANTS.REGISTRANTID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
            )

        delete from dbo.REGISTRANT
        where 
            exists (
                select 1
                from @DELETEREGISTRANTS as [DELETEREGISTRANTS]
                where 
                    DELETEREGISTRANTS.REGISTRANTID = REGISTRANT.ID and
                    REGISTRANT.ID <> @HOSTREGISTRANTID
            )

        -- reset CONTEXT_INFO to previous value 

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

        -- Edit tickets

        declare @PRICETYPES xml;

        set @PRICETYPES = (
            select
                isnull([REGISTRANTS].[SALESORDERITEMID],newid()) as [ID],
                PRICETYPES.DESCRIPTION as [PRICETYPE],
                REGISTRANTS.PRICETYPECODEID,
                PRICETYPES.FACEPRICE,
                count(REGISTRANTS.PRICETYPECODEID) as [QUANTITY]
            from
                @ORDERREGISTRANTS as REGISTRANTS
            inner join
                @AVAILABLEPRICETYPES as PRICETYPES on REGISTRANTS.PRICETYPECODEID = PRICETYPES.PRICETYPECODEID
            group by
                REGISTRANTS.SALESORDERITEMID,
                PRICETYPES.DESCRIPTION,
                REGISTRANTS.PRICETYPECODEID,
                PRICETYPES.FACEPRICE
            for xml raw('ITEM'), type, elements, root('PRICETYPES'), binary base64
        );

        declare @PROGRAMID uniqueidentifier
        select @PROGRAMID = [EVENT].[PROGRAMID]
        from dbo.[EVENT]
        where [EVENT].[ID] = @EVENTID

        exec dbo.USP_SALESORDERITEMTICKETS_EDIT
            @ID,
            @EVENTID,
            @SALESORDERID,
            @PRICETYPES,
            0,
            @CHANGEAGENTID,
            @PROGRAMID,
            @CURRENTDATE;

        --Make sure that the constituent is marked as will not attend if not in orderregistrants

        update dbo.[REGISTRANT]
        set
            [WILLNOTATTEND] = 1,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        where 
            REGISTRANT.ID = @HOSTREGISTRANTID and
            exists (
                select 1
                from @DELETEREGISTRANTS as [DELETEREGISTRANTS]
                where DELETEREGISTRANTS.REGISTRANTID = @HOSTREGISTRANTID
            )

        --Make sure that the constituent is marked as will attend if in orderregistrants

        update dbo.[REGISTRANT]
        set
            [WILLNOTATTEND] = 0,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        where 
            REGISTRANT.ID = @HOSTREGISTRANTID and
            exists (
                select 1
                from @ORDERREGISTRANTS as [ORDERREGISTRANTS]
                where [ORDERREGISTRANTS].[ID] = @HOSTREGISTRANTID
            )

        --See if any registrant constituents have relocated to a different registrant id and null the constituent since just going straight to update could create a duplicate constituent registration

        update dbo.[REGISTRANT]
        set 
            [CONSTITUENTID] = null,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        from @ORDERREGISTRANTS as [ORDERREGISTRANTS]
        where 
            [REGISTRANT].[ID] = [ORDERREGISTRANTS].[ID] and 
            (
                [REGISTRANT].[CONSTITUENTID] <> [ORDERREGISTRANTS].[CONSTITUENTID] or
                [ORDERREGISTRANTS].[CONSTITUENTID] is null
            )


        --Now update the registrants

        update dbo.[REGISTRANT]
        set
            [REGISTRANT].[CONSTITUENTID] = [ORDERREGISTRANTS].[CONSTITUENTID],
            [REGISTRANT].[NOTES] = [ORDERREGISTRANTS].[NOTES],
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        from @ORDERREGISTRANTS as [ORDERREGISTRANTS]
        where [REGISTRANT].[ID] = [ORDERREGISTRANTS].[ID]

        -- Mark all registrants that exist on this as attended if box is checked

        -- Otherwise don't touch them (we do not assume the negative of box unchecked unmarking all)

        if @MARKREGISTRANTSATTENDED = 1
        begin
            update dbo.[REGISTRANT] set
                [REGISTRANT].[ATTENDED] = 1,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
            from @ORDERREGISTRANTS as [ORDERREGISTRANTS]
            where [REGISTRANT].[ID] = [ORDERREGISTRANTS].[ID]
        end

        --Registrants might have changed pricetypes.  Make sure the registrant tickets point to the correct ticket id

        update dbo.[SALESORDERITEMTICKETREGISTRANT]
        set 
            [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [TICKET].[TICKETID],
            [SALESORDERITEMTICKETREGISTRANT].[DATECHANGED] = @CURRENTDATE,
            [SALESORDERITEMTICKETREGISTRANT].[CHANGEDBYID] = @CHANGEAGENTID
        from @ORDERREGISTRANTS as [ORDERREGISTRANTS]
        cross apply (
            select top 1 [SALESORDERITEMTICKET].[ID] as [TICKETID]
            from dbo.[SALESORDERITEMTICKET]
            inner join dbo.[SALESORDERITEM]
                on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
            where 
                [SALESORDERITEMTICKET].[EVENTID] = @EVENTID and
                [SALESORDERITEMTICKET].[PRICETYPECODEID] =  [ORDERREGISTRANTS].[PRICETYPECODEID] and
                [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
        ) as [TICKET]
        where [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID] = [ORDERREGISTRANTS].[ID]

        insert into dbo.REGISTRANT
        (
            ID,
            EVENTID,
            CONSTITUENTID,
            WILLNOTATTEND,
            GUESTOFREGISTRANTID,
            NOTES,
            ATTENDED,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            ID,
            @EVENTID,
            CONSTITUENTID,
            0,
            @HOSTREGISTRANTID,
            NOTES,
            @MARKREGISTRANTSATTENDED,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from @ORDERREGISTRANTS as [ORDERREGISTRANTS]
        where
            not exists (
                select 1
                from dbo.[REGISTRANT]
                where [REGISTRANT].[ID] = [ORDERREGISTRANTS].[ID]
            )

        --cache current context information

        set @contextCache = CONTEXT_INFO();

        --set CONTEXT_INFO to @CHANGEAGENTID

        set CONTEXT_INFO @CHANGEAGENTID;

        --Delete preferences that are no longer valid for the existing registrants

        delete from dbo.[REGISTRANTPREFERENCE]
        where 
            exists (
                select 1
                from @ORDERREGISTRANTS as [REGISTRANTS]
                where [REGISTRANTS].[ID] = [REGISTRANTPREFERENCE].[REGISTRANTID]
            ) and
            not exists (
                select 1
                from @ORDERREGISTRANTPREFERENCES as [PREFERENCES]
                where [PREFERENCES].[ID] = [REGISTRANTPREFERENCE].[ID]
            )

        -- reset CONTEXT_INFO to previous value 

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

        --Update registrant preferences

        update dbo.[REGISTRANTPREFERENCE]
        set
            [REGISTRANTPREFERENCE].[REGISTRANTID] = [PREFERENCES].[REGISTRANTID],
            [REGISTRANTPREFERENCE].[EVENTPREFERENCEID] = [PREFERENCES].[EVENTPREFERENCEID],
            [REGISTRANTPREFERENCE].[CHANGEDBYID] = @CHANGEAGENTID,
            [REGISTRANTPREFERENCE].[DATECHANGED] = @CURRENTDATE
        from @ORDERREGISTRANTPREFERENCES as [PREFERENCES]
        where [REGISTRANTPREFERENCE].[ID] = [PREFERENCES].[ID]

        insert into dbo.REGISTRANTPREFERENCE
        (
            ID,
            REGISTRANTID,
            EVENTPREFERENCEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            ID,
            REGISTRANTID,
            EVENTPREFERENCEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from @ORDERREGISTRANTPREFERENCES as [PREFERENCES]
        where
            not exists (
                select 1
                from dbo.[REGISTRANTPREFERENCE]
                where [REGISTRANTPREFERENCE].[ID] = [PREFERENCES].[ID]
            )                    

        merge into dbo.REGISTRANTREGISTRATIONINFORMATION as target
        using (
            select
                ID,
                REGISTRANTID,
                REGISTRATIONINFORMATIONID,
                RESPONSETYPECODE,
                PERSONDETAILTYPECODE,
                TEXTVALUE,
                case RESPONSETYPECODE
                    when 3 then
                        case REGISTRATIONINFORMATIONOPTIONID
                            when '11111111-1111-1111-1111-111111111111' then 1
                            else 0
                        end
                    else 0
                end as BOOLEANVALUE,
                case RESPONSETYPECODE
                    when 2 then REGISTRATIONINFORMATIONOPTIONID
                    else null
                end as REGISTRATIONINFORMATIONOPTIONID
            from @ORDERREGISTRANTREGISTRATIONINFORMATION
        ) as source
            on target.ID = source.ID
        when matched and (
                target.REGISTRANTID <> source.REGISTRANTID
                or (source.RESPONSETYPECODE in (0,1,4) and source.TEXTVALUE <> target.TEXTVALUE)
                or (source.RESPONSETYPECODE = 2 
                    and ( 
                        (source.REGISTRATIONINFORMATIONOPTIONID is null and target.REGISTRATIONINFORMATIONOPTIONID is not null)
                        or (source.REGISTRATIONINFORMATIONOPTIONID is not null and target.REGISTRATIONINFORMATIONOPTIONID is null)
                        or (source.REGISTRATIONINFORMATIONOPTIONID <> target.REGISTRATIONINFORMATIONOPTIONID)
                    )
                ) 
                or (source.RESPONSETYPECODE = 3 and target.BOOLEANVALUE <> source.BOOLEANVALUE)
            )then
            update
                set
                    target.REGISTRANTID = source.REGISTRANTID,
                    target.TEXTVALUE = source.TEXTVALUE,
                    target.REGISTRATIONINFORMATIONOPTIONID = source.REGISTRATIONINFORMATIONOPTIONID,
                    target.BOOLEANVALUE = source.BOOLEANVALUE,
                    target.CHANGEDBYID = @CHANGEAGENTID,
                    target.DATECHANGED = @CURRENTDATE
        when not matched by target then
            insert (ID, REGISTRANTID, REGISTRATIONINFORMATIONID, REGISTRATIONINFORMATIONOPTIONID, TEXTVALUE, BOOLEANVALUE, PERSONDETAILTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (source.ID, source.REGISTRANTID, source.REGISTRATIONINFORMATIONID, source.REGISTRATIONINFORMATIONOPTIONID, source.TEXTVALUE, source.BOOLEANVALUE, source.PERSONDETAILTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
        when not matched by source and target.REGISTRANTID in (select ID from @ORDERREGISTRANTS) then
            delete;


        insert into dbo.SALESORDERITEMTICKETREGISTRANT
        (
            ID,
            SALESORDERITEMTICKETID,
            REGISTRANTID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            newid(),
            SALESORDERITEMTICKET.ID,
            REGISTRANTS.ID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from dbo.SALESORDERITEMTICKET
        inner join @ORDERREGISTRANTS as REGISTRANTS 
                on REGISTRANTS.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID
        inner join
            dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        where
            SALESORDERITEM.SALESORDERID = @SALESORDERID and
            SALESORDERITEMTICKET.EVENTID = @EVENTID and
            not exists (
                select 1
                from dbo.[SALESORDERITEMTICKETREGISTRANT]
                where [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID] = [REGISTRANTS].[ID]
            )
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

return 0;