USP_INSTALLMENT_GETINSTALLMENTS_2_ADDFROMXML

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

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

Copy


            CREATE procedure dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_ADDFROMXML
            (
                @REVENUEID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
            begin

            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 (
                    [AMOUNT] money,
                    [BASECURRENCYID] uniqueidentifier,
                    [BASEEXCHANGERATEID] uniqueidentifier,
                    [DATE] datetime,
                    [ID] uniqueidentifier,
                    [ORGANIZATIONAMOUNT] money,
                    [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
                    [ORGANIZATIONRECEIPTAMOUNT] money,
                    [RECEIPTAMOUNT] money,
                    [SEQUENCE] int,
                    [TRANSACTIONAMOUNT] money,
                    [TRANSACTIONCURRENCYID] uniqueidentifier,
                    [TRANSACTIONRECEIPTAMOUNT] money)

                insert into @TempTbl select
                    [AMOUNT],
                    [BASECURRENCYID],
                    [BASEEXCHANGERATEID],
                    [DATE],
                    [ID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],
                    [ORGANIZATIONRECEIPTAMOUNT],
                    [RECEIPTAMOUNT],
                    [SEQUENCE],
                    [TRANSACTIONAMOUNT],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONRECEIPTAMOUNT] 
                from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_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 [INSTALLMENT] 
                    ([REVENUEID], 
                    [AMOUNT],
                    [BASECURRENCYID],
                    [BASEEXCHANGERATEID],
                    [DATE],
                    [ID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],
                    [ORGANIZATIONRECEIPTAMOUNT],
                    [RECEIPTAMOUNT],
                    [SEQUENCE],
                    [TRANSACTIONAMOUNT],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONRECEIPTAMOUNT],
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED)
                select
                    @REVENUEID,
                    [AMOUNT],
                    [BASECURRENCYID],
                    [BASEEXCHANGERATEID],
                    [DATE],
                    [ID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],
                    [ORGANIZATIONRECEIPTAMOUNT],
                    [RECEIPTAMOUNT],
                    [SEQUENCE],
                    [TRANSACTIONAMOUNT],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONRECEIPTAMOUNT],
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE
                from @TempTbl as [temp]

                if @@Error <> 0
                    return 2;

                return 0;

            end