USP_REVENUEUPDATEBATCH_GETNOTES_ADDFROMXML

CREATE procedure dbo.USP_REVENUEUPDATEBATCH_GETNOTES_ADDFROMXML
(
@BATCHREVENUEID uniqueidentifier,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
with execute as caller
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,
   [NOTETITLE] nvarchar(max),
   [NOTEAUTHORID] uniqueidentifier,
   [NOTEDATEENTERED] date,
   [NOTETEXTNOTE] nvarchar(max),  
   [NOTETYPECODEID] uniqueidentifier,
   [REVENUENOTEID] uniqueidentifier,
   [NOTEHTMLNOTE] nvarchar(max))

insert into @TempTbl select 
    [ID],
    [NOTETITLE],
    [NOTEAUTHORID],
    [NOTEDATEENTERED],
    [NOTETEXTNOTE] ,
    [NOTETYPECODEID],
    [REVENUENOTEID],
    [NOTEHTMLNOTE]
from dbo.UFN_REVENUEUPDATEBATCH_GETNOTES_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 [BATCHREVENUENOTE] 
    ([BATCHREVENUEID], 
    [ID],
    [TITLE],
    [AUTHORID],
    [DATEENTERED],
    [TEXTNOTE] ,    
    [NOTETYPECODEID],
    [REVENUENOTEID],                
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED,
    [HTMLNOTE])
select @BATCHREVENUEID, 
    [ID],
    [NOTETITLE],
    [NOTEAUTHORID],
    [NOTEDATEENTERED],
    [NOTETEXTNOTE] ,    
    [NOTETYPECODEID],
    [REVENUENOTEID], 
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE,
    [NOTEHTMLNOTE]
from @TempTbl as [temp]

if @@Error <> 0
    return 2;

return 0;