USP_REVENUEUPDATEBATCH_NOTE_CUSTOMUPDATEFORITEMLISTXML

CREATE procedure dbo.USP_REVENUEUPDATEBATCH_NOTE_CUSTOMUPDATEFORITEMLISTXML
(
    @REVENUEID uniqueidentifier,
    @NOTES 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 (
    [ID] uniqueidentifier,
    [REVENUENOTETYPECODEID] uniqueidentifier,
    [TITLE] nvarchar(255),
    [DATEENTERED] datetime,
    [AUTHORID] uniqueidentifier,
    [TEXTNOTE] nvarchar(max),
    [REVENUENOTEID] uniqueidentifier,
    [HTMLNOTE] nvarchar(max)    
)

insert into @TempTbl (
    ID,
    REVENUENOTETYPECODEID,  
    TITLE,
    DATEENTERED, 
    AUTHORID, 
    TEXTNOTE,
    HTMLNOTE
)
select 
    REVENUENOTEID,
    NOTETYPECODEID, 
    NOTETITLE,
    NOTEDATEENTERED, 
    NOTEAUTHORID, 
    NOTETEXTNOTE,
    NOTEHTMLNOTE
from dbo.UFN_REVENUEUPDATEBATCH_GETNOTES_FROMITEMLISTXML(@NOTES)

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

if @@Error <> 0
    return 1;

declare @contextCache varbinary(128);
declare @e int;

-- cache current context information 

set @contextCache = CONTEXT_INFO();

-- set CONTEXT_INFO to @CHANGEAGENTID 

if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

-- delete any items that no longer exist in the XML table

delete from dbo.REVENUENOTE where REVENUENOTE.ID in 
    (select ID from dbo.REVENUENOTE RN where RN.REVENUEID = @REVENUEID
    EXCEPT select ID from @TempTbl)    

select @e=@@error;

-- reset CONTEXT_INFO to previous value 

if not @contextCache is null
    set CONTEXT_INFO @contextCache;

if @e <> 0
    return 2;

--update notes

update dbo.REVENUENOTE
set DATEENTERED = temp.DATEENTERED,
    TITLE = temp.TITLE,
    AUTHORID = temp.AUTHORID,
    TEXTNOTE = temp.TEXTNOTE,
    REVENUENOTETYPECODEID = temp.REVENUENOTETYPECODEID,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CHANGEDATE,
    HTMLNOTE = temp.HTMLNOTE
from dbo.REVENUENOTE inner join @TempTbl as temp on REVENUENOTE.ID = temp.ID
where 
    (REVENUENOTE.DATEENTERED<>temp.DATEENTERED) or 
    (REVENUENOTE.DATEENTERED is null and temp.DATEENTERED is not null) or 
    (REVENUENOTE.DATEENTERED is not null and temp.DATEENTERED is null) or 
    (REVENUENOTE.TITLE<>temp.TITLE) or 
    (REVENUENOTE.TITLE is null and temp.TITLE is not null) or 
    (REVENUENOTE.TITLE is not null and temp.TITLE is null) or 
    (REVENUENOTE.AUTHORID<>temp.AUTHORID) or 
    (REVENUENOTE.AUTHORID is null and temp.AUTHORID is not null) or 
    (REVENUENOTE.AUTHORID is not null and temp.AUTHORID is null) or 
    (REVENUENOTE.TEXTNOTE<>temp.TEXTNOTE) or 
    (REVENUENOTE.TEXTNOTE is null and temp.TEXTNOTE is not null) or 
    (REVENUENOTE.TEXTNOTE is not null and temp.TEXTNOTE is null) or 
    (REVENUENOTE.REVENUENOTETYPECODEID<>temp.REVENUENOTETYPECODEID) or 
    (REVENUENOTE.REVENUENOTETYPECODEID is null and temp.REVENUENOTETYPECODEID is not null) or 
    (REVENUENOTE.REVENUENOTETYPECODEID is not null and temp.REVENUENOTETYPECODEID is null) or
    (REVENUENOTE.HTMLNOTE<>temp.HTMLNOTE) or 
    (REVENUENOTE.HTMLNOTE is null and temp.HTMLNOTE is not null) or 
    (REVENUENOTE.HTMLNOTE is not null and temp.HTMLNOTE is null);

select @e=@@error;

-- reset CONTEXT_INFO to previous value 

if not @contextCache is null
    set CONTEXT_INFO @contextCache;

if @e <> 0
    return 3;


--insert notes

insert into dbo.[REVENUENOTE] 
(
    [ID],
    [DATEENTERED],
    [TITLE],
    [AUTHORID],
    [TEXTNOTE],
    [REVENUENOTETYPECODEID],
    [REVENUEID],
    [ADDEDBYID],
    [CHANGEDBYID],
    [DATEADDED],
    [DATECHANGED],
    [HTMLNOTE]
) 
select
    ID,
    DATEENTERED,
    TITLE,
    AUTHORID,
    TEXTNOTE, 
    REVENUENOTETYPECODEID,
    @REVENUEID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE,
    HTMLNOTE
from @TempTbl as temp
where not exists (select data.ID from dbo.REVENUENOTE as data where data.ID = temp.ID)

if @@Error <> 0
    return 4;

return 0;