USP_SALESORDERITEMTICKETS_ADD

            CREATE procedure dbo.USP_SALESORDERITEMTICKETS_ADD
            (
                @SALESORDERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @EVENTID uniqueidentifier,
                @PRICETYPES xml,
                @DATA xml = null,
                @OPTIONS xml = null,
                @CALLBACKURL nvarchar(255) = '',
                @SYSTEMTYPENAME nvarchar(255) = '',
                @ASSEMBLYNAME nvarchar(255) = '',
                @ATTRIBUTES xml = null,
                @CATEGORYNAME nvarchar(255) = '',
                @EXPIREDCALLBACKURL nvarchar(255) = '',
                @ACKNOWLEDGEMENT nvarchar(max) = '',
                @ALLOWPASTEVENTS bit = 0
            )
            as
                set nocount on;

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

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

                declare @SALESMETHODTYPECODE tinyint;
                declare @SALESORDERSTATUSCODE tinyint;
                declare @SALESMETHODID uniqueidentifier;

                select
                    @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE,
                    @SALESORDERSTATUSCODE = SALESORDER.STATUSCODE,
                    @SALESMETHODID = SALESMETHOD.ID
                from dbo.[SALESORDER]
                inner join dbo.SALESMETHOD on SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE
                where SALESORDER.ID = @SALESORDERID

                -- Check that event has not already gone past the off sale time                

                declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = sysdatetimeoffset()

                declare @DESCRIPTION nvarchar(100);
                declare @PROGRAMID uniqueidentifier;
                declare @EVENTENDDATETIMEWITHOFFSET datetimeoffset;

                select
                    @DESCRIPTION = EVENT.NAME,
                    @PROGRAMID = EVENT.PROGRAMID,
                    @EVENTENDDATETIMEWITHOFFSET = 
                    case
                        when [PROGRAMSALESMETHOD].[ID] is null then [EVENT].[STARTDATETIMEWITHOFFSET]
                        when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
                        when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                        when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                        when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                    end
                from dbo.[EVENT]
                left join dbo.[PROGRAMSALESMETHOD]
                    on 
                        [EVENT].[PROGRAMID] = [PROGRAMSALESMETHOD].[PROGRAMID] and
                        [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
                where 
                    [EVENT].[ID] = @EVENTID;

                if (@CURRENTDATETIMEWITHOFFSET >= @EVENTENDDATETIMEWITHOFFSET) and @ALLOWPASTEVENTS = 0
                begin
                    raiserror('ERR_PASTEVENT', 13, 1);
                end

                --For online, we need to make sure that this ticket can be delivered

                if @SALESMETHODTYPECODE = 2 and @SALESORDERSTATUSCODE <> 7 --unless it's an unresolved order

                begin
                    declare @HASUNRESTRICTEDDELIVERYMETHOD bit = 0
                    declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null
                    declare @DELIVERYMETHODEXISTS bit = 0
                    exec dbo.USP_DELIVERYMETHOD_INFO_BYSALESMETHODID 
                        @SALESMETHODID, 
                        @DELIVERYMETHODEXISTS output, 
                        @HASUNRESTRICTEDDELIVERYMETHOD output, 
                        @EARLIESTVALIDEVENTDATETIMEWITHOFFSET output

                    if @DELIVERYMETHODEXISTS = 1
                    begin
                        if @HASUNRESTRICTEDDELIVERYMETHOD = 0
                        begin
                            if @EARLIESTVALIDEVENTDATETIMEWITHOFFSET > (select top 1 [STARTDATETIMEWITHOFFSET] from dbo.[EVENT] where [ID] = @EVENTID)
                                raiserror('ERR_HASNODELIVERYMETHOD', 13, 1)
                        end
                    end
                    else
                        raiserror('ERR_HASNODELIVERYMETHOD', 13, 1)
                end

                declare @ORDERITEMS table
                    (ID uniqueidentifier,
                    QUANTITY int,
                    PRICETYPECODEID uniqueidentifier,
                    PRICETYPE nvarchar(100),
                    PRICE money);

                insert into @ORDERITEMS
                select
                    ISNULL(T.items.value('(SALESORDERITEMID)[1]','uniqueidentifier'), newid()), 
                    T.items.value('(QUANTITY)[1]','integer'), 
                    T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier'),
                    T.items.value('(PRICETYPE)[1]','nvarchar(100)'),
                    -- 4/16/09, LeeCh, Bug 19897

                    -- Changed decimal(9,2) into decimal (19,2) to

                    --     avoid decimal overflow error

                    -- See work item fix and attachment for details

                    T.items.value('(FACEPRICE)[1]','decimal(19,2)')
                from @PRICETYPES.nodes('/PRICETYPES/ITEM') T(items)
                where T.items.value('(QUANTITY)[1]','integer') > 0;

                declare @QUANTITY integer;

                select @QUANTITY = SUM(QUANTITY)
                from @ORDERITEMS;

                begin try
                    -- Check availability

                    if @QUANTITY > dbo.UFN_EVENT_GETAVAILABILITY(@EVENTID)
                    begin
                        raiserror('ERR_TICKETINSERT_UNAVAILABLE', 13, 1);
                    end

                    -- 4/16/09, LeeCh, Bug 19897, Starts

                    -- Check if total overflows by

                    --     adding the total in new order items and 

                    --     the total in existing order items

                    -- An overflow error will be processed in the 

                    --     catch block


                    declare @TOTAL money;

                    -- total in new order items

                    select @TOTAL = SUM(QUANTITY * PRICE)
                    from @ORDERITEMS;

                    -- total in existing order items

                    select @TOTAL = @TOTAL + isnull(SUM(SALESORDERITEM.TOTAL), 0)
                    from dbo.SALESORDERITEM
                    left join dbo.SALESORDERITEMFEE on
                        SALESORDERITEM.ID = SALESORDERITEMFEE.ID
                    where 
                        SALESORDERITEM.SALESORDERID = @SALESORDERID and
                        (SALESORDERITEM.TYPECODE <> 3 or 
                         (SALESORDERITEM.TYPECODE = 3 and
                          SALESORDERITEMFEE.APPLIESTOCODE = 0));

                    -- 4/16/09, LeeCh, Bug 19897, Ends


                    update dbo.SALESORDERITEM with (rowlock) set
                        SALESORDERITEM.QUANTITY = SALESORDERITEM.QUANTITY + ITEMS.QUANTITY,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from @ORDERITEMS ITEMS
                    where SALESORDERITEM.ID = ITEMS.ID;

                    update dbo.SALESORDERITEM with (rowlock) set
                    SALESORDERITEM.QUANTITY = SALESORDERITEM.QUANTITY + ITEMS.QUANTITY,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                    from @ORDERITEMS ITEMS
                    where
                    not exists(select 1 from @ORDERITEMS where ID = SALESORDERITEM.ID) and
                    SALESORDERID = @SALESORDERID and
                    SALESORDERITEM.ID in
                    ( 
                        select SALESORDERITEMTICKET.ID 
                        from dbo.SALESORDERITEMTICKET 
                        inner join dbo.SALESORDERITEM as B on SALESORDERITEMTICKET.ID = B.ID
                        left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                        where B.SALESORDERID = @SALESORDERID and
                            EVENTID = @EVENTID and 
                            SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID and
                            SALESORDERITEMTICKETCOMBINATION.ID is null
                    );

                    insert into dbo.SALESORDERITEM
                        (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DATA, OPTIONS, CALLBACKURL, SYSTEMTYPENAME, ASSEMBLYNAME, ATTRIBUTES, CATEGORYNAME, EXPIREDCALLBACKURL, ACKNOWLEDGEMENT)
                    select
                        ITEMS.ID, 
                        @SALESORDERID, 
                        0, 
                        @DESCRIPTION + ' - ' + ITEMS.PRICETYPE, 
                        ITEMS.QUANTITY, 
                        ITEMS.PRICE,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE,
                        @DATA,
                        @OPTIONS,
                        @CALLBACKURL,
                        @SYSTEMTYPENAME,
                        @ASSEMBLYNAME,
                        @ATTRIBUTES,
                        @CATEGORYNAME,
                        @EXPIREDCALLBACKURL,
                        @ACKNOWLEDGEMENT
                    from @ORDERITEMS ITEMS
                    where 
                        not exists
                        (
                            select 1 from dbo.SALESORDERITEM
                            inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                            left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                            where
                                SALESORDERITEM.ID = ITEMS.ID or
                                (
                                    SALESORDERID = @SALESORDERID and
                                    EVENTID = @EVENTID and
                                    SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID and
                                    SALESORDERITEMTICKETCOMBINATION.ID is null
                                )
                        );

                    declare @PROGRAMNAME nvarchar(100);
                    declare @PROGRAMCATEGORY nvarchar(100);

                    select
                        @PROGRAMNAME = PROGRAM.NAME,
                        @PROGRAMCATEGORY = isnull(PROGRAMCATEGORYCODE.DESCRIPTION, N'')
                    from
                        dbo.PROGRAM
                    left outer join
                        dbo.PROGRAMCATEGORYCODE on PROGRAMCATEGORYCODE.ID = PROGRAM.PROGRAMCATEGORYCODEID
                    where
                        PROGRAM.ID = @PROGRAMID;

                    insert into dbo.SALESORDERITEMTICKET
                    (
                        ID,
                        EVENTID,
                        PROGRAMID,
                        PROGRAMNAME,
                        PROGRAMCATEGORYNAME,
                        PRICETYPECODEID,
                        PRICE,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED, 
                        DATECHANGED
                    )
                    select
                        ITEMS.ID, 
                        @EVENTID, 
                        @PROGRAMID,
                        @PROGRAMNAME,
                        @PROGRAMCATEGORY,
                        ITEMS.PRICETYPECODEID,
                        ITEMS.PRICE,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                    from @ORDERITEMS ITEMS
                    where
                        ITEMS.ID in
                        ( select ID from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID ) and
                        not exists(select 1 from dbo.SALESORDERITEMTICKET where ID = ITEMS.ID);

                    exec dbo.USP_SALESORDER_CALCULATEFEES @SALESORDERID, @CHANGEAGENTID;

                    exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;

                    exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;
                end try

                begin catch
                    -- 4/16/09, LeeCh, Bug 19897, Starts

                    -- catch the datatype money overflow error, 

                    if ERROR_NUMBER() = 8115
                    begin
                        raiserror('ERR_TICKETINSERT_MONEYOVERFLOW', 13, 1);
                    end
                    -- 4/16/09, LeeCh, Bug 19897, Ends


                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;