USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_ADDFROMXML

Used to add a set of records defined by UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL from the given xml string.
Parameters

Parameter	Parameter Type	Mode
@BATCHCONSTITUENTID	uniqueidentifier	IN
@XML	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_ADDFROMXML
            (
                @BATCHCONSTITUENTID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

            -- build a temporary table containing the values from the XML

            declare @TempTbl table (
               [COUNTRYID] uniqueidentifier,
               [ENDTIME] char(4),
               [ID] uniqueidentifier,
               [ISPRIMARY] bit,
               [NUMBER] nvarchar(100),
               [PHONETYPECODEID] uniqueidentifier,
               [SEASONALENDDATE] varchar(4),
               [SEASONALSTARTDATE] varchar(4),
               [SEQUENCE] int,
               [STARTDATE] date,
               [STARTTIME] char(4),
               [INFOSOURCECODEID] uniqueidentifier,
               [INFOSOURCECOMMENTS] nvarchar(256))

            insert into @TempTbl select 
                [COUNTRYID],
                [ENDTIME],
                [ID],
                [ISPRIMARY],
                [NUMBER] = dbo.UFN_PHONE_REMOVECOUNTRYCODE([NUMBER], [COUNTRYID]),
                [PHONETYPECODEID],
                [SEASONALENDDATE],
                [SEASONALSTARTDATE],
                [SEQUENCE],
                [STARTDATE],
                [STARTTIME],
                [INFOSOURCECODEID],
                [INFOSOURCECOMMENTS]
            from dbo.UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_FROMITEMLISTXML(@XML)

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            if @@Error <> 0
                return 1;

            -- insert new items

            insert into [BATCHCONSTITUENTPHONE] 
                ([BATCHCONSTITUENTID], 
                [COUNTRYID],
                [ENDTIME],
                [ID],
                [ISPRIMARY],
                [NUMBER],
                [PHONETYPECODEID],
                [SEASONALENDDATE],
                [SEASONALSTARTDATE],
                [SEQUENCE],
                [STARTDATE],
                [STARTTIME],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED,
                [INFOSOURCECODEID],
                [INFOSOURCECOMMENTS])
            select @BATCHCONSTITUENTID, 
                [COUNTRYID],
                [ENDTIME],
                [ID],
                [ISPRIMARY],
                [NUMBER],
                [PHONETYPECODEID],
                [SEASONALENDDATE],
                [SEASONALSTARTDATE],
                [SEQUENCE],
                [STARTDATE],
                [STARTTIME], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE,
                [INFOSOURCECODEID],
                ISNULL([INFOSOURCECOMMENTS],'')
            from @TempTbl as [temp]

            if @@Error <> 0
                return 2;

            return 0;