USP_REVENUE_GETSPLITS_ADDFROMXML

CREATE PROCEDURE [dbo].[USP_REVENUE_GETSPLITS_ADDFROMXML] 
(
@REVENUEID 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()

declare @POSTSTATUSCODE tinyint;
declare @POSTDATE date;
select @POSTSTATUSCODE = case when FT.POSTSTATUSCODE = 2 then 1 else FT.POSTSTATUSCODE end, @POSTDATE = FT.POSTDATE
from dbo.FINANCIALTRANSACTION FT
where FT.ID = @REVENUEID;

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

declare @TempTbl table (
   [AMOUNT] money,
   [APPLICATIONCODE] tinyint,
   [DESIGNATIONID] uniqueidentifier,
   [ID] uniqueidentifier,
   [TYPECODE] tinyint)

insert into @TempTbl select 
    [AMOUNT],
    [APPLICATIONCODE],
    [DESIGNATIONID],
    [ID],
    [TYPECODE] 
from dbo.UFN_REVENUE_GETSPLITS_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 dbo.FINANCIALTRANSACTIONLINEITEM
    (FINANCIALTRANSACTIONID,
    BASEAMOUNT,
    ID,
    TYPECODE,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED,
    ORGAMOUNT,
    TRANSACTIONAMOUNT,
    POSTDATE,
    POSTSTATUSCODE)
select @REVENUEID,
    AMOUNT,
    ID,
    0,
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE,
    AMOUNT,
    AMOUNT,
    @POSTDATE,
    @POSTSTATUSCODE
from @TempTbl

insert into dbo.REVENUESPLIT_EXT
    (ID,
  TYPECODE,
    APPLICATIONCODE,
    DESIGNATIONID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED)
select ID,
  TYPECODE,
    APPLICATIONCODE,
    DESIGNATIONID,
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE
from @TempTbl        

if @@Error <> 0
    return 2;

return 0;