USP_PROGRAMEVENT_INSERT

            CREATE procedure dbo.USP_PROGRAMEVENT_INSERT
            (
                @EVENTS xml = null,
                @SCHEDULEID uniqueidentifier = null,
                @PROGRAMID uniqueidentifier = null,
                @NAME nvarchar(100) = null,
                @DESCRIPTION nvarchar(500) = null,
                @CAPACITY integer = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null
            )
            as 
            set nocount on;
            begin

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

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

                declare @CURRENTSYSTEMDEFAULTDATE datetime
                set @CURRENTSYSTEMDEFAULTDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate())

                begin try 

                    declare @idoc int
                    exec sp_xml_preparedocument @idoc OUTPUT, @EVENTS
                    --handle the events first          

                    insert into dbo.EVENT
                    (
                        ID,
                        SCHEDULEID,
                        PROGRAMID,
                        NAME,
                        DESCRIPTION,
                        CAPACITY,
                        STARTDATE,
                        STARTTIME,
                        ENDDATE,
                        ENDTIME,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        EVENTID,
                        @SCHEDULEID,
                        @PROGRAMID,
                        @NAME,
                        @DESCRIPTION,
                        @CAPACITY,
                        STARTDATE,
                        STARTTIME,
                        ENDDATE,
                        ENDTIME,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    from openxml (@idoc,'/EVENTS/ITEM', 2)
                    with
                        (EVENTID uniqueidentifier,
                        STARTDATE date,
                        STARTTIME udt_HOURMINUTE,
                        ENDDATE date,
                        ENDTIME udt_HOURMINUTE,
                        ISCONFLICT bit)
                    where ISCONFLICT = 0


                    if exists(select 1 from dbo.PROGRAMSALESMETHOD where PROGRAMID = @PROGRAMID)
                    begin
                        insert into dbo.EVENTSALESMETHOD
                        (
                            ID,
                            EVENTID,
                            SALESMETHODID,
                            ONSALEDATE,
                            ONSALETIME,
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED
                        )
                        (
                            select
                                newid(),
                                p.eventid,
                                SALESMETHODID,
                                case ONSALETYPECODE
                                    when 0 then @CURRENTSYSTEMDEFAULTDATE
                                    when 1 then ONSALEDATE
                                    when 2 then dateadd(day, -1 * ONSALETIMEBEFORE, P.STARTDATE)
                                    when 3 then dateadd(week, -1 * ONSALETIMEBEFORE, P.STARTDATE)
                                    when 4 then dateadd(month, -1 * ONSALETIMEBEFORE, p.startdate)
                                end,
                                case ONSALETYPECODE
                                    when 0 then dbo.UFN_HOURMINUTE_GETFROMDATE(@CURRENTSYSTEMDEFAULTDATE)
                                    else ONSALETIME
                                end,
                                @CHANGEAGENTID, 
                                @CHANGEAGENTID, 
                                @CURRENTDATE, 
                                @CURRENTDATE
                            from dbo.PROGRAMSALESMETHOD
                    inner join openxml (@idoc,'/EVENTS/ITEM', 2)
                    with
                        (EVENTID uniqueidentifier,
                        STARTDATE date,
                        STARTTIME udt_HOURMINUTE,
                        ENDDATE date,
                        ENDTIME udt_HOURMINUTE,
                        PROGRAMID uniqueidentifier,
                        ISCONFLICT bit) P on P.PROGRAMID = PROGRAMSALESMETHOD.PROGRAMID
                        where P.ISCONFLICT = 0
                        )
                    END
                    exec sp_xml_removedocument @idoc                    

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